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