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

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:

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:

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

MS SQL Server - Query - Concatenate data from multiple records in a GROUP BY query

Problem: Need to find out the employees reporting to a manager and create a comma separated list of names. Data will look like table-01.

Expected output would look like:
Table-01:

Solution-01: using FOR XML PATH

-- Create table
CREATE TABLE 
#employeeManagerAssociation (managerID INT, employeeID INT, firstName VARCHAR(100), lastName VARCHAR(100))
;

-- Insert data
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (1, 101,'E1_First', 'E1_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (1,102,'E2_First', 'E2_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (2,103,'E3_First', 'E3_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (2,104,'E4_First', 'E4_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (3,105,'E5_First', 'E5_Last');

SELECT 
  managerID,
  STUFF((
    SELECT ', ' + firstName + ' ' + lastName 
    FROM #employeeManagerAssociation 
    WHERE managerID = main.managerID
    FOR XML PATH('')
)--,TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #employeeManagerAssociation main
GROUP BY managerID

-- Delete temporary table

DROP TABLE #employeeManagerAssociation

Eclipse - Setup - How to set java for during eclipse start up

Description: 
I faced starting up Eclipse IDE which complaining about old java version. The java version in my environment was 1.6 which is needed to run old programs. I installed Java 8 did below thing to make it work.

Environment:
Eclipse version - neno.3
Old java version - 1.6.0_29
New Java version - 1.8.u111

Steps to follow:

1. Find and write down the Java 8 installation location (for me, it was in e:\java\)
2. Go to location where eclipse is installed
3. Open file 'eclipse.ini' with any file editor
4. Type the below lines - (change the java path)
   -vm
   e:\Java\jdk1.8_u111\bin\javaw.exe
5. Save the change
6. Execute the eclipse.exe