JdbcUtil和JdbcTemplate的封装

#jdbc笔记

• 复习抽取JdbcUtil工具
• 抽取JdbcTemplate模板 ##基本DAO的curd 下面以Employee的增删改查为例: #####定义DAO的接口

public interface IEmployeeDao {
		void save(Employee emp);
		void delete(Employee emp);
		void update(Employee emp);
		Employee find(Employee emp);
		List<Employee> findAll();
	}
复制代码

#####DAO的实现 导包:mysql-connector-java-5.1.26-bin.jar

public class EmployeeDaoImpl implements IEmployeeDao{
	//员工的添加操作
	public void save(Employee emp){
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
			ps = conn.prepareStatement("insert into employee values(null,?,?,?)");
			ps.setString(1, emp.getName());
			ps.setInt(2, emp.getAge());
			ps.setBigDecimal(3, emp.getSalary());
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					if(conn!=null){
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
		
	}
	//员工的删除操作
	public void delete(Employee emp){
		Connection conn=null;
		PreparedStatement ps=null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
			ps = conn.prepareStatement("delete from employee where id=?");
			ps.setLong(1, emp.getId());
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					if(conn!=null){
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
		
	}
	//员工的修该操作
	public void update(Employee emp){
		Connection conn=null;
		PreparedStatement ps=null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
			ps = conn.prepareStatement("update employee set name=? ,age=? ,salary=? where id=? ");
			ps.setString(1, emp.getName());
			ps.setInt(2, emp.getAge());
			ps.setBigDecimal(3, emp.getSalary());
			ps.setLong(4, emp.getId());
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(ps!=null){
					ps.close();
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
	}
	//查询某一个员工
	public Employee find(Employee emp){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
			ps = conn.prepareStatement("select * from employee where id=? ");
			ps.setLong(1,emp.getId());
			
			rs = ps.executeQuery();
			if(rs.next()){
				
				emp.setName(rs.getString("name"));
				emp.setAge(rs.getInt("age"));
				emp.setSalary(rs.getBigDecimal("salary"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(ps!=null){
					ps.close();
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		
		
		return emp;
		
	}
	//查询所有员工
	@Override
	public List<Employee> findAll() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Employee> list=new ArrayList<>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","123456");
			ps = conn.prepareStatement("select * from employee");
			rs = ps.executeQuery();
			if(rs.next()){
				Employee emp=new Employee();
				emp.setName(rs.getString("name"));
				emp.setAge(rs.getInt("age"));
				emp.setSalary(rs.getBigDecimal("salary"));
				list.add(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(ps!=null){
					ps.close();
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		
		return list;
	}
}
复制代码

这样的操作代码重复量很高,下面先进行一些基本的抽取

• 首先将driverClassName,username,password,url抽取出来,放入到db.properties文件中.

• db.properties

  username=root
  password=123456
  driverClassName=com.mysql.jdbc.Driver
  url=jdbc:mysql://localhost:3306/jdbcdemo

• 然后然后新建一个JdbcUtil的类,在静态代码块中加载db.properties文件,

• 同时创建一个静态方法Connection getConnection(),用来返回Connection对象.

• 然后在JdbcUtil类中提供一个关闭conn,ps,rs的方法.

  public class JdbcUtil {

    private static Properties prop = new Properties();
    static {
    	InputStream in = Thread.currentThread().getContextClassLoader()
    			.getResourceAsStream("product.properties");
    	try {
    		prop.load(in);
    	} catch (IOException e1) {
    		e1.printStackTrace();
    	}
  
    	try {
  
    		Class.forName(prop.getProperty("driverClassName"));
    	} catch (ClassNotFoundException e) {
    		e.printStackTrace();
    	}
    }
  
    public static Connection getConnection() {
    	try {
    		return DriverManager.getConnection(prop.getProperty("url"),prop.getProperty("username"), prop.getProperty("password"));
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return null;
    }
  
    public static void close(Connection conn, ResultSet rs, PreparedStatement ps) {
    	if (rs != null) {
    		try {
    			rs.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			if (ps != null) {
    				try {
    					ps.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				} finally {
    					if (conn != null) {
    						try {
    							conn.close();
    						} catch (SQLException e) {
    							// TODO Auto-generated catch block
    							e.printStackTrace();
    						}
    					}
    				}
  
    			}
    		}
    	}
    }
  复制代码

  }

这样,就可以稍微简化一下DAO的操作了

//员工的添加操作
public void save(Employee emp){
	Connection conn=null;
	PreparedStatement ps=null;
	try {
			
		conn = JdbcUtil.getConnection();
		ps = conn.prepareStatement("insert into employee values(null,?,?,?)");
		ps.setString(1, emp.getName());
		ps.setInt(2, emp.getAge());
		ps.setBigDecimal(3, emp.getSalary());
		ps.execute();
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		//没有结果集,就用null
		JdbcUtil.close(conn,ps,null);
	}
		
}
复制代码

####接下来就是进行DAO的代码重构了

• dao的增删改操作基本上类似,可以取出相同的,不同的则由使用者自己添加.

• 查询的操作也基本上类似,也可以进行重构.

  public class MyJdbcTemplate {

    // 操作,增删改,传入sql语句和参数,参数和sql之间要一一对应
    public static boolean execute(String sql, Object... obj){
    	Connection conn = null;
    	PreparedStatement ps = null;
    	ResultSet rs = null;
    	conn = JdbcUtil.getConnection();
    	try{
    		ps = conn.prepareStatement(sql);
    		for (int i = 0; i < obj.length; i++) {
    			ps.setObject(i + 1, obj[i]);
    		}
    		if (ps.execute()) {
    			JdbcUtil.close(conn, rs, ps);
    			return true;
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	JdbcUtil.close(conn, rs, ps);
    	return false;
    }
  
    // 查询返回的是一个list集合,也可以用来查询单个,取list.get(0);
    public static <T> List<T> query(String sql,Class<T> clz, Object... params){
    	Connection conn = null;
    	PreparedStatement ps = null;
    	ResultSet rs = null;
    	//用来存储对象
    	List<T> list =new ArrayList<>();
    	conn = JdbcUtil.getConnection();
    	try{
    		ps = conn.prepareStatement(sql);
    		for (int i = 0; i < params.length; i++) {
    			ps.setObject(i + 1, params[i]);
    		}
    		rs = ps.executeQuery();
    		//内省机制，获取javabean中的属性方法
    		BeanInfo beanInfo=Introspector.getBeanInfo(clz,Object.class);
    		PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors();
    		while(rs.next()){
    			//实例化一个对象，用来存储获取到的值，
    			T obj =clz.newInstance();
    			for (PropertyDescriptor property : descriptors) {
    				//获取属性set方法
    				Method writeMethod = property.getWriteMethod();
    				//获取属性名称
    				String name=property.getName();
    				writeMethod.invoke(obj, rs.getObject(name));
    			}
    			list.add(obj);
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	
    	JdbcUtil.close(conn, rs, ps);
    	return list;
    }
  复制代码

  }

得到MyJdbcTemplate之后,curd的操作就变得十分简洁了.

public class EmployeeDaoImpl2 implements IEmployeeDao{
	public void save(Employee emp){
		String sql="insert into employee values(null,?,?,?)";
		try {
			MyJdbcTemplate.execute(sql, new Object[]{emp.getName(),emp.getAge(),emp.getSalary()});
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
	public void delete(Employee emp){
		String sql="delete from employee where id=?";
		try {
			MyJdbcTemplate.execute(sql, new Object[]{emp.getId()});
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	
		
	}
	public void update(Employee emp){
		String sql="update employee set name=? ,age=? ,salary=? where id=? ";
		try {
			MyJdbcTemplate.execute(sql, new Object[]{emp.getName(),emp.getAge(),emp.getSalary(),emp.getId()});
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		
		
		
	}
	public Employee find(Employee emp){
		String sql="select * from employee where id=?";
				
		try {
			return MyJdbcTemplate.query(sql, emp.getClass(), new Object[]{emp.getId()}).get(0);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		return null;
		
	}
	@Override
	public List<Employee> findAll() {
		String sql="select * from employee";
		try {
			return MyJdbcTemplate.query(sql, Employee.class);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	return null;	
	}
}
复制代码