How to use an arraylist as a prepared statement parameter

Question

I have looked and have been unable to find an answer to the following challenge I am having   It seems pretty straightforward but I have been unable to resolve it   I have an ArrayList of record ids that are type Long -  ArrayList lt Long gt    I would like to use this list of record ids to select rows from another table   So far so good   Now onto the  challenge     a  I am using a prepared statement to select the data from a table using the ArrayList as input    for this     selectPS   dbConnection prepareStatement  select columnA from tableA where id in        Question on the above - how should the parameter be defined   The above does not seem correct for    an ArrayList type parameter   b  I am also running into problems when setting the value of the parameter for the prepared    statement   There is no method for setting an ArrayList type value and I see no other viable    options   --- gt  selectPS set      1  arraylistParameter        ResultSet rs   selectPS executeQuery       Any help or direction you can set me in is greatly appreciated   Thank you

User · Answer

If you have ArrayList then convert into Array Object   ArrayList lt String gt  list   new ArrayList lt String gt     PreparedStatement pstmt                conn prepareStatement  select   from employee where id in        Array array   conn createArrayOf  VARCHAR   list toArray     pstmt setArray 1  array   ResultSet rs   pstmt executeQuery

User · Answer

why making life hard-  PreparedStatement pstmt   conn prepareStatement  select   from employee where id in     StringUtils join arraylistParameter iterator

User · Answer

You may want to use setArray method as mentioned in the javadoc below   http   docs oracle com javase 6 docs api java sql PreparedStatement html setArray int  java sql Array    Sample Code   PreparedStatement pstmt                    conn prepareStatement  select   from employee where id in        Array array   conn createArrayOf  VARCHAR   new Object    1    2   3     pstmt setArray 1  array   ResultSet rs   pstmt executeQuery

User · Answer

JulienD Best way is to break above process into two steps   Step 1    Lets say  rawList  as your list that you want to add as parameters in prepared statement   Create another list     ArrayList lt String gt  listWithQuotes   new ArrayList lt String gt      for String element   rawList       listWithQuotes add     element           Step 2    Make  listWithQuotes  comma separated   String finalString   StringUtils join listWithQuotes iterator            finalString  will be string parameters with each element as single quoted and comma separated

[java] How to use an arraylist as a prepared statement parameter

Examples related to java

Examples related to arraylist

Examples related to prepared-statement