Example usage for org.hibernate SQLQuery setMaxResults

List of usage examples for org.hibernate SQLQuery setMaxResults

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

Introduction

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

Prototype

@Override
    Query<R> setMaxResults(int maxResult);

Source Link

Usage

From source file:com.viettel.hqmc.DAOHE.FeePaymentInfoDAOHE.java

/**
 * danh sach hoa don doanh nghiep//from  www .  j  a v  a 2s .c  o  m
 *
 * @param fileId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstPayment(Long fileId, int start, int count, String sortField) {

    //        String sql = "from Fee f left join Fee_Payment_Info fpi on f.fee_Id = fpi.fee_Id where f.fee_Id "
    //                     + "in (select fp.fee_Id from Fee_Procedure fp where fp.procedure_Id = ((select fi.file_Type from Files fi where fi.file_Id = ?)) union "
    //                     + "select ff.fee_Id from Fee_File ff where ff.file_Id = ? )  and f.is_Active = 1";
    String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id where fpi.file_id = ? and f.is_Active=1 and fpi.is_Active=1";
    SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery("select count(*) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery(
            "select f.fee_Id,f.fee_Name,f.description,fpi.cost,f.fee_Type,fpi.status,fpi.fee_Payment_Type_Id, f.price,fpi.payment_Person,fpi.payment_Date,fpi.payment_Info,fpi.bill_path  "
                    + sql);

    query.setLong(0, fileId);
    //query.setLong(1, fileId);
    countQuery.setParameter(0, fileId);
    //countQuery.setParameter(1, fileId);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();

    for (int i = 0; i < lstResult.size(); i++) {
        Object[] row = (Object[]) lstResult.get(i);
        if (row.length > 0) {
            if (row[0] != null && !"".equals(row[0])) {
                item.setFeeId(Long.parseLong(row[0].toString()));
            }
            if (row[1] != null && !"".equals(row[1])) {
                item.setFeeName(row[1].toString());
            }
            if (row[2] != null && !"".equals(row[2])) {
                item.setDescription(row[2].toString());
            }
            if (row[7] != null && !"".equals(row[7])) {
                item.setPrice(Long.parseLong(row[7].toString()));
            }
            if (row[4] != null && !"".equals(row[4])) {
                item.setFeeType(Long.parseLong(row[4].toString()));
            }
            if (row[5] != null && !"".equals(row[5])) {
                item.setStatus(Long.parseLong(row[5].toString()));
            }
            if (row[6] != null && !"".equals(row[6])) {
                item.setFeePaymentType(Long.parseLong(row[6].toString()));
            }
            if (row[8] != null && !"".equals(row[8])) {
                item.setPaymentPerson(row[8].toString());
            }
            if (row[9] != null && !"".equals(row[9])) {
                item.setPaymentDate(row[9].toString());
            }
            if (row[10] != null && !"".equals(row[10])) {
                item.setPaymentInfo(row[10].toString());
            }
            if (row[11] != null && !"".equals(row[11])) {
                item.setBillPath(row[11].toString());
            }
        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeePaymentInfoDAOHE.java

/**
 * quan ly nop phi//from   w  w  w . jav a  2  s .c o m
 *
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeeManage(int start, int count, String sortField) {
    String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id where fpi.fee_id in (select f.fee_id from fee f where f.fee_type = 1 )";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery(
            "select distinct f.file_code,f.product_name,fpi.payment_date,fpi.cost,fpi.bill_path,fpi.fee_payment_type_id,fpi.status,fpi.fee_id,fpi.file_id,fpi.payment_info_id,fpi.payment_person "
                    + sql);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();

    for (int i = 0; i < lstResult.size(); i++) {
        Object[] row = (Object[]) lstResult.get(i);
        if (row.length > 0) {
            if (row[0] != null && !"".equals(row[0])) {
                item.setFileCode(row[0].toString());
            }
            if (row[1] != null && !"".equals(row[1])) {
                item.setProductName(row[1].toString());
            }
            if (row[2] != null && !"".equals(row[2])) {
                item.setPaymentDate(row[2].toString());
            }
            if (row[3] != null && !"".equals(row[3])) {
                item.setCost(Long.parseLong(row[3].toString()));
            }
            if (row[7] != null && !"".equals(row[7])) {
                item.setFeeId(Long.parseLong(row[7].toString()));
            }
            if (row[4] != null && !"".equals(row[4])) {
                item.setBillPath(row[4].toString());
            }
            if (row[5] != null && !"".equals(row[5])) {
                item.setFeePaymentType(Long.parseLong(row[5].toString()));
            }
            if (row[6] != null && !"".equals(row[6])) {
                item.setStatus(Long.parseLong(row[6].toString()));
            }
            if (row[8] != null && !"".equals(row[8])) {
                item.setFileId(Long.parseLong(row[8].toString()));
            }
            if (row[9] != null && !"".equals(row[9])) {
                item.setPaymentInfoId(Long.parseLong(row[9].toString()));
            }
            if (row[10] != null && !"".equals(row[10])) {
                item.setPaymentPerson((row[10].toString()));
            }

        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.wm.framework.sh.dao.impl.BaseDaoImpl.java

License:Open Source License

/**??executeSQLFindByPagination <br>
 * ??SQL ? <br>/*w  w  w. j a v  a  2s .c o m*/
 * ? <br>
 * 20141223?11:26:15 
 * @see com.wm.framework.sh.dao.BaseDao#executeSQLFindByPagination(java.lang.String, com.fklvtu.framework.sh.util.Pagination, java.lang.Object[]) 
 * @param sql
 * @param pagination
 * @param params
 * @return
 */
public void executeSQLFindByPagination(String sql, String sqlTotalCount, Pagination<JSONObject> pagination,
        Object... params) {
    log.debug("executeSQLFind start");
    SQLQuery sqlQuery = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql);
    this.queryAttachParams(sqlQuery, params);
    int pageNo = pagination.getPageNo();
    int pageSize = pagination.getPageSize();
    sqlQuery.setFirstResult((pageNo - 1) * pageSize);
    sqlQuery.setMaxResults(pageSize);
    List<JSONObject> affected = sqlQuery.list();
    //
    String[] fields = this.buildFields(sqlTotalCount);
    //String[] fields = this.buildFields(sql);
    JSONArray ja = this.buildJSONArray(fields, affected);
    pagination.setFields(fields);
    pagination.setData(ja);

    this.sqlGetTotalCount(sql, pagination, params);
    log.debug("executeSQLFind end " + affected);

}

From source file:com.wm.framework.sh.dao.impl.BaseDaoImpl.java

License:Open Source License

@Override
public void executeSQLFindByPagination2(String sql, String sqlTotalCount, Pagination<JSONObject> pagination,
        Object... params) {/* w w w. java2  s .co m*/
    log.debug("executeSQLFind start");
    SQLQuery sqlQuery = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql);
    System.out.println("sql---->" + sql);
    this.queryAttachParams(sqlQuery, params);
    int pageNo = pagination.getPageNo();
    int pageSize = pagination.getPageSize();
    sqlQuery.setFirstResult((pageNo - 1) * pageSize);
    sqlQuery.setMaxResults(pageSize);
    List<JSONObject> affected = sqlQuery.list();
    //
    String[] fields = this.buildFields(sqlTotalCount);
    JSONArray ja = this.buildJSONArray(fields, affected);
    pagination.setFields(fields);
    pagination.setData(ja);
    log.debug("executeSQLFind end " + affected);

}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

@Override
public PageFinder<ApiKey> queryApiKey(ApiKeyMetadata apiKeyMetadata, Query query) throws Exception {
    StringBuilder sqlPrefixBuilder = new StringBuilder();
    sqlPrefixBuilder.append(" select ");
    sqlPrefixBuilder.append(//from  w w  w  .j  a  v  a  2s.c  o m
            " t1.id, t1.app_key as appKey, t1.app_secret as appSecret, t1.status, t1.update_user as updateUser, t1.update_time as updateTime ");
    StringBuilder sqlSuffixBuilder = new StringBuilder();
    sqlSuffixBuilder.append(" from ");
    sqlSuffixBuilder.append(" tbl_merchant_api_key t1 ");
    sqlSuffixBuilder.append(" left join ");
    sqlSuffixBuilder.append(" tbl_merchant_api_key_metadata t2 ");
    sqlSuffixBuilder.append(" on(t1.id = t2.key_id) ");
    sqlSuffixBuilder.append(" left join ");
    sqlSuffixBuilder.append(" ( ");
    sqlSuffixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement());
    sqlSuffixBuilder.append(" ) t3 ");
    sqlSuffixBuilder.append(" on (t2.metadata_val = t3.metadata_val) ");
    sqlSuffixBuilder.append(" where 1 = 1 ");

    // ?
    Map<String, Object> properties = new HashMap<String, Object>();
    if (apiKeyMetadata != null) {
        if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataTag())) {
            sqlSuffixBuilder.append(" and t3.metadata_tag like :metadata_tag");
            properties.put("metadata_tag", '%' + apiKeyMetadata.getMetadataTag() + '%');
        }
        if (apiKeyMetadata.getMetadataKey() != null) {
            sqlSuffixBuilder.append(" and t2.metadata_key = :metadata_key ");
            properties.put("metadata_key", apiKeyMetadata.getMetadataKey().name());
        }
        if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataVal())) {
            sqlSuffixBuilder.append(" and t2.metadata_val = :metadata_val ");
            properties.put("metadata_val", apiKeyMetadata.getMetadataVal());
        }
        if (apiKeyMetadata.getApiKey() != null) {
            if (apiKeyMetadata.getApiKey().getStatus() != null) {
                sqlSuffixBuilder.append(" and t1.status = :status ");
                properties.put("status", apiKeyMetadata.getApiKey().getStatus().ordinal());
            }
        }
    }

    Session session = null;
    PageFinder<ApiKey> pageFinder = null;
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery("select count(1) " + sqlSuffixBuilder.toString());
        sqlQuery.setProperties(properties);
        int rowCount = ((Number) sqlQuery.uniqueResult()).intValue();
        pageFinder = new PageFinder<ApiKey>(query.getPage(), query.getPageSize(), rowCount);
        if (rowCount > 0) {
            // ?
            sqlSuffixBuilder.append(" group by ");
            sqlSuffixBuilder
                    .append(" t1.id, t1.app_key, t1.app_secret, t1.status, t1.update_user, t1.update_time ");
            sqlSuffixBuilder.append(" order by ");
            sqlSuffixBuilder.append(" t1.update_time desc ");
            // ?APP
            sqlQuery = session.createSQLQuery(sqlPrefixBuilder.append(sqlSuffixBuilder).toString());
            sqlQuery.setFirstResult(pageFinder.getStartOfPage());
            sqlQuery.setMaxResults(pageFinder.getPageSize());
            sqlQuery.setProperties(properties);
            sqlQuery.addScalar("id", Hibernate.STRING);
            sqlQuery.addScalar("appKey", Hibernate.STRING);
            sqlQuery.addScalar("appSecret", Hibernate.STRING);
            sqlQuery.addScalar("status", customApiKeyStatus);
            sqlQuery.addScalar("updateTime", Hibernate.STRING);
            sqlQuery.addScalar("updateUser", Hibernate.STRING);
            sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKey.class));
            List<ApiKey> merchantApiKeys = sqlQuery.list();
            // ?APP?
            sqlPrefixBuilder.setLength(0);
            sqlPrefixBuilder.append(" select ");
            sqlPrefixBuilder.append(
                    " t1.metadata_key as metadataKey, t1.metadata_val as metadataVal, t2.metadata_tag as metadataTag ");
            sqlPrefixBuilder.append(" from ");
            sqlPrefixBuilder.append(" tbl_merchant_api_key_metadata t1 ");
            sqlPrefixBuilder.append(" left join ");
            sqlPrefixBuilder.append(" ( ");
            sqlPrefixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement());
            sqlPrefixBuilder.append(" ) t2 ");
            sqlPrefixBuilder.append(" on(t1.metadata_val = t2.metadata_val) ");
            sqlPrefixBuilder.append(" where ");
            sqlPrefixBuilder.append(" t1.key_id = ? ");
            sqlQuery = session.createSQLQuery(sqlPrefixBuilder.toString());
            sqlQuery.addScalar("metadataKey", customAppType);
            sqlQuery.addScalar("metadataVal", Hibernate.STRING);
            sqlQuery.addScalar("metadataTag", Hibernate.STRING);
            sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
            for (ApiKey merchantApiKey : merchantApiKeys) {
                sqlQuery.setParameter(0, merchantApiKey.getId());
                merchantApiKey.setApiKeyMetadatas(new HashSet<ApiKeyMetadata>(sqlQuery.list()));
            }
            pageFinder.setData(merchantApiKeys);
        }
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
    return pageFinder;
}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getVFDBList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getVFDBSQL(key, "function");

    sql += " GROUP BY vfg_id, gene_name, gene_product, vf_id, vf_name, vf_fullname, function ";

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {/*from ww  w .j  a va2  s.c  om*/

        sql += " ORDER BY b.vfg_id, b.gene_name";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindVFDBSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("vfg_id", obj[0]);
        row.put("gene_name", obj[1]);
        row.put("gene_product", obj[2]);
        row.put("vf_id", obj[3]);
        row.put("vf_name", obj[4]);
        row.put("vf_fullname", obj[5]);
        row.put("function", obj[6]);
        row.put("feature_count", obj[7]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getVFDBFeatureList(Map<String, String> key, Map<String, String> sort, int start,
        int end) {

    String sql = "";

    sql += getVFDBFeatureSQL(key, "function");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {/*  w  w  w. ja  va  2 s . com*/

        sql += " ORDER BY na_feature_id";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindVFDBFeatureSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {
        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("vf_id", obj[0]);
        row.put("vf_name", obj[1]);
        row.put("vfg_id", obj[2]);
        row.put("gene_name", obj[3]);
        row.put("na_feature_id", obj[4]);
        row.put("genome_info_id", obj[5]);
        row.put("genome_name", obj[6]);
        row.put("accession", obj[7]);
        row.put("locus_tag", obj[8]);
        row.put("product", obj[9]);
        row.put("genome_id", obj[10]);
        row.put("feature_id", obj[11]);
        row.put("patric_id", obj[12]);
        row.put("refseq_locus_tag", obj[13]);
        row.put("alt_locus_tag", obj[14]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getCTDList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getCTDSQL(key, "function");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {/*w  w  w  .j a  v a2 s. c  o  m*/

        sql += " ORDER BY cs.gene_sym, cs.disease_name";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("gene_disease_rel", obj[2]);
        row.put("disease_name", obj[3]);
        row.put("pubmed_id", obj[4]);
        row.put("disease_id", obj[5]);
        row.put("gd_app_name", obj[6]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getCTDGraphList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getCTDSQL(key, "graphlist");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {/*from   w ww  . j  a v  a 2 s.co m*/

        sql += " ORDER BY cs.gene_sym, cs.disease_name";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindGraphCTDSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("gene_disease_rel", obj[2]);
        row.put("disease_name", obj[3]);
        row.put("pubmed_id", obj[4]);
        row.put("disease_id", obj[5]);
        row.put("gd_app_name", obj[6]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getGADGraphList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getGADSQL(key, "graphlist");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {// w ww . j a va  2  s  . c o m

        sql += " ORDER BY gs.gene_sym";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindGraphGADSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("association", obj[2]);
        row.put("mesh_disease_terms", obj[3]);
        row.put("broad_phenotype", obj[4]);
        row.put("pubmed_id", obj[5]);
        row.put("conclusion", obj[6]);
        row.put("gd_app_name", obj[7]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;
}