• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statement with "in" predicate

 
Dan Murphy
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Amit Kumar S
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 49819
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Amit ....on java ranch".
Please check your private messages about an important administrative matter.
 
Dan Murphy
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!)
 
Brian Hart II
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic