JavaWeb-JDBC

学完了Java SE部分的内容后，继续学习Java EE。学习JavaWeb以及一些主流的开发框架如SSM,SpringBoot,SpringClound等。首先来学习JDBC，一个与数据库连接相关的API。

一、JDBC

1、概述

1）概念
使用Java语言操作关系型数据库的一套API

JDBC（Java DataBase Connectivity），即==Java数据库连接==

2）本质

• 是一套操作所有关系型数据库的规则，即接口
• 由各数据库厂商去实现这套接口，提供数据库驱动jar包（驱动即实现类）
• 面向接口（JDBC）编程，真正执行代码的是驱动jar包中的实现类

3）好处

• 各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发
• 可随时替换底层数据库，访问数据库的Java代码基本不变

2、基本原理

2.1 使用步骤

1.创建工程，导入驱动jar包
- 注意要Add as Library将jar包添加到 java项目中，才会生效
2.注册驱动
- class.forName("com.mysql.jdbc.Driver"); （用到反射）
3.获取连接
- Connection conn=DriverManager.getConnecction(url,username,password);
4.定义SQL语句
- String sql="update...";
5.获取执行SQL对象
- Statement stmt=conn.createStatement();
6.执行SQL
- stmt.executeUpdate(sql);
7.处理返回结果
8.释放资源

流程小结：
导入jar包---->注册驱动---->获取连接---->定义SQL---->获取对象---->执行SQL---->返回结果---->释放资源

2.2 代码示例

1）数据库准备
在数据库管理工具Navicat下创建一个数据库db1和表account，表中定义了id,name,money三个字段，查询表中的数据如下

2）使用Java操作数据库（JDBC数据库连接）
IDEA右键src，new一个Java Class，命名为com.itweb.jdbc.JDBCDemo，这样使层级结构更加清晰，方便管理不同功能的类。

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
 * jdbc操作步骤
 */
public class jdbcDemo {
    public static void main(String[] args) throws Exception {
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存
        // 获取连接
        String url="jdbc:mysql://127.0.0.1:3306/db1";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 定义sql语句
        String sql="UPDATE account SET money=500 WHERE id=1";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql
        int count = stmt.executeUpdate(sql); // 受影响的行数
        // 返回处理结果
        System.out.println(count);
        // 释放资源
        stmt.close();
        conn.close(); // 先开后关
    }
}

3）查看运行结果
运行结果如下图

此时再返回Navicat查询结果

注：可能有的同学在java那边操作完成后，回来直接通过点击表的方式来查看，发现没变化，这是因为没有刷新。右键选择刷新即可。

3、相关API

3.1 DriverManager（驱动管理类）

主要有两个作用

1.注册驱动
- Class.forName("com.mysql.jdbc.Driver");  感兴趣的可ctrl+B查看Driver类源码
2.获取数据库连接
DriverManager.getConnection(url, username, password);

相关说明
1）url：连接路径（jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2...）
如："jdbc:mysql://127.0.0.1:3306/db1"，如果连接的是本机mysql服务器（ip为127.0.0.1，也可写为localhost），
且mysql服务默认端口为3306，则以上可以简写为 "jdbc:mysql:///db1";
2）配置useSSL=false参数，禁用安全连接方式，解决警告问题。

3.2 Connection（数据库连接对象）

主要作用

1）获取执行SQL的对象
- 普通执行SQL对象：Statement createStatement()
- 预编译SQL的执行SQL对象，防止SQL注入：PreparedStatement prepareStatement(sql)
- 执行存储过程的对象：CallableStatement prepareCall(sql)
2）管理事务（重点）
- mysql事务管理
    - 开启事务：begin;/start transaction;
    - 提交事务：commit
    - 回滚事务：rollback
- jdbc事务管理：Connection接口中定义了3个对应的方法（使用try-catch异常处理机制）
    - 开启事务：setAutoCommit(Boolean autoCommit)：true为自动提交事务；false为手动提交事务，即为开启事务
    - 提交事务：commit()
    - 回滚事务：rollback()
由上，只要单独记一下两者的开启事务，提交和回滚相同

3.3 Statement（执行SQL语句）

主要作用

1）int excuteUpdate(sql)：执行DML、DDL语句
- 返回值
    - DML语句影响的行数   
    - DDL语句执行后，执行成功也可能返回0
2）ResultSet excuteQuery(sql)：执行DQL语句
- 返回值
    - ResultSet结果集对象

拓展（DDL和DML）：

DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。常用关键字：select、update、delete、insert、merge

DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。关键字：create、alter、drop、truncate、comment、grant、revoke

区别：① DML操作是可以手动控制事务的开启、提交和回滚的。② DDL操作是隐性提交的，不能rollback！

3.4 ResultSet（结果集对象）

API文档解释

ResultSet对象维护指向其当前数据行的游标。 最初，光标位于第一行之前。 next方法将光标移动到下一行，并且因为当ResultSet对象中没有更多行时它返回false ，它可以在while循环中用于迭代结果集。

主要作用

1）封装了DQL查询语句的结果：ResultSet  
stmt.executeQuery(sql)
- 执行DQL语句，返回ResultSet对象

2）获取查询结果
boolean next()
- 将光标从当前位置向前移动一行
- 判断行当前是否为有效行（是否有数据）

[数据类型] get[数据类型](参数)：获取数据，如 
int getInt(参数); 
String getString(参数)
其中int为列编号(从1开始)，String为列名称

3.5 PreparedStatement（预编译SQL对象）

API文档解释

是一个接口，继承自Statement，表示预编译SQL语句的对象

主要作用：预编译SQL语句并执行，预防SQL注入问题

SQL注入：通过操作输入来修改事先定义好的SQL语句，来达到执行代码对服务器进行攻击的方法

防止SQL注入步骤（重点）

1）获取PreparedStatement对象
- SQL语句中的参数值用占位符？替代
String sql="SELECT * FROM tb_user WHERE username=? AND password=?";

- 通过Connection对象的获取，并传入对应的sql语句
PreparedStatement pstmt=conn.prepareStatement(sql)

2）设置参数值
PreparedStatement对象：setXxx(参数1，参数2)：给？赋值
Xxx：数据类型；如setInt(参数1，参数2)
参数：
- 参数1：？的位置编号，从1开始
- 参数2：？的值

3）执行SQL
executeUpdate(); / executeQuery();  不需要传递sql

防止SQL注入的好处（了解）

1.预编译SQL，性能更高
2.防止SQL注入，将敏感字符进行转义

3.6 代码示例

1）演示Connection

package com.itweb.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * jdbc API Connection类
 */
public class JDBCDemo {
    public static void main(String[] args) throws Exception {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // 将类加载进内存
        // 获取连接
        String url="jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 定义sql语句
        String sql1="UPDATE account SET money=1000 WHERE id=1";
        String sql2="UPDATE account set money=2000 WHERE id=3";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        try {
            // 开启事务
            conn.setAutoCommit(false); // 手动提交事务
            // 执行sql
            int count1 = stmt.executeUpdate(sql1); // 受影响的行数
            // 返回处理结果
            System.out.println(count1);
            int i=3/0; // 制造异常
            int count2 = stmt.executeUpdate(sql2); // 受影响的行数
            System.out.println(count2);
            // 提交事务
            conn.commit();
        } catch (SQLException e) {
            // 出现异常则回滚事务
            conn.rollback();
            e.printStackTrace();
        }
        // 释放资源
        stmt.close();
        conn.close(); // 先开后关
    }
}

运行结果

1
Exception in thread "main" java.lang.ArithmeticException: / by zero
	at com.itweb.jdbc.JDBCDemo.main(JDBCDemo.java:31)

回去刷新数据库表，发现原来的数据没有被更改，这样保证了在任意一个出现异常时都不会提交事务而是回滚。很好理解，比如自己可以思考一下生活中银行转账的例子。
2）演示Statement

package com.itweb.jdbc;

import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
 * jdbc API Statement类
 * 单元测试，执行DML，DDL语句
 */
public class JDBCDemo {
    @Test
    public void testDML() throws Exception {
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 定义sql语句
        String sql="UPDATE account SET money=3000 WHERE id=1";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql语句
        int count=stmt.executeUpdate(sql); // 执行完DML语句后，返回受影响的行数
        // 返回处理结果
//        System.out.println(count); // 打印结果
        if(count>0){
            System.out.println("修改成功！"); // 以输出提示的方式，易于用户理解
        }
        else {
            System.out.println("修改失败！");
        }
        // 释放资源
        stmt.close();
        conn.close(); // 先开后关
    }
    @Test
    public void testDDL() throws Exception{ // 单元测试，执行DML，DDL语句
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql:///db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 定义sql语句
//        String sql="CREATE DATABASE db2"; // 创建好后注释掉，防止再创建
        String sql="DROP DATABASE db2";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql语句
        int count=stmt.executeUpdate(sql); // 执行完DML语句后，返回受影响的行数
        // 返回处理结果
//        System.out.println(count); // 打印结果
//        if(count>0){
//            System.out.println("修改成功！"); // 以输出提示的方式，易于用户理解
//        }
//        else {
//            System.out.println("修改失败！");
//        }
        System.out.println(count); // 0
        // 释放资源
        stmt.close();
        conn.close(); // 先开后关
    }
}

运行结果说明
运行testDML()方法前，数据库表的数据

运行testDML()方法，如下，再回去刷新数据库，发现id为1的用户money修改成功。

运行testDDL()方法，如下，再回去刷新数据库，发现db2已经成功被删除。

3）演示ResultSet

package com.itweb.jdbc;

import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 * jdbc API ResultSet类
 * 执行DQL语句
 */
public class JDBCDemo {
    @Test
    public void testResultSet() throws Exception{
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql:///db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 定义sql语句
        String sql="SELECT * FROM `account`";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql语句
        ResultSet rs=stmt.executeQuery(sql);
        // 处理结果,遍历rs中的所有数据
        while (rs.next()){
//            int id = rs.getInt(1);
//            String name = rs.getString(2);
//            double money = rs.getDouble(3);
            // 使用重载的方法(结果也是一样)
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            // 打印结果
            System.out.println(id);
            System.out.println(name);
            System.out.println(money);
            System.out.println("-----------");
        }
        // 释放资源
        rs.close();
        stmt.close();
        conn.close();
    }
}

运行结果

4）ResultSet综合案例
需求：查询account账户表数据，封装为Account对象，并储存到ArrayList集合中。
首先需要创建一个Account类

package com.itweb.pojo;

public class Account {
    // 封装成员属性，注意数据类型要和数据库定义的一样
    private int id; 
    private String name;
    private double money;

    // 提供对应的get和set方法
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getMoney() {
        return money;
    }
    public void setMoney(double money) {
        this.money = money;
    }

    @Override
    public String toString() { // 提供toString()方法，方便查看
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

然后再编写测试类

package com.itweb.jdbc;

import com.itweb.pojo.Account;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * jdbc API ResultSet类
 * 执行DQL语句
 */
public class JDBCDemo {
    @Test
    public void testResultSet() throws Exception{
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql:///db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 定义sql语句
        String sql="SELECT * FROM `account`";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql语句
        ResultSet rs=stmt.executeQuery(sql);

        // 创建集合,用于存储Account对象中的数据,定义泛型为Account类
        List<Account> list=new ArrayList<>();

        // 处理结果,遍历rs中的所有数据
        while (rs.next()){
            // 创建Account对象
            Account account=new Account();

            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            // 调用set方法将账户表中的数据传入到Account对象中
            account.setId(id);
            account.setName(name);
            account.setMoney(money);
            // 将Account对象中的数据添加进集合
            list.add(account);
        }
        // 打印集合，输出结果
        System.out.println(list);
        // 释放资源
        rs.close();
        stmt.close();
        conn.close();
    }
}

运行结果

5）用户登录案例+演示SQL注入（使用单元测试Junit）
首先在数据库db1下创建一个user表，包含username和password两个字段，查询如下

编写测试类

package com.itweb.jdbc;

import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 * 用户登录
 */
public class JDBCDemo {
    @Test
    public void testLogin() throws Exception{
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql:///db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 接收用户输入 用户名和密码
        String name="张三";
        String pwd="123";
        // 定义sql语句
        String sql="SELECT * FROM `user` where username='"+name+"' and password='"+pwd+"'";
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql
        ResultSet rs=stmt.executeQuery(sql);
        // 判断是否登录成功
        if (rs.next()){ // 查询结果集(数据库)里面包含用户输入的用户名和密码，即登录成功
            System.out.println("登录成功~");
        }
        else{
            System.out.println("登录失败~");
        }
        // 释放资源
        rs.close();
        stmt.close();
        conn.close();
    }

    /**
     * 演示SQL注入
     */
    @Test
    public void testLogin_Inject() throws Exception{
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql:///db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 接收用户输入 用户名和密码
        String name="aaa";
        String pwd="' or '1'='1";
        // 定义sql语句
        String sql="SELECT * FROM `user` where username='"+name+"' and password='"+pwd+"'";
        System.out.println(sql);
        // 获取执行sql对象
        Statement stmt = conn.createStatement();
        // 执行sql
        ResultSet rs=stmt.executeQuery(sql);
        // 判断是否登录成功
        if (rs.next()){ // 查询结果集(数据库)里面包含用户输入的用户名和密码，即登录成功
            System.out.println("登录成功~");
        }
        else{
            System.out.println("登录失败~");
        }
        // 释放资源
        rs.close();
        stmt.close();
        conn.close();
    }
}

运行testLogin()，结果如下

运行testLogin_Inject()，结果如下

==原因分析（重点）==

使用（’ or ‘1’=’1）等特殊字符作为登录密码，破解了上述定义sql查询语句的字符串拼接漏洞（即SQL注入的本质：通过操作输入来修改事先定义好的SQL语句，来达到执行代码对服务器进行攻击的方法）。通过打印sql，得到SELECT * FROM user where username=’aaa’ and password=’’ or ‘1’=’1’，可以发现，where后面的条件判断为false and false or true，由于and的优先级比or高，所以整体结果为true，即无论输入的是什么用户名，通过该串密码都能查询成功。当然，现在的程序都不会让SQL注入发生，因为这是一个低级且严重的错误。

==6）演示防止SQL注入（重点）==

package com.itweb.jdbc;

import org.junit.Test;
import java.sql.*;
/**
 * API详解：PreparedStatement
 * 防止SQL注入操作
 */
public class JDBCDemo {
    @Test
    public void testPreparedStatement() throws Exception{
        // 注册驱动
        //Class.forName("com.mysql.jdbc.Driver"); // 将类加载进内存 (可以省略)
        // 获取连接
        String url="jdbc:mysql:///db1?useSSL=false";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 接收用户输入 用户名和密码
        String name="aaa";
        String pwd="' or '1'='1";

        // 定义sql语句
        String sql="SELECT * FROM user WHERE username=? AND password=?";
        // 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        // 设置?的值
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);
        // 执行sql
        ResultSet rs=pstmt.executeQuery(); // 这里不能传sql
        // 判断是否登录成功
        if (rs.next()){ // 查询结果集(数据库)里面包含用户输入的用户名和密码，即登录成功
            System.out.println("登录成功~");
        }
        else{
            System.out.println("登录失败~");
        }
        // 释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }
}

运行结果

实质：pstmt对象在传入参数时会对其值进行转义操作，从而避免了由于拼接产生的语义问题。

4、数据库连接池（了解，会用就行）

1）概念
管理数据库连接的容器，负责分配、管理数据库连接
2）特点
- 允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个
- 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏
3）好处
- 资源重用
- 提示系统响应速度
- 避免数据库连接遗漏
4）Driud（德鲁伊）数据库连接池（阿里巴巴）
使用步骤
- 导入jar包
- 定义配置文件
- 加载配置文件
- 获取数据库连接池对象
- 获取连接