MySQL学习笔记（六）—— JDBC

---

一、名词解释

1） 什么是JDBC?
答：JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，由此可以使用Java访问数据库。它由一组用Java语言编写的类和接口组成。

2） 什么是数据库驱动？
答：由于应用程序不能直接使用数据库，所以需通过相应的数据库驱动程序，实现与数据库的交互。这些数据库的驱动为数据库厂商的JDBC接口实现。

二、第一个JDBC程序

1.引入库

需要因此mysql-connector-java包和java.sql包

// 1. 通过maven引入mysql-connector-java包
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

// 2.引入java.sql
import java.sql.*;

2. 创建一个MySQL数据库

创建一个数据库jdbcStudy，用于JDBC代码的测试。

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

3. 完成第一个JDBC程序

1）目标：使用Java查询数据库jdbcStudy中users表的全部信息。

2）步骤：加载数据库驱动 -> 输入登录数据库的用户信息和数据库url -> 建执行数据库的对象connection -> 创建执行SQL的对象statementl -> 编写SQL语句，让执行SQL的对象去执行该语句 -> 释放所有已创建对象的连接。

3）代码实现

import java.sql.*;

// 第一个jdbc程序
public class demo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 输入用户信息和数据库url
        // jdbcstudy:要操作的数据库名称。
        // ?：连接参数。
        // useUnicode=true：支持中文编码
        // characterEncoding=utf8：设置字符编码集
        // useSSl=true：设定SSL安全传输机制
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
        String username = "root";
        String password = "123456";


        // 3. 连接成功后，创建执行数据库的对象connection（可查看表的信息，设置事务提交等）
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. 创建执行SQL的对象statement
        Statement statement = connection.createStatement();

        // 5. 编写SQL语句，让执行SQL的对象去执行该语句
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);  // resultSet返回的查询结果（链表）
//        int num = statement.executeUpdate(sql); // 增删改用该语句，返回表中改变的行数。


        while (resultSet.next()){
            System.out.println("id:" + resultSet.getObject("id")); // 在不知道列类型的情况下获得使用object
            System.out.println("name:" + resultSet.getObject("NAME"));
            System.out.println("password:" + resultSet.getObject("PASSWORD"));
            System.out.println("email:" + resultSet.getObject("email"));
            System.out.println("birthday:" + resultSet.getObject("birthday"));
            System.out.println("-----------------------------------");
        }

        // 6. 释放所有已创建对象的连接.(先创建的对象后释放)
        resultSet.close();
        statement.close();
        connection.close();
    }
}

4）代码查询结果

三、SQL注入

1.概念

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。简而言之，SQL存在漏洞，会被攻击，导致数据泄露等问题出现。

例如：
在登录业务中，使用第二部分的代码匹配用户账号密码的语句：

select * from users where username='name' and password ='password'

其中username和password需要用户填入。
若用户输入以下语句：

String name=" '' or  1=1";
String password ="12412r1"; //password值在此不重要

可匹配到表中所有用户信息，从而导致信息泄露。

2. 解决方法——PreparedStatement

1）针对SQL注入的解决方法为：将创建执行SQL语句的对象改成PreparedStatement。
2）原因：PreparedStatement将传递进来的参数当作字符，其中存在转义的字符将被直接转义。
3）代码实现：
注意：步骤4和5与Statement不同。

import java.sql.*;
public class Demo3 {
    public static void main(String[] args) throws Exception {
        // 1. 加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 输入用户信息和数据库url
        // jdbcstudy:要操作的数据库名称。
        // ?：连接参数。
        // useUnicode=true：支持中文编码
        // characterEncoding=utf8：设置字符编码集
        // useSSl=true：设定SSL安全传输机制
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
        String username = "root";
        String password = "qpb1994@VNTY";

        // 3. 连接成功后，创建执行数据库的对象connection（可查看表的信息，设置事务提交等）
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. 编写SQL语句  （？：占位符，表示需要传入值的位置）
        String sql="insert into users(id,name,password,email,birthday)" +
                "values(?,?,?,?,?);";

        // 5. 创建执行SQL的对象preStatement，并对每一位占位符的参数手动赋值。
        PreparedStatement preStatement = connection.prepareStatement(sql);  // 预编译sql

        preStatement.setInt(1,100);
        preStatement.setString(2,"hi");
        preStatement.setString(3,"123");
        preStatement.setString(4,"123@qq.com");
        preStatement.setDate(5,new Date(new Date(123).getTime()));

        // 查看插入结果
        int i = preStatement.executeUpdate();
        if (i>0){
            System.out.println("插入成功！");
        }

        // 6. 释放所有已创建对象的连接.(先创建的对象后释放)
        preStatement.close();
        connection.close();
    }
}

四、JDBC操作事务

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

public class Demo4 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preStatement = null;
        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 输入用户信息和数据库url
            String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
            String username = "root";
            String password = "123456";

            // 3. 连接成功后，创建执行数据库的对象connection
            connection = DriverManager.getConnection(url, username, password);

            // 4. 关闭自动提交，自动开启事务
            connection.setAutoCommit(false);

            String sql1 = "update users set name='1' where name='123';";
            preStatement = connection.prepareStatement(sql1);  // 预编译sql
            preStatement.executeUpdate();

//            int i = 1/0;  // 验证回滚

            String sql2 = "update users set name='345' where name='1';";
            preStatement = connection.prepareStatement(sql2);  // 预编译sql
            preStatement.executeUpdate();

            // 5. 业务完毕，提交事务
            connection.commit();
            
        } catch (Exception e) {
            // 或者出错，回滚
            connection.rollback();
        } finally {
            // 6. 释放所有已创建对象的连接
            preStatement.close();
            connection.close();
        }
    }
}