Java JDBC 基础知识总结

野性酷女 2023-02-21 06:52 37阅读 0赞

**JavaSE中：**

*  数组、集合、对象----可以理解为小容器  
    产生的数据都在内存中的临时空间中，数据保存不了，会丢失
 *  文件----永久性存储在硬盘上，数据持久化
 *  I/O----读取文件中的数据、内容  
    JAVA内存----桥梁----硬盘

**数据库：**

*  可以理解为数据的本质就是通过I/O读写文件
 *  我们做的事情就变少了
 *  只需要发送简单的SQL指令，解析、读取、写入文件、分析
 *  数据是为了今后做逻辑准备，给用户查看使用  
    用户提供的数据，我们处理好写回数据库中

# JDBC概述 #

全称：`Java DataBase Connectivity`

可以理解为以前学的Socket桥梁

确切的说JDBC是Java平台提供的一种统一的执行规范/标准（接口），每一个厂商生产的数据库产品都实现了此规范

![在这里插入图片描述][20190624104144936.png]  
我们要找到不同的数据库厂商实现的规范，即**JDBC驱动**

JDBC驱动类型：

*  `JDBC-ODBC`每一个客户机器都需要安装，很耗费空间，管理也不方便，现在很少用了，现在基本上只在实验室出现了
 *  `JDBC-Native`类似第一种，也需要每一个客户机器安装，但是调用的是本地原生c/c++，具体到某一个数据库，如果想要改变，需要改变底层实现，提高了很多性能，但是不灵活
 *  `JDBC-Net`使用了标准的Socket通信，不需要每一个客户机器都进行安装，相对比较灵活
 *  **纯JavaJDBC**一个纯粹的基于Socket进行的通信，不需要安装客户机上，每一个数据库的厂商自身提供的  
    **现在的驱动基本都是这种类型**

## 适合MySQL数据库的JDBC驱动包 ##

下载驱动包，去官方网站`www.mysql.com`中去找  
[先进入到这个网站][Link 1]  
![在这里插入图片描述][20190624110012562.png]  
MySQL有不同的版本，我下载的版本是MySQL8.0  
MySQL的驱动包也跟着MySQL有不同的版本，可以下载最新的版本，也可以下载5.7版本的

下图是下载最新版本的  
![在这里插入图片描述][20190624110431303.png]  
我的MySQL版本是最新版本的前一个版本  
如果想下载前面版本的，先点击上图中的`Looking for previous GA versions？`  
![在这里插入图片描述][20190624110722294.png]  
（注意，这两个版本的驱动包的用法有些微的不同）

# 使用JDBC操作数据库 #

先玩一下mysql-connector-java-5.1.47这个版本的驱动包：

解压之后，打开文件夹  
![在这里插入图片描述][20190624121107557.png]  
把上图中我选中的jar文件等会复制到我在idea创建的项目中

## 一：JDBC六步曲 ##

1.  导包（将找到的这个jar文件包导入到Java工程中）  
    具体步骤，看下面的图
2.  加载驱动类`Driver`  
    `class.forName("com.mysql.jdbc.Driver");`
3.  获取连接（可以理解为以前学的Socket）  
    `Connection connection = DriverManager.getConnection(url,user,password);`  
    `url`连接数据库，写法：`"jdbc:数据库名://ip:port/database名"`  
    `user`数据库的账户名  
    `password`数据库的账户密码
4.  创建状态参数（可以理解为以前Socket中的流）  
    `Statement stat = connection.createStatement;`
5.  执行数据库操作
    
     *  Update：DDL语句和DML语句中的`insert`、`delete`、`update`（对数据库进行更新）  
        `int count = stat.executeUpdate(sql);`返回值是数据库更新的行数，有需要就接收
     *  Query：DML语句中的`select`，即DQL（数据库没有发生变化，我需要处理结果）  
        `stat.executeQuery(sql);`
6.  关闭  
    `close()`

先在项目中创建一个文件夹`lib`，把驱动包复制后放到这个文件夹中  
![在这里插入图片描述][2019062417362313.png]  
然后还做一个设置（关联工程和这个包，这个驱动包才能好使）

**File --> Project Structure --> Libraries --> 点击中间的+ --> 选择Java --> 选择jar文件路径**

接下来的步骤操作用的表格是study数据库中的emp表  
![在这里插入图片描述][20190624175106889.png]

create database study;
    use study;
    
    create table emp(
    empno int(10) comment '编号',
    ename varchar(12) comment '姓名',
    job varchar(20) comment '职务',
    mgr int(10) comment '上级',
    hiredate date comment '入职日期',
    sal int(10) comment '月薪',
    comm int(10) comment '佣金',
    depno int(5) comment '部门'
    );
    insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
    insert into emp values(7499,'allen','salesman',7698,'1981-2-20',1600,300,30);
    insert into emp values(7521,'ward','salesman',7698,'1981-2-22',1250,500,30);
    insert into emp values(7566,'jones','manager',7839,'198-4-2',2975,null,20);
    insert into emp values(7654,'martin','salesman',7698,'1981-9-28',1250,1400,30);
    insert into emp values(7698,'blake','manager',7839,'1981-5-1',2850,null,30);
    insert into emp values(7782,'clark','manager',7839,'1981-6-9',2450,null,10);
    insert into emp values(7788,'scott','analyst',7566,'1987-4-19',3000,null,20);
    insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
    insert into emp values(7844,'turner','salesman',7698,'1981-9-8',1500,0,30);
    insert into emp values(7876,'adams','clerk',7788,'1987-5-23',1100,null,20);
    insert into emp values(7900,'james','clerk',7698,'1981-12-3',950,null,30);
    insert into emp values(7902,'ford','analyst',7566,'1981-12-3',3000,null,20);
    insert into emp values(7934,'miller','clerk',7782,'1982-1-23',1300,null,10);

## 二：Update操作（使用的5.x驱动包） ##

以下操作中SQL语句是DML中的`insert`

import java.sql.*;
    
    public class Update { 
        public static void main(String[] args){ 
            try { 
                //1.导包
                //2.加载驱动类
                String className = "com.mysql.jdbc.Driver";
                Class.forName(className);//加载类 类中的静态元素就执行啦
                //3.获取连接
                String url = "jdbc:mysql://localhost:3306/study"; //jdbc:mysql://ip:port/database名
                String user = "root";
                String password = "root";
                Connection conn = DriverManager.getConnection(url,user,password);
                //4.创建状态参数
                Statement stat = conn.createStatement();
                //5.执行操作
                String sql = "insert into emp(empno,ename) values(9999,'zgh')";
                stat.executeUpdate(sql);
                System.out.println("执行完毕");
                //6.关闭
                stat.close();
                conn.close();
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            }
        }
    }

执行后，再查看emp表格：  
![在这里插入图片描述][20190624175953915.png]

将SQL语句改为DML中的`update`  
![在这里插入图片描述][20190624180453694.png]  
再查表：  
![在这里插入图片描述][2019062418051746.png]  
将SQL语句改为DML中的`delete`  
![在这里插入图片描述][20190624180602402.png]  
再查表：  
![在这里插入图片描述][20190624180621710.png]  
再把SQL语句改为一个DDL语句试一下：  
![在这里插入图片描述][20190624181227941.png]  
再看表：  
![在这里插入图片描述][20190624181251744.png]

## 三：Query操作（使用的8.x驱动包） ##

加载驱动包的时候

*  驱动包8.x和5.x版本不一致，可能会出现问题
 *  Driver类的全名：
 *  5.x是`com.mysql.jdbc.Driver`
 *  8.x是`com.mysql.cj.jdbc.Driver`
 *  还有：
 *  在8.x版本中加载了新的属性`Time Zone`时区
 *  url：
 *  5.x版本`jdbc:mysql://ip:port/database名`
 *  8.x版本`jdbc:mysql://ip:port/database名?serverTimezone = CST`我国时区是东八区，即CST

![在这里插入图片描述][20190624183059563.png]  
**（1）File --> Project Structure --> Libraries --> 点击中间的+ --> 选择Java --> 选择jdbc8.x的jar文件路径**  
**（2）如果 File --> Project Structure --> Libraries 已经关联过其余版本的jdbc驱动包，请将其去除**

--------------------

**在执行Query操作时，数据库没有发生真实变化，我们需要对得到的结果做处理，肯定需要返回值**

**在emp表中，一行有8个值，这一堆的返回值该怎么存呢？先来猜测一下jdbc开发者的想法**  
有以下结构可以考虑：

*  数组\[8\] Object\[8\]
 *  集合
    
     *  List
     *  Set
     *  Map<String,Object>
 *  对象（对象里面设置8个属性）

1.  对象虽然好，但是数据库中的数据类型，开发者是不知道的，没有选择这个
2.  然后，Map集合是可读性最好的  
    所以在底层中，一行记录是存储在Map集合中的
    
     *  empno=7369 ename=smith job=clerk mgr=7902 hiredate=1980-1-1 sam=800 comm=null deptno=10
3.  但是又有问题来了？如果查询的结果不止一条怎么办？  
    将Map集合放在Set集合中即可 Set<Map<String,Object>>
    
     *  一行记录–>Map集合; 14行记录–>14Map集合
     *  Set<map>
     *  set目的是为了存储好多行记录，每一行是一个map对象
     *  map目的是为了存储一行中多个单元格的值：key列名，value单元格查询出来

--------------------

先看一下emp表各列的属性：  
![在这里插入图片描述][20190624190115160.png]

import java.sql.*;
    
    public class Query{ 
        public static void main(String[] args) { 
            try { 
                //1.导包
                //2.加载驱动类
                String className = "com.mysql.cj.jdbc.Driver";
                Class.forName(className);
    
                //3.获取链接
                String url = "jdbc:mysql://localhost:3306/study?serverTimezone=CST";
                String user = "root";
                String password = "root";
                Connection connection = DriverManager.getConnection(url,user,password);
    
                //4.创建状态参数
                Statement stat = connection.createStatement();
    
                //5.执行操作
                String sql = "select * from emp where empno = 7369";
                ResultSet rs = stat.executeQuery(sql);
                while(rs.next()){ //判断是否有下一个元素
                    int empno = rs.getInt("empno");
                    String ename = rs.getString("ename");
                    Date hiredate = rs.getDate("hiredate");
                    float sal = rs.getFloat("salary");
                    System.out.println(empno + "--" + ename + "--" + hiredate + "--" + sal);
                }
                System.out.println("执行完毕");
    
                //6.关闭
                stat.close();
                connection.close();
    
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            }
        }
    }

运行结果：  
![在这里插入图片描述][20190624190630753.png]

我把SQL语句改一下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1poYW5nZ3VvaGFvNjY2_size_16_color_FFFFFF_t_70]  
运行结果：  
![在这里插入图片描述][20190624190914439.png]

再补充几点：  
![在这里插入图片描述][20190624191058868.png]  
还有：  
![在这里插入图片描述][20190624191258512.png]

## 四：加载驱动类Driver的多种写法（jdbc8.0.15版本） ##

JDBC六部曲中的加载驱动类`Driver`到底是怎么一回事？

**一般而言我们使用`Class.forName();`语句来加载驱动；可以看出我们需要的是类中的静态成员**

> static 特征修饰符，修饰属性、方法、块、内部类，在类加载时就出现了，并且只加载一次，全部类对象共享，通过类名可以调用

看一下Driver类的源码（版本：jdbc8.0.15）：  
![在这里插入图片描述][20190624192230944.png]  
发现，`Class.forName();`加载Driver类的时候，因为没有创建对象，所以没有执行构造函数

但是，类的所有静态成员都执行了  
Driver类中有一个静态块，里面就执行了这么一句`DriverManager.registerDriver(new Driver());`

所以加载驱动的语句可以改为`DriverManager.registerDriver(new Driver());`

然后再看看这个语句的参数`new Driver()`，这是创建Driver对象，那么它的静态块一定会执行，  
![在这里插入图片描述][20190624193121556.png]  
所以，加载驱动类的语句改为`new Driver();`更加好

其实加载驱动类还有另一种语句可以做到

System.setProperty("jdbc.driver","com.mysql.cj.jdbc.Driver");

所以，加载驱动类有五种语句可以达到目的

1.  通过反射机制  
    `Class.forName("com.mysql.cj.jdbc.Driver");`
2.  通过DriverManager静态方法注册驱动  
    `DriverManager.registerDriver(new Driver());`
3.  通过直接创建驱动对象  
    `new Driver();`
4.  通过System类中设置属性值来加载  
    `System.setProperty("jdbc.driver","com.mysql.cj.jdbc.Driver");`  
    重要的是第二个参数，第一个参数可以随便写的（不和Java System的预定义属性名冲突即可）
5.  其实不写也可以

因为DriverManager这个类会帮你默认加载驱动类（但是它要一个个读取，性能慢）  
![在这里插入图片描述][20190624194102431.png]  
5种方式，1和4是最灵活的，不写是最不好的

## 四补充：加载驱动类Driver的多种写法（jdbc8.0.20版本） ##

**最近下载JDBC8.0.20版本，发现加载驱动类的源码进行了修改，第二种方法无法使用**

加载驱动类有四种语句可以达到目的

1.  通过反射机制  
    `Class.forName("com.mysql.cj.jdbc.Driver");`
2.  通过直接创建驱动对象  
    `new Driver();`
3.  通过System类中设置属性值来加载  
    `System.setProperty("jdbc.driver","com.mysql.cj.jdbc.Driver");`
4.  不写

## 五：事务操作 ##

1.  JDBC中操作事务，在获取连接那一步
2.  JDBC默认的开启事务，默认位自动提交事务
3.  设置手动提交事务`conn.setAutoCommit(false);`
    
     *  false，表示手动提交
     *  true默认，表示自动提交
4.  设置事务的隔离级别  
    ![在这里插入图片描述][20200701003124318.png]
5.  事务的提交

conn.commit();

1.  事务的回滚

conn.rollback();

1.  事务中保存还原点

Savepoint s = conn.setSavepoint("x");
    connection.rollback(s);

# PreparedStatement使用 #

本部分内容与字符串的拼接有关

## SQL注入 ##

所谓的SQL注入，意思就是通过SQL命令拼接其他的字符串，让其他的那些字符串来改变原有SQL语句的执行，最终达到欺骗服务器的效果；并且里面拼接的其他字符，肯定是SQL语法认可的合法的字符。

**看一个例子：**  
![在这里插入图片描述][20200708135914409.png]  
如果要查询上表三行记录中的某一行记录，在JDBC六部曲中若使用的是状态参数 `Statement stat = connection.createStatement;` 那么SQL语句就需要拼接字符串：

String sql = "select * from atm where aname='"+ 变量1 +"' and apassword='"+ 变量2 +"'";

那么，SQL注入就有空子可以钻了

如果用户输入的变量1为`xxx`，变量2为`xxx' or '1' = '1`，你觉得如上sql语句会执行吗？

select * 
    from atm 
    where aname = 'xxx' and apassword = 'xxx' or '1' = '1';

分析SQL语句中的where子句，`and`的优先级比`or`高：

where false or true

那么，这个SQL语句会查询整个atm表。

**SQL注入问题产生的原因？**

1.  代码逻辑判断不严谨导致的
2.  SQL语句问题，允许拼接字符串，应该认为用户很不安全
3.  可以利用PreparedStatement来处理SQL

## PreparedStatement ##

那么怎么解决SQL语句的问题呢？  
jdbc六部曲中`Statement`是没有办法的，但是换为`PreparedStatement`可以解决这个问题

Statement

1.  普通的状态参数
2.  创建时不需要SQL
3.  此时没有执行

PreparedStatement

1.  预处理状态参数
2.  创建时就**需要预先加载SQL语句**，**可以参数动态化**
3.  此时没有执行，但底层预先处理SQL需要查询的结果，**性能更高**
4.  而且可以利用**动态化进行参数的处理，利用`？`代替数据类型及值** ，可以有效防止SQl注入，还增强了可读性

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

*  预处理状态参数处理SQL语句中的`?`，索引是从1开始的

## 模糊查询（Java） ##

*  关键字：**like**

<table> 
 <thead> 
  <tr> 
   <th>通配符</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%</td> 
   <td>替代0个或多个字符</td> 
  </tr> 
  <tr> 
   <td>_</td> 
   <td>替代1个字符</td> 
  </tr> 
 </tbody> 
</table>

Java中，当 **PreparedStatement** 遇到模糊查询 **like**，一般处理SQL语句有两种方式：

**方式一（正常写SQL语句，赋值时候处理）**

String sql = "select * from 表格 where 列名 like ?;"

pstat.setString(1, "%"+变量+"%");

**方式二（将%放置在SQL语句里）**

String sql = "select * from 表格 where 列名 like \"%\" ? \"%\";"

pstat.setString(1, 变量);

*  SQL语句对于Java程序来说本身就是一个字符串，而 `?` 前后的两个 `"%"` 中的双引号是需要传到数据库的SQL语句中的
 *  而SQL语句本身的双引号和Java中String类型的双引号冲突，在Java中需要利用转义字符来帮忙
 *  `\"%\" ? \"%\"` 如此处理：预处理状态符会将变量的值赋给`?`，然后将变量的类型赋给`\"%\" ? \"%\"`

## 分页查询（Java） ##

> 分页查询为补充内容。  
> 分页查询的写法无论是 SQL 语句还是 JDBC（Java）基本一致，无需做特殊处理。

limit a,b

*  a 表示起始行索引（从0开始）
 *  b 表示需要查找的行数

# 多表联合查询（Java） #

Java中使用MVC分层架构思想，处理一张表格并不难：

<table> 
 <thead> 
  <tr> 
   <th>表格中的数据</th> 
   <th>Java中该如何储存</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>一张表格</td> 
   <td>domain类</td> 
  </tr> 
  <tr> 
   <td>一行记录</td> 
   <td>每一个对象</td> 
  </tr> 
 </tbody> 
</table>

**但若是遇到了多张表格，在Java中怎么处理表格之间的关系呢？**  
看一个例子：

emp表格的最后一列**depno**，有外键约束  
![在这里插入图片描述][20200709115917594.png]  
关联着dept表格的第一列  
![在这里插入图片描述][20200709115957972.png]  
显而易见，emp表格与dept表格是聚合关系，一对多

**那么，在Java中该如何处理拥有外键约束的列呢？**

**第一步**，在domain层中先将dept对应的实体类写出来：  
![在这里插入图片描述][202007091609086.png]

*  Dept实体类，中规中矩，并没有外键，如此写即可

**第二步**，在domain层中将emp对应的实体类写出来：  
![在这里插入图片描述][20200709161114699.png]

*  关键是对于 dept 属性的处理，由于其是另一个表格的外键，所以不能简简单单的将其数据类型定义为 `int`；而是要与 dept 表格进行映射，那么将其数据类型定义为 `Dept`对象 是非常合适的。

# 使用聚合函数进行分组查询（Java） #

![在这里插入图片描述][20200709165030113.png]  
**【例】查询emp表格每个部门的人数**

在MySQL中使用SQL语句查询非常简单：

select depno,count(depno) from emp group by depno;

那么，在Java中该如何处理 `count(depno)` 计算而来的值呢？

一般，我们在Java中存储表的值时：

但 `count(depno)` 不是原有的列名，是通过后续的方式计算出来的值，该怎么存储？

*  一个Map集合来存储信息----相当于是一个对象  
    ![在这里插入图片描述][20200709170438541.png]  
    调用它，并遍历查询出来的值  
    ![在这里插入图片描述][20200709170844651.png]

[20190624104144936.png]: https://img-blog.csdnimg.cn/20190624104144936.png
[Link 1]: https://dev.mysql.com/downloads/connector/
[20190624110012562.png]: https://img-blog.csdnimg.cn/20190624110012562.png
[20190624110431303.png]: https://img-blog.csdnimg.cn/20190624110431303.png
[20190624110722294.png]: https://img-blog.csdnimg.cn/20190624110722294.png
[20190624121107557.png]: https://img-blog.csdnimg.cn/20190624121107557.png
[2019062417362313.png]: https://img-blog.csdnimg.cn/2019062417362313.png
[20190624175106889.png]: https://img-blog.csdnimg.cn/20190624175106889.png
[20190624175953915.png]: https://img-blog.csdnimg.cn/20190624175953915.png
[20190624180453694.png]: https://img-blog.csdnimg.cn/20190624180453694.png
[2019062418051746.png]: https://img-blog.csdnimg.cn/2019062418051746.png
[20190624180602402.png]: https://img-blog.csdnimg.cn/20190624180602402.png
[20190624180621710.png]: https://img-blog.csdnimg.cn/20190624180621710.png
[20190624181227941.png]: https://img-blog.csdnimg.cn/20190624181227941.png
[20190624181251744.png]: https://img-blog.csdnimg.cn/20190624181251744.png
[20190624183059563.png]: https://img-blog.csdnimg.cn/20190624183059563.png
[20190624190115160.png]: https://img-blog.csdnimg.cn/20190624190115160.png
[20190624190630753.png]: https://img-blog.csdnimg.cn/20190624190630753.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1poYW5nZ3VvaGFvNjY2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190624190854938.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1poYW5nZ3VvaGFvNjY2,size_16,color_FFFFFF,t_70
[20190624190914439.png]: https://img-blog.csdnimg.cn/20190624190914439.png
[20190624191058868.png]: https://img-blog.csdnimg.cn/20190624191058868.png
[20190624191258512.png]: https://img-blog.csdnimg.cn/20190624191258512.png
[20190624192230944.png]: https://img-blog.csdnimg.cn/20190624192230944.png
[20190624193121556.png]: https://img-blog.csdnimg.cn/20190624193121556.png
[20190624194102431.png]: https://img-blog.csdnimg.cn/20190624194102431.png
[20200701003124318.png]: https://img-blog.csdnimg.cn/20200701003124318.png
[20200708135914409.png]: https://img-blog.csdnimg.cn/20200708135914409.png
[20190703132839822.png]: https://img-blog.csdnimg.cn/20190703132839822.png
[20200709115917594.png]: https://img-blog.csdnimg.cn/20200709115917594.png
[20200709115957972.png]: https://img-blog.csdnimg.cn/20200709115957972.png
[202007091609086.png]: https://img-blog.csdnimg.cn/202007091609086.png
[20200709161114699.png]: https://img-blog.csdnimg.cn/20200709161114699.png
[20200709165030113.png]: https://img-blog.csdnimg.cn/20200709165030113.png
[20200709170438541.png]: https://img-blog.csdnimg.cn/20200709170438541.png
[20200709170844651.png]: https://img-blog.csdnimg.cn/20200709170844651.png