SpringBoot系列——Spring-Data-JPA

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

前言

jpa是ORM映射框架，更多详情，请戳：apring-data-jpa官网：http://spring.io/projects/spring-data-jpa，以及一篇优秀的博客：https://www.cnblogs.com/cmfwm/p/8109433.html，这里只是记录项目实现。

查询方式

jpa查询方法大致可分为JPA命名查询，@Query查询，EntityManager对象查询

JPA命名查询

interface PersonRepositoryextendsRepository<User, Long>{
List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress,String lastname);
// 去重查询
List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname,String firstname);
List<Person> findPeopleDistinctByLastnameOrFirstname(String lastname,String firstname)
// 忽略大小写
List<Person> findByLastnameIgnoreCase(String lastname);
List<Person> findByLastnameAndFirstnameAllIgnoreCase(String lastname,String firstname);
// 排序查询
List<Person> findByLastnameOrderByFirstnameAsc(String lastname);
List<Person> findByLastnameOrderByFirstnameDesc(String lastname);}
}

使用Top和First限制查询的结果大小

User findFirstByOrderByLastnameAsc();
User findTopByOrderByAgeDesc();
Page<User> queryFirst10ByLastname(String lastname,Pageable pageable);
Slice<User> findTop3ByLastname(String lastname,Pageable pageable);
List<User> findFirst10ByLastname(String lastname,Sort sort);
List<User> findTop10ByLastname(String lastname,Pageable pageable);

统计查询

interface UserRepositoryextendsCrudRepository<User,Long>{
long countByLastname(String lastname);
}

分页查询

Page<User>findAll(PageRequest.of(1,20));

下表描述了JPA支持的关键字以及包含该关键字的JPA命名查询方法：

关键字	示例	SQL
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull	findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1(parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1(parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1(parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection<Age> ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection<Age> ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

@Query查询

普通查询

public interface UserRepositoryextendsJpaRepository<User,Long>{
@Query(value ="SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery =true)
User findByEmailAddress(String emailAddress);
}

排序查询

public interface UserRepositoryextendsJpaRepository<User, Long>{
@Query("select u from User u where u.lastname like ?1%")
List<User> findByAndSort(String lastname,Sort sort);
}

分页查询

public interface UserRepositoryextendsJpaRepository<User,Long>{
@Query(value ="SELECT * FROM USERS WHERE LASTNAME = ?1",
countQuery ="SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery =true)
Page<User> findByLastname(String lastname,Pageable pageable);
}

使用命名参数

public interface UserRepositoryextendsJpaRepository<User,Long>{
@Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
User findByLastnameOrFirstname(@Param("lastname")String lastname,@Param("firstname")String firstname);
}

删除，如果希望自动清除EntityManager，可以将@Modifying注释的clearautomatic属性设置为true。

interface UserRepositoryextendsRepository<User, Long> {
@Modifying
@Transactional
@Query("delete from User u where user.role.id = ?1")
void deleteInBulkByRoleId(long roleId);
}

EntityManager对象查询

@PersistenceContext
private EntityManager em; private void pageTest() {
//SQL
String sql = "select * from tb_user t where t.username like :name"; //设置SQL、映射实体，以及设置值，返回一个Query对象
Query query = em.createNativeQuery(sql, TbUser.class).setParameter("name", "huanzi%"); //分页、排序信息，并设置，page从0开始
PageRequest pageRequest = PageRequest.of(0, 10, new Sort(Sort.Direction.ASC, "id"));
query.setFirstResult((int) pageRequest.getOffset());
query.setMaxResults(pageRequest.getPageSize()); //获取分页结果
Page page = PageableExecutionUtils.getPage(query.getResultList(), pageRequest, () -> {
//设置countQuerySQL语句
Query countQuery = em.createNativeQuery("select count(1) from ( " + ((NativeQueryImpl) query).getQueryString() + " ) count_table");
//设置countQuerySQL参数
query.getParameters().forEach(parameter -> countQuery.setParameter(parameter.getName(), query.getParameterValue(parameter.getName())));
//返回一个总数
return Long.valueOf(countQuery.getResultList().get(0).toString());
}); //组装返回值 //总数
long total = page.getTotalElements();
System.out.println(total);
//分页信息
Pageable pageable = page.getPageable();
System.out.println(pageable);
//数据集合
List<TbUser> content = page.getContent();
System.out.println(content.size());
System.out.println(content);
}

工程结构

代码编写

maven引包

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency> 
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency> 
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>

applicaction.yml

#注意:在yml文件中添加value值时,value前面需要加一个空格
#2.0.0的配置切换为servlet.path而不是"-"
server:
port: #端口号
servlet:
context-path: /springboot #访问根路径spring:
thymeleaf:
cache: false #关闭页面缓存
prefix: classpath:/view/ #thymeleaf访问根路径
mode: LEGACYHTML5 datasource: #数据库相关
url: jdbc:mysql://localhost:/test?characterEncoding=utf-
username: root
password:
driver-class-name: com.mysql.jdbc.Driver jpa:
show-sql: true mvc:
date-format: yyyy-MM-dd HH:mm:ss #mvc接收参数时对日期进行格式化 jackson:
date-format: yyyy-MM-dd HH:mm:ss #jackson对响应回去的日期参数进行格式化
time-zone: GMT+

实体类与表数据

tb_user

tb_description

/**
* 用户类
*/
@Entity
@Table(name = "tb_user")
@Data
public class User implements Serializable { @Id
@GeneratedValue(strategy= GenerationType.IDENTITY) //IDENTITY 自增
private Integer id; @Column(name = "username")//命名相同或驼峰标识（与数据库下划线映射）可以不用写
private String username; private String password; private Date created; private String descriptionId; @OneToOne
@JoinColumn(name = "descriptionId",referencedColumnName = "id", insertable = false, updatable = false)
@NotFound(action= NotFoundAction.IGNORE)
//用户描述信息
private Description description;
}

/**
* 用户描述类
*/
@Entity
@Table(name = "tb_description")
@Data
public class Description implements Serializable {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY) //IDENTITY 自增
private Integer id; private String userId; private String description;
}

通讯对象

/**
* 统一返回对象
*/@Data
public class Result<T> implements Serializable {
/**
* 通信数据
*/
private T data;
/**
* 通信状态
*/
private boolean flag = true;
/**
* 通信描述
*/
private String msg = ""; /**
* 通过静态方法获取实例
*/
public static <T> Result<T> of(T data) {
return new Result<>(data);
} public static <T> Result<T> of(T data, boolean flag) {
return new Result<>(data, flag);
} public static <T> Result<T> of(T data, boolean flag, String msg) {
return new Result<>(data, flag, msg);
} @Deprecated
public Result() { } private Result(T data) {
this.data = data;
} private Result(T data, boolean flag) {
this.data = data;
this.flag = flag;
} private Result(T data, boolean flag, String msg) {
this.data = data;
this.flag = flag;
this.msg = msg;
}}

分页对象

/**
* 分页对象（参考JqGrid插件）
*/
@Data
public class PageInfo<M> {
private int page;//当前页码
private int pageSize;//页面大小
private String sidx;//排序字段
private String sord;//排序方式 private List<M> rows;//分页结果
private int records;//总记录数
private int total;//总页数 /**
* 获取统一分页对象
*/
public static <M> PageInfo<M> of(Page page, Class<M> entityModelClass) {
int records = (int) page.getTotalElements();
int pageSize = page.getSize();
int total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1; PageInfo<M> pageInfo = new PageInfo<>();
pageInfo.setPage(page.getNumber() + 1);//页码
pageInfo.setPageSize(pageSize);//页面大小
pageInfo.setRows(CopyUtil.copyList(page.getContent(), entityModelClass));//分页结果
pageInfo.setRecords(records);//总记录数
pageInfo.setTotal(total);//总页数
return pageInfo;
} /**
* 获取JPA的分页对象
*/
public static Page readPage(Query query, Pageable pageable, Query countQuery) {
if (pageable.isPaged()) {
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
}
return PageableExecutionUtils.getPage(query.getResultList(), pageable, () -> executeCountQuery(countQuery));
} private static Long executeCountQuery(Query countQuery) {
Assert.notNull(countQuery, "TypedQuery must not be null!"); List<Number> totals = countQuery.getResultList();
Long total = 0L;
for (Number number : totals) {
if (number != null) {
total += number.longValue();
}
}
return total;
}
}

/**
* 分页条件（参考JqGrid插件）
*/
@Data
public class PageCondition {
private int page = 1;//当前页码
private int rows = 10;//页面大小
private String sidx;//排序字段
private String sord;//排序方式 /**
* 获取JPA的分页查询对象
*/
public Pageable getPageable() {
//处理非法页码
if (page < 0) {
page = 1;
}
//处理非法页面大小
if (rows < 0) {
rows = 10;
}
return PageRequest.of(page - 1, rows);
}
}

2019-09-27补充：之前的这个分页信息对象不是很好，现在更新一下

1、int改成Integer

2、非法参数处理加强

3、使用@JsonInclude注解，减少数据传输，例如：

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.util.StringUtils;/**
* 分页条件（参考JqGrid插件）
*/
@Data
//当属性的值为空（null或者""）时，不进行序列化，可以减少数据传输
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class PageCondition {
private Integer page;//当前页码
private Integer rows;//页面大小
private String sidx;//排序字段
private String sord;//排序方式 /**
* 获取JPA的分页查询对象
*/
@JsonIgnore
public Pageable getPageable() {
//处理非法页码
if (StringUtils.isEmpty(page) || page < 0) {
page = 1;
}
//处理非法页面大小
if (StringUtils.isEmpty(rows) || rows < 0) {
rows = 10;
}
return PageRequest.of(page - 1, rows);
}
}

UserController

@RestController
@RequestMapping("/user")
public class UserController { @Autowired
private UserService userService; @RequestMapping("/getAllUser")
public ModelAndView getAllUser(){
Result result=userService.getAllUser();
ModelAndView mv=new ModelAndView();
mv.addObject("userList",result.getData());
mv.setViewName("index.html");
return mv;
} @RequestMapping("page")
public Result<PageInfo<User>> page(User entity, PageCondition pageCondition) {
return userService.page(entity,pageCondition);
} @RequestMapping("list")
public Result<List<User>> list(User entity) {
return userService.list(entity);
} @RequestMapping("get/{id}")
public Result<User> get(@PathVariable("id") Integer id) {
return userService.get(id);
} @RequestMapping("save")
public Result<User> save(User entity) {
return userService.save(entity);
} @RequestMapping("delete/{id}")
public Result<Integer> delete(@PathVariable("id") Integer id){
return userService.delete(id);
}
}

UserService

public interface UserService{ Result<PageInfo<User>> page(User entity, PageCondition pageCondition); Result<List<User>> list(User entity); Result<User> get(Integer id); Result<User> save(User entity); Result<Integer> delete(Integer id); Result getAllUser();
}

@Service
@Transactional
public class UserServiceImpl implements UserService { @Autowired
private UserRepository userRepository; @Override
public Result<PageInfo<User>> page(User entity ,PageCondition pageCondition) {
Page<User> page = userRepository.findAll(Example.of(CopyUtil.copy(entity, User.class)), pageCondition.getPageable());
int records = (int) page.getTotalElements();
int pageSize = page.getSize();
int total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1;
PageInfo<User> pageInfo = new PageInfo<>();
pageInfo.setPage(page.getNumber() + 1);//页码
pageInfo.setPageSize(pageSize);//页面大小
pageInfo.setRows(page.getContent());//分页结果
pageInfo.setRecords(records);//总记录数
pageInfo.setTotal(total);//总页数
return Result.of(pageInfo);
} @Override
public Result<List<User>> list(User entity) {
List<User> entityList = userRepository.findAll(Example.of(entity));
return Result.of(entityList);
} @Override
public Result<User> get(Integer id) {
Optional<User> optionalE = userRepository.findById(id);
if (!optionalE.isPresent()) {
throw new RuntimeException("ID不存在！");
}
return Result.of(optionalE.get());
} @Override
public Result<User> save(User entity) {
User user = userRepository.save(entity);
return Result.of(user);
} @Override
public Result<Integer> delete(Integer id) {
userRepository.deleteById(id);
return Result.of(id);
} @Override
public Result getAllUser() {
List<User> userList = userRepository.getAllUser();
if(userList != null && userList.size()>0){
return Result.of(userList);
}else {
return Result.of(userList,false,"获取失败！");
}
}
}

UserRepository

public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> { @Query(value = "from User") //HQL
// @Query(value = "select * from tb_user",nativeQuery = true)//原生SQL
List<User> getAllUser();}

效果

get接口

http://localhost:10086/springboot/user/get/1

list接口

http://localhost:10086/springboot/user/list

http://localhost:10086/springboot/user/list?username=张三

page接口

http://localhost:10086/springboot/user/page?page=1&rows=10

http://localhost:10086/springboot/user/page?page=1&rows=10&username=张三

save接口（插入跟更新）

没有id或id不存在，为插入，http://localhost:10086/springboot/user/save?username=张麻子&password=123

id已存在，则为更新，注意：这里的更新是你的字段是什么jpa就帮你存什么，如果想要实现只更新接参对象有值的字段，应该先用id去同步数据，再更新，http://localhost:10086/springboot/user/save?id=1&username=张三1&password=666

delete接口

http://localhost:10086/springboot/user/delete/6

自定义Dao层方法

http://localhost:10086/springboot/user/getAllUser

后记

JPA牛逼！

注意：

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.2.RELEASE</version>
<relativePath/>
</parent>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.9.RELEASE</version>
<relativePath/>
</parent>

2.0之后是大版本升级，使用了1.8JDK，许多方法API都发生了改变，其中就碰到了一个坑，之前写得不规范，Service中已经@Transactional了一次，Repository的自定义SQL又@Transactional一次，当我们service直接调公用JPA方法，后面又调我们自定义的Dao层方法时，由于事务传播，后面的事务不进行提交，而且也不报错

补充

有同学发现我们少贴了部分代码，在这里补充一下，CopyUtil类是我们自定义的一个实体类型转换的工具类，用于将实体模型与实体的转换，在 SpringBoot系列——Spring-Data-JPA（升级版）中，我们已经对该工具类进行了升级，升级之后支持复杂对象的转换

/**
* 实体类型转换的工具类
*/public class CopyUtil { /**
* 类型转换：实体模型<->实体
* <p>
* 例如：List<DataModel> <--> List<Data>
*/ public static <T> List<T> copyList(List list, Class<T> target) {
List<T> newList = new ArrayList<>();
for (Object entity : list) {
newList.add(copy(entity, target));
}
return newList;
} /**
* 类型转换：实体模型 <->实体
* <p>
* 例如：DataModel <--> Data
*/
public static <T> T copy(Object origin, Class<T> target) {
T t = null;
try {
t = target.newInstance();
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
}
BeanUtils.copyProperties(origin, t);
return t;
}
}

2019-06-21补充：jpa有个坑，默认情况下（数据库是oracle），调用super.方法，事务会排在最后提交，因此造成我update的数据后面又被super的事务update回来

（PS：如果你看到这里，就要注意正确使用super.方法，super.方法应该要放在最后调用，避免入坑，事实上，关于jpa的事务管理还有很多坑，这个我们以后再好好聊）

例如：

//先调用父类的保存方法
super.save(); //执行自定义update语句
my.update();

虽然代码先执行super.save()，但这个保存事务会在最后执行提交

我们期待的事务顺序应该是这样

最后我是直接使用在service层注入dao层，直接调用dao.save()，事务处理就与我们期待的一样了

可能有些同学看到会有些疑问，看到我两个操作都有操作同一个表，为什么不合并成一次操作呢，直接修改对象值，调用super.save()方法进行保存不就行了吗，还要直接写update？？这是因为save方法我们进行了特殊处理，jpa原生save方法是传入的对象的属性值是什么就帮我们保存什么，这并不符合我们的常规保存操作，我传入一个对象，里面哪些属性有值就帮我们保存哪些值，而我们的自定义update刚好就是有设置某个字段的值为空，调用父类的save方法不会帮我们设置，所以才需要单独写一个update

我被SQL注入了

2019-10-24补充：客户突然反馈，网站访问出现访问响应时间太久、进而崩溃的情况，查看日志发现是数据库连接超时，仔细一看执行的SQL不对劲

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30014ms.

select * from super_search_ucid where 1 = 1 and game_id = '' AND 7645=IF((ORD(MID((SELECT DISTINCT(IFNULL(CAST(grantee AS CHAR),CHAR(32))) FROM information_schema.USER_PRIVILEGES LIMIT 0,1),2,1)) > 3008),SLEEP(5),7645) AND 'HhsP'='HhsP' and role_list like '%SP琉%' and props_list like '%MR琉璃%' and ( division like '%官服安卓IOS通用%' )

这个SQL被SQL注入攻击，这个就是对方注入进来的字符串

6' AND 7645=IF((ORD(MID((SELECT DISTINCT(IFNULL(CAST(grantee AS CHAR),CHAR(32))) FROM information_schema.USER_PRIVILEGES LIMIT 0,1),2,1)) > 3008),SLEEP(5),7645) AND 'HhsP'='HhsP

他在疯狂的查询信息库的用户权限表，企图搞事情

经实测，这个SQL在生产上执行耗时一百多秒都没响应，导致数据库连接池的连接线程一直被占用，没有空闲的可以被调用导致连接超时

这个接口是查询接口，我们在Security的配置中配置的是无需登录就可以访问，而且由于需要分页，同时还需要等值查询等各种操作，所以我采用的是文章前面提到的 EntityManager对象查询 方法，动态拼接SQL，而且当时并没有想到需要防范SQL注入，这才落下了那么大的一个坑，所幸并没有造成重大影响

防范、解决：

1、动态拼接的SQL一定要记得做转义

/**
* sql转义
*/
public static String escapeSql(String str) {
if (str == null) {
return null;
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < str.length(); i++) {
char src = str.charAt(i);
switch (src) {
case '\'':
sb.append("''");// hibernate转义多个单引号必须用两个单引号
break;
case '\"':
case '\\':
sb.append('\\');
default:
sb.append(src);
break;
}
}
return sb.toString();
}