JDBC工具类:JDBCUtils

• 目的:简化书写

• 分析:

  1. 注册驱动抽取

  2. 抽取一个方法获取连接对象

     • 需求:不想传递参数,但又可以保证工具类的通用性

     • 解决:配置文件,假如我们需要修改连接的数据库那些,我们只需要修改配置文件即可,复用性大大提高

       jdbc.properties

       url=
       user=
       password=
       driver=

  3. 抽取一个方法释放资源

  JDBCUtils

  /**
   * @author YQHP-YuKi
   * @create 2021-11-14 15:58
   */
  
  import java.io.FileReader;
  import java.io.IOException;
  import java.net.URL;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  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);
        //2.加载文件
        pro.load(new FileReader(path));
        //3.获取属性,赋值
        url = pro.getProperty("url");
        user = pro.getProperty("user");
        password = pro.getProperty("password");
        driver = pro.getProperty("driver");
        //注册驱动
        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();
        }
      }
    }
  
    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();
        }
      }
    }
  }

  测试用例:直接省去getConnection与close()

  /**
   * 定义一个方法,查询EMP表并将其数据封装为对象,然后装载集合并返回
   */
  public class Demo09 {
  
    public static void main(String[] args) {
      List<Emp> list = new Demo09().findAll();
      System.out.println(list);
      System.out.println(list.size());
    }
  
    /**
     * 查询所有emp对象
     */
    public List<Emp> findAll() {
      ResultSet rs = null;
      Statement stmt = null;
      Connection conn = null;
      List<Emp> list = null;
      try {
        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 empno = rs.getInt("EMPNO");
          String ename = rs.getString("ENAME");
          String job = rs.getString("JOB");
          int mgr = rs.getInt("MGR");
          Date hiredate = rs.getDate("HIREDATE");
          double sal = rs.getDouble("SAL");
          double comm = rs.getDouble("COMM");
          int deptno = rs.getInt("DEPTNO");
          //创建EMP对象,并赋值
          emp = new Emp();
          emp.setEMPNO(empno);
          emp.setENAME((ename));
          emp.setJOB(job);
          emp.setMGR(mgr);
          emp.setHIREDATE(hiredate);
          emp.setSAL(sal);
          emp.setCOMM(comm);
          emp.setDEPTNO(deptno);
          //装载集合
          list.add(emp);
        }
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        JDBCUtils.close(rs, stmt, conn);
      }
      return list;
    }
  }

练习

• 需求:

  1. 通过键盘录入用户名和密码

  2. 判断用户是否登录成功

     select * from user where username = '你输入的' and password = '你输入的';

     • 如果这个sql语句有返回查询结果,则成功,反之失败

• 步骤:

  1. 创建数据库表user
  2. 写代码,注:这个代码有sql注入问题,仅作为演示

  public class Demo10 {
  
    public static void main(String[] args) {
      //1.键盘录入usernaeme和password
      Scanner sc = new Scanner(System.in);
      System.out.println("请输入username");
      String username = sc.nextLine();
      System.out.println("请输入password");
      String password = sc.nextLine();
      //2.调用方法
      boolean flag = new Demo10().login(username, password);
      //3.判断结果
      if (flag) {
        System.out.println("login successful");
      } else {
        System.out.println("login failed");
      }
    }
  
    /**
     * 登录方法
     */
    public boolean login(String username, String password) {
      if (username != null && password != null) {
        //连接数据库判断是否登录成功
        //1.获取数据库连接
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
          conn = JDBCUtils.getConnection();
          //2.定义sql
          String sql =
              "select * from user where username = '" + username + "' and password = '" + password
                  + "'";
          //3/获取执行sql的对象
          stmt = conn.createStatement();
          //4.执行查询sql
          rs = stmt.executeQuery(sql);
          //5.判断sql是否执行成功
          return rs.next();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          JDBCUtils.close(rs, stmt, conn);
        }
      } else {
        return false;
      }
      return false;
    }
  }

PreparedStatement:执行sql的对象

1. SQL注入问题:在拼接sql时,有一些sql的关键字参与字符串的拼接,会造成安全性问题

• 例:输入用户名username随便,密码输入a' or 'a' = 'a ,先前代码执行,结果都会为login successful

select * from user where username = 'dsafsdfesf' and password = 'a' or 'a' = 'a'

可以在MySQL中执行这条语句看看结果,因为or后面都会为true,所以都会查询出全部类容

2. 解决SQL注入问题:使用PreparedStatement对象来解决
3. 预编译的SQL:参数使用?作为占位符

4. 使用步骤:

   1. 导入驱动jar包
   2. 注册驱动
   3. 获取数据库连接对象Connection

   4. 定义sql

      • 注意:sql的参数使用?作为占位符,如:select * from user where username = ? and password = ?;
   5. 获取执行sql语句的对象 PreparedStatement Connection.prepareStatement(sql);

   6. 给?赋值

      • 方法:set***(参数1,参数2)

        • 参数1:?的位置编号,从1开始
        • 参数2:?的值
   7. 执行sql,接受返回结果,不需要传递sql语句
   8. 处理结果
   9. 释放资源

5. 注意:后期都会使用PreparedStatement来完成增删改查的所有操作

   1. 可以防止SQL注入
   2. 效率更高

例:

防止SQL注入

请输入username
dsadasfaswe
请输入password
a' or 'a' = 'a
/D:/Java/workspeace/learn/out/production/learn/jdbc.properties
login failed

  /**
   * 使用PreparedStatement登录方法
   */
  public boolean login(String username, String password) {
    if (username != null && password != null) {
      //连接数据库判断是否登录成功
      //1.获取数据库连接
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try {
        conn = JDBCUtils.getConnection();
        //2.定义sql
        String sql =
            "select * from user where username = ? and password = ?";
        //3/获取执行sql的对象
        pstmt = conn.prepareStatement(sql);
        //给?赋值
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        //4.执行查询sql.不需要传参
        rs = pstmt.executeQuery();
        //5.判断sql是否执行成功
        return rs.next();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        JDBCUtils.close(rs, pstmt, conn);
      }
    } else {
      return false;
    }
    return false;
  }

JDBC控制事务

1. 事务:一个包含多个步骤的业务操作,如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败

2. 操作

   1. 开启事务
   2. 提交事务
   3. 回滚事务

3. 使用Connection对象来管理事务

   • 开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即开启事务

     • 在执行sql之前开启事务

   • 提交事务:commit()

     • 当所有sql都执行完后提交事务

   • 回滚事务:rollback()

     • 在catch中回滚事务

例：进行转账操作,但中间代码有意制造报错,并抓取到报错进行事务回滚

  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    try {
      //1.获取连接
      conn = JDBCUtils.getConnection();
      //开启事务
      conn.setAutoCommit(false);
      //2.定义sql
      //2.1 XianKe转给YuKi 600 XianKe -600
      String sql1 = "update account set balance = balance - ? where id = ?";
      //2.2 YuKi +600
      String sql2 = "update account set balance = balance + ? where id = ?";
      //获取执行sql对象
      pstmt1 = conn.prepareStatement(sql1);
      pstmt2 = conn.prepareStatement(sql2);
      //设置参数
      pstmt1.setInt(1, 600);
      pstmt1.setInt(2, 2);
      pstmt2.setInt(1, 600);
      pstmt2.setInt(2, 1);
      //执行sql
      pstmt1.executeUpdate();
      //手动制造异常
      int i = 3 / 0;
      pstmt2.executeUpdate();
      //提交事务
      conn.commit();
    } catch (Exception e) {
      e.printStackTrace();
      //事务回滚
      try {
        if (conn != null) {
          conn.rollback();
        }
      } catch (SQLException ex) {
        ex.printStackTrace();
      }
    } finally {
      JDBCUtils.close(pstmt2, conn);
      JDBCUtils.close(pstmt1, null);
    }
  }

数据库连接池

• 概念:其实就是一个容器(集合),存放数据库连接的容器,当系统初始化好之后,容器被创建,容器中会申请一些连接对象,当用户来访问数据库时,从容器中获取连接对象,用户访问完之后,会将连接对象归还给容器

• 优势:

  1. 节约资源
  2. 用户访问高效

• 实现:

  1. 标准接口:DataSource javax.sql包下

     1. 方法:

        • 获取连接:getConnection()
        • 归还连接:Connection.close(),如果连接对象Connection是从连接池中获取的,那么调用Connection.close()方法,则不会再关闭连接了,而是直接归还连接

  2. 一般我们不去实现它,有数据库厂商来实现

     1. C3P0:数据库连接池技术
     2. Druid:数据库连接池技术

C3P0:数据库连接池技术

• 步骤:

  1. 导入jar包(两个) c3p0-0.9.5.5.jar mchange-commons-java-0.2.19.jar,下载网址

  2. 定义配置文件:

     • 名称:c3p0.properties或者c3p0-config.xml
     • 路径:直接将文件放在src目录下即可
  3. 创建核心对象:数据库连接池对象ComboPooledDataSource
  4. 获取连接:getConnection

  c3p0-config.xml

  <?xml version="1.0" encoding="UTF-8"?>
  <c3p0-config>
    <default-config>
      <!--mysql数据库连接的各项参数-->
      <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql:///testmysql?useSSL=false&amp;serverTimezone=UTC</property>
      <property name="user">root</property>
      <property name="password">zxasqw12</property>
      <!--配置数据库连接池的初始连接数、最小链接数、获取连接数、最大连接数、最大空闲时间-->
      <!--连接池参数-->
      <!--初始化申请的连接池数量-->
      <property name="initialPoolSize">5</property>
      <property name="minPoolSize">10</property>
      <!--超时时间-->
      <property name="checkoutTimeout">3000</property>
      <property name="acquireIncrement">5</property>
      <!--最大的连接数量-->
      <property name="maxPoolSize">10</property>
      <property name="maxIdleTime">30</property>
    </default-config>
  
    <named-config name="c3p0other1">
      <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql:///testmysql?useSSL=false&amp;serverTimezone=UTC</property>
      <property name="user">root</property>
      <property name="password">zxasqw12</property>
      <property name="initialPoolSize">5</property>
      <property name="maxPoolSize">8</property>
      <property name="checkoutTimeout">1000</property>
    </named-config>
  </c3p0-config>

  注意:一定要对应自己的driverClass与jdbcUrl,很多时候显示连接错误都是这两个写错了造成的,还有就是在连接的时候也需要mysql的jar包

  连接测试

    public static void main(String[] args) throws SQLException {
      //创建数据库连接对象
      DataSource ds = new ComboPooledDataSource("c3p0other1");
      for (int i = 1; i <= 10; i++) {
        //获取连接对象
        Connection conn = ds.getConnection();
        //打印
        System.out.println(i + ":" + conn);
      }
    }

  因为我们这时候xml设置的最大10个连接数,所以我们同一时间连接10个是不会报错的,但是如果你把for循环里的值调大,再运行代码,就会发生报错

  归还到连接池

    public static void main(String[] args) throws SQLException {
      //1.获取DataSource,使用默认配置
      DataSource ds = new ComboPooledDataSource();
      //2.获取连接
      for (int i = 1; i <= 11; i++) {
        Connection conn = ds.getConnection();
        System.out.println(i + ":" + conn);
        if (i == 5) {
          //归还连接到连接池中
          conn.close();
        }
      }
    }

  虽然我们没有修改xml,但是运行却不会报错,这是因为我们把第5个连接归还到了连接池中,我们可以仔细看一下控制台输出,可以发现之后的连接编号有一个跟5连接编号是一样的,说明5成功归还到了连接池

  使用不同的config

    public static void main(String[] args) throws SQLException {
      //创建数据库连接对象
      DataSource ds = new ComboPooledDataSource("c3p0other1");
      for (int i = 1; i <= 10; i++) {
        //获取连接对象
        Connection conn = ds.getConnection();
        //打印
        System.out.println(i + ":" + conn);
      }
    }

  我们也发现了xml中有一些config是带名称的,这是因为如果我们默认连接的是default-config,但如果我们想连接不同的database,就可以使用不同的config,只需要在连接对象中填入对应的config名称即可

druid:数据库连接池技术,由阿里巴巴提供的开源项目

下载地址:github项目

• 步骤:

  1. 导入jar包:druid-1.2.8.jar

  2. 定义配置文件:

     • 是properties形式的
     • 可以叫任意名称,可以放在任意目录下
  3. 加载配置文件Properties
  4. 获取数据库连接对象:通过工厂类来获取DruidDataSourceFactory
  5. 获取连接:getConnection

  配置文件

  driverClassName=com.mysql.cj.jdbc.Driver
  url=jdbc:mysql:///testmysql?useSSL=false&serverTimezone=UTC
  username=root
  password=root
  initialSize=5
  maxActive=10
  maxWait=3000

  连接测试

    public static void main(String[] args) throws Exception {
      //1.导入jar包
      //2.定义配置文件
      //3.加载配置文件
      Properties pro = new Properties();
      InputStream inputStream = Demo1.class.getClassLoader()
          .getResourceAsStream("druid.properties");
      pro.load(inputStream);
      //4.获取连接池对象
      DataSource ds = DruidDataSourceFactory.createDataSource(pro);
      Connection conn = ds.getConnection();
      System.out.println(conn);
    }

• 定义工具类

  1. 定义一个类JDBCUtils
  2. 提供静态代码块加载配置文件,初始化连接对象

  3. 提供方法

     1. 获取连接方法:通过数据库连接池获取连接
     2. 释放资源
     3. 获取连接池的方法

  JDBCUtils工具类

  /**
   * Druid连接池的工具类
   */
  public class JDBCUtils {
  
    /**
     * 1.定义成员变量DataSource
     */
    private static DataSource ds;
  
    static {
      try {
        //1.加载配置文件
        Properties pro = new Properties();
        pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
        //2.获取DataSource
        ds = DruidDataSourceFactory.createDataSource(pro);
      } catch (IOException e) {
        e.printStackTrace();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  
    /**
     * 获取连接方法
     */
    public static Connection getConnection() throws SQLException {
      return ds.getConnection();
    }
  
    /**
     * 释放资源
     */
    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();
        }
      }
    }
  
    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 DataSource getDataSource() {
      return ds;
    }
  }

  测试工具类

    public static void main(String[] args) {
      //给account表中添加一条数据
      Connection conn = null;
      PreparedStatement pstmt = null;
      try {
        //1.获取连接
        conn = JDBCUtils.getConnection();
        //2.定义sql
        String sql = "insert into account values(?,?,?)";
        //3.获取pstmt对象
        pstmt = conn.prepareStatement(sql);
        //4.给?赋值
        pstmt.setInt(1, 3);
        pstmt.setString(2, "HanHan");
        pstmt.setInt(3, 4000);
        //5.执行sql
        int count = pstmt.executeUpdate();
        System.out.println(count);
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        //6.释放资源
        JDBCUtils.close(pstmt, conn);
      }
    }

Spring JDBC

• Spring框架对JDBC进行了简单封装,提供了一个JDBCTemplate对象简化了JDBC的开发

• 步骤:

  1. 导入jar包:commons-logging-1.2.jar,spring-beans-5.1.10.RELEASE.jar,spring-core-5.1.10.RELEASE.jar,spring-jdbc-5.1.10.RELEASE.jar,spring-tx-5.1.10.RELEASE.jar

  2. 创建JDBCTemplate对象,依赖于数据源DataSource

     • JdbcTemplate template = new JdbcTemplate(ds);

  连接测试

    public static void main(String[] args) {
      //1.导入jar包
      //2.创建JDBCTemplate对象
      JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
      //3.调用方法
      String sql = "update account set balance = 7000 where name = ?";
      int count = template.update(sql, "XianKe");
      System.out.println(count);
    }

3. 调用JDBCTemplate的方法来完成CURD的操作

   • update():执行DML语句,增删改语句

   • queryForMap():查询结果将结果集封装为map集合

     • 注意:这个方法查询的结果集长度只能是1

   • queryForList():查询结果将结果集封装为list集合

     • 注意:将每一条记录封装为一个Map集合,再将Map集合装载到List集合中

   • query():查询结果,将结果封装为JavaBean对象

     • query的参数:RowMapper

       • 一般我们使用BeanPropertyRowMapper实现类,可以完成数据到JavaBean的自动封装
       • new BeanPropertyRowMapper<泛型>(类型.class)

   • queryForObject:查询结果,将结果封装为对象

     • 一般用于聚合函数的查询

4. 练习

   • 需求

     1. 修改1号数据的balance为3500
     2. 添加一条记录
     3. 删除刚才添加的记录
     4. 查询id为1的记录,将其封装为Map集合
     5. 查询所有记录,将其封装为List
     6. 查询所有记录,将其封装为Emp对象的List集合
     7. 查询总记录数

练习实现

/**
 * @author YQHP-YuKi
 * @create 2021-12-01 8:45
 */
public class Demo2 {

  //Junit单元测试,可以让方法独立的执行

  private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

  /**
   * 1.修改1号数据的balance为3500
   */
  @Test
  public void test1() {
    //定义sql
    String sql = "update account set balance = 3500 where name = 'YuKi'";
    //执行sql
    int count = template.update(sql);
    System.out.println(count);
  }

  /**
   * 2.添加一条记录
   */
  @Test
  public void test2() {
    String sql = "insert into account(id,name,balance) values(?,?,?)";
    int count = template.update(sql, 4, "XP", 10000);
    System.out.println(count);
  }

  /**
   * 3.删除刚才添加的记录
   */
  @Test
  public void test3() {
    String sql = "delete from account where id = ?";
    int count = template.update(sql, 4);
    System.out.println(count);
  }

  /**
   * 4.查询id为1的记录,将其封装为Map集合 注意:这个方法查询的结果集长度只能是1,将列名作为key,将值作为value,将这条记录封装为一个map集合
   */
  @Test
  public void test4() {
    String sql = "select * from account where id = ?";
    Map<String, Object> map = template.queryForMap(sql, 1);
    //{id=1, name=YuKi, balance=3500}
    System.out.println(map);
  }

  /**
   * 5.查询所有记录,将其封装为list
   */
  @Test
  public void test5() {
    String sql = "select * from account";
    List<Map<String, Object>> list = template.queryForList(sql);
    for (Map<String, Object> stringObjectMap : list) {
      System.out.println(stringObjectMap);
    }
  }

  /**
   * 6.查询所有记录,将其封装为Account对象的List集合
   */
  @Test
  public void test6() {
    String sql = "select * from account";
    List<Account> list = template.query(sql, new BeanPropertyRowMapper<Account>(Account.class));
    for (Account account : list) {
      System.out.println(account);
    }
  }

  /**
   * 7.查询总的记录数
   */
  @Test
  public void test7() {
    String sql = "select count(*) from account";
    Long aLong = template.queryForObject(sql, Long.class);
    System.out.println(aLong);
  }
}