File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes prepared statement with Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepared statement with "in" predicate" Watch "prepared statement with "in" predicate" New topic
Author

prepared statement with "in" predicate

Dan Murphy
Ranch Hand

Joined: Mar 29, 2005
Posts: 126
Hi,

I want to use a prepared statement to execute some SQL like



The actual list of ages will vary from one execution to the next, so the SQL string contains a parameter:



However there doesn't appear to be any method defined on PreparedStatement for setting a parameter to a list of values. Although there is a setArray() method, the array in question is of type java.sql.Array, and I've no idea how to create one of these from a "regular" Java array or Collection.

Any suggestions?

Cheers,
Dan
[ June 26, 2008: Message edited by: Dan Murphy ]

SCJP, SCJD, SCWCD
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

Dan,
There isn't a method for that. You need to use:
select * from person where age in (?, ?, ?);

Since the number of "?" varies each time, this means you need to build the String dynamically at runtime.

If you are concerned about the effects of caching multiple numbers of parameters, see this article I wrote.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Amit Kumar S
Greenhorn

Joined: Jun 27, 2008
Posts: 3
Originally posted by Dan Murphy:
Hi,

I want to use a prepared statement to execute some SQL like



The actual list of ages will vary from one execution to the next, so the SQL string contains a parameter:



However there doesn't appear to be any method defined on PreparedStatement for setting a parameter to a list of values. Although there is a setArray() method, the array in question is of type java.sql.Array, and I've no idea how to create one of these from a "regular" Java array or Collection.

Any suggestions?

Cheers,
Dan

[ June 26, 2008: Message edited by: Dan Murphy ]

----------------------------------------------------


DO THIS :
--------
int iVariable1=10;
int iVariable1=20;
int iVariable1=30;


select * from person where age in (?,?,?);
ps.setInt(1,iVariable1); //where 'ps' is ref varible of PreparedStatement
ps.setInt(1,iVariable2);
ps.setInt(1,iVariable3);

I hope.....
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38793
    
  23
Welcome to JavaRanch , Amit Soni.

You meant
int iVariable1=10;
int iVariable2=20;
int iVariable3=30;

I presume?

I think that will work, but the names of your variables could be improved. Please find the CODE button for future posts; it makes quoted code easier to read and preserves indentation.

[edit]Add: Also, ps.setInt(2, . . . etc[/edit]
[ June 27, 2008: Message edited by: Campbell Ritchie ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

"Amit ....on java ranch".
Please check your private messages about an important administrative matter.
Dan Murphy
Ranch Hand

Joined: Mar 29, 2005
Posts: 126
Originally posted by amit soni:

DO THIS :
--------
int iVariable1=10;
int iVariable1=20;
int iVariable1=30;

select * from person where age in (?,?,?);
ps.setInt(1,iVariable1); //where 'ps' is ref varible of PreparedStatement
ps.setInt(1,iVariable2);
ps.setInt(1,iVariable3);


That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.
[ June 27, 2008: Message edited by: Dan Murphy ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

Originally posted by Dan Murphy:
That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.

While you can't define a variable for each one, you can define a '?' parameter for each one. The elements are stored in some data structure (array or list I would presume.) Which means you can build a String with the correct number of '?' values and then loop through the data structure calling preparedStatement.setString() the appropriate number of times.
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
Maybe you can try
ResultSet rs1=stmt.executeQuery("select 10 union select 20 union select 30");
PreparedStatiment prestmt=con.prepareStatment("select * from person where age in ?");
prestmt.setParameter(1,rs1);
If your jdbc driver doesn't support that feature, you can try to create a temporary table in memory too.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

Originally posted by Wei Dai:
Maybe you can try
ResultSet rs1=stmt.executeQuery("select 10 union select 20 union select 30");
PreparedStatiment prestmt=con.prepareStatment("select * from person where age in ?");
prestmt.setParameter(1,rs1);
If your jdbc driver doesn't support that feature, you can try to create a temporary table in memory too.

This moves the problem to how to create the "select 10 union select ..." statement. If you don't use the prepared statement, you leave yourself vulnerable to SQL injection.
Amit Kumar S
Greenhorn

Joined: Jun 27, 2008
Posts: 3
Originally posted by Dan Murphy:


That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.

[ June 27, 2008: Message edited by: Dan Murphy ]


This code is working for variable number of argument.....
This is done by Dynamic query generation.....
However I am searching smaller function that will do the same ..
For the time being use it ,if it is useful for you...

//------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test
{
public static void main(String[] args) throws SQLException
{
int i=0,count=0;
String str=null;
int col[]={10,88,300,1000,200,300,341,20,400,500};
count=col.length;
Connection con=getConnection();
str="select staff_code from staff_master where salary in (";
while(i<count-1)
{
str=str+col[i]+",";
i++;
}
str=str+col[i]+")";
PreparedStatement ps=con.prepareStatement(str);
ResultSet rs=ps.executeQuery();
while(rs.next())
{
System.out.println("---- "+rs.getInt(1));
}
}

public static Connection getConnection()throws SQLException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection connectionoCon= DriverManager.getConnection("jdbcracle:thin:@192.168.111.40:1575:demt","train1","train1");
return connectionoCon;
}
}

//--------------------------
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

My 2 cents...

"IN" clauses are the one area JDBC is underdeveloped. There's no good solution other than to construct the SQL query by hand at runtime, possibly using a loop for unknown number of values (watch the commas tho if there's only one element!)


My Blog: Down Home Country Coding with Scott Selikoff
Brian Hart II
Greenhorn

Joined: Jul 04, 2008
Posts: 3
I would suggest you use features of the DBMS to accomplish your goal. The key is to stay committed to the use of bind variables, but you need to parse the value of that bind variable at the DBMS level for it to behave as you want it.

Tom Kyte has a discussion on this issue and all the options available to you at:
http://tkyte.blogspot.com/2006/06/varying-in-lists.html
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepared statement with "in" predicate