Hye
I have a question:
Suppose I use JDBC, JDBCTemplate in order to execute a sql query.
The query is something like:
query = "SELECT ... FROM ... WHERE user = ? AND password = ? AND x='valuex' AND y='valuey' ..."

Where user,password - I got from the web user - so I want it to be in a PreparedStatement.
But x,y,... (Suppose there are many variables like this) are values which I set their values myself at the code (e.g. constants), so there is no use for PreparedStatement on them.

Is there a way I can combine the two ways, something like:
PreparedStatement preparedStatement = ...
preparedStatement.setString(1,userValue)
preparedStatement.setString(2,passwordValue)
execute(query,preparedStatement,new Object[]('valuex','valuey'...)) ?

If there is, please write me an example of this part of the code, how can I do this exactly.

Thanks

If you need to pass them for every call to prepared statement, then they are not constant and should be part of prepare statement. If they really are constants, you can embed those values in the query itself. Or you can combine both the approaches:

var pStmt = "select * from tbl where u=? and p=? and x=? and y=?";
pStmt.setString(1, user); pStmt.setString(2, pwd);
pStmt.setString(3, CONST1); pStmt.setString(4, CONST2);

OK, i was not clear enough.
x,y,... are variables, but not from the user.
It's true that I can do:
query = "SELECT ... FROM ... WHERE user = ? AND password = ? AND x='"+valuex+'" AND y='"+valuey+"' ..."
It will give me what I need, but it's not so readable.

Therefore, I want something like:
query = "SELECT ... FROM ... WHERE user = ? AND password = ? AND x=? AND y=? ..."
PreparedStatement preparedStatement = ...
preparedStatement.setString(1,userValue)
preparedStatement.setString(2,passwordValue)
execute(query,preparedStatement,new Object[]('valuex','valuey'...)) ?

It does not matter whether x and y are from the user or not. You can set it in the prepared statement as you do it for username and password. As long as 'x' and 'y' are variables which are available in the scope of the method, you can use them as you would use your username and password.

So it's pretty much like new Object[]{...} ? There is no significant performance different if I put many variables in the PreparedStatement ?

Not that I know of; on the contrary, PreparedStatements are capable of being compiled down to an efficient representation since the structure of the query remains the same with the variables being parametrized. This is much better than creating a SQL query for each request by concatenating the values of x and y to the original SELECT query which can't be compiled down to an optimized form.

OK, thanks for the quick and detailed answer.

If anyone else disagrees, please write.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.