Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. setParameter

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

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

License:Open Source License

public List<ObjectsDTO> getLstObjectsDTOByStaffId(String staffId) {
    List<ObjectsDTO> lstObjects;
    StringBuilder sql = new StringBuilder();

    sql.append("     SELECT DISTINCT a.OBJECT_ID objectId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name, ");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.OBJECT_TYPE objectType, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     a.URL url ");
    sql.append("        FROM OBJECTS a ");
    sql.append("        JOIN ROLE_OBJECTS b ");
    sql.append("           ON b.OBJECT_ID = a.OBJECT_ID ");
    sql.append("        AND b.ROLE_ID IN ");
    sql.append("              (SELECT a.ROLE_ID ");
    sql.append("              FROM roles a ");
    sql.append("              JOIN MAP_STAFF_ROLES b ");
    sql.append("              ON b.ROLE_ID   = a.ROLE_ID ");
    sql.append("              AND b.STAFF_ID = ? )");
    try {//w w w . j  a  v a  2  s .co m
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(ObjectsDTO.class));
        query.addScalar("objectId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("objectType", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("url", new StringType());
        //
        query.setParameter(0, staffId);

        lstObjects = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        lstObjects = null;
    }
    if (DataUtil.isListNullOrEmpty(lstObjects)) {
        return null;
    } else {
        return lstObjects;
    }
}

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

License:Open Source License

public List<ObjectsDTO> getListObjectByRole(RolesDTO role) {
    List<ObjectsDTO> lstObjects;
    StringBuilder sql = new StringBuilder();
    //        List lstParams = new ArrayList();
    sql.append("     SELECT DISTINCT a.OBJECT_ID objectId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name,");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.OBJECT_TYPE objectType, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     a.URL url, ");
    sql.append("                     b.ID roleObjectId ");
    sql.append("        FROM OBJECTS a ");
    sql.append("        JOIN ROLE_OBJECTS b ");
    sql.append("             ON b.OBJECT_ID = a.OBJECT_ID ");
    sql.append("            AND b.ROLE_ID = ? ");
    try {/* w  w w .  j  ava  2  s . co  m*/
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(ObjectsDTO.class));
        query.addScalar("objectId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("objectType", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("url", new StringType());
        query.addScalar("roleObjectId", new StringType());

        query.setParameter(0, role.getRoleId());

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

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

License:Open Source License

/**
 * Lay danh sach vai tro du nhan vien/*  w  ww. ja v  a 2 s . c  om*/
 *
 * @param staffId
 * @return
 */
public List<RolesDTO> getListRolesByStaffId(String staffId) {
    List<RolesDTO> lstRoles;
    StringBuilder sql = new StringBuilder();
    sql.append("     SELECT DISTINCT a.ROLE_ID roleId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name, ");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     b.MAP_ID mapId ");
    sql.append("        FROM roles a ");
    sql.append("        JOIN MAP_STAFF_ROLES b ");
    sql.append("                  ON b.ROLE_ID   = a.ROLE_ID ");
    sql.append("                 AND b.STAFF_ID = ? ");
    try {
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(RolesDTO.class));
        query.addScalar("roleId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("mapId", new StringType());

        query.setParameter(0, staffId);

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

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);
    }// ww w . jav a  2s . co 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  ww  w .ja  v a2  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(/*from   w w w  .jav a  2s .c  o  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) {/* ww  w  .j a  v a 2  s  .c  o 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.connexience.server.model.metadata.MetadataQueryBuilder.java

License:Open Source License

/** Create the Hibernate Query */
public SQLQuery createSQLQuery(Session session) throws Exception {
    // Metadata query
    String queryText = buildMetaDataSearchQuery();

    // Full query
    String fullQuery = "select * from objectsflat where id in (" + queryText + ")";
    SQLQuery q = session.createSQLQuery(fullQuery);

    Iterator<MetadataQueryItem> items = metadataQuery.items();
    MetadataQueryItem i;//from w  ww  .ja  v  a 2s . c o m

    // Set category if needed 
    Object[][] params;
    int positionCounter = 0;
    while (items.hasNext()) {
        i = items.next();
        i.setPositionCounter(positionCounter);
        params = i.getParameters();
        for (int j = 0; j < params.length; j++) {
            q.setParameter((String) params[j][0], params[j][1]);
        }
        positionCounter = i.getPositionCounter();
    }
    q.addEntity(ServerObject.class);
    return q;
}

From source file:com.connexience.server.model.metadata.MetadataQueryBuilder.java

License:Open Source License

/** Create a count query */
public SQLQuery createSQLCountQuery(Session session) throws Exception {
    // Metadata query
    String queryText = buildMetaDataSearchQuery();

    // Full query
    String fullQuery = "select count(id) from objectsflat where id in (" + queryText + ")";
    SQLQuery q = session.createSQLQuery(fullQuery);

    Iterator<MetadataQueryItem> items = metadataQuery.items();
    MetadataQueryItem i;/*from   w w w . j  ava2s  .c  o m*/

    // Set category if needed 
    Object[][] params;
    int positionCounter = 0;
    while (items.hasNext()) {
        i = items.next();
        i.setPositionCounter(positionCounter);
        params = i.getParameters();
        for (int j = 0; j < params.length; j++) {
            q.setParameter((String) params[j][0], params[j][1]);
        }
        positionCounter = i.getPositionCounter();
    }
    return q;
}

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);//  ww w  . j  av  a2  s  .co m
    query.setCacheRegion(cacheRegion);
    query.setParameter("phone", entity.getPhone());
    List res = query.list();
    Assert.assertEquals(expected, res);
    session.close();
}