复杂查询情况分析
在遇到复杂查询时，尤其是需要动态传递查询参数时，jpa为我们提供给可一下几种方式可以应对上述情况：
Search
Search 封装分页，扩展了findAll()函数.
Eg:
@Override
public Page<Student> search(finalStudent student, PageInfo page) {
return studentRepository.findAll(new
@Override
public
Predicate stuNameLike = null;
if(null
stuNameLike =cb.like(root.<String> get("name"), "%" +student.getName() + "%");
}
null;
if(null!= student && null
clazzNameLike = cb.like(root.<String>get("clazz").<String> get("name"), "%" +student.getClazz().getName() + "%");
}
if(null!= stuNameLike) query.where(stuNameLike);
if(null
returnnull;
}
new PageRequest(page.getPage() - 1,page.getLimit(), new
}
@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）
注：需要注意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();
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
