This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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 Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dynamic SQl - PreparedStatement" Watch "Dynamic SQl - PreparedStatement" New topic

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(" ', ");
userRole.append(" ' ");
userRole.append(" ' ");
sqlQuery = sqlQuery + " AND GROUP IN ( ? );
stmt = conn.prepareStatement(sqlQuery);

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.


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.

Consider Paul's rocket mass heater.
subject: Dynamic SQl - PreparedStatement
Similar Threads
Dynamic SQL Query Doubt
out of memory error
Can we use plain SQL queries in Hibernate?
Unknown name value for enum class hibernate exception
benefit of Statement over PreparedStatement?