JDBC相关内容

13

1.思考

当下我们如何操作数据库？

  • 使用客户端工具访问数据库，手工建立连接，输入用户名和密码登录。
  • 编写SQL语句，点击执行，查看操作结果(结果集或受影响行数)。

实际开发中，会采用上述方式吗？

  • 不会，因为操作量过大，无法保证效率和正确性。
  • 普通用户不能直接操作客户端工具。

JDBC

概念：

  • JDBC (Java Database Connectivity)，SUN公司定义的一套连接数据库的规范(标准)。
  • 使用JDBC连接数据库完成CRUD操作。

核心思想：

  • Java中定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式。
  • 由数据库厂商提供驱动实现类(Driver数据库驱动)。

JDBC API：

[wppay]

JDBC开发步骤

1.导入驱动包步骤 创建文件夹lib 复制数据库驱动包 右击add as library

url:(Unifrom Resource locator 统一资源定位符)连接字符串

DriverManager类

概念：

java.sql.DriverManager管理所有数据库的驱动注册，如果想要建立数据库连接需要先在java.sql.DriverManager中注册registerDriver对应的驱动类，然后调用getConnection方法才能连接上数据库。

注册驱动:本质这条语句是加载Driver类，加载类时会注册驱动。JDK1.6后支持自动注册。

Class.forName("com.mysql.jdbc.Driver");

底层实际调用：

 //从JDK1.6开始会自动注册
Class.forName("com.mysql.jdbc.Driver");
 //为什么不这样写：(1).耦合性高，依赖驱动包 (2)会注册两次
//DriverManager.registerDriver(new Driver());

Connection

概念：

Connection接口代表与特定的数据库的连接，要对数据表中的数据进行操作,首先要获取数据库连接，Connection实现就像在应用程序中与数据库之间开通了一条渠道。

获取连接

String url = "jdbc:mysql://localhost:3306/companyDB";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
//参数1：userSSL = false;禁止安全连接
//参数2  characterEncoding=utf8(utf-8);使用编码
//参数3  serverTimezone=GMT%2B8; 服务时区 GMT+8
//注意：连接字符和参数之间用？隔开，每个参数用&隔开
String url = "jdbc:mysql://localhost:3306/companydb?userSSL=false&characterEncoding=utf8";
public class JDBCDemo3 {
    public static void main(String[] args) throws Exception {
        //1. 注册驱动(加载Driver类,执行静态代码块，注册驱动)
        //从JDK1.6开始会自动注册
        Class.forName("com.mysql.jdbc.Driver");
        //为什么不这样写：(1).耦合性高，依赖驱动包 (2)会注册两次
        //DriverManager.registerDriver(new Driver());
        //2.获取连接
        //参数1：userSSL = false;禁止安全连接
        //参数2  characterEncoding=utf8(utf-8);使用编码
        //参数3  serverTimezone=GMT%2B8; 服务时区 GMT+8
        //注意：连接字符和参数之间用？隔开，每个参数用&隔开
        String url = "jdbc:mysql://localhost:3306/companydb?useSSL=false&characterEncoding=utf8";
        Connection con = DriverManager.getConnection(url, "root", "123456");
        //3.创建命令对象
        Statement stat = con.createStatement();
        //4.执行命令
        //int update = stat.executeUpdate("insert into student(no,name,password,born) values (2,1,1,'2020-1-1')");
        int update = stat.executeUpdate("delete from student where no = 2");
        //5.处理
        System.out.println(update);
        //6.关闭
        stat.close();
        con.close();
    }
}

Statement

概念：

  • 获取连接对象后，可以创建Statement对象，用来执行命令。
  • Statement stat = 连接对象.createStatement;

ResultSet

概念：在执行查询后，存放查询到的结果集数据。

接收结果集：

ResultSet query = statement.executeQuery(sql);

注意：作用就是完成了查询结果的存储功能，而且只能读取一次，不能够来回的滚动读取。但是实际上的实现可以。

遍历ResultSet:

ResultSet以表结构进行临时结果的存储，通过JDBC API将其中数据进行一次获取。

有关时间的读取：

  • mysqlS数据库中的DateTime -> java.sql.Timestamp extends java.util.Date
  • mysqlS数据库中的Date -> java.sql.Date extends java.util.Date
  • mysqlS数据库中的Time -> java.sql.Time extends java.util.Date
  • mysqlS数据库中的Timestamp -> java.sql.Timestamp extends java.util.Date

经验：getXxx方法可以获取的类型有：基本数据类型,引用数据类型。

class JDBCDemo4 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/companydb?useSSL=false&characterEncoding=utf8",
            "root",
            "123456");
        Statement statement = con.createStatement();
        ResultSet query = statement.executeQuery("select * from student");
        //从前往后
        while (query.next()) {
            System.out.println(query.getInt("no"));
            System.out.println(query.getString("name"));
            System.out.println(query.getString("password"));
            //mysqlS数据库中的DateTime -> java.sql.Timestamp extends java.util.Date
            //mysqlS数据库中的Date -> java.sql.Date extends java.util.Date
            //mysqlS数据库中的Time -> java.sql.Time extends java.util.Date
            //mysqlS数据库中的Timestamp -> java.sql.Timestamp extends java.util.Date
            System.out.println(query.getTimestamp("born"));
        }
        //从后往前
        System.out.println("----------逆向----------");
        while (query.previous()){
            System.out.println(query.getInt("no"));
        }
        query.close();
        statement.close();
        con.close();
    }
}

常见错误

SQL注入

原因：

用户输入的数据中有SQL关键字或语句并且参与了SQL语句的编译，导致SQL语句编译后的条件含义为true，实现欺骗服务器，一直得到正确结果，这种现象称为SQL注入。

如何避免：

不要使用SQL拼接方式，SQL语句要在用户输入数据钱就已编译成完整的SQL语句，在进行填充数据。

复现：

public class Login {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请刷入用户名：");
        //输入：asdad' or 1=1;#
        String name = scanner.nextLine();
        System.out.println("请刷入密码：");
        String password = scanner.nextLine();
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/companydb?useSSL=false&characterEncoding=utf8",
            "root",
            "123456");
        Statement statement = con.createStatement();
        ResultSet query = statement.executeQuery("select * from user " +
            "where username = '" + name + "' and password='" + password + "'");
        if (query.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        query.close();
        statement.close();
        con.close();
    }
}

避免：

public class Login2 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请刷入用户名：");
        //asdad' or 1=1;#
        String name = scanner.nextLine();
        System.out.println("请刷入密码：");
        String password = scanner.nextLine();
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/companydb?useSSL=false&characterEncoding=utf8",
            "root",
            "123456");
        String sql = "select * from user where username = ? and password=? ";
        PreparedStatement statement = con.prepareStatement(sql);
        //把一些特殊的符号转义
        statement.setString(1, name);
        statement.setString(2, password);
        ResultSet query = statement.executeQuery();
        if (query.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        query.close();
        statement.close();
        con.close();
    }
}

PreparedStatement

概念：

PreparedStatement继承了Statement接口，执行SQL语句的方法无异。

作用：

  • 预编译SQL语句，效率高。
  • 安全，避免SQL注入。
  • 可以动态的填充数据，执行多个同构的SQL语句。

应用：

预编译SQL语句，参数使用？占位：

PreparedStatement pre = con.prepareStatement(sql);

为参数下标赋值：

pre.setString(1, "123");
pre.setString(2, "test3");
pre.setInt(3, 1);

注意：

  • JDBC中的所有参数都由？符号占位，被称为参数标记。
  • 在执行SQL语句之前，必须为每个参数提供值。

封装工具类

背景：

  • 在JDBC的使用中，连接数据库、关闭连接等存在着大量的重复代码。
  • 把传统的JDBC代码进行重构，抽取出通用的JDBC工具类。

重用性方案：

背景：

  • 重用性方案中的参数都是硬编码，当驱动、URL等参数需要更换时，需要重新编译。
  • 通过配置文件读取配置信息，使用软编码方式，更灵活的方案。

跨平台方案：

  • 创建properties配置文件。
  • 创建Properties集合:
    • public static final Properties prop = new Properties);
  • 静态代码块中，使用输入流，读取配置文件。
//封装工具类
public class DbUtils {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    //(1).注册驱动,放在静态代码块中
    static {
        try {
            Properties properties = new Properties();
            InputStream stream = DbUtils8.class.getClassLoader().getResourceAsStream("db.properties");
            properties.load(stream);
            stream.close();
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            System.out.println("注册驱动失败!");
            e.printStackTrace();
        }
    }

    //(2).获取连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //(3).释放资源
    public static void closeAll(ResultSet rs, Statement stat, Connection coon) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stat != null) {
                stat.close();
            }
            if (coon != null) {
                coon.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student2?useSSL=false&characterEncoding=utf8
user=root
password=123456

ORM

概念：

  • Object Relational Mapping:对象关系映射。
  • 对结果集(ResultSet)遍历时，取出的都是零散的数据。
  • 在实际应用开发中，我们需要将零散的数据进行封装整理。

将数据以对象集合的方式进行传递，用实体类去封装数据。

实体类(Entity)：

  • 一行数据中，多个零散的数据进行整理。
  • 通过entity的规则对表中的数据进行对象的封装。
  • 表名=类名;列名=属性名;提供各个属性的get、set方法。
  • 提供无参构造方法(视情况添加有参构造)。
  • 包的命名: beans、entity、domain、pojo

实体类与表对应：

  • 属性=列名。
  • 属性类型=列的类型。
  • 提供构造方法、get/set方法。

Student类：

public class Student {
    private int stuNo;
    private String name;
    private Date bornDate;
    ......
}

测试添加操作：

    public static void main(String[] args) throws Exception{
        //添加学生
        //1准备数据
        Scanner input=new Scanner(System.in);
        System.out.println("请输入学生姓名");
        String name=input.next();
        System.out.println("请输入学生出生日期");
        String bornDate=input.next();
       //2获取连接
       Connection conn = DbUtils.getConnection();
       //3创建命令对象
       PreparedStatement pstat=conn.prepareStatement("insert into student(name,password,age,sex,bornDate,address,phone) values(?,?,?,?,?,?,?);");
       //4执行命令
       //4.1参数赋值
       pstat.setString(1, name);
       SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
       pstat.setObject(5, sdf.parse(bornDate));
       //4.2执行
       int count=pstat.executeUpdate();
       //5处理
       if(count>0){
           System.out.println("添加成功");
       }
       //6关闭
       DbUtils.closeAll(null, pstat, conn);
    }

测试查询操作：

  public static List<Student> selectAll() throws Exception {
    Connection conn = DbUtils.getConnection();
    PreparedStatement pre = conn.prepareStatement("select * from student");
    ResultSet rs = pre.executeQuery();
    ArrayList<Student> students = new ArrayList<>();
    while (rs.next()) {
      Integer stuNo = rs.getInt("stuNo");
      String name = rs.getString("name");
      Date bornDate = rs.getDate("bornDate");
      students.add(new Student(stuNo, name, bornDate));
    }
    DbUtils.closeAll(rs,pre,conn);
    return students;
  }

DAO

概念：

  • Data Access Object:数据访问对象。
  • DAO实现了业务逻辑与数据库访问相分离。
  • 对同一张表的所有操作封装在XxxDaolmpl对象中。
  • 根据增删改查提供具体的方法(insert、update、delete、select、selectAll)

Service

什么是业务？

  • 代表用户完成的一个业务功能，可以由一个或多个DAO的调用组成。
  • 软件所提供的一个功能叫业务。

复用：

  • 数据操作的复用:使用DAO。
  • 业务功能的复用:使用Service。

事务

概念：

  • 事务是一个原子操作，由一个或多个SQL语句组成。
  • 在同一个事务当中，所有的SQL语句要么全部执行成功，要么全部失败。

事务的边界：

  • 在JDBC中，先获得Connection对象。
  • 开始事务:conn.setAutoCommit(false);
  • 手动提交事务:conn.commit(;
  • 手动回滚事务:conn.rollback();
  • 在Service中，调用了多次DAO操作，每一个业务功能都要控制事务。

问题：

  • 当程序发生异常，事务回滚并没有成功。

原因：

  • Service控制事务和DAO访问数据库时的连接对象，并非同一个。

在一个业务流程中，要保证多个数据库访问操作的连接对象是同一个。

连接对象同步方案1：

  • 将Connection对象通过Service获取并传递给各个DAO方法。

传递的问题：

  • 如果使用传递Connection，容易造成接口污染。
  • 定义接口是为了更容易更换实现，而将Connection定义在接口中，会污染当前接口。

连接对象同步方案2：

  • 可以将整个线程中(单线程)中，存储一个共享值。
  • 线程拥有一个类似Map的属性，键值对结构<ThreadLocal对象，值>。

ThreadLocal

  • 每一个线程通过ThreadLocal绑定一个连接对象。
  • 在整个流程中任一环节可以存值或取值。

ThreadLocal应用流程：

ThreadLocal应用：

  • 在释放资源的方法中，连接对象关闭后，提供threadLocal.remove()
  • 将关闭后的连接从当前ThreadLocal中移除。

现有问题：

  • 事务的开启，提交，回滚的代码都写在了Service中
  • Service的主要职责是模拟现实业务功能，要将事务的控制封装在工具类中。

工具类封装事务控制方法：

  • 开启事务 begin()
  • 提交事务 commit()
  • 回滚事务 rollback()
  • 关闭连接
    • 关闭前解除绑定连接
    • close()

DbUtils

//封装工具类
//(1) 注册驱动
//(2) 获取连接
//(3) 释放资源
public class DbUtils {

  private static String driver;
  private static String url;
  private static String user;
  private static String password;
  //创建线程本地变量
  public static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

  //(1) 注册驱动,放在静态代码块中
  static {
    try {
      Properties properties = new Properties();
      InputStream is = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
      properties.load(is);
      is.close();
      driver = properties.getProperty("driver");
      url = properties.getProperty("url");
      user = properties.getProperty("user");
      password = properties.getProperty("password");

      Class.forName(driver);
    } catch (Exception e) {
      System.out.println("注册驱动失败..." + e.getMessage());
    }
  }

  //(2) 获取连接
  public static Connection getConnection() {
    try {
      Connection conn = threadLocal.get();
      //第一次访问
      if (conn == null) {
        conn = DriverManager.getConnection(url, user, password);
        //连接绑定到线程上
        threadLocal.set(conn);
        System.out.println("线程绑定上一个连接:" + conn.hashCode());
      }
      return conn;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }

  //(3) 释放资源
  public static void closeAll(ResultSet rs, Statement stat, Connection conn) {
    try {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        //判断有没有开启事务
        //如果为true没有开启事务
        if (conn.getAutoCommit()) {
          conn.close();
          //解除绑定
          threadLocal.remove();
          System.out.println("没有开启事务关闭连接:" + conn.hashCode());
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  //添加四个与事务有关的方法
  //1 开启事务
  public static void beginTransaction() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.setAutoCommit(false);
    }
  }


  //2 提交事务
  public static void commit() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.commit();
    }
  }

  //3 回滚事务
  public static void rollback() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.rollback();
    }
  }

  //4 事务关闭连接
  public static void close() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.close();
      //remove();解除绑定
      threadLocal.remove();
      System.out.println("开启事务关闭连接:" + conn.hashCode());
    }
  }
}

Service转账事务代码(Dao层不变)：

  @Override
  public boolean transMoney(String fromCardNo, String toCardNo, BigDecimal m) {
    try {
      //开启事务
      DbUtils.beginTransaction();
      AccountDao accountDao = new AccountDaoImpl();
      //校验
      //转入账户和转出账户是否相同
      if (fromCardNo == toCardNo) {
        throw new RuntimeException("转入账户和转出账户不能相同");
      }
      //验证对方账户是否存在
      boolean b = accountDao.selectByCardNo(toCardNo);
      if (!b) {
        throw new RuntimeException("对方账户不存在");
      }
      //验证余额是否大于等于转账金额
      BigDecimal money = accountDao.selectMoney(fromCardNo);
      if (money.compareTo(m) < 0) {
        throw new RuntimeException("余额不足");
      }
      //1 转出账户扣钱
      accountDao.updateTake(fromCardNo, m);
      //int i=10/0;
      //2 转入账户加钱
      accountDao.updateSave(toCardNo, m);
      //提交
      DbUtils.commit();
      return true;
    } catch (Exception e) {
      //回滚
      try {
        DbUtils.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      throw new RuntimeException(e);
    } finally {
      /* 关闭资源 */
      try {
        DbUtils.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

ThreadLocal线程本地变量源码：

在线程上有一个集合，这个集合叫ThreadLocalMap。每一个线程都有一个集合，存放与线程相关的信息。

    public void set(T value) {
        Thread t = Thread.currentThread();
        ThreadLocalMap map = getMap(t);
        if (map != null)
            map.set(this, value);
        else
            createMap(t, value);
    }
    ThreadLocalMap getMap(Thread t) {
        return t.threadLocals;
    }

通过getMap()方法可以看到再Thread类里面本身就定义了ThreadLocalMap，如下：

    /* ThreadLocal values pertaining to this thread. This map is maintained
     * by the ThreadLocal class. */
    ThreadLocal.ThreadLocalMap threadLocals = null;

但是Thread里面的ThreadLocalMap又是调用的ThreadLocal类里面的，从ThreadLocal里面可以看到ThreadLocalMap是一个静态内部类。

        static class Entry extends WeakReference<ThreadLocal<?>> {
            /** The value associated with this ThreadLocal. */
            Object value;

            Entry(ThreadLocal<?> k, Object v) {
                super(k);
                value = v;
            }
        }

ThreadLocal作为K，Connection作为value。

可以看到键值对对象继承弱引用，可以避免内存泄漏。

三层架构

现有问题：

  • 在之前学习过程中，通过DAO与Service对象实现数据访问与业务逻辑的分离。
  • 面向实现类编程:层与层之间的耦合度高、不易修改、扩展、更换实现。

程序设计时，要考虑易修改、易扩展。

为Service层和DAO层设计接口，便于未来更换实现类。

什么是三层：

  • 界面层(User Interface layer)
  • 业务逻辑层(Business Logic Layer)
  • 数据访问层(Data Access layer)

分层次的目的即为了“高内聚低耦合”的思想。

优化DbUtils

  • 现有问题:
    • 在DAO层中，对数据库表的增、删、改、查操作存在代码冗余。
    • 可对其进行抽取封装Dbutils工具类实现复用。
  • 增删改方法:
    • 相同:获取连接、预编译SQL、占位符赋值、执行并返回结果。
    • 不同:sal语句不同、参数列表不同。
  • 将DAO中增删改的数据库访问操作进行封装。
  //执行增删改的方法
  // sql:执行的sql语句
  // params:sql语句的参数
  public static int executeUpdate(String sql, Object... params) {
    Connection conn = getConnection();
    PreparedStatement pstat = null;
    try {
      pstat = conn.prepareStatement(sql);//insert ???  update ???  delete ?
      if (params != null && params.length > 0) {
        for (int i = 0; i < params.length; i++) {
          pstat.setObject(i + 1, params[i]);
        }
      }
      return pstat.executeUpdate();
    } catch (Exception e) {
      throw new RuntimeException(e);
    } finally {
      DbUtils.closeAll(null, pstat, conn);
    }
  }
    @Override
    public void insert(Book book) {
        String sql = "insert into book(title,author,publishDate,publisher,isbn,price,picture,cid) values(?,?,?,?,?,?,?,?);";
        Object[] params = {book.getTitle(), book.getAuthor(), book.getPublishDate(), book.getPublisher(), book.getIsbn(), book.getPrice(), book.getPicture(), book.getCid()};
        DbUtils.executeUpdate(sql, params);
    }
  • 查询方法:
    • 相同:获取连接、预编译SQL、占位符赋值、执行并返回结果。
    • 不同: sql语句不同、参数列表不同、返回的结果不同(单个对象或集合、对象类型)。
  • 封装问题:
    • 方法返回值类型。
    • 方法的参数列表。
    • 如何封装对象。
    • 方法中如何返回单个对象或多个对象。
  • 工具类无法同时完成执行查询、封装对象、返回结果的操作。
  //封装通用的查询方法（内省技术: PropertyDescriptor  BeanInfo  Instrospector）
  //泛型：泛型类   泛型方法  泛型接口
  public static <T> List<T> selectList(String sql, Class<T> clazz, Object... params) {
    Connection conn = getConnection();
    PreparedStatement pstat = null;
    ResultSet rs = null;
    try {
      List<T> list = new ArrayList<>();
      pstat = conn.prepareStatement(sql);
      if (params != null && params.length > 0) {
        for (int i = 0; i < params.length; i++) {
          pstat.setObject(i + 1, params[i]);
        }
      }
      rs = pstat.executeQuery();
      //获取结果集的标题
      ResultSetMetaData metaData = rs.getMetaData();
      while (rs.next()) {
        //使用类对象创建对象
        T t = clazz.newInstance();
        //属性赋值
        for (int i = 0; i < metaData.getColumnCount(); i++) {
          String columnLabel = metaData
              .getColumnLabel(i + 1);//id   title  author  publishDate  publisher
          try {
            //当表类字段名称不一样存在问题，try-catch一下：
            PropertyDescriptor pd = new PropertyDescriptor(columnLabel, clazz);
            Method writeMethod = pd.getWriteMethod(); // setId  setTitle setAuthor setPublishDate
            writeMethod.invoke(t, rs.getObject(columnLabel));
          } catch (Exception e) {
            continue;
          }
        }
        list.add(t);
      }
      return list;
    } catch (Exception e) {
      throw new RuntimeException(e);
    } finally {
      DbUtils.closeAll(rs, pstat, conn);
    }
  }
  @Override
  public Book selectById(int bookId) {
    String sql = "select id,title,author,publishDate,publisher,isbn,price,picture,cid from book where id=?";
    List<Book> books = DbUtils.selectList(sql, Book.class, bookId);
    if (books != null && books.size() > 0) {
      return books.get(0);
    }
    return null;
  }

其中可能存在的问题：字段名和表名不一致。解决方式如下：

  • 1.修改类名与表名一致
  • 2.数据库里面不区分大小写，Java中会区分，在写sql的时候可以这样修改。
    • realname as realName
    • 直接写类名中的realName

数据库连接池

  • 现有问题:
    • 数据库连接是—种关键的、有限的、昂贵的资源。
    • 频繁的创建、关闭会影响程序的性能。
  • 连接池:
    • 数据库连接池负责分配、管理和释放数据库连接。
    • 在程序初始化时，预先创建指定数量的数据库连接对象存储在池中。
    • 避免频繁的创建、关闭。
  • 用户需要访问数据库时,从连接池中取出—个已建立的空闲连接对象。
  • 使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中。
  • 连接的建立、断开都由连接池自身来管理。

JDBC中提供了一个接口DataSource所有的连接池都必须实现这个接口。

自定义连接池：

public class MyDbPool implements DataSource {

  //创建一个集合
  private static CopyOnWriteArrayList<Connection> arrayList = new CopyOnWriteArrayList<>();

  static {
    try {
      Properties properties = new Properties();
      InputStream is = MyDbPool.class.getClassLoader().getResourceAsStream("db.properties");
      properties.load(is);
      is.close();
      String url = properties.getProperty("url");
      String user = properties.getProperty("user");
      String password = properties.getProperty("password");
      for (int i = 0; i < 5; i++) {
        Connection connection = DriverManager.getConnection(url, user, password);
        arrayList.add(connection);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  @Override
  public Connection getConnection() throws SQLException {
    if (arrayList.size() > 0) {
      return arrayList.remove(0);
    }
    return null;
  }

  //归还连接
  public void release(Connection conn) {
    arrayList.add(conn);
  }
//其他实现的方法可以暂不实现，主要放在就这两个
}

测试自定义连接池：

public class TestMyPool {

  public static void main(String[] args) throws Exception {
    MyDbPool pool = new MyDbPool();
    for (int i = 0; i < 100; i++) {
      Connection connection = pool.getConnection();
      if (connection != null) {
        System.out.println(i + "获取了" + connection.hashCode());
        //归还
        pool.release(connection);
      } else {
        System.out.println(i + "没获取到连接");
      }
    }
  }
}

常见连接池

  • DBCP
  • C3P0
  • Druid

Druid连接池:

  • 支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、SQL Server等。
  • 简单SQL语句用时10微秒以内，复杂SQL用时30微秒。

应用:

  • src下创建database.properties配置文件。
  • 引入druid-1.1.5.jar文件。

实现步骤:

  • 声明连接池对象:
    • private static Druid DataSource ds;。
  • 创建连接池:
    • ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);

创建Druid连接池方式一：

public class TestDruid {

  public static void main(String[] args) throws Exception {
    //创建连接池
    DruidDataSource dataSource = new DruidDataSource();
    //4个基本配置
    //driver
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    //url
    dataSource.setUrl("jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf-8");
    //user
    dataSource.setUsername("root");
    //password
    dataSource.setPassword("root");
    //4个可选配置
    //初始大小
    dataSource.setInitialSize(10);
    //最大大小
    dataSource.setMaxActive(50);
    //最小空闲个数
    dataSource.setMinIdle(5);
    //最大等待时间
    dataSource.setMaxWait(5000);
    for (int i = 0; i < 100; i++) {
      DruidPooledConnection connection = dataSource.getConnection();
      System.out.println(i + "..." + connection.toString());
      //归还(close不是关闭连接，放回连接池)
      // connection.close();
    }
  }
}

创建Druid连接池方式二：

druid.properties

druid.driverClassName=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf-8
druid.username=root
druid.password=root
druid.initialSize=10
druid.maxActive=50
druid.minIdle=5
druid.maxWait=5000

测试方法：

public class TestDruid {

  public static void main(String[] args) throws Exception {
    //创建连接池
    Properties properties = new Properties();
    InputStream is = TestDruid.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(is);
    is.close();
    //DruidDataSource dataSource=new DruidDataSource();
    //dataSource.configFromPropety(properties);
    DruidDataSource dataSource = (DruidDataSource) DruidDataSourceFactory
        .createDataSource(properties);

    for (int i = 0; i < 100; i++) {
      DruidPooledConnection connection = dataSource.getConnection();
      System.out.println(i + "..." + connection.toString());
      //归还(close不是关闭连接，放回连接池)
      // connection.close();
    }
  }
}

druid工具类

druid.driverClassName=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf-8
druid.username=root
druid.password=root
druid.initialSize=10
druid.maxActive=50
druid.minIdle=5
druid.maxWait=5000
//三个基本功能 （1）注册驱动  （2）获取连接  （3）释放资源
//四个事务相关方法
public class DbUtils_druid {

  //创建数据源对象
  private static DruidDataSource dataSource;
  //创建线程本地变量
  private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

  static {
    //1加载配置
    try {
      Properties properties = new Properties();
      InputStream is = DbUtils_druid.class.getClassLoader().getResourceAsStream("druid.properties");
      properties.load(is);
      is.close();
      dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
      System.out.println("初始化数据源失败:" + e.getMessage());
    }

  }

  public static Connection getConnection() {
    try {
      Connection conn = threadLocal.get();
      if (conn == null) {
        conn = dataSource.getConnection();
        threadLocal.set(conn);//线程绑定连接
      }
      return conn;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }

  public static void closeAll(ResultSet rs, Statement stat, Connection conn) {
    try {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        //判断是否开启事务
        if (conn.getAutoCommit()) {
          conn.close();
          threadLocal.remove();//线程解除绑定
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  //执行增删改的方法
  // sql:执行的sql语句
  // params:sql语句的参数
  public static int executeUpdate(String sql, Object... params) {
    Connection conn = getConnection();
    PreparedStatement pstat = null;
    try {
      pstat = conn.prepareStatement(sql);//insert ???  update ???  delete ?
      if (params != null && params.length > 0) {
        for (int i = 0; i < params.length; i++) {
          pstat.setObject(i + 1, params[i]);
        }
      }
      return pstat.executeUpdate();
    } catch (Exception e) {
      throw new RuntimeException(e);
    } finally {
      DbUtils_druid.closeAll(null, pstat, conn);
    }
  }

  //封装通用的查询方法（内省技术: PropertyDescriptor  BeanInfo  Instrospector）
  //泛型：泛型类   泛型方法  泛型接口
  public static <T> List<T> selectList(String sql, Class<T> clazz, Object... params) {
    Connection conn = getConnection();
    PreparedStatement pstat = null;
    ResultSet rs = null;
    try {
      List<T> list = new ArrayList<>();
      pstat = conn.prepareStatement(sql);
      if (params != null && params.length > 0) {
        for (int i = 0; i < params.length; i++) {
          pstat.setObject(i + 1, params[i]);
        }
      }
      rs = pstat.executeQuery();
      //获取结果集的标题
      ResultSetMetaData metaData = rs.getMetaData();
      while (rs.next()) {
        //使用类对象创建对象
        T t = clazz.newInstance();
        //属性赋值
        for (int i = 0; i < metaData.getColumnCount(); i++) {
          String columnLabel = metaData
              .getColumnLabel(i + 1);//id   title  author  publishDate  publisher
          try {
            PropertyDescriptor pd = new PropertyDescriptor(columnLabel, clazz);
            Method writeMethod = pd.getWriteMethod(); // setId  setTitle setAuthor setPublishDate
            writeMethod.invoke(t, rs.getObject(columnLabel));
          } catch (Exception e) {
            continue;
          }
        }
        list.add(t);
      }
      return list;
    } catch (Exception e) {
      throw new RuntimeException(e);
    } finally {
      DbUtils_druid.closeAll(rs, pstat, conn);
    }
  }

  //四个与事务有关的方法
  public static void begin() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.setAutoCommit(false);
    }
  }

  public static void commit() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.commit();
    }
  }

  public static void rollback() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.rollback();
    }
  }

  public static void close() throws SQLException {
    Connection conn = getConnection();
    if (conn != null) {
      conn.close();
      threadLocal.remove();//线程解除绑定
    }
  }
}
  @Override
  public List<Book> selectAll() {
    String sql = "select id,title,author,publishDate,publisher,isbn,price,picture,cid from book";
    return DbUtils_druid.selectList(sql, Book.class);
  }

Commons DbUtils

  • 概念:
    • Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库。
    • 使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。
  • 作用:
    • 小巧、简单、实用。
    • 对于数据表的查询操作，可以把结果转换为List、Array.Set等集合。
    • 对于数据表的DML操作，只需要写SQL语句。
  • 核心内容:
    • DbUtils类:启动类。
    • ResultsSetHandler接口:转换类型接口。
  • QueryRunner类:执行SQL语句的类。
    • 增、删、改: update();
    • 查:query();
  • 使用DbUtils:
    • 引入commons-dbutils-1.6.jar。
    • 引入commons-logging.jar

配置文件：

#四个基本参数
druid.driverClassName=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf-8
druid.username=root
druid.password=root
#四个可选参数
druid.initialSize=10
druid.maxActive=50
druid.minIdle=5
druid.maxWait=5000

连接池创建：

public class DataSourceUtils {
    private static DruidDataSource dataSource=null;
    static {
        try {
            Properties properties=new Properties();
            InputStream is = DataSourceUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            is.close();
            dataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            System.out.println("初始数据源失败");
        }
    }
    public  static DataSource getDataSource(){
        return dataSource;
    }
}

DaoImpl使用：

public class BookDaoImpl implements BookDao {
    private QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
    @Override
    public List<Book> selectAll() {
        try {
            return qr.query("select * from book", new BeanListHandler<>(Book.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public Book selectById(int bookId) {
        try {
            return qr.query("select * from book where id=?", new BeanHandler<>(Book.class),bookId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public long selectCount() {
        try {
            return qr.query("select count(*) from book", new ScalarHandler<>());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public void delete(int bookId) {
        try {
            qr.update("delete from book where id=?",bookId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public List<Book> selectByTitle(String title) {
        try {
            return qr.query("select * from book where title like ?", new BeanListHandler<>(Book.class),"%"+title+"%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

[/wppay]