使用切面思想创建JDBC工具类

数据库编程步骤：
1.加载驱动，获取连接对象
2.通过连接对象获取预编译对象
3.通过预编译对象的一些方法执行SQL语句对数据库进行操作
4.处理结果

5.释放资源

        普通JDBC

	public static void main(String[] args) {
		String sql ="select * from stu";
		try {
			//1.加载驱动，通过驱动管理器获取连接对象
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/hello","root","root");
			//2.通过连接对象获取预编译对象
			PreparedStatement ps= con.prepareStatement(sql);
			//3.通过预编译对象的一些方法执行sql语句，操作数据库
			ResultSet rs= ps.executeQuery();
			//4.处理结果
			while(rs.next()){
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getString("name"));
				System.out.println(rs.getTimestamp("createDate")+"\n");
			}
			//5.释放资源
			rs.close();
			ps.close();
			con.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

 优化后的JDBC

    //一个工具类，负责获取连接和释放资源

    public abstract class MySQLUtil {
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private Object o = null;		//入参

	public MySQLUtil() {
	}

	public MySQLUtil(Object o) {
		this.o = o;
	}

	public void setRs(ResultSet rs) {
		this.rs = rs;
	}

	public Object getO() {
		return o;
	}

	// 获取连接对象
	private Connection getConnection() throws ClassNotFoundException,
			SQLException {
		// 加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		// 获取连接对象并返回
		return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/hello",
				"root", "root");
	}

	// 获取预编译对象
	protected PreparedStatement getPreparedStatement(String sql)
			throws ClassNotFoundException, SQLException {
		con = getConnection();
		ps = con.prepareStatement(sql);
		return ps;
	}

	// 释放资源
	private void close() throws SQLException {
		if (ps != null) {
			ps.close();
		}
		if (con != null) {
			con.close();
		}
		if (rs != null) {
			rs.close();
		}
	}

	//查询获得结果集
	protected ResultSet executeQuery() {
		if (ps != null) {
			try {
				rs=ps.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rs;
	}
	
	protected ResultSet executeQuery(String sql) {
		if (ps != null) {
			try {
				rs=ps.executeQuery(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rs;
	}
	
	/**
	 * 启动
	 * 
	 * @return 
	 *         Object,如果返回-1则出现ClassNotFoundException如果-2出现SQLExection,-3释放资源时出现异常
	 */
	public Object start() {
		Object o = null;
		try {
			o = by();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return o;
	}

	protected abstract Object by() throws ClassNotFoundException, SQLException;
}

工具类的使用方法

        将创建连接和释放资源单独拿出来，而我们只需要注重业务逻辑的实现

        

	public static List<String> ab(int i) {
		return (List<String>) new MySQLUtil(i) {
			protected Object by() throws ClassNotFoundException, SQLException {
				int i = (Integer) getO();
				// sql语句
				String sql = "select * from stu where id=?";
				if (i == 0) {
					sql = "select * from stu where 1=1 or id=?";
				}
				// 获取预编译对象
				PreparedStatement ps = getPreparedStatement(sql);
				// 替换占位符
				ps.setInt(1, i);
				// 执行sql语句，得到结果集
				ResultSet rs = executeQuery();
				List<String> strs = new ArrayList();
				while (rs.next()) {
					strs.add("id["
							+ rs.getInt("id")
							+ "]姓名["
							+ rs.getString("name")
							+ "]创建时间["
							+ new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒")
									.format(rs.getTimestamp("createDate"))
							+ "]");
				}
				return strs;
			}
		}.start();
	}