Java/ JDBC – Multi Parameter Search Best Practice

I’m using the following code to generate a search results from a relational DB, depending on the multiple (Optional) search parameters from the web based client.

Presently I’m using “java.sql.Statement” to achieve the functionality but I need the same to be achieved using “java.sql.Prepared开发者_开发问答Statement” in order to prevent SQL injections.

Let me know a best practice to change the code

E.g.

User inputs from web based client.

• param1 - Optional
• param2 - Optional
• dateParamFr - Optional
• dateParamTo - Optional

Pseudo code of SQL patterns depending on the search parameters as follows

IF (WITHOUT ANY SEARCH PARAMETER){
    SELECT * FROM TEST_TABLE;
}
ELSE IF(WITH param1){
    SELECT * FROM TEST_TABLE WHERE COLUMN1= param1;
}
ELSE IF(WITH param1 & param2){
    SELECT * FROM TEST_TABLE WHERE COLUMN1= param1 AND  COLUMN2= param2
}
SO ON
………

Following is the fragment of Java code in my EJB

    /* 
        NOTE : Hashtable pSearchParam is a method parameter
    */

    Connection  cnBOP           = null;
    Statement stmt              = null;
    StringBuffer sb             = new StringBuffer("");         

    try {
        cnBOP   = jdbcBOP.getConnection(); // DataSource jdbcBOP
        stmt    = cnBOP.createStatement();

        /* ######################## SQL BODY ######################################*/
        sb.append("SELECT COLUMN1, COLUMN2, DATE_COLUMN ");
        sb.append("FROM TEST_TABLE ");

        /* ######################## SQL WHERE CLAUSE ##############################*/
        if(pSearchParam.size()>=1){
            sb.append("WHERE ");
            Enumeration e = pSearchParam.keys();
            int count =0;

            while(e.hasMoreElements()){
                if (count >=1) sb.append("AND ");

                String sKey = (String) e.nextElement();

                if (sKey.equals("param1"))              sb.append ("COLUMN1 ='"+pSearchParam.get(sKey)+"' ");
                else if (sKey.equals("param1"))         sb.append ("COLUMN2 ='"+pSearchParam.get(sKey)+"' ");                   
                else if (sKey.equals("dateParamFr"))    sb.append ("DATE_COLUMN >= TO_DATE('"+pSearchParam.get(sKey)+" 00:00:00','DD/MM/YYYY HH24:MI:SS') ");
                else if (sKey.equals("dateParamTo"))    sb.append ("DATE_COLUMN <= TO_DATE('"+pSearchParam.get(sKey)+" 23:59:59','DD/MM/YYYY HH24:MI:SS') ");

                count ++;
            }
        }
        /* ######################## SQL ORDER BY CLAUSE ############################*/
        sb.append("ORDER BY DATE_COLUMN DESC");

        ResultSet rs = stmt.executeQuery(sb.toString());

Instead of

sb.append ("COLUMN1 ='"+pSearchParam.get("param1")+"' ");

You will have to do

sb.append ("COLUMN1 = ? ");

and then after you create the statement you do

stmt.setString(1, pSearchParam.get("param1"));

This is only for the first parameter, you need to do this for all statements and enumerate the index in

setString(int index, String param);

Note that you will need to use other methods for int, long, Date... etc

Depend on your database engine you may use SQL functions like

isnull(value,valueIfNull) 

for example in MSSQL

select * from Order where storeId = isnull(?,storeId)

next in you java code

preparedStatement.setNull(1,java.sql.Types.INTEGER)

if you need omit this param from filter or,

preparedStatement.setInt(1,20)

if you need find all orders with storeId = 20

This really looks like a job for Hibernate Criteria Queries...

Criteria is a simplified API for retrieving entities by composing Criterion objects. This is a very convenient approach for functionality like "search" screens where there is a variable number of conditions to be placed upon the result set.

Are you using Hibernate? Then you can use the criteria API. Else for non hibernate you can take a look at the SqlBuilder tool to generate SQLs based on conditions.

Also you should use markers "?" instead of actual values.

So this query should be like this.

SELECT * FROM TEST_TABLE WHERE COLUMN1= ?;

You can then use PreparedStatements to set values for this column. An introductory tutorial on using PreparedStatement is here.