CODEWITHSUNDEEP
javasqlsql-server
ahmy
ahmy

Reputation: 4365

Java SQL query prepare statement dynamic parameter

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 :

  1. name
  2. start
  3. 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.

Upvotes: 2

Views: 7885

Answers (3)

Clark Bao
Clark Bao

Reputation: 1693

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!

Upvotes: 0

Adriaan Koster
Adriaan Koster

Reputation: 16209

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.

Upvotes: 0

Codemwnci
Codemwnci

Reputation: 54884

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.

Upvotes: 1

Related Questions