Skip to content

Instantly share code, notes, and snippets.

@mdoering
Last active February 18, 2016 09:24
Show Gist options
  • Save mdoering/6163607aeb681d16d4eb to your computer and use it in GitHub Desktop.
Save mdoering/6163607aeb681d16d4eb to your computer and use it in GitHub Desktop.
Postgres JDBC text array behavior
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/**
*
*/
public class JdbcArrayTest {
/**
* Prepared statements in postgres jdbc have a bug when caching text[] array types.
* They return null after the 5th call.
*/
public static void testArrays(Connection c) throws Exception {
try (Statement st = c.createStatement()) {
st.execute("CREATE TABLE IF NOT EXISTS article (id serial, para text[], words int[])");
st.execute("TRUNCATE article");
}
try (PreparedStatement ps = c.prepareStatement("insert into article (id, para, words) values (?, ?, ?)");) {
String[] para = {"Gimme danger, little stranger", "And I feel you at ease",
"Gimme danger, little stranger", "And I feel your disease",
"There's nothing in my dreams", "Just some ugly memories", "Kiss me like the ocean breeze, hey"};
Integer[] words = {4,6,4,5,5,4,7};
for (int id = 0; id < 20; id++) {
Array array = c.createArrayOf("text", para);
Array arrayInt = c.createArrayOf("int", words);
ps.setInt(1, id);
ps.setArray(2, array);
ps.setArray(3, arrayInt);
ps.execute();
}
}
try (PreparedStatement ps = c.prepareStatement("select para, words from article where id=?");) {
for (int id = 0; id < 10; id++) {
ps.setInt(1, id);
ps.execute();
ResultSet rs = ps.getResultSet();
rs.next();
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
rs.close();
}
}
}
public static void main (String[] args) throws Exception {
try (Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/jdbctest", "postgres", "pogo")) {
testArrays(c);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment