工作上对JPA的使用记录

JPA定义：

JPA是Java Persistence API的简称，中文名Java持久层API，JPA的宗旨是为POJO提供持久化标准规范
JPQL（Java Persistence Query Language）中文名Java持久化查询语言，是JPA定义的一种独特的查询语言，它是针对实体的一种查询语言，操作对象是实体，而不是关系数据库中的表

注解

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@PersistenceContext
private EntityManager em;

单表查询

StringBuilder sb = new StringBuilder("select g.* from goods g where 1=1 ");
StringBuilder sum = new StringBuilder("select count(*) from goods g where 1=1 ");

if (StringUtils.isNotBlank(name)) {
     sb.append(" and g.name like ? ");
     sum.append(" and g.name like ? ");
}

// 总数量
Query sumQuery = em.createNativeQuery(sum.toString());
sumQuery .setParameter(1, String.format("%%%s%%", name));
Integer count = ((BigInteger)sumQuery .getSingleResult()).intValue();

Query query = em.createNativeQuery(sb.toString(), Goods.class);
// 第一个参数指的是占位符的位置（从1开始）
// 第二个参数指的是占位符的取值
query.setParameter(1, String.format("%%%s%%", name));

query.setFirstResult( (page-1)*size ); // 起始角标从0开始
query.setMaxResults(10); // 每页大小

PageImpl<Goods> pageImpl = new PageImpl(query.getResultList(), PageRequest.of(page-1,size), count);


## 常见查询方式
### 模糊查询
String name = "zhang";
query.setParameter(1, String.format("%%%s%%", name));
### 日期
String time= "2021-09-12 00:00:00";
Date time = new Date();
Timestamp time = new Timestamp(new Date().getTime());
query.setParameter(1, time)
### in
List<Long> ids = new ArrayList<>();
ids.add(1L);
query.setParameter(1, ids)

拼接原生SQL

StringBuilder pageData = new StringBuilder("select g.* from goods g where isDelete=0 ");
StringBuilder count = new StringBuilder("select count(*) from goods g where isDelete=0 ");
StringBuilder conditions = new StringBuilder();
// 模糊查询
conditions.append(" and name like ").append(String.format("'%%%s%%'", name));
// between(既包含头也包含尾)
conditions.append(" and start_time between ").append(String.format("'%s'", startTime1)).append(" and ").append(String.format("'%s'", startTime2));
// 日期
String timeStr = "2020-12-12 10:10:10"
Date timeDate = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
conditions.append(" and start_time > ").append(String.format("'%s'", time));
conditions.append(" and start_time > ").append(String.format("'%s'", sdf.format(timeDate)));
// 日期函数
conditions.append(" and start_time > now()");

其它

@PersistenceContext
private EntityManager em;

return new PageImpl<VoteVo>(VoteVos, new PageRequest(page - 1, size), total.size());