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.PreparedStatement” 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());

回答1:

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

回答2:

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

回答3:

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.

回答4:

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.