This discussion is archived
9 Replies Latest reply: Jun 7, 2007 4:44 AM by 807606 RSS

Prepared Statements taking Arraylist as input

807606 Newbie
Currently Being Moderated
I have got a prepared statement to be written.
Let me explain how far i am, before i describe where i am stuck!

Consider this as the query which i'm doing a Prep Stmnt.
SQL Query:
select order,state,pin from details_table where state in('a','b','c') and pin='20'
Note: I get the state values in an array list, whose size i don't know.

My code so far:
String SQL_QUERY="select order,state,pin from details_table where pin=? and state in (?)";
conn = sybConn.getConnection();
pstmt = StatementFactory.getStatement(conn, SQL_QUERY);
pstmt.setString(1, pinValue);
//pstmt.setString(2, stateValue);
//here comes the catch. how do i present the values for the state?
ResultSet rs = pstmt.executeQuery();
The values for pin(int) and state(arraylist) come as arguments.
Any hints?

Thanks in Advance.
  • 1. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    //here comes the catch. how do i present the values
    for the state?
    You have to reformat the state as a comma-separated string, like "Alabama,Oregon,Texas". Then, setting it as a string will work just fine:
    import java.util.ArrayList;
    
    public class ArrayListToString {
    
        public static String getString(ArrayList<String> states) {
            String ret="";
            for (int i=0; i<states.size(); i++) {
                if (i==0)
                    ret += (String)states.get(i);
                else
                    ret += "," + (String)states.get(i);
            }
            return ret;
        }
    
        public static void main(String[] args) {
            ArrayList<String> states = new ArrayList<String>();
            states.add("Alabama");
            states.add("Oregon");
            states.add("Texas");
    
            System.out.println( getString(states) );
        }
    }
  • 2. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    String getListAsString( List aList ) {
       String listAsString = aList.toString();
       return listAsString.substring( 1, listAsString.length() - 1 );
    }
  • 3. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    > String getListAsString( List aList ) {
    
    String listAsString = aList.toString();
    return listAsString.substring( 1,
    listAsString.length() - 1 );
    }
    Much more succinct, and equally accurate. Thanks :).
  • 4. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    Yours is more pedagogic and answers the question in a better way. Mine is a hack but thanks!
  • 5. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    Thanks for the response. But that isn't working.

    If this is where i add the string,
    prepstatmnt.setString(1,resultString);
    SQL adds singlequote ( ' ) before and after resultString.

    Say, if resultString = alabama,texas,ohio
    SQL takes it as, 'alabama,texas,ohio' , which gives me a wrong result.
    If resultString= alabama','texas','ohio Java doesn't compile.

    Still i am at a loss!
  • 6. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    Say, if resultString = alabama,texas,ohio
    SQL takes it as, 'alabama,texas,ohio' , which gives
    me a wrong result.
    If resultString= alabama','texas','ohio Java doesn't
    compile.

    Still i am at a loss!
    Ahh, I see your problem, now.

    Try using something like:
    prepstatmnt.setArray( list.toArray() );
    From what I can tell, support for it depends on the database you're using.

    EDIT: Ignore this. It's not even the right type of Array that it takes in as a parameter (it takes a java.sql.Array).

    See this post for details :). Sorry for the misdirection.
  • 7. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    list.toArray() returns an Object.

    But,
    prepstatmnt.setArray(int, Array);
    takes some Array as the second parameter. It isn't taking a Java Object or a String Array.

    I'm not able to figure out what Array it is!!!
  • 8. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    Yeah, sorry about that, I tried to edit that post before you read it, but I guess I wasn't quick enough :-\.

    It seems there isn't an easy answer. You can either dynamically insert the " IN (?,?,?)" depending on how many values you have, and then use setString on the specific parts, or you can use trickery within the query (that is also likely to break database-independence) to cleverly insert the list.

    If you're trying to find records on the top five, that'd be easy, but if you don't know how big the list is, you're going to have to dynamically create your SQL statement with the appropriate amount of question marks. Sorry about the trouble, and good luck with the fix.
  • 9. Re: Prepared Statements taking Arraylist as input
    807606 Newbie
    Currently Being Moderated
    The number of values in the array is what i don't know!!! Finally i had to do it dynamically. But the whole point of using a Prepared Statement goes for a toss!

    By the way, the DB is Sybase.
    Anyways, thanks.