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


Win a copy of Java 8 in Action this week in the Java 8 forum!
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: 59

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
 
Similar Threads
Dynamic SQL Query Doubt
out of memory error
Can we use plain SQL queries in Hibernate?
benefit of Statement over PreparedStatement?
Unknown name value for enum class hibernate exception