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 现在好了，只写一次就可以了