CODEWITHSUNDEEP
javapostgresqljdbcprepared-statementlimit
Jonas
Jonas

Reputation: 860

Add a limit parameter

In Oracle I can have an SQL string like this

select * from table where rownum <= ?

Then in a PreparedStatement I can set the rownum as a parameter.

How does it work with PostgreSQL JDBC (Java) with the LIMIT clause? Doing the following won't let me set a parameter:

select * from table limit ?

Upvotes: 0

Views: 138

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

It is not possible use a ? placeholder in a LIMIT clause with your current database and driver (q.v. the other answers and comments which indicate that your current query might work). If you absolutely needed to do this from SQL, one workaround might be to use ROW_NUMBER() with a subquery. In other words, change this:

SELECT * FROM yourTable ORDER BY some_col LIMIT ?

to this:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY some_col) rn
    FROM yourTable
) t
WHERE rn < ?;

However, in practice, if you are using JPA, you could use the various JPA methods to programmatically limit the size of the result set.

Upvotes: 0

prasad_
prasad_

Reputation: 14287

How does it work with Postgres JDBC (Java) with the LIMIT clause? Doing the following won'e let me set a parameter:

select * from table limit ?

This expands on the answer by @Andrey Smelik.

Using PostgreSQL JDBC driver (access database using Java programming), you can use the SQL LIMIT clause to limit the number of rows returned by the query.

This also allows set the prepared statement's (of JDBC API) parameter to specify the "limit". For example:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "mydb", "***");
final String SQL = "SELECT name FROM table LIMIT ?";

try (PreparedStatement pst = conn.prepareStatement(SQL)) {

    pst.setInt(1, 5); // limit the number of rows by 5
    ResultSet rs = pst.executeQuery();
    while (rs.next())
        System.out.println("Column data: " + rs.getString("name"));
}

// ... close connection, etc.

The above code prints five rows of name column value from the database table table.

Upvotes: 2

Andrey Smelik
Andrey Smelik

Reputation: 1241

Apparently you are using such an old version of the JDBC driver (version 7.x.x or lower), in which the LIMIT parameterization is not supported. This version was released about twenty years ago and is completely outdated. I recommend that you switch to using a new version of the driver as soon as possible.

In PostgreSQL, you can use LIMIT to limit the number of records retrieved.

In the following code

PreparedStatement statement = connection.prepareStatement("SELECT * FROM users LIMIT ?"));
statement.setInt(1, 10);

You create a prepared statement and set the LIMIT parameter to get the first 10 records.

For more information, see the PostgreSQL documentation.

Upvotes: 2

Related Questions