JDBC (二) jdbc高级

faramita_of_mine

于 2021-11-06 09:58:22 发布

阅读量315

点赞数 2

分类专栏： JDBC 文章标签：开发语言 JDBC JAVA

本文链接：https://blog.csdn.net/faramita_of_mine/article/details/121149040

版权

JDBC 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

jdbc 高级

本篇主要内容如下：

一、批处理

有时候我们需要执行大批量的增删改时，可以使用 jdbc 的批处理，它能有效的提高执行效率。实现批处理可以使用 Statement 与 PreparedStatement 完成，通常在使用中常用的 API 有三个 addBatch(sql)、executeBatch() 和 clearBatch()，分别完成 SQL 的追加、执行与清除。清除主要是为了当批处理数据量过大时导致内存泄露。
案例：

private static void t1() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("insert into user (name,password,age) values (?,?,18)");
    for (int i = 0; i < 50000; i++) {
        System.out.println(i);
        statement.setString(1, "name" + i);
        statement.setString(2, "123" + i);
        statement.addBatch();
        if (i % 1000 == 0) {//防止内存溢出
            statement.executeBatch();
            statement.clearBatch();
        }
    }
    statement.executeBatch();
    statement.close();
    connection.close();
}

练习：

1.使用 jdbc 的批量操作完成 user（name:admin，password：000000）…user（name：admin，password：999999）的数据插入。

参考代码：

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

public class JdbcTest {

    public static void main(String[] arg) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
        PreparedStatement statement = connection.prepareStatement("insert into user (name,password,age) values (?,?,18)");
        for (int i = 0; i < 1000000; i++) {
            System.out.println(i);
            statement.setString(1, "admin");
            statement.setString(2, String.format("%06d", i));
            statement.addBatch();
            if (i % 1000 == 0) {//防止内存溢出
                statement.executeBatch();
                statement.clearBatch();
            }
        }
        statement.executeBatch();
        statement.close();
        connection.close();
    }
}

二、事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部不成功。

例如：A——B 转帐，对应于如下两条 sql 语句

update account set money=money-100 where name=‘a’;

update account set money=money+100 where name=‘b’;

要保证这两句代码在任何情况下，要么一起成功要么一起失败，这时就需要使用 jdbc 事务。当 Jdbc 程序向数据库获得一个 Connection 对象时，默认情况下这个 Connection 对象会自动向数据库提交在它上面发送的 SQL 语句。若想关闭这种默认提交方式，让多条 SQL 在一个事务中执行，可使用下列语句：

connection.setAutoCommit(false); //关闭自动提交
connection.rollback(); //回滚
connection.commit(); //提交
案例：

public static void main(String[] arg) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = null;
    try {
        connection = DriverManager.getConnection("jdbc:mysql:///mydb?useSSL=false"
                , "root", "123456");
        connection.setAutoCommit(false);
        PreparedStatement statement1 = connection.prepareStatement("update user set password ='******' where id =1");
        statement1.executeUpdate();
        System.out.println(1 / 0);//一旦此处发生异常，则下面的语句将不再执行，程序直接跳到 catch 里面

        PreparedStatement statement2 = connection.prepareStatement("update user set password ='******' where id =2");
        statement2.executeUpdate();

        statement1.close();
        statement2.close();
        connection.commit();
    } catch (Exception e) {
        e.printStackTrace();
        connection.rollback();
    } finally {
        connection.close();
    }
}

事务的特性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

练习：

1.设计一个代码，删除表中前 10 条数据，但是删除前会在控制台弹出一个提示：是否要删除数据(Y/N)。

如果用户输入 Y，则提交。如果输入 N 则回滚。如果输入的既不是 Y 也不是 N，则重复提示。

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

public class JdbcTest {

    public static void main(String[] arg) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Scanner scanner = new Scanner(System.in);
        Connection connection = DriverManager.getConnection("jdbc:mysql:///mydb?useSSL=false", "root", "123456");
        connection.setAutoCommit(false);
        PreparedStatement statement = connection.prepareStatement("DELETE FROM `user`  ORDER BY id DESC LIMIT 10");
        statement.executeUpdate();
        while (true) {
            System.out.println("是否要删除数据(Y/N)");
            String next = scanner.next();
            if (next.equalsIgnoreCase("y")) {
                connection.commit();
                break;
            } else if (next.equalsIgnoreCase("n")) {
                connection.rollback();
                break;
            }
        }
        statement.close();
        connection.close();
        scanner.close();
    }
}

三、一对一级联查询

在数据库中经常会用到一对一的级联查询，如查询学生时需要将其所属的班级查询出来。如下数据表及数据。

CREATE TABLE `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

insert  into `classes`(`id`,`name`,`remark`) values (1,'java','软件开发');
insert  into `classes`(`id`,`name`,`remark`) values (2,'ui','ui设计');
insert  into `classes`(`id`,`name`,`remark`) values (3,'新媒体','新媒体运营');

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  `sex` int(11) NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

insert  into `student`(`id`,`name`,`age`,`sex`,`class_id`) values (1,'张三',18,1,1);
insert  into `student`(`id`,`name`,`age`,`sex`,`class_id`) values (2,'李四',20,0,1);
insert  into `student`(`id`,`name`,`age`,`sex`,`class_id`) values (3,'王二',40,1,2);
insert  into `student`(`id`,`name`,`age`,`sex`,`class_id`) values (4,'麻子',25,0,3);

案例一：查询学生时级联班级
实体：

public class Student {
  private Integer id;
  private String name;
  private Integer age;
  private Integer sex;
  private Classes classes;
}

dao

public Student findByIdWithClasses(int id) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("SELECT s.*,c.`name` class_name,c.remark FROM `student` s " +
            "LEFT JOIN `classes` c ON s.`class_id`=c.`id` WHERE s.id =?");
    statement.setObject(1, id);
    ResultSet resultSet = statement.executeQuery();
    Student student = null;
    if (resultSet.next()) {
        student = new Student();
        Classes classes = null;
        if (resultSet.getString("class_name") != null) {
            classes = new Classes();
            classes.setId(resultSet.getInt("class_id"));
            classes.setName(resultSet.getString("class_name"));
            classes.setRemark(resultSet.getString("remark"));
        }
        student.setId(resultSet.getInt("id"));
        student.setClasses(classes);
        student.setAge(resultSet.getInt("age"));
        student.setSex(resultSet.getInt("sex"));
        student.setName(resultSet.getString("name"));
    }
    resultSet.close();
    statement.close();
    connection.close();
    return student;
}

案例二：查询学生时级联班级
StudentDao

public Student findByIdWithClasses2(int id) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM `student` WHERE id =?");
    statement.setObject(1, id);
    ResultSet resultSet = statement.executeQuery();
    Student student = null;
    if (resultSet.next()) {
        student = new Student();
        student.setId(resultSet.getInt("id"));
        Classes classes = classesDao.findById(resultSet.getInt("class_id"));
        student.setClasses(classes);
        student.setAge(resultSet.getInt("age"));
        student.setSex(resultSet.getInt("sex"));
        student.setName(resultSet.getString("name"));
    }
    resultSet.close();
    statement.close();
    connection.close();
    return student;
}

ClassesDao

public Classes findById(int id) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select * from classes where id=?");
    statement.setObject(1, id);
    ResultSet resultSet = statement.executeQuery();
    Classes classes = null;
    if (resultSet.next()) {
        classes = new Classes();
        classes.setId(resultSet.getInt("id"));
        classes.setRemark(resultSet.getString("remark"));
        classes.setName(resultSet.getString("name"));
    }
    resultSet.close();
    statement.close();
    connection.close();
    return classes;
}

四、一对多级联查询

在实际开发中经常会遇到一对多查询，比如说一个班级对应多个学生，在需求中我们又需要查询出每个班的学生。

Classes实体

public class Classes {
    private Integer id;
    private String name;
    private String remark;
    private List<Student> students;
}

ClassesDao

StudentDao studentDao = new StudentDao();
public Classes findByIdWithStudent(int id) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select * from classes where id=?");
    statement.setObject(1, id);
    ResultSet resultSet = statement.executeQuery();
    Classes classes = null;
    if (resultSet.next()) {
        classes = new Classes();
        classes.setId(resultSet.getInt("id"));
        classes.setRemark(resultSet.getString("remark"));
        classes.setName(resultSet.getString("name"));
        classes.setStudents(studentDao.findByClassId(resultSet.getInt("id")));
    }
    resultSet.close();
    statement.close();
    connection.close();
    return classes;
}

StudentDao

public List<Student> findByClassId(int classId) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM `student` WHERE class_id =?");
    statement.setObject(1, classId);
    ResultSet resultSet = statement.executeQuery();
    List<Student> list = new ArrayList<>();

    while (resultSet.next()) {
        Student student = new Student();
        student.setId(resultSet.getInt("id"));
        student.setAge(resultSet.getInt("age"));
        student.setSex(resultSet.getInt("sex"));
        student.setName(resultSet.getString("name"));
        list.add(student);
    }
    resultSet.close();
    statement.close();
    connection.close();
    return list;
}

练习：（30分钟默写）

1.Boss，Car，Meeting 三个实体存在依赖关系如下：

Car：属性有 brand（品牌）、color（颜色）、parameter （参数）

Boss：属性有 name（名字）、company（公司全称）

Meeting：属性 theme（主题）综合采用所学内容，设计数据库表并完成以上综合级联查询。

一次会议包含：张三，李四和王二三个 Boss，他们每个人都有自己的爱车 car，并在单元测试中输出结果如下：

Meeting{id=1,theme='全国程序员保护协会一次会议', bosses=[
Boss{id=1,name='张三', company='ABC', car=Car{id=1,brand='轩逸', color='红色', parameter={参考价=9.98-14.30万, 排量=1.6L}}}, 
Boss{id=2,name='李四', company='DEF', car=Car{id=2,brand='卡罗拉', color='白色', parameter={参考价=10.98-15.98万, 排量=1.2L,1.5L,1.8L}}}, 
Boss{id=3,name='王二', company='GHI', car=Car{id=3,brand='哈弗H6', color='白色', parameter={参考价=9.98-15.48万, 排量=1.5L,2.0L}}}]}

五、JDBC 常见错误（自）

1.java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

驱动包没找到，或位置没放对

2.The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

服务没开，或者服务连接不上

3.Access denied for user ‘root’@‘localhost’ (using password: YES)

数据库账号密码错误

4.Unknown database ‘user_system1’

数据库找不到

5.Table ‘user_system.user1’ doesn’t exist

表不存在

6.Column ‘createtime’ not found.

字段没找到，或者取了数据库中不存在的列

7.You have an error in your SQL syntax;

SQL语法写错了

8.Column ‘id’ in where clause is ambiguous

字段是混淆的

六、连接池

对于一个简单的数据库应用，由于对于数据库的访问不是很频繁。这时可以简单地在需要访问数据库时，就新创建一个连接，用完后就关闭它，这样做也不会带来什么明显的性能上的开销。

但是对于一个复杂的数据库应用，情况就完全不同了。频繁的建立、关闭连接，会极大的减低系统的性能，因为对于连接的使用成了系统性能的瓶颈。

在标准 JDBC 对应用的接口中，并没有提供资源的管理方法。所以，缺省的资源管理由应用自己负责。虽然在 JDBC 规范中，多次提及资源的关闭/回收及其他的合理运用。但最稳妥的方式，还是为应用提供有效的管理手段。所以，JDBC 为第三方应用服务器（Application Server）提供了一个由数据库厂家实现的管理标准接口：连接缓冲(connection pooling)。引入了连接池( Connection Pool )的概念，也就是以缓冲池的机制管理数据库的资源。
如下为一个简单的连接池案例：

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.Properties;

public class DataSource {

    private static LinkedList<Connection> pool = new LinkedList<>();

    static {
        try {
            InputStream inputStream = DataSource.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            String driver = properties.getProperty("jdbc.driverClassName");
            String url = properties.getProperty("jdbc.url");
            String username = properties.getProperty("jdbc.username");
            String password = properties.getProperty("jdbc.password");
            Class.forName(driver);
            for (int i = 0; i < 10; i++) {
                try {
                    pool.add(DriverManager.getConnection(url, username, password));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private DataSource() {
    }

    public static Connection getConnection() {
        return pool.getFirst();
    }

    public static void closeConnection(Connection connection) {
        pool.addLast(connection);
    }
}

db.properties

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=false
jdbc.username=root
jdbc.password=123456

注意：在小皮系统的 mysql 中，my.ini 配置文件中的 wait_timeout 为 120 秒，也就是正常创建的连接在无动作后 2 分钟就被 mysql 服务清除，导致连接不可用，可将值改为 28800 。

优秀的连接池应该有如下特性：

连接池内的连接在快使用完时自动扩容。
扩容后的连接池在闲暇时自动关闭和删除多创建的连接
定时清除连接池内已失效的连接。
能完成如上所说的就有如下的开源连接池。

JDBC Tomcat Pool
DBCP（DataBase Connection Pool）
C3P0
Druid

开源连接池 Druid：阿里巴巴出品，淘宝和支付宝专用数据库连接池，支持所有 JDBC 兼容的数据库，包括 Oracle、MySql、Derby、Postgresql、SQL Server、H2 等等，Druid 针对 Oracle 和 MySql 做了特别优化。

在项目中加入 druid-1.1.13.jar

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DruidTest {

    @Test
    public void test1() {
        DruidDataSource dataSource = new DruidDataSource();
        //获取驱动
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        //建立连接
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/user_system?useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        try {
            //获取连接
            DruidPooledConnection connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement("select * from user");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

如下是 Druid 连接池配置参数明细表
在这里插入图片描述

七、乐观锁与悲观锁

悲观锁（Pessimistic Lock）顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁（Optimistic Lock）顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

悲观锁一般使用行锁或表锁，查询时表明该查询是为了修改而查询，当事务未提交前其他连接是不能操作该条数据。

select * from user where id = 1 for update

乐观锁一般使用版本锁，每次修改前查询一次版本，修改时指定查询到的版本。如果版本过期，则修改失败并进入重试逻辑。

update user set name='张三',version = 6 where id =1 and version = 5

八、ThreadLocal（自）

在并发编程的时候，成员变量如果不做任何处理其实是线程不安全的，各个线程都在操作同一个变量，显然是不行的。那么在有一种情况之下，我们需要满足这样一个条件：

变量是同一个，但是每个线程都使用同一个初始值，也就是使用同一个变量的一个新的副本。

这种情况之下 ThreadLocal 就非常使用，比如说 DAO 的数据库连接，我们知道 DAO 是单例的，那么他的属性 Connection 就不是一个线程安全的变量。而我们每个线程都需要使用他，并且各自使用各自的。这种情况，ThreadLocal 就比较好的解决了这个问题。

public final class ConnectionUtil {

    private ConnectionUtil() {}

    private static final ThreadLocal<Connection> conn = new ThreadLocal<>();

    public static Connection getConn() {
        Connection con = conn.get();
        if (con == null) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("url", "userName", "password");
                conn.set(con);
            } catch (ClassNotFoundException | SQLException e) {
                // ...
            }
        }
        return con;
    }
}

这样子，都是用同一个连接，但是每个连接都是新的，是同一个连接的副本。
那么实现机制是如何的呢？

1、每个 Thread 对象内部都维护了一个 ThreadLocalMap 这样一个 ThreadLocal 的 Map，可以存放若干个 ThreadLocal。

2、当我们在调用 get()方法的时候，先获取当前线程，然后获取到当前线程的 ThreadLocalMap 对象，如果非空，那么取出 ThreadLocal 的 value，否则进行初始化，初始化就是将 initialValue 的值 set 到 ThreadLocal 中。

public T get() {
    Thread t = Thread.currentThread();
    ThreadLocalMap map = getMap(t);
    if (map != null) {
        ThreadLocalMap.Entry e = map.getEntry(this);
        if (e != null)
            return (T)e.value;
    }
    return setInitialValue();
}

3、当我们调用 set() 方法的时候，很常规，就是将值设置进 ThreadLocal 中。

4、总结：当我们调用 get 方法的时候，其实每个当前线程中都有一个 ThreadLocal。每次获取或者设置都是对该 ThreadLocal 进行的操作，是与其他线程分开的。

5、应用场景：当很多线程需要多次使用同一个对象，并且需要该对象具有相同初始化值的时候最适合使用 ThreadLocal。

6、其实说再多也不如看一下源码来得清晰。如果要看源码，其中涉及到一个 WeakReference 和一个 Map，这两个地方需要了解下，这两个东西分别是 Java 的弱引用，也就是 GC 的时候会销毁该引用所包裹(引用)的对象，这个 threadLocal 作为 key 可能被销毁，但是只要我们定义成他的类不卸载，这个强引用就始终引用着这个 ThreadLocal 的，永远不会被 gc 掉，和 HashMap 差不多。

事实上，从本质来讲，就是每个线程都维护了一个 map，而这个 map 的 key 就是 ThreadLocal，而值就是我们 set 的那个值，每次线程在 get 的时候，都从自己的变量中取值，既然从自己的变量中取值，那肯定就不存在线程安全问题，总体来讲，ThreadLocal 这个变量的状态根本没有发生变化，他仅仅是充当一个 key 的角色，另外提供给每一个线程一个初始值。如果允许的话，我们自己就能实现一个这样的功能，只不过恰好 JDK 就已经帮我们做了这个事情。

faramita_of_mine

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
JDBC (二) jdbc高级

jdbc 高级一、批处理二、事务三、一对一级联查询四、一对多级联查询五、JDBC 常见错误（自）六、连接池七、乐观锁与悲观锁八、ThreadLocal（自）本篇主要内容如下：一、批处理有时候我们需要执行大批量的增删改时，可以使用 jdbc 的批处理，它能有效的提高执行效率。实现批处理可以使用 Statement 与 PreparedStatement 完成，通常在使用中常用的 API 有三个 addBatch(sql)、executeBatch() 和 clearBatch()，分别完成 SQL
复制链接

扫一扫