Example usage for org.hibernate SQLQuery setFetchSize

Introduction

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

Prototype

@Override
    NativeQuery<T> setFetchSize(int fetchSize);

Source Link

Usage

From source file:at.ac.tuwien.ifs.tita.dao.time.EffortDao.java

License:Apache License

/** {@inheritDoc} */
@SuppressWarnings("unchecked")
@Override/*from  ww  w .jav  a 2  s . co  m*/
public List<UserProjectEffort> findEffortsForTiTAProjectAndTimeConsumerId(List<String> projectIds,
        List<String> tIds, String grouping) {
    String pIds = StringUtil.generateIdStringFromStringList(projectIds);
    String tcIds = StringUtil.generateIdStringFromStringList(tIds);

    String queryString = "select UUID() as ID, "
            + " sum(duration) as DURATION, username as USERNAME, project as PROJECT";

    if (grouping.equals("month")) {
        queryString += ", year as YEAR, month as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year as YEAR, month as MONTH, day as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from (select sum(e1.duration) as duration, tu.username as username,"
            + " tp.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, " + " day(e1.date) as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from effort e1 " + "join tita_task tt on e1.tita_task_id = tt.id "
            + "join tita_project tp on tt.tita_project_id = tp.id " + "join tita_user tu on tu.id = e1.user_id "
            + "where tp.name in (" + pIds + ") and tu.username in (" + tcIds + ") ";

    if (grouping.equals("month")) {
        queryString += " group by tp.name, tu.username, year(e1.date), " + " month(e1.date) ";
    } else if (grouping.equals("day")) {
        queryString += " group by tp.name, tu.username, year(e1.date), " + " month(e1.date), day(e1.date) ";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp.name, tu.username ";
    }

    queryString += " union all" + " select sum(e2.duration) as duration, tu1.username as username, "
            + " tp2.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e2.date) as YEAR, " + " month(e2.date) as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year(e2.date) as YEAR, " + " month(e2.date) as MONTH, day(e2.date) as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from effort e2 " + "join issue_tracker_task it on e2.issuet_task_id = it.id "
            + "join issue_tracker_project itp on it.issue_tracker_project_id ="
            + "itp.id join tita_project tp2 on tp2.id = itp.tita_project_id "
            + "join tita_user tu1 on tu1.id = e2.user_id " + "where tp2.name in (" + pIds
            + ") and tu1.username in (" + tcIds + ")";

    if (grouping.equals("month")) {
        queryString += " group by tp2.name, tu1.username, year(e2.date), " + " month(e2.date)";
    } else if (grouping.equals("day")) {
        queryString += " group by tp2.name, tu1.username, year(e2.date), " + " month(e2.date), day(e2.date)";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp2.name, tu1.username";
    }

    queryString += ") as U group by project, username, year, month, day "
            + " order by project, year, month, day, duration, username";

    org.hibernate.SQLQuery q = getSession().createSQLQuery(queryString.toUpperCase());
    q.addEntity(UserProjectEffort.class);
    q.setFetchSize(C_FETCHSIZE);

    List<UserProjectEffort> efforts = null;

    try {
        efforts = q.list();
    } catch (NoResultException e) {
        // nothing to do
    }
    return efforts;
}

---

From source file:at.ac.tuwien.ifs.tita.dao.time.EffortDao.java

License:Apache License

/** {@inheritDoc} */
@SuppressWarnings("unchecked")
@Override/*www.j a  v a2  s . c  o m*/
public List<UserProjectEffort> findEffortsForTiTAProjectId(List<String> projectIds, String grouping) {
    String pIds = StringUtil.generateIdStringFromStringList(projectIds);

    String queryString = "select UUID() as ID,"
            + " sum(duration) as DURATION, project as PROJECT, null as USERNAME";

    if (grouping.equals("month")) {
        queryString += ", year as YEAR, month as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year as YEAR, month as MONTH, day as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from (select sum(e1.duration) as duration, tp.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, null as DAY ";
    } else if (grouping.equals("day")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, day(e1.date) as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from effort e1 join tita_task tt on e1.tita_task_id = tt.id "
            + "join tita_project tp on tt.tita_project_id = tp.id " + "where tp.name in (" + pIds + ") ";

    if (grouping.equals("month")) {
        queryString += " group by tp.name, year(e1.date), " + " month(e1.date) ";
    } else if (grouping.equals("day")) {
        queryString += " group by tp.name, year(e1.date), " + " month(e1.date), day(e1.date) ";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp.name ";
    }

    queryString += " union all" + " select sum(e2.duration) as duration, tp2.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e2.date) as year, " + " month(e2.date) as month, null as day ";
    } else if (grouping.equals("day")) {
        queryString += ", year(e2.date) as year, " + "month(e2.date) as month, day(e2.date) as day";
    } else if (grouping.equals("overall")) {
        queryString += ", null as year, null as month, null as day";
    }

    queryString += " from effort e2 " + "join issue_tracker_task it on e2.issuet_task_id = it.id "
            + "join issue_tracker_project itp on it.issue_tracker_project_id ="
            + "itp.id join tita_project tp2 on tp2.id = itp.tita_project_id " + "where tp2.name in (" + pIds
            + ") ";

    if (grouping.equals("month")) {
        queryString += " group by tp2.name, year(e2.date), " + " month(e2.date) ";
    } else if (grouping.equals("day")) {
        queryString += " group by tp2.name, year(e2.date), " + " month(e2.date), day(e2.date) ";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp2.name ";
    }

    queryString += ") as U group by project, year, month, day, username "
            + " order by project, year, month, day, duration";

    org.hibernate.SQLQuery q = getSession().createSQLQuery(queryString.toUpperCase());
    q.addEntity(UserProjectEffort.class);
    q.setFetchSize(C_FETCHSIZE);
    List<UserProjectEffort> efforts = new ArrayList<UserProjectEffort>();

    try {
        efforts = q.list();
    } catch (NoResultException e) {
        // nothing to do
    }
    return efforts;
}

---

From source file:at.ac.tuwien.ifs.tita.dao.time.EffortDao.java

License:Apache License

/** {@inheritDoc} */
@Override//from  ww w .  ja v a2s  .  c  om
public List<Effort> findEffortsForTiTAProjectAndTiTAUserOrdered(Long projectId, Long userId) {
    List<Effort> returnValue = new ArrayList<Effort>();

    String queryString = "select e1.ID, e1.DESCRIPTION, e1.TITA_TASK_ID, e1.ISSUET_TASK_ID, "
            + "e1.DATE, e1.START_TIME, e1.END_TIME, e1.DURATION, e1.DELETED, "
            + "e1.USER_ID, e1.COST_CENTER from Effort e1 " + "join tita_task tt on e1.tita_task_id = tt.id "
            + "join tita_project tp on tt.tita_project_id = tp.id " + "join tita_user tu on tu.id = e1.user_id "
            + "where tu.id = " + userId + " and tp.id = " + projectId + " and e1.deleted != true " + "UNION "
            + "select e2.ID, e2.DESCRIPTION, e2.TITA_TASK_ID, e2.ISSUET_TASK_ID, e2.DATE, "
            + "e2.START_TIME, e2.END_TIME, e2.DURATION, e2.DELETED, "
            + "e2.USER_ID, e2.COST_CENTER from Effort e2 "
            + "join issue_tracker_task itt on e2.issuet_task_id = itt.id "
            + "join issue_tracker_project itp on itt.issue_tracker_project_id = itp.id "
            + "join tita_project tp2 on tp2.id = itp.tita_project_id "
            + "join tita_user tu2 on tu2.id = e2.user_id " + "where tu2.id = " + userId + " and tp2.id = "
            + projectId + " and e2.deleted != true " + "order by date desc, end_time desc";

    org.hibernate.SQLQuery q = getSession().createSQLQuery(queryString.toUpperCase());
    q.addEntity(Effort.class);
    q.setFetchSize(C_FETCHSIZE);

    try {
        returnValue = q.list();
    } catch (NoResultException e) {
        // no results
    }
    return returnValue;
}

---

From source file:com.mysema.query.jpa.hibernate.sql.AbstractHibernateSQLQuery.java

License:Apache License

private Query createQuery(String queryString) {
    logQuery(queryString);/* ww w .j  a v  a2  s.c o  m*/
    org.hibernate.SQLQuery query = session.createSQLQuery(queryString);
    // set constants
    HibernateUtil.setConstants(query, constants, queryMixin.getMetadata().getParams());
    // set entity paths
    for (Path<?> path : entityPaths) {
        query.addEntity(path.toString(), path.getType());
    }
    // set result transformer, if projection is an EConstructor instance
    List<? extends Expression<?>> projection = queryMixin.getMetadata().getProjection();
    if (projection.size() == 1 && projection.get(0) instanceof FactoryExpression) {
        query.setResultTransformer(new FactoryExpressionTransformer((FactoryExpression<?>) projection.get(0)));
    }
    if (fetchSize > 0) {
        query.setFetchSize(fetchSize);
    }
    if (timeout > 0) {
        query.setTimeout(timeout);
    }
    if (cacheable != null) {
        query.setCacheable(cacheable);
    }
    if (cacheRegion != null) {
        query.setCacheRegion(cacheRegion);
    }
    if (readOnly != null) {
        query.setReadOnly(readOnly);
    }
    return query;
}

---

From source file:com.querydsl.jpa.hibernate.sql.AbstractHibernateSQLQuery.java

License:Apache License

private Query createQuery(boolean forCount) {
    NativeSQLSerializer serializer = (NativeSQLSerializer) serialize(forCount);
    String queryString = serializer.toString();
    logQuery(queryString, serializer.getConstantToLabel());
    org.hibernate.SQLQuery query = session.createSQLQuery(queryString);
    // set constants
    HibernateUtil.setConstants(query, serializer.getConstantToLabel(), queryMixin.getMetadata().getParams());

    if (!forCount) {
        ListMultimap<Expression<?>, String> aliases = serializer.getAliases();
        Set<String> used = Sets.newHashSet();
        // set entity paths
        Expression<?> projection = queryMixin.getMetadata().getProjection();
        if (projection instanceof FactoryExpression) {
            for (Expression<?> expr : ((FactoryExpression<?>) projection).getArgs()) {
                if (isEntityExpression(expr)) {
                    query.addEntity(extractEntityExpression(expr).toString(), expr.getType());
                } else if (aliases.containsKey(expr)) {
                    for (String scalar : aliases.get(expr)) {
                        if (!used.contains(scalar)) {
                            query.addScalar(scalar);
                            used.add(scalar);
                            break;
                        }//from   w ww. j a va2s.  c  om
                    }
                }
            }
        } else if (isEntityExpression(projection)) {
            query.addEntity(extractEntityExpression(projection).toString(), projection.getType());
        } else if (aliases.containsKey(projection)) {
            for (String scalar : aliases.get(projection)) {
                if (!used.contains(scalar)) {
                    query.addScalar(scalar);
                    used.add(scalar);
                    break;
                }
            }
        }

        // set result transformer, if projection is a FactoryExpression instance
        if (projection instanceof FactoryExpression) {
            query.setResultTransformer(new FactoryExpressionTransformer((FactoryExpression<?>) projection));
        }
    }

    if (fetchSize > 0) {
        query.setFetchSize(fetchSize);
    }
    if (timeout > 0) {
        query.setTimeout(timeout);
    }
    if (cacheable != null) {
        query.setCacheable(cacheable);
    }
    if (cacheRegion != null) {
        query.setCacheRegion(cacheRegion);
    }
    if (readOnly != null) {
        query.setReadOnly(readOnly);
    }
    return query;
}

---

From source file:org.openiam.idm.srvc.qry.service.QueryDataServiceImpl.java

License:Open Source License

public List<Object> executeQuery(Query qry) {

    Session session = sessionFactory.getCurrentSession();

    SQLQuery sqlQry = session.createSQLQuery(qry.getSql());
    sqlQry.addEntity(qry.getObjectClass());

    // add the parameters for the query if they exists
    List<QueryParam> paramList = qry.getParamList();
    if (paramList != null && paramList.size() > 0) {
        for (QueryParam param : paramList) {
            if (param.getParamType() == ParameterTypeEnum.STRING) {
                System.out.println(" - Setting parameters in query service.");
                sqlQry.setString(param.getParamName(), (String) param.getParamValue());
            }//  w w w. j  av  a2 s  . c  o  m
        }
    }

    if (qry.getMaxRowCount() != 0) {
        sqlQry.setFetchSize(qry.getMaxRowCount());
        sqlQry.setMaxResults(qry.getMaxRowCount());
    }
    return sqlQry.list();

}

---

From source file:org.openxdata.server.dao.hibernate.HibernateEditableDAO.java

License:Apache License

@SuppressWarnings("unchecked")
@Override//from www.  j a  v a  2s .c o  m
@Secured("Perm_View_Form_Data")
public List<Object[]> getResponseData(String formBinding, String[] questionBindings, int offset, int limit,
        String sortField, boolean ascending) {
    StringBuilder sql = new StringBuilder();
    sql.append("select openxdata_form_data_id,");
    sql.append(StringUtils.arrayToCommaDelimitedString(questionBindings));
    sql.append(" from ");
    sql.append(formBinding);
    if (sortField != null && !sortField.trim().equals("")) {
        sql.append(" order by ");
        sql.append(sortField);
        if (!ascending)
            sql.append(" DESC");
    }
    log.debug("executing sql: " + sql + " firstResult=" + offset + " maxResults=" + limit);
    // execute + limit results for page
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setFirstResult(offset);
    query.setFetchSize(limit);
    query.setMaxResults(limit);
    List<Object[]> data = (List<Object[]>) query.list();
    return data;
}

---

From source file:org.openxdata.server.dao.hibernate.HibernateEditableDAO.java

License:Apache License

/**
 * Runs a given <code>SQL</code> statement within a given
 * <code>sessionFactory</code>.
 * //from w w w  .jav  a  2 s. co  m
 * @param SQL
 *            <code>SQL</code> to run.
 * @param editableId
 *            Optional <code>Id</code> for the <code>Editable</code>.
 * @param sessionFactory
 *            <code>sessionFactory</code> to create session in which the SQL
 *            will be run.
 * 
 * @return <code> Boolean</code>
 */
@SuppressWarnings("unchecked")
private Boolean runSQLToAscertainDataExistence(String SQL, int editableId) {
    Boolean hasData = false;
    Session session = getSession();
    SQLQuery query = session.createSQLQuery(SQL);
    query.setInteger("id", editableId);
    query.setFirstResult(0);
    query.setFetchSize(1);
    List<FormDataHeader> items = query.list();
    if (items != null) {
        if (items.size() > 0)
            hasData = true;
        else
            hasData = false;
    }
    return hasData;
}

---