• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

prepared statement with "in" predicate

 
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.....
 
Marshal
Posts: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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("jdbc:oracle:thin:@192.168.111.40:1575:demt","train1","train1");
return connectionoCon;
}
}

//--------------------------
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!)
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic