Example usage for org.hibernate Query setResultTransformer

List of usage examples for org.hibernate Query setResultTransformer

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setResultTransformer

Introduction

In this page you can find the example usage for org.hibernate Query setResultTransformer.

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:com.registryKit.survey.surveyDAO.java

/**
 * The 'getSurveyPages' function will return all pages associated with the survey.
 * //from ww w.j  a v  a 2  s. co  m
 * @param surveyId  The id of the passed in survey.
 * @param getQuestions  Boolean value to decide to pull questions for each page
 * @return
 * @throws Exception 
 */
@SuppressWarnings("unchecked")
public List<SurveyPages> getSurveyPages(Integer surveyId, boolean getQuestions, Integer clientId,
        Integer engagementId, Integer completedSurveyId) throws Exception {

    List<SurveyPages> surveyPagesList;

    if (completedSurveyId > 0) {
        Query query = sessionFactory.getCurrentSession().createSQLQuery(
                "SELECT * from survey_Pages where surveyId = :surveyId and survey_Pages.id in (select surveyPageId from patient_completedSurveyAnswers where completedSurveyid = :completedSurveyId)")
                .setParameter("surveyId", surveyId).setParameter("completedSurveyId", completedSurveyId);

        query.setResultTransformer(Transformers.aliasToBean(SurveyPages.class));

        surveyPagesList = query.list();

    } else {
        Criteria criteria = sessionFactory.getCurrentSession().createCriteria(SurveyPages.class);
        criteria.add(Restrictions.eq("surveyId", surveyId));
        criteria.addOrder(Order.asc("pageNum"));

        surveyPagesList = criteria.list();

    }

    List<SurveyPages> surveyPagesListForReturn = new ArrayList<SurveyPages>();

    if (getQuestions) {
        //we populate the survey questions here 
        for (SurveyPages sp : surveyPagesList) {
            List<SurveyQuestions> sqs = getSurveyQuestions(sp.getId(), 0, completedSurveyId);

            for (SurveyQuestions question : sqs) {
                if (question.getAnswerTypeId() == 1 || question.getAnswerTypeId() == 2) {
                    question.setquestionChoices(
                            getQuestionChoices(question.getId(), question.isAlphabeticallySort()));
                }

                if (completedSurveyId > 0) {
                    Query qAns = sessionFactory.getCurrentSession().createQuery(
                            "from patientCompletedSurveyAnswers where completedSurveyId = :completedSurveyId and questionId = :questionId");
                    qAns.setParameter("completedSurveyId", completedSurveyId);
                    qAns.setParameter("questionId", question.getId());

                    patientCompletedSurveyAnswers surveyAnswer = (patientCompletedSurveyAnswers) qAns
                            .uniqueResult();

                    if (surveyAnswer != null) {
                        if (surveyAnswer.getAnswerId() > 0) {
                            question.setQuestionValue(String.valueOf(surveyAnswer.getAnswerId()));
                        } else {
                            question.setQuestionValue(surveyAnswer.getAnswerText());
                        }
                    }
                } else {
                    if (question.isAutoPopulateFromField() == true) {
                        dataElements field = clientDAO.getFieldDetails(question.getSaveToFieldId());
                        question.setQuestionValue(engagementDAO.getTableData(field.getSaveToTableName(),
                                field.getSaveToTableCol(), engagementId, clientId));
                    }
                }
            }

            sp.setSurveyQuestions(sqs);
            surveyPagesListForReturn.add(sp);
        }
    } else {
        surveyPagesListForReturn = surveyPagesList;
    }

    return surveyPagesListForReturn;
}

From source file:com.registryKit.survey.surveyDAO.java

/**
 * The 'getSurveyQuestions' function will return the questions associated with the survey
 * /*from   ww  w.j  av a 2  s.c  o  m*/
 * @param surveyPageId  The id for the current page
 * @return
 * @throws Exception 
 */
@SuppressWarnings("unchecked")
public List<SurveyQuestions> getSurveyQuestions(Integer surveyPageId, Integer goToQuestion,
        Integer completedSurveyId) throws Exception {

    List<SurveyQuestions> surveyQuestions = null;

    if (completedSurveyId > 0) {
        Query query = sessionFactory.getCurrentSession().createSQLQuery(
                "SELECT * from survey_questions where surveyPageId = :surveyPageId and survey_questions.id in (select questionId from patient_completedSurveyAnswers where completedSurveyid = :completedSurveyId) order by questionNum")
                .setParameter("surveyPageId", surveyPageId)
                .setParameter("completedSurveyId", completedSurveyId);

        query.setResultTransformer(Transformers.aliasToBean(SurveyQuestions.class));

        surveyQuestions = query.list();

    } else {
        Criteria criteria = sessionFactory.getCurrentSession().createCriteria(SurveyQuestions.class);
        criteria.add(Restrictions.eq("surveyPageId", surveyPageId));
        criteria.add(Restrictions.eq("deleted", false));
        criteria.add(Restrictions.eq("hide", false));

        if (goToQuestion > 0) {
            Criteria goToQuestioncriteria = sessionFactory.getCurrentSession()
                    .createCriteria(SurveyQuestions.class);
            goToQuestioncriteria.add(Restrictions.eq("id", goToQuestion));

            SurveyQuestions goToQuestionDetails = (SurveyQuestions) goToQuestioncriteria.uniqueResult();

            criteria.add(Restrictions.ge("questionNum", goToQuestionDetails.getQuestionNum()));
        }

        criteria.addOrder(Order.asc("questionNum"));
        surveyQuestions = criteria.list();
    }

    for (SurveyQuestions question : surveyQuestions) {
        if (question.getValidationId() > 0) {
            question.setValidation(clientDAO.getValidationType(question.getValidationId()));
        }
    }

    return surveyQuestions;
}

From source file:com.registryKit.survey.surveyDAO.java

/**
 * The 'getPatientSurveysNoEngagement' function will return a list of surveys not associated to any 
 * engagement. /*from  ww  w. java 2  s  . c o  m*/
 * 
 * @param clientId
 * @return
 * @throws Exception 
 */
public List<patientCompletedSurveys> getPatientSurveysNoEngagement(Integer clientId, Integer programId)
        throws Exception {

    Query query = sessionFactory.getCurrentSession().createSQLQuery(
            "SELECT a.*, b.title as surveyTitle, CONCAT(c.firstName, ' ', c.lastName) as staffMember FROM patient_completedSurveys a inner join surveys b on b.id = a.surveyId inner join users c on c.id = a.systemUserId where a.programId = :programId and a.programpatientId = :clientId order by a.dateCreated desc")
            .setParameter("programId", programId).setParameter("clientId", clientId);

    query.setResultTransformer(Transformers.aliasToBean(patientCompletedSurveys.class));

    List<patientCompletedSurveys> surveys = query.list();

    return surveys;
}

From source file:com.registryKit.survey.surveyDAO.java

/**
 * /*w  w w.  ja v a  2s . c  o  m*/
 * @param clientId
 * @param engagementId
 * @return
 * @throws Exception 
 */
public List<patientCompletedSurveys> getPatientEngagementSurveys(Integer clientId, Integer engagementId)
        throws Exception {

    Query query = sessionFactory.getCurrentSession().createSQLQuery(
            "SELECT a.*, b.title as surveyTitle, CONCAT(c.firstName, ' ', c.lastName) as staffMember FROM patient_completedSurveys a inner join surveys b on b.id = a.surveyId inner join users c on c.id = a.systemUserId where a.engagementId = :engagementId and a.programpatientId = :clientId order by a.dateCreated desc")
            .setParameter("engagementId", engagementId).setParameter("clientId", clientId);

    query.setResultTransformer(Transformers.aliasToBean(patientCompletedSurveys.class));

    List<patientCompletedSurveys> surveys = query.list();

    return surveys;

}

From source file:com.rta.vsd.data.service.impl.ChannelTestDataServiceImpl.java

/**
 * /*from   w w w.  j a  v  a  2 s.c o m*/
 * Gets a list of Test Results with their testing center and testing information by the vehicles plate details provided
 * 
 * @author Eldon Barrows
 * @param dsContext
 * @param retrieveArabicData
 * @param plateDetails
 * @param paginationValues
 * @return List<TestResult>
 * @throws VSDDataAccessException
 */
public List<TestResult> getVehicleTestResultsByPlateDetails(DataServiceContext dsContext,
        boolean retrieveArabicData, VehiclePlate plateDetails, PaginationParam paginationValues)
        throws VSDDataAccessException {
    logger.info("getVehicleTestResultsByPlateDetails -- START");
    List<TestResult> testResults = null;
    try {

        /*SELECT D.VIOLATION_TICKET_CODE VIOLATION_TICKET_CODE, D.REPORTED_DATE REPORTED_DATE, H.CHANNEL_PART_INST_NAME CHANNEL_PART_INST_NAME, K.PARTNER_NAME PARTNER_NAME,
        G.TEST_STATUS TEST_STATUS, I.TEST_TYPE_NAME TEST_TYPE_NAME 
        FROM 
        VSD_VEHICLE_INFO B,
        VSD_INSPECTION C,
        VSD_VIOLATION D,
        VSD_CHANNEL_DEFECT E,
        VSD_CHANNEL_DEFECT_TEST F,
        VSD_CHANNEL_TEST G,
        VSD_CHANNEL_PART_INST H,
        VSD_TEST_TYPE I,
        VSD_CHANNEL_PARTNER J,
        VSD_PARTNER K
        WHERE
        B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID
        AND C.VIOLATION_ID = D.VIOLATION_ID
        AND C.VIOLATION_ID = E.VIOLATION_ID
        AND E.CHANNEL_DEFECT_ID = F.CHANNEL_DEFECT_ID
        AND F.CHANNEL_TEST_ID = G.CHANNEL_TEST_ID
        AND E.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID
        AND G.TEST_TYPE_ID = I.TEST_TYPE_ID
        AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID
        AND J.PARTNER_ID = K.PARTNER_ID
        ORDER BY G.CREATED_TIMESTAMP DESC*/

        Session session = (Session) dsContext.getInternalContext();
        String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME;
        //         StringBuffer query = new StringBuffer("SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," 
        //         + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" ,  G.TEST_STATUS_A as \"testResultA\" " 
        //         + " FROM "+defaultSchema+".VSD_VEHICLE_INFO B, "+defaultSchema+".VSD_INSPECTION C, "+defaultSchema+".VSD_VIOLATION D, "+defaultSchema+".VSD_VIOLATION_TEST_FEE E, "+defaultSchema+".VSD_TEST_FEE F, "+defaultSchema+".VSD_CHANNEL_TEST G, " 
        //         + " "+defaultSchema+".VSD_CHANNEL_PART_INST H, "+defaultSchema+".VSD_TEST_TYPE I, "+defaultSchema+".VSD_CHANNEL_PARTNER J, "+defaultSchema+".VSD_PARTNER K " 
        //         + "   WHERE " 
        //         + " B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " 
        //         + " AND C.VIOLATION_ID = D.VIOLATION_ID "
        //         + " AND D.VIOLATION_ID = E.VIOLATION_ID "
        //         + " AND E.TEST_FEE_ID = F.TEST_FEE_ID "
        //         + " AND F.TEST_FEE_ID = G.TEST_FEE_ID "
        //         + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID "
        //         + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID "
        //         + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID "
        //         + "   AND J.PARTNER_ID = K.PARTNER_ID "
        //         + "   AND B.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND C.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND D.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND E.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND F.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND G.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND H.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND I.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND J.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         + "   AND K.IS_DELETED = '"+IDataService.BOOL_FALSE+"' "
        //         );
        //         
        //         if(plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) {
        //            query.append(" AND lower(B.VEHICLE_PLATE_CATEGORY) = '"+plateDetails.getPlateCategory().toLowerCase()+"'");
        //         }
        //         if(plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) {
        //            query.append(" AND lower(B.VEHICLE_PLATE_CODE) = '"+plateDetails.getPlateCode().toLowerCase()+"'");
        //         }
        //         if(plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) {
        //            query.append(" AND lower(B.VEHICLE_PLATE_NUMBER) = '"+plateDetails.getPlateNumber().toLowerCase()+"'");
        //         }
        //         if(plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) {
        //            query.append(" AND lower(B.VEHICLE_PLATE_SOURCE) = '"+plateDetails.getPlateSource().toLowerCase()+"'");
        //         }
        //         query.append(" ORDER BY G.CREATED_TIMESTAMP DESC ");
        StringBuffer query = new StringBuffer(
                "SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\",  G.TEST_STATUS_A as \"testResultA\" "
                        + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema
                        + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema
                        + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema
                        + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema
                        + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema
                        + ".VSD_PARTNER K " + " WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID "
                        + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID "
                        + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID "
                        + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID "
                        + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID "
                        + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID "
                        + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND B.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE
                        + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "
                        + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE
                        + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "
                        + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE
                        + "' ");
        if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) {
            query.append(" AND LOWER(B.VEHICLE_PLATE_CATEGORY) = '"
                    + plateDetails.getPlateCategory().toLowerCase() + "'");
        }
        if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) {
            query.append(
                    " AND LOWER(B.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'");
        }
        if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) {
            query.append(" AND LOWER(B.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase()
                    + "'");
        }
        if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) {
            query.append(" AND LOWER(B.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase()
                    + "'");
        }
        query.append(
                " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", CT.TEST_STATUS_A AS \"testResultA\" "
                        + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema
                        + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema
                        + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, "
                        + defaultSchema + ".VSD_PARTNER P " + " WHERE V.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID "
                        + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "
                        + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' "
                        + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' "
                        + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID "
                        + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = "
                        + ANNUAL_TEST_TYPE_CODE);
        if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) {
            query.append(" AND LOWER(V.VEHICLE_PLATE_CATEGORY) = '"
                    + plateDetails.getPlateCategory().toLowerCase() + "'");
        }
        if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) {
            query.append(
                    " AND LOWER(V.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'");
        }
        if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) {
            query.append(" AND LOWER(V.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase()
                    + "'");
        }
        if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) {
            query.append(" AND LOWER(V.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase()
                    + "'");
        }
        query.append(" ) ORDER BY \"resultDateTime\" DESC");
        Query hQuery = session.createSQLQuery(query.toString());
        if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) {
            hQuery = hQuery.setFirstResult(paginationValues.getFirstResult().intValue());
        }
        if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) {
            hQuery = hQuery.setMaxResults(paginationValues.getFetchedSize().intValue());
        }
        testResults = hQuery.setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list();

    } catch (Exception ex) {
        logger.error("An error occured in getVehicleTestResultsByPlateDetails()");
        throw new VSDDataAccessException(ex.getMessage(), ex);
    }
    logger.info("getVehicleTestResultsByPlateDetails -- END");
    return testResults;
}

From source file:com.rta.vsd.data.service.impl.ChannelTestDataServiceImpl.java

/**
 * //from  ww  w.j  a v a  2s . c  om
 * Gets a list of Test Results with their testing center and testing information by the vehicle owners trade license number
 * 
 * @author Eldon Barrows
 * @param dsContext
 * @param retrieveArabicData
 * @param tradeLicenseNumber
 * @param paginationValues
 * @return List<TestResult>
 * @throws VSDDataAccessException
 */
public List<TestResult> getVehicleTestResultsByTradeLicenseNumber(DataServiceContext dsContext,
        boolean retrieveArabicData, String tradeLicenseNumber, PaginationParam paginationValues)
        throws VSDDataAccessException {
    logger.info("getVehicleTestResultsByTradeLicenseNumber -- START");
    List<TestResult> testResults = null;
    try {

        Session session = (Session) dsContext.getInternalContext();
        String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME;
        StringBuffer query = new StringBuffer(
                "SELECT D.VIOLATION_TICKET_CODE as \"violationId\", D.REPORTED_DATE as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\","
                        + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" "
                        + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema
                        + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema
                        + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema
                        + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + " " + defaultSchema
                        + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema
                        + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + "   WHERE "
                        + " A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID "
                        + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID "
                        + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID "
                        + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID "
                        + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID "
                        + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID "
                        + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID "
                        + "   AND J.PARTNER_ID = K.PARTNER_ID " + "   AND A.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + "   AND B.IS_DELETED = '" + IDataService.BOOL_FALSE
                        + "' " + "   AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "
                        + "   AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + "   AND E.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + "   AND F.IS_DELETED = '" + IDataService.BOOL_FALSE
                        + "' " + "   AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "
                        + "   AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + "   AND I.IS_DELETED = '"
                        + IDataService.BOOL_FALSE + "' " + "   AND J.IS_DELETED = '" + IDataService.BOOL_FALSE
                        + "' " + "   AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' ");

        if (tradeLicenseNumber != null && !tradeLicenseNumber.equals("")) {
            query.append(" AND A.TRADE_LICENSE_NUMBER = '" + tradeLicenseNumber + "'");
        }
        query.append(" ORDER BY G.CREATED_TIMESTAMP DESC ");
        Query hQuery = session.createSQLQuery(query.toString());
        if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) {
            hQuery = hQuery.setFirstResult(paginationValues.getFirstResult().intValue());
        }
        if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) {
            hQuery = hQuery.setMaxResults(paginationValues.getFetchedSize().intValue());
        }
        testResults = hQuery.setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list();

    } catch (Exception ex) {
        logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()");
        throw new VSDDataAccessException(ex.getMessage(), ex);
    }
    logger.info("getVehicleTestResultsByTradeLicenseNumber -- END");
    return testResults;
}

From source file:com.salesmanager.core.service.order.impl.dao.OrderDao.java

License:Open Source License

public SearchOrderResponse searchOrderByCustomer(SearchOrdersCriteria searchCriteria) {

    Criteria criteria = super.getSession().createCriteria(Order.class)
            .add(Restrictions.eq("customerId", searchCriteria.getCustomerId()))
            .add(Restrictions.eq("merchantId", searchCriteria.getMerchantId()))
            .add(Restrictions.eq("channel", OrderConstants.ONLINE_CHANNEL))
            .addOrder(org.hibernate.criterion.Order.desc("orderId"));

    StringBuffer q = new StringBuffer();

    q.append(" select o from Order o where o.merchantId=:mId");
    q.append(" and channel=:channel");

    q.append(" and o.customerId = :cId");
    q.append(" and o.merchantId = :mId");
    q.append(" order by o.orderId desc");

    Query query = super.getSession().createQuery(q.toString());
    query.setInteger("channel", OrderConstants.ONLINE_CHANNEL);
    query.setInteger("mId", searchCriteria.getMerchantId());
    query.setLong("cId", searchCriteria.getCustomerId());
    query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

    criteria.setProjection(Projections.rowCount());
    Integer count = (Integer) criteria.uniqueResult();

    criteria.setProjection(null);/*w  ww  .  j ava  2 s . co m*/

    int max = searchCriteria.getQuantity();

    List list = null;
    if (max != -1 && count > 0) {
        query.setMaxResults(searchCriteria.getUpperLimit(count));
        query.setFirstResult(searchCriteria.getLowerLimit());
        list = query.list();
    } else {
        list = query.list();
    }

    SearchOrderResponse response = new SearchOrderResponse();
    response.setCount(count);
    response.setOrders(list);

    return response;

}

From source file:com.sccl.attech.common.persistence.BaseDao.java

License:Open Source License

/**
 * Map by sql./* w  w w  . j  av  a 2s.  c  o  m*/
 * ?sql map list
 * @param sql
 *            the sql
 * @param parameter
 *            the parameter
 * @return the list
 */
public List<Map<String, Object>> mapBySql(String sql, Parameter parameter) {
    Query query = createSqlQuery(sql, parameter);
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    return query.list();
}

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

@Override
public List<EvidenceProvider> getEvidenceProvidersByType(String type, Integer storeId) {
    List<EvidenceProvider> evidenceProviders = new ArrayList<EvidenceProvider>();

    StringBuilder sqlEvidenceProviders = new StringBuilder();
    sqlEvidenceProviders.append("select ep.* ");
    sqlEvidenceProviders.append(" from evidence_provider ep, evidence_provider_type ept ");
    sqlEvidenceProviders.append(" where ep.evidence_provider_type_id = ept.id ");
    sqlEvidenceProviders.append(" and ep.store_id = ").append(storeId).append(" ");
    sqlEvidenceProviders.append(" and ept.description = '").append(type).append("'");

    Session session = this.getSession();
    Query query = session.createSQLQuery(sqlEvidenceProviders.toString());
    query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    List<Map<String, Object>> list = query.list();

    for (Map<String, Object> row : list) {
        EvidenceProvider ep = new EvidenceProvider();
        ep.setId((Integer) row.get("id"));
        ep.setDefinitionData((String) row.get("definition_data"));
        ep.setDescription((String) row.get("description"));
        ep.setName((String) row.get("name"));

        evidenceProviders.add(ep);// w  w w  . j  av a 2  s.  com
    }

    return evidenceProviders;
}

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

@Override
public List<VadaroEvent> getLastVadaroEvents(Integer minutes, Integer storeId, Date timeEvidence) {

    List<VadaroEvent> ret = new ArrayList<VadaroEvent>();
    String date = DateFormatUtils.format(timeEvidence, "yyyy-MM-dd HH:mm:ss");
    StringBuilder sql = new StringBuilder();
    //        sql.append("select * from vadaro_event ");
    //        sql.append("where store_id = ").append(storeId);
    //        sql.append(" and server_time >= (now() - interval '").append(minutes).append(" minutes') ");
    //        sql.append(" order by server_time desc");
    sql.append("select * from vadaro_event ");
    sql.append("where store_id = ").append(storeId);
    sql.append(" and time >= (to_timestamp('").append(date).append("', 'YYYY-MM-DD HH24:MI:SS') - interval '")
            .append(minutes).append(" minutes') ");
    sql.append(" and time <= '").append(date).append("'");
    sql.append(" order by time desc");
    Session session = this.getSession();

    try {/*w  w  w .ja va2  s.  c o  m*/
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        for (Map<String, Object> row : list) {
            VadaroEvent event = new VadaroEvent();

            event.setId((Integer) row.get("id"));
            event.setAbandoned((Integer) row.get("abandoned"));
            event.setCameraName((String) row.get("camera_name"));
            event.setEntered((Integer) row.get("entered"));
            event.setExited((Integer) row.get("exited"));
            event.setLength((Integer) row.get("length"));
            event.setService((String) row.get("service"));
            event.setServiceTime((Double) row.get("service_time"));
            event.setTime((Date) row.get("time"));
            event.setWaitTime((Double) row.get("wait_time"));
            event.setServerTime((Date) row.get("server_time"));

            Store store = new Store();
            store.setId((Integer) row.get("store_id"));
            event.setStore(store);

            ret.add(event);
        }
    } catch (HibernateException e) {
        log.error(e, e);
    } finally {
        this.releaseSession(session);
    }

    return ret;
}