如何使用setObject为PreparedStatement对象传入动态参数?

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zd10101501/article/details/42835143

   来自stackoverflow的答案,注意的是一般为sqlserver中datetime类型传入值的时候,对应的是java.sql.Timestamp类型,别把值传错了,当然以前用hibernate的时候,框架已经把这些类型差异都封装屏蔽好了,现在自己用jdbc写,好多坑坑。。。

http://stackoverflow.com/questions/10125270/prepare-statement-create-method-using-an-arraylist-of-parameters/10126048#10126048

You should use an object representation for your parameters, rather than an ArrayList<Object> of size 2. Also, you don't need your own DataType enumeration, there is already a java.sql.Typesclass. Best of all, there's a setObject() method on PreparedStatement that recognizes these types values, so you don't need a switch statement or calls to type-specific PreparedStatement.set..()methods.

Here's a solution that has an object representation for the parameters, takes advantage of java.sql.Types and preparedStatement.setObject() support for those types, and also insulates you from the type constant values.

First, the object representation for the parameters:

/**   
* @Title: ParamDescriptor.java 
* @Package com.zd.util 
* @Description: TODO 
* @author Dan  Zhang   
* @date 2015-1-18 下午01:20:14 
* @version V1.0   
*/
package com.zd.util;


/** 
 * @ClassName: ParamDescriptor 
 * @Description: TODO
 * @author Dan Zhang 
 * @date 2015-1-18 下午01:20:14 
 *  
 */
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;


public class ParamDescriptor {
    // Constructor itself is private, we are encapsulating so that
    // you don't need to write java.sql.Types constants yourself
    private ParamDescriptor(int dataType, Object value) {
        _dataType = dataType;
        _value = value;
    }


    // Factory methods for actual instantiation
    public static ParamDescriptor forInt (int paramVal) {
        return new ParamDescriptor (Types.INTEGER, paramVal);
    }


    public static ParamDescriptor forString (String paramVal) {
        return new ParamDescriptor (Types.VARCHAR, paramVal);
    }


    public static ParamDescriptor forDate (Date paramVal) {
        return new ParamDescriptor (Types.DATE, paramVal);
    }
    public static ParamDescriptor forDateTime (Timestamp paramVal) {
        return new ParamDescriptor (Types.TIMESTAMP, paramVal);
    }
    // Add more here to support more data types . . . .    




    public int getDataType() {
        return _dataType;
    }


    public Object getValue() {
        return _value;
    }


    private int     _dataType;
    private Object  _value;
}

Next, the new version of executeInsertStmt(). We've reduced it to just a few lines, and it never needs to change again, regardless of future support for parameter types:

  public void executeInsertStmt(String strQuery, List<ParamDescriptor> parameters) throws SQLException {    
        PreparedStatement preparedStatement = cnx.prepareStatement(strQuery);

        int counter = 1;
        for (ParamDescriptor paramDescriptor : parameters) {
            preparedStatement.setObject(counter, 
                                        paramDescriptor.getValue(),
                                        paramDescriptor.getDataType());
            counter++;
        }
    }

Finally, code that would call the new executeInsertStmt():

String strQuery = "insert into toto values (?,?)";

ParamDescriptor paramToPass1 = ParamDescriptor.forString("TheValueForTheString");
ParamDescriptor paramToPass2 = ParamDescriptor.forString("TheValueForTheString2");

List<ParamDescriptor> parameters = new ArrayList<ParamDescriptor>();
parameters.add(paramToPass1);
parameters.add(paramToPass2);

executeInsertStmt(strQuery, parameters);

Note that if you ever plan on handling decimals, there is another version of PreparedStatement.setObject() that supports specifying decimal scale. You may need to add some sort of support for that into your executeInsertStmt().


展开阅读全文

没有更多推荐了,返回首页