Eliminating quotes in prepared statement in mysql n java

Question

I have a prepared statement like this

stmt = select * from table_name where id IN (?);

Once I pass the parameters the stmt looks like way

stmt = select * from table_name where id IN ('1,2,3');

There is no error while executing the query. However the resultset is returned only for the id=1. Is there some way I can eliminate the quotes / get the resultset for all these id's.

stmt = select * from table_name where id IN (?);

select GROUP_CONCAT(id) id from table ;

if(rs.next()){

    stmt.setString(1,rs.getString("id"));

    stmt.executeQuery();
}

Thanks in advance.

Your ORM is treating the variable you are passing in as a string, not an array. You probably have to just pass an array to it instead. What ORM are you using? Show the full code you use to run the query. — Ben Lee, Feb 29 '12 at 11:37
the ids are the resultset of another query when i use stmt.setString(1,rs.getstring(id)); the quotes bind with it internally — Archana, Feb 29 '12 at 11:38
@Archana, please edit your answer and post the full code you are using to generate that query. — Ben Lee, Feb 29 '12 at 11:44
For another explanation, along with a couple alternative solutions, see here http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives and then here - http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 — Perception, Feb 29 '12 at 12:28

score 4 · Accepted Answer · answered Feb 29 '12 at 11:37

4

It's not clear what the ID type is, but I believe you should actually be preparing a statement with each possible value as a separate parameter:

select * from table_name where id IN (?, ?, ?)

Then add the three values for the three parameters. It's a common problem with parameterized SQL - when you want to be able to specify a variable number of values, you need to vary the SQL. There may be a MySQL-specific way of coping with this (like table-valued parameters in SQL Server 2008) but I don't believe there's a generic JDBC way of doing this.

answered Feb 29 '12 at 11:37

Jon Skeet

1,421,763
867
9,128
9,194

Why would you do it that way? Most ORMs (all ORMs I've ever seen) allow you to essentially use a "(?)" as a splat if you pass an array for that parameter. No need to know the number of items ahead of time. – Ben Lee Feb 29 '12 at 11:40
1

@BenLee: Most ORMs would, sure - but they'll be generating *SQL* with the right number of ?s. There's a big difference between what you give to an ORM and what it does to fire off a query. There's no indication that the OP is even using an ORM. – Jon Skeet Feb 29 '12 at 11:41
Ah, I see what you're saying now. That's true, but I believe the OP already knows that. It looks like he is just having problems with his ORM. I would say the fact that he has a "prepared statament" and talks about "passing parameters" are strong indications that he is using an ORM. – Ben Lee Feb 29 '12 at 11:42
@BenLee: Um, I don't think so. `java.sql.PreparedStatement` and the various `set` methods which are used to set parameters. On the other hand, there's *nothing* in the description which indicates an ORM. – Jon Skeet Feb 29 '12 at 11:52
stmt = select * from table_name where id IN (?); select GROUP_CONCAT(id) id from table ; if(rs.next()){ stmt.setString(1,rs.getString("id")); stmt.executeQuery(); } the code is something like this. I dont have a prior knowledge of the number of ids fetched in the prev query – Archana Feb 29 '12 at 11:56
@Archana: Rather than getting the database to concatenate the IDs, you should fetch them yourself, then build the SQL with the appropriate number of parameters. – Jon Skeet Feb 29 '12 at 11:59
There can be more then a 100 ids.. If i concatenate in the code it will be time consuming. – Archana Feb 29 '12 at 12:06
@Archana: You're doing a *database query*. Do you really think a few simple string operations (using `StringBuilder` for efficiency) will be the bottleneck? – Jon Skeet Feb 29 '12 at 12:07
Even though i build the string. I wont know the number to parameters to be passed when creating the prepared stmt – Archana Feb 29 '12 at 12:15
@Archana: Yes you will - you just need to delay preparing the statement until you've got the parameters. – Jon Skeet Feb 29 '12 at 12:30
hmm Oh that way.. Will it be ok if i put the prepared statement within a loop? won't it compile every time it enters the loop? – Archana Feb 29 '12 at 12:32
@Archana: Yes, it will be prepared each time - although if you've got a smart connection pool it may only prepare it once for each number of parameters. However, it'll still be relatively quick. See also the link that Perception mentioned: http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 – Jon Skeet Feb 29 '12 at 12:35
oh ok i got it.. But the thing is the ids can be upto 10000. Looping like this will be a bad idea i think.. There is really no other function to eliminate the quotes which it binds internally? Thank you soo much for the quick reply – Archana Feb 29 '12 at 12:42
@Archana: If you have 10,000 IDs to search through and you've already fetched this data from a database, there's probably a better approach using a join... – Jon Skeet Feb 29 '12 at 12:59

Eliminating quotes in prepared statement in mysql n java

1 Answers1