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.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<LclModel> getBlChargesValidate(Long fileId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*from  www .  jav  a2 s .  c o m*/
            " SELECT IF(chg.ar_bill_to_party = 'A',b.agent_acct_no,IF(chg.ar_bill_to_party = 'F',b.fwd_acct_no, ");
    queryStr.append("IF(chg.ar_bill_to_party = 'S',b.ship_acct_no, b.third_party_acct_no))) AS vendorName, ");
    queryStr.append("gm.Charge_code AS chargeCode,chg.ar_bill_to_party AS billToParty   ");
    queryStr.append("FROM lcl_bl b JOIN lcl_bl_ac chg ON b.file_number_id = chg.file_number_id  ");
    queryStr.append("JOIN gl_mapping gm ON gm.id = ar_gl_mapping_id ");
    queryStr.append(
            "WHERE chg.ar_amount > 0.00 AND b.file_number_id  =:fileId   GROUP BY  chg.ar_bill_to_party HAVING vendorName IS NULL ");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.setResultTransformer(Transformers.aliasToBean(LclModel.class));
    query.addScalar("vendorName", StringType.INSTANCE);
    query.addScalar("chargeCode", StringType.INSTANCE);
    query.addScalar("billToParty", StringType.INSTANCE);
    return query.list();
}

---

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

public boolean isPickedFile(Long fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(" SELECT if( count(*) > 0, true, false) as result  FROM lcl_booking_piece bp ");
    sb.append(" JOIN lcl_booking_piece_unit bpu ON bpu.`booking_piece_id` = bp.`id` ");
    sb.append(" JOIN lcl_unit_ss lus ON lus.id = bpu.`lcl_unit_ss_id` ");
    sb.append(" JOIN lcl_ss_detail lsd ON lsd.`ss_header_id` = lus.`ss_header_id` ");
    sb.append(" WHERE bp.`file_number_id` =:fileId ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult();

}

---

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

public boolean isDrContainCollectCharge(Long fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(" select if( count(*)>0 , true ,false ) as result from lcl_bl_ac ac  ");
    sb.append(" join lcl_bl bl on bl.file_number_id = ac.file_number_id ");
    sb.append(" where ac.file_number_id=:fileId  and ac.ar_bill_to_party = 'A' ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult();
}

---

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

public String getPickedDrBillToParty(Long fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(" SELECT GROUP_CONCAT(DISTINCT ac.`ar_bill_to_party`) AS billtToParty FROM lcl_bl_ac ac ");
    sb.append(" WHERE ac.`file_number_id` =:fileId ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    return (String) query.addScalar("billtToParty", StringType.INSTANCE).uniqueResult();
}

---

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

public List<ExportVoyageSearchModel> searchVoyageDetails(String voyageNumber) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(//from   www  . j  a v  a2  s . c o m
            " SELECT lclssh.id AS ssHeaderId,lclssh.service_type as serviceType,lclssd.id AS ssDetailId,");
    queryStr.append(" lclssh.schedule_no AS scheduleNo,lclssd.sp_acct_no AS carrierAcctNo,");
    queryStr.append(
            " (SELECT acct_name FROM trading_partner WHERE acct_no = lclssd.sp_acct_no LIMIT 1) AS carrierName,");
    queryStr.append(" (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append(" WHERE lclssh.id = luss.ss_header_id) AS unitcount,");
    queryStr.append(getAppendQueryForUnitNo());
    queryStr.append(" lclssd.sp_reference_name AS vesselName,lclssd.sp_reference_no AS ssVoyage,");
    queryStr.append(" UnLocationGetCodeByID (lclssd.departure_id) AS departPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lclssd.departure_id) AS departPier,");
    queryStr.append(" UnLocationGetCodeByID (lclssd.arrival_id) AS arrivalPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lclssd.arrival_id) AS arrivalPier,");
    queryStr.append(" lclssd.relay_lrd_override AS lrdOverride,lclssd.relay_lrd_override AS lrdOverrideDays, ");
    queryStr.append(
            " DATE_FORMAT(lclssd.general_lrdt, '%d-%b-%Y') AS polLrdDate,lclssd.general_lrdt as polLrdDates,");
    queryStr.append(" lclssd.sta as etaPodDates,lclssd.std as etaSailDates,");
    queryStr.append(
            " DATE_FORMAT(lclssd.std, '%d-%b-%Y') AS etaSailDate,DATE_FORMAT(lclssd.sta, '%d-%b-%Y') AS etaPodDate,");
    queryStr.append(" lclssh.datasource AS dataSource,");
    queryStr.append(" UserDetailsGetLoginNameByID (lclssh.entered_by_user_id) AS createdBy,");
    queryStr.append(" UserDetailsGetLoginNameByID (lclssh.owner_user_id) AS voyOwner,");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.status ='M') as manifestUnitCount, ");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.cob = 1 ) as cobUnitCount, ");
    queryStr.append("  (SELECT MAX(CASE WHEN lclssd.sta = luss.cob_datetime THEN 1 ");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus  WHERE lus.`ss_header_id` = lclssh.id)  =  ");
    queryStr.append(
            "  (SELECT COUNT(*) FROM lcl_unit_ss lus WHERE lus.`ss_header_id` = lclssh.id AND lus.cob_datetime IS NULL) THEN 2");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus ");
    queryStr.append("  WHERE lus.`ss_header_id` = lclssh.id)>1 AND luss.cob_datetime IS NULL THEN 3 ");
    queryStr.append("  WHEN lclssd.sta <> luss.cob_datetime THEN 4 ELSE 0 END ");
    queryStr.append("  ) AS vETA FROM lcl_unit_ss luss WHERE luss.ss_header_id = lclssh.id) AS verifiedEta ");
    queryStr.append(" FROM lcl_ss_header lclssh ");
    queryStr.append(" LEFT JOIN lcl_ss_detail lclssd ON (lclssh.id = lclssd.ss_header_id)");
    queryStr.append(" WHERE ");

    if (CommonUtils.isNotEmpty(voyageNumber)) {
        queryStr.append(" lclssh.schedule_no=:scheduleNo");
    }
    queryStr.append(" GROUP BY lclssh.schedule_no ");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    if (CommonUtils.isNotEmpty(voyageNumber)) {
        query.setParameter("scheduleNo", voyageNumber);
    }
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", StringType.INSTANCE);
    query.addScalar("ssDetailId", StringType.INSTANCE);
    query.addScalar("serviceType", StringType.INSTANCE);
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("carrierName", StringType.INSTANCE);
    query.addScalar("carrierAcctNo", StringType.INSTANCE);
    query.addScalar("unitcount", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    query.addScalar("vesselName", StringType.INSTANCE);
    query.addScalar("ssVoyage", StringType.INSTANCE);
    query.addScalar("departPierUnloc", StringType.INSTANCE);
    query.addScalar("departPier", StringType.INSTANCE);
    query.addScalar("arrivalPierUnloc", StringType.INSTANCE);
    query.addScalar("arrivalPier", StringType.INSTANCE);
    query.addScalar("lrdOverrideDays", StringType.INSTANCE);
    query.addScalar("polLrdDate", StringType.INSTANCE);
    query.addScalar("etaSailDate", StringType.INSTANCE);
    query.addScalar("etaPodDate", StringType.INSTANCE);
    query.addScalar("createdBy", StringType.INSTANCE);
    query.addScalar("voyOwner", StringType.INSTANCE);
    query.addScalar("dataSource", StringType.INSTANCE);
    query.addScalar("manifestUnitCount", StringType.INSTANCE);
    query.addScalar("cobUnitCount", StringType.INSTANCE);
    query.addScalar("verifiedEta", IntegerType.INSTANCE);
    return query.list();
}

---

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

public String validateGlAccount(String unitSsId, String originId) throws Exception {
    StringBuilder sb = new StringBuilder();
    StringBuilder result = new StringBuilder();
    sb.append(" SELECT CASE WHEN glAcct.glAccount IS NULL THEN ");
    sb.append(" CONCAT('<tr><td>',glAcct.fileNo,'</td><td>', ");
    sb.append(" glAcct.chargeCode,'</td><td class=\''red\''> No GL account is mappped.</td></tr>') ");
    sb.append(" WHEN IsValidGlAccount(glAcct.glAccount) = 0 THEN ");
    sb.append(" CONCAT('<tr><td>',glAcct.fileNo,'</td><td>', ");
    sb.append(//from ww w. j av  a2 s .  c om
            " glAcct.chargeCode,'</td><td>GL Account <span class=\''red\''>',glAcct.glAccount,'</span> is not valid one.</td></tr>') ");
    sb.append(" ELSE '' END  AS glAccountMsg ");
    sb.append(" FROM (SELECT lf.id AS fileId, lf.`file_number` AS fileNo, ");
    sb.append(" gl.`Charge_code` AS chargeCode, ");
    sb.append(" DeriveLCLExportGlAccount(gl.id,'','0',").append(originId).append(") AS glAccount ");
    sb.append(" FROM  lcl_bl_ac blac  ");
    sb.append(
            " JOIN gl_mapping gl ON (gl.id = blac.`ar_gl_mapping_id` and gl.shipment_type='LCLE' and gl.transaction_type = 'AR') ");
    sb.append(" JOIN lcl_file_number lf ON lf.id = blac.`file_number_id` ");
    sb.append(" JOIN lcl_booking_piece lbp ON lbp.`file_number_id` = lf.id ");
    sb.append(" JOIN lcl_booking_piece_unit bpu ON bpu.`booking_piece_id` = lbp.`id` ");
    sb.append(" JOIN lcl_unit_ss lus ON lus.`id` = bpu.`lcl_unit_ss_id` ");
    sb.append(" WHERE lus.id =:unitSsId ");
    sb.append(" GROUP BY gl.`bluescreen_chargecode` , lf.`id` )  AS  glAcct ");
    sb.append(" HAVING glAccountMsg <> '' ORDER BY glAcct.fileId DESC ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("unitSsId", unitSsId);
    List errorList = query.list();
    if (!errorList.isEmpty()) {
        for (Object obj : errorList) {
            result.append(obj.toString());
        }
    }
    return result.toString();
}

---

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeNotExist(String Code, String fileId) throws Exception {
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)<1,true,false) as result from lcl_booking_hot_code "
                    + " where code=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", Code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

---

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public void insertQuery(String fileId, String code, Integer user) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(/* w w w  . ja  v  a 2s.c om*/
            "insert into lcl_booking_hot_code(file_number_id,code,entered_datetime,entered_by_user_id,modified_datetime,modified_by_user_id) ");
    sb.append(" values(:fileId,:code,:now,:user,:now,:user)");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    query.setParameter("code", code);
    query.setParameter("now", new Date());
    query.setParameter("user", user);
    query.executeUpdate();
}

---

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeExistForThreeDigit(String fileId, String type, String Code) throws Exception { // type is not but exists method will affected
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)<1,true,false) as result from lcl_booking_hot_code "
                    + " where SUBSTRING_INDEX(code,'/',1)=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", Code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

---

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeValidate(Long fileId, String code) throws Exception { // type is not but exists method will affected
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)>0,true,false) as result from lcl_booking_hot_code "
                    + " where SUBSTRING_INDEX(code,'/',1)=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

---

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