List of usage examples for org.hibernate SQLQuery list
List<R> list();
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(/*w w w.ja v a 2 s.co 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 List<ExportVoyageSearchModel> searchVoyageDetails(String voyageNumber) throws Exception { StringBuilder queryStr = new StringBuilder(); queryStr.append(//from www .j a v a 2 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 List<ExportVoyageSearchModel> getMultiBookingSearchList(LclUnitsScheduleForm lclUnitsScheduleForm) throws Exception { StringBuilder queryStr = new StringBuilder(); queryStr.append("SELECT lmb.sp_booking_no AS bookingNo,"); queryStr.append(" lsh.id AS ssHeaderId,lsh.schedule_no as scheduleNo,"); queryStr.append(" UnLocationGetCodeByID (lsh.destination_id) AS departPierUnloc,"); queryStr.append(" UnLocationGetCodeByID(lsh.origin_id) AS arrivalPierUnloc,"); queryStr.append(" UnLocationGetNameStateCntryByID (lsh.origin_id) AS arrivalPier,"); queryStr.append(" UnLocationGetNameStateCntryByID (lsh.destination_id) AS departPier,"); queryStr.append(" lsh.origin_id as pooId,lsh.destination_id as fdId "); queryStr.append(" FROM lcl_ss_masterbl lmb "); queryStr.append(" JOIN lcl_ss_header lsh ON lsh.id=lmb.ss_header_id "); queryStr.append(" WHERE lmb.`sp_booking_no`="); queryStr.append("'").append(lclUnitsScheduleForm.getBookingNo()).append("' "); SQLQuery query = getSession().createSQLQuery(queryStr.toString()); query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class)); query.addScalar("ssHeaderId", StringType.INSTANCE); query.addScalar("scheduleNo", StringType.INSTANCE); query.addScalar("departPierUnloc", StringType.INSTANCE); query.addScalar("departPier", StringType.INSTANCE); query.addScalar("arrivalPierUnloc", StringType.INSTANCE); query.addScalar("arrivalPier", StringType.INSTANCE); query.addScalar("pooId", StringType.INSTANCE); query.addScalar("fdId", StringType.INSTANCE); query.addScalar("bookingNo", StringType.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 w w w . ja va 2 s. co m " 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.LclApplyDefaultDetailsDAO.java
public List getLclDefaultNameList(Integer userId) throws Exception { List<LabelValueBean> applyDefaultList = new ArrayList<LabelValueBean>(); SQLQuery query = getCurrentSession().createSQLQuery( "select id, upper(apply_default_name) from lcl_apply_default_details where user_id =:userId"); query.setInteger("userId", userId); List<Object[]> resultList = query.list(); for (Object[] row : resultList) { Integer cid = (Integer) row[0]; String cname = (String) row[1]; if (null != row[1] && null != row[0]) { applyDefaultList.add(new LabelValueBean(cname, cid.toString())); }//from ww w.j a va 2 s . c om } return applyDefaultList; }
From source file:com.green.common.persistence.BaseDao.java
License:Open Source License
/** * SQL /*from ww w . j a va 2s .co m*/ * @param sqlString * @param resultClass * @param parameter * @return */ @SuppressWarnings("unchecked") public <E> List<E> findBySql(String sqlString, Parameter parameter, Class<?> resultClass) { SQLQuery query = createSqlQuery(sqlString, parameter); setResultTransformer(query, resultClass); return query.list(); }
From source file:com.heimaide.server.common.persistence.BaseDao.java
License:Open Source License
public <E> List<E> findBySql(String sqlString, Parameter parameter, Class<?> resultClass, ResultTransformer rtf) {// w w w . j a va2s . c o m SQLQuery query = createSqlQuery(sqlString, parameter); if (rtf != null) { query.setResultTransformer(rtf); } else { setResultTransformer(query, resultClass); } return query.list(); }
From source file:com.hibernate.dao.CarinfoDAO.java
public CarInfoJSON selectcarinfo(String phonenum) { // session//from w ww . j a v a2 s . c o m Session session = HibernateSessionFactory.getSession(); CarInfoJSON carinfo = new CarInfoJSON(); Transaction tx = session.beginTransaction(); tx.begin(); SQLQuery query = session.createSQLQuery("SELECT * FROM car_info where DriverID=:phonenum"); query.setParameter("phonenum", phonenum); try { Object[] result = (Object[]) query.list().get(0); tx.commit(); carinfo.setId(result[0].toString()); carinfo.setCarNum(result[1].toString()); carinfo.setDriverId(result[2].toString()); carinfo.setCarBrand(result[3].toString()); carinfo.setCarModel(result[4].toString()); carinfo.setCarColor(result[5].toString()); carinfo.setCarCapacity(result[6].toString()); // carinfo.setDrivedYears(result[7].toString()); // carinfo.setCarPohto(result[8].toString()); // carinfo.setDriveLicense(result[9].toString()); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); // } finally { // session session.close(); } return carinfo; }
From source file:com.hrms.manager.GasManager.java
public boolean searchEmployeee(Gasdto emp) { Session s = HibernateUtil.getSessionFactory().openSession(); Transaction t = s.beginTransaction(); int empId = emp.getEmpId(); // String empName=emp.getEmpName(); // Criteria cr=null; // String aadharNumber=emp.getAadharNumber(); try {/*w ww . j ava 2s . c o m*/ String sql = "select quarter_id,emp_id from quarter_allotment where emp_id=:empId"; SQLQuery query = s.createSQLQuery(sql); query.setParameter("empId", empId); List<QuarterAllotment> list = query.list(); if (list.size() > 0) { s.close(); return true; } } catch (HibernateException h) { System.out.println("HibernateException during Searching:" + h); } catch (Exception ex) { System.out.println("Exception during Searching:" + ex); } finally { // s.close(); } return false; }
From source file:com.hrms.manager.GasManager.java
public List consumerno(Gasdto q) { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction tx = session.beginTransaction(); List<GasConnectionMaster> list1 = null; int empId = q.getEmpId(); String quartertype = null;/*ww w . j av a 2 s .c om*/ String sql = null; List quartercode; // List l=null; List<GasAllotment> list = (List<GasAllotment>) session.createCriteria(GasAllotment.class).list(); if (list.isEmpty()) { Criteria cr = session.createCriteria(GasConnectionMaster.class).setProjection( Projections.projectionList().add(Projections.property("consumerNumber"), "consumerNumber")); list1 = cr.list(); System.out.println(list1); return list1; } else sql = "select r.consumer_number from gas_connection_master r where r.connection_id not in (select connection_id from gas_allotment) "; SQLQuery query = session.createSQLQuery(sql); // query.setParameter("quartertype",quartertype); quartercode = query.list(); // System.out.println(quartercode); return quartercode; }