JDBC05-JDBC工具类3
每次都要注册驱动、获取连接等重复的代码，重复比较繁琐，所以抽取JDBC工具类 ： JDBCUtils
1、目的
简化书写
2、分析
1. 注册驱动也抽取
2. 抽取一个方法获取连接对象
需求：不想传递参数（麻烦），还得保证工具类的通用性。
解决：配置文件
jdbc.properties
url=
user=
password=
3. 抽取一个方法释放资源
3、代码实现
src根路径下创建一个jdbc.properties文件
url=jdbc:mysql:///db3
user=root
password=root
driver=com.mysql.jdbc.Driver
package com.itcast.jdbc; import java.util.Date; /** * 封装Emp表数据的JavaBean */ public class Emp { private int id; private String ename; private int job_id; private int mgr; private Date joindate; private double salary; private double bonus; private int dept_id; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public int getJob_id() { return job_id; } public void setJob_id(int job_id) { this.job_id = job_id; } public int getMgr() { return mgr; } public void setMgr(int mgr) { this.mgr = mgr; } public Date getJoindate() { return joindate; } public void setJoindate(Date joindate) { this.joindate = joindate; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } public double getBonus() { return bonus; } public void setBonus(double bonus) { this.bonus = bonus; } public int getDept_id() { return dept_id; } public void setDept_id(int dept_id) { this.dept_id = dept_id; } @Override public String toString() { return "Emp{" + "id=" + id + ", ename='" + ename + '\'' + ", job_id=" + job_id + ", mgr=" + mgr + ", joindate=" + joindate + ", salary=" + salary + ", bonus=" + bonus + ", dept_id=" + dept_id + '}'; } }
package com.itcast.jdbc; import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer; import java.io.*; 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{ //读取资源文件，获取值。 //1. 创建Properties集合类 Properties pro = new Properties(); //获取src路径下的文件的方式--->ClassLoader类加载器(加载字节码文件进内容，可获取src下资源文件的路径) ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL resource = classLoader.getResource("jdbc.properties");//以src为根路径 String path = resource.getPath(); System.out.println(path); try { //2. 加载文件 // pro.load(new FileReader("/Users/alsc/Desktop/lj/workspace/day05_jdbc/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 stat * @param conn */ public static void close(Statement stat, Connection conn){ if(stat != null){ try { stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } /** * 释放资源 * @param rs * @param stat * @param conn */ public static void close(ResultSet rs,Statement stat, Connection conn){ if(rs != null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(stat != null){ try { stat.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
package com.itcast.jdbc; import java.sql.*; import java.util.ArrayList; import java.util.List; public class JDBCDemo9 { public static void main(String[] args) { List<Emp> list = new JDBCDemo9().findAll2(); System.out.println(list); System.out.println(list.size()); } /** * 查询所有emp对象 * @return */ public List<Emp> findAll(){ Connection conn = null; Statement stat = 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"); //3. 定义sql String sql = "select * from emp"; //4. 获取执行sql的对象 Statement 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");//这是java.sql.Date，是java.util.Date的子类 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 (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //7. 释放资源 //避免空指针异常 if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stat != null){ try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return list; } /** * 演示JDBC工具类 * @return */ public List<Emp> findAll2(){ Connection conn = null; Statement stat = null; ResultSet rs = null; List<Emp> list = null; try { //1. 注册驱动 //2. 获取连接 conn = JDBCUtils.getConnection(); //3. 定义sql String sql = "select * from emp"; //4. 获取执行sql的对象 Statement 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");//这是java.sql.Date，是java.util.Date的子类 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 { //7. 释放资源 //避免空指针异常 JDBCUtils.close(rs,stat,conn); } return list; } }
输出结果：
4、练习
需求：1. 通过键盘录入用户名和密码 2. 判断用户是否登录成功
select * from user where username = "" and password = "";
如果这个sql有查询结果则成功，反之则失败
步骤：
1. db4数据库中创建数据库表user
CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32), PASSWORD VARCHAR(32) );
INSERT INTO USER VALUES (NULL,"zhangsan","123"); INSERT INTO USER VALUES (NULL,"lisi","1234");
2. 代码实现
package cn.itcast.jdbc; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; /** * 需求： * 1. 通过键盘录入用户名和密码 * 2. 判断用户是否登录成功 */ public class JDBCDemo10 { public static void main(String[] args) { //1. 键盘录入，接收用户名和密码 Scanner sc = new Scanner(System.in); System.out.println("请输入用户名"); String username = sc.nextLine(); System.out.println("请输入密码"); String password = sc.nextLine(); //2. 调用方法 JDBCDemo10 jdbcDemo10 = new JDBCDemo10(); boolean flag = jdbcDemo10.login(username,password); //3. 判断结果，输出不同语句 if(flag){ System.out.println("登录成功！"); }else{ System.out.println("用户名或密码错误！"); } } /** * 登录方法 */ public boolean login(String username,String password){ if(username == null && password == null){ return false; } Connection conn = null; Statement stat = null; ResultSet rs = null; //连接数据库判断是否登录成功 //1. 获取连接 try { conn = JDBCUtils.getConnection(); //2. 定义sql String sql = "select * from user where username= '" + username + "' and password = '" + password+"'"; //3. 获取执行sql的对象 stat = conn.createStatement(); //4. 执行查询 rs = stat.executeQuery(sql); //5. 判断 return rs.next();//如果有下一行则返回true } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stat, conn); } return false; } }
执行结果：
但是存在一个问题，用户名不是数据库的用户名有可能会登录成功，需要PreparedStatement去解决
