Some tools may set JDBC drivers to some default transaction isolation level, regardless of the default of the underlying database. The setting you want, to match PostgreSQL's default, is 2, java.sql.Connection.TRANSACTION_READ_COMMITTED.
26 May 2014
Newer versions of PostgreSQL added an application_name column to the pg_stat_activity table, which is really handy for tracking connections. Long-running processes such as daemons can name their connections so that they can more easily be found in pg_stat_activity, for instance --- great for debugging.
The simplest way to set the application_name for your connection is to
just do set application_name to 'foo'; using your connection.
But, conveniently, the PostgreSQL JDBC driver will run this SQL command
for you when the connection is created, if you have set the "ApplicationName"
property for the connection, like so:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class TestApplicationName {
public static void main(String[] args) throws ClassNotFoundException, InterruptedException, SQLException {
Connection conn = null;
Properties props = new Properties();
props.setProperty("ApplicationName", "test application");
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test?user=test&password=test", props);
Thread.currentThread().sleep(10000);
/* psql to your test db, and, while this application runs for 10 seconds,
* run this query:
* select application_name from pg_stat_activity; rollback;
* You will see that application_name = 'test application' for this connection.
*/
conn.close();
}
}
NOTE: I have not checked how new the version of the PostgreSQL JDBC driver has to be to make this work, but this was tested on the 9.3-1101 driver.
When using the PostgreSQL JDBC driver, a SQLException is also a PSQLException. You can get the same server error message that you would see if you were in a psql session. This is particularly useful when you want to check for constraint violations, because Postgres names the constraints that you violate in the server error message.
try {
sqlMap.startTransaction(); // iBATIS
// some sql statement
sqlMap.commitTransaction(); // iBATIS
} catch (SQLException e) {
boolean ok = false;
Throwable t = e.getCause();
if (t instanceof PSQLException) {
PSQLException psqle = (PSQLException)t;
String serverErrorMessage = psqle.getServerErrorMessage().getMessage();
if ("duplicate key violates unique constraint \"my_table_pk\"".equals(serverErrorMessage)) {
ok = true;
log.trace("This is OK: " + serverErrorMessage + "; rolling back and moving on");
}
}
if ( ! ok) {
log.fatal("This is not good: ", e);
}
} finally {
try {
sqlMap.endTransaction();
} catch (SQLException e2) {
log.fatal("This is not good: ", e2);
}
}
27 May 2014
Let's say you create the following table, populate it with a couple of rows, copy those rows to a copy file, and then truncate the table:
create table foo (an_int_col integer not null, a_text_col text not null); commit; insert into foo (an_int_col, a_text_col) values (1, 'Hello'), (2, 'World'); commit; select * from foo; rollback; ┌────────────┬────────────┐ │ an_int_col │ a_text_col │ ├────────────┼────────────┤ │ 1 │ Hello │ │ 2 │ World │ └────────────┴────────────┘ \copy foo to foo.copy commit; truncate table foo; commit; \q
Your copy file looks like this:
$ cat foo.copy 1 Hello 2 World
Here's how you would use the PostgreSQL JDBC driver to copy the file into your database:
package com.manniwood.mmpt.test;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.postgresql.PGConnection;
import org.postgresql.copy.CopyManager;
public class Hello {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
System.out.println("Hello");
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/test", "test", "test");
conn.setAutoCommit(false);
CopyManager copyManager = ((PGConnection)conn).getCopyAPI();
FileReader fileReader = new FileReader("/home/mwood/foo.copy");
copyManager.copyIn("copy foo from stdin", fileReader);
conn.commit();
conn.close();
}
}
Please note that if you have access to a shell and regular psql sessions, psql's copy command will work well (and with greater conciseness). But, sometimes you need to do stuff from Java.
Also, the command "copy foo from stdin" is much more similar to psql's \copy command, because the copied file is on the client filesysem where JDBC is running, not the filesystem PostgreSQL is running.
package com.manniwood.postgresql;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.postgresql.PGConnection;
import org.postgresql.copy.PGCopyInputStream;
import org.postgresql.copy.PGCopyOutputStream;
/**
* @author mwood
*
*/
public class CopyTastic2 {
public static void main(String[] args) {
System.out.println("copying...");
Connection originConn = null;
Connection destinationConn = null;
byte[] buf = new byte[1024*1024];
try {
Class.forName("org.postgresql.Driver");
originConn = DriverManager.getConnection("jdbc:postgresql://originhost/my_origin_database?user=myusername&password=mypassword");
originConn.setAutoCommit(false);
PGCopyInputStream originCopyInStream = new PGCopyInputStream((PGConnection)originConn, "COPY users TO STDOUT");
destinationConn = DriverManager.getConnection("jdbc:postgresql://desthost/dest_db?user=myusername&password=mypassword");
destinationConn.setAutoCommit(false);
PGCopyOutputStream destinationCopyOutStream = new PGCopyOutputStream((PGConnection)destinationConn, "COPY users FROM STDIN");
int bytesRead = originCopyInStream.read(buf);
while (bytesRead > 0) {
destinationCopyOutStream.writeToCopy(buf, 0, bytesRead);
bytesRead = originCopyInStream.read(buf);
}
destinationCopyOutStream.endCopy();
//originCopyInStream.close(); // causes exception, so don't bother
//destinationCopyOutStream.close(); // causes exceeption, so don't bother
originConn.rollback();
destinationConn.commit();
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
try {
if (originConn != null) {
System.out.println("closing origin connection");
originConn.close();
}
if (destinationConn != null) {
System.out.println("closing destination connection");
destinationConn.close();
}
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
System.out.println("...done!");
}
}
22 June 2014
NOTE! As a general rule, you should let JDBC translate dates for you. But sometimes, you are forced to consume dates in textual formats: for example, when Java needs to consume dates from a web service backed by PostgreSQL, and dates arrive from the web service as strings that need to be parsed.
If you don't mind your dates being accurrate to only one second granularity (with Java, this means you lose milliseconds; with PostgreSQL, this means you lose milliseconds and microseconds), these date formats correspond to one another:
PostgreSQL --> YYYY-MM-DD HH24:MI:SS TZ Java --> yyyy-MM-dd HH:mm:ss z
Using these corresponding string formats, you can translate these dates from PostgreSQL's native microseconds since the epoch to seconds since the epoch; and Java's milliseconds since the epoch to seconds since the epoch.
PostgreSQL sending through some REST service:
-- result of this query gets sent out as text through some rest interface select to_char(now(), 'YYYY-MM-DD HH24:MI:SS TZ' as "myJavaDate"
Java recieving through some REST service:
include java.text.SimpleDateFormat; include java.util.Date; String date = restService.getMyJavaDate(); String format = "yyyy-MM-dd HH:mm:ss z"; SimpleDateFormat fmt = new SimpleDateFormat(format) Date = fmt.parse(date);
22 June 2014
timestamp with time zone 'epoch' + (some_bigint_holding_millisecionds * interval '1ms') );