工具类封装
在前面的JDBC案例中我们不难发现，每次操纵数据库都需要进行数据库的注册、连接等大量重复的操作，是的代码变得十分的臃肿，所以此时我们应该考虑如何把重复代码提取出来，随时需要随时拿来用。
这就是工具类的封装。
案例实现：实现emp表的查询、添加、删除、修改
封装DbUtils
由于多个地方都需要使用数据库连接和释放，所以把功能封装到工具类中DbUtils
四个功能：
注册驱动
获取连接
释放资源
执行命令
db.properties信息文件
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/emp user=root password=root
自定义DbUtils工具类
public class DBUtils { private static String driver; private static String url; private static String user; private static String password; static { // 使用信息文件进行赋值 // 使用类加载器加载信息文件 InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties pro = new Properties(); try { // 读取文件信息并赋值 pro.load(is); driver = pro.getProperty("driver"); url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); } catch (IOException e) { e.printStackTrace(); } try { // 注册驱动 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } // 获取数据库连接 public static Connection getConnection() { Connection conn = null; try { // 获取数据库连接 conn = DriverManager.getConnection(url, user, password); return conn; } catch (SQLException e) { e.printStackTrace(); return null; } } // 更新数据库 public static int update(String sql, Object... params) {// 接收SQL语句和数据完成数据库的操作 Connection conn = getConnection(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } int len = ps.executeUpdate(); return len; } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(conn, ps, null); } return 0; } // 查询所有 public static List<Employee> getAll() { List<Employee> list = new ArrayList<Employee>(); String sql = "SELECT * FROM emp;"; Connection conn = getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { int empno = rs.getInt(1); String ename = rs.getString(2); String job = rs.getString(3); int mgr = rs.getInt(4); Date hiredate = rs.getDate(5); double sal = rs.getDouble(6); double comm = rs.getDouble(7); int deptno = rs.getInt(8); Employee emp = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno); list.add(emp); } return list; } catch (SQLException e) { e.printStackTrace(); return null; } } // 根据ID查询 public static Employee getById(int empno) { String sql = "SELECT * FROM emp WHERE empno=?"; Connection conn = getConnection(); PreparedStatement ps = null; ResultSet rs = null; Employee emp = null; try { ps = conn.prepareStatement(sql); ps.setInt(1, empno); rs = ps.executeQuery(); if (rs.next()) { String ename = rs.getString(2); String job = rs.getString(3); int mgr = rs.getInt(4); Date hiredate = rs.getDate(5); double sal = rs.getDouble(6); double comm = rs.getDouble(7); int deptno = rs.getInt(8); emp = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno); } return emp; } catch (SQLException e) { e.printStackTrace(); return null; } } // 关闭数据库连接 public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (ps != null) { ps.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
