Example usage for org.hibernate SQLQuery list

List of usage examples 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.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;
}