查询自定义VO对象的sql

最新推荐文章于 2023-04-26 17:43:29 发布

weixin_34365417

最新推荐文章于 2023-04-26 17:43:29 发布

阅读量238

点赞数

文章标签： java

原文链接：http://www.cnblogs.com/ms-grf/p/7667580.html

版权

查询的方法

public List<DrugPrescMasterVO> findHerbalDrugReturn(String patientId, String prescNo, String storageCode, Integer prescSource, Integer displayTime,Integer isYfTy) {
    StringBuffer sb = new StringBuffer();
    sb.append("select distinct ");
    sb.append("dpm.*, ");
    sb.append("dd.dept_name as  orderd_dept_name, ");
    sb.append("odm.USER_DESCRIPTION  as  USER_DESCRIPTION, ");
    sb.append("odm.TCM_DECOCTION_INDICATOR as TCM_DECOCTION_INDICATOR, ");
    sb.append("odm.PROPORTION_CONCENTRATION as PROPORTION_CONCENTRATION ");
    sb.append("from drug_presc_master dpm ");
    sb.append("inner join drug_presc_detail dpd on dpm.presc_id = dpm.presc_id ");
    sb.append("and dpd.PRESC_INDICATOR = dpm.PRESC_INDICATOR ");
    sb.append("and dpd.PRESC_DATE = dpm.PRESC_DATE ");
    sb.append("left join dept_dict dd on dd.dept_code = dpm.ORDERED_BY ");
    sb.append("left join orders_drug_master odm on odm.presc_no = dpm.presc_no ");
    sb.append("where dpm.DISPENSARY = '" + storageCode + "' ");
    if (!Strings.isNullOrEmpty(patientId)) {
        sb.append("and dpm.patient_id = '" + patientId + "' ");
    }
    if (!Strings.isNullOrEmpty(prescNo)) {
        sb.append("and dpm.presc_no = '" + prescNo + "' ");
    }
    if (prescSource != null) {
        sb.append("and dpm.presc_source = '" + prescSource + "' ");
    }
    if(isYfTy!=null && isYfTy.intValue()==1){//药房退药页面发起
        sb.append("and dpm.doctor_return_repetition >0 "); //药房不主动发起退药，只查询由医生站发起的
    }
    sb.append("and dpm.PRESC_INDICATOR = '1' ");
    sb.append("and dpm.PRESC_TYPE = '1' ");
    sb.append("and dpm.INVALID_INDICATOR = '0' ");
    sb.append("and (dpd.DISPENSING_FLAG <> -1 or dpd.DISPENSING_FLAG is null) ");
    if (null != displayTime && displayTime > 0) {
        Calendar now = Calendar.getInstance();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        now.setTime(new Date());
        now.add(Calendar.DATE, 1);
        now.set(Calendar.HOUR_OF_DAY, 0);
        now.set(Calendar.MINUTE, 0);
        now.set(Calendar.SECOND, 0);
        Calendar beginDate = Calendar.getInstance();
        beginDate.setTime(new Date());
        beginDate.add(Calendar.DATE, -displayTime + 1);
        beginDate.set(Calendar.HOUR_OF_DAY, 0);
        beginDate.set(Calendar.MINUTE, 0);
        beginDate.set(Calendar.SECOND, 0);
        sb.append(" and dpm.PRESC_DATE >= TO_DATE('").append(format.format(beginDate.getTime())).append("', 'yyyyMMddHH24MIss')");
        sb.append(" and dpm.PRESC_DATE < TO_DATE('").append(format.format(now.getTime())).append("', 'yyyyMMddHH24MIss')");
    }
    sb.append("order by dpm.presc_date desc ");
    Query query = createNativeQuery(sb.toString());
    query.unwrap(SQLQuery.class).setResultTransformer(new AliasToBeanResultTransformer(DrugPrescMasterVO.class));
    List<DrugPrescMasterVO> drugPrescMasterVOList = query.getResultList();
    return drugPrescMasterVOList;
}
查询的sql

select distinct dpm.*,
                dd.dept_name                 as orderd_dept_name,
                odm.USER_DESCRIPTION         as USER_DESCRIPTION,
                odm.TCM_DECOCTION_INDICATOR  as TCM_DECOCTION_INDICATOR,
                odm.PROPORTION_CONCENTRATION as PROPORTION_CONCENTRATION
  from drug_presc_master dpm
 inner join drug_presc_detail dpd
    on dpm.presc_id = dpm.presc_id
   and dpd.PRESC_INDICATOR = dpm.PRESC_INDICATOR
   and dpd.PRESC_DATE = dpm.PRESC_DATE
  left join dept_dict dd
    on dd.dept_code = dpm.ORDERED_BY
  left join orders_drug_master odm
    on odm.presc_no = dpm.presc_no
 where dpm.DISPENSARY = 'A02030090200'
   and dpm.patient_id = '10000141'
   and dpm.presc_source = '0'
   and dpm.PRESC_INDICATOR = '1'
   and dpm.PRESC_TYPE = '1'
   and dpm.INVALID_INDICATOR = '0'
   and (dpd.DISPENSING_FLAG <> -1 or dpd.DISPENSING_FLAG is null)
   and dpm.PRESC_DATE >= TO_DATE('20171012000000', 'yyyyMMddHH24MIss')
   and dpm.PRESC_DATE < TO_DATE('20171015000000', 'yyyyMMddHH24MIss')
 order by dpm.presc_date desc;

sql解释在http://www.cnblogs.com/ms-grf/p/7667316.html

Query 所属的jar：hibernate-jpa-2.1-api-1.0.0.Final.jar

SQLQuery 所属的jar：hibernate-core-5.0.1.Final.jar

上面红色的相关内容

DrugPrescMasterVO.class

package com.heren.his.domain.vo.drug;

import com.heren.his.domain.entity.drug.DrugPrescDetail;

import javax.persistence.Column;
import java.util.Date;
import java.util.List;


public class DrugPrescMasterVO {

    private String prescId;

    private String prescNo;

    private Integer prescIndicator;

    private Date prescDate;

    private String dispensary;

    private String patientId;

    private String name;

    private String identityName;

    private String chargeType;

    private String unitInContract;

    private Integer prescType;

    private String prescAttr;

    private Integer prescSource;

    private Integer repetition;

    private Double costs;

    private Double payments;

    private String orderedBy;

    private String prescribedBy;

    private String prescribedName;

    private String deployEmpId;

    private String deployEmpName;

    private String dispensingEmpId;

    private String dispensingEmpName;

    private Integer invalidIndicator;

    private String memo;

    private String orderedByName;

    private String namePhonetic;

    //流水号
    private String visitNo;
    //开单科室名称
    private String orderdDeptName;
    //用法描述
    private String userDescription;
    //中草药配比浓度
    private String proportionConcentration;
    //代煎标志
    private Integer tcmDecoctionIndicator;
    //本地科室
    private String storageCode;
    //新剂数
    private Integer newRepetition;
    //医生退药付数
    private Integer doctorReturnRepetition;
    //处方特定类型
    private Integer prescSpecType;
    //强制代煎药标识（望江山模式）
    private Integer forceTcmDecoction=0;
    // 护理单元名称
    private String wardName;
    // 床位号
    private Integer bedNo;
    // 床位名称
    private String bedLabel;
    // 住院号
    private String inpNo;
    public DrugPrescMasterVO(String prescNo, Integer prescIndicator, Date prescDate, String dispensary, String patientId, String name, String identityName, String chargeType, String unitInContract, Integer prescType, String prescAttr, Integer prescSource, Integer repetition, Double costs, Double payments, String orderedBy, String prescribedBy, String prescribedName, String deployEmpId, String deployEmpName, String dispensingEmpId, String dispensingEmpName, Integer invalidIndicator, String memo, String orderedByName, String namePhonetic) {
        this.prescNo = prescNo;
        this.prescIndicator = prescIndicator;
        this.prescDate = prescDate;
        this.dispensary = dispensary;
        this.patientId = patientId;
        this.name = name;
        this.identityName = identityName;
        this.chargeType = chargeType;
        this.unitInContract = unitInContract;
        this.prescType = prescType;
        this.prescAttr = prescAttr;
        this.prescSource = prescSource;
        this.repetition = repetition;
        this.costs = costs;
        this.payments = payments;
        this.orderedBy = orderedBy;
        this.prescribedBy = prescribedBy;
        this.prescribedName = prescribedName;
        this.deployEmpId = deployEmpId;
        this.deployEmpName = deployEmpName;
        this.dispensingEmpId = dispensingEmpId;
        this.dispensingEmpName = dispensingEmpName;
        this.invalidIndicator = invalidIndicator;
        this.memo = memo;
        this.orderedByName = orderedByName;
        this.namePhonetic = namePhonetic;
    }

    public DrugPrescMasterVO(String prescId, String prescNo, Integer prescIndicator, Date prescDate, String dispensary, String patientId, String name, String identityName, String chargeType, String unitInContract, Integer prescType, String prescAttr, Integer prescSource, Integer repetition, Double costs, Double payments, String orderedBy, String prescribedBy, String prescribedName, String deployEmpId, String deployEmpName, String dispensingEmpId, String dispensingEmpName, Integer invalidIndicator, String memo, String orderedByName, String namePhonetic) {
        this.prescId = prescId;
        this.prescNo = prescNo;
        this.prescIndicator = prescIndicator;
        this.prescDate = prescDate;
        this.dispensary = dispensary;
        this.patientId = patientId;
        this.name = name;
        this.identityName = identityName;
        this.chargeType = chargeType;
        this.unitInContract = unitInContract;
        this.prescType = prescType;
        this.prescAttr = prescAttr;
        this.prescSource = prescSource;
        this.repetition = repetition;
        this.costs = costs;
        this.payments = payments;
        this.orderedBy = orderedBy;
        this.prescribedBy = prescribedBy;
        this.prescribedName = prescribedName;
        this.deployEmpId = deployEmpId;
        this.deployEmpName = deployEmpName;
        this.dispensingEmpId = dispensingEmpId;
        this.dispensingEmpName = dispensingEmpName;
        this.invalidIndicator = invalidIndicator;
        this.memo = memo;
        this.orderedByName = orderedByName;
        this.namePhonetic = namePhonetic;
    }

    public DrugPrescMasterVO() {

    }

    public String getPrescId() {
        return prescId;
    }

    public void setPrescId(String prescId) {
        this.prescId = prescId;
    }

    public String getPrescNo() {
        return prescNo;
    }

    public void setPrescNo(String prescNo) {
        this.prescNo = prescNo;
    }

    public Integer getPrescIndicator() {
        return prescIndicator;
    }

    public void setPrescIndicator(Integer prescIndicator) {
        this.prescIndicator = prescIndicator;
    }

    public Date getPrescDate() {
        return prescDate;
    }

    public void setPrescDate(Date prescDate) {
        this.prescDate = prescDate;
    }

    public String getDispensary() {
        return dispensary;
    }

    public void setDispensary(String dispensary) {
        this.dispensary = dispensary;
    }

    public String getPatientId() {
        return patientId;
    }

    public void setPatientId(String patientId) {
        this.patientId = patientId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getIdentityName() {
        return identityName;
    }

    public void setIdentityName(String identityName) {
        this.identityName = identityName;
    }

    public String getChargeType() {
        return chargeType;
    }

    public void setChargeType(String chargeType) {
        this.chargeType = chargeType;
    }

    public String getUnitInContract() {
        return unitInContract;
    }

    public void setUnitInContract(String unitInContract) {
        this.unitInContract = unitInContract;
    }

    public Integer getPrescType() {
        return prescType;
    }

    public void setPrescType(Integer prescType) {
        this.prescType = prescType;
    }

    public String getPrescAttr() {
        return prescAttr;
    }

    public void setPrescAttr(String prescAttr) {
        this.prescAttr = prescAttr;
    }

    public Integer getPrescSource() {
        return prescSource;
    }

    public void setPrescSource(Integer prescSource) {
        this.prescSource = prescSource;
    }

    public Integer getRepetition() {
        return repetition;
    }

    public void setRepetition(Integer repetition) {
        this.repetition = repetition;
    }

    public Double getCosts() {
        return costs;
    }

    public void setCosts(Double costs) {
        this.costs = costs;
    }

    public Double getPayments() {
        return payments;
    }

    public void setPayments(Double payments) {
        this.payments = payments;
    }

    public String getOrderedBy() {
        return orderedBy;
    }

    public void setOrderedBy(String orderedBy) {
        this.orderedBy = orderedBy;
    }

    public String getPrescribedBy() {
        return prescribedBy;
    }

    public void setPrescribedBy(String prescribedBy) {
        this.prescribedBy = prescribedBy;
    }

    public String getPrescribedName() {
        return prescribedName;
    }

    public void setPrescribedName(String prescribedName) {
        this.prescribedName = prescribedName;
    }

    public String getDeployEmpId() {
        return deployEmpId;
    }

    public void setDeployEmpId(String deployEmpId) {
        this.deployEmpId = deployEmpId;
    }

    public String getDeployEmpName() {
        return deployEmpName;
    }

    public void setDeployEmpName(String deployEmpName) {
        this.deployEmpName = deployEmpName;
    }

    public String getDispensingEmpId() {
        return dispensingEmpId;
    }

    public void setDispensingEmpId(String dispensingEmpId) {
        this.dispensingEmpId = dispensingEmpId;
    }

    public String getDispensingEmpName() {
        return dispensingEmpName;
    }

    public void setDispensingEmpName(String dispensingEmpName) {
        this.dispensingEmpName = dispensingEmpName;
    }

    public Integer getInvalidIndicator() {
        return invalidIndicator;
    }

    public void setInvalidIndicator(Integer invalidIndicator) {
        this.invalidIndicator = invalidIndicator;
    }

    public String getMemo() {
        return memo;
    }

    public void setMemo(String memo) {
        this.memo = memo;
    }

    public String getOrderedByName() {
        return orderedByName;
    }

    public void setOrderedByName(String orderedByName) {
        this.orderedByName = orderedByName;
    }

    public String getNamePhonetic() {
        return namePhonetic;
    }

    public String getVisitNo() {
        return visitNo;
    }

    public void setVisitNo(String visitNo) {
        this.visitNo = visitNo;
    }

    public void setNamePhonetic(String namePhonetic) {
        this.namePhonetic = namePhonetic;
    }

    public String getUserDescription() {
        return userDescription;
    }

    public void setUserDescription(String userDescription) {
        this.userDescription = userDescription;
    }

    public String getProportionConcentration() {
        return proportionConcentration;
    }

    public void setProportionConcentration(String proportionConcentration) {
        this.proportionConcentration = proportionConcentration;
    }

    public Integer getTcmDecoctionIndicator() {
        return tcmDecoctionIndicator;
    }

    public void setTcmDecoctionIndicator(Integer tcmDecoctionIndicator) {
        this.tcmDecoctionIndicator = tcmDecoctionIndicator;
    }

    public String getOrderdDeptName() {
        return orderdDeptName;
    }

    public void setOrderdDeptName(String orderdDeptName) {
        this.orderdDeptName = orderdDeptName;
    }

    public String getStorageCode() {
        return storageCode;
    }

    public void setStorageCode(String storageCode) {
        this.storageCode = storageCode;
    }

    public Integer getNewRepetition() {
        return newRepetition;
    }

    public void setNewRepetition(Integer newRepetition) {
        this.newRepetition = newRepetition;
    }

    public Integer getDoctorReturnRepetition() {
        return doctorReturnRepetition;
    }

    public void setDoctorReturnRepetition(Integer doctorReturnRepetition) {
        this.doctorReturnRepetition = doctorReturnRepetition;
    }

    public Integer getPrescSpecType() {
        return prescSpecType;
    }

    public void setPrescSpecType(Integer prescSpecType) {
        this.prescSpecType = prescSpecType;
    }

    public Integer getForceTcmDecoction() {
        return forceTcmDecoction;
    }

    public void setForceTcmDecoction(Integer forceTcmDecoction) {
        this.forceTcmDecoction = forceTcmDecoction;
    }

    public String getWardName() {
        return wardName;
    }

    public void setWardName(String wardName) {
        this.wardName = wardName;
    }

    public Integer getBedNo() {
        return bedNo;
    }

    public void setBedNo(Integer bedNo) {
        this.bedNo = bedNo;
    }

    public String getBedLabel() {
        return bedLabel;
    }

    public void setBedLabel(String bedLabel) {
        this.bedLabel = bedLabel;
    }

    public String getInpNo() {
        return inpNo;
    }

    public void setInpNo(String inpNo) {
        this.inpNo = inpNo;
    }
}

View Code

createNativeQuery(sb.toString())：createNativeQuery()方法

package com.heren.his.domain.facade.share;

import com.google.common.base.Optional;
import com.heren.his.commons.util.AliasToBeanResultTransformer;
import com.heren.his.commons.util.AliasToEntityMapResultTransformer;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.internal.SessionFactoryImpl;

import javax.inject.Inject;
import javax.persistence.*;
import javax.persistence.criteria.CriteriaQuery;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

import static com.google.common.base.Optional.absent;
import static com.google.common.base.Throwables.propagate;

public abstract class BaseFacade {

    @Inject
    protected EntityManager entityManager;

    protected BaseFacade() {
    }

    protected <T> T strictFindByPrimaryKey(Class<T> entityClass, Object key, String... exceptionMsg) {
        String msg = exceptionMsg != null && exceptionMsg.length > 0 ? exceptionMsg[0] : "指定的资源无法找到";
        if (key == null) {
            throw new com.heren.his.commons.exceptions.NotFoundException(String.format(msg + ": null"));
        }
        T t = entityManager.find(entityClass, key);
        if (t == null) {
            throw new com.heren.his.commons.exceptions.NotFoundException(String.format(msg + ": [%s]", key.toString()));
        }
        return t;
    }

    /**
     * 查找实体
     *
     * @param <T>
     * @param entityClass 实体类
     * @param pk          主键
     * @return 根据指定主键返回实体
     */
    public <T> T get(Class<T> entityClass, Object pk) {
        return entityManager.find(entityClass, pk);
    }


    public <T> T persist(T entity) {
        entityManager.persist(entity);
        return entity;
    }


    /**
     * 修改实体
     *
     * @param entity
     * @return
     */
    public <T> T merge(T entity) {
        return entityManager.merge(entity);
    }

    /**
     * 删除实体
     *
     * @param entity
     * @param <T>
     * @return
     */
    public <T> void remove(T entity) {
        this.getEntityManager().remove(entity);
    }

    public void removeALl(List list) {
        for (Object obj : list) {
            this.getEntityManager().remove(obj);
        }
    }

    /**
     * 批量删除
     *
     * @param entityClass
     * @param ids
     */
    public void remove(Class<?> entityClass, List<Long> ids) {
        for (Long pk : ids) {
            this.getEntityManager().remove(entityManager.merge(entityManager.getReference(entityClass, pk)));
        }
    }

    /**
     * 批量删除
     *
     * @param entityClass
     * @param ids
     */
    public void removeByStringIds(Class<?> entityClass, List<String> ids) {
        for (String pk : ids) {
            this.getEntityManager().remove(entityManager.find(entityClass, pk));
        }
    }


    public <T> List<T> findRange(T entity, int[] range) {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entity.getClass()));
        Query q = getEntityManager().createQuery(cq);
        q.setMaxResults(range[1] - range[0]);
        q.setFirstResult(range[0]);
        return q.getResultList();
    }

    public <T> int count(T entity) {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        javax.persistence.criteria.Root<T> rt = cq.from(entity.getClass());
        cq.select(getEntityManager().getCriteriaBuilder().count(rt));
        Query q = getEntityManager().createQuery(cq);
        return ((Long) q.getSingleResult()).intValue();
    }

    public <T> List<T> findAll(Class<T> entity) {
        TypedQuery query = entityManager.createQuery("FROM " + entity.getCanonicalName(), entity);
        return (List<T>) query.getResultList();
    }

    public Query createNativeQuery(String sqlString) {
        return entityManager.createNativeQuery(sqlString);
    }

    public Query createNativeQuery(String sqlString, Object... params) {
        Query query = entityManager.createNativeQuery(sqlString);
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        return query;
    }

    public Query getNativeQuery(String sqlString, List<Object> params) {
        Query query = entityManager.createNativeQuery(sqlString);
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i + 1, params.get(i));
        }
        return query;
    }

    public List<Object[]> createNativeQuery(String sqlString, List<Object> params) {
        Query query = entityManager.createNativeQuery(sqlString);
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i + 1, params.get(i));
        }
        return query.getResultList();
    }

    /**
     * 执行查询方法
     *
     * @param <T>
     * @param entityClass 实体类
     * @param whereJpql   指定查询返回的第一条记录
     * @param orderBy     用于排序
     * @param args        作为JPQL 查询字符的参数的值
     * @return 返回查询得到的实体List
     */
    public <T> List<T> getResultList(Class<T> entityClass, String whereJpql,
                                     LinkedHashMap<String, String> orderBy, Object... args) {
        //获取实体名称
        String entityName = entityClass.getSimpleName();
        //创建查询
        TypedQuery query = this.getEntityManager().createQuery(" from " + entityName
                + " as o " + whereJpql + this.buildOrderby(orderBy), entityClass);

        //为查询字符串中的参数设置值
        for (int i = 0; i < args.length; i++) {
            query.setParameter(i + 1, args[i]);
        }

        //返回结果集
        return (List<T>) query.getResultList();
    }

    /**
     * 执行查询，并进行分页
     *
     * @param <T>
     * @param entityClass 实体类
     * @param whereJpql   指定查询返回的第一条记录
     * @param firstResult 启始记录数
     * @param maxResult   显示的最大记录数
     * @param orderBy     用于排序
     * @param args        作为JPQL 查询字符的参数的值
     * @return 返回查询得到的实体List
     */
    public <T> List<T> getResultList(Class<T> entityClass, String whereJpql,
                                     int firstResult, int maxResult,
                                     LinkedHashMap<String, String> orderBy, Object... args) {
        //获取实体名称
        String entityName = entityClass.getSimpleName();
        //创建查询
        TypedQuery query = this.getEntityManager().createQuery("select o from " + entityName
                + " as o " + whereJpql + this.buildOrderby(orderBy), entityClass);

        //为查询字符串中的参数设置值
        for (int i = 0; i < args.length; i++) {
            query.setParameter(i + 1, args[i]);
        }
        //对查询的结果集进行分页
        query.setMaxResults(maxResult).setFirstResult(firstResult);

        //返回结果集
        return (List<T>) query.getResultList();
    }


    /**
     * 构建排序子句
     *
     * @param orderby 排序条件
     * @return
     */
    private static String buildOrderby(LinkedHashMap<String, String> orderby) {
        StringBuffer out = new StringBuffer();

        if (orderby != null && orderby.size() > 0) {
            out.append(" order by ");
            for (String key : orderby.keySet()) {
                out.append(" o." + key + " " + orderby.get(key) + " ,");
            }
            out.deleteCharAt(out.length() - 1);
        }
        return out.toString();
    }

    /**
     * 组合排序拼接orderBy
     *
     * @param orderBySort
     * @param jpqlQuery
     * @param tableAlias
     */
    public void buildOrderBy(String orderBySort, StringBuilder jpqlQuery, String tableAlias) {
        if (orderBySort != null && !orderBySort.equals("")) {
            String orderBy = " order by ";
            String[] feilds = orderBySort.split(",");
            int count = feilds.length;
            for (String str : feilds) {
                if (count > 1) {
                    orderBy += tableAlias + "." + str + ",";
                } else {
                    orderBy += tableAlias + "." + str;
                }
                count--;
            }
            jpqlQuery.append(orderBy);
        }
    }

    public JpqlQueryBuilder jpqlQueryBuilder(String jpql, Object param, String judge) {
        return jpqlQueryBuilder(new StringBuilder(jpql), param, judge, new ArrayList<>());
    }

    public JpqlQueryBuilder jpqlQueryBuilder(StringBuilder jpql, Object param, String judge) {
        return jpqlQueryBuilder(jpql, param, judge, new ArrayList<>());
    }

    public JpqlQueryBuilder jpqlQueryBuilder(StringBuilder jpql, Object param, String judge, boolean addWhereOrNot) {
        return jpqlQueryBuilder(jpql, param, judge, new ArrayList<>(), addWhereOrNot);
    }

    public JpqlQueryBuilder jpqlQueryBuilder(StringBuilder jpql, Object param, String judge, ArrayList<Object> parameters) {
        return jpqlQueryBuilder(jpql, param, judge, parameters, true);
    }

    public JpqlQueryBuilder jpqlQueryBuilder(StringBuilder jpql, Object param, String judge, ArrayList<Object> parameters, boolean addWhereOrNot) {
        return new JpqlQueryBuilder(jpql, param, judge, parameters, addWhereOrNot);
    }

    public static class JPQLBuilder {
        private StringBuilder sb = new StringBuilder();
        private List<Object> params = new ArrayList<>();
        private int count=0;

        private JPQLBuilder() {
        }

        public static JPQLBuilder getInstance() {
            return new JPQLBuilder();
        }

        public StringBuilder get$QL() {
            return sb;
        }

        public List<Object> getParams() {
            return params;
        }

        public JPQLBuilder append(String $ql) {
            sb.append(" " + $ql + " ");
            return this;
        }

        public JPQLBuilder select(String selectJPQL) {
            sb.append(" select " + selectJPQL).append(" ");
            return this;
        }

        public JPQLBuilder delete(String selectJPQL) {
            sb.append(" delete from " + selectJPQL).append(" ");
            return this;
        }

        public JPQLBuilder update(String selectJPQL) {
            sb.append(" update " + selectJPQL).append(" ");
            return this;
        }

        public JPQLBuilder select() {
            sb.append(" select ");
            return this;
        }

        public JPQLBuilder from(String fromJPQL) {
            sb.append(" from " + fromJPQL).append(" ");
            return this;
        }

        public JPQLBuilder from() {
            sb.append(" from ");
            return this;
        }

        public JPQLBuilder where() {
            sb.append(" where 1 = 1 ");
            return this;
        }

        public JPQLBuilder where(String whereJPQL) {
            sb.append(" where " + whereJPQL).append(" ");
            return this;
        }
        public JPQLBuilder orderBy(String OrderJPQL) {
            sb.append(" order by "+OrderJPQL+" ");
            return this;
        }

        /**
         * @param param 没有值传 "notParam"
         */
        public JPQLBuilder set(String addJpQL,Object param) {
            if (count == 0) {
                sb.append(" set ").append(addJpQL).append(" ");
                count++;
            } else {
                sb.append(" , ").append(addJpQL).append(" ");
            }
            if (!"notParam".equals(param)) {
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder add(String addJpQL) {
            sb.append(" and ").append(addJpQL).append(" ");
            return this;
        }

        public JPQLBuilder addIsNull(String field) {
            sb.append(" and " + field + " is null ");
            return this;
        }

        public JPQLBuilder addIsNotNull(String field) {
            sb.append(" and " + field + " is not null ");
            return this;
        }

        public JPQLBuilder addEqualTo(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " = ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addNotEqualTo(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " <> ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addGreaterThan(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " > ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addGreaterThanOrEqualTo(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " >= ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addLessThan(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " < ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addLessThanOrEqualTo(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " <= ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addLike(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " like ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addNotLike(String field, Object param) {
            if (param != null) {
                sb.append(" and " + field + " not like ? ");
                params.add(param);
            }
            return this;
        }

        public JPQLBuilder addIn(String field, List<String> param) {
            if (param != null && !param.isEmpty()) {
                String elements = "";
                for (int i = 0, size = param.size(); i < size; i++) {
                    if ("".equals(param.get(i))) {
                        continue;
                    }
                    if (i + 1 == size) {
                        elements = elements + "\'" + param.get(i) + "\'";
                    } else {
                        elements = elements + "\'" + param.get(i) + "\',";
                    }
                }
                if (!"".equals(elements)) {
                    sb.append(" and " + field + " in (" + elements + ")");
                }
            }
            return this;
        }

        public JPQLBuilder addIn(String field, Object... param) {
            if (param != null && param.length != 0) {
                String elements = "";
                for (int i = 0, size = param.length; i < size; i++) {
                    if ("".equals(param[i])) {
                        continue;
                    }
                    if (i + 1 == size) {
                        elements = elements + "\'" + param[i] + "\'";
                    } else {
                        elements = elements + "\'" + param[i] + "\',";
                    }
                }
                if (!"".equals(elements)) {
                    sb.append(" and " + field + " in (" + elements + ")");
                }
            }
            return this;
        }

        public JPQLBuilder addNotIn(String field, List<String> param) {
            if (param != null && !param.isEmpty()) {
                String elements = "";
                for (int i = 0, size = param.size(); i < size; i++) {
                    if ("".equals(param.get(i))) {
                        continue;
                    }
                    if (i + 1 == size) {
                        elements = elements + "\'" + param.get(i) + "\'";
                    } else {
                        elements = elements + "\'" + param.get(i) + "\',";
                    }
                }
                if (!"".equals(elements)) {
                    sb.append(" and " + field + " not in (" + elements + ")");
                }
            }
            return this;
        }

        public JPQLBuilder addBetween(String field, Object start, Object end) {
            if (start != null && end != null) {
                sb.append(" and " + field + " between ? and ? ");
                params.add(start);
                params.add(end);
            }
            return this;
        }

        public JPQLBuilder addNotBetween(String field, Object start, Object end) {
            if (start != null && end != null) {
                sb.append(" and " + field + " not between ? and ? ");
                params.add(start);
                params.add(end);
            }
            return this;
        }
    }

    public <T> List<T> createNativeQuery(String sql, List<Object> params, Class<T> clazz) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(new AliasToBeanResultTransformer(clazz));
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i + 1, params.get(i));
        }
        return query.getResultList();
    }

    public List<Map> createNativeQueryToMap(String sql, Object... params) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        for (int i = 0, length = params.length; i < length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        return query.getResultList();
    }

    public List<Map> createNativeQueryToMap(String sql, List<Object> params) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        for (int i = 0, length = params.size(); i < length; i++) {
            query.setParameter(i + 1, params.get(i));
        }
        return query.getResultList();
    }

    public List<Map> createNativeQueryToMapForPage(String sql, List<Object> params, int page, int pageSize) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        for (int i = 0, length = params.size(); i < length; i++) {
            query.setParameter(i + 1, params.get(i));
        }
        query.setFirstResult((page - 1) * pageSize).setMaxResults(pageSize);
        return query.getResultList();
    }

    public List<Map> createNativeQueryToMapForMax(String sql, List<Object> params, int maxResult) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        for (int i = 0, length = params.size(); i < length; i++) {
            query.setParameter(i + 1, params.get(i));
        }
        query.setMaxResults(maxResult);
        return query.getResultList();
    }


    public <T> List<T> createNativeQuery(Class<T> clazz, String sql, Object... params) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(new AliasToBeanResultTransformer(clazz));
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        return query.getResultList();
    }

    public class JpqlQueryBuilder {

        private StringBuilder jpql;
        private int count;
        private ArrayList<Object> params;
        private boolean addWhereOrNot;

        /**
         * 根据传入的初始jpql，以及初始参数和该初始参数在JPQL中需要显示的查询语句片段，以便构造JpqlQueryBuilder。
         *
         * @param jpql
         * @param param
         * @param judge
         */
        public JpqlQueryBuilder(StringBuilder jpql, Object param, String judge, ArrayList<Object> params, boolean addWhereOrNot) {
            this.params = params;
            this.count = 0;
            this.jpql = jpql;
            this.addWhereOrNot = addWhereOrNot;
            this.param(param, judge);
        }

        /**
         * 传入需要进行JPQL语句组织时，用到的参数和参数查询语句片段。
         *
         * @param param
         * @param judge
         * @return
         */
        public JpqlQueryBuilder param(Object param, String judge) {
            if (param != null && !"".equals(param)) {
                if (count == 0 && addWhereOrNot) {
                    jpql.append(" where " + judge);
                } else {
                    jpql.append(" and " + judge);
                }
                if (!param.equals("joinsExpression")) {
                    params.add(param);
                }
                ++count;
            }
            return this;
        }

        /**
         * 获得最终组织好的Jpql语句。
         *
         * @return
         */
        public StringBuilder getJpql() {
            return jpql;
        }

        /**
         * 获得最终组织好的、相对于Jpql语句的参数列表。
         *
         * @return
         */
        public ArrayList<Object> getJpqlParams() {
            return params;
        }

        /**
         * 获得计数器当前的值。
         *
         * @return
         */
        public int getCount() {
            return count;
        }
    }

    public <T> List<T> find(Class<T> type, String query, List<Object> parameters) {
        return createQuery(type, query, parameters).getResultList();
    }

    public <T> TypedQuery<T> createQuery(Class<T> type, String query, List<Object> parameters) {
        TypedQuery<T> typedQuery = entityManager.createQuery(query, type);
        for (int i = 0; i < parameters.size(); i++) {
            if (parameters.get(i) instanceof Date) {
                typedQuery.setParameter(i + 1, (Date) parameters.get(i), TemporalType.TIMESTAMP);
            } else {
                typedQuery.setParameter(i + 1, parameters.get(i));
            }
        }
        return typedQuery;
    }


    public <T> List<T> find(Class<T> type, String query, Object... parameters) {
        return createQuery(type, query, parameters).getResultList();
    }

    public <T> Optional<T> first(Class<T> type, String query, Object... parameters) {
        try {
            return Optional.of(createQuery(type, query, parameters).setMaxResults(1).getSingleResult());
        } catch (NoResultException e) {
            return absent();
        }
    }

    public <T> TypedQuery<T> createQuery(Class<T> type, String query, Object... parameters) {
        TypedQuery<T> typedQuery = entityManager.
                createQuery(query, type);
        for (int i = 0; i < parameters.length; i++) {
            if (parameters[i] instanceof Date) {
                typedQuery.setParameter(i + 1, (Date) parameters[i], TemporalType.DATE);
            } else {
                typedQuery.setParameter(i + 1, parameters[i]);
            }
        }
        return typedQuery;
    }
    public Query createQuery(String query) {
        return entityManager.createQuery(query);
    }

    protected int update(String queryStr, Object... parameters) {
        try {
            Query query = entityManager.createQuery(queryStr);
            for (int i = 0; i < parameters.length; i++) {
                if (parameters[i] instanceof Date) {
                    query.setParameter(i + 1, (Date) parameters[i], TemporalType.TIMESTAMP);
                } else {
                    query.setParameter(i + 1, parameters[i]);
                }
            }
            int i = query.executeUpdate();
            return i;
        } catch (Exception e) {
            propagate(e);
        }
        return 0;
    }

    public void detach(Object entity) {
        entityManager.detach(entity);
    };

    public EntityManager getEntityManager() {
        return entityManager;
    }

    public Connection getConnection() {
        Connection connection = null;
        try {
            Session session = (Session) entityManager.getDelegate();
            SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();
            connection = sessionFactory.getJdbcServices().getBootstrapJdbcConnectionAccess().obtainConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

View Code

AliasToBeanResultTransformer.class

package com.heren.his.commons.util;

import org.hibernate.HibernateException;//hibernate-core-5.0.1.Final.jar
import org.hibernate.engine.spi.SessionFactoryImplementor;//hibernate-core-5.0.1.Final.jar
import org.hibernate.property.access.internal.PropertyAccessStrategyBasicImpl;//hibernate-core-5.0.1.Final.jar
import org.hibernate.property.access.internal.PropertyAccessStrategyChainedImpl;//hibernate-core-5.0.1.Final.jar
import org.hibernate.property.access.internal.PropertyAccessStrategyFieldImpl;//hibernate-core-5.0.1.Final.jar
import org.hibernate.property.access.internal.PropertyAccessStrategyMapImpl;//hibernate-core-5.0.1.Final.jar
import org.hibernate.property.access.spi.PropertyAccessStrategy;//hibernate-core-5.0.1.Final.jar
import org.hibernate.property.access.spi.Setter;//hibernate-core-5.0.1.Final.jar
import org.hibernate.transform.AliasedTupleSubsetResultTransformer;//hibernate-core-5.0.1.Final.jar

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Arrays;

public class AliasToBeanResultTransformer extends AliasedTupleSubsetResultTransformer {

    private final Class resultClass;
    private boolean isInitialized;
    private String[] aliases;
    private Setter[] setters;

    public AliasToBeanResultTransformer(Class resultClass) {
        if(resultClass == null) {
            throw new IllegalArgumentException("resultClass cannot be null");
        } else {
            this.isInitialized = false;
            this.resultClass = resultClass;
        }
    }

    public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
        return false;
    }

    public Object transformTuple(Object[] tuple, String[] aliases) {
        try {
            this.aliases = new String[aliases.length];
            for(int i = 0; i < aliases.length; ++i) {
                aliases[i] = StringUtils.toHumpName(aliases[i]);
                if(aliases[i] != null) {
                    this.aliases[i] = aliases[i];
                }
            }
            if(!this.isInitialized) {
                this.initialize(aliases);
            } else {
                this.check(aliases);
            }

            Object result = this.resultClass.newInstance();

            for (int i = 0; i < aliases.length; i++) {
                if (setters[i] != null) {
                    Class<?> aClass = setters[i].getMethod().getParameterTypes()[0];
                    Object temp;
                    if(tuple[i] instanceof BigDecimal) {
                        if(aClass == Integer.class) {
                            temp = ((BigDecimal)tuple[i]).intValue();
                        } else if(aClass == BigInteger.class) {
                            temp = ((BigDecimal)tuple[i]).toBigInteger();
                        } else if(aClass == Double.class) {
                            temp = ((BigDecimal)tuple[i]).doubleValue();
                        } else if(aClass == Float.class) {
                            temp = ((BigDecimal)tuple[i]).floatValue();
                        } else if(aClass == Long.class) {
                            temp = ((BigDecimal)tuple[i]).longValue();
                        } else if(aClass == Short.class) {
                            temp = ((BigDecimal)tuple[i]).shortValue();
                        } else if(aClass == Byte.class) {
                            temp = ((BigDecimal)tuple[i]).byteValue();
                        } else {
                            temp = tuple[i];
                        }
                    } else {
                        temp = tuple[i];
                    }
                    setters[i].set(result, temp, null);
                }
            }

            return result;
        } catch (InstantiationException var5) {
            throw new HibernateException("Could not instantiate resultclass: " + this.resultClass.getName());
        } catch (IllegalAccessException var6) {
            throw new HibernateException("Could not instantiate resultclass: " + this.resultClass.getName());
        }
    }

    private void initialize(String[] aliases) {
        PropertyAccessStrategyChainedImpl propertyAccessStrategy = new PropertyAccessStrategyChainedImpl(new PropertyAccessStrategy[]{PropertyAccessStrategyBasicImpl.INSTANCE, PropertyAccessStrategyFieldImpl.INSTANCE, PropertyAccessStrategyMapImpl.INSTANCE});
        this.setters = new Setter[aliases.length];

        for(int i = 0; i < aliases.length; ++i) {
            String alias = aliases[i];
            if(alias != null) {
                this.setters[i] = propertyAccessStrategy.buildPropertyAccess(this.resultClass, alias).getSetter();
            }
        }

        this.isInitialized = true;
    }

    private void check(String[] aliases) {
        if(!Arrays.equals(aliases, this.aliases)) {
            throw new IllegalStateException("aliases are different from what is cached; aliases=" + Arrays.asList(aliases) + " cached=" + Arrays.asList(this.aliases));
        }
    }

    public boolean equals(Object o) {
        if(this == o) {
            return true;
        } else if(o != null && this.getClass() == o.getClass()) {
            AliasToBeanResultTransformer that = (AliasToBeanResultTransformer)o;
            return !this.resultClass.equals(that.resultClass)?false:Arrays.equals(this.aliases, that.aliases);
        } else {
            return false;
        }
    }

    public int hashCode() {
        int result = this.resultClass.hashCode();
        result = 31 * result + (this.aliases != null?Arrays.hashCode(this.aliases):0);
        return result;
    }
}

View Code

转载于:https://www.cnblogs.com/ms-grf/p/7667580.html

weixin_34365417

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
查询自定义VO对象的sql

查询的方法public List<DrugPrescMasterVO> findHerbalDrugReturn(String patientId, String prescNo, String storageCode, Integer prescSource, Integer displayTime,Integer isYfTy) { StringBuffer sb...
复制链接

扫一扫