OmniSciDB  a987f07e93
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
HeavyAIPrepareTest.java
Go to the documentation of this file.
1 package ai.heavy.jdbc;
2 
3 import static org.junit.Assert.*;
4 
5 import org.junit.After;
6 import org.junit.Before;
7 import org.junit.Test;
8 
9 import java.sql.*;
10 import java.util.Properties;
11 
12 public class HeavyAIPrepareTest {
13  static Properties PROPERTIES = new Property_loader("prepare_test.properties");
14  static final String url = PROPERTIES.getProperty("default_db_connection_url");
15  static final String user = PROPERTIES.getProperty("default_super_user");
16  static final String password = PROPERTIES.getProperty("default_user_password");
17 
18  private Connection m_conn = null;
19 
20  @Before
21  public void setUp() throws Exception {
22  Properties pt = new Properties();
23  pt.setProperty("user", user);
24  pt.setProperty("password", password);
25  m_conn = DriverManager.getConnection(url, pt);
26  }
27 
28  @After
29  public void tearDown() throws Exception {
30  m_conn.close();
31  }
32 
33  @Test
34  public void prepare_test() throws Exception {
35  Statement statement = m_conn.createStatement();
36  {
37  statement.executeUpdate(PROPERTIES.getProperty("drop_base_t1"));
38  statement.executeUpdate(PROPERTIES.getProperty("create_base_t1"));
39  String prepare_insert_statement =
40  "insert into test_prepare_table1 (cc, bb, aa) values (?,?,?)";
41  PreparedStatement pr1 = m_conn.prepareStatement(prepare_insert_statement);
42  int aa_i = 100;
43  int bb_i = 1000;
44  int cc_i = 10000;
45  pr1.setInt(1, cc_i);
46  pr1.setInt(2, bb_i);
47  pr1.setInt(3, aa_i);
48  pr1.executeUpdate();
49  ResultSet rs = statement.executeQuery("select cc, bb, aa from test_prepare_table1");
50 
51  while (rs.next()) {
52  int returned_cc = rs.getInt("cc");
53  assertEquals(cc_i, returned_cc);
54  int returned_bb = rs.getInt("bb");
55  assertEquals(bb_i, returned_bb);
56  int returned_aa = rs.getInt("aa");
57  assertEquals(aa_i, returned_aa);
58  }
59  statement.executeUpdate(PROPERTIES.getProperty("drop_base_t1"));
60  }
61  {
62  statement.executeUpdate(PROPERTIES.getProperty("drop_base_t2"));
63  statement.executeUpdate(PROPERTIES.getProperty("create_base_t2"));
64  String prepare_insert_statement2 =
65  "insert into test_prepare_table2 (cc, bb, aa) values (?,?,?)";
66  PreparedStatement pr2 = m_conn.prepareStatement(prepare_insert_statement2);
67  String aa_s = "100";
68  String bb_s = "1000";
69  String cc_s = "10000";
70  pr2.setString(1, cc_s);
71  pr2.setString(2, bb_s);
72  pr2.setString(3, aa_s);
73  pr2.executeUpdate();
74  ResultSet rs2 =
75  statement.executeQuery("select cc, bb, aa from test_prepare_table2");
76 
77  while (rs2.next()) {
78  String returned_cc = rs2.getString("cc");
79  assertEquals(cc_s, returned_cc);
80  String returned_bb = rs2.getString("bb");
81  assertEquals(bb_s, returned_bb);
82  String returned_aa = rs2.getString("aa");
83  assertEquals(aa_s, returned_aa);
84  }
85  statement.executeUpdate(PROPERTIES.getProperty("drop_base_t2"));
86  }
87  }
88 
89  @Test
90  public void get_metadata() throws Exception {
91  Statement statement = m_conn.createStatement();
92  statement.executeUpdate(PROPERTIES.getProperty("drop_base_t3"));
93  statement.executeUpdate(PROPERTIES.getProperty("create_base_t3"));
94  statement.executeQuery("insert into test_prepare_table3 values(1, 1.1, 'one')");
95  ResultSetMetaData md = null;
96 
97  PreparedStatement pr_select_no_params =
98  m_conn.prepareStatement("select aa, bb, cc from test_prepare_table3");
99  md = pr_select_no_params.getMetaData();
100  assertNotNull(md);
101  assertEquals(md.getColumnCount(), 3);
102  assertEquals(md.getColumnName(1), "aa");
103  assertEquals(md.getColumnType(1), Types.INTEGER);
104  assertEquals(md.getColumnType(2), Types.DOUBLE);
105 
106  PreparedStatement pr_select_with_params = m_conn.prepareStatement(
107  "select bb, aa from test_prepare_table3 where cc <> ? and aa > ?");
108  md = pr_select_with_params.getMetaData();
109  assertNotNull(md);
110  assertEquals(md.getColumnCount(), 2);
111  assertEquals(md.getColumnName(1), "bb");
112  assertEquals(md.getColumnType(1), Types.DOUBLE);
113  assertEquals(md.getColumnType(2), Types.INTEGER);
114 
115  // TODO: Re-enable commented SQL statement after Calcite parser migration is
116  // completed.
117  // String commented_sql_statement = " \n \n"
118  // + "-- comment\n"
119  // + "\n\n"
120  // + "/*some\n"
121  // + "multiline\n"
122  // + "comment\n"
123  // + "-- comment inside comment\n"
124  // + "*/ \n"
125  // + "-- another /*tricky edge case/*\n"
126  // + " select bb, aa from test_prepare_table3 where cc <> ? and aa >
127  // ?";
128  String sql_statement =
129  " \n \n select bb, aa from test_prepare_table3 where cc <> ? and aa > ?";
130  PreparedStatement pr_select_with_params_and_comments =
131  m_conn.prepareStatement(sql_statement);
132  md = pr_select_with_params_and_comments.getMetaData();
133  assertNotNull(md);
134  assertEquals(md.getColumnCount(), 2);
135 
136  PreparedStatement pr_insert = m_conn.prepareStatement(
137  "insert into test_prepare_table3(aa, bb, cc) values (?, ?, ?)");
138  md = pr_insert.getMetaData();
139  assertNull(md);
140 
141  PreparedStatement pr_insert_from_select = m_conn.prepareStatement(
142  "insert into test_prepare_table3(aa, bb, cc) select aa, bb, cc from test_prepare_table3 where cc <> ?");
143  md = pr_insert_from_select.getMetaData();
144  assertNull(md);
145 
146  statement.executeUpdate(PROPERTIES.getProperty("drop_base_t3"));
147  }
148 
149  private void formBatch(int start, int end, PreparedStatement ps, Integer[][] ia)
150  throws Exception {
151  for (int i = start; i < end; ++i) {
152  ps.setInt(1, i);
153  ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
154  if (ia[i] != null) {
155  ps.setArray(3, m_conn.createArrayOf("INT", ia[i]));
156  } else {
157  ps.setNull(3, Types.ARRAY);
158  }
159  ps.addBatch();
160  }
161  }
162 
163  @Test
164  public void batchTest() throws Exception {
165  Statement stmt = m_conn.createStatement();
166  stmt.executeUpdate("DROP TABLE IF EXISTS batch_tbl");
167  stmt.executeUpdate("CREATE TABLE batch_tbl ("
168  + "i INTEGER,"
169  + "t TIMESTAMP,"
170  + "ia INTEGER[])");
171  Integer[][] ia = {{1, 10, 100}, {null}, null, {543, null, null, 123, 543}, {17}};
172  Integer[][] ia2 = {{12345, 12, null, 1234, null}, {1, -1, -2, 2, 3, -3, -4, 4, -5}};
173  PreparedStatement ps =
174  m_conn.prepareStatement("INSERT INTO batch_tbl VALUES(?, ?, ?)");
175  formBatch(0, 4, ps, ia);
176  int[] result = ps.executeBatch();
177  for (int i : result) {
178  assertEquals(i, 1);
179  }
180  formBatch(0, 2, ps, ia2);
181  ps.clearBatch();
182  formBatch(4, 5, ps, ia);
183  result = ps.executeBatch();
184  assertEquals(result.length, 1);
185  assertEquals(result[0], 1);
186  ps.close();
187 
188  ResultSet rs = stmt.executeQuery("SELECT i, ia FROM batch_tbl");
189  int i = 0;
190  while (rs.next()) {
191  assertEquals(rs.getInt("i"), i);
192  if (ia[i] == null) {
193  assertNull(rs.getArray("ia"));
194  } else {
195  assertArrayEquals((Integer[]) rs.getArray("ia").getArray(), ia[i]);
196  }
197  i++;
198  }
199  assertEquals(i, 5);
200  }
201 
202  @Test
203  public void partialBatchTest() throws Exception {
204  Statement stmt = m_conn.createStatement();
205  stmt.executeUpdate("DROP TABLE IF EXISTS partial_batch_tbl");
206  stmt.executeUpdate("CREATE TABLE partial_batch_tbl ("
207  + "i INTEGER,"
208  + "ia INTEGER[],"
209  + "s TEXT ENCODING DICT,"
210  + "ls LINESTRING)");
211  PreparedStatement ps = m_conn.prepareStatement("INSERT INTO partial_batch_tbl(i, ia)"
212  + " VALUES(?, ?)");
213  Integer[] is = {1, 2, null, 4};
214  Integer[][] ias = {{1, 2, 3}, {10, 20, 30}, null, {1000, 2000, 3000}};
215  String[] ss = {null, null, "One", "Two"};
216  String[] lss = {null, null, "LINESTRING (0 1,2 2)", "LINESTRING (4 1,5 3)"};
217  ps.setInt(1, is[0]);
218  ps.setArray(2, m_conn.createArrayOf("INT", ias[0]));
219  ps.addBatch();
220  ps.setInt(1, is[1]);
221  ps.setArray(2, m_conn.createArrayOf("INT", ias[1]));
222  ps.addBatch();
223  ps.executeBatch();
224  ps.close();
225  ps = m_conn.prepareStatement("INSERT INTO partial_batch_tbl(s, ls)"
226  + " VALUES(?, ?)");
227  ps.setString(1, ss[2]);
228  ps.setString(2, lss[2]);
229  ps.addBatch();
230  ps.executeBatch();
231  ps.close();
232  ps = m_conn.prepareStatement("INSERT INTO partial_batch_tbl(i, ia, s, ls)"
233  + " VALUES(?, ?, ?, ?)");
234  ps.setInt(1, is[3]);
235  ps.setArray(2, m_conn.createArrayOf("INT", ias[3]));
236  ps.setString(3, ss[3]);
237  ps.setString(4, lss[3]);
238  ps.addBatch();
239  ps.executeBatch();
240  ps.close();
241 
242  ResultSet rs = stmt.executeQuery("SELECT i, ia, s, ls FROM partial_batch_tbl");
243  int i = 0;
244  while (rs.next()) {
245  assertEquals(rs.getObject("i"), is[i] == null ? null : is[i].longValue());
246  if (ias[i] == null) {
247  assertNull(rs.getArray("ia"));
248  } else {
249  assertArrayEquals((Integer[]) rs.getArray("ia").getArray(), ias[i]);
250  }
251  assertEquals(rs.getString("s"), ss[i]);
252  assertEquals(rs.getString("ls"), lss[i]);
253  i++;
254  }
255  assertEquals(i, 4);
256  }
257 }
void formBatch(int start, int end, PreparedStatement ps, Integer[][] ia)