Java JDBC IN Clause Example with PreparedStatement MySQL


Post Banner

As you must be aware that createArrayOf() method from the Connection Interface will give you a java.sql.SQLFeatureNotSupportedException exception. One of the "not so ideal" ways to achieve the IN Clause in MySQL is to dynamically create the number of parameters as ? in your IN Clause.


import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.stream.Collectors;
import java.util.stream.Stream;


public class JDBCInClauseExample {
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //The elements for IN Clause
        ArrayList<String> studentNames = new ArrayList<>();
        studentNames.add("Harry");
        studentNames.add("Ron");
        
        //Create an array of same size
        String[] arrayOfQuestionMarks = new String[studentNames.size()];

        //Create a array of question marks ?
        for(int i=0;i< studentNames.size();i++) {
            arrayOfQuestionMarks[i] = "?";
        }

        //Create a string of question marks ?
        String inClause = String.join(",", arrayOfQuestionMarks);
        
        String url ="jdbc:mysql://localhost:3306/my_uat";
        String userName="root";
        String password ="root123";
        String selectQuery ="Select * from students where student_name in ("+inClause+")";
        Connection connection = DriverManager.getConnection(url,userName,password);
        PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
       
        for(int i=0;i< studentNames.size();i++) {
            preparedStatement.setString(i+1, studentNames.get(i));
        }

        ResultSet resultSet = preparedStatement.executeQuery();

        while(resultSet.next()) {
            System.out.print(resultSet.getInt(1)+"\t");
            System.out.print(resultSet.getString(2)+"\t");
            System.out.print(resultSet.getString(3)+"\t");
            System.out.print(resultSet.getString(4)+"\t");
            System.out.println("");
        }
    
    }
}
Output

1 Harry 1997-01-03 00:00:00 London
2 Ron 1999-03-19 00:00:00 Paris



Have Questions? Post them here!
Advertisements
Try Out Code2care Dev Tools:

Advertisements

Advertisements
Code2care is an initiative to publish and share varied knowledge in programming and technical areas gathered during day-to-day learnings and development activities.

Students and software developers can leverage this portal to find solutions to their various queries without re-inventing the wheel by referring to our easy to understand posts. Technical posts might include learnings, tutorials, trouble-shooting steps, video tutorials, code snippets, how-to, blogs, articles, etc.

🎉 We are celebrating the 10th years of Code2care! Thank you for all your support!

We strongly support Gender Equality & Diversity.