3.JDBC最终重构

一.DBCP连接池

DBCP连接池是Apache提供的,性能不错

1. 导入三个包

commons-dbcp2-2.1.1.jar
commons-pool2-2.4.2.jar
commons-logging-1.2.jar

代码演示

DBCP工具类 DbcpUtil

//Dbcp连接池
public class DbcpUtil {
	//数据源
	private static DataSource  ds;
	//静态代码块
	static {
		InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
		Properties p = new Properties();
		try {
			p.load(in);
			ds = BasicDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	public static DataSource getDataSource(){
		return ds;
	}
	public static void close(Connection connection, Statement statement, ResultSet resultSet) {
		// 5)释
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

DBCP测试类DbcpTest

// 测试
public class DbcpTest {
	StudentDAOImpl sImpl = new StudentDAOImpl();

	// 插入
	@Test
	public void testInsert() {
		// SQL语句
		String sql = "insert into t_student(name, age) value(?,?);";
		Connection connection = null;
		PreparedStatement statement = null;
		// 1)加
		try {
			connection = DbcpUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			// 4)执
			statement.setString(1, "洞妖洞妖");
			statement.setInt(2, 1122);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DbcpUtil.close(connection, statement, null);
		}

	}

	// 删除
	@Test
	public void testDelete() {
		// sql语句
		String sql = "delete from t_student where id = ?";
		Connection connection = null;
		PreparedStatement statement = null;
		// 1)加
		try {
			connection = DbcpUtil.getDataSource().getConnection();

			// 3)语
			statement = connection.prepareStatement(sql);
			statement.setLong(1, 13L);
			// 4)执
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DbcpUtil.close(connection, statement, null);
		}
	}

	// 更新
	@Test
	public void testUpdate() {
		// sql
		String sql = "update t_student set name = ?, age = ? where id = ?";
		Connection connection = null;
		PreparedStatement statement = null;
		// 1)加
		try {
			connection = DbcpUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			// 4)执
			statement.setString(1, "深蓝色");
			statement.setInt(2, 23);
			statement.setLong(3, 14L);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DbcpUtil.close(connection, statement, null);
		}
	}

	@Test
	public void testQuery() {
		// sql
		String sql = "select * from t_student where id = ?";
		Student student = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		// 1)加
		try {
			connection = DbcpUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			statement.setLong(1, 15L);
			// 4)执
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				student = new Student(resultSet.getLong("id"), resultSet.getString("name"), resultSet.getInt("age"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DbcpUtil.close(connection, statement, resultSet);
		}
		System.out.println(student);
	}

	@Test
	public void testQueryAll() {
		List<Student> list = new ArrayList<>();
		// sql语句
		String sql = "select * from t_student";
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		// 1 )加
		try {
			connection = DbcpUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			// 4)执
			resultSet = statement.executeQuery(sql);
			// 操作
			while (resultSet.next()) {
				// 添加到list
				Student student = new Student(resultSet.getLong("id"), resultSet.getString("name"),
						resultSet.getInt("age"));
				list.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbcpUtil.close(connection, statement, resultSet);
		}
		for (Student student : list) {
			System.out.println(student);
		}
	}

}

二.Duird连接池

druid:是阿里巴巴研发出来的号称Java语言领域性能最高的连接池.

wiki地址:https://github.com/alibaba/druid/wiki

导入一个druid包

代码演示

Druid工具类 DruidUtil

//Druid
public class DruidUtil {
	private static DataSource dataSource;
	
	static {
		InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
		Properties p = new Properties();
		try {
			p.load(in);
			dataSource = DruidDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	private DruidUtil() {
		
	}
	public static DataSource getDataSource() {
		return dataSource;
	}
	//close
	public static void close(Connection con,PreparedStatement ps,ResultSet rs) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}

Druid测试类

//Druid Test
public class DruidTest {
	StudentDAOImpl sImpl = new StudentDAOImpl();

	// 插入
	@Test
	public void testInsert() {
		// SQL语句
		String sql = "insert into t_student(name, age) value(?,?);";
		Connection connection = null;
		PreparedStatement statement = null;
		// 1)加
		try {
			connection = DruidUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			// 4)执
			statement.setString(1, "洞妖洞妖");
			statement.setInt(2, 1122);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DruidUtil.close(connection, statement, null);
		}

	}

	// 删除
	@Test
	public void testDelete() {
		// sql语句
		String sql = "delete from t_student where id = ?";
		Connection connection = null;
		PreparedStatement statement = null;
		// 1)加
		try {
			connection = DruidUtil.getDataSource().getConnection();

			// 3)语
			statement = connection.prepareStatement(sql);
			statement.setLong(1, 13L);
			// 4)执
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DruidUtil.close(connection, statement, null);
		}
	}

	// 更新
	@Test
	public void testUpdate() {
		// sql
		String sql = "update t_student set name = ?, age = ? where id = ?";
		Connection connection = null;
		PreparedStatement statement = null;
		// 1)加
		try {
			connection = DruidUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			// 4)执
			statement.setString(1, "深蓝色");
			statement.setInt(2, 23);
			statement.setLong(3, 14L);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DruidUtil.close(connection, statement, null);
		}
	}

	@Test
	public void testQuery() {
		// sql
		String sql = "select * from t_student where id = ?";
		Student student = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		// 1)加
		try {
			connection = DruidUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			statement.setLong(1, 15L);
			// 4)执
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				student = new Student(resultSet.getLong("id"), resultSet.getString("name"), resultSet.getInt("age"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5)释
			DruidUtil.close(connection, statement, resultSet);
		}
		System.out.println(student);
	}

	@Test
	public void testQueryAll() {
		List<Student> list = new ArrayList<>();
		// sql语句
		String sql = "select * from t_student";
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		// 1 )加
		try {
			connection = DruidUtil.getDataSource().getConnection();
			// 3)语
			statement = connection.prepareStatement(sql);
			// 4)执
			resultSet = statement.executeQuery(sql);
			// 操作
			while (resultSet.next()) {
				// 添加到list
				Student student = new Student(resultSet.getLong("id"), resultSet.getString("name"),
						resultSet.getInt("age"));
				list.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DruidUtil.close(connection, statement, resultSet);
		}
		for (Student student : list) {
			System.out.println(student);
		}
	}
}

三.JDBC最终重构

StudentDAOImpl DAO实现类

// DAO实现类
public class StudentDAOImpl implements IBeanDAO<Student> {
	// 插入
	public void insert(Student student) {
		String sql = "insert t_student(name,age) values(?,?)";
		DruidTempla.dml(sql, student.getName(),student.getAge());

	}

	public void delete(Long id) {
		String sql = "delete from t_student where id = ?";
		DruidTempla.dml(sql, id);
	}

	public void update(Student student) {
		String sql = "update t_student set name = ?, age = ? where id = ?";
		DruidTempla.dml(sql, student.getName(),student.getAge(),student.getId());
	}

	public Object query(Long id) {
		String sql = "select * from t_student where id = ?";
		List<Student> list = DruidTempla.dql(sql, new BeanResultHandler<>(Student.class), id);
		return list.size() > 0 ? list.get(0) : null;
	}

	@Override
	public List<Student> queryAll() {
		String sql = "select * from t_student";
		List<Student> list = DruidTempla.dql(sql, new BeanResultHandler<>(Student.class));
		return list.size() > 0 ? list : null;
	}

}

IBeanDAO接口

//DAO接口
public interface IBeanDAO<T> {
	/**
	 * 增
	 * @param student
	 */
	void insert(T t);
	/**
	 * 删
	 * @param id
	 */
	void delete(Long id);
	/**
	 * 改
	 * @param student
	 */
	void update(T t);
	/**
	 * 查
	 * @param id
	 * @return
	 */
	Object query(Long id);
	/**
	 * 查全部
	 * @return
	 */
	List<T> queryAll();
}

Student实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
	private Long id;
	private String name;
	private Integer age;
}

通用处理结果类

/**
 * 通用处理结果类
 * @author Administrator
 *
 * @param <T>
 */
public class BeanResultHandler<T> implements IResultHandler<List<T>> {
	private Class<T> clz;
	public BeanResultHandler(Class<T> clz){
		this.clz = clz;
	}
	/**
	 * 
	 * @param ResultSet rs
	 * @return List<T>
	 */
	public List<T> resultHandler(ResultSet rs) {
		//1)通过内省获得属性描述器
		List<T> list = new ArrayList<>();
		try {
			BeanInfo info = Introspector.getBeanInfo(clz,Object.class);
			PropertyDescriptor[] pds = info.getPropertyDescriptors();
			//2)遍历结果集
			while (rs.next()) {
				T t = clz.newInstance();
				//3)遍历属性描述器 设置值
				for (PropertyDescriptor pd :pds) {
					pd.getWriteMethod().invoke(t, rs.getObject(pd.getName()));
				}
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list.size() > 0 ? list : null;
	}

}

处理结果接口 IResultHandler

/**
 * 处理结果接口
 * @author Administrator
 *
 */
public interface IResultHandler <T>{
	/**
	 * 
	 * @param ResultSet rs
	 * @return T
	 */
	T resultHandler(ResultSet rs);
}

DAO测试类 StudentDAOTest

/**
 * DAO测试类
 * @author Administrator
 *
 */
public class StudentDAOTest {
	IBeanDAO<Student> dao = new StudentDAOImpl();
	@Test
	public void testInsert() {
		Student student = new Student(null,"非常龙",17);
		dao.insert(student);
	}

	@Test
	public void testDelete() {
		dao.delete(15L);
	}

	@Test
	public void testUpdate() {
		Student student = new Student(16L,"真.红眼黑龙",17);
		dao.update(student);
	}

	@Test
	public void testQuery() {
		Object student = dao.query(24L);
		System.out.println(student);
	}

	@Test
	public void testQueryAll() {
		List<Student> list = dao.queryAll();
		for (Object object : list) {
			System.out.println(object);
		}
	}

}

DML和DQL模板 DruidTempla类

/**
 * DML和DQL模板
 * @author Administrator
 *
 */
public class DruidTempla {
	private DruidTempla() {
	};

	/**
	 * DML操作
	 * @param sql
	 * @param objects
	 */
	public static void dml(String sql, Object... objects) {
		// 1)获取Connection
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = DruidUtil.getDataSource().getConnection();
			// 2)获取PerparedSatement对象
			ps = connection.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < objects.length; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			// 执行
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DruidUtil.close(connection, ps, null);
		}

	}

	public static <T> T dql(String sql,IResultHandler<T> rh,Object...objects) {
		// 1)获取Connection
		Connection connection = null;
		PreparedStatement ps = null;
		T t = null;
		try {
			connection = DruidUtil.getDataSource().getConnection();
			// 2)获取PerparedSatement对象
			ps = connection.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < objects.length; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			// 执行
			ResultSet rs = ps.executeQuery();
			t = rh.resultHandler(rs);			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DruidUtil.close(connection, ps, null);
		}
		return t;
	}
}

Druid工具类

// Druid工具类
public class DruidUtil {
	private static DataSource ds;
	static {
		InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
		Properties p = new Properties();
		try {
			p.load(in);
			ds = DruidDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取DataSource
	 * @return DataSource
	 */
	public static DataSource getDataSource() {
		return ds;
	}

	/**
	 * 关闭资源
	 * @param Connection con
	 * @param PreparedStatement ps
	 * @param ResultSet rs
	 */
	public static void close(Connection con, PreparedStatement ps, ResultSet rs) {
		// 关闭
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}