关于jpa中复杂查询的解决方案

参考网址：http://blog.csdn.net/mendeliangyang/article/details/52366799

 复杂查询情况分析

在遇到复杂查询时，尤其是需要动态传递查询参数时，jpa为我们提供给可一下几种方式可以应对上述情况：

 Search

Search 封装分页，扩展了findAll()函数.

 Eg:

@Override

 public Page<Student> search(finalStudent student, PageInfo page) {

    return studentRepository.findAll(new Specification<Student>() {

       @Override

        public PredicatetoPredicate(Root<Student> root, CriteriaQuery<?> query,CriteriaBuilder cb) {

               

             Predicate stuNameLike = null;

              if(null != student &&!StringUtils.isEmpty(student.getName())) {

                  stuNameLike =cb.like(root.<String> get("name"), "%" +student.getName() + "%");

              }

                

            Predicate clazzNameLike = null;

           if(null!= student && null !=student.getClazz() &&!StringUtils.isEmpty(student.getClazz().getName())) {

            clazzNameLike = cb.like(root.<String>get("clazz").<String> get("name"), "%" +student.getClazz().getName() + "%");

               }

               

            if(null!= stuNameLike) query.where(stuNameLike);

              if(null != clazzNameLike) query.where(clazzNameLike);

              returnnull;

         }

      }, new PageRequest(page.getPage() - 1,page.getLimit(), new Sort(Direction.DESC,page.getSortName())));

 }

 

@Query 注解自定义的查询

Eg:

@Query("select a.labelIdfrom MbLabelRelEntity a where a.mbId in ?1 group by a.labelId")

List<String> getLabelIdList(List<String> mbIds);

 

@Modifying

@Query("update User bean set bean.status=?2 wherebean.id=?1")

publicvoid checkUser(Integer id, int status);

注：

1、当注解的sql语句涉及更新表操作是，需要添加@Modifying注解

2、其中 ?1、?2表示第几个参数

 

@Query(value = "select name,author,pricefrom Book b where b.name like %:name%")

List<Book>findByNameMatch(@Param("name") String name);

 

动态查询

JPA提供的API中，动态查询大概有这么一些方法，

CreateNameQuery

@NamedNativeQueries({  

   @NamedNativeQuery(  

       name = "getNativeNutShellInfo",   //需要调用的name

       query = "{call cp_syslogin_web(?,?,?)}",   //存储过程,返回的List字段对应的值@EntityResult此处填充实体的属性值

       resultSetMapping = "ReturnColumnEntityList" 

   )  

})  

 

@SqlResultSetMappings({  

   @SqlResultSetMapping(  

       name = "ReturnColumnEntityList",  

       entities = {

         @EntityResult(entityClass = NutShellInfo.class,fields={  //返回的List字段对应的值此处填充实体的属性值

          @FieldResult(name = "aguid", column = "aguid"),

          @FieldResult(name = "user", column = "htkusr"),

          @FieldResult(name = "qty", column = "qtynow"),

          @FieldResult(name = "registered", column = "breg"),

          @FieldResult(name = "hint", column = "hint"),

          @FieldResult(name = "ver", column = "ver"),

          @FieldResult(name = "overQty", column = "boverqty"),

          @FieldResult(name = "timeout", column = "btimeout"),

          @FieldResult(name = "host", column = "htkwww")

         })

       } 

   )  

})

 @Entity

public class NutShellInfo {

 @Id

 private String aguid;

 @Column(name = "htkusr")

 private String user;

 @Column(name = "qtynow")

 private double qty;

 @Column(name = "breg")

 private boolean registered;

 private String hint;

 private String ver;

 @Column(name = "boverqty")

 private boolean overQty;

 @Column(name = "btimeout")

 private boolean timeout;

 @Column(name = "htkwww")

 private String host;

 @Transient

 private String password;//这个字段不对数据库操作

 

 /***setterand getter**/

 }

 =========================dao=========================

 @Service

 publicclass PubBaseDaoImpl<T> implements PubBaseDao<T> {

 private EntityManager em = null;

 

 @PersistenceContext

 public void setEm(EntityManager em) {

 this.em = em;

 }

  @Override

 public List<T> qryNamedQuery(StringnativeName,Object[] params) {

  Queryq = em.createNamedQuery(nativeName);

 setQueryParams(q, params);

 return q.getResultList();

 }

 

 public void setQueryParams(Query query,Object[] params) {

  try{

   if(params != null && params.length > 0) {

   for (int i = 0; i < params.length; i++) {

    query.setParameter(i + 1, params[i]);

    }

   }

  }catch (Exception e) {

  e.printStackTrace();

  }

 }

 =================services===================================

 @Override

 public void qryInfo(String userId, StringmachineCode) {

 List<NutShellInfo> list =baseDao.qryNamedQuery("getNativeNutShellInfo",new Object[] { userId,machineCode, 0 });

 

 }

  

JPA 原生态SQL 的复杂查询之createNamedQuery,select* from查询，返回的List字段对应的填充实体

============实体类==========================

@NamedNativeQueries({  

   @NamedNativeQuery(  

       name = "getNativeNutShellInfo",   //需要调用的name

       query = "select * from NutShellInfo where aguid=? and  htkusr=? and btimeout=? ",   //存储过程,返回的List字段对应的值@EntityResult此处填充实体的属性值

       resultSetMapping = "ReturnColumnEntityList" 

   )  

})  

 

@SqlResultSetMappings({  

   @SqlResultSetMapping(  

       name = "ReturnColumnEntityList",  

       entities = {

         @EntityResult(entityClass = NutShellInfo.class,fields={  //返回的List字段对应的值此处填充实体的属性值

          @FieldResult(name = "aguid", column = "id"),

          @FieldResult(name = "user", column = "htkusr"),

          @FieldResult(name = "qty", column = "qtynow"),

          @FieldResult(name = "registered", column = "breg"),

          @FieldResult(name = "hint", column = "hint"),

          @FieldResult(name = "ver", column = "ver"),

          @FieldResult(name = "overQty", column = "boverqty"),

          @FieldResult(name = "timeout", column = "btimeout"),

          @FieldResult(name = "host", column = "htkwww")

         }) 

       } 

   )  

})

 

@Entity

public class NutShellInfo {

 @Id

 private String aguid;

 @Column(name = "htkusr")

 private String user;

 @Column(name = "qtynow")

 private double qty;

 @Column(name = "breg")

 private boolean registered;

 private String hint;

 private String ver;

 @Column(name = "boverqty")

 private boolean overQty;

 @Column(name = "btimeout")

 private boolean timeout;

 @Column(name = "htkwww")

 private String host;

 @Transient

 private String password;//这个字段不对数据库操作 

/****setter and getter*****/ 

 }

 

=========================dao=========================

 @Service

 public class PubBaseDaoImpl<T>implements PubBaseDao<T> {

 private EntityManager em = null;

 

 @PersistenceContext

 public void setEm(EntityManager em) {

 this.em = em;

 }

 

 @Override

 public List<T> qryNamedQuery(StringnativeName,Object[] params) {

 Query q = em.createNamedQuery(nativeName);

 setQueryParams(q, params);

 return q.getResultList();

 }

 

 public void setQueryParams(Query query,Object[] params) {

  try{

   if(params != null && params.length > 0) {

   for (int i = 0; i < params.length; i++) {

    query.setParameter(i + 1, params[i]);

    }

   }

  }catch (Exception e) {

  e.printStackTrace();

  }

 }

 

=================services===================================

 @Override

 public void qryInfo(String userId, StringmachineCode) {

 List<NutShellInfo> list =baseDao.qryNamedQuery("getNativeNutShellInfo",new Object[] { userId,machineCode, 0 });

  } 

 

JPA 原生态SQL 的复杂查询之createNamedQuery,selectxxx,xxx from查询，返回的List字段对应的填充实体

============实体类==========================

@NamedNativeQueries({  

   @NamedNativeQuery(  

       name = "getNativeNutShellInfo",   //需要调用的name

       query = "select aguid,htkusr from NutShellInfo where aguid=?and  htkusr=? and btimeout=? ",   //存储过程,返回的List字段对应的值@EntityResult此处填充实体的属性值

       resultSetMapping = "ReturnColumnEntityList" 

   )  

})  

 

@SqlResultSetMappings({  

   @SqlResultSetMapping(  

       name = "ReturnColumnEntityList",  

       entities = {}

       columns = {

    @ColumnResult(name = "aguid"),  

           @ColumnResult(name = "htkusr"),  

       }

   )  

})

 

@Entity

public class NutShellInfo {

 @Id

 private String aguid;

 @Column(name = "htkusr")

 private String user;

 @Column(name = "qtynow")

 private double qty;

 @Column(name = "breg")

 private boolean registered;

 private String hint;

 private String ver;

 @Column(name = "boverqty")

 private boolean overQty;

 @Column(name = "btimeout")

 privateboolean timeout;

 @Column(name = "htkwww")

 private String host;

 @Transient

 private String password;//这个字段不对数据库操作

/******setter  and getter********/

 }

 

=========================dao=========================

 @Service

 public class PubBaseDaoImpl<T>implements PubBaseDao<T> {

 private EntityManager em = null;

 

 @PersistenceContext

 public void setEm(EntityManager em) {

 this.em = em;

 }

 

 @Override

 public List<T> qryNamedQuery(StringnativeName,Object[] params) {

 Query q = em.createNamedQuery(nativeName);

 setQueryParams(q, params);

 return q.getResultList();

 }

 

 public void setQueryParams(Query query,Object[] params) {

  try{

   if(params != null && params.length > 0) {

   for (int i = 0; i < params.length; i++) {

     query.setParameter(i+ 1, params[i]);

    }

   }

  }catch (Exception e) {

  e.printStackTrace();

  }

 }

 

=================services===================================

 @Override

 public void qryInfo(String userId, StringmachineCode) {

 List<NutShellInfo> list =baseDao.qryNamedQuery("getNativeNutShellInfo",new Object[] { userId,machineCode, 0 });

  } 

 

createNativeQuery

执行原生的SQL语句. 但当我们查询结果没有对应实体类时，query.getResultList()返回的是一个List.

为了解决上述问题，所以提供了

createNativeQuery(String sqlString, Class resultClass)

createNativeQuery(String sqlString,String resultSetMapping)

 

第一种方式

一般针对复杂（多表）查询，但是返回结果为单张表信息，可以用定义的javaBean实体进行sql结果的格式化。（未进行测试）

第二种方式（@SqlResultSetMapping 或 @SqlResultSetMappings）：

针对复杂多表查询，返回结果为多表信息或几张表中摘取的部分字段，或经过聚合函数计算后的字段信息，在javabean格式化时，可以定义resultSetMapping 来进行接受。（@SqlResultSetMapping 或@SqlResultSetMappings）

Eg:

String sql=” select * from   ”

List<ComparativeResultEntity> list=em.createNativeQuery(sql, “comparativeResult")

.getResultList();

@Entity

@SqlResultSetMapping(name = "comparativeResult",

       entities = { @EntityResult(entityClass = ComparativeResultEntity.class,

       fields = {

                   @FieldResult(name = "id", column = "gmonth"),

                   @FieldResult(name = "month", column = "gmonth"),

                   @FieldResult(name = "thisMoney", column = "thisMoney"),

                   @FieldResult(name = "thisAmount", column = "thisAmount"),

                   @FieldResult(name = "lastMoney", column = "lastMoney"),

                   @FieldResult(name = "lastAmount", column = "lastAmount"),

                   @FieldResult(name = "moneyRatio", column = "moneyRatio"),

                   @FieldResult(name = "amountRatio", column = "amountRatio")

                   })

       }

)

publicclass ComparativeResultEntity implements Serializable {

 

         privatestaticfinallongserialVersionUID =5852089358217463285L;

         @Id

         private String id;

     private Integer thisAmount;

         private Double thisMoney;

         private Integer lastAmount;

         private Double lastMoney;

         private String month;

         private Double moneyRatio;

/****setter andgetter****/

}

 

第三种方式（setResultTransformer）

参考网址：http://www.cnblogs.com/wucongyun/p/6730582.html

注：需要注意hibernate的版本

String sql =”select * from  table”;

Query nativeQuery= em.createNativeQuery(sql);

nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

List list = nativeQuery.getResultList();

最后

以上就是自由胡萝卜为你收集整理的关于jpa中复杂查询的解决方案的全部内容，希望文章能够帮你解决关于jpa中复杂查询的解决方案所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。