Example usage for org.hibernate SQLQuery 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.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

public static Object getFirstWithSql(String sql, Object param1) throws HibernateException {
    Session session = getSession();/*from w ww  .ja v  a2 s.  c  om*/
    SQLQuery query = session.createSQLQuery(sql);
    query.setParameter(0, param1);

    List<?> result = query.list();

    if (result == null || result.size() == 0) {
        return null;
    }

    return result.get(0);
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

public static Object getFirstWithSql(String sql, Object param1, Object param2) throws HibernateException {
    Session session = getSession();/*ww w . j av a  2s  .  c  om*/
    SQLQuery query = session.createSQLQuery(sql);
    query.setParameter(0, param1);
    query.setParameter(01, param2);

    List<?> result = query.list();

    if (result == null || result.size() == 0) {
        return null;
    }

    return result.get(0);
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

@SuppressWarnings("unchecked")
public static <T> List<T> searchWithSql(Class<T> entityClass, String sql, Object param1)
        throws HibernateException {
    Session session = getSession();// w  w  w  .  j a  v a 2 s  .  co  m

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(entityClass);
    query.setParameter(0, param1);
    List<T> result = query.list();

    return result;
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

@SuppressWarnings("unchecked")
public static <T> List<T> searchWithSql(Class<T> entityClass, String sql, Object param1, Object param2)
        throws HibernateException {
    Session session = getSession();/*from w ww .j  av  a  2 s  .c  om*/

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(entityClass);
    query.setParameter(0, param1);
    query.setParameter(1, param2);
    List<T> result = query.list();

    return result;
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

@SuppressWarnings("unchecked")
public static <T> List<T> searchWithSql(Class<T> entityClass, String sql, Object param1, Object param2,
        Object param3) throws HibernateException {
    Session session = getSession();//www. j a  v a 2 s .  c  om

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(entityClass);
    query.setParameter(0, param1);
    query.setParameter(1, param2);
    query.setParameter(2, param3);
    List<T> result = query.list();

    return result;
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

@SuppressWarnings("unchecked")
public static <T> List<T> searchWithSql(Class<T> entityClass, String sql, Object param1, Object param2,
        Object param3, Object param4) throws HibernateException {
    Session session = getSession();//from  w ww  .  j  a  v  a  2 s  . c om

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(entityClass);
    query.setParameter(0, param1);
    query.setParameter(1, param2);
    query.setParameter(2, param3);
    query.setParameter(3, param4);
    List<T> result = query.list();

    return result;
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

/**
 * Execute sql, return the result.//from   w  ww.  ja  va  2s. co  m
 * 
 * @param sql
 * @param params
 * @return
 */
public static List<?> searchWithSql(String sql, Map<String, ?> params) throws HibernateException {
    Session session = getSession();
    SQLQuery query = session.createSQLQuery(sql);

    if (params != null) {
        Iterator<String> iterator = params.keySet().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            query.setParameter(key, params.get(key));
        }
    }

    List<?> result = query.list();

    return result;
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

/**
 * Execute sql, return the result./*from  ww w  .  ja  v  a 2 s  .  c om*/
 * 
 * @param sql
 * @param params
 * @return
 */
@SuppressWarnings("unchecked")
public static <T> List<T> searchWithSql(String sql, Map<String, ?> params, Class<T> clazz)
        throws HibernateException {
    Session session = getSession();
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(clazz);

    if (params != null) {
        Iterator<String> iterator = params.keySet().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            query.setParameter(key, params.get(key));
        }
    }

    List<T> result = query.list();

    return result;
}

---

From source file:com.globalsight.persistence.hibernate.HibernateUtil.java

License:Apache License

/**
 * Execute sql, return the result./*from   w  w w.  j av  a  2s  .co m*/
 * 
 * @param sql
 * @param params
 * @return
 */
public static List<?> searchWithSqlWithIn(String sql, Map<String, ?> params, Map<String, List<Object>> ins)
        throws HibernateException {
    Session session = getSession();
    SQLQuery query = session.createSQLQuery(sql);

    if (params != null) {
        Iterator<String> iterator = params.keySet().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            query.setParameter(key, params.get(key));
        }
    }

    if (ins != null) {
        Iterator<String> iterator = ins.keySet().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            query.setParameterList(key, ins.get(key));
        }
    }

    List<?> result = query.list();

    return result;
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ManifestBean> getUnitViewDrList(Long unitSSId, HttpServletRequest request) throws Exception { // to show DR or Bl list in View Dr page
    User user = (User) request.getSession().getAttribute("loginuser");
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder/* w  w w  .  j a v a 2  s .  c  o m*/
            .append(" SELECT fn.fileId AS fileId, fn.fileNo AS fileNo, fn.STATUS AS STATUS,fn.state as state,");
    queryBuilder.append(
            "(SELECT CONCAT_WS('~~~',ar.invoice_number,ar.status) FROM ar_red_invoice ar WHERE ar.bl_number = fn.fileNo ORDER BY ar.id DESC LIMIT 1) AS arInvoiceNumber,");
    queryBuilder.append(
            " getDisposion_UnLoc(fn.fileId) AS disposition, IF(fn.state ='BL' , BlNumberSystemForLclExports(bl.file_number_id),'') AS blNo,");
    queryBuilder.append(
            " piece.total_piece as totalPieceCount, piece.total_weight_imperial as totalWeightImperial ,piece.total_volume_imperial as totalVolumeImperial, ");
    queryBuilder.append(
            " blPiece.blCft as blCft ,blPiece.blCbm as blCbm,blPiece.blLbs as blLbs ,blPiece.blKgs as blKgs, ");
    queryBuilder.append(" IF(fn.state = 'BL',bl.rate_type,'') AS rateType,  ");
    queryBuilder.append(
            " (SELECT SUM(chg.ap_amount) FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND deleted = '0' AND chg.ap_gl_mapping_id IN (SELECT id FROM gl_mapping WHERE charge_code = 'FFCOMM' AND shipment_type='LCLE')) AS ffComm,  ");
    queryBuilder.append(
            " IF(fn.state = 'BL',TradingPartnerAcctName(bl.ship_acct_no),TradingPartnerAcctName(b.ship_acct_no)) AS shipperName,  ");
    queryBuilder.append(
            " IF(fn.state = 'BL',TradingPartnerAcctName(bl.cons_acct_no),TradingPartnerAcctName(b.cons_acct_no)) AS consigneeName,  ");
    queryBuilder.append(
            " IF(fn.state = 'BL',TradingPartnerAcctName(bl.fwd_acct_no),TradingPartnerAcctName(b.fwd_acct_no)) AS forwarderName,  ");
    queryBuilder.append(" UnLocationGetCodeByID(b.poo_id) AS origin,");
    queryBuilder.append(" UnLocationGetCodeByID (b.fd_id) AS destination,");
    queryBuilder.append(" UnLocationGetCodeByID (b.pol_id) AS pol,");
    queryBuilder.append(" UnLocationGetCodeByID (b.pod_id) AS pod,");
    queryBuilder.append(
            " IF(fn.state = 'BL',TerminalGetLocationByNo(bl.billing_terminal),TerminalGetLocationByNo(b.billing_terminal))AS terminalLocation,");
    queryBuilder.append(
            " IF(b.poo_pickup,(SELECT pickup_city FROM lcl_booking_pad WHERE file_number_id =fn.fileId),'')  AS pickupCity, ");
    queryBuilder
            .append(" UnLocationGetNameByID(b.poo_id) AS originName,getStateCode(b.poo_id) AS originState,");
    queryBuilder.append(
            " UnLocationGetNameByID(b.pol_id) AS polName,getStateCode(b.pol_id) AS polState,UnLocationGetNameByID(b.pod_id) AS podName,");
    queryBuilder.append(
            " getStateCode(b.pod_id) AS podCountry,UnLocationGetNameByID(b.fd_id) AS destinationName,getStateCode(b.fd_id) AS destinationCountry,");
    queryBuilder.append(
            " bl.billing_type AS billingType,IF(b.client_pwk_recvd = 1, 'Y', '') AS doc,(SELECT invoice_number FROM TRANSACTION WHERE drcpt = fn.fileNo LIMIT 1) AS blInvoiceNo,");
    queryBuilder.append(
            "(SELECT schedule_no FROM lcl_ss_header WHERE id = b.booked_ss_header_id) AS bookedVoyageNo,bl.posted_by_user_id AS postedByUserId,fn.haz AS hazmat,");
    queryBuilder.append(
            "(SELECT CONCAT_WS('~~~',GROUP_CONCAT(htc.code SEPARATOR '<br>'),COUNT(htc.code),GROUP_CONCAT(LEFT(htc.code, INSTR(htc.code, '/') - 1)))");
    queryBuilder.append(" FROM lcl_booking_hot_code htc WHERE htc.file_number_id = fn.fileId) AS hotCodes, ");
    queryBuilder.append(
            " IF(bl.file_number_id != fn.fileId , null ,IF(fn.state = 'BL',(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_bl_ac chg WHERE chg.file_number_id = bl.file_number_id AND chg.ar_bill_to_party = 'A'),(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND chg.ar_bill_to_party = 'A'))) AS colCharge, ");
    queryBuilder.append(
            " IF(bl.file_number_id != fn.fileId , null ,IF(fn.state = 'BL',(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_bl_ac chg WHERE chg.file_number_id = bl.file_number_id AND chg.ar_bill_to_party != 'A'),(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND chg.ar_bill_to_party != 'A'))) AS ppdCharge, ");
    queryBuilder.append(
            " IF(bl.file_number_id != fn.fileId , '' ,IF(fn.state = 'BL',(SELECT GROUP_CONCAT(DISTINCT chg.ar_bill_to_party SEPARATOR '/') FROM lcl_bl_ac chg WHERE chg.file_number_id = bl.file_number_id AND chg.ar_bill_to_party != 'A'),(SELECT GROUP_CONCAT(DISTINCT chg.ar_bill_to_party SEPARATOR '/') FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND chg.ar_bill_to_party != 'A'))) AS ppdParties, ");
    // checking condition for  status and classname label in list
    queryBuilder.append(" CASE   ");
    Boolean isManifest_Posted_Bl = new RoleDutyDAO().getRoleDetails("lcl_manifest_postedbl",
            user.getRole().getRoleId());
    if (isManifest_Posted_Bl) {
        queryBuilder.append(" WHEN fn.state <> 'BL' AND lbe.no_bl_required ='1'  THEN 'purpleBold'  ");
    }
    queryBuilder.append(" WHEN fn.state <> 'BL' THEN 'fileNo' ");
    queryBuilder.append(" WHEN bl.file_number_id != fn.fileId THEN 'greenBold14px' ");
    queryBuilder.append(
            " WHEN (bl.posted_by_user_id <> '' OR  bl.posted_by_user_id IS NOT NULL ) AND  fn.status <> 'M' THEN  'purpleBold' ");
    queryBuilder.append(" WHEN fn.status ='M' THEN 'greenBold14px' ");
    queryBuilder.append(
            " WHEN fn.state = 'BL' AND (bl.posted_by_user_id='' OR  bl.posted_by_user_id IS NULL) THEN 'fileNo' END  AS className, ");
    // ---------------------------------------------------------------STATUS LABEL-----------------------------------------------------------------------------
    queryBuilder.append(" CASE  ");
    if (isManifest_Posted_Bl) {
        queryBuilder.append("  WHEN fn.state <> 'BL' AND lbe.no_bl_required ='1'  THEN 'NO B/L Required'   ");
    }
    queryBuilder.append(" WHEN fn.state <> 'BL' THEN 'NoBL'");
    queryBuilder.append(" WHEN bl.file_number_id != fn.fileId THEN 'CONS'");
    queryBuilder.append(
            " WHEN (bl.posted_by_user_id <> '' OR  bl.posted_by_user_id IS NOT NULL ) AND  fn.status <> 'M' THEN  'POSTED' ");
    queryBuilder.append(" WHEN fn.status ='M' THEN 'MANIFESTED' ");
    queryBuilder.append(
            " WHEN fn.state = 'BL' AND (bl.posted_by_user_id = '' OR  bl.posted_by_user_id IS NULL) THEN 'POOL' END  AS statusLabel,");
    queryBuilder.append(" IF(lc.id IS NOT NULL,TRUE,FALSE) AS isCorrection, ");
    queryBuilder.append(" IF(lc.id IS NOT NULL,(SELECT SUM(ch.new_amount) FROM lcl_correction_Charge ch  ");
    queryBuilder.append(" JOIN gl_mapping gl ON gl.id = ch.gl_mapping_id WHERE  ");
    queryBuilder.append(" ch.correction_id=lc.id AND gl.charge_code='FTFFEE'),  ");
    queryBuilder.append(
            " (SELECT SUM(blac.ar_amount) FROM lcl_bl_Ac blac JOIN gl_mapping gl ON gl.id = blac.ar_gl_mapping_id  ");
    queryBuilder.append(" WHERE file_number_id = bl.file_number_id AND gl.charge_code='FTFFEE')) AS ftfFee  ");

    // --------------------------------------------------------MAIN SUB QUERY------------------------------------------------------------------------------------
    queryBuilder.append(" from ( ");
    queryBuilder.append(
            " SELECT f.id AS fileId, f.file_number AS fileNo, f.state AS state, f.status AS STATUS, lbp.hazmat AS haz ");
    queryBuilder.append(
            " FROM lcl_file_number f JOIN lcl_booking_piece lbp ON lbp.file_number_id = f.id JOIN lcl_booking_piece_unit u ON u.booking_piece_id = lbp.id ");
    queryBuilder.append(" WHERE u.lcl_unit_ss_id =:unitSdId ) fn");
    // ---------------------------------------------------------JOIN STATS HERE-----------------------------------------------------------------------------------
    queryBuilder.append(" LEFT JOIN lcl_booking b  ON fn.fileId = b.file_number_id ");
    // ------------------------------------- Conslidation logic is applied please verify ----------------------------------------------------------------
    queryBuilder
            .append("  LEFT JOIN lcl_bl bl  ON (bl.file_number_id = getHouseBLForConsolidateDr(fn.fileId))");
    queryBuilder.append(
            "  LEFT JOIN lcl_correction lc ON (lc.file_number_id = bl.file_number_id AND lc.status = 'A') ");

    if (isManifest_Posted_Bl) {
        queryBuilder.append(" JOIN lcl_booking_export lbe ON fn.fileId = lbe.file_number_id  ");
    }
    queryBuilder.append(getSumOfCommodityValues());
    queryBuilder.append(getSumOfBlCommodityValues());
    queryBuilder.append(" GROUP BY fn.fileId ");
    queryBuilder.append(" ORDER BY fn.fileNo ");

    SQLQuery query = getCurrentSession().createSQLQuery(queryBuilder.toString());
    query.setParameter("unitSdId", unitSSId);
    query.setResultTransformer(Transformers.aliasToBean(ManifestBean.class));
    query.addScalar("fileId", LongType.INSTANCE);
    query.addScalar("fileNo", StringType.INSTANCE);
    query.addScalar("status", StringType.INSTANCE);
    query.addScalar("state", StringType.INSTANCE);
    query.addScalar("arInvoiceNumber", StringType.INSTANCE);
    query.addScalar("disposition", StringType.INSTANCE);
    query.addScalar("blNo", StringType.INSTANCE);
    query.addScalar("totalPieceCount", IntegerType.INSTANCE);
    query.addScalar("totalWeightImperial", BigDecimalType.INSTANCE);
    query.addScalar("totalVolumeImperial", BigDecimalType.INSTANCE);
    query.addScalar("blCft", BigDecimalType.INSTANCE);
    query.addScalar("blCbm", BigDecimalType.INSTANCE);
    query.addScalar("blKgs", BigDecimalType.INSTANCE);
    query.addScalar("blLbs", BigDecimalType.INSTANCE);
    query.addScalar("rateType", StringType.INSTANCE);
    query.addScalar("ffComm", BigDecimalType.INSTANCE);
    query.addScalar("shipperName", StringType.INSTANCE);
    query.addScalar("consigneeName", StringType.INSTANCE);
    query.addScalar("forwarderName", StringType.INSTANCE);
    query.addScalar("origin", StringType.INSTANCE);
    query.addScalar("destination", StringType.INSTANCE);
    query.addScalar("pol", StringType.INSTANCE);
    query.addScalar("pod", StringType.INSTANCE);
    query.addScalar("terminalLocation", StringType.INSTANCE);
    query.addScalar("pickupCity", StringType.INSTANCE);
    query.addScalar("originName", StringType.INSTANCE);
    query.addScalar("originState", StringType.INSTANCE);
    query.addScalar("polName", StringType.INSTANCE);
    query.addScalar("polState", StringType.INSTANCE);
    query.addScalar("podName", StringType.INSTANCE);
    query.addScalar("podCountry", StringType.INSTANCE);
    query.addScalar("destinationName", StringType.INSTANCE);
    query.addScalar("destinationCountry", StringType.INSTANCE);
    query.addScalar("billingType", StringType.INSTANCE);
    query.addScalar("doc", StringType.INSTANCE);
    query.addScalar("blInvoiceNo", StringType.INSTANCE);
    query.addScalar("bookedVoyageNo", StringType.INSTANCE);
    query.addScalar("hotCodes", StringType.INSTANCE);
    query.addScalar("postedByUserId", IntegerType.INSTANCE);
    query.addScalar("hazmat", BooleanType.INSTANCE);
    query.addScalar("colCharge", BigDecimalType.INSTANCE);
    query.addScalar("ppdCharge", BigDecimalType.INSTANCE);
    query.addScalar("ppdParties", StringType.INSTANCE);
    query.addScalar("className", StringType.INSTANCE);
    query.addScalar("statusLabel", StringType.INSTANCE);
    query.addScalar("isCorrection", BooleanType.INSTANCE);
    query.addScalar("ftfFee", BigDecimalType.INSTANCE);
    List<ManifestBean> drList = query.list();
    return drList;
}

---

• «
• 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
• »