Last active
February 18, 2016 09:24
-
-
Save mdoering/6163607aeb681d16d4eb to your computer and use it in GitHub Desktop.
Postgres JDBC text array behavior
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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