Java基础19-java进阶(10)【JDBC基础】

Java进阶10

JDBC入门

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

1. 使用第三方客户端来访问 MySQL： SQLyog 、 Navicat 、 MySQL Workbench …
2. 使用 MySQL 自带的命令行方式
3. 通过各种编程语言的库来访问 MySQL 数据库

1.1 什么是JDBC

JDBC全称是 Java DataBase Connectivity 中文翻译为 Java 访问数据库 ，是使用Java操作数据库的一组标准规范，即一组使用Java语言编写的接口。但是真正怎么操作数据库还需要具体的实现类，也就是数据库驱动。每个数据库厂商根据自家数据库的通信格式编写好自己数据库的驱动。所以我们只需要会调用 JDBC 接口中的方法即可，数据库驱动由数据库厂商提供。

使用 JDBC 的好处

1. 程序员如果要开发访问数据库的程序，只需要会调用 JDBC 接口中的方法即可，不用关注类是如何实现的。

2. 使用同一套 Java 代码，进行少量的修改就可以访问其他 JDBC 支持的数据库。

使用 JDBC开发使用到的包

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

1.2 快速入门

步骤

1. 导入驱动包

2. 加载并注册驱动

2 核心API

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

2.1 DriverManager类

作用

1. 管理和注册驱动

2. 创建数据库的连接

类中的方法

1. Connection getConnection (String url, String user, String password) :通过连接字符串，用户名，密码来得到数据库的连接对象

2. Connection getConnection (String url, Properties info) :通过连接字符串，属性对象来得到连接对象

使用 JDBC 连接数据库的四个参数

1. 用户名：登录数据库的用户名

2. 密码：登录数据库的密码

3. URL：不同的数据库 URL 是不同的，mysql 的写法 jdbc:mysql://localhost:3306/数据库[?参 数名=参数值]

4. 驱动类的字符串名：com.mysql.jdbc.Driver

• 如果是本地服务且端口是3306，URL可简写为 jdbc:mysql:///数据库名
• 如果出现中文乱码可以在URL后加上参数 characterEncoding=utf8

2.2 Connection接口

作用

Connection 接口，具体的实现类由数据库的厂商实现，代表一个连接对象。

常用方法

1. Statement createStatement() :创建一条 SQL 语句对象

2.3 Statement接口

访问数据库的步骤

1. 注册和加载驱动(可以省略)

2. 获取连接

3. Connection 获取 Statement 对象

4. 使用 Statement 对象执行 SQL 语句

5. 返回结果集

6. 释放资源

作用

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

常用方法

1. int executeUpdate(String sql) :用于发送 DML 语句，增删改的操作。 insert 、 update 、 delete 参数：SQL 语句 返回值：返回对数据库影响的行数

2. ResultSet executeQuery(String sql) :用于发送 DQL 语句，执行查询的操作。 select 参数：SQL 语句 返回值：查询的结果集

2.4 ResultSet接口

作用

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

常用的方法

1. boolean next() :1) 游标向下移动 1 行 2) 返回 boolean 类型，如果还有下一条记录，返回true，否则返回 false

2. getXxx() :1) 通过字段名，参数是 String 类型。返回不同的类型 2) 通过列号，参数是整数，从 1开始。返回不同的类型

常用数据类型转换表

java.sql.Date、Time、Timestamp(时间戳)，三个共同父类是：java.util.Date

2.5 释放资源

当完成与数据库的交互后，需要将于数据库有关的资源释放，不然可能会产生内存泄漏

需要释放的对象

ResultSet 结果集，Statement 语句，Connection 连接

释放原则

先开的后关，后开的先关。ResultSet->Statement->Connection

放在哪个代码块中

finally块

Java8里的一个新语法特性：try-with-resources。try-with-resources的特性就是，在try( …)里声明的资源，会在try-catch代码块结束后自动关闭掉。

3 基本CRUD

3.1 插入数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*** 向学生表中添加 4 条记录，主键是自动增长 */
public class Demo5DML { 
    public static void main(String[] args) throws SQLException { 
        // 1) 创建连接对象 
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root"); 
        // 2) 创建 Statement 语句对象 
        Statement statement = connection.createStatement(); 
        // 3) 执行 SQL 语句：executeUpdate(sql)
        int count = 0; 
        // 4) 返回影响的行数 
        count += statement.executeUpdate("insert into student values(null, 'tom', 1, '1993-03- 24')"); 
        count += statement.executeUpdate("insert into student values(null, 'jack', 0, '1995-03- 24')");
        count += statement.executeUpdate("insert into student values(null, 'lily', 1, '1903-03-24')");
        count += statement.executeUpdate("insert into student values(null, 'lucy', 0, '1993-03- 11')"); 
        System.out.println("插入了" + count + "条记录"); 
        // 5) 释放资源 
        statement.close(); 
        connection.close(); 
    } 
}

3.2 修改数据

import java.sql.Connection; 
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; 
public class Demo5DML { 
    public static void main(String[] args) throws SQLException { 
        // 1) 创建连接对象 
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root"); 
        // 2) 创建 Statement 语句对象
        Statement statement = connection.createStatement();
        // 3) 执行 SQL 语句：
        executeUpdate(sql) int count = 0; 
        // 4) 返回影响的行数 
        count += statement.executeUpdate("update student set name = 'andy' where id = 1"); 
        System.out.println("修改了" + count + "条记录");
        // 5) 释放资源 
        statement.close(); 
        connection.close(); 
    } 
}

3.3 删除数据

import java.sql.Connection; 
import java.sql.DriverManager;
import java.sql.SQLException; 
import java.sql.Statement;
public class Demo5DML { 
    public static void main(String[] args) throws SQLException { 
        // 1) 创建连接对象 
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root"); 
        // 2) 创建 Statement 语句对象
        Statement statement = connection.createStatement();
        // 3) 执行 SQL 语句：
        executeUpdate(sql) int count = 0; 
        // 4) 返回影响的行数 
        count += statement.executeUpdate("delete from student where id = 1"); 		          System.out.println("删除了" + count + "条记录");
        // 5) 释放资源 
        statement.close(); 
        connection.close(); 
    } 
}

3.4 查询数据

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement;
public class Demo5DML { 
    public static void main(String[] args) throws SQLException {
        //1) 得到连接对象 
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day24","root","root");
        //2) 得到语句对象 
        Statement statement = connection.createStatement();
        //3) 执行 SQL 语句得到结果集 
        ResultSet 对象 ResultSet rs = statement.executeQuery("select * from student"); 
        //4) 循环遍历取出每一条记录 
        while(rs.next()) { 
            int id = rs.getInt("id"); 
            String name = rs.getString("name"); 
            boolean gender = rs.getBoolean("gender");
            Date birthday = rs.getDate("birthday");
            //5) 输出的控制台上 
            System.out.println("编号：" + id + ", 姓名：" + name + ", 性别：" + gender + ", 生日：" + birthday); 
        } 
        //6) 释放资源
        rs.close(); 
        statement.close(); 
        connection.close(); 
    } 
}

关于 ResultSet 接口中的注意事项：

1. 如果光标在第一行之前，使用 rs.getXX()获取列值，报错：Before start of result set

2. 如果光标在最后一行之后，使用 rs.getXX()获取列值，报错：After end of result set

3. 使用完毕以后要关闭结果集 ResultSet，再关闭 Statement，再关闭 Connection

4 数据库工具类

为什么要自己创建工具类？

如果一个功能经常要用到，我们建议把这个功能做成一个工具类，可以在不同的地方重用。

工具类的设计

1. 可以把几个字符串定义成常量：用户名，密码，URL，驱动类

2. 得到数据库的连接： getConnection()

3. 关闭所有打开的资源： close(Connection conn, Statement stmt) close(Connection conn, Statement stmt, ResultSet rs)

import java.sql.*; 
public class JdbcUtils { 
    //可以把几个字符串定义成常量：用户名，密码，URL，驱动类 
    private static final String USER = "root"; 
    private static final String PWD = "root"; 
    private static final String URL = "jdbc:mysql://localhost:3306/day24";
    private static final String DRIVER= "com.mysql.jdbc.Driver"; 
    /*** 注册驱动 */ 
    static { 
        try {
            Class.forName(DRIVER) ; 
        } 
        catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
    }
    /*** 得到数据库的连接 */
    public static Connection getConnection() throws SQLException { 
        return DriverManager.getConnection(URL,USER,PWD); 
    }
    /*** 关闭所有打开的资源 */ 
    public static void close(Connection conn, Statement stmt) { 
        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(Connection conn, Statement stmt, ResultSet rs) { 
        if (rs!=null) { 
            try {rs.close(); 
                } catch (SQLException e) { 
                e.printStackTrace( ); 
            } 
        } 
    } 
}

5 用户登陆

5.1 需求

1. 有一张用户表

2. 添加几条用户记录

create table user ( 
    id int primary key auto_increment, 
    name varchar(20), 
    password varchar(20) 
)
insert into user values (null,'jack','123'),(null,'rose','456'); 
-- 登录, SQL 中大小写不敏感 
select * from user where name='JACK' and password='123';
-- 登录失败 
select * from user where name='JACK' and password='333';

3. 使用 Statement 字符串拼接的方式实现用户的登录, 用户在控制台上输入用户名和密码。

5.2 步骤

1. 得到用户从控制台上输入的用户名和密码来查询数据库

2. 写一个登录的方法

• 通过工具类得到连接
• 创建语句对象，使用拼接字符串的方式生成 SQL 语句
• 查询数据库，如果有记录则表示登录成功，否则登录失败
• 释放资源

import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.Scanner; 
public class Demo7Login { 
    //从控制台上输入的用户名和密码 
    public static void main(String[] args) { 
        Scanner sc = new Scanner(System.in); 
        System.out.println("请输入用户名："); 
        String name = sc.nextLine(); 
        System.out.println("请输入密码：");
        String password = sc.nextLine(); 
        login(name, password); 
    }
    /*** 登录的方法 */ 
    public static void login(String name, String password) { 
        //a) 通过工具类得到连接
        Connection connection = null; 
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            //b) 创建语句对象，使用拼接字符串的方式生成 SQL 语句
            statement = connection.createStatement();
            //c) 查询数据库，如果有记录则表示登录成功，否则登录失败 
            String sql = "select * from user where name='" + name + "' and password='" + password + "'"; 
            System.out.println(sql);
            rs = statement.executeQuery(sql); 
            if (rs.next()) {
                System.out.println("登录成功，欢迎您：" + name); 
            } 
            else { 
                System.out.println("登录失败"); 
            } 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            //d) 释放资源 
            JdbcUtils.close(connection, statement, rs); 
        } 
    } 
}

5.3 SQL注入

当我们输入以下密码，我们发现我们账号和密码都不对竟然登录成功了

请输入用户名：newboy 
请输入密码： a' or '1'='1 
select * from user where name='newboy' and password='a' or '1'='1' 
登录成功，欢迎您：newboy

分析

select * from user where name='newboy' and password='a' or '1'='1' name='newboy' and password='a' 为假 '1'='1' 真 相当于 select * from user where true; 查询了所有记录

我们让用户输入的密码和 SQL 语句进行字符串拼接。用户输入的内容作为了 SQL 语句语法的一部分，改变了原有 SQL 真正的意义，以上问题称为 SQL 注入。要解决 SQL 注入就不能让用户输入的密码和我们的 SQL 语句进行简单的字符串拼接。

5.4 PreparedStatement 接口

PreparedStatement 是 Statement 接口的子接口，继承于父接口中所有的方法。它是一个预编译的 SQL 语句

1. 因为有预先编译的功能，提高 SQL 的执行效率。

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

常用方法

使用步骤

1. 编写 SQL 语句，未知内容使用?占位：“SELECT * FROM user WHERE name=? AND password=?”;

2. 获得 PreparedStatement 对象

3. 设置实际参数：setXxx(占位符的位置, 真实的值)

4. 执行参数化 SQL 语句

5. 关闭资源

5.5 改进登录方法

private static void login(String name, String password) throws SQLException { 
    Connection connection = JdbcUtils.getConnection(); 
    //写成登录 SQL 语句，没有单引号 
    String sql = "select * from user where name=? and password=?";
    //得到语句对象
    PreparedStatement ps = connection.prepareStatement(sql);
    //设置参数 
    ps.setString(1, name); 
    ps.setString(2,password);
    ResultSet resultSet = ps.executeQuery();
    if (resultSet.next()) {
        System.out.println("登录成功：" + name); 
    }else {
        System.out.println("登录失败");
    }
    //释放资源,子接口直接给父接口 
    JdbcUtils.close(connection,ps,resultSet); 
}

6 事务管理

6.1 准备数据

CREATE TABLE account ( 
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10), balance DOUBLE 
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('Jack', 1000), ('Rose', 1000);

6.2 API介绍

6.3 开发步骤

1. 获取连接

2. 开启事务

3. 获取到 PreparedStatement

4. 使用 PreparedStatement 执行两次更新操作

5. 正常情况下提交事务

6. 出现异常回滚事务

7. 最后关闭资源

import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
public class Demo12Transaction {
    //没有异常，提交事务，出现异常回滚事务 
    public static void main(String[] args) {
        //1) 注册驱动
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //2) 获取连接 
            connection = JdbcUtils.getConnection();
            //3) 开启事务
            connection.setAutoCommit(false);
            //4) 获取到PreparedStatement 
            //从 jack 扣钱 
            ps = connection.prepareStatement("update account set balance = balance - ? where name=?"); 
            ps.setInt(1, 500);
            ps.setString(2,"Jack"); 
            ps.executeUpdate();
            //出现异常 
            System.out.println(100 / 0); 
            //给 rose 加钱 
            ps = connection.prepareStatement("update account set balance = balance + ? where name=?"); 
            ps.setInt(1, 500);
            ps.setString(2,"Rose"); 
            ps.executeUpdate();
            //提交事务
            connection.commit();
            System.out.println("转账成功"); 
        } catch (Exception e) { 
            e.printStackTrace() ;
            try {
                //事务的回滚 
                connection.rollback(); 
            } catch (SQLException e1) {
                e1.printStackTrace() ;
            }
            System.out.println("转账失败"); 
        }finally { 
            //7) 关闭资源 
            JdbcUtils.close(connection,ps);
        } 
    } 
}