「JavaWeb学习笔记01」JDBC 编程

¶Chapter 1. JDBC

¶1.1 JDBC 概述

客户端操作 MySQL 数据库的方式：

1. 使用第三方客户端（如：SQLyog、Navicat 等）来访问；
2. 通过 MySQL 自带的命令行方式；
3. 通过 Java 来访问 MySQL 数据库

Java 数据库连接，Java Database Connectivity （简称 JDBC），是一套面向对象的应用程序接口，指定了统一的访问各种类型数据库的标准接口。

JDBC是一种底层的API（Application Programming Interface，应用程序接口），是连接数据库和Java应用程序的纽带，因此我们在访问数据库时需要在业务逻辑层中嵌入SQL语句。

SQL语句是面向关系的，依赖于关系模型，所以通过JDBC技术访问数据库也是面向关系的。

JDBC技术主要是完成以下几个任务：

1. 与数据建立一个链接；
2. 向数据库发送SQL语句；
3. 处理从数据库返回的结果。

JDBC 访问数据库的步骤：

1. 导入驱动 jar 包
2. 复制 jar 包到项目的 libs 目录下，右键 Add As Library
3. 注册驱动
4. 获取数据库连接对象 Connection
5. 定义 SQL 语句的字符串
6. 获取执行 SQL 语句的对象 Statement 或 PreparedStatement
7. 执行 SQL ，接受返回结果
8. 处理结果
9. 释放资源

¶1.2 导入、加载与注册驱动

需要注意的是，JDBC并不能直接访问我们的数据库，必须依赖各个数据库厂商提供的JDBC驱动程序（jar 包），我们可以使用这套接口（JDBC）编程，而真正执行代码是驱动 jar 包中的实现类。

使用 JDBC 开发使用到的包	说明
java.sql	所有与 JDBC 访问数据库相关的接口和类
javax.sql	数据库拓展包，提供数据库额外的功能。如：连接池
数据库的驱动	由各大数据库厂商提供，需额外去下载，是对 JDBC 接口实现的类

¶1.2.1 下载并导入驱动 jar 包

• jar 包下载戳这，选择 Platform Independent并选择版本。笔者这里选择了 5.1.37（笔者的 MySQL 版本为 5.7.30 ）。

  Connector / J版本	JDBC版本	MySQL 服务器版	JRE 支持	编译需要 JDK	状态
  8	4.2	5.6,5.7,8.0	1.8.x的	1.8.x的	一般可用性。推荐版本。
  5.1	3.0,4.0,4.1,4.2	5.6 *，5.7 *，8.0 *	1.5.x，1.6.x，1.7.x，1.8.x *	1.5.x和1.8.x.	一般可用性

• 将下载得到的压缩包中的 jar 包放到 项目里的一个目录中（一般命名为 lib），并右键该目录选择 Add as Library 。

  

¶1.2.2 加载和注册驱动

注册驱动：告诉程序该使用哪一个数据库驱动 jar

对应的方法：

Class.forName(数据库驱动实现类)
//加载和注册数据库驱动，MySQL的话是"com.mysql.jdbc.Driver"

com.mysql.jdbc.Driver 源代码：

public class Driver implements java.sql.Driver { //Driver接口，所有数据库厂商必须实现的接口
    public Driver() throws SQLException {
    } 
    static { 
        try { 
            DriverManager.registerDriver(new Driver()); //注册数据库驱动 
        } catch (SQLException var1) { 
            throw new RuntimeException("Can't register driver!"); 
        } 
    }
}

¶1.3 JDBC的核心API

接口或类	作用
DriverManager 类	① 管理和注册数据库驱动 ② 得到数据库连接对象
Connection 接口	一个连接对象，可用于创建 Statement 和 PreparedStatement 对象
Statement 接口	一个 SQL 语句 对象，用于将 SQL 语句发送数据库服务器
PreparedStatement 接口	一个 SQL语句 对象，是 Statement 的子接口
ResultSet 接口	用于封装数据库查询结果集，返回给客户端 Java 程序

¶1.3.1 DriverManager 类——驱动管理对象

功能：

（一）管理和注册驱动：见 1.2.2 节

（二）创建数据库连接

• （较常用的）类中的方法：

  static Connection getConnection(String url, String user, String password)

• 参数解释：

  • url：用于标识数据库的位置，程序员通过 url 地址告诉 JDBC 程序连接哪个数据库

    MySQL 写法如图：（注意，Java连接每一种数据库的写法是有所差异的）

    

    若数据库出现乱码：则可指定参数：?characterEncoding=utf8，标识让数据库以 UTF-8编码来处理数据

  • user、password：登陆的用户名、密码

¶1.3.2 Connection 接口——数据库连接对象

功能：

（一）管理事务：见下

（二）获取 执行 SQL 的对象，方法有：

• Statement createStatement()
• PreparedStatement prepareStatement(String sql)（注意，方法名不加 ed，返回对象名加 ed）

¶1.3.3 Statement接口——执行SQL的对象

作用：代表一条语句对象，用于发送 SQL 语句给服务器，用于执行静态 SQL 语句并返回它所生成结果的对象。

方法：

①：int executeUpdate(String sql) ：用于发送 DML 语句（INSERT、UPDATE、DELETE）、DDL语句（CREATE、ALTER、DROP）。

其返回值（int）代表影响的行数，通过该值我们可以判断 DML 语句是否执行成功——返回值大于零则说明执行成功；反之，则失败。

举例使用：

package JoyDee.LearningJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCdemo {
    public static void main(String[] args) {
        //1. 导入驱动jar包（已略）
        Statement stmt = null;
        Connection conn = null; //放外面，是为了使用finally子句
        try {
            //2. 注册驱动
            Class.forName("com.mysql.jdbc.Driver"); //反射技术：将字节码文件加载入内存
            //3. 获取数据库连接对象
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db1", "root", "root");
            //4. 获取执行SQL语句的实例对象
            stmt = conn.createStatement();
            //5. 定义SQL语句
            String sqlstr = "INSERT INTO stu VALUES (null, 'Nami', 'London')";
            //6. 执行SQL
            int cnt = stmt.executeUpdate(sqlstr);//将SQL语句发送到服务器并将其执行
            //7. 打印 影响的行数
            System.out.println(cnt);
            if(cnt > 0) System.out.println("添加成功!");
            else        System.out.println("添加失败!");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //为避免空指针异常
            if(stmt != null){
                try{
                    stmt.close(); //先释放stmt(源自conn)
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try{
                    conn.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

运行结果：

可以看到表中多了一条关于 Nami 的记录。

②：ResultSet executeQuery(String sql)：用于发送 DQL 语句（SELECT），返回结果集对象（见 下节）

¶1.3.4 ResultSet接口——结果集对象，封装查询结果

作用：封装数据库查询的结果集，对结果集进行遍历，取出每一条记录。

ResultSet 接口中的方法	描述
boolean next()（确定行）	① 使游标向下移动一行； ② 返回 boolean 类型，若还有下一条记录，则返回 true ，否则为 false
数据类型 getXxx()（确定列）	① 若列名查询，则参数是 String 类型，返回不同的类型； ② 若通过列号，则参数是整数，从 1 开始，返回不同类型

java.sql.Date、java.sql.Time、java.sql.Timestamp ，这三个的共同父类为 java.util.Date

方法ResultSet executeQuery(String sql)的使用举例：

可以注意到有大量的重复代码，在下几节中会讨论简化代码的方式（抽取并封装为一个工具类），这里仅仅是展示方法的用法。

package JoyDee.LearningJDBC;
import java.sql.*;

public class JDBCdemo {
    public static void main(String[] args) {
        //1. 导入驱动jar包（已略）
        Statement stmt = null;
        Connection conn = null;
        ResultSet res = null;
        try {
            //2. 注册驱动
            Class.forName("com.mysql.jdbc.Driver"); //反射技术：将字节码文件加载入内存
            //3. 获取数据库连接对象
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db1", "root", "root");
            //4. 定义SQL语句
            String sqlstr = "SELECT * FROM stu";
            //5. 获取执行SQL语句的实例对象
            stmt = conn.createStatement();
            //6. 执行SQL
            res = stmt.executeQuery(sqlstr);
            // 7. 处理结果
            if(res.next()){ //7.1 让游标下移一行时，一定要判断是否有数据
                //7.2 获取数据
                int id = res.getInt(1);
                String name = res.getString("name");
                System.out.println(id + ":" + name + "\n");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if(res != null){ //为避免空指针异常
                try{
                    res.close(); //先释放stmt(源自conn)
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(stmt != null){
                try{
                    stmt.close(); //先释放stmt(源自conn)
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try{
                    conn.close();
                } catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

运行结果：

¶1.3.5 PreparedStatement接口——执行SQL的对象

¶SQL注入问题

拼接SQL字符串会有一个安全漏洞，假如一个用户登录密码的项目中，用户在输入密码时可以使用恒等式诸如 a' OR 'a' = 'a，使得拼接后的SQL字符串为：

SELECT * FROM user WHERE username = 'xxxx' AND password = 'a' or 'a' = 'a'; //恒等式，无论用户名是什么都能成立

解决方案：使用 PreparedStatement 对象来解决上述问题。

PreparedStatement 是 Statement 接口的子接口，继承于父接口中所有的方法。

¶创建方法

PreparedStatement prepareStatement(String sql)：指定预编译的 SQL 语句来创建一个语句对象，其中SQL语句使用占位符 ? 。（区别于 Statement createStatement() ）

¶使用步骤⭐

1. 编写 SQL 语句，用 ? 占位待定的内容，如：

   String sql = "SELECT * FROM user WHERE name = ? AND password = ?;";

2. 调用 Connection 对象的方法，其中传入 SQL 串，返回 PreparedStatement 对象。

3. 设置实际参数：setXxx(占位符位置编号, 实际值)，如：

   pstmt.setDouble(1, 3.14159); 
   pstmt.setString(3, "Luffy"); //将第三个占位符设置值为"Luffy"

4. 调用方法来执行参数化 SQL 语句：

   int executeUpdate();
   ResultSet executeUpdate();    

5. 关闭资源

¶优点

1. 因为它有预先编译的功能，故我们能够多次传入不同的参数给 PreparedStatement 对象并执行，减少了 SQL 编译次数，提高了效率。

   Statement stmt = conn.createStatement();
   stmt.executeUpdate("INSERT INTO users VALUES(1, "Luffy");");
   stmt.executeUpdate("INSERT INTO users VALUES(2, "Zoro");"); //效率低，数据库需编译一万次

   String sql = "INSERT INTO users VALUES(?, ?);";
   PreparedStatement pstmt = conn.preparedStatement(sql);
   
   //设置参数
   pstmt.setString(1, 1);
   pstmt.setInt(2, "Luffy");
   pstmt.executeUpdate();
   //再次设置参数
   pstmt.setString(1, 2);
   pstmt.setString(2, "Zoro");
   pstmt.executeUpdate();

2. 可以有效地防止 SQL 注入的问题，安全性更高

¶1.3 抽取JDBC工具类及其案例

当一个功能经常需要用到，则应该将该功能封装为一个工具类，可在不同的地方重用。

¶1.3.1 JDBCUtil

该工具类应包含三个方法：

1. 创建个配置文件，用于设置 用户名、密码、URL、驱动类
2. 获取数据库的连接： getConnection()
3. 关闭所有打开的资源（由于关闭的资源的数量不限定，我们应该重载方法）

¶1.3.2 用户登陆案例

需求：通过键盘录入用户名和密码，系统判断用户是否登陆成功。

jdbc.properties文件：

url = jdbc:mysql:///db1
user = root
password = root
driver = com.mysql.jdbc.Driver

JDBCUtils.java 文件（自定义JDBC工具类）：

package JoyDee.utils;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //只有静态的变量，才能被静态代码块访问
    private static String url, user, password, driver;
    //文件的读入，只需读入一次即可拿到相应值，使用静态代码块较为妥当
    static {
        try {
            //1. 创建Properties这一属性集合类
            Properties pro = new Properties(); 
            //2. 获取配置文件的路径
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();//使用类加载器
            //URL为统一资源定位器，定位到该配置文件的绝对路径
            URL resurl = classLoader.getResource("jdbc.properties");
            String path = resurl.getPath(); //得到字符串路径
            //3. 加载配置文件
            pro.load(new FileReader(path));
            //4. 获取属性值并赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //5. 注册驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    } //获取连接对象并将其返回

    public static void close(Statement stmt, Connection conn){
        if(stmt != null){
            try{
                stmt.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
        // 注意，为保证两个对象能够正常关闭资源，两段try..catch不能合并。
        if(conn != null){
            try{
                conn.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet res, Statement stmt, Connection conn){
        if(res != null){
            try{
                res.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try{
                stmt.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
        // 注意，为保证两个对象能够正常关闭资源，两段try..catch不能合并。
        if(conn != null){
            try{
                conn.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

JDBCDemo.java文件：（为避免“SQL注入”问题，使用了 PreparedStatement 接口）

package JoyDee.LearningJDBC;

import JoyDee.utils.JDBCUtils;
import java.sql.*;
import java.util.Scanner;

public class JDBCLogin {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in); //键盘录入信息
        System.out.println("请输入您要登陆的用户名:");
        String username = sc.nextLine();
        System.out.println("请输入您的密码:");
        String password = sc.nextLine();
        boolean flag = new JDBCLogin().login(username, password);
        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;
        PreparedStatement pstmt = null; //改为Statement的子类
        ResultSet res = null;
        try{
            conn = JDBCUtils.getConnection();
            String sqlstr = "SELECT * FROM userlist WHERE username = ? AND password = ?";
            pstmt = conn.prepareStatement(sqlstr); //传入SQL语句字符串
            pstmt.setString(1, username); //传递该方法的传入参数
            pstmt.setString(2, password);
            res = pstmt.executeQuery(); //无需再传入参数!
            return res.next();
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            JDBCUtils.close(res, pstmt, conn);
        }
        return false;
    }
}

登陆成功	登陆失败

¶1.4 JDBC控制事务

¶1.4.1 相关API

Connection 接口中与事务有关的方法	说明
void setAutoCommit(boolean autoCommit)	参数为 true 或 false，若设置为 false，则表示关闭自动提交，相当于 开启事务。
void commit()	提交事务
void rollback()	回滚事务

¶1.4.2 案例演示

package JoyDee.LearningJDBC;

import JoyDee.utils.JDBCUtils; //引用自定义的工具类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionDemo {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            conn = JDBCUtils.getConnection();//获取连接
            conn.setAutoCommit(false); //开启事务
            pstmt = conn.prepareStatement("UPDATE stu SET score = score + ? WHERE name = ?");
            pstmt.setDouble(1, 99.8);
            pstmt.setString(2, "Luffy"); //Luffy得分增加99.8
            pstmt.executeUpdate();
            pstmt.setDouble(1, -99.8);
            pstmt.setString(2, "Zoro"); //Zoro 得分减少99.8
            pstmt.executeUpdate();
            conn.commit(); //别忘记提交事务
            System.out.println("转账成功!");
        } catch (Exception e){
            e.printStackTrace();
            try{
                conn.rollback(); //既然捕捉到了异常，那么一定要对事务进行回滚
            } catch (NullPointerException |SQLException e2){ //避免发生空指针异常
                e2.printStackTrace();
            }
            System.out.println("转账失败!");
        } finally {
            JDBCUtils.close(pstmt, conn);
        }
    }
}