Example usage for org.hibernate SQLQuery list

Introduction

In this page you can find the example usage for org.hibernate SQLQuery list.

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName, String staffCode) {
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    if (DataUtil.isStringNullOrEmpty(staffCode)) {
        return getListTaxAuthorityFromMineName(mineName);
    }/*from w ww  .  j av a 2s .  c o m*/
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM customer_status cs  ");
    sqlQuery.append("                WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sqlQuery.append("             AND cs.mine_name = :mineName ");
        }
        //            sqlQuery.append("            AND cs.mine_name = :mineName  ");
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("                AND cs.staff_code  = :staffCode ");
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }

        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

---

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineNameAndStaffCodeAndProvider(String mineName,
        String staffCode, String provider, String status) {
    if (DataUtil.isStringNullOrEmpty(staffCode)) {
        return getListTaxAuthorityFromMineNameAndProvider(mineName, provider, status);
    }/*from   w  w  w. j a v  a 2  s  .co m*/
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM customer_status cs  ");
    if (!DataUtil.isStringNullOrEmpty(provider) || !DataUtil.isStringNullOrEmpty(mineName)) {
        sqlQuery.append("            JOIN term_information ti ON ti.TAX_CODE = cs.TAX_CODE ");
    }
    sqlQuery.append("                WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("            AND cs.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("            AND cs.mine_name = :mineName  ");
        }
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("                AND cs.staff_code  = :staffCode ");
    }
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("            AND lower(ti.provider) = ANY (:provider)  ");
    }
    if (!DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("            AND cs.status = ANY (:status)  ");
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        System.out.println(sqlQuery.toString());
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        if (!DataUtil.isStringNullOrEmpty(status)) {
            query.setParameterList("status", DataUtil.parseInputListString(status));
        }
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }

        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

---

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName) {
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append(//  w w w . ja  v  a2s  .  co m
            "                FROM CUSTOMER cs INNER JOIN TERM_INFORMATION ti ON ti.TAX_CODE = cs.TAX_CODE ");
    sqlQuery.append("                WHERE 1=1 AND ti.IS_CONTACT_INFO is NULL ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("            AND ti.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("            AND ti.mine_name = :mineName  ");
        }
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

---

From source file:com.cms.dao.TaxAuthorityDAO.java

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineNameAndProvider(String mineName, String provider,
        String status) {//  w w w  .j a  v  a 2s.  co m
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT c.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM CUSTOMER c ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                INNER JOIN TERM_INFORMATION ti ON ti.TAX_CODE = c.TAX_CODE ");
    }
    if (!DataUtil.isStringNullOrEmpty(mineName) || !DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("                INNER JOIN CUSTOMER_STATUS cs ON c.TAX_CODE = cs.TAX_CODE ");
    }
    sqlQuery.append("                   WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                   AND ti.IS_CONTACT_INFO is NULL ");
    }
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("                   AND cs.mine_name IN (:mineName)  ");
        } else {
            sqlQuery.append("                   AND cs.mine_name = :mineName  ");
        }
    }
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("                   AND lower(ti.provider) = ANY (:provider)  ");
    }
    if (!DataUtil.isStringNullOrEmpty(status)) {
        sqlQuery.append("                   AND cs.STATUS = :status  ");
    }
    sqlQuery.append("                ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        if (!DataUtil.isStringNullOrEmpty(status)) {
            query.setParameter("status", status);
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

---

From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java

License:Apache License

private void checkQueryResult(SimpleEntity entity, List expected) {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE phone = :phone");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);/*w  w w.ja v  a 2  s. c  o m*/
    query.setCacheRegion(cacheRegion);
    query.setParameter("phone", entity.getPhone());
    List res = query.list();
    Assert.assertEquals(expected, res);
    session.close();
}

---

From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java

License:Apache License

private List listQueryResult() {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE address = :address");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);/*w ww  .j a v  a 2  s.c  o  m*/
    query.setCacheRegion(multiCacheRegion);
    query.setParameter("address", addressValue);
    List res = query.list();
    session.close();
    return res;
}

---

From source file:com.court.controller.GuarantorsFxmlController.java

private List<Member> getAvailableGuarantors() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    SQLQuery query = session
            .createSQLQuery("SELECT\n" + "    m.member_id AS memberId,\n" + "    m.full_name AS fullName,\n"
                    + "    wb.branch_name AS workingOffice,\n" + "    pb.branch_name AS payingOffice,\n"
                    + "    ml.member_loan_code AS loanCode,\n" + "    ml.granted_date AS grantedDate,\n"
                    + "    ml.loan_amount AS loanAmount,\n" + "    ml.loan_interest AS loanInterest,\n"
                    + "    ml.interest_per AS interestPer,\n" + "    ml.interest_method AS interMethod,\n"
                    + "    ml.loan_duration AS loanDuration,\n" + "    ml.duration_per AS lDurationPer,\n"
                    + "    ml.is_complete AS loanComplete\n" + "FROM\n" + "    court_loan.branch wb\n"
                    + "INNER JOIN\n" + "    court_loan.member m\n" + "ON\n" + "    (\n"
                    + "        wb.id = m.branch_id)\n" + "INNER JOIN\n" + "    court_loan.branch pb\n" + "ON\n"
                    + "    (\n" + "        m.pay_office_id = pb.id)\n" + "LEFT OUTER JOIN\n"
                    + "    court_loan.member_loan ml\n" + "ON\n" + "    (\n" + "        m.id = ml.member_id) ;")
            .addScalar("memberId", new StringType()).addScalar("fullName", new StringType())
            .addScalar("workingOffice", new StringType()).addScalar("payingOffice", new StringType())
            .addScalar("loanCode", new StringType()).addScalar("grantedDate", new DateType())
            .addScalar("loanAmount", new DoubleType()).addScalar("loanInterest", new DoubleType())
            .addScalar("interestPer", new StringType()).addScalar("interMethod", new StringType())
            .addScalar("loanDuration", new IntegerType()).addScalar("lDurationPer", new StringType())
            .addScalar("loanComplete", new BooleanType());

    List<Object[]> rows = query.list();
    List<Member> list = new ArrayList<>();
    for (Object[] row : rows) {
        Member m = new Member();
        m.setMemberId(row[0].toString());
        m.setFullName(row[1].toString());
        m.setBranch(new Branch(row[2].toString()));
        m.setPayOffice(new Branch(row[3].toString()));
        list.add(m);/*  w  w  w  . ja v a  2 s .c  o m*/
    }

    session.close();
    return list;
}

---

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

public List<BriefServerInfo> getAccessiblePoolServers(final long userId, final String poolId) {
    try {//w w w.j av  a 2  s. c om
        return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
            @Override
            public List<BriefServerInfo> run(Session session) {
                Map<String, Object> keyValues = new HashMap<>();

                SQLQuery query;
                if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID.equals(poolId)) {
                    query = session
                            .createSQLQuery(GLOBAL_POOL_QUERY + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                } else if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_ALL_ID.equals(poolId)
                        || poolId == null) {

                    // system user has access to all servers in the pool
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        query = session
                                .createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_SYSTEM_USER_QUERY
                                        + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                    } else {
                        query = session.createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_QUERY
                                + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                        query.setParameter("userId", userId);
                    }
                } else {
                    StringBuilder sql;
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        sql = new StringBuilder(SPECIFIC_POOL_SYSTEM_USER_QUERY + " AND g.seq_id = :poolId");
                    } else {
                        sql = new StringBuilder(SPECIFIC_POOL_QUERY + " AND g.seq_id = :poolId");
                    }
                    keyValues.put("poolId", Long.valueOf(poolId));

                    sql.append(POOL_GROUP_BY_CLAUSE);
                    sql.append(POOL_ORDER_BY_CLAUSE);

                    query = session.createSQLQuery(sql.toString());
                    if (userId != DBInit.SYSTEM_USER_ID)
                        query.setParameter("userId", userId);
                }

                for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }

                return buildBriefServerInfoList(query.list());
            }

            @Override
            public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                logger.error("Failed to look up servers from pool " + poolId + " for user " + userId, e);
                throw e;
            }
        });
    } catch (SQLException e) {
        throw new AsmManagerRuntimeException(e);
    }
}

---

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

public List<BriefServerInfo> getAccessibleServers(final long userId, final List<String> serverRefIds) {
    try {/*  w  w  w. jav a  2  s .c o m*/
        return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
            private StringBuilder appendServerRefIds(StringBuilder sql, Map<String, Object> keyValues) {
                if (serverRefIds != null && serverRefIds.size() > 0) {
                    int i = 0;
                    sql.append(" AND i.ref_id IN (");
                    for (String serverRefId : serverRefIds) {
                        String name = "ref" + i++;
                        sql.append(':').append(name).append(", ");
                        keyValues.put(name, serverRefId);
                    }
                    sql.deleteCharAt(sql.length() - 1);
                    sql.deleteCharAt(sql.length() - 1);
                    sql.append(")");
                }
                return sql;
            }

            @Override
            public List<BriefServerInfo> run(Session session) {
                Map<String, Object> keyValues = new HashMap<>();

                StringBuilder sql = new StringBuilder(GLOBAL_POOL_QUERY);
                if (userId == DBInit.SYSTEM_USER_ID) {
                    appendServerRefIds(sql, keyValues).append(" UNION ")
                            .append(SPECIFIC_POOL_SYSTEM_USER_QUERY);
                } else {
                    appendServerRefIds(sql, keyValues).append(" UNION ").append(SPECIFIC_POOL_QUERY);
                }
                appendServerRefIds(sql, keyValues).append(POOL_GROUP_BY_CLAUSE).append(POOL_ORDER_BY_CLAUSE);
                SQLQuery query = session.createSQLQuery(sql.toString());

                if (userId != DBInit.SYSTEM_USER_ID)
                    query.setParameter("userId", userId);

                for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }

                return buildBriefServerInfoList(query.list());
            }

            @Override
            public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                logger.error("Failed to look up servers " + serverRefIds + " for user " + userId, e);
                throw e;
            }
        });
    } catch (SQLException e) {
        throw new AsmManagerRuntimeException(e);
    }
}

---

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceInventoryDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
// NOTE[fcarta] - Its noted that concatenating a sql string leaves the query vulnerable to SQL
// injection attacks. In this case this method is private and will only be called with trusted
// UUIDs.//ww  w  .  java 2s .c om
private List<String> getRefIdsOfDevicesByDeploymentIds(final List<String> deploymentIds) {
    final String deploymentIdsJoined = "'" + StringUtils.join(deploymentIds, "','") + "'";
    logger.info("Retrieving ref Ids of Devices for the given deploymentIds : " + deploymentIdsJoined);
    Session session = null;
    Transaction tx = null;

    List<String> refIds = ListUtils.EMPTY_LIST;
    try {
        session = _dao._database.getNewSession();
        tx = session.beginTransaction();
        final String sql =
                //"select device_id from deployment_to_device_map where deployment_id in ( :deploymentIds )";
                "select device_id from deployment_to_device_map where deployment_id in (" + deploymentIdsJoined
                        + ")";
        SQLQuery query = session.createSQLQuery(sql);
        //query.setParameter("deploymentIds", deploymentIdsJoined);
        refIds = query.list();
        tx.commit();
    } catch (Exception e) {
        logger.warn("Caught exception getting refIds of deployment ids " + deploymentIdsJoined, e);
        throw new AsmManagerInternalErrorException("Get Ref Ids by Deployment Ids", "DeviceInventoryDAO", e);
    } finally {
        try {
            if (session != null) {
                session.close();
            }
        } catch (Exception ex) {
            logger.warn("Unable to close session during ref Id retrieval: " + ex);
        }
    }

    return refIds;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »