Hibernate NativeQuery代码优化一则

使用hibernate，用原始sql查询分页，代码优化过程：

1.原始版：

        String x = "";
        //补全语句，当然也可以不用判断查询条件，全部生成sql
        if (drugName != null && drugName.equals("") == false) {
            x += "and a.DRUG_NAME like :name ";
        }
        if (approvalNum != null && approvalNum.equals("") == false) {
            x += "and a.PERMIT_NUMBER like :name1 ";
        }
        if (productNum != null && productNum.equals("") == false) {
            x += "and a.P_NUM like :name2 ";
        }
        if (companyName != null && companyName.equals("") == false) {
            x += "and a.PRODUCTION_ENTERPRISES like :name3 ";
        }
        if (shop != null && shop.equals("") == false) {
            x += "and c.PHARMACY_NAME like :name4 ";
        }
        String sql_count = getActiveSql(x,true);

        javax.persistence.Query sqlQuery = entityManager.createNativeQuery(sql_count);
        //设置参数
        if (drugName != null && drugName.equals("") == false) {
            sqlQuery.setParameter("name","%" + drugName + "%");
        }
        if (approvalNum != null && approvalNum.equals("") == false) {
            sqlQuery.setParameter("name1", "%" + approvalNum + "%");
        }
        if (productNum != null && productNum.equals("") == false) {
            sqlQuery.setParameter("name2", "%" + productNum + "%");
        }
        if (companyName != null && companyName.equals("") == false) {
            sqlQuery.setParameter("name3", "%" + companyName + "%");
        }
        if (shop != null && shop.equals("") == false) {
            sqlQuery.setParameter("name4", "%" + shop + "%");
        }
        List<BigDecimal> total_obj = sqlQuery.getResultList();
        //返回list查询
        sqlQuery = entityManager.createNativeQuery(getActiveSql(x,false), DrugDistribution.class);
        //设置参数
        if (drugName != null && drugName.equals("") == false) {
            sqlQuery.setParameter("name","%" + drugName + "%");
        }
        if (approvalNum != null && approvalNum.equals("") == false) {
            sqlQuery.setParameter("name1", "%" + approvalNum + "%");
        }
        if (productNum != null && productNum.equals("") == false) {
            sqlQuery.setParameter("name2", "%" + productNum + "%");
        }
        if (companyName != null && companyName.equals("") == false) {
            sqlQuery.setParameter("name3", "%" + companyName + "%");
        }
        if (shop != null && shop.equals("") == false) {
            sqlQuery.setParameter("name4", "%" + shop + "%");
        }
        List<DrugDistribution> list = sqlQuery.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize()).setMaxResults(pageable.getPageSize()).getResultList();
        Page<DrugDistribution> page = new PageImpl(list, pageable, totals_obj.get(0).intValue());
        return  page;

第一版优化，使用数组替代各种if else判断。

String x = "";
        String[] searchList = {drugName, approvalNum, productNum, companyName, shop};
        String[] columnNameList = {"a.DRUG_NAME", "a.PERMIT_NUMBER", "a.P_NUM", "b.SUPPLIER_NAME", "c.PHARMACY_NAME"};
        for (int i = 0; i < searchList.length; i++) {
            if (searchList[i] != null && searchList[i].equals("") == false) {
                x += " and " + columnNameList[i] + " like :name" + i;
            }
        }
        String sql_count = getActiveSupplierSql(x, true);
        javax.persistence.Query sqlQuery = entityManager.createNativeQuery(sql_count);

        for (int i = 0; i < searchList.length; i++) {
            if (searchList[i] != null && searchList[i].equals("") == false) {
                sqlQuery.setParameter("name" + i, "%" + searchList[i] + "%");
            }
        }
        List<BigDecimal> totals_obj = sqlQuery.getResultList();
        //
        sqlQuery = entityManager.createNativeQuery(getActiveSupplierSql(x, false), DrugDistribution.class);
        for (int i = 0; i < searchList.length; i++) {
            if (searchList[i] != null && searchList[i].equals("") == false) {
                sqlQuery.setParameter("name" + i, "%" + searchList[i] + "%");
            }
        }
        List<DrugDistribution> list = sqlQuery.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize()).setMaxResults(pageable.getPageSize()).getResultList();
        Page<DrugDistribution> page = new PageImpl(list, pageable, totals_obj.get(0).intValue());
        return page;

经过精简，代码大幅减少，但是还是有2个相同的for循环，再次进行重构

 String x = "";
        String[] searchList = {drugName, approvalNum, productNum, companyName, shop};
        String[] columnNameList = {"a.DRUG_NAME", "a.PERMIT_NUMBER", "a.P_NUM", "b.SUPPLIER_NAME", "c.PHARMACY_NAME"};
        for (int i = 0; i < searchList.length; i++) {
            if (searchList[i] != null && searchList[i].equals("") == false) {
                x += " and " + columnNameList[i] + " like :name" + i;
            }
        }
        String sql_count = getActiveSupplierSql(x, true);
        javax.persistence.Query sqlQuery = entityManager.createNativeQuery(sql_count);
        
        sqlQuery = setParameter(sqlQuery, searchList);
        List<BigDecimal> total_obj = sqlQuery.getResultList();
        
        sqlQuery = entityManager.createNativeQuery(getActiveSupplierSql(x, false), DrugDistribution.class);
        sqlQuery = setParameter(sqlQuery, searchList);
        List<DrugDistribution> list = sqlQuery.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize()).setMaxResults(pageable.getPageSize()).getResultList();
        Page<DrugDistribution> page = new PageImpl(list, pageable, total_obj.get(0).intValue());
        return page;

第三次重构：

public Page<PrescriptionDrugs> getSupplier(Pageable pageable, String tradeName, String salesperson, String beginDate, String endDate) {
        String x = "";
        String[] columnNameList = {"a.TRADE_NAME like ", "b.SALE_BY  like ", "b.SALE_DATE>=", "b.SALE_DATE<"};
        String[] searchList = {tradeName, salesperson, beginDate, endDate};
        String[] prefixList = {"%", "%", "", ""};
        String[] suffixList = {"%", "%", "", ""};
        for (int i = 0; i < searchList.length; i++) {
            if (searchList[i] != null && searchList[i].equals("") == false) {
                x += " and " + columnNameList[i] + " :name" + i;
            }
        }
        String sql_count = getActiveSupplierSql(x, true);
        javax.persistence.Query sqlQuery = entityManager.createNativeQuery(sql_count);

        sqlQuery = setParameter(sqlQuery, searchList, prefixList, suffixList);
        List<BigDecimal> total_obj = sqlQuery.getResultList();

        sqlQuery = entityManager.createNativeQuery(getActiveSupplierSql(x, false), PrescriptionDrugs.class);
        sqlQuery = setParameter(sqlQuery, searchList, prefixList, suffixList);
        List<PrescriptionDrugs> list = sqlQuery.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize()).setMaxResults(pageable.getPageSize()).getResultList();
        Page<PrescriptionDrugs> page = new PageImpl(list, pageable, total_obj.get(0).intValue());
        return page;
    }

或者：

 

 public Page<PrescriptionDrugs> getSupplier(Pageable pageable, String tradeName, String salesperson, String beginDate, String endDate) {
        String x = "";
        String[] columnNameList = {"a.TRADE_NAME like '%'+:par0+'%'", "b.SALE_BY like '%'+:par1+'%'", "b.SALE_DATE>=:par2", "b.SALE_DATE<:par3"};
        String[] searchList = {tradeName, salesperson, beginDate, endDate};
        for (int i = 0; i < searchList.length; i++) {
            x += " and " + columnNameList[i];
        }
        String sql_count = getActiveSupplierSql(x, true);
        javax.persistence.Query sqlQuery = entityManager.createNativeQuery(sql_count);

        sqlQuery = setParameter(sqlQuery, searchList);
        List<BigDecimal> total_obj = sqlQuery.getResultList();

        sqlQuery = entityManager.createNativeQuery(getActiveSupplierSql(x, false), PrescriptionDrugs.class);
        sqlQuery = setParameter(sqlQuery, searchList);
        List<PrescriptionDrugs> list = sqlQuery.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize()).setMaxResults(pageable.getPageSize()).getResultList();
        Page<PrescriptionDrugs> page = new PageImpl(list, pageable, total_obj.get(0).intValue());
        return page;
    }

 

针对for进行再次提炼，此处代码略。