JPA
-
Entity初始化
- 从数据库反向生成实体
- Entity类注解
@Table(name = "pm_user")只保留name,删除schema``catalog -
UUID为了方便, 数据库实体统一使用String - 时间类型修改为
LocalDateTime方便使用 - 布尔值,数据库中指定为
tinyint(1), 转换为实体修改为boolean
-
基础查询
以Entity为基本, 适用于最简单的单表查询
接口继承
public List<PmUserModel> search(String loginNameParam, String trueNameParam, Pageable pageable) {
//JpaRepository<T, K> 基本jpa查询接口,可以直接拼凑查询方法名 T=Entity类型, K=主键类型
pmUserRepository.getOne(id);
pmUserRepository.findAll();
//PagingAndSortingRepository<T, K> 分页排序接口,返回 Page<PmUserEntity>, pageable 为前端传入spring自动构建的pageable类
pmUserRepository.findByLoginNameContaining(name, pageable);
// JpaSpecificationExecutor<T> 条件查询接口, 书写太复杂,不建议使用
pmUserRepository.findAll(new Specification<PmUserEntity>() {
@Override
public Predicate toPredicate(Root<PmUserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
if (!StringUtils.isEmpty(loginNameParam)) {
list.add(criteriaBuilder.like(root.get("loginName"), "%" + loginNameParam + "%"));
}
if (!StringUtils.isEmpty(trueNameParam)) {
list.add(criteriaBuilder.like(root.get("trueName"), "%" + trueNameParam + "%"));
}
Predicate[] p = new Predicate[list.size()];
return criteriaBuilder.and(list.toArray(p));
}
}, pageable);
}
QueryDSL
<!--query dsl-->
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
<!--query dsl end-->
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
需要先执行 mvn:compile
单表配合JPA
//QuerydslPredicateExecutor<PmUserEntity> QueryDSL配合JPA查询接口, 比paSpecificationExecutor<T>要简单实用
public List<PmUserModel> search(String loginNameParam, String trueNameParam, Pageable pageable) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
BooleanBuilder where = new BooleanBuilder();
if (loginNameParam != null) {
where.and(QPmUserEntity.pmUserEntity.loginName.like('%' + loginNameParam + '%'));
}
if (trueNameParam != null) {
where.and(QPmUserEntity.pmUserEntity.trueName.like('%' + trueNameParam + '%'));
}
return pmUserRepository.findAll(where, pageable);
}
多表联查
public List<PmUserModel> search(String loginNameParam, String trueNameParam, Pageable pageable) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
BooleanBuilder where = new BooleanBuilder();
if (loginNameParam != null) {
where.and(QPmUserEntity.pmUserEntity.loginName.like('%' + loginNameParam + '%'));
}
if (trueNameParam != null) {
where.and(QPmUserEntity.pmUserEntity.trueName.like('%' + trueNameParam + '%'));
}
List<PmUserModel> userModelList = queryFactory
// .select(QPmUserEntity.pmUserEntity, QParamSexEntity.paramSexEntity)
.select(Projections.bean(
PmUserModel.class,//返回自定义实体的类型
QPmUserEntity.pmUserEntity.id,
QPmUserEntity.pmUserEntity.loginName,
QParamSexEntity.paramSexEntity.name.as("sexStr")
)
)
.from(QPmUserEntity.pmUserEntity)
.leftJoin(QParamSexEntity.paramSexEntity)
.on(QParamSexEntity.paramSexEntity.id.eq(QPmUserEntity.pmUserEntity.sex))
.where(where)
.orderBy(QPmUserEntity.pmUserEntity.loginName.asc())
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
return userModelList;
}
NamedQuery
所有@NamedQuery,@NamedNativeQuery, @SqlResultSetMapping只能注解在已有@Entity类上
不能注解在Model上
完整Entity Class
package cn.tiantianquan.springant.entity;
import cn.tiantianquan.springant.model.PmUserModel;
import com.fasterxml.jackson.annotation.JsonBackReference;
import com.fasterxml.jackson.annotation.JsonIgnore;
import javax.persistence.*;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.Objects;
@Entity
@Table(name = "pm_user")
@NamedNativeQueries({
@NamedNativeQuery(
name = "PmUserModelNativeQuery",
query = "select pu.id, true_name, login_name, pwd, sex, create_time, update_time, is_del,ps.name as sex_str from pm_user pu left join param_sex ps on ps.id = pu.sex",
resultSetMapping = "PmUserModelMapping"
)
})
@NamedQueries({
@NamedQuery(
name = "PmUserModelQuery",
query = "select new cn.tiantianquan.springant.model.PmUserModel(pu.trueName,pu.createTime) from PmUserEntity pu"
)
})
@SqlResultSetMappings({
@SqlResultSetMapping(
name = "PmUserModelMapping",
classes = {
@ConstructorResult(
targetClass = cn.tiantianquan.springant.model.PmUserModel.class,
columns = {
//ColumnResult name 相对应的是sql中的列名, type 是sql返回的默认type, columns 中ColumnResult的顺序是
//model构造函数中的参数顺序,与列名无关,不会自动映射
@ColumnResult(name = "id", type = String.class),
@ColumnResult(name = "true_name", type = String.class),
@ColumnResult(name = "login_name", type = String.class),
@ColumnResult(name = "pwd", type = String.class),
@ColumnResult(name = "sex", type = Integer.class),
@ColumnResult(name = "create_time", type = Date.class),
@ColumnResult(name = "update_time", type = Date.class),
@ColumnResult(name = "is_del", type = Integer.class),
@ColumnResult(name = "sex_str", type = String.class)
}
)
}
),
@SqlResultSetMapping(
name = "PmUserModelEntityMapping",
entities = {
@EntityResult(
entityClass = PmUserEntity.class,
fields = {
@FieldResult(name = "id", column="id"),
@FieldResult(name = "trueName", column = "true_name"),
@FieldResult(name = "loginName", column = "login_name"),
@FieldResult(name = "pwd", column ="pwd"),
@FieldResult(name = "sex", column = "sex"),
@FieldResult(name = "createTime", column = "create_time"),
@FieldResult(name = "updateTime", column = "update_time"),
@FieldResult(name = "isDel", column = "is_del")
}
)
},
columns = {
@ColumnResult(name = "sexStr", type = String.class)
}
)
})
public class PmUserEntity {
private String id;
private String trueName;
private String loginName;
private String pwd;
private Integer sex;
private LocalDateTime createTime;
private Timestamp updateTime;
private boolean isDel;
@Id
@Column(name = "id", nullable = false, length = 36)
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Basic
@Column(name = "true_name", nullable = false, length = 200)
public String getTrueName() {
return trueName;
}
public void setTrueName(String trueName) {
this.trueName = trueName;
}
@Basic
@Column(name = "login_name", nullable = false, length = 200)
public String getLoginName() {
return loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}
@Basic
@Column(name = "pwd", nullable = false, length = 200)
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
@Basic
@Column(name = "sex", nullable = true)
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
@Basic
@Column(name = "create_time", nullable = false)
public LocalDateTime getCreateTime() {
return createTime;
}
public void setCreateTime(LocalDateTime createTime) {
this.createTime = createTime;
}
@Basic
@Column(name = "update_time", nullable = false)
public Timestamp getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Timestamp updateTime) {
this.updateTime = updateTime;
}
@Basic
@Column(name = "is_del", nullable = false)
public boolean getIsDel() {
return isDel;
}
public void setIsDel(boolean isDel) {
this.isDel = isDel;
}
}
完整Model Class
package cn.tiantianquan.springant.model;
import cn.tiantianquan.springant.entity.PmUserEntity;
import lombok.AllArgsConstructor;
import lombok.Data;
import javax.persistence.*;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.Date;
@Data
public class PmUserModel {
public PmUserModel() {
}
public PmUserModel(String id, String trueName, String loginName, String pwd, Integer sex, Date createTime, Date updateTime, Integer isDel, String sexStr) {
this.id = id;
this.trueName = trueName;
this.loginName = loginName;
this.pwd = pwd;
this.sex = sex;
this.createTime = new Timestamp(createTime.getTime()).toLocalDateTime();
this.updateTime = new Timestamp(updateTime.getTime()).toLocalDateTime();
this.sexStr = sexStr;
this.isDel = isDel == 1;
}
public PmUserModel(String trueName, LocalDateTime createTime) {
this.trueName = trueName;
this.createTime = createTime;
}
private String id;
private String trueName;
private String loginName;
private String pwd;
private Integer sex;
private LocalDateTime createTime;
private LocalDateTime updateTime;
private boolean isDel;
private String sexStr;
}
-
NamedNativeQueries 原生SQL查询
注解形式 NamedNativeQueries->NamedNativeQuery
最后需要绑定结果映射resultSetMapping
//查询语句
@NamedNativeQueries({
@NamedNativeQuery(
name = "PmUserModelNativeQuery",
query = "select pu.id, true_name, login_name, pwd, sex, create_time, update_time, is_del,ps.name as sex_str from pm_user pu left join param_sex ps on ps.id = pu.sex",
resultSetMapping = "PmUserModelMapping"
)
})
-
构造器映射:
- 适用于装载非Entity类,如Model类
- 构造器 ColumnResult name 相对应的是sql中的列名, type 是sql返回的默认type
- columns 中ColumnResult的顺序是model构造函数中的参数顺序,与列名无关,不会自动映射
- 构造器映射model类中一定要有相应的构造函数
//构造器映射
@SqlResultSetMapping(
name = "PmUserModelMapping",
classes = {
//构造器映射,适用于装载非Entity类,如Model类
@ConstructorResult(
targetClass = cn.tiantianquan.springant.model.PmUserModel.class,
columns = {
//ColumnResult name 相对应的是sql中的列名, type 是sql返回的默认type, columns 中ColumnResult的顺序是
//model构造函数中的参数顺序,与列名无关,不会自动映射
@ColumnResult(name = "id", type = String.class),
@ColumnResult(name = "true_name", type = String.class),
@ColumnResult(name = "login_name", type = String.class),
@ColumnResult(name = "pwd", type = String.class),
@ColumnResult(name = "sex", type = Integer.class),
@ColumnResult(name = "create_time", type = Date.class),
@ColumnResult(name = "update_time", type = Date.class),
@ColumnResult(name = "is_del", type = Integer.class),
@ColumnResult(name = "sex_str", type = String.class)
}
)
}
)
-
实体映射:
- 适用于装载Entity类
1.@FieldResult 中 name 对应属性名, column 对应列名 - 最后的 columns 参数, 指的是与实体或模型都无关的字段, 整个返回对象为
List<Object[]>
需要自己手动取出相应内容,具体代码见 (代码中拼接sql 并指定setmap的执行方式) (构造器也适用)
//实体映射
@SqlResultSetMapping(
name = "PmUserModelEntityMapping",
entities = {
@EntityResult(
entityClass = PmUserEntity.class,
fields = {
@FieldResult(name = "id", column="id"),
@FieldResult(name = "trueName", column = "true_name"),
@FieldResult(name = "loginName", column = "login_name"),
@FieldResult(name = "pwd", column ="pwd"),
@FieldResult(name = "sex", column = "sex"),
@FieldResult(name = "createTime", column = "create_time"),
@FieldResult(name = "updateTime", column = "update_time"),
@FieldResult(name = "isDel", column = "is_del")
}
)
},
columns = {
@ColumnResult(name = "sexStr", type = String.class)
}
)
-
执行
一般执行
Query q = em.createNamedQuery("PmUserModelNativeQuery");
return q.getResultList();
在代码中拼接sql 并指定setmap的执行方式
Query q = em.createNativeQuery("select pu.id, true_name, login_name, pwd, sex, create_time, update_time, is_del,ps.name as sexStr from pm_user pu left join param_sex ps on ps.id = pu.sex", "PmUserModelEntityMapping");
List<Object[]> resultList = q.setFirstResult(1).setMaxResults(2).getResultList();
List<PmUserModel> pmUserModelList = resultList.stream().map(i -> {
PmUserEntity entity = (PmUserEntity) i[0];
PmUserModel model = new PmUserModel();
model.setCreateTime(entity.getCreateTime());
model.setId(entity.getId());
model.setSexStr((String) i[1]);
return model;
}).collect(Collectors.toList());
return pmUserModelList ;
createNamedQuery 可追加 setFirstResult起始数据行,setMaxResults最大数据数,来进行分页
JPQL HQL
注意构造器生成时, 一定要把包名写全
@NamedQuery(
name = "PmUserModelQuery",
query = "select new cn.tiantianquan.springant.model.PmUserModel(pu.trueName,pu.createTime) from PmUserEntity pu"
)
总结
SQL复杂度从低到高
基础查询 < QueryDSL JPA < HQL = QueryDSL < NamedQueries < NamedNativeQueries
根据复杂度选择相应的查询方式
