Java学习之==>JDBC

一、概述

官方解释：

　　JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的 Java API，可以为多种关系型数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是个商标名。

简单地说，JDBC 可做三件事：与数据库建立连接、发送 操作数据库的语句并处理结果。

理解：

　　JDBC就是一套访问数据库的规范（或者说是一组接口），提供数据库的协议标准，各个数据库厂商根据这套规范提供一套访问自己数据库的驱动。

二、JDBC工作原理

从图中可以看到 JDBC 的几个重要组成要素。最顶层是我们自己编写的Java应用程序（Java Application），Java 应用程序可以使用集成在JDK中的 java.sql 和 javax.sql 包中的JDBC API来连接和操作数据库。下面我们依次介绍JDBC的组成要素：

1、JDBC API

　　JDBC API 由Sun公司提供，提供了Java 应用程序与各种不同数据库交互的标准接口，如：Connection（连接）接口，Statement 接口，ResultSet（结果集）接口，PreparedStatement 接口等。开发者使用这些JDBC接口进行各种数据库操作。

• DriverManager：此类管理数据库驱动程序列表。使用通信子协议将来自 Java 应用程序的连接请求与适当的数据库驱动程序匹配；
• Driver：此接口处理与数据库服务器的通信，我们很少会直接与Driver对象进行交互。而是使用DriverManager对象来管理这种类型的对象；
• Connection：该接口具有用于联系数据库的所有方法。连接对象通信上下文，即，与数据库的所有通信仅通过连接对象；
• Statement：使用从此接口创建的对象将SQL语句提交到数据库。除了执行存储过程之外，一些派生接口还接受参数；
• ResultSet： 在使用 Statement 对象执行 SQL查询后，这些对象保存从数据库检索的数据。它作为一个迭代器，允许我们移动其数据；
• PreparedStatement：继承自 Statement 接口，主要用于执行预编译 SQL，除了具有 Statement 接口的所有功能，还另外它还添加了一整套方法，用于设置发送给数据库以取代 IN 参数占位符的值；

2、JDBC Driver Manager

　　JDBC Driver Manager（驱动程序管理器）由Sun公司提供，它是 JDBC 体系结构的支柱，负责管理各种不同的 JDBC 驱动，把 Java 应用程序连接到相应的 JDBC 驱动程序上，位于JDK的 java.sql 包中。

3、JDBC Driver

　　JDBC Driver 由各个数据库厂商或第三方中间件厂商提供，负责连接各种不同的数据库。例如，上图中，访问 MySQL 和 Oracle 时需要不同的JDBC驱动，这些 JDBC 驱动都实现了JDBC API 中定义的各种接口。在开发Java应用程序时，我们只需正确加载JDBC驱动，正确调用 JDBC API，就可以进行数据库访问了。

三、JDBC 访问数据库的步骤

我们先来看一下下面这段代码：

public class App {
  private static final String URL = "jdbc:mysql://192.168.182.131:3306/cakes";
  private static final String USER_NAME = "root";
  private static final String PASSWD = "123456";

  public static void main(String[] args) throws Exception {

    // 1.加载驱动
    Class.forName("com.mysql.jdbc.Driver");

    // 2.建立连接
    Connection connection = DriverManager.getConnection(URL, USER_NAME, PASSWD);

    // 3.建立Statement
    Statement statement = connection.createStatement();

    // 4.执行sql
    boolean execResult = statement.execute("insert into user(`name`,`passwd`) values('木木','123456')");
    // statement.executeUpdate返回的是影响的行数
    int effectRows = statement.executeUpdate("insert into user(`name`,`passwd`) values('大叔','888888')");

    System.out.println("execResult = " + execResult);
    System.out.println("effectRows:" + effectRows);

    // 5.资源关闭
    statement.close();
    connection.close();
  }
}

执行结果如下：

以上红色字体为警告信息，意思是：com.mysql.jdbc.Driver 这个驱动已经被弃用了，建议使用 com.mysql.cj.jdbc.Driver 这个驱动，更改后再次运行则没有这个警告信息了。

所以标准代码如下：

public class App {
  private static final String URL = "jdbc:mysql://192.168.182.131:3306/cakes";
  private static final String USER_NAME = "root";
  private static final String PASSWD = "123456";

  public static void main(String[] args) throws Exception {

    // 1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");

    // 2.建立连接
    Connection connection = DriverManager.getConnection(URL, USER_NAME, PASSWD);

    // 3.建立Statement
    Statement statement = connection.createStatement();

    // 4.执行sql
    boolean execResult = statement.execute("insert into user(`name`,`passwd`) values('木木','123456')");
    // statement.executeUpdate返回的是影响的行数
    int effectRows = statement.executeUpdate("insert into user(`name`,`passwd`) values('大叔','888888')");

    System.out.println("execResult = " + execResult);
    System.out.println("effectRows:" + effectRows);

    // 5.资源关闭
    statement.close();
    connection.close();
  }
}

同时，因为我本地的 Gradle 工程引入的 mysql-connector-java 包的版本是 8.0.16，所以加载驱动那一步可以省略不写（5.x.x版本之后）。

JDBC访问数据库基本步骤：

1、加载驱动程序

2、建立连接

3、创建 Statement 对象

4、执行 SQL 语句

5、处理返回结果

6、关闭资源，包括：ResultSet，Statement 和 Connection

四、Statement 和 PreparedStatement

我们先来看两段代码：

public static void test01() {

  Connection connection = null;
  Statement statement = null;

  try {
    connection = DriverManager.getConnection(URL, USER_NAME, PASSWD);

    statement = connection.createStatement();

    String sql = "insert into user(`name`,`passwd`) values('木木','123456')";

    int executeNum = statement.executeUpdate(sql);
    System.out.println(String.format("成功插入%d条数据",executeNum));

  } catch (SQLException se) {
    se.printStackTrace();
  }finally {
    try {
      if (null != statement) {
        statement.close();
      }
      if (null != connection) {
        connection.close();
      }
    } catch (SQLException se) {
      se.printStackTrace();
    }
  }
}

public static void test02() {

  Connection connection = null;
  PreparedStatement preparedStatement = null;

  try {
    connection = DriverManager.getConnection(URL, USER_NAME, PASSWD);

    String sql = "insert into user(`name`,`passwd`) values(?, ?)";

    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1,"jack");
    preparedStatement.setString(2,"112233");

    int executeNum = preparedStatement.executeUpdate();
    System.out.println(String.format("成功插入%d条数据",executeNum));

  } catch (SQLException se) {
    se.printStackTrace();
  }finally {
    try {
      if (null != preparedStatement) {
        preparedStatement.close();
      }
      if (null != connection) {
        connection.close();
      }
    } catch (SQLException se) {
      se.printStackTrace();
    }
  }
}

PreparedStatment 接口继承自 Statement 接口，PreparedStatment 执行的是预编译 SQL，使用占位符，创建 PreparedStatment 对象时就会把 SQL语句作为参数参入。

通常情况下，我们更建议使用 PreparedStatment，原因如下：

1、提高了代码的可读性和可维护性

虽然使用 PreparedStatement 来代替 Statement 会多几行代码，但避免了烦琐麻烦又容易出错的SQL语句拼接，提高了代码的可读性和可维护性。

2、提高了SQL语句执行的性能

创建 Statement 对象时不使用SQL语句做参数，不会解析和编译SQL语句，每次调用方法执行SQL语句时都要进行SQL语句解析和编译操作，即操作相同仅仅是数据不同。

创建 PreparedStatement 对象时使用SQL语句做参数，会解析和编译该SQL语句，也可以使用带占位符的SQL语句做参数，在通过 setXxx ()方法给占位符赋值后执行SQL语句时无须再解析和编译SQL语句，直接执行即可。多次执行相同操作可以大大提高性能。

3、提高了安全性

PreparedStatement 使用预编译语句，传入的任何数据都不会和已经预编译的SQL语句进拼接，避免了SQL注入攻击。

五、ResultSet

使用 Statement 或 PreparedStatement 对象执行 SQL 查询后，会返回 ResultSet 结果集，作为一个迭代器，我们一般使用 next() 方法来判断把光标挪向下一行。

public class UserDemo3 {

  private static final String URL = "jdbc:mysql://192.168.182.131:3306/cakes";
  private static final String USER_NAME = "root";
  private static final String PASSWD = "123456";


  public static void main(String[] args) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = DriverManager.getConnection(URL, USER_NAME, PASSWD);
      String sql = "select * from user where name=?";

      preparedStatement = connection.prepareStatement(sql);
      preparedStatement.setString(1, "大叔");

      resultSet = preparedStatement.executeQuery();

      ArrayList<User> users = new ArrayList<>();

      while (resultSet.next()) {
        User user = User.of();
        user.setId(resultSet.getInt("id"));
        user.setName(resultSet.getString("name"));
        user.setPasswd(resultSet.getString("passwd"));

        users.add(user);
      }
      for (User user : users) {
        System.out.println(user);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (null != resultSet) {
          resultSet.close();
        }
        if (null != preparedStatement) {
          preparedStatement.close();
        }
        if (null != connection) {
          connection.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

六、事物操作

七、SQL 执行工具设计

下面我们使用 JDBC 简单设计一款 SQL 执行工具

public interface JdbcConsts {

  String URL = "jdbc:mysql://192.168.182.131:3306/cakes";

  String USER_NAME = "root";

  String PASSWD = "123456";

}

JdbcConsts 接口中存放的是数据库连接配置。

public final class ConnectionHandler {

  private ConnectionHandler() {
  }

  public static ConnectionHandler of() {
    return new ConnectionHandler();
  }

  public Connection getConnection() {
    try {
      return DriverManager.getConnection(URL, USER_NAME, PASSWD);
    } catch (SQLException e) {
      throw new IllegalStateException(e);
    }
  }
}

ConnectionHandler 类封装了数据库连接这个操作。

public abstract class AbstractDBHandler<T> {

  /**
   * @param sql SQL语句, update user set name=?,age=? where id=?
   * @param params [zhangsan,23,12]
   */
  public int modify(String sql, List<Object> params) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = ConnectionHandler.of().getConnection();

      // statement = connection.prepareStatement(sql);
      //
      // for (int i = 0; i < params.size(); i++) {
      //   statement.setObject(i + 1, params.get(i));
      // }
      statement = preparedStatement(sql,params,connection);

      int effectRows = statement.executeUpdate();

      System.out.println("effectRows = " + effectRows);

      return effectRows;
    } catch (SQLException e) {
      throw new IllegalStateException(e);
    } finally {
      release(null, statement, connection);
    }
  }

  public List<T> select(String sql, List<Object> params) {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      connection = ConnectionHandler.of().getConnection();

      // statement = connection.prepareStatement(sql);
      //
      // for (int i = 0; i < params.size(); i++) {
      //   statement.setObject(i + 1, params.get(i));
      // }
      statement = preparedStatement(sql,params,connection);

      resultSet = statement.executeQuery();

      List<T> result = new ArrayList<>();
      while (resultSet.next()) {
        // 抽象点，通过抽象方法来实现
        T t = mapping(resultSet);
        result.add(t);
      }
      return result;
    } catch (SQLException e) {
      throw new IllegalStateException(e);
    } finally {
      release(resultSet, statement, connection);
    }
  }

  private PreparedStatement preparedStatement(String sql,List<Object> params,
                                              Connection connection) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(sql);
    for (int i = 0; i < params.size(); i++) {
      statement.setObject(i + 1, params.get(i));
    }
    return statement;
  }

  protected abstract T mapping(ResultSet resultSet) throws SQLException;

  private void release(ResultSet resultSet, Statement statement, Connection connection) {
    try {
      if (null != resultSet) {
        resultSet.close();
      }
      if (null != statement) {
        statement.close();
      }
      if (null != connection) {
        connection.close();
      }
    } catch (SQLException e) {
      throw new IllegalStateException(e);
    }
  }
}

AbstractDBHandler 类中定了了 select() 和 modify() 两个方法，modify()用于执行增、删、改，select()用于执行查询。modify() 方法传入两个参数 sql 和 List<Object> params，具体执行的什么 SQL 和 该 SQL 应该传入什么值，全部在 Dao 中定义。select() 方法查询出的结果对于不同的表返回的是不一样的，所以，我们使用了一个抽象方法 mapping() 来抽象返回结果，具体我们查询的是哪张表，返回的是什么样的结果，全部在 Dao中定义，如下：

public class UserDao extends AbstractDBHandler<User> {

  @Override
  protected User mapping(ResultSet resultSet) throws SQLException {

    User user = User.of();
    user.setId(resultSet.getInt("id"));
    user.setName(resultSet.getString("name"));
    user.setPasswd(resultSet.getString("passwd"));

    return user;
  }

  public int addUser(User user) {
    String sql = "insert into user(`name`,`passwd`) values(?,?)";
    List<Object> params = new ArrayList<>();
    params.add(user.getName());
    params.add(user.getPasswd());
    return modify(sql, params);
  }

  public int delUser(User user) {
    String sql = "delete from user where name=? and passwd=?";
    List<Object> params = new ArrayList<>();
    params.add(user.getName());
    params.add(user.getPasswd());
    return modify(sql, params);
  }

  public List<User> queryByName(User user) {
    String sql = "select * from user where name=? and passwd=?";
    List<Object> params = new ArrayList<>();
    params.add(user.getName());
    params.add(user.getPasswd());
    return select(sql, params);
  }
}

UserDao 继承自 AbstractDBHandler 类，当中定义了关于 User 表的 增、删、改、查等操作。其中，增、删、改调用的是 modify() 方法而查询调用的是 select() 方法。同时也定义了 User 表查询结果由三个字段组成：id，name，passwd。

public class App {

  @Test
  public void testInsert() {
    UserDao userDao = new UserDao();

    User user = User.of("小芹", "s123456");

    userDao.addUser(user);
  }

  @Test
  public void testDelete() {
    UserDao userDao = new UserDao();

    User user = User.of("jack", "123456");

    userDao.delUser(user);
  }

  @Test
  public void testSelect() {
    UserDao userDao = new UserDao();

    User user = User.of("大叔", "111222");

    List<User> users = userDao.queryByName(user);

    System.out.println(users);
  }
}

测试类中只需要传入执行 增、删、改、查具体的数据就可以。

以上，只是对针对 JDBC 的操作进行了简单的设计和封装，还有很多地方可以优化，这里就不再深入讲解。