Java-JDBC-JDBCUtils工具类
目录
文章目录
内容
之前关于JDBC的测试,都有一些相同的步骤:
- 数据库连接
- 资源释放
每次都要书写相同的步骤,是不是很麻烦?那么我们就可以编写个工具类,把这些共同的操作放一起,方便以后使用。
-
工具类代码分析:
- 需要一个获取连接对象的方法getConnection
- getConnection所需参数url, username, password通过配置文件获取
- 只需要读取一次,可以在静态代码块中配置
- 需要释放资源的方法close
- 需要一个获取连接对象的方法getConnection
-
工具类代码:
package cn.gaogzhen.util; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JDBCUtils { private static Connection conn; /** * 静态代码块,获取配置信息 */ static { try { Properties prop = new Properties(); prop.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties")); String url = prop.getProperty("url"); String username = prop.getProperty("username"); String password = prop.getProperty("password"); String driver = prop.getProperty("driver"); // 注册驱动 Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (IOException | ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { return conn; } /** * 释放资源 * @param rs 结果集对象 * @param stmt 执行SQL语句对象 * @param conn 连接对象 */ public static void close(ResultSet rs, Statement stmt, Connection conn) { if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(Statement stmt, Connection conn) { close(null, stmt, conn); } }
-
使用测试:获取表emp中所有数据,封装对象并放入集合中-改造
// 之前没用工具类代码在JDBC快速入门博文中 // 改造后 package cn.gaogzhen.jdbc; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.List; import cn.gaogzhen.domain.Emp; import cn.gaogzhen.util.JDBCUtils; public class JDBCDemo6 { public static void main(String[] args) { List<Emp> list = new JDBCDemo6().findAll(); System.out.println(list); } public List<Emp> findAll() { Connection conn = null; Statement stmt = null; ResultSet rs = null; List<Emp> list = new ArrayList<>(); // 2. 注册驱动 try { conn = JDBCUtils.getConnection(); // 4. sql语句 String sql = "select * from emp"; // 5. 获取执行sql语句的对象statement stmt = conn.createStatement(); // 6. 执行sql操作,获取结果 rs = stmt.executeQuery(sql); // 7. 解析结果 while(rs.next()) { int id = rs.getInt("id"); String ename = rs.getString("ename"); int job_id = rs.getInt("job_id"); int mgr = rs.getInt("mgr"); Date joindate = rs.getDate("joindate"); double salary = rs.getDouble("salary"); double bonus = rs.getDouble("bonus"); int dept_id = rs.getInt("dept_id"); list.add(new Emp(id, ename, job_id, mgr, joindate, salary, bonus, dept_id)); } } catch (SQLException e) { e.printStackTrace(); } finally { // 8. 释放资源 JDBCUtils.close(rs, stmt, conn); } return list; } }
测试结果:
[Emp [id=1001, name=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=8000.0, bonus=0.0, dept_id=20], Emp [id=1002, name=卢俊义, job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.0, bonus=3000.0, dept_id=30], Emp [id=1003, name=林冲, job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.0, bonus=5000.0, dept_id=30], Emp [id=1004, name=唐僧, job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.0, bonus=0.0, dept_id=20], Emp [id=1005, name=李逵, job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.0, bonus=14000.0, dept_id=30], Emp [id=1006, name=宋江, job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.0, bonus=0.0, dept_id=30], Emp [id=1007, name=刘备, job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.0, bonus=0.0, dept_id=10], Emp [id=1008, name=猪八戒, job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.0, bonus=0.0, dept_id=20], Emp [id=1009, name=罗贯中, job_id=1, mgr=0, joindate=2001-11-17, salary=50000.0, bonus=0.0, dept_id=10], Emp [id=1010, name=吴用, job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.0, bonus=0.0, dept_id=30], Emp [id=1011, name=沙僧, job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.0, bonus=0.0, dept_id=20], Emp [id=1012, name=李逵, job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.0, bonus=0.0, dept_id=30], Emp [id=1013, name=小白龙, job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.0, bonus=0.0, dept_id=20], Emp [id=1014, name=关羽, job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.0, bonus=0.0, dept_id=10]]
后记 :
本项目为参考某马视频开发,相关视频及配套资料可自行度娘或者联系本人。上面为自己编写的开发文档,持续更新。欢迎交流,本人QQ:806797785
前端项目源代码地址:https://gitee.com/gaogzhen/vue-leyou
后端JAVA源代码地址:https://gitee.com/gaogzhen/JAVA