Arrays, PreparedStatements, JDBC and Oracle

Well, more for myself to remember if I ever need this again (although I don’t think I won’t remember this… ;-) )
Ever had the problem with Java, JDBC and PreparedStatements to do this:
SELECT * FROM TESTTABLE WHERE ID IN (?)

And now you want to use PreparedStatements via JDBC?
So here is what you need to do (and believe me it is not quite as simple as you think…)

First try on your Java Code, as it seems to be obvious to use the setArray-Method of JDBC. But mention the Query-Part after the IN-Clause.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package de.itemis.jdbc.learningtests;
 
  import static org.junit.Assert.assertEquals;
  import static org.junit.Assert.fail;
 
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
 
  import oracle.sql.ARRAY;
  import oracle.sql.ArrayDescriptor;
 
  import org.junit.After;
  import org.junit.Before;
  import org.junit.Test;
 
  public class ArraysAndPreparedStatements {
 
      private Connection con = null;
 
      @Before
      public void setUp() throws Exception {
          Class.forName("oracle.jdbc.driver.OracleDriver");
          String url = "jdbc:oracle:thin:@localhost:1521:TEST";
          String user = "SCOTT";
          String password = "TIGER";
          con = DriverManager.getConnection(url, user, password);
      }
 
      @After
      public void tearDown() throws Exception {
          if (con != null) {
              con.close();
          }
      }
 
      @Test
      public void preparedStatementWithInClause() throws Exception {
          if (con != null) {
              String[] ids = new String[] { "1", "2", "4" };
              ARRAY idArray = null;
              try {
                  ArrayDescriptor arrayDescriptor = ArrayDescriptor
                          .createDescriptor("TT_VARCHAR", con);
                  idArray = new ARRAY(arrayDescriptor, con, ids);
                  String query = "select * from testtable " + 
                                 "where id in (select COLUMN_VALUE from table(?))";
                  PreparedStatement stmt = con
                          .prepareStatement(query);
                  stmt.setArray(1, idArray);
                  ResultSet rs = stmt.executeQuery();
                  int rowCount = 0;
                  while (rs.next()) {
                      System.out.println(rs.getString("ID")
                              + rs.getString("NAME"));
                      rowCount++;
                  }
                  assertEquals(3, rowCount);
              } catch (Exception e) {
                  e.printStackTrace();
                fail("Exception on SQL-Query: " + e.getMessage());
              }
          }
      }
  }

Also make sure, you have the TT_VARCHAR-Type defined in your Oracle Database and mention the new line with the slash.
(in my tests oracle only compiled this Statement)

1
2
  CREATE TYPE tt_varchar AS TABLE OF varchar2(32);
  /

Your Test-Table for this Learning-Test could be this one with inserting some data:

1
2
3
4
5
6
7
8
9
  CREATE TABLE testtable(
     ID    VARCHAR2(32),
     NAME  VARCHAR2(255)
   );
 
   INSERT INTO testtable (ID, NAME) VALUES ('1', 'Hans');
   INSERT INTO testtable (ID, NAME) VALUES ('2', 'Hugo');
   INSERT INTO testtable (ID, NAME) VALUES ('3', 'Horst');
   INSERT INTO testtable (ID, NAME) VALUES ('4', 'Heinrich');

Be sure you have a type for each column-type you will fire queries on, in this case the ID Column as VARCHAR2(32).
With this your PreparedStatements should also work for IN-Clause with Arrays.
Note:
You need the real oracle connection to create the ArrayDescriptor. So if you are working within a Container and use a DataSource, be sure,
you are not passing a Wrapper-Connection-Object!
Update:
Thanks to my colleague Frank at my current customer who digged in really deep to find this solution!

Autor:
Datum: Thursday, 5. March 2009 21:14
Trackback: Trackback-URL Themengebiet: diesunddas

Feed zum Beitrag: RSS 2.0 Diesen Artikel kommentieren

9 Kommentare

  1. 1

    Hmmm … I really can remember this problem in some projects and also some ugly workarounds for this. The solution here is the most elegant for this scenario i’ve seen so far. Glad you posted it here …

    Thanks,
    Wolfgang

  2. 2

    I tried your example, but it didn’t work.

    I added this code after idArray is set:

    System.out.println(((Object[])idArray.getArray())[0]);
    System.out.println(idArray.length());

    And it printed this:
    ???
    3

    Any idea what may be wrong? Using jdk6 with ojdbc14.jar from oracle’s website.

  3. 3

    Well, I tried your System.outs in my example and they worked:
    1 (first id in array)
    3 (length of array)

    I tested my Example with Oracle and Oracle XE, JDK 1.6 and ojdbc14.jar, Version 10.0.2.2.
    I run it on WinXP in a Parallels VM, so maybe you post your System-Environment maybe we find the problem on your side.

    Cheers,
    Michael

  4. 4

    Thanks for your solution. Just about every other person on the web said it couldn’t be done, while a few people posted partial solutions. This is the most complete one I’ve found after a couple days of searching. Thanks for posting it.

  5. 5

    My search hunt stops from here on. thanks dude

  6. 6

    [...] didn’t find my own demo code anymore, so I googled for a quick example in Java –¬†http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/. If you do have your own code examples, feel free to post links to these into [...]

  7. 7

    Hello! I have the same problem as Blink mentioned:
    idArray.getArray()[0] return “???”. Any ideas?

  8. 8

    Hello! I have the same problem as Blink mentioned.
    idArray.getArray()[0] = “???”.
    Any suggestions? I use oracle 11g, ojdbc6.jar.

  9. 9

    [...] http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/ [...]

Kommentar abgeben