* How to Conveniently and Flexibly Pass an Array Parameter to SQL from Java


We need to modify the SQL query in a special way when Java passes in an array parameter to it. To pass array [1,2,4] to the SQL query select * from EMP where EID in(?), for instance, the query is in the format of select * from EMP where EID in(?, ?, ?). The number of question marks is the same as the length of the parameter array. The query should be rewritten whenever the length of the parameter array is changed. That’s inconvenient. If the passed-in parameter is a string or datatime, they should be enclosed by double quotes or converted to right type. This compromises the query’s flexibility and generality.
Yet you can conveniently and flexibly implement the pass-in of any array parameter through esProc.
Download esProc installation package and free license file
HERE.

Let’s take the above instance to see how esProc works to get this done.

1. Start esProc, configure database connection and find a name for it. The parameters should be consistent with those in the JDBC connection configuration.
Do the configurations in the following window(Tool->Datasource Connection->Datasouce):
undefined

If the database connection is already configured, just skip this step.

2. Write script in esProc and define an array parameter named param:

undefined


Execute the following SPL script test.dfx to accomplish the task:

A

B

1

=connect("db")

/ Connect to database

2

=A1.query@x("select *   from EMP where  EID in(?)", param)

/ Make query using the condition   of an array parameter


3. Execute the script and return result:

A2

EID

NAME

SURNAME

GENDER

STATE

BIRTHDAY

HIREDATE

DEPT

SALARY

1

Rebecca

Moore

F

California

1974-11-20

2005-03-11

R&D

7000

2

Ashley

Wilson

F

New York

1980-07-19

2008-03-16

Finance

11000

4

Emily

Smith

F

Texas

1985-03-07

2006-08-15

HR

7000

4. Integrate result into the Java program

esProc provides JDBC driver to integrate the script in the following way:

public static void testDataServer() {
Connection con =null;
java.sql.PreparedStatement st;
try{
  int[] ls =new int[]{6,7,9,10}; // array parameter
   Class.forName("com.esproc.jdbc.InternalDriver");
  con= DriverManager.getConnection("jdbc:esproc:local://"); 
  // Call the test.dfx script
  st =con.prepareCall("test ?");
  // Just enter the SQL query as it is without modification
  st.setObject(1, ls);
  st.execute();
  // Get result set
  ResultSet rst = st.getResultSet();
  System.out.println(rst);
}catch(Exception e){
  System.out.println(e);
}finally{
  // Close database connection
 if(con!=null) {
    try{
      con.close();
    }catch(Exception e) {
      System.out.println(e);
    }
   }
 }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc script into a Java progam.
Similarly, you can handle the commonly-seen multi-condition queries that use an indefinite number of parameters with esProc conveniently.