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.
Java Class:
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:
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:
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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | 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.
