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?

Benniebenning

What about using your own PreparedStatementCreator for those statements and call Statement.setMaxRows() when creating the prepared statement? – Tamayo

@a_horse_with_no_name that seems a great idea, can you provide me some example of using PreparedStatementCreator, I'm new to Spring... – Benniebenning

I'm new to Spring myself, otherwise I would have added an answer with an example :) – Tamayo

With Spring boot you can use the below property in application.properties spring.jdbc.template.max-rows=100 – Welsh

Answer 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

Alrzc

Or SELECT * FROM EMPLOYEE WHERE ROWNUM < 10 in case of Oracle. – Hourglass

@Alrzc yes, I know, but in my method implementation, the limit is optional, so, if you have to limit the query directly using SQL, you have to write two different queries... – Benniebenning

In such cases I usually build the query string in my code handling the "limit or no-limit" cases differently and then execute it. I don't know a better solution, curious if someone does.. :-) – Alrzc

@Alrzc definitely Burusothman sultion is better - independent of db vendor. If someone use few vendors query customization will be insane. – Messinger

spring.jdbc.template.max-rows=100 . Using this property in Spring boot application.properties, you can limit the number of rows. – Welsh

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

Microstructure

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.

Jackdaw

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

Oblate