日期:2014-05-16  浏览次数:20564 次

JDBC命名参数传参

?

package com.piend.sqlserver;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class NamedParamSqlUtil {
	//select * from table where colum1=#colum1# and colum2=#colum2# or colum3=#colum3#
	//insert into table valuse(#colum1#,#colum2#,#colum4#,#colum5#,#colum11#)
	//update table set colum1=#colum1# and colum2=#colum2# where colum3=#colum3#
	//delete from table where colum1=#colum1# and colum2=#colum2# where colum3=#colum3#
	//paraMap.put("colum1",value1);paraMap.put("colum2",value2);paraMap.put("colum3",value3);
	public static PreparedStatement namedParamPrepareStatement(String sql,
			Connection conn, Map<String, Object> paraMap) {
		Object ob;
		PreparedStatement ps = null;
		Map<Integer, String> index = new HashMap<Integer, String>();
		String regex = "#(\\w+)#";
		Integer id = 0;
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sql);
		while (m.find()) {
			index.put(new Integer(++id), m.group(1));
		}
		try {
			ps = conn.prepareStatement(sql.replaceAll(regex, "?"));
			for (Integer i : index.keySet()) {
				ob = paraMap.get(index.get(i));
				if (ob instanceof Date) {
					ps.setDate(i, (Date) ob);
				} else if (ob instanceof InputStream) {
					ps.setBinaryStream(i, (InputStream) ob);
				} else {
					ps.setObject(i, ob);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ps;
	}

}

?