• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

about preparestatement

 
popoe shi
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
/*==========================================
str = "'aaa','bbb','ccc'";
sql = "select * from tb where lot in (?)";
stmt = conn.prepareStatement(sql);
stmt.setObject(1,str);
============================================*/
Is this true? I can't find the value.
When change setObject to setString,it is also wrong.
Please help me,thank you.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, you need to bind to a separate '?' for each string.

the way you have done it, the single quotes would be escaped and the query would treat the whole lot as a single string eqivalent to '\'aaa\',\'bbb\',\'ccc\''
Dave
 
sandhiya sindhi
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if u want to use select statement than why u r using prepared statement
i think u have better option of Statement for Select Query.
for this u can see,
str = "'aaa','bbb','ccc'";
sql="SELECT * FROM table WHERE lot IN '"+str+"'";
stmt= con.createStatement();
reset= stmt.executeQuery(sql);
well still i dont know clearly that what u want to do...
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Translation:
u = you
r = are
Please reduce the use of contractions, they make posts harder to understand.
A PreparedStatement is perfectly acceptable for a database select. I'm actually confused about what you are proposing

The original request wanted to select where an attribute was in a list, their JDBC was almost correct, but not quite. Your code won't work since the first and last single quote will be duplicated.
This will work:

but moves away from the structure of JDBC a starts to be on-the-fly SQL code.
 
popoe shi
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
/*==========================================
String[] arr = String{aaa,bbb,ccc};
String str="";
int i=0;
while(arr[i]!=null)
{
if(i!=0) str += ",";
str += "'"+arr[i]+"'";
}
//str = "'aaa','bbb','ccc'";
sql = "select * from tb where lot in (?)";
stmt = conn.prepareStatement(sql);
stmt.setObject(1,str);
============================================*/
The arr I will use "while" to set value,so it will change.
And Array this time is arr[3],but next time is arr[10].
[ November 05, 2003: Message edited by: popoe shi ]
[ November 05, 2003: Message edited by: popoe shi ]
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(Firstly, please use a StringBuffer instead, it'll be much faster, and you can use [ code ] tags to format your code)
Take the design you have, but rather than building str dynamically, build the query dynamically and insert one ? for each element in the array.
After you have built a query string, it will look something like this:

Then you can loop through the array again and bind each string from the array to the PreparedStatement.
Dave
[ November 05, 2003: Message edited by: David O'Meara ]
 
popoe shi
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sorry,I confused.
In my code,this time sql is "select * from tb where lots in ( ?, ?, ? )".
But next time maybe "select * from tb where lots in ( ?, ?, ? ,? ,? ,? ,? ,?)"
I can't decide the amount of parameter,so why I try to use "lots in (?)"
thank you.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But what you want after everything is completed is something similar to

BUT (?) won't do it for you. If you bind everything as one string, it ends up like this:

ie the inner quotes get escaped. What you have is one String equal to "'aaa', 'bbb', 'ccc'", and this is what will be matched on. It will not match on the three individual string. This should be easy to test yourself - try it out!
This is why you need one ? for each string that you want to test on.
Dave
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic