接着上面spring boot系列（四）spring boot 配置spring data jpa 保存修改方法继续做查询的测试：
1 创建UserInfo实体类，代码和https://www.cnblogs.com/kxm87/p/9273555.html中的一样。
2 创建数据库操作类相当于dao层，主要创建一个接口UserRepository，继承JpaRepository接口即可。本代码中主要都是自定义方法。
使用findXX 或者countXX（这两个不用编写sql，jpa会自动生成） 或者@Query 编写JPQL语句 或者原生语句
package com.cfj.ceshi.jpa.repository; import java.util.List; import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param; import com.cfj.ceshi.jpa.domain.UserInfo; public interface UserRepository extends JpaRepository<UserInfo, Integer> { List<UserInfo> findByUserNameNotNull(); //不为空 List<UserInfo> findByUserNameIsNull(); //为空 /**
* 按照姓名=?查询
* 最终转成sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.user_name=?
* @param name
* @return
*/
UserInfo findByUserName(String name);//用名字直接查询 /**
* 按照 name = ? and age = ? 查询
* 最终转成sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.user_name=? and userinfo0_.age=?
* @param name
* @param age
* @return
*/
UserInfo findByUserNameAndAge(String name,String age);//and /**
* 按照 id between ? and ? 查询
* 最终解析sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.id between ? and ?
* @param idStrat
* @param idEnd
* @return
*/
List<UserInfo> findByIdBetween(Integer idStrat,Integer idEnd); /**
* 按照年龄统计行数
* 最终解析sql语句为：select count(userinfo0_.id) as col_0_0_ from user_info userinfo0_ where userinfo0_.age=?
* @param age
* @return
*/
Integer countByAge(String age); /**
* 目的是类似 user_name like '%dd%'这种查询 所以必须用Containing
* 这样会把%%包含在里面 如果直接是like 则不包含%
* @param name
* @return
*/
List<UserInfo> findByUserNameContaining(String name); /**
* 按照username in ()
* 最终解析sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.user_name in (? , ?) * @return
*/
List<UserInfo> findByUserNameIn(List<String> list); /**
* 使用JPA sql查询 注意 1 对应userName 2 对应age ？号中的数字和参数表中从左向右顺序一一对应
* @param userName
* @param age
* @return
*/
@Query(value = "select u from UserInfo u where u.userName=?1 and u.age = ?2")
UserInfo getUserInfo(String userName,String age); /**
* 使用JPA sql查询 注意 1 对应userName 2 对应age ？号中的数字和参数表中从左向右顺序一一对应
* 返回name
* @param userName
* @param age
* @return
*/
@Query(value = "select u.userName from UserInfo u where u.userName=?1 and u.age = ?2")
String getUserInfoTwo(String userName,String age); /**
* 使用原生sql查询
* @param userName
* @param age
* @return
*/
@Query(value = "select * from user_info u where u.user_name=?1 and u.age = ?2" ,nativeQuery = true)
UserInfo getUserInfoThree(String userName,String age); /**
* 用参数方式
* @param userName
* @param age
* @return
*/
@Query(value = "select u.userName from UserInfo u where u.userName=:name and u.age = :age")
String getUserInfoFour(@Param("name")String userName,@Param("age")String age); /**
* jpa sql
* @param age
* @param userName
* @return
*/
@Modifying
@Query(value = "update UserInfo u set u.age = ?1 where u.userName = ?2")
Integer updateUserInfo(String age,String userName); /**
* 原生sql
* @param age
* @param userName
* @return
*/
@Modifying
@Query(value = "update user_info set age = ?1 where user_name = ?2",nativeQuery = true)
Integer updateUserInfoOne(String age,String userName); /**
* 删除方法
* @param userName
* @return
*/
@Modifying
@Query(value = "delete from UserInfo u where u.userName = ?1")
Integer deleteUserInfo(String userName); }
3 创建service接口和它的实现类，代码如下：
package com.cfj.ceshi.jpa.service;
import java.util.List;
import com.cfj.ceshi.jpa.domain.UserInfo;
public interface UserService {
public Integer save(UserInfo user);
public void delete(Integer id);
List<UserInfo> findByUserNameNotNull();
List<UserInfo> findByUserNameIsNull();
UserInfo findByUserName(String name);//用名字直接查询
UserInfo findByUserNameAndAge(String name,String age);//and
List<UserInfo> findByUserIdBetween(Integer idStrat,Integer idEnd);
Integer countByAge(String age);
List<UserInfo> findByUserNameContaining(String name);
List<UserInfo> findByUserNameIn(List<String> list);
UserInfo getUserInfo(String userName,String age);
String getUserInfoTwo(String userName,String age);
UserInfo getUserInfoThree(String userName,String age);
String getUserInfoFour(String userName,String age);
Integer updateUserInfo(String age,String userName);
Integer updateUserInfoOne(String age,String userName);
Integer deleteUserInfo(String userName);
UserInfo findOne(Integer id);
List<UserInfo> findAll();
}
package com.cfj.ceshi.jpa.service.impl; import java.util.List; import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional; import com.cfj.ceshi.jpa.domain.UserInfo;
import com.cfj.ceshi.jpa.repository.UserRepository;
import com.cfj.ceshi.jpa.service.UserService; @Service
@Transactional
public class UserServiceImpl implements UserService{ @Autowired
private UserRepository userRepository; @Override
public Integer save(UserInfo user) {
return userRepository.save(user).getId();
} @Override
public void delete(Integer id) {
userRepository.delete(id);
} @Override
public List<UserInfo> findByUserNameNotNull() {
return userRepository.findByUserNameNotNull();
} @Override
public List<UserInfo> findByUserNameIsNull() {
return userRepository.findByUserNameIsNull();
} @Override
public UserInfo findByUserName(String name) {
return userRepository.findByUserName(name);
} @Override
public UserInfo findByUserNameAndAge(String name, String age) {
return userRepository.findByUserNameAndAge(name, age);
} @Override
public List<UserInfo> findByUserIdBetween(Integer idStrat, Integer idEnd) {
return userRepository.findByIdBetween(idStrat, idEnd);
} @Override
public Integer countByAge(String age) {
return userRepository.countByAge(age);
} @Override
public List<UserInfo> findByUserNameContaining(String name) {
return userRepository.findByUserNameContaining(name);
} @Override
public List<UserInfo> findByUserNameIn(List<String> list) {
return userRepository.findByUserNameIn(list);
} @Override
public UserInfo getUserInfo(String userName, String age) {
return userRepository.getUserInfo(userName, age);
} @Override
public String getUserInfoTwo(String userName, String age) {
return userRepository.getUserInfoTwo(userName, age);
} @Override
public UserInfo getUserInfoThree(String userName, String age) {
return userRepository.getUserInfoThree(userName, age);
} @Override
public String getUserInfoFour(String userName, String age) {
return userRepository.getUserInfoFour(userName, age);
} @Override
public Integer updateUserInfo(String age, String userName) {
return userRepository.updateUserInfo(age, userName);
} @Override
public Integer updateUserInfoOne(String age, String userName) {
return userRepository.updateUserInfoOne(age, userName);
} @Override
public Integer deleteUserInfo(String userName) {
return userRepository.deleteUserInfo(userName);
} @Override
public UserInfo findOne(Integer id) {
return userRepository.findOne(id);
} @Override
public List<UserInfo> findAll() {
return userRepository.findAll();
} }
有删除或者修改的时候 service层一定要加上事务控制@Transactional，否则就会报错:Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query
4 创建控制类（controller）在spring boot 里面用web包表示，代码如下：
package com.cfj.ceshi.jpa.web; import java.util.ArrayList;
import java.util.List; import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController; import com.cfj.ceshi.jpa.domain.UserInfo;
import com.cfj.ceshi.jpa.service.UserService; @RestController
@RequestMapping("/userquery")
public class UserQueryWeb { @Autowired
private UserService userService; @RequestMapping("/checknull")
public String getUserInfoOne() {
String result = "id集合:[";
//List<UserInfo> list = userService.findByUserNameNotNull(); List<UserInfo> list = userService.findByUserNameIsNull(); for (int i = 0; i< list.size(); i++) {
UserInfo u = new UserInfo();
u = list.get(i);
result += u.getId().toString()+",";
}
result = result+"]";
return result; } /**
* 按照姓名=?查询
* 最终转成sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.user_name=?
* @param name
* @return
*/
@RequestMapping("/getByName")
public String getUserInfoByName(String name) {
return userService.findByUserName(name).toString(); } /**
* 按照 name = ? and age = ? 查询
* 最终转成sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.user_name=? and userinfo0_.age=?
* @param name
* @param age
* @return
*/
@RequestMapping("/getByNameAndAge")
public String getUserInfoByName(String name,String age) {
return userService.findByUserNameAndAge(name, age).toString(); } /**
* 按照 id between ? and ? 查询
* 最终解析sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.id between ? and ?
* @param idStrat
* @param idEnd
* @return
*/
@RequestMapping("/getByIdBetween")
public String getUserInfoTwo(Integer idStrat, Integer idEnd) { String result = "id集合:[";
List<UserInfo> list = userService.findByUserIdBetween(idStrat, idEnd); for (int i = 0; i< list.size(); i++) {
UserInfo u = new UserInfo();
u = list.get(i);
result += u.getId().toString()+",";
}
result = result+"]";
return result; }
/**
* 按照年龄统计行数
* 最终解析sql语句为：select count(userinfo0_.id) as col_0_0_ from user_info userinfo0_ where userinfo0_.age=?
* @param age
* @return
*/
@RequestMapping("/getByCount")
public Integer getCount(String age) {
return userService.countByAge(age); } /**
* 目的是类似 user_name like '%dd%'这种查询 所以必须用Containing
* 这样会把%%包含在里面 如果直接是like 则不包含%
* @param name
* @return
*/
@RequestMapping("/getByUserNameLike")
public String getUserNameLike(String name) { String result = "id集合:[";
List<UserInfo> list = userService.findByUserNameContaining(name); for (int i = 0; i< list.size(); i++) {
UserInfo u = new UserInfo();
u = list.get(i);
result += u.getId().toString()+",";
}
result = result+"]";
return result; } /**
* 按照username in ()
* 最终解析sql语句为：select userinfo0_.id as id1_0_, userinfo0_.age as age2_0_,
* userinfo0_.user_name as user_nam3_0_ from user_info userinfo0_
* where userinfo0_.user_name in (? , ?) * @return
*/
@RequestMapping("/getByUserNameIn")
public String getUserNameIn() { String result = "id集合:[";
List<String> arrylist = new ArrayList<String>();
arrylist.add("w");
arrylist.add("ww");
List<UserInfo> list = userService.findByUserNameIn(arrylist); for (int i = 0; i< list.size(); i++) {
UserInfo u = new UserInfo();
u = list.get(i);
result += u.getId().toString()+",";
}
result = result+"]";
return result; } /*以下使用@Query查询*/ //使用JPA sql查询 注意 1 对应userName 2 对应age ？号中的数字和参数表中从左向右顺序一一对应
@RequestMapping("/testquery")
public String getUserNameByNameAndAge() { return userService.getUserInfo("rrr", "333").toString(); } //使用JPA sql查询 注意 1 对应userName 2 对应age ？号中的数字和参数表中从左向右顺序一一对应 返回name
@RequestMapping("/testquerytwo")
public String getUserNameByNameAndAgeTwo(String name,String age) { return userService.getUserInfoTwo(name, age); } // 使用原生sql查询
@RequestMapping("/testquerythree")
public String getUserNameByNameAndAgeThree() { return userService.getUserInfo("rrr", "333").toString(); } //使用参数方式 @Param("name")
@RequestMapping("/testqueryfour")
public String getUserNameByNameAndAgeFour(String name,String age) { return userService.getUserInfoFour(name, age); } //jpa sql 更新
@RequestMapping("/updateUserInfo")
public String updateUserInfo(String age,String name) { return userService.updateUserInfo(age, name).toString(); } //原生sql 更新
@RequestMapping("/updateUserInfoOne")
public String updateUserInfoOne(String age,String name) { return userService.updateUserInfoOne(age, name).toString(); }
//删除
@RequestMapping("/deleteUserInfo")
public String deleteUserInfo(String name) { return userService.deleteUserInfo(name).toString(); } //按照id查询 使用spring data jpa接口中的findOne方法
@RequestMapping("/getOne")
public String getOne(Integer id) {
return userService.findOne(id).toString();
} //按照id查询 使用spring data jpa接口中的findAll方法
@RequestMapping("/getAll")
public String getAll() {
String result = "id集合:[";
List<UserInfo> list = userService.findAll(); for (int i = 0; i< list.size(); i++) {
UserInfo u = new UserInfo();
u = list.get(i);
result += u.getId().toString()+",";
}
result = result+"]";
return result;
} }
5 使用postman工具测试
以上都是查询方法，所有用postman方法测试的时候，使用GET方式，然后选择URL栏旁边的Params设置传参，如下图：
本项目码云地址：