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?

Solution 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

Solution 2

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

Solution 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.

Solution 4

setFetchSize or setMaxRows is not the same as LIMIT in DB SQL. Setting fetch size in JdbcTemplate means that the resultset of your query will be fetched in chunks of the size set with setFetchSize. This is to control the memory usage and the number of database calls.

View more solutions