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();
      }
    }
  }
}

测试用例:直接省去getConnectionclose()

/**
 * 定义一个方法,查询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,所以都会查询出全部类容

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

    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. 释放资源
  4. 注意:后期都会使用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>

注意:一定要对应自己的driverClassjdbcUrl,很多时候显示连接错误都是这两个写错了造成的,还有就是在连接的时候也需要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);
  }
  1. 调用JDBCTemplate的方法来完成CURD的操作

    • update():执行DML语句,增删改语句
    • queryForMap():查询结果将结果集封装为map集合

      • 注意:这个方法查询的结果集长度只能是1
    • queryForList():查询结果将结果集封装为list集合

      • 注意:将每一条记录封装为一个Map集合,再将Map集合装载到List集合中
    • query():查询结果,将结果封装为JavaBean对象

      • query的参数:RowMapper

        • 一般我们使用BeanPropertyRowMapper实现类,可以完成数据到JavaBean的自动封装
        • new BeanPropertyRowMapper<泛型>(类型.class)
    • queryForObject:查询结果,将结果封装为对象

      • 一般用于聚合函数的查询
  2. 练习

    • 需求

      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);
  }
}
Last modification:December 14, 2021
If you think my article is useful to you, please feel free to appreciate