I've got an issue with fetching behavior and ResultSet.isAfterLast method. I think it comes from the fact that isAfterLast is checked on current fetched page and not checked for the whole resulset.
To illustrate my problem here is a test case - tested : - JDBC driver build number : postgresql-9.4-1201-jdbc41.jar
- JVM : 1.7
- PostgreSQL version : 9.3.5
- structure & data CREATE TABLE test_table(col1 numeric(19,0) NOT NULL); INSERT INTO test_table(col1) VALUES (1); INSERT INTO test_table(col1) VALUES (2); INSERT INTO test_table(col1) VALUES (3); INSERT INTO test_table(col1) VALUES (4);
- java code final String databaseUrl = "jdbc:postgresql://localhost:5432/RNA?loglevel=2"; final Properties properties = new Properties(); properties.setProperty("user", "postgres"); properties.setProperty("password", "pwd"); connection = DriverManager.getConnection(databaseUrl, properties); connection.setAutoCommit(false); Statement statement = connection.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(fetchSize);
ResultSet result = statement .executeQuery("select * from TEST_TABLE");
while (result.next()) { System.out.println("row " + result.getString(1) + " - result.isAfterLast()=" + result.isAfterLast()); } System.out .println("after there is no more row - result.isAfterLast()=" + result.isAfterLast());
result.close(); statement.close();
- results if fetchSize = 3, I can see in output :
12:16:02.005 (2) FE=> Parse(stmt=S_1,query="select * from TEST_TABLE",oids={}) 12:16:02.005 (2) FE=> Execute(portal=C_2,limit=3) row 1 - result.isAfterLast()=false row 2 - result.isAfterLast()=false row 3 - result.isAfterLast()=false 12:16:02.007 (2) FE=> Execute(portal=C_2,limit=3) row 4 - result.isAfterLast()=false after there is no more row - result.isAfterLast()=true ====> seems OK - I've navigate through the 4 rows
but if fetchSize = 2, I can see in output : 12:16:01.963 (1) FE=> Parse(stmt=S_1,query="select * from TEST_TABLE",oids={}) 12:16:01.963 (1) FE=> Execute(portal=C_2,limit=2) row 1 - result.isAfterLast()=false row 2 - result.isAfterLast()=false 12:16:01.976 (1) FE=> Execute(portal=C_2,limit=2) row 3 - result.isAfterLast()=false row 4 - result.isAfterLast()=false 12:16:01.976 (1) FE=> Execute(portal=C_2,limit=2) 12:16:01.977 (1) <=BE CommandStatus(SELECT 0) after there is no more row - result.isAfterLast()=false ====> KO - I've navigate through the 4 rows and afterLast is still false.
Cordialement.
Luis Michel PINTO DA COSTA GIRC Agirc-Arrco Architecture et Infrastructure Logicielle - Infrastructure Middleware & SGBD - Ormes Tél : 02 38 42 83 15 lpintodacosta@girc.agirc-arrco.fr
From:
Dave Cramer Date: Subject:
Re: Re: Resurrected thread: Speed improvement - Group batch
Insert - Rewrite the INSERT at the driver level (using a parameter)