Java SQL query prepare statement dynamic parameter

Question

Im daling with sql query using java with parameters that maybe exists or not in a query.

For example, there is a http request parameter of :

name
start
limit

in the jsp, i did something like this.

if (request.getParameter("query") != null) {        
  query = request.getParameter("query"); 
}

if (request.getParameter("start") != null) {
  start = Integer.parseInt(request.getParameter("start"));  
}

if (request.getParameter("limit") != null) {
  limit = Integer.parseInt(request.getParameter("limit"));  
}

....

if (query != null) {
  sql += " AND dbo.TABLENAME.namelike '%?%'";
}

if (start != null) {
  sql += " AND RowNum >= ?";
}

if (limit != null) {
  sql += " AND RowNum <= ?";
  if (start == null) 
    start =0;
}

is there any easy way to do this with PreparedStatement ? or is there any cleaner way to do this. If im not mistaken, one must specify the SQL string first in prepared statement, and not latter.

That's just it, this project must not use ORM, only jsp. It is not my call and I know that's crazy. — ahmy, Aug 22 '11 at 08:50
Codemwnci's answer is not enough,you need to consider a lot. Especially take care of the special character. — Clark Bao, Aug 22 '11 at 08:54
Yes, i understand, I really wanted to use ORM, but our client won't accept it. — ahmy, Aug 22 '11 at 08:57
You can't use `like '%?%'`. You must use `like ?` and put the `%` characters aroung the bound string parameter. — JB Nizet, Aug 22 '11 at 09:03
Be careful, if in your example rownum is the oracle keyword, your paging will work only with start = 0. See http://stackoverflow.com/questions/241622/paging-with-oracle — Thierry, Aug 23 '11 at 09:25

score 1 · Answer 1 · answered Aug 22 '11 at 08:31

1

In your particular scenario, you should be okay to do the following

sql += "AND dbo.TABLENAME.namelike '%?%' AND RowNum >= ? AND RowNum <= ?";

You can then just set the RowNum to 0 if no start, and RowNum to Integer.MAX_INTEGER if no end. Also, if no query is passed in, then it will return everything anyway.

This is pretty inefficient though, so you could us the CASE statement to try to make it more efficient.

answered Aug 22 '11 at 08:31

Codemwnci

54,176
10
96
129

what if there is another parameter say gender so the query will look like `sql += "AND dbo.TABLENAME.name like '%?%' AND gender ='F' AND RowNum >= ? AND RowNum <= ?";` – ahmy Aug 22 '11 at 08:46
The only reason the statement above works, is that you are working with elements that you can mimic not existing (by using max and min values or empty values). If you want to default a gender if one does not exist, then that is fine, because you can default, but if you want to ignore gender, if one is not passed in, you would have to use the LIKE statement, and this will make it more and more inefficient – Codemwnci Aug 22 '11 at 09:10

score 0 · Answer 2 · edited Apr 29 '13 at 06:59

0

Try to use SQL tag. JSTL offers this sql tag as you can only use JSP. It's a part of Java EE standard.

It's much cleaner and easier than writing java code in your jsp.

Here is the link. http://download.oracle.com/javaee/1.4/tutorial/doc/JSTL7.html.

Hope it helps!

edited Apr 29 '13 at 06:59

Arjan Tijms

37,782
12
108
140

answered Aug 22 '11 at 08:59

Clark Bao

1,743
3
21
39

Adriaan Koster · Answer 3 · 2011-08-23T09:01:38.830

To make it cleaner without help of a framework you could externalize separate queries, in your example with- and without paging parameters, and put these in a properties file, for example queries.properties:

query.paging=SELECT * FROM dbo.TABLENAME WHERE namelike ? AND RowNum >= ? AND RowNum <= ?
query.nopaging=SELECT * FROM dbo.TABLENAME WHERE namelike ?

Then in your code distinguish the use cases and access the appropriate query:

ResourceBundle queries = ResourceBundle.getBundle("queries");

String nameLike = request.getParameter("nameLike");
String startRow= request.getParameter("startRow");
String endRow= request.getParameter("endRow");

if (nameLike == null) {        
    nameLike = ""; // or you could report an error here
}

Connection connection = ...get a JDBC Conection...

PreparedStatement st;
if (startRow != null && endRow != null) {
    st = connection.prepareStatement(queries.get("query.paging"));
    st.setString(1, nameLike);
    st.setString(2, startRow);
    st.setString(3, endRow);
}
else {
    st = connection.prepareStatement(queries.get("query.nopaging"));
    st.setString(1, nameLike);
}

Using PreparedStatement avoids the security risk of SQL injection attacks.

Java SQL query prepare statement dynamic parameter

3 Answers3