Java passing values into IN where clause

Question

below string x can grow dynamically. So when I use setstring it throws error as ORA-01722: invalid number

Any help would be highly appreciated.

String x= "'628','633','634','636','723'"

String grpStructure = "select grp_id, tkn_id from group_structure where grp_id in (?) order by grp_id";
            try (PreparedStatement pst = conn2.prepareStatement(groupStructure)) {
                ResultSet rs;
                pst.setString(1, x);
                rs = pst.executeQuery(); 
                while (rs.next()) {
                groupId = rs.getString("grp_id");
                tokenId= rs.getString("tkn_id");
                    System.out.println("group_id:" + grpId + "  token_id :"+tknId );
                }
            }

refer this page https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause — Doctor Who, Apr 21 '21 at 12:05

score 0 · Answer 1 · answered Apr 21 '21 at 12:04

The in SQL clause accept an Array not a String. You should try with x values in an array of String

String[] x= new String[] { "628", "633", "634", "636", "723" };

String grpStructure = "select grp_id, tkn_id from group_structure where grp_id in (?) order by grp_id";
try (PreparedStatement pst = conn2.prepareStatement(groupStructure)) {
    ResultSet rs;
    pst.setArray(1, x);
    rs = pst.executeQuery(); 
    while (rs.next()) {
        groupId = rs.getString("grp_id");
        tokenId= rs.getString("tkn_id");
        System.out.println("group_id:" + grpId + "  token_id :"+tknId );
    }
}

Java passing values into IN where clause

1 Answers1