接着上面spring boot系列(四)spring boot 配置spring data jpa 保存修改方法继续做查询的测试:
1 创建UserInfo实体类,代码和中的一样。
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设置传参,如下图: