What are alternatives to JDBC driver for access PostgreSQL database

Question

I'm using official JDBC driver for PostgreSQL, but I'm stuck with the following issues:

• No support for PostgreSQL-ish data structures such as UUIDs.
• Common JDBC weirdness, such as:
  • No function to escape values for consuming by PostgreSQL.
  • Limited support for executing heterogeneous statements in batch.
  • No rewriting of multiple insert statements into single insert statement when inserting many rows in one table.

So, the question — is there any PostgreSQL database driver which can leverage full power of PostgreSQL without much boilerplate? I'm also use Scala language for development, so if driver is designed specifically for Scala it would be so much awesome awesome.

Answer 1

Some of this seems to be (unless I'm not understanding) user error in using JDBC. JDBC is a pretty ugly API, so never ask if you can do it elegantly, just ask if you can do it at all.

Escaping and inserting multiple rows should be handled, as @ColinD and @a_horse pointed out, with Prepared statements and batch operations. Under the hood, I would expect a good JDBC implementation to do the things you want (I am not familiar with PostgreSQL's implementation).

Regarding UUIDs, here is a solution:

All that PostgreSQL can do is convert string literals to uuid.

You can make use of this by using the data type org.postgresql.util.PGobject, which is a general class used to represent data types unknown to JDBC.

You can define a helper class:

public class UUID extends org.postgresql.util.PGobject {
    public static final long serialVersionUID = 668353936136517917L;
    public UUID(String s) throws java.sql.SQLException {
        super();
        this.setType("uuid");
        this.setValue(s);
    }
}

Then the following piece of code will succeed:

 java.sql.PreparedStatement stmt =
 conn.prepareStatement("UPDATE t SET uid = ? WHERE id = 1");
 stmt.setObject(1, new UUID("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"));
 stmt.executeUpdate();

Answer 2

The driver supports batched statements to speed up bulk inserts.

And using batched statements is a lot more portable than using proprietary INSERT syntax (and as far as I can tell, there is no big different between a multi-row insert and batched inserts)

Check out PreparedStatement.addBatch()

The reason why UUID is not supported is probably that UUID is not part of the Postgres core, just a contrib module.

Edit
Regarding the execute heterogeneous statements

The Postgres driver does support different types of statements in the a batch.

The following works fine:

Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "foo", "bar");
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("create table foo (id integer, data varchar(100))");
stmt.addBatch("insert into foo values (1, 'one')");
stmt.addBatch("insert into foo values (2, 'two')");
stmt.addBatch("update foo set data = 'one_other' where id = 1");
stmt.executeBatch();
con.commit();

Although you do lose the automatic escaping that PreparedStatement gives you.

Answer 3

I realise this doesn't answer your entire question, but hopefully it will be useful all the same.

I'm using Java 6 and Postgres 8.4. The driver I'm using is in my Maven POM file as:

<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>8.4-702.jdbc4</version>
</dependency>

I'm using PreparedStatement.getObject() and PreparedStatement.setObject() with Java's java.util.UUID class to retrieve and store UUIDs.

For example:

pstm.setObject(1, guid); //where pstm is a PreparedStatement and guid is a UUID

and:

//where rs is a ResultSet
UUID myGuid = (UUID) rs.getObject("my_uuid_column_name"); 

Works fine.

With newer drivers, the following is alsow supported

UUID myGuid = rs.getObject("my_uuid_column_name", UUID.class);