连接jdbc时sql语句统一设置参数

各位网友java代码里面的注释怎么不变颜色，具体方法怎么操作，请各位网友指点？还有本文描述的怎么样？是否叙述的详细？请各位博主能指点一二，谢谢

一、业务背景介绍

本业务专门针对连接oracle数据库，经常在开发中写sql语句有的字段需要预定义，而这些字段类型有多种，如：

sql="select * from t_corp t where t.corpid=? and t.corpname=? " ,本文解决的问题就是如何把sql语句预定义的

字段统一的进行管理，客户不需考虑设置的字段是什么类型，只需要添加字段的值就可以，

如：DBParams params = new DBParams();
  params.addParam(1314);
  params.addParam("惠山分局钱桥派出所");
  params.addParam("2010-01-01 00:00:00");

二、代码详解

客户端的调用

package com.hewen.param.manage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/***
 * 注意从数据库取日期,比如：数据库字段中有stamp=2010-02-08 09:31:53
 * rs.getTime("stamp")只能得到09:31:53，rs.getDate("stamp")只能得到2010-02-08
 * rs.getTimestamp("stamp")，得到2010-02-08 09:31:53.0,
 * 只能通过函数rs.getTimestamp("stamp").toString().substring(0, rs.getTimestamp("stamp").toString().length()-2)
 * 得到2010-02-08 09:31:53
 * @author Administrator
 *
 */
public class DBParamTest {
	public static void main(String[] args) {	
		StringBuilder sql=new StringBuilder();
		 sql.append( "select * from t_corp t where t.corpid=? and t.corpname=? ")
		 .append("and t.stamp between to_date(?,'yyyy-mm-dd HH24:mi:ss') and to_date(?,'yyyy-mm-dd HH24:mi:ss')");
		
		Connection con = null;
		try {
			con = DBTest.getCon();//连接数据库
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		//为sql语句设置参数
		DBParams params = new DBParams();
		params.addParam(1314);
		params.addParam("惠山分局钱桥派出所");
		params.addParam("2010-01-01 00:00:00");
		params.addParam("2010-04-03 03:00:00");

		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con.prepareStatement(sql.toString());
			params.prepareStatement(pst);
			rs = pst.executeQuery();
			if(rs.next()){
				System.out.println("remark:" + rs.getString("corpname"));
				System.out.println("stamp:"+rs.getTimestamp("stamp").toString().substring(0, rs.getTimestamp("stamp").toString().length()-2));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeRs(rs);
			DBUtil.closePst(pst);
			DBUtil.closeCon(con);
		}
	}
}

 负责连接数据库的类

package com.hewen.param.manage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/***
 * 这个是连接Oracle数据库
 * @author Administrator
 *
 */
public class DBTest {
	public static Connection getCon() throws SQLException{
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		}
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
		String user = "avls";
		String password = "1";
		
		return DriverManager.getConnection(url, user, password);	
	}
}

对设置的参数统一管理类

package com.hewen.param.manage;

import java.lang.reflect.Type;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class DBParams 
{
	/**
	 * 数据库的基本类型，包含的jdbc的封装的基本类型
	 */
	private static final int[] SQLTYPES = {Types.INTEGER, Types.BIGINT, Types.VARCHAR, 
			Types.DATE, Types.TIMESTAMP, Types.DOUBLE, Types.TIME};
	/**
	 * Java数据基本类型，SQLTYPES与CLASSTYPES类型是一一对应的
	 */
	private static final Class<?>[] CLASSTYPES = {Integer.class, Long.class, String.class,
			Date.class, Timestamp.class, Double.class, Time.class};
	//访问数据库，更新、删除、修改、查询、添加的sql语句预先定义的语句参数
	private List<ParamEntity> paramList = new ArrayList<ParamEntity>();
	/**
	 * 复制一份sql语句预定义参数的集合
	 * @return 
	 * 
	 */
	public DBParams copy(){
		DBParams copy = new DBParams();
		for(ParamEntity entity: paramList){
			copy.paramList.add(entity);
		}
		return copy;
	}
	/***
	 * 为sql语句设置参数如：
	 * select * from t_logistics_road t where t.roadid=?
	 * @param o　添加的参数对象，此对象可以是任意的数据基本类型和类
	 */
	public void addParam(Object o){
		addParam(o, getSqlTypeByClassType(o.getClass()));
	}
	/**
	 * 
	 * @param type Java 编程语言中所有类型的公共高级接口。
	 * 它们包括原始类型、参数化类型、数组类型、类型变量和基本类型
	 * @return
	 */
	private int getSqlTypeByClassType(Type type){
		for(int i = 0; i < CLASSTYPES.length; i++){
			if(type == CLASSTYPES[i]){
				return SQLTYPES[i];
			}
		}
		throw new RuntimeException("unSupport Type type:" + type);
	}
	
	private int checkSupportType(int sqlType){
		for(int i = 0; i < SQLTYPES.length; i++){
			if(sqlType == SQLTYPES[i]){
				return i;
			}
		}
		throw new RuntimeException("unsurpport sqltype:" + sqlType);
	}
	
	public void addNullParam(int sqlType){
		checkSupportType(sqlType);
		addParam(null, sqlType);
	}
	
	public void addNullParam(Type t){
		addParam(null, getSqlTypeByClassType(t));
	}
	/***
	 * 
	 * @param o 添加参数设置对象
	 * @param type jdbc数据库数据类型，如Types.INTEGER, Types.BIGINT, Types.VARCHAR等
	 */
	private void addParam(Object o, int type){
		ParamEntity entity = new ParamEntity(o, type);
		paramList.add(entity);
	}
	
//	public void removeParam(int index){
//		paramList.remove(index);
//	}
//	
//	public void clearParams(){
//		paramList.clear();
//	}
	/***
	 * @param pst
	 * @param startIndex 由于sql语句设置的参数有多个，注意默认是从1开始
	 */
	public void prepareStatement(PreparedStatement pst,int startIndex) throws SQLException{
		for(ParamEntity e: paramList){
			int v = e.getValueType();
			Object o = e.getValue();
			if(o == null){//如果为空就设置为null
				pst.setNull(startIndex++, v);
				continue;
			}
			//判断参数实体ParamEntity中Object对象是哪一种jdbc数据类型			
			if(v == SQLTYPES[0]){				
				pst.setInt(startIndex++, (Integer) o);
			}else if(v == SQLTYPES[1]){
				pst.setLong(startIndex++, (Long) o);
			}else if(v == SQLTYPES[2]){
				pst.setString(startIndex++, (String) o);
			}else if(v == SQLTYPES[3]){
				pst.setDate(startIndex++, (Date) o);
			}else if(v == SQLTYPES[4]){
				pst.setTimestamp(startIndex++, (Timestamp) o);
			}else if(v == SQLTYPES[5]){
				pst.setDouble(startIndex++, (Double) o);
			}else if(v == SQLTYPES[6]){
				pst.setTime(startIndex++, (Time) o);
			}
		}
	}
	/***
	 * 为执行数据库的sql语句设置参数值
	 * @param pst
	 * @throws SQLException
	 */
	public void prepareStatement(PreparedStatement pst) throws SQLException{
		prepareStatement(pst, 1);
	}
}

jdbc数据类型的种类和该数据类型的对应的值， 相当于map中的 key和value,把这样的参数类型封装成一个实体对象

package com.hewen.param.manage;
/***
 * jdbc数据类型的种类和该数据类型的对应的值，
 * 相当于map中的 key和value,把这样的参数类型封装成一个实体对象
 * @author Administrator
 *
 */
public class ParamEntity {
	private Object value;//该jdbc数据类型的哪个类型
	private int sqlType;//jdbc数据类型的哪一种
	/***
	 * 
	 * @param value
	 * @param sqlType
	 */
	public ParamEntity(Object value, int sqlType){
		this.value = value;
		this.sqlType = sqlType;
	}
	
	public Object getValue() {
		return value;
	}
	public void setValue(Object value) {
		this.value = value;
	}
	public int getValueType() {
		return sqlType;
	}
	public void setValueType(int sqlType) {
		this.sqlType = sqlType;
	
	}
}

 一个公共的类，对数据库进行查询，添加、修改、删除操作，这里只用到了对数据库的关闭，其他功能在下面的文章进行讲解

package com.hewen.param.manage;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {
	public static void rollBack(Connection con){
		try {
			con.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static List<Map<String, Object>> executeQuery(Connection con, String sql) throws SQLException{
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			return getListFromRs(rs);
		}finally{
			closeRs(rs);
			closePst(pst);
		}
	}
	
	public static List<Object> executeQuery(Connection con, String sql, Class<?> c) throws SQLException{
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			return getListFromRs(rs, c);
		}finally{
			closeRs(rs);
			closePst(pst);
		}
	}
	
	public static List<Map<String, Object>> getListFromRs(ResultSet rs) throws SQLException{
		ResultSetMetaData md = rs.getMetaData();
		int columns = md.getColumnCount();
		int i;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		while(rs.next()){
			Map<String, Object> map = new HashMap<String, Object>();
			for(i = 0; i < columns; i++){
				map.put(md.getColumnName(i + 1), getValueByType(rs, md.getColumnType(i + 1), md.getColumnName(i + 1)));
			}
			list.add(map);
		}
		return list;
	}
	
	public static List<Map<String, Object>> getListFromRsLowerCase(ResultSet rs) throws SQLException{
		ResultSetMetaData md = rs.getMetaData();
		int columns = md.getColumnCount();
		int i;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		while(rs.next()){
			Map<String, Object> map = new HashMap<String, Object>();
			for(i = 0; i < columns; i++){
				map.put(md.getColumnName(i + 1).toLowerCase(), getValueByType(rs, md.getColumnType(i + 1), md.getColumnName(i + 1)));
			}
			list.add(map);
		}
		return list;
	}
	
	public static List<Map<String, Object>> getListFromRsUpperCase(ResultSet rs) throws SQLException{
		ResultSetMetaData md = rs.getMetaData();
		int columns = md.getColumnCount();
		int i;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		while(rs.next()){
			Map<String, Object> map = new HashMap<String, Object>();
			for(i = 0; i < columns; i++){
				map.put(md.getColumnName(i + 1).toUpperCase(), getValueByType(rs, md.getColumnType(i + 1), md.getColumnName(i + 1)));
			}
			list.add(map);
		}
		return list;
	}
	
	public static List<Object> getListFromRs(ResultSet rs, Class<?> c) throws SQLException{
		List<Object> list = new ArrayList<Object>();
		try {
			while(rs.next()){
				Object o = initObjectFromRs(rs, c);
				list.add(o);
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static Object getFirstObjectFromRs(ResultSet rs, Class<?> c) throws SQLException{
		Object o = null;
		try {
			o = initObjectFromRsIfExist(rs, c);
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return o;
	}
	
	//根据类型获取值
	private static Object getValueByType(ResultSet rs, int type, String name) throws SQLException{
		switch(type){
			case Types.NUMERIC:
				return rs.getLong(name);
			case Types.VARCHAR:
				return rs.getString(name);
			case Types.DATE:
				return rs.getDate(name);
			case Types.TIMESTAMP:
				return rs.getTimestamp(name);
			case Types.INTEGER:
				return rs.getInt(name);
			case Types.DOUBLE:
				return rs.getDouble(name);
			case Types.FLOAT:
				return rs.getFloat(name);
			case Types.BIGINT:
				return rs.getLong(name);
			default:
				return rs.getObject(name);
		}
	}
	
	private static boolean rsContainsFields(ResultSet rs, String fieldName) throws SQLException{
		ResultSetMetaData md = rs.getMetaData();
		for(int i = 0; i < md.getColumnCount(); i++){
			if(md.getColumnName(i + 1).equalsIgnoreCase(fieldName)){
				return true;
			}
		}
		return false;
	}
	
	private static Object initObjectFromRs(ResultSet rs, Class<?> c) throws InstantiationException, SQLException, IllegalAccessException{
		Object o = c.newInstance();
		Method[] methods = o.getClass().getMethods();
		for(Method m: methods){
			if(m.getName().startsWith("set")){
				try {					
					m.invoke(o, getParamValueFromRs(rs, m));					
				} catch (IllegalArgumentException e) {
					throw new RuntimeException("IllegalArgumentException:" + e + "\nMethods:" + m.getName());
				} catch (InvocationTargetException e) {
					throw new RuntimeException("InvocationTargetException:" + e + "\nMethods:" + m.getName());
				}
			}
		}
		return o;
	}
	
	private static Object initObjectFromRsIfExist(ResultSet rs, Class<?> c) throws SQLException, IllegalAccessException, InstantiationException{
		Object o = c.newInstance();
		Method[] methods = o.getClass().getMethods();
		String field;
		for(Method m: methods){
			field = m.getName().substring(3);
			if(m.getName().startsWith("set") && rsContainsFields(rs, field)){
				try {					
					m.invoke(o, getParamValueFromRs(rs, m));					
				} catch (IllegalArgumentException e) {
					throw new RuntimeException("IllegalArgumentException:" + e + "\nMethods:" + m.getName());
				} catch (InvocationTargetException e) {
					throw new RuntimeException("InvocationTargetException:" + e + "\nMethods:" + m.getName());
				}
			}
		}
		return o;
	}
	
	private static Object getParamValueFromRs(ResultSet rs, Method m) throws SQLException
	{
		String fieldName = m.getName().substring(3);
		Type type = m.getGenericParameterTypes()[0];		
		return getValueFromRs(rs, fieldName, type);
	}
	
	private static Object getValueFromRs(ResultSet rs, String fieldName, Type t) throws SQLException{
		String type = t.toString();
		try{
			if(type.equals("int") || type.equals("class java.lang.Integer")){
				return rs.getInt(fieldName);
			}else if(type.equals("float") || type.equals("class java.lang.Float")){
				return rs.getFloat(fieldName);
			}else if(type.equals("double") || type.equals("class java.lang.Double")){
				return rs.getDouble(fieldName);
			}else if(type.equals("long") || type.equals("class java.lang.Long")){
				return rs.getLong(fieldName);
			}else if(type.equals("class java.lang.String")){
				return rs.getString(fieldName);
			}else if(type.equals("class java.sql.Timestamp")){
				return rs.getTimestamp(fieldName);
			}else if(type.equals("class java.sql.Date")){
				return rs.getDate(fieldName);
			}else if(type.equals("class java.sql.Time")){
				return rs.getTime(fieldName);
			}
		}catch(SQLException e){
			throw new SQLException("SQLException when get field:" + fieldName + "\n" + e);
		}
		throw new RuntimeException("getValueFromRsByField fail, field type is:" + type + ",field name is:" + fieldName);
	}
	
	public static void closeRs(ResultSet... rss){
		for(ResultSet rs: rss){
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
	}
	
	public static void closePst(Statement... psts){
		for(Statement pst: psts){
			if(pst != null){
				try {
					pst.close();
				} catch (SQLException e) {
				}
			}
		}
	}
	
	public static void closeCon(Connection... cons){
		for(Connection con: cons){
			if(con != null)
			{
				try {
					con.close();
				} catch (SQLException e) {
				}
			}
		}
	}
}

    运行的结果

remark:惠山分局钱桥派出所
stamp:2010-02-08 09:31:53