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();