Prepared Statements taking Arraylist as input
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.
[1096 byte] By [
Pret123a] at [2007-11-27 6:36:12]
> //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) );
}
}
String getListAsString( List aList ) {String listAsString = aList.toString();return listAsString.substring( 1, listAsString.length() - 1 );}
> String getListAsString( List aList ) {
>String listAsString = aList.toString();
> return listAsString.substring( 1,
> listAsString.length() - 1 );
> }
Much more succinct, and equally accurate. Thanks :).
Yours is more pedagogic and answers the question in a better way. Mine is a hack but thanks!
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!
> 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 [url=http://forum.java.sun.com/thread.jspa?threadID=504838&messageID=2391560]this post[/url] for details :). Sorry for the misdirection.
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!!!
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.
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.
