JDBC使用Java操作数据库、索引和事务

瘦皮猴117

已于 2022-08-03 21:57:59 修改

阅读量811

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 database

于 2022-06-09 20:22:47 首次发布

本文链接：https://blog.csdn.net/XHT117/article/details/125171888

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1. 索引

索引背后的数据结构

MySQL的索引实现基于B+树的设计（N叉搜索树）

1. 索引

索引是一种特殊的文件，包含数据库中所有记录的引用。

类似于数组的引用下标，通过这个下标就能拿到数组中的数据。和数组中下标一样，通过下标寻找数组数据很快，通过索引去查找数据库中的数据也很快。经常查询的属性设置为索引。

为何需要索引？——查多改少

eg：索引和原数据表的关系，就相当于书的目录和书本的内容之间的关系，根据书本目录去查找特定内容，远比你拿一本书直接翻快的多。但是涉及到书本内容的修改、删除，还需要改目录。

索引提高查询数据的效率，但是会拖慢增，删，改的效率，创建索引也需要时间和空间的开销。

在test_user表中为id_number属性创建索引 id_idx
create index id_idx on test_user(id_number);
查询 id_number 为778899的用户信息
select * from test_user where id_number=778899;
为提供查询速度，创建 id_number 字段的索引
create index id_idx on test_user(id_number);
换一个身份证号查询，并比较执行时间
select * from test_user where id_number=776655;
在很少的常数的间之内就可以查询到数据。

索引背后的数据结构

高效查找数据的数据结构

BST：二分搜索树——>RBTree，查找的时间复杂度为logN。

当数据量非常大时，由于二叉树每个节点最多有两个子树，RBTree的高度很高，数据库的数据都在磁盘上存储，每当访问一个二叉树的节点都得读写一次磁盘，这样就非常慢。

哈希表：理论上O(1)找到数据，常数时间。
哈希表是查找数据是否等于指定数据，在区间上查找就不行了，无法处理范围查找查找。

MySQL的索引实现基于B+树的设计（N叉搜索树）

B树家族是严格平衡树，左右子树的高度差为0，左树和右树高度完全相等。

首先了解B-树（B树）

引入N叉搜索树后可以大大降低树的高度（相较于BST来说）

B-树的特点
1. 每个树节点上都有多个值。

2. 每个节点上的（分叉树）子树的个数，就是当前节点值的个数+1。

3. 保证子树中的节点值一定要处在父节点的范围之内。

B+树

特点：

1. B+树中，子节点中存在的最大值(最小值)是在父节已经出现过的值。这样做的目的就是最底层的叶子节点包含了整个数据的全集。

2. B＋树中,最底层的叶子节点使用链表连接。更高效率的区间查找。
3. 对于B+树来说，所有的数据都存储在叶子节点，非叶子节点只需要保存索引列的辅助信息(索引列的值)。非叶子节点占用空间小，可以直接放在内存中。减少磁盘l0。

聚簇索引

聚簇索引（主键索引，一个表只有一个聚簇索引）。

构建索引树上的每个节点，除了要保存索引列的信息，还需要保存这条记录的完整内容。

特点：查询速度快，一个表只有一个聚簇索引，保存的信息多，占用空间大。

select name from stu where id = 300;
根据聚簇索引找到id= 300的节点，就能拿到所有的列信息。只需要在聚簇索引树上查找即可。

非聚簇索引-二级索引

非聚簇索引（普通索引，唯一索引，index创建的都是非聚簇索引，一张表可以有多个非聚簇索引）。

索引树上的每个节点，除了保存索引列的信息之外，还需要保存该记录的行号（对应的主键id）。

select name from stu where k = 3;
根据非聚簇索引先在索引树上找到k =3的这条记录，然后这个节点内部保存了k = 3对应的主键id为300。还需要返回在聚簇索引树中找到id = 300的记录，才能拿到完整的值——回表查询。

特点：非聚簇索引相较于聚簇索引有更多的查表操作——更多的读写磁盘操作。

查询速度慢（只能找到主键信息，需要回表查询），一个表可以有多个非聚簇索引，保存的信息少，占用空间小。

存储引擎

MySQL数据库插件式的设计，每种索引在不同的存储引擎中的实现都有可能不同。

存储引擎：就是MySQL到底如何对数据进行增删查改的不同实现方案。
MySQL有多个存储引擎，如同一种汽车品牌可能有多种车型。

MySQL中最经典的两种存储引擎
MylSAM：MySQL5.5之前的默认存储引擎，不支持事务，性能比较高。
InnoDB：MySQL5.5之后的默认存储引擎，支持事务，性能不如MyISAM。

show variables like 'default_storage_engine';

2. 事务

所谓的事务：把若干个SQL操作打包为一个整体，实际执行的时候，这个整体要么全部执行，要么都不执行。

若执行的过程中出现了突发情况，某些操作执行不下去了，MySQL可以保证突发情况恢复之后，数据没有遭到破坏。

通过事务的"回滚"roll back操作，进行数据的还原(MySQL中的binlog文件实现，记录了所有表数据的修改动作)。

事务的应用场景-银行转账的场景

A向B转账 100$

update account set banlance = banlance - 100$ where name = 'A';
update account set banlance = banlance + 100$ where name = 'B';

转账之后很行系统崩溃，A的钱扣了，但是B没收到，没给B的账户转账。

此时需要事务，实际执行的时候，这个整体要么全部执行，要么都不执行。

事务的ACID特性

原子性atomicity，持久性isolation，隔离性durability，一致性consistency

原子性

原子性：事务中最核心的操作
一个事务中的所有操作，要么全部执行成功，要么全部执行失败。(执行失败之后，数据的恢复就通过rollback回滚)。

持久性

持久性：一个事务执行完成之后，这个事务对数据库的所有修改都有永久的(持久化，保存到磁盘上)，不会丢失。

隔离性

隔离性：多个并发事务访问数据库时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，不同事务之间相互隔离。

普通SQL：一个SQL中执行的操作，MySQL自身可以保证数据并发时的正确性（通过读写锁），若是事务，并发执行就会存在相应的问题。

并发：多个任务不一定是在同时执行的，也不一定就是在不同的CPU上执行。以前的单核处理器上的任务调度都属于并发执行。

例如：

所有任务并发执行，在不同的时间段执行不同的任务。

此时CPU的任务调度顺序为1234，让用户感觉好像是这两个程序，任务在同时进行其实在不同时间段执行了不同的任务。

并行：多个任务一定是在不同的CPU上同时执行
问题1：脏读

事务A在修改数据，事务B读取到了事务A修改后的数据，此时事务A进行了"回滚"，前面的修改不做数了，事务B读到的数据就是"脏"数据，这种情况称为脏读。

问题2：不可重复读

同一个事务在多次相同查询后得到的数据不同。

在不同的查询时，其他事务的修改对于本事务来说是可见的。

例如：
事务A: select name from stu;

//其他操作...

再次select name from stu;

问题3：幻读

每次查询得到的值都是相同的，其他事务的修改对于本事务不可见，明明事务中查询不到重复的信息，但是就是插入不进，称为幻读。

一致性

一致性：一个事务执行前后的数据都是一种合法性的状态，事务永远都是从一个一致性状态到另一个一致性状态。在执行事务前后，数据库的数据都是合理的。

假设执行更新操作之前，A账户余额1$，B账户余额300$，B向A转账100$。

没有事务，就有可能出现：A账户余额1$，B账户余额200$。数据不合理，二者的总余额应相等。

事务的三个关键字

开启事务，start transaction，后面的多个sql语句是一个整体。

回滚操作，rollback回滚了上次对数据库做的修改。
提交事务，就是把开启事务之后的所有SQL语句统一在数据库上进行持久化。

MySQL事务的四种隔离级别

1. 读未提交

处在该隔离级别的事务可以看到其他还没提交事务对数据库的修改——RU

造成脏读，不可重复读，幻读。

2. 读已提交——Oracle数据库默认

处在该隔离级别的事务可以看到其他已经提交事务对数据库的修改——RC的隔离级别
3.可重复读——lnnoDB引擎默认的隔离级别
一个事务一旦开启，在该隔离级别下，该事务提交之前，多次查询看到的结果是相同的。

无论其他事务如何修改数据库，在当前这个事务下都是不可见的。

4. 串行化

事务最高隔离级别，所有事务都串行访问数据库。不会发生冲突，不会产生任何事务的问题，但就没有并发执行了。

事务的隔离级别越高，安全性越强，并发性就越低。

练习：求不同隔离级别下v1，v2，v3的值

3. JDBC

Java操作数据库的规范

JDBC为多种关系数据库提供了统一访问方式，作为特定厂商数据库访问API的一种高级抽象，它主要包含一些通用的接口类。

java.sql：无论现在通过Java操作哪个具体的数据库，数据库厂商实现的驱动包都需要满足JDBC的标准(接口)。

不管是什么数据库，MySQL，SQLLite，Oracle都得实现JDBC的接口。
对于程序员来说，不管操作什么数据库都是相同的套路，只是更换了具体子类(驱动)。

*.jar就是把一系列编译好的class文件，打包压缩后产生的文件，其他程序引入这个jar包就有了这个包中的所有类，等同于别人写好的东西直接拿来用。

配置MySQL的IP和端口号

MySQL的驱动包背后都是一些网络请求，操作数据库其实就是在发起网络请求

JDBC相关代码

JDBC插入操作

package jdbc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

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

/**
 * Java操作MySQL数据库
 */
public class JDBCTest {
    public static void main(String[] args) throws SQLException {
        // 1.先获取数据源 - 数据是从哪个数据库来的
        MysqlDataSource dataSource = new MysqlDataSource();
        // 2.配置数据源的属性，用户名，密码，连接的IP地址，端口号
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/my_class_ds?CharacterEncoding=utf8&useSSL=false");
        // -u root
        dataSource.setUser("root");
        // -p 123456
        dataSource.setPassword("123456");
        // 3.获取数据库连接 - 根据上面的配置发起网络请求
        // java.sql.Connection 连接对象
        Connection connection = dataSource.getConnection();
        // 4.拼装SQL语句，就是String 对象
        String sql = "insert into user(username,password) values(?,?)";
        // 5.获取执行SQL的Statement对象，mysql中每一个sql的执行就对应一个Statement对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "小明");
        statement.setString(2, "123");
        // 6.执行这个SQL，增删改调用executeUpdate，返回受影响的行数
        int rows = statement.executeUpdate();
        System.out.println("插入成功，插入" + rows + "行");
        // 7.关闭资源
        connection.close();
    }
}

查询操作

package jdbc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

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

/**
 * 进行查询操作
 */
public class JDBCTest1 {
    public static void main(String[] args) throws SQLException {
        // 1.获取数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/my_class_ds?characterEncoding=utf8&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        // 2.获取连接对象，发起请求获取MySQL连接
        Connection connection = dataSource.getConnection();
        // 3.获取Statement对象，就是具体执行SQL的对象
        String sql = "select * from user where username = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "小明");
        // 4.执行SQL取得返回值，返回值存在ResultSet对象中
        ResultSet resultSet = statement.executeQuery();
        // 5.遍历结果集
        while (resultSet.next()) {
            // 获取查询结果集中列名为id的属性值，数据库的列是什么类型，咱就用Java中对应的类型来接收
            int id = resultSet.getInt("id");
            // 查询结果集中属性名为username的属性值
            String userName = resultSet.getString("username");
            // 查询结果集中属性名为password的属性值
            String password = resultSet.getString("password");
            System.out.println("id = " + id + ",userName = " + userName + ",password = " + password);
        }
        // 6.关闭资源
        resultSet.close();
        connection.close();
    }
}

小结

java.sql下的接口
1. 获取数据源 DataSource，配置连接地址，用户名，密码等；
2. 获取连接对象，就是发送网络清求，建立和数据库的连接Connection；

3. 获取执行SQL的对象PreparedStatement对象，封装的SQL语句；

4. 执行更新操作executeUpdate -> int 本次更新受影响的行数，

执行查询 executeQuery -> Result 结果集；
5. 遍历结果集ResultSet对象，每当调用一次next方法就从结果集中取出一行数据，

resule.getXX("列名") -> 获取本行数据的具体属性值；
6. 操作之后关闭资源.close()，resultSet，Connection对象。

获取数据源的两种方式

获取数据源有两种方式，一个是DataSource方式(实际用到的)，内部创建连接池；另外一种是DriverManager(不推荐使用)。

池：资源的重复利用。

当一个Connection对象调用close方法之后，不是真正关闭，这个对象会被DataSource回收，进入连接池。
若此时有别的用户需要建立连接，不是创建一个新的连接，若此时连接池中有空闲连接，直接使用此连接。

数据库连接的建立和销毁操作还是很耗时耗资源

1. DriverManager类来获取的 Connection 连接，是无法重复利用的，每次使用完以后释放资源时，通过connection.close()都是关闭物理连接。
2. DataSource提供连接池的支持。连接池在初始化时将创建一定数量的数据库连接，这些连接是可以复用的，每次使用完数据库连接，释放资源调用connection.close()都是将Conncetion连接对象回收。

执行SQL的对象的两种方式

具体执行SQL的对象的两种方式

1. PreparedStatement对象(推荐使用)，

2. Statement对象。

带条件、带参数的：select password from user where...

PreparedStatement对象的优点

SQL注入
伪代码：select name,password from user where name = , password == or select1 == 1;

返回true。

瘦皮猴117

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
JDBC使用Java操作数据库、索引和事务

1. 索引索引背后的数据结构MySQL的索引实现基于B+树的设计（N叉搜索树）首先了解B-树（B树）B+树聚簇索引非聚簇索引-二级索引存储引擎2. 事务事务的应用场景-银行转账的场景事务的ACID特性原子性持久性隔离性一致性事务的三个关键字MySQL事务的四种隔离级别3. JDBC配置MySQL的IP和端口号JDBC相关代码........................
复制链接

扫一扫