aspose file tools*
The moose likes JDBC and the fly likes Dynamic SQl - PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dynamic SQl - PreparedStatement" Watch "Dynamic SQl - PreparedStatement" New topic
Author

Dynamic SQl - PreparedStatement

Sat Nar
Ranch Hand

Joined: Oct 22, 2004
Posts: 83
Hi All,

I have the below requirement in my project. The project uses dynamic sql in its queries in the following way.

sqlQuery = sqlQuery + " AND GROUP IN ("+ userRole.toString().trim() + ") ";

userRole is StringBuffer whose value would be 'admin','inq'

Now the query hard codes the value, i want the value to be set at run time using PreparedStatement.

I tried modifiying the above query as below.

StringBuffer userRole = new StringBuffer(" ");
userRole.append(" ' ");
userRole.append("admin");
userRole.append(" ', ");
userRole.append(" ' ");
userRole.append("inq");
userRole.append(" ' ");
sqlQuery = sqlQuery + " AND GROUP IN ( ? );
stmt = conn.prepareStatement(sqlQuery);
stmt.setString(1,userRole.toString().trim());

But the problem here is while runtime, the below value is considered as a "single String" instead of two different strings due to which my query is failing.

'admin','inq'

Can someone help me out on this? I need the value to be two different strings when i am setting it dynamically.
Joseph Mokenela
Ranch Hand

Joined: Jan 18, 2011
Posts: 61

Why did you decide to hard code the values?
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
The easiest way is to store your IN values in an array instead of StringBuffer. You simply build your query with bind variables like :1 to :n (n = length of array). Then you would need to bind the values of the array to the SQL query in a loop.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dynamic SQl - PreparedStatement