JDBC练习_select语句与JDBC工具类
JDBC练习_select语句
练习：
定义一个方法，查询emp表的数据将其封装为对象，然后封装集合，返回。
1.定义Emp类
2.定义方法 public List<emp> findAll()
3.实现方法 select * from emp;
package CN.XueQiang.JDBC; import CN.XueQiang.domain.Emp; import java.sql.*; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class JDBCDemo8 { /* 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回 */ public static void main(String[] args) { List<Emp> all = new JDBCDemo8().findAll(); for (Emp emp : all) { System.out.println(emp); } } public List<Emp> findAll() { ResultSet rs = null; Connection conn = null; Statement stat = null; List<Emp> list = null; try { //1. 注册驱动 Class.forName("com.mysql.jdbc.Driver"); // 2.获取连接 conn = DriverManager.getConnection("jdbc:mysql:///win1", "root", "njzyb555"); // 3.定义数据库 String sql = "select * from emp"; // 4.获取执行sql的对象 stat = conn.createStatement(); // 5.执行sql rs = stat.executeQuery(sql); // 6.遍历结果集，封装对象，装载集合 Emp emp = null; list = new ArrayList<Emp>(); 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"); emp = new Emp(); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); // 装载集合 list.add(emp); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stat != null) { try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } return list; } }
JDBC工具类
目的：简化书写
分析：
1.注册驱动也抽取
2.抽取一个方法获取连接
需求：不想传递参数（麻烦），还得保证工具类的通用性
解决：配置文件
jdbc.properties
3.抽取一个方法释放资源
JDBCDemo8
/** * 演示JDBC工具类 * * @return */ public List<Emp> findAll2() { Connection conn = null; Statement stmt = null; ResultSet rs = null; List<Emp> list = null; try { /* //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");*/ conn = JDBCUtils.getConnection(); //3.定义sql String sql = "select * from emp"; //4.获取执行sql的对象 stmt = conn.createStatement(); //5.执行sql rs = stmt.executeQuery(sql); //6.遍历结果集，封装对象，装载集合 Emp emp = null; list = new ArrayList<Emp>(); 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"); // 创建emp对象,并赋值 emp = new Emp(); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); //装载集合 list.add(emp); } } catch (SQLException e) { e.printStackTrace(); } finally { /*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(); } }*/ JDBCUtils.close(rs, stmt, conn); } return list; } }
JDBCUtils
package CN.XueQiang.util; import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.sql.*; import java.util.Properties; /** * JDBC工具类 */ public class JDBCUtils { private static String url; private static String user; private static String password; private static String driver; /** * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块 */ static{ //读取资源文件，获取值。 try { //1. 创建Properties集合类。 Properties pro = new Properties(); //获取src路径下的文件的方式--->ClassLoader 类加载器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); // System.out.println(path);///D:/IdeaProjects/itcast/out/production/day04_jdbc/jdbc.properties //2. 加载文件 // pro.load(new FileReader("D:\\IdeaProjects\\itcast\\day04_jdbc\\src\\jdbc.properties")); pro.load(new FileReader(path)); //3. 获取数据，赋值 url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //4. 注册驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取连接 * @return 连接对象 */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } /** * 释放资源 * @param stmt * @param conn */ public static void close(Statement stmt,Connection conn){ if( stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if( conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 释放资源 * @param stmt * @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(); } } } }
jdbc.properties
url: jdbc:mysql://localhost:3306/win1?useSSL=false&characterEncoding=utf8&useOldAliasMetadataBehavior=true user=root password=******** driver=com.mysql.jdbc.Driver
