Hotline for IT engineers/model developers: December 2017

Background: I needed to execute query with IN clause where the list is dynamic in nature. The database is sql server 2012 and the driver doesn't support java.sql.Statement.setArray(1, java.sql.Array) yet. Had to come up with the below trick to make it work. In the example, tried it on both SELECT and DELETE. Code is written in Java and tried on SQL server 2012.

Script to create table and populate data:

CREATE TABLE employee (id INT, name VARCHAR(1000));

INSERT INTO employee (id, name) VALUES(1, 'First');
INSERT INTO employee (id, name) VALUES(2, 'Second');
INSERT INTO employee (id, name) VALUES(3, 'Third');
INSERT INTO employee (id, name) VALUES(4, 'Fourth');

Java Class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestParameterizedIN {

 // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
   static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=testingGround";

   // Database credentials
   static final String USER = "***";
   static final String PASS = "***";

   public static void main(String[] args) {
     Connection conn = null;
     PreparedStatement stmt = null;
     try {
       // Register JDBC driver
       Class.forName(JDBC_DRIVER);

       // Open a connection
       System.out.println("Connecting to database...");
       conn = DriverManager.getConnection(DB_URL, USER, PASS);

       //Fetch records
       System.out.println("Fetching data...");
       int[] ids = new int[]{1,2,3};//IDs of records to select data
       String sql = "";
       for(int each=0; each<ids.length; each++){
        if(each == 0)
         sql = "SELECT id, name FROM employee WHERE ID IN (?";
        else
         sql += " ,?";
       }
       sql += ")";
       
       System.out.println("--------------------");
       stmt = conn.prepareStatement(sql);
       for(int each=0; each<ids.length; each++){
        stmt.setInt(each+1, ids[each]);
       }
       try(ResultSet rs = stmt.executeQuery()){
      // Extract data from result set
        while (rs.next()) {
          // Retrieve by column name
          int id = rs.getInt("id");
          String name = rs.getString("name");

          System.out.print("ID: " + id);
          System.out.print(", Name: " + name);
          System.out.println("");
        }
       }
       System.out.println("--------------------");
       stmt.close();
       
       //Delete records
       System.out.println("Delete records...");
       sql = "";
       for(int each=0; each<ids.length; each++){
        if(each == 0)
         sql = "DELETE FROM employee WHERE ID IN (?";
        else
         sql += " ,?";
       }
       sql += ")";
       
       stmt = conn.prepareStatement(sql);
       for(int each=0; each<ids.length; each++){
        stmt.setInt(each+1, ids[each]);
       }
       System.out.println(stmt.executeUpdate()+" records are deleted");
       
     } catch (SQLException se) {
       se.printStackTrace();
     } catch (Exception e) {
       e.printStackTrace();
     } finally {
       // finally block used to close resources
       try {
         if (stmt != null)
           stmt.close();
       } catch (SQLException se2) {
       }
       try {
         if (conn != null)
           conn.close();
       } catch (SQLException se) {
         se.printStackTrace();
       }
     }
   }
}

Output of the class execution:

Connecting to database...
Fetching data...
--------------------
ID: 1, Name: First
ID: 2, Name: Second
ID: 3, Name: Third
--------------------
Delete records...
3 records are deleted

Note:

Used mssql-jdbc-6.2.2.jre8.jar for SQL Server driver.
Will work on another blog to try out java.sql.Statement.setArray(1, java.sql.Array), may be on HSQLDB.

Hotline for IT engineers/model developers

Saturday, December 9, 2017

Java - JDBC - use parameterized query with IN clause having dynamic list