【JavaWeb笔记】第二章 JDBC

╰半橙微兮° 2022-01-29 16:07 392阅读 0赞

# JDBC #

### 文章目录 ###

*  JDBC
 *  1.JDBC概述
 *   *  1.1 什么是JDBC
     *  1.2 使用 JDBC 开发使用到的包和API
     *  1.3 idea导入mysql jar包
 *  2.JDBC中各个对象详解
 *   *  2.1 DriverManager：驱动管理对象
     *   *  DriverManager 作用
         *  DriverManager类中的方法
     *  2.2 Connection：数据库连接对象
     *  2.3 Statement：执行sql的对象
     *   *  Statement中的方法
         *  三种 Statement对象
     *  2.4 ResultSet：结果集对象,封装查询结果
     *   *  ResultSet中常用方法
         *  使用步骤
     *  2.5 PreparedStatement：执行sql的对象
     *   *  PreparedSatement 的好处
         *  PreparedStatement 接口中的方法
 *  3 JDBC使用
 *   *  3.1 JDBC访问数据库步骤
     *  3.2 JDBC实现DDL(数据定义语言)
     *  3.3 JDBC实现DQL(数据查询语言)
     *  3.4 JDBC实现DML(数据操作语言:增删改)
     *  3.5 JDBC实现登录功能(解决sql注入)
     *   *  1.使用Statement(未注意sql注入问题)
         *  2.使用PreparedStatement(解决sql注入问题)
     *  3.6 数据库工具类DBUtil(使用Properties来存储配置信息)
     *  3.7 反射封装查询,修改的统一方法
 *  4.JDBC事务的处理

# 1.JDBC概述 #

## 1.1 什么是JDBC ##

JDBC（Java Data Base Connectivity,Java数据库连接）是一种用于执行SQL语句的Java API，为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成

JDBC 规范定义接口，其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

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

**使用 JDBC 的好处**

1.  程序员如果要开发访问数据库的程序，只需要会调用 JDBC 接口中的方法即可，不用关注类是如何实现的。
2.  使用同一套 Java 代码，进行少量的修改就可以访问其他 JDBC 支持的数据库
3.  有了JDBC，程序员只需用JDBC API写一个程序，就可以访问所有数据库。
4.  将Java语言和JDBC结合起来使程序员不必为不同的平台编写不同的应用程序，只须写一遍程序就可以让它在任何平台上运行，这也是Java语言“编写一次，处处运行”的优势。

## 1.2 使用 JDBC 开发使用到的包和API ##

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

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

## 1.3 idea导入mysql jar包 ##

**方法一**:

1.  点击`file`\->`Project Structure`。
2.  点击左边的`Modules`\->`dependencies`\->右上角的＋号->选择第一个选项。
3.  选择jdbc包的路径，点击ok就行。

**方法二**:直接在jar包所在lib目录右击，显示 `Add as Library`点击即可

# 2.JDBC中各个对象详解 #

## 2.1 DriverManager：驱动管理对象 ##

### DriverManager 作用 ###

1.  **管理和注册驱动**
    
     *  **告诉程序该使用哪一个数据库驱动jar**

`static void registerDriver(Driver driver)` :注册与给定的驱动程序 `DriverManager` 。

写代码使用： `Class.forName("com.mysql.jdbc.Driver");`

通过查看源码发现：在`com.mysql.jdbc.Driver`类中存在静态代码块

static {
     		try {
    			java.sql.DriverManager.registerDriver(new Driver());
    		} catch (SQLException E) {
            	throw new RuntimeException("Can't register driver!");
             }
     }

1.  **获取数据库连接**
    
     *  方法：`static Connection getConnection(String url, String user, String password)`
     *  参数
        
         *  url：指定连接的路径
            
             *  语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
             *  例子：jdbc:mysql://localhost:3306/db3
             *  细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
         *  user：用户名
         *  password：密码

### DriverManager类中的方法 ###

<table> 
 <thead> 
  <tr> 
   <th><strong>DriverManager 类中的静态方法</strong></th> 
   <th><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Connection getConnection (String url, String user, String password)</td> 
   <td>通过连接字符串，用户名，密码来得到数据库的连接对象</td> 
  </tr> 
  <tr> 
   <td>Connection getConnection (String url, Properties info)</td> 
   <td>通过连接字符串，属性对象来得到连接对象</td> 
  </tr> 
 </tbody> 
</table>

## 2.2 Connection：数据库连接对象 ##

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

**功能**

1.  获取执行sql 的对象
    
     *  **Statement createStatement()** //创建一条 SQL 语句对象
     *  **PreparedStatement prepareStatement(String sql)**
2.  管理事务
    
     *  **setAutoCommit(boolean autoCommit)** ：开启事务调用该方法设置参数为false，即开启事务
     *  **commit()** ：提交事务
     *  **rollback()** ：回滚事务

## 2.3 Statement：执行sql的对象 ##

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

### Statement中的方法 ###

1.  `boolean execute(String sql)`：可以执行任意SQL语句，然后获得一个布尔值，表示是否返回ResultSet  
    int
2.  `executeUpdate(String sql)`：执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功，反之，则失败。
3.  `ResultSet executeQuery(String sql)`：执行DQL（select)语句,执行SQL查询并获取到ResultSet对象
    
     *  注意：executeQuery返回结果永远不为null，即便查不到数据，也为一个空集合

<table> 
 <thead> 
  <tr> 
   <th align="left"><strong>方法</strong></th> 
   <th><strong>作用</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">ResultSet executeQuery(String sql)</td> 
   <td>可以执行插入、删除、更新等操作，返回值是执行该操作所影响的行数</td> 
  </tr> 
  <tr> 
   <td align="left">boolean execute(String sql)</td> 
   <td>可以执行任意SQL语句，然后获得一个布尔值，表示是否返回ResultSet</td> 
  </tr> 
  <tr> 
   <td align="left">int executeUpdate(String sql)</td> 
   <td>执行SQL查询并获取到ResultSet对象</td> 
  </tr> 
 </tbody> 
</table>

### 三种 Statement对象 ###

1.  `Statement`：用于执行不带参数的简单SQL语句；
2.  `PreparedStatement`（从 Statement 继承）：用于执行带或不带参数的预编译SQL语句；可以解决sql注入问题
    
     *  SQL注入, 用户将自己填写的内容拼接到sql语句中, 从而破坏原有的SQL语句的结构的情况.
     *  PreparedStatement可以对sql进行预编译, 比Statement效率高, 而且安全
3.  `CallableStatement`（从PreparedStatement 继承）：用于执行数据库存储过程的调用。

## 2.4 ResultSet：结果集对象,封装查询结果 ##

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

*  ResultSet对象是executeQuery()方法的返回值，它被称为结果集，它代表符合SQL语句条件的所有行，并且它通过一套getXXX方法（这些get方法可以访问当前行中的不同列）提供了对这些行中数据的访问。
 *  ResultSet里的数据一行一行排列，每行有多个字段，且有一个记录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前的数据行。我们如果想要取得某一条记录，就要使用ResultSet的next()方法 ,如果我们想要得到ResultSet里的所有记录，就应该使用while循环。
 *  ResultSet对象自动维护指向当前数据行的游标。每调用一次next()方法，游标向下移动一行。
 *  初始状态下记录指针指向第一条记录的前面，通过next()方法指向第一条记录。循环完毕后指向最后一条记录的后面。

### ResultSet中常用方法 ###

1.  `boolean next()`：游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，  
    如果不是则返回true
2.  `getXxx(参数)`:获取数据
    
     *  Xxx：代表数据类型 如： int getInt() , String getString()
     *  参数：
        
         *  通过列号：int：代表列的编号,**从1开始** 如： getString(1)
         *  通过字段名：String：代表列名称。 如： getDouble(“balance”)

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

1.  如果光标在第一行之前，使用 rs.getXX()获取列值，报错：Before start of result set
2.  如果光标在最后一行之后，使用 rs.getXX()获取列值，报错：After end of result set
3.  使用完毕以后要关闭结果集 ResultSet，再关闭 Statement，再关闭 Connection
4.  java.sql.Date、Time、Timestamp(时间戳)，三个共同父类是：java.util.Date
5.  **java.sql.Date和java.util.Date的区别**
    
    1.  sql.Date继承util.Date
    2.  sql下的Date只有年月日 util下的Date既有年月日，又有时分秒
    3.  sql下的Date有无参构造器，util下的Date没有无参构造器

### 使用步骤 ###

1.  游标向下移动一行
2.  判断是否有数据
3.  获取数据

//循环判断游标是否是最后一行末尾。
    while(rs.next()){ 
        //获取数据
        int id = rs.getInt(1);
        String name = rs.getString("name");
        double balance = rs.getDouble(3);
    	System.out.println(id + "---" + name + "---" + balance);
    }

<table> 
 <thead> 
  <tr> 
   <th>方法名</th> 
   <th>说 明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>boolean next()</td> 
   <td>将光标从当前位置向下移动一行</td> 
  </tr> 
  <tr> 
   <td>boolean previous()</td> 
   <td>游标从当前位置向上移动一行</td> 
  </tr> 
  <tr> 
   <td>void close()</td> 
   <td>关闭ResultSet 对象</td> 
  </tr> 
  <tr> 
   <td>int getInt(int colIndex)</td> 
   <td>以int形式获取结果集当前行指定列号值</td> 
  </tr> 
  <tr> 
   <td>int getInt(String colLabel)</td> 
   <td>以int形式获取结果集当前行指定列名值</td> 
  </tr> 
  <tr> 
   <td>float getFloat(int colIndex)</td> 
   <td>以float形式获取结果集当前行指定列号值</td> 
  </tr> 
  <tr> 
   <td>float getFloat(String colLabel)</td> 
   <td>以float形式获取结果集当前行指定列名值</td> 
  </tr> 
  <tr> 
   <td>String getString(int colIndex)</td> 
   <td>以String 形式获取结果集当前行指定列号值</td> 
  </tr> 
  <tr> 
   <td>String getString(String colLabel)</td> 
   <td>以String形式获取结果集当前行指定列名值</td> 
  </tr> 
 </tbody> 
</table>

## 2.5 PreparedStatement：执行sql的对象 ##

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

### PreparedSatement 的好处 ###

1.  安全性更高，可以防止SQL注入
2.  prepareStatement()会先将 SQL 语句发送给数据库预编译。PreparedStatement 会引用着预编译后的结果。可以多次传入不同的参数给 PreparedStatement 对象并执行。减少 SQL 编译次数，提高效率。
3.  提高了程序的可读性
    
    **注意：后期都会使用PreparedStatement来完成增删改查的所有操作**

> **SQL注入问题**
> 
> 在拼接sql时，有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题
> 
> 1.  输入用户随便，输入密码：a’ or ‘a’ = 'a
> 2.  sql：select \* from user where username = ‘fhdsjkf’ and password = ‘a’ or ‘a’ = ‘a’
> 
> **解决sql注入问题**：使用PreparedStatement对象来解决

### PreparedStatement 接口中的方法 ###

1.  `int executeUpdate()`:执行 DML，增删改的操作，返回影响的行数。
2.  `ResultSet executeQuery()` :执行 DQL，查询的操作，返回结果集
3.  `PreparedStatement prepareStatement(String sql)` :指定预编译的 SQL 语句，SQL 语句中使用占位符?创建一个语句对象

# 3 JDBC使用 #

## 3.1 JDBC访问数据库步骤 ##

**1. 导入驱动jar包**

`mysql-connector-java-5.1.37-bin.jar`

1.  复制`mysql-connector-java-5.1.37-bin.jar`到项目的libs目录下
2.  右键–>`Add As Library`

**2. 加载和注册驱动(反射, DriverManager)**

加载JDBC驱动是通过调用方法`java.lang.Class.forName()`，下面列出常用的几种数据库驱动程序加载语句的形式 :

Class.forName(“oracle.JDBC.driver.OracleDriver”)；//使用Oracle的JDBC驱动程序 
    Class.forName(“com.microsoft.JDBC.sqlserver.SQLServerDriver”)；//使用SQL Server的JDBC驱动程序 
    Class.forName(“com.ibm.db2.JDBC.app.DB2Driver”)；//使用DB2的JDBC驱动程序 
    Class.forName("com.mysql.JDBC.Driver");//使用MySql的JDBC驱动程序

**3. 获取数据库连接对象 Connection**

与数据库建立连接的方法是调用`DriverManager.getConnection(String url, String user, String password )`方法

Connection conn=null;
    String url="jdbc:mysql://127.0.0.1:3306/db_demo?useUnicode=true&characterEncoding=utf8&useSSL=false";
    String user=“root";
    String password=“root";
    conn = DriverManager.getConnection(url, user, password);

**连接数据库的 URL 地址格式：**

协议名:子协议://服务器名或 IP 地址:端口号/数据库名?参数=参数值

**注意**：

1.  服务器名或IP地址 写为 127.0.0.1 和localhost代表连接本机，也可填写其他主机的ip地址，进行远程访问，前提是已经授权了远程访问
    
     *  MySQL授权远程访问代码如下
        
            grant all privileges on *.* to root@'%' identified by 'root' with grant option;
            flush privileges;
        
         *  \*.\* 中第一个\*代表所有的database 第二个\*代表所有database下的所有表格
         *  root@’%’ :代表任意ip均可连接本机器 root@ ‘192.168.10.1’ 代表ip为192.168.10.1的用户可连接本机器
         *  ‘root’ ''中间代表数据库的连接密码
         *  with grant option ：让连接用户拥有授权的权限
2.  MySQL 中可以简写：jdbc:mysql:///数据库名
    
     *  前提：必须是本地服务器，端口号是 3306

> **注意**
> 
> 1.  如果数据库出现乱码，可以指定参数: ?characterEncoding=utf8，表示让数据库以 UTF-8 编码来处理数据。jdbc:mysql://localhost:3306/数据库?characterEncoding=utf8
> 2.  useUnicode=true ：使用unicode编码 characterEncoding=utf8 :字符编码设为utf-8 useSSL=false :不使用ssl协议，不写容易出警告

**4. 定义sql**

**5. 获取执行sql语句的对象 Statement**

Statement对象用于将 SQL 语句发送到数据库中，或者理解为执行sql语句

//第一种 可能出现sql注入问题
     Statement stmt = conn.createStatement();
     //第二种 可以解决sql注入问题
     PreparedStatement pstmt =conn.prepareStatement(sql);
     pstmt.setString(1, username);
     pstmt.setString(2, pwd);

**6. 发送并执行SQL语句, 得到结果(查询会得到ResultSet, 增删改会得到整数, 表示影响的行数)**

//发送SQL语句, 得到结果ResultSet
    ResultSet rs = stmt.executeQuery(sql);
     // 发送执行, 得到结果, 代表受影响的行数, 0表示操作无效
    int rows = stmt.executeUpdate(sql);

**7. 处理结果**

**8. 释放资源(ResultSet, Statement, Connection)**

1.  需要释放的对象：ResultSet 结果集，Statement 语句，Connection 连接
2.  释放原则：先开的后关，后开的先关。ResultSet–> Statement–> Connection
3.  放在哪个代码块中：finally 块

**注意**

*  作为一种好的编程风格，应在不需要Statement对象和Connection对象时显式地关闭它们。关闭Statement对象和Connection对象的语法形式为：`public void close() throws SQLException`
 *  用户不必关闭ResultSet。当它的 Statement 关闭、重新执行或用于从多结果序列中获取下一个结果时，该ResultSet将被自动关闭。
 *  注意：要按先ResultSet结果集，后Statement，最后Connection的顺序关闭资源，因为Statement和ResultSet是需要连接是才可以使用的，所以在使用结束之后有可能其他的Statement还需要连接，所以不能先关闭Connection。

## 3.2 JDBC实现DDL(数据定义语言) ##

**建表和数据库**

-- 创建database
    create database db_demo charset=utf8;
    -- 切换数据库
    use db_demo;
    -- 创建表格
    -- Integer是int的别名，相当于int(11)
    -- comment ''  注释
    -- date:年月日  datetime:年月日时分秒
    create table emp (
    	empno Integer primary key comment '编号',
    	ename varchar(20) not null comment '姓名',
    	job varchar(20) comment '职位',
    	mgr Integer comment '领导编号',
    	hiredate date comment '入职日期',
    	sal double(7,2) comment '薪水',
    	comm double(7,2) comment '提成',
    	deptno Integer
    );
    -- 录入测试数据
    insert into emp values (1111, 'KING', 'PRESIDENT', null, '1990-02-06', 5000, null, 10),
    	(1112, 'SMITH', 'CLERK', 1111, '1999-12-26', 2000, null, 10),
    	(1113, '张三', 'CLERK', 1112, '1999-12-16', 1000, 200, 10);
    
    select * from emp;
    
    -- 截断表格, 相当于先删除表, 再重新创建
    truncate table tb_user;

**jdbc代码**

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /** * 创建一张学生表 */
    public class Demo4DDL { 
        public static void main(String[] args) { 
            //1. 创建连接
            Connection conn = null;
            Statement statement = null;
            try { 
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day24", "root", "root");
                //2. 通过连接对象得到语句对象
                statement = conn.createStatement();
                //3. 通过语句对象发送 SQL 语句给服务器
                //4. 执行 SQL
                statement.executeUpdate("create table student (id int PRIMARY key auto_increment, " +
                        "name varchar(20) not null, gender boolean, birthday date)");
                //5. 返回影响行数(DDL 没有返回值)
                System.out.println("创建表成功");
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            //6. 释放资源
            finally { 
                //关闭之前要先判断
                if (statement != null) { 
                    try { 
                        statement.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                    8 / 21
                }
                if (conn != null) { 
                    try { 
                        conn.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
            }
        }
    }

## 3.3 JDBC实现DQL(数据查询语言) ##

import java.sql.*;
    
    public class TestQuery { 
        public static void main(String[] args) throws Exception { 
            // [0] 准备工作
            String url = "jdbc:mysql://127.0.0.1:3306/db_demo?useUnicode=true&characterEncoding=utf8&useSSL=false"; // 定位数据库安装的位置
            String user = "root";
            String password = "root";
            String sql = "select * from emp";
            // [1] 注册驱动, 让虚拟机加载驱动类com.mysql.jdbc.Driver(全限定路径: 包.类)
            Class.forName("com.mysql.jdbc.Driver");
            // [2] 获取连接对象Connection
            Connection conn = DriverManager.getConnection(url, user, password);
            // [3] 基于连接对象获取发送器Statement
            Statement stmt = conn.createStatement();
            // [4] 发送SQL语句, 得到结果ResultSet
            ResultSet rs = stmt.executeQuery(sql);
            // [5] 处理结果
            while(rs.next()) { 
                int empno = rs.getInt("empno"); // 列索引从1开始, 写0就错了
                String ename = rs.getString("ename");
                Date hiredate = rs.getDate("hiredate");
                double sal = rs.getDouble("sal");
                System.out.println(empno + ":" + ename + ":" + hiredate + ":" + sal);
                System.out.println("-------------------------------");
            }
            // [6] 关闭资源, 先开的后关
            rs.close();
            stmt.close();
            conn.close();
        }
    }

## 3.4 JDBC实现DML(数据操作语言:增删改) ##

1.  创建连接对象
2.  创建 Statement 语句对象
3.  执行 SQL 语句：executeUpdate(sql)
4.  返回影响的行数
5.  释放资源

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /** * 上下移动: Ctrl + Shift + ↑/↓ * .var: 接收变量 * .try: 异常捕获 * .for: 遍历集合 * * 模拟登录操作 * */
    public class TestDml { 
        public static void main(String[] args) { 
            // 声明相关信息
            Connection conn = null;
            Statement stmt = null;
            String url = "jdbc:mysql://localhost:3306/db_demo?useUnicode=true&characterEncoding=utf8&useSSL=false";
            String user = "root";
            String password = "root";
            // String sql = "insert into tb_user values (default, 'lisi', '123', '李四')";
            // String sql = "update tb_user set password='456' where id=2";
            String sql = "delete from tb_user where id=2";
            try { 
                // 注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                // 获取连接
                conn = DriverManager.getConnection(url, user, password);
                // 发送器
                stmt = conn.createStatement();
                // 发送执行, 得到结果, 代表受影响的行数, 0表示操作无效
                int rows = stmt.executeUpdate(sql);
                // 处理结果
                if(rows > 0){ 
                    System.out.println("操作成功!");
                } else { 
                    System.out.println("操作失败!");
                }
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                // 关闭资源
                try { 
                    if(null != stmt) { 
                        stmt.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                try { 
                    if(null != conn) { 
                        conn.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

## 3.5 JDBC实现登录功能(解决sql注入) ##

### 1.使用Statement(未注意sql注入问题) ###

**缺陷：存在SQL注入风险**

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

![在这里插入图片描述][20190522152346961.png]

问题分析：

![在这里插入图片描述][20190522152403330.png]  
我们让用户输入的密码和 SQL 语句进行字符串拼接。用户输入的内容作为了 SQL 语句语法的一部分，改变了原有 SQL 真正的意义，以上问题称为 **SQL 注入**。要解决 SQL 注入就不能让用户输入的密码和我们的 SQL 语句进行简单的字符串拼接。

import java.sql.*;
    import java.util.Scanner;
    
    public class Login { 
        public static void main(String[] args) { 
            String user = "root";
            String password = "12345";
            String url = "jdbc:mysql://127.0.0.1:3306/db_demo?useUnicode=true&characterEncoding=utf8&useSSL=false";
            Connection conn = null;
            Statement stmt = null;   
            ResultSet rs = null;
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入用户名:");
            String uname = sc.nextLine();
            System.out.println("请输入密码:");
            String pwds = sc.nextLine();
    
            String sql = "select count(*) from tb_user where username='" + uname + "' and password='" + pwds + "'";
    
            try { 
                Class.forName("com.mysql.jdbc.Driver");
                // [2] 获取连接对象Connection
                conn = DriverManager.getConnection(url, user, password);
                // [3] 基于连接对象获取发送器Statement
                stmt = conn.createStatement();
                // [4] 发送SQL语句, 得到结果ResultSet
                rs = stmt.executeQuery(sql);
                if (rs.next()) { 
                    int count=rs.getInt(1);
                    if(count==0){ 
                        System.out.println(sql);
                        System.out.println("用户名密码错误,请重新登录！");
                    }else{ 
                        System.out.println(sql);
                        System.out.println("登录成功!");
                    }
    
                }
    
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                // 关闭资源
                try { 
                    if (null != stmt) { 
                        rs.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                try { 
                    if (null != stmt) { 
                        stmt.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                try { 
                    if (null != conn) { 
                        conn.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

### 2.使用PreparedStatement(解决sql注入问题) ###

为了解决sql注入问题，需要采用PreparedStatement。

public class Login { 
        public static void main(String[] args) { 
            // 创建扫描对象Scanner
            Scanner sc = new Scanner(System.in);
            // 接收用户输入的用户名和密码
            System.out.println("请输入用户名和密码进行登录: ");
            String username = sc.nextLine();
            String pwd = sc.nextLine();
            // 声明相关信息
            String url = "jdbc:mysql://localhost:3306/db_demo?useUnicode=true&characterEncoding=utf8&useSSL=false";
            String user = "root";
            String password = "root";
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            // SQL语句中的占位符, ?
            String sql = "select count(*) from tb_user where username=? and password=?";
            System.out.println("sql = " + sql);
            try { 
                // 注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                // 获取连接
                conn = DriverManager.getConnection(url, user, password);
                // 发送器
                pstmt = conn.prepareStatement(sql);
                // 为sql绑定参数
                pstmt.setString(1, username);
                pstmt.setString(2, pwd);
                // 执行得到结果集
                // rs = stmt.executeQuery(sql);
                rs = pstmt.executeQuery();// 不要将sql传递了
                // 处理结果集
                if(rs.next()) { 
                    int count = rs.getInt(1);
                    if(count == 0) { 
                        System.out.println("用户名密码错误, 请重新登录!");
                    } else { 
                        System.out.println("登录成功!");
                    }
                }
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                // 关闭资源
                try { 
                    if(rs != null){ 
                        rs.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                try { 
                    if(pstmt != null){ 
                        pstmt.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                try { 
                    if(conn != null){ 
                        conn.close();
                    }
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

## 3.6 数据库工具类DBUtil(使用Properties来存储配置信息) ##

**db.Properties**

# mysql连接相关信息
    db.driver=com.mysql.jdbc.Driver
    db.user=root
    db.password=12345
    db.url=jdbc:mysql://localhost:3306/db_oa?useUnicode=true&characterEncoding=UTF8&useSSL=false

**DBUtil.java**

import java.io.IOException;
    import java.sql.*;
    import java.util.Properties;
    
    /** * JDBC操作工具类, 提供注册驱动, 连接, 发送器, 动态绑定参数, 关闭资源等方法 * jdbc连接参数的提取, 使用Properties进行优化(软编码) */
    public class DBUtil { 
        private static String driver;
        private static String url;
        private static String user;
        private static String password;
    
        static { 
            // 借助静态代码块保证配置文件只读取一次就行
            // 创建Properties对象
            Properties prop = new Properties();
            try { 
                // 加载配置文件, 调用load()方法
                // 类加载器加载资源时, 去固定的类路径下查找资源, 因此, 资源文件必须放到src目录才行
                prop.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
                // 从配置文件中获取数据为成员变量赋值
                driver = prop.getProperty("db.driver").trim();
                url = prop.getProperty("db.url").trim();
                user = prop.getProperty("db.user").trim();
                password = prop.getProperty("db.password").trim();
                // 加载驱动
                Class.forName(driver);
            } catch (IOException e) { 
                e.printStackTrace();
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            }
        }
        /** * 统一关闭资源 * * @param rs * @param stmt * @param conn */
        public static void close(ResultSet rs, Statement stmt, Connection conn) { 
            try { 
                if(rs != null){ 
                    rs.close();
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            try { 
                if(stmt != null){ 
                    stmt.close();
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            try { 
                if(conn != null){ 
                    conn.close();
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            }
        }
    
        /** * 动态绑定参数 * * @param pstmt * @param params */
        public static void bindParam(PreparedStatement pstmt, Object... params) { 
            try { 
                for (int i = 0; i < params.length; i++) { 
                    pstmt.setObject(i + 1, params[i]);
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            }
        }
    
        /** * 预处理发送器 * * @param conn * @param sql * @return */
        public static PreparedStatement getPstmt(Connection conn, String sql) { 
            PreparedStatement pstmt = null;
            try { 
                pstmt = conn.prepareStatement(sql);
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return pstmt;
        }
    
        /** * 获取发送器的方法 * * @param conn * @return */
        public static Statement getStmt(Connection conn) { 
            Statement stmt = null;
            try { 
                stmt = conn.createStatement();
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return stmt;
        }
        /** * 获取数据库连接的方法 * * @return */
        public static Connection getConn() { 
            Connection conn = null;
            try { 
                conn = DriverManager.getConnection(url, user, password);
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            return conn;
        }
    }

## 3.7 反射封装查询,修改的统一方法 ##

import java.lang.reflect.Method;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    /** * 当前dao用于封装查询和更新的统一方法, 要求所有其他dao应该继承 */
    public class BaseDao { 
        /** * 统计数量的方法 * * @param sql * @param params * @return */
        public int count(String sql, Object... params) { 
            Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
            DBUtil.bindParam(pstmt, params);
            ResultSet rs = null;
            try { 
                rs = pstmt.executeQuery();
                if (rs.next()) { 
                    return rs.getInt(1);
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                DBUtil.close(rs, pstmt, conn);
            }
            return 0;
        }
    
        /** * 查询单个数据的统一方法 * * @param cls - 要查询的对象的Class类型 * @param sql - 要执行的sql语句 * @param params - sql语句要绑定的参数 * @return */
        public <E> E queryOne(Class<E> cls, String sql, Object... params) { 
            List<E> list = queryList(cls, sql, params);
            return list.size() > 0 ? list.get(0) : null;
        }
    
        /** * 查询多条数据的统一方法 * * @param cls - 要查询的对象的Class类型 * @param sql - 要执行的sql语句 * @param params - sql语句要绑定的参数 * @return */
        public <E> List<E> queryList(Class<E> cls, String sql, Object... params) { 
            List<E> list = new ArrayList<>();
    
            Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
            DBUtil.bindParam(pstmt, params);
            ResultSet rs = null;
    
            try { 
                rs = pstmt.executeQuery();
                // 获取数据库表格元数据
                ResultSetMetaData metaData = rs.getMetaData();
                while (rs.next()) { 
                    // 通过反射创建对象
                    E bean = cls.newInstance();
                    // 为对象的属性赋值, 有几列就调用几次set方法
                    for (int i = 1; i <= metaData.getColumnCount(); i++) { 
                        // 一列一列的获取
                        String columnLabel = metaData.getColumnLabel(i);
                        // 通过反射获取属性的类型
                        Class<?> type = cls.getDeclaredField(columnLabel).getType();
                        // 基于属性拼接set方法 empno --> setEmpno
                        String setMethodName = "set" + columnLabel.substring(0, 1).toUpperCase() +
                                columnLabel.substring(1);
                        // 通过反射获取set方法
                        Method method = cls.getMethod(setMethodName, type);
                        // 调用set方法为属性赋值
                        method.invoke(bean, rs.getObject(columnLabel));
                    }
                    // 将对象加入list集合
                    list.add(bean);
                }
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                DBUtil.close(rs, pstmt, conn);
            }
    
            return list;
        }
    
        /** * 执行DML操作的统一方法 * * @param sql * @param params * @return */
        public int update(String sql, Object... params) { 
            Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
            DBUtil.bindParam(pstmt, params);
            try { 
                return pstmt.executeUpdate();
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                DBUtil.close(null, pstmt, conn);
            }
            return 0;
        }
    }

# 4.JDBC事务的处理 #

1.  **事务**：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。
    
     *  事务的四大特性
        
        1.  A, Atomicity, 原子性, 一次事务中的多个操作不可分割
        2.  C, Consistency, 一致性, 事务操作的前后要保持数据的一致性
        3.  I, Isolation, 隔离性, 多个事务之间相互隔离, 不会干扰
        4.  D, durability, 持久性, 事务一旦被提交, 数据永久保存到数据库.
     *  **JDBC中, 事务默认自动提交, 不安全**.
2.  **使用Connection对象来管理事务**
    
    1.  开启事务 `setAutoCommit(boolean autoCommit)`调用该方法设置参数为false，即开启事务
        
         *  在执行sql之前开启事务.如果设置为 false，表示关闭自动提交，相当于开启事务
    2.  提交事务：`commit()`
        
         *  当所有sql都执行完提交事务
    3.  回滚事务：`rollback()`
        
         *  在catch中回滚事务
3.  在JDBC中，事务操作缺省是自动提交。一条对数据库的更新表达式代表一项事务操作操作成功后，系统将自动调用commit()提交，否则调用rollback()回滚
4.  在JDBC中，事务操作方法都位于接口java.sql.Connection中。可以通过调用setAutoCommit(false)来禁止自动提交。之后就可以把多个数据库操作的表达式作为一个事务，在操作完成后调用commit()来进行整体提交，倘若其中一个表达式操作失败，都不会执行到commit()，并且将产生响应的异常；此时就可以在异常捕获时调用rollback()进行回滚,回复至数据初始状态
5.  事务结束的边界是commit或者rollback方法的调用
6.  事务开始的边界则不是那么明显了，它会开始于组成当前事务的所有statement中的第一个被执行的时候。

import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class TestTx { 
        public static void main(String[] args) { 
            // 定义sql
            String sql1 = "update tb_account set balance=balance-100 where id=1";
            String sql2 = "update tb_account set balance=balance+100 where id=4";
            // 数据库连接
            Connection conn = DBUtil.getConn();
            // 发送器
            Statement stmt1 = DBUtil.getStmt(conn);
            Statement stmt2 = DBUtil.getStmt(conn);
            try { 
                // 关闭事务的自动提交功能
                conn.setAutoCommit(false);
                // 执行转账操作
                int rows1 = stmt1.executeUpdate(sql1);
                int rows2 = stmt2.executeUpdate(sql2);
                // 判断是否成功
                if(rows1 == 1 && rows2 == 1) { 
                    // 成功时手动提交事务
                    conn.commit();
                    System.out.println("转账成功!");
                } else { 
                    // 失败事务回滚
                    conn.rollback();
                    System.out.println("转账失败!");
                }
            } catch (SQLException e) { 
                // 发生异常时要事务回滚
                try { 
                    if(conn != null) { 
                        conn.rollback();
                    }
                } catch (SQLException e1) { 
                    e1.printStackTrace();
                }
                e.printStackTrace();
            } finally { 
                DBUtil.close(null, stmt2, null);
                DBUtil.close(null, stmt1, conn);
            }
        }
    }

[20190522152346961.png]: /images/20220129/4feb0f77c4f643b3a8750968fd8943a1.png
[20190522152403330.png]: /images/20220129/c38f20d4915f4b0b806673beadd2828b.png