How to get only the first row from a java.sql.ResultSet?

Question

I have a ResultSet object containing all the rows returned from an sql query.

I want to be able to (in the java code, NOT force it in the SQL) to be able to take a ResultSet and transform it so that it only contains 1 (the first) row.

What would be the way to acheive this? Also, is there another appropriate class (somewhere in java.sql or elsewhere) for storing just a single row rather than trimming my ResultSet?

Thanks!

Answer 1

For the purpose of just limiting the number of rows in a result set you can do the following:

String yourQuery = "select * from some_table";
PreparedStatement statement = connection.prepareStatement(yourQuery); 
statement.setMaxRows(1); 
rs = statement.executeQuery();

As for a specific class that can hold only one row, this really depends on what you want to do with the data. You could for example just extract the contents of the result set and store them in an array, or if you need names as well as values a map. You could also just read the data into a POJO, if you would rather work with a specific object rather than a generic data structure.

Answer 2

See Statement.setMaxRows

Answer 3

You reference the need to store a single row of data, in which case I'd say that using a ResultSet is probably a bad idea as it will be typically consume database resources until closed (e.g. the underlying java.sql.Connection).

Instead I'd recommend reading the first row of your ResultSet into a POJO, perhaps using Spring's JDBC utility classes (e.g. RowMapper) to achieve this concisely. Immediately after this, close the ResultSet and associated Statement and Connection (or allow Spring to do it for you).