JPA(Hibernate)+QueryDSL+NamedQue

作者: A_1341 | 来源:发表于2018-08-01 13:42 被阅读185次

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
根据复杂度选择相应的查询方式

JPA(Hibernate)+QueryDSL+NamedQue

JPA

Entity初始化

基础查询

QueryDSL

NamedQuery

NamedNativeQueries 原生SQL查询

构造器映射:

实体映射:

执行

JPQL HQL

总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

springboot