Spring JdbcTemplate: how to limit selected rows?

Question

I'm using Spring JdbcTemplate interface for fetching data from a MS SqlServer DB. In the documentation I see there is the setMaxRows() method to set a limit for all the queries, but what if I want to limit only a select?

Is there a way to set a limit only for a specific invoked query in a "configurable" way?

Answer 1

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);

Answer 2

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

Answer 3

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.