It's not a secret anymore!
The moose likes JDBC and Relational Databases 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 and Relational Databases
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: 64

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:
subject: Dynamic SQl - PreparedStatement
It's not a secret anymore!