Any time you want to issue SQL statements to the database, you require aStatement orPreparedStatement instance. Once you have aStatement orPreparedStatement , you can use issue a query. This will return aResultSet instance, which contains the entire result (see the section calledGetting results based on a cursor here for how to alter this behaviour).Example 5.1, “Processing a Simple Query in JDBC” illustrates this process.

Example 5.1. Processing a Simple Query in JDBC

This example will issue a simple query and print out the first column of each row using aStatement .

Statementst=conn.createStatement();ResultSetrs=st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");while(rs.next()){System.out.print("Column 1 returned ");System.out.println(rs.getString(1));}rs.close();st.close();

This example issues the same query as before but uses aPreparedStatement and a bind value in the query.

intfoovalue=500;PreparedStatementst=conn.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");st.setInt(1,foovalue);ResultSetrs=st.executeQuery();while(rs.next()){System.out.print("Column 1 returned ");System.out.println(rs.getString(1));}rs.close();st.close();

Getting results based on a cursor

By default, the driver collects all the results for the query at once. This can be inconvenient for large data sets sothe JDBC driver provides a means of basing aResultSet on a database cursor and only fetching a small number of rows.

A small number of rows are cached on the client side of the connection and when exhausted the next block of rows isretrieved by repositioning the cursor.

NOTE

Cursor basedResultSets cannot be used in all situations. There a number of restrictions which will make the driversilently fall back to fetching the wholeResultSet at once.

• The connection to the server must be using the V3 protocol. This is the default for (and is only supported by)server versions 7.4 and later.

• TheConnection must not be in autocommit mode. The backend closes cursors at the end of transactions, so inautocommit mode the backend will have closed the cursor before anything can be fetched from it.

• TheStatement must be created with aResultSet type ofResultSet.TYPE_FORWARD_ONLY. This is the default,so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards orotherwise jump around in theResultSet.

• The query given must be a single statement, not multiple statements strung together with semicolons.

Example 5.2. Setting fetch size to turn cursors on and off.

Changing the code to use cursor mode is as simple as setting the fetch size of theStatement to the appropriate size.Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour).

// make sure autocommit is offconn.setAutoCommit(false);Statementst=conn.createStatement();// Turn use of the cursor on.st.setFetchSize(50);ResultSetrs=st.executeQuery("SELECT * FROM mytable");while(rs.next()){System.out.print("a row was returned.");}rs.close();// Turn the cursor off.st.setFetchSize(0);rs=st.executeQuery("SELECT * FROM mytable");while(rs.next()){System.out.print("many rows were returned.");}rs.close();// Close the statement.st.close();

Using the Statement or PreparedStatement Interface

The following must be considered when using theStatement orPreparedStatement interface:

• You can use a singleStatement instance as many times as you want. You could create one as soon as you open theconnection and use it for the connection’s lifetime. But you have to remember that only oneResultSet can existperStatement orPreparedStatement at a given time.

• If you need to perform a query while processing aResultSet, you can simply create and use anotherStatement .

• If you are using threads, and several are using the database, you must use a separateStatement for each thread.Refer toUsing the Driver in a Multithreaded or a Servlet Environment if you are thinking ofusing threads, as it covers some important points.

• When you are done using theStatement orPreparedStatement you should close it.

• In JDBC, the question mark (?) is the placeholder for the positional parameters of aPreparedStatement.There are, however, a number of PostgreSQL® operators that contain a question mark. To keep such question marks in an SQLstatement from being interpreted as positional parameters, use two question marks (?? ) as escape sequence.You can also use this escape sequence in aStatement , but that is not required. Specifically only in aStatementa single (? ) can be used as an operator.

Using the ResultSet Interface

The following must be considered when using theResultSet interface:

• Before reading any values, you must callnext(). This returns true if there is a result, but more importantly,it prepares the row for processing.

• You must close aResultSet by callingclose() once you have finished using it.

• Once you make another query with theStatement used to create aResultSet, the currently openResultSet instanceis closed automatically.

• When PreparedStatement API is used,ResultSet switches to binary mode after five query executions (this default isset by theprepareThreshold connection property, seeServer Prepared Statements.This may cause unexpected behaviour when some methods are called. For example, results on method calls such asgetString()on non-string data types, while logically equivalent, may be formatted differently after execution exceeds the setprepareThreshold when conversion to object method switches to the method with a return type matching the return mode.

Performing Updates

To change data (perform anINSERT ,UPDATE , orDELETE ) you use theexecuteUpdate() method. This method issimilar to the methodexecuteQuery()

used to issue aSELECT statement, but it doesn’t return aResultSet instead it returns the number of rows affectedby theINSERT ,UPDATE , orDELETE statement.Example 5.3, “Deleting Rows in JDBC”illustrates the usage.

Example 5.3. Deleting Rows in JDBC

This example will issue a simpleDELETE statement and print out the number of rows deleted.

intfoovalue=500;PreparedStatementst=conn.prepareStatement("DELETE FROM mytable WHERE columnfoo = ?");st.setInt(1,foovalue);introwsDeleted=st.executeUpdate();System.out.println(rowsDeleted+" rows deleted");st.close();

Creating and Modifying Database Objects

To create, modify or drop a database object like a table or view you use theexecute() method. This method is similarto the methodexecuteQuery() , but it doesn’t return a result.Example 5.4, “Dropping a Table in JDBC illustrates the usage.

Example 5.4. Dropping a Table in JDBC

This example will drop a table.

Statementst=conn.createStatement();st.execute("DROP TABLE mytable");st.close();

Using Java 8 Date and Time classes

The PostgreSQL® JDBC driver implements native support for theJava 8 Date and Time API(JSR-310) using JDBC 4.2.

Table 5.1. Supported Java 8 Date and Time classes

This is closely aligned with tables B-4 and B-5 of the JDBC 4.2 specification.

Note

ZonedDateTime ,Instant andOffsetTime / TIME WITH TIME ZONE are not supported. Also note that allOffsetDateTime instances will have be in UTC (have offset 0). This is because the backend stores them as UTC.

Example 5.2. Reading Java 8 Date and Time values using JDBC

Statementst=conn.createStatement();ResultSetrs=st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");while(rs.next()){System.out.print("Column 1 returned ");LocalDatelocalDate=rs.getObject(1,LocalDate.class);System.out.println(localDate);}rs.close();st.close();

For other data types simply pass other classes to#getObject .

Note

The Java data types needs to match the SQL data types in table 7.1.

Example 5.3. Writing Java 8 Date and Time values using JDBC

LocalDatelocalDate=LocalDate.now();PreparedStatementst=conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");st.setObject(1,localDate);st.executeUpdate();st.close();