Example usage for org.hibernate SQLQuery setResultTransformer

List of usage examples for org.hibernate SQLQuery setResultTransformer

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

Introduction

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

Prototype

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

Source Link

Document

Set a strategy for handling the query results.

Usage

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

public LclModel getPickedVoyageByFileId(Long fileId, String serviceType) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(" SELECT lsh.schedule_no as scheduleNo,lu.unit_no as unitNo ");
    queryStr.append(" FROM lcl_booking_piece lbp ");
    queryStr.append(" JOIN lcl_booking_piece_unit lbpu ON lbpu.booking_piece_id=lbp.id ");
    queryStr.append(" JOIN lcl_unit_ss lus ON lus.id=lbpu.lcl_unit_ss_id");
    queryStr.append(" JOIN lcl_unit lu ON lu.id=lus.unit_id");
    queryStr.append(" JOIN lcl_ss_header lsh ON lsh.id=lus.ss_header_id");
    queryStr.append(" WHERE lbp.file_number_id=:fileId");
    queryStr.append(" AND lsh.service_type=:serviceType");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.setParameter("serviceType", serviceType);
    query.setResultTransformer(Transformers.aliasToBean(LclModel.class));
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    List<LclModel> pickedList = query.list();
    if (pickedList != null && !pickedList.isEmpty()) {
        return pickedList.get(0);
    }//from w  w  w  . j  av a2 s  .co  m
    return null;
}

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  w ww. j  a  v  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 List<ExportVoyageSearchModel> searchVoyageDetails(String voyageNumber) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(// w  w w  .j  a va  2  s  .  co  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.heimaide.server.common.persistence.BaseDao.java

License:Open Source License

public <E> List<E> findBySql(String sqlString, Parameter parameter, Class<?> resultClass,
        ResultTransformer rtf) {//from   www .j av  a 2  s  . 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.iluwatar.cqrs.queries.QueryServiceImpl.java

License:Open Source License

@Override
public Author getAuthorByUsername(String username) {
    Author authorDTo = null;//w  w w. ja  v a2s  . c o  m
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session
                .createSQLQuery("SELECT a.username as \"username\", a.name as \"name\", a.email as \"email\""
                        + "FROM Author a where a.username=:username");
        sqlQuery.setParameter("username", username);
        authorDTo = (Author) sqlQuery.setResultTransformer(Transformers.aliasToBean(Author.class))
                .uniqueResult();
    }
    return authorDTo;
}

From source file:com.iluwatar.cqrs.queries.QueryServiceImpl.java

License:Open Source License

@Override
public Book getBook(String title) {
    Book bookDTo = null;// ww w  .  j a  v  a  2 s  .c  o m
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session.createSQLQuery(
                "SELECT b.title as \"title\", b.price as \"price\"" + " FROM Book b where b.title=:title");
        sqlQuery.setParameter("title", title);
        bookDTo = (Book) sqlQuery.setResultTransformer(Transformers.aliasToBean(Book.class)).uniqueResult();
    }
    return bookDTo;
}

From source file:com.iluwatar.cqrs.queries.QueryServiceImpl.java

License:Open Source License

@Override
public List<Book> getAuthorBooks(String username) {
    List<Book> bookDTos = null;
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session.createSQLQuery("SELECT b.title as \"title\", b.price as \"price\""
                + " FROM Author a , Book b where b.author_id = a.id and a.username=:username");
        sqlQuery.setParameter("username", username);
        bookDTos = sqlQuery.setResultTransformer(Transformers.aliasToBean(Book.class)).list();
    }/*from  w ww  .  ja v  a  2s  .c  o m*/
    return bookDTos;
}

From source file:com.impetus.client.rdbms.HibernateClient.java

License:Apache License

/**
 * Find.// w  w  w .j a  v a2s  . c om
 * 
 * @param nativeQuery
 *            the native fquery
 * @param relations
 *            the relations
 * @param m
 *            the m
 * @return the list
 */
public List find(String nativeQuery, List<String> relations, EntityMetadata m) {
    List entities = new ArrayList();

    s = getStatelessSession();

    SQLQuery q = s.createSQLQuery(nativeQuery);
    q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

    List result = q.list();

    try {
        MetamodelImpl metaModel = (MetamodelImpl) kunderaMetadata.getApplicationMetadata()
                .getMetamodel(m.getPersistenceUnit());

        EntityType entityType = metaModel.entity(m.getEntityClazz());

        List<AbstractManagedType> subManagedType = ((AbstractManagedType) entityType).getSubManagedType();
        for (Object o : result) {
            Map<String, Object> relationValue = null;
            Object entity = null;
            EntityMetadata subEntityMetadata = null;
            if (!subManagedType.isEmpty()) {
                for (AbstractManagedType subEntity : subManagedType) {
                    String discColumn = subEntity.getDiscriminatorColumn();
                    String disColValue = subEntity.getDiscriminatorValue();
                    Object value = ((Map<String, Object>) o).get(discColumn);
                    if (value != null && value.toString().equals(disColValue)) {
                        subEntityMetadata = KunderaMetadataManager.getEntityMetadata(kunderaMetadata,
                                subEntity.getJavaType());
                        break;
                    }
                }
                entity = instantiateEntity(subEntityMetadata.getEntityClazz(), entity);
                relationValue = HibernateUtils.getTranslatedObject(kunderaMetadata, entity,
                        (Map<String, Object>) o, m);

            } else {
                entity = instantiateEntity(m.getEntityClazz(), entity);
                relationValue = HibernateUtils.getTranslatedObject(kunderaMetadata, entity,
                        (Map<String, Object>) o, m);
            }

            if (relationValue != null && !relationValue.isEmpty()) {
                entity = new EnhanceEntity(entity, PropertyAccessorHelper.getId(entity, m), relationValue);
            }
            entities.add(entity);
        }
        return entities;
    } catch (Exception e) {
        if (e.getMessage().equals("Can not be translated into entity.")) {
            return result;
        }
        throw new EntityReaderException(e);
    }
}

From source file:com.istarindia.cms.controller.EditQuestionController.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from ww w  .  j ava  2 s .co  m
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    AssessmentDAO assessmentDAO = new AssessmentDAO();
    int assessment_id = Integer.parseInt(request.getParameter("assessment_id"));
    int question_id = Integer.parseInt(request.getParameter("question_id"));
    String[] learningObjectives = null;
    StringBuffer lo_ids = new StringBuffer();
    StringBuffer sql = new StringBuffer();

    Assessment assessment = assessmentDAO.findById(assessment_id);

    learningObjectives = (String[]) request.getParameterMap().get("selected_items");
    for (int i = 0; learningObjectives != null && i < learningObjectives.length; i++) {
        lo_ids.append(learningObjectives[i]);
        if (i < learningObjectives.length - 1) {
            lo_ids.append(',');
        }
    }

    if (!request.getParameterMap().containsKey("only_learning_objevtives")) {
        sql.append(" delete from learning_objective_question where questionid = " + question_id);

        if (lo_ids.length() > 0) {
            sql.append("; INSERT INTO learning_objective_question (learning_objectiveid, questionid) "
                    + "SELECT lo. ID, " + question_id + " FROM learning_objective lo WHERE   lo.id IN ("
                    + lo_ids + ")");
        }

        Question question = (new QuestionDAO()).findById(question_id);
        String question_text = request.getParameter("question_text");
        String question_type = request.getParameter("question_type");
        int difficulty_level = Integer.parseInt(request.getParameter("difficulty_level").toString());
        int duration_in_sec = Integer.parseInt(request.getParameter("duration_in_sec"));

        QuestionService service = new QuestionService();
        service.updateQuestion(question_id, question_text, question_type, difficulty_level, duration_in_sec);

        OptionService opService = new OptionService();

        String[] answers = request.getParameterValues("answers");
        Integer[] optionValue = new Integer[5];

        if (answers != null) {
            for (int i = 0; i < 5; i++) {
                optionValue[i] = null;
            }
            for (int j = 0; j < answers.length; j++) {
                optionValue[Integer.parseInt(answers[j]) - 1] = 1;
            }
        }

        opService.updateNewOption(Integer.parseInt(request.getParameter("option1_id")),
                request.getParameter("option1"), question, optionValue[0]);
        opService.updateNewOption(Integer.parseInt(request.getParameter("option2_id")),
                request.getParameter("option2"), question, optionValue[1]);
        opService.updateNewOption(Integer.parseInt(request.getParameter("option3_id")),
                request.getParameter("option3"), question, optionValue[2]);
        opService.updateNewOption(Integer.parseInt(request.getParameter("option4_id")),
                request.getParameter("option4"), question, optionValue[3]);
        opService.updateNewOption(Integer.parseInt(request.getParameter("option5_id")),
                request.getParameter("option5"), question, optionValue[4]);

    } else if (request.getParameter("only_learning_objevtives").toString().equalsIgnoreCase("true")) {
        sql.append("INSERT INTO learning_objective_question (learning_objectiveid, questionid) "
                + "SELECT lo. ID, " + question_id + " FROM learning_objective lo WHERE   lo.id IN (" + lo_ids
                + ")");
    }

    try {
        IstarUserDAO dao = new IstarUserDAO();

        Session session = dao.getSession();
        SQLQuery query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        int result = query.executeUpdate();
        session.beginTransaction().commit();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    response.sendRedirect("/content/edit_lesson?task_id=" + assessment.getLesson().getTask().getId()
            + "&assessment_id=" + assessment_id + "&question_id=" + question_id);
}