当使用JPA作数据库持久层框架时,进行多表关联动态分页查询时,并没有mybatis写Sql时用起来那么方便。
进行单表动态分页查询时,可以查看此处
多表关联查询时分为SQL和HQL两种查询方式。
在开发过程中当实体类的字段和数据库字段一样时(这里指数据库字段没下划线),可以使用SQL进行查询。当数据库是下划线分割,实体类时驼峰命名时,建议使用HQL进行查询(我在开发中,使用sql,会导致结果不能进行map映射,不知道你们是否有这问题)。如有问题请指正。
创建写sql或hql的类
public class QueryBusinessDaoImpl {
@PersistenceContext
private EntityManager em;
}
下面类中的方法:
SQL进行查询
public List<SealBillOutVO> queryMyList(int pageNumber, int pageSize, String sealShortName, String fusePlace, String fuseStartTime, String fuseEndTime, String fcreateUserAccount) {
HibernateEntityManager hEntityManager = (HibernateEntityManager) em;
Session session = hEntityManager.getSession();
try {
String hql = "select distinct S.ID, S.FUSEPLACE ,S.FUSECAUSE, S.FCREATETIME ,s.applyDate, S.FSTATE, S.FRETURNDATE from B_SEALBILL_OUT S, B_SEALRECORD R, B_SEAL BS "
+ "where S.ID = R.SEALBILLID AND R.SEALID = BS.ID AND s.fstate > -1 and BS.SEALSHORTNAME LIKE :sealShortName "
+ "and S.FUSEPLACE LIKE :fusePlace "
+ "and S.FCREATEUSERACCOUNT = :fcreateUserAccount ";
if (!StringX.nullity(fuseStartTime)) {
hql += "and TO_CHAR(S.applyDate, 'yyyy-MM-dd') >= :fuseStartTime ";
}
if (!StringX.nullity(fuseEndTime)) {
hql += "and TO_CHAR(S.applyDate, 'yyyy-MM-dd') < :fuseEndTime ";
}
hql += " order by S.FCREATETIME DESC";
Query query = session.createSQLQuery(hql).addScalar("id", StandardBasicTypes.STRING)
.addScalar("fusePlace", StandardBasicTypes.STRING)
.addScalar("fuseCause", StandardBasicTypes.STRING)
.addScalar("freturnDate", StandardBasicTypes.DATE)
.addScalar("fcreateTime", StandardBasicTypes.DATE)
.addScalar("applyDate", StandardBasicTypes.TIMESTAMP)
.addScalar("fstate", StandardBasicTypes.INTEGER)
.setString("sealShortName", sealShortName)
.setString("fusePlace", fusePlace)
.setString("fcreateUserAccount", fcreateUserAccount)
.setFirstResult((pageNumber - 1) * pageSize)
.setMaxResults(pageSize);
if (!StringX.nullity(fuseStartTime)) {
query.setString("fuseStartTime", fuseStartTime);
}
if (!StringX.nullity(fuseEndTime)) {
query.setString("fuseEndTime", fuseEndTime);
}
query.setResultTransformer(Transformers.aliasToBean(SealBillOutVO.class));
return query.list();
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
hEntityManager.close();
}
}
HQL进行查询
public List<FlowBusinessMapEntity> getBusinessType(int pageNumber, int pageSize, String businessNo, String businessType, List<String> state, String operator, Date startDate, Date endDate) {
HibernateEntityManager hEntityManager = (HibernateEntityManager) em;
Session session = hEntityManager.getSession();
try {
String hql = "select f from FlowBusinessMapEntity f WHERE f.businessType not in ('BGLY','BGCG','BGRK') ";
if (!StringX.nullity(businessNo)) {
hql += " and f.businessNo = :businessNo";
}
if (!StringX.nullity(businessType)) {
hql += " and f.businessType = :businessType";
}
if (state != null && state.size() > 0) {
hql += " and f.state IN :state ";
}
if (!StringX.nullity(operator)) {
hql += " and f.operator = :operator";
}
if (startDate != null ) {
hql += " and f.applyDate >= :startDate";
}
if (endDate != null) {
hql += " and f.applyDate < :endDate";
}
TypedQuery<FlowBusinessMapEntity> query = em.createQuery(hql, FlowBusinessMapEntity.class)
.setFirstResult((pageNumber - 1) * pageSize)
.setMaxResults(pageSize);
if (!StringX.nullity(businessNo)) {
query.setParameter("businessNo", businessNo);
}
if (!StringX.nullity(operator)) {
query.setParameter("operator", operator);
}
if (!StringX.nullity(businessType)) {
query.setParameter("businessType", businessType);
}
if (state != null && state.size() > 0) {
query.setParameter("state", state);
}
if (startDate != null ) {
query.setParameter("startDate", startDate);
}
if (endDate != null) {
query.setParameter("endDate", endDate);
}
// query.setResultTransformer(Transformers.aliasToBean(FlowBusinessMapEntity.class));
List<FlowBusinessMapEntity> resultList = query.getResultList();
return resultList;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
hEntityManager.close();
}
}
对比:
- HQL的from后是对应的实体名,建议为每个实体起一个别名。SQL就是平常的写法。
- 执行SQL语句是通过EntityManager 获取Session对象,然后执行createSQLQuery方法,参数为SQL语句,返回结果为SQLQuery对象。SQLQuery是Query对象的子类。
- 执行HQL语句时,是直接通过EntityManager执行createQuery方法,传入参数为hql语句和对应的实体类的Class,返回结果为TypedQuery对象,此类也为Query类的子类。
- 执行SQL的条件参数设置,SQLQuery中的addScalar方法的作用是给查询结果的字段指定类型。setString是Query中的方法,作用是给相应的字段设置对应的参数。还有一些其他方法setParameterList设置list类型参数,setDate设置日期类型参数。
- 执行HQL时的条件参数设置,只能通过TypedQuery中的setParameter方法来设置参数。
- HQL结果集,通过TypedQuery中的getResultList方法,获取结果集。
- 在获取SQL的结果集之前,还需要进行返回结果的对象映射设置。如下:
query.setResultTransformer(Transformers.aliasToBean(SealBillOutVO.class));
再通过list加粗样式方法,获取结果集对象。
之前没用过JPA,这样的写法刚开始很不习惯,也会遇到很多问题,在这里记录一下,有问题望指出.