jdbc封装

以前项目写的jdbc代码很烂，今天重新封装一下代码如下：
这个是主要用的类

 package com.own.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.own.util.ReflectHelper;

public class MyJdbcTemplate {

	/**
	 * 
	 * @param sql  传入的sql语句
	 * @param params 要设置的参数
	 * @return
	 */
	public int update(String sql,Object[] params){

		Connection con = null;
		PreparedStatement pst = null;
		int result = 0;

		try{
			con = DBHelper.getConnection();
			pst = con.prepareStatement(sql);
		    setValues(pst, params);
			result = pst.executeUpdate();

		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			DBHelper.close(con, null,pst);
		}

		return result;


	}


	/** 
	 * 
	 * @param sql  传入sql语句
	 * @param pstSetter给参数赋值
	 * @return
	 */
	public int update(String sql,PreparedStatementSetter pstSetter)throws DataAccessException {

		Connection con = null;
		PreparedStatement pst = null;
		int result = 0;

		try{
			con = DBHelper.getConnection();
			pst = con.prepareStatement(sql);
		    pstSetter.setValues(pst);
			result = pst.executeUpdate();

		}catch(SQLException e){
			throw new DataAccessException(e.getMessage());
		}finally{
			DBHelper.close(con, null,pst);
		}

		return result;

	}

	public <T>  List<T> queryForList(String sql,Object[] params,RowMapper<T> rowMapper) throws DataAccessException{

		List<T> list = new ArrayList<T>();

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;


		try{
			con = DBHelper.getConnection();
			pst = con.prepareStatement(sql);
		    setValues(pst,params);	
		    rs = pst.executeQuery();
		    if(rs != null){
		    	while(rs.next()){
		    	 T t =	rowMapper.rowMap(rs);
		    	 list.add(t);
		    	}
		    }
		 }catch(SQLException e){
			throw new DataAccessException(e.getMessage());
		}finally{
			DBHelper.close(con, null,pst);
		}



		return list;

	}


	private void setValues(PreparedStatement pst, Object[] params) throws SQLException {
		// TODO Auto-generated method stub

		if(params == null){
			throw new IllegalArgumentException("params 为空");
		}

		for(int i = 0;i < params.length;i++){
			pst.setObject(i + 1, params[i]);
		}
	}

	/** note that 调用这个方法时,数据库字段的名字要和对象的属性名字相同,否则会报错
	 * 
	 * @param <T>
	 * @param sql
	 * @param params
	 * @param myClass 
	 * @return
	 * @throws DataAccessException
	 */

	public <T>  List<T> queryForList(String sql,Object[] params,Class<T> myClass) throws DataAccessException{


		List<T> list = new ArrayList<T>();

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;


		try{
			con = DBHelper.getConnection();
			pst = con.prepareStatement(sql);
		    setValues(pst,params);	
		    rs = pst.executeQuery();
		    if(rs != null){
		    	while(rs.next()){
		    		T t = ReflectHelper.setproperties(myClass, rs);
		    		list.add(t);
		    	}
		    }
		 }catch(SQLException e){
			throw new DataAccessException(e.getMessage());
		}finally{
			DBHelper.close(con, null,pst);
		}



		return list;



	}






}

数据库帮助类

  package com.own.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.own.config.DBConfig;
import com.own.util.LoadDBConfig;

public class DBHelper {

	public static DBConfig dbConfig = LoadDBConfig.getDBConfig();


	public static Connection  getConnection() throws SQLException{


		 Connection con = null;

		 try {
			Class.forName(dbConfig.getDriverClassName());
			con = DriverManager.getConnection(dbConfig.getUrl(),dbConfig.getUsername(),dbConfig.getPassword()
					);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block

		}

		return con;
	}


	public static void close(Connection con,ResultSet rs ,Statement smt){

		if(con != null){
	         try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}


		if(smt != null){
	         try {
				smt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}


		if(rs != null){
	         try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}

	}

}

用来给PreparedStatement 设置参数的借口，有具体的类去实现

  package com.own.db;

import java.sql.PreparedStatement;

public interface PreparedStatementSetter {

	void setValues(PreparedStatement pst); 

}

把数据中的一行数据映射到一个对象，同样是一个接口，有具体的dao类实现

    package com.own.db;

import java.sql.ResultSet;

public interface RowMapper<T> {

	T rowMap(ResultSet rs);
}

自定义一个异常类

  package com.own.db;

@SuppressWarnings("serial")
public class DataAccessException extends Exception {

	public DataAccessException(String message){
		super(message);
	}

}

用来读数据库配置文件的类

package com.own.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import com.own.config.DBConfig;

public class LoadDBConfig {

	private static Properties p = new Properties();

	static{
		InputStream is = LoadDBConfig.class.getClassLoader().getResourceAsStream("dbConfig.properties");
		try {
			p.load(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	public static DBConfig  getDBConfig(){

		DBConfig dbConfig = new DBConfig();
		dbConfig.setPassword(p.getProperty("password"));
		dbConfig.setUsername(p.getProperty("username"));
		dbConfig.setUrl(p.getProperty("url"));
		dbConfig.setDriverClassName(p.getProperty("driverClassName"));
		return dbConfig;
	}

}

反射操作辅助类

  package com.own.util;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ReflectHelper {

	public  static <T>  T setproperties(Class<T> myClass,ResultSet rs){
		T t = null; 
		try {
			t = myClass.newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Field[] fields = myClass.getDeclaredFields(); 

		for(Field field : fields){
			String methodName = field.getName();
			field.setAccessible(true);
			try {
				field.set(t,rs.getObject(methodName));

			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


		return t;
	}




}

以前每个dao都要用try catch 现在好了，只写一次就可以了