Example usage for org.springframework.batch.item.database PagingQueryProvider generateFirstPageQuery

Introduction

In this page you can find the example usage for org.springframework.batch.item.database PagingQueryProvider generateFirstPageQuery.

Prototype

String generateFirstPageQuery(int pageSize);

Source Link

Document

Generate the query that will provide the first page, limited by the page size.

Usage

From source file:admin.service.JdbcSearchableStepExecutionDao.java

public Collection<StepExecution> findStepExecutions(String jobName, String stepName, int start, int count) {

    String whereClause;/* ww  w.j  a va 2 s .c  om*/

    if (jobName.contains("*")) {
        whereClause = "JOB_NAME like ?";
        jobName = jobName.replace("*", "%");
    } else {
        whereClause = "JOB_NAME = ?";
    }

    if (stepName.contains("*")) {
        whereClause = whereClause + " AND STEP_NAME like ?";
        stepName = stepName.replace("*", "%");
    } else {
        whereClause = whereClause + " AND STEP_NAME = ?";
    }

    PagingQueryProvider queryProvider = getPagingQueryProvider(whereClause);

    List<StepExecution> stepExecutions;
    if (start <= 0) {
        stepExecutions = getJdbcTemplate().query(queryProvider.generateFirstPageQuery(count),
                new StepExecutionRowMapper(), jobName, stepName);
    } else {
        try {
            Long startAfterValue = getJdbcTemplate().queryForObject(
                    queryProvider.generateJumpToItemQuery(start, count), Long.class, jobName, stepName);
            stepExecutions = getJdbcTemplate().query(queryProvider.generateRemainingPagesQuery(count),
                    new StepExecutionRowMapper(), jobName, stepName, startAfterValue);
        } catch (IncorrectResultSizeDataAccessException e) {
            return Collections.emptyList();
        }
    }

    return stepExecutions;

}

---

From source file:org.copperengine.spring.audit.AuditTrailQueryEngine.java

@Override
public List<AuditTrailInfo> getAuditTrails(String transactionId, String conversationId, String correlationId,
        Integer level, int maxResult) {

    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();

    String sortClause = "SEQ_ID";
    String whereClause = "where 1=1 ";
    List<Object> args = new ArrayList<Object>();

    if (level != null) {
        whereClause += " and LOGLEVEL <= ? ";
        sortClause = "LOGLEVEL";
        args.add(level);/*from  w w  w  .  j a va 2  s  .c o  m*/
    }
    if (StringUtils.hasText(correlationId)) {
        whereClause += " and CORRELATION_ID = ? ";
        sortClause = "CORRELATION_ID";
        args.add(correlationId);
    }

    if (StringUtils.hasText(conversationId)) {
        whereClause += " and CONVERSATION_ID = ? ";
        sortClause = "CONVERSATION_ID";
        args.add(conversationId);
    }

    if (StringUtils.hasText(transactionId)) {
        whereClause += " and TRANSACTION_ID = ? ";
        sortClause = "TRANSACTION_ID";
        args.add(transactionId);
    }

    String selectClause = "select " + "SEQ_ID," + "TRANSACTION_ID," + "CONVERSATION_ID," + "CORRELATION_ID,"
            + "OCCURRENCE," + "LOGLEVEL," + "CONTEXT," + "INSTANCE_ID," + "MESSAGE_TYPE";

    factory.setDataSource(getDataSource());
    factory.setFromClause("from COP_AUDIT_TRAIL_EVENT ");

    factory.setSelectClause(selectClause);

    factory.setWhereClause(whereClause);
    factory.setSortKey(sortClause);

    PagingQueryProvider queryProvider = null;
    try {
        queryProvider = (PagingQueryProvider) factory.getObject();
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return null;
    }

    String query = queryProvider.generateFirstPageQuery(maxResult);

    // this.getJdbcTemplate().setQueryTimeout(1000);

    long start = System.currentTimeMillis();
    RowMapper<AuditTrailInfo> rowMapper = new RowMapper<AuditTrailInfo>() {

        public AuditTrailInfo mapRow(ResultSet rs, int arg1) throws SQLException {

            return new AuditTrailInfo(rs.getLong("SEQ_ID"), rs.getString("TRANSACTION_ID"),
                    rs.getString("CONVERSATION_ID"), rs.getString("CORRELATION_ID"),
                    rs.getTimestamp("OCCURRENCE").getTime(), rs.getInt("LOGLEVEL"), rs.getString("CONTEXT"),
                    rs.getString("INSTANCE_ID"), rs.getString("MESSAGE_TYPE"));
        }

    };
    List<AuditTrailInfo> res = this.getJdbcTemplate().query(query, rowMapper, args.toArray());

    long end = System.currentTimeMillis();

    logger.info("query took: " + (end - start) + " ms : " + query);

    return res;
}

---

From source file:org.springframework.batch.item.database.JdbcPagingQueryIntegrationTests.java

@Test
public void testQueryFromStart() throws Exception {

    PagingQueryProvider queryProvider = getPagingQueryProvider();

    int total = JdbcTestUtils.countRowsInTable(jdbcTemplate, "T_FOOS");
    assertTrue(total > pageSize);/* w  w  w.j  av a 2  s  .  c o  m*/
    int pages = total / pageSize;

    int count = 0;

    List<Map<String, Object>> list = jdbcTemplate.queryForList(queryProvider.generateFirstPageQuery(pageSize));
    logger.debug("First page result: " + list);
    assertEquals(pageSize, list.size());
    count += pageSize;
    Map<String, Object> oldValues = null;

    while (count < pages * pageSize) {
        Map<String, Object> startAfterValues = getStartAfterValues(queryProvider, list);
        assertNotSame(oldValues, startAfterValues);
        list = jdbcTemplate.queryForList(queryProvider.generateRemainingPagesQuery(pageSize),
                getParameterList(null, startAfterValues).toArray());
        assertEquals(pageSize, list.size());
        count += pageSize;
        oldValues = startAfterValues;
    }

    if (count < total) {
        Map<String, Object> startAfterValues = getStartAfterValues(queryProvider, list);
        list = jdbcTemplate.queryForList(queryProvider.generateRemainingPagesQuery(pageSize),
                getParameterList(null, startAfterValues).toArray());
        assertEquals(total - pages * pageSize, list.size());
        count += list.size();
    }

    assertEquals(total, count);
}

---