Spring JdbcTemplate: how to limit selected rows?

Some SQL based query languages(derby) doesn't support LIMIT keyword. So you can't use LIMIT in query directly. Using Spring JDBC Template we can set maximum number of rows needed through setMaxRows(Integer intvalue)

jdbcTemplate.setMaxRows(1);

Limiting the result set of a specific query can be done by putting the limit directly into the query. Consult your DB vendor documentation to see if it supports for example LIMIT.

Example on MySQL: SELECT * FROM EMPLOYEE LIMIT 10


You can also user limit keyword in query. see below query

select * from FileShare limit 3 offset 3

if in your application limit and offset can be assign dynamically by user the use below query

@Autowired
private JdbcTemplate template;

public JdbcTemplate getTemplate() {
    return HibernateUtil.getJdbcTemplate();
}

public List<FileShare> getAllSharedFiless(int limit,int offset)
            throws ShareMeException {
String query="select * from FileShare  limit ? offset ?";
        return getTemplate().query(query,
                new SharedFilesRowMapper(),
                new Object[]{limit,offset});

}

Here FileShare is a table name and SharedFilesRowMapper is rowMapper which list rows from table.