御坂19327号的个人博客
Web体系结构与程序设计 实验4
2024-05-08
大学课程
00

目录

1 引入依赖
实验要求1～6
2 实验要求7

记录实验4过程及代码。

1 引入依赖

Maven项目修改pom.xml，添加以下依赖：

xml
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.8.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.22</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>

实验要求1～6

数据库初始化：

image.png

java
// SQLConnect.java
package org.example.webstructure.experiment4;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;

public class SQLConnect {
    public static void main(String[] args) throws SQLException {

        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:19327/test", "root", "123456");

        // 增
        PreparedStatement preparedStatement = connection.prepareStatement("insert into testtable values('textKey2', 'textValue2')");
        int columnNum = preparedStatement.executeUpdate();
        System.out.println(columnNum);
        preparedStatement = connection.prepareStatement("select value1 from testtable where key1 = 'textKey2'");
        ResultSet result = preparedStatement.executeQuery();
        result.next();
        System.out.println(result.getString("value1"));

        // 删
        preparedStatement = connection.prepareStatement("delete from testtable where key1 = 'textKey2'");
        columnNum = preparedStatement.executeUpdate();
        System.out.println(columnNum);
        preparedStatement = connection.prepareStatement("select count(value1) from testtable where key1 = 'textKey2'");
        result = preparedStatement.executeQuery();
        result.next();
        System.out.println(result.getInt(1));

        // 改
        // 先增一个数据用来改
        preparedStatement = connection.prepareStatement("insert into testtable values('textKey2', 'textValue2')");
        columnNum = preparedStatement.executeUpdate();
        System.out.println(columnNum);
        preparedStatement = connection.prepareStatement("update testtable set value1 = 'textValue3' where key1 = 'textKey2'");
        columnNum = preparedStatement.executeUpdate();
        System.out.println(columnNum);
        preparedStatement = connection.prepareStatement("select value1 from testtable where key1 = 'textKey2'");
        result = preparedStatement.executeQuery();
        result.next();
        System.out.println(result.getString("value1"));

        // 查
        preparedStatement = connection.prepareStatement("select * from testtable");
        result = preparedStatement.executeQuery();
        result.next();
        System.out.println(result.getString("value1"));
        // 再删一次 省得我重复运行的时候这块老是报错
        preparedStatement = connection.prepareStatement("delete from testtable where key1 = 'textKey2'");
        columnNum = preparedStatement.executeUpdate();

        // 释放资源
        result.close();
        preparedStatement.close();
        connection.close();

        // 连接池初始化
        DruidDataSource dataSource = getDruidDataSource();
        // 获取连接
        Connection connectionFromPool = dataSource.getConnection();
        connectionFromPool.close();

        // 通过DBUtils进行增删改查
        connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:19327/test", "root", "123456");
        QueryRunner queryRunner = new QueryRunner();
        ResultSetHandler<test> resultSetHandler = new BeanHandler<>(test.class);
        try {
            // 查
            test t = queryRunner.query(connection, "select key1, value1 from testtable where key1 = 'testKey1'", resultSetHandler);
            System.out.println(t.getKey1() + " " + t.getValue1());

            // 增
            queryRunner.update(connection, "insert into testtable values('testKey3', 'testValue3')");
            t = queryRunner.query(connection, "select key1, value1 from testtable where key1 = 'testKey3'", resultSetHandler);
            System.out.println(t.getKey1() + " " + t.getValue1());

            // 改
            queryRunner.update(connection, "update testtable set value1 = 'testValue4' where key1 = 'testKey3'");
            t = queryRunner.query(connection, "select * from testtable where key1 = 'testKey3'", resultSetHandler);
            System.out.println(t.getKey1() + " " + t.getValue1());

            // 删
            queryRunner.update(connection, "delete from testtable where key1 = 'testKey3'");
            Long i = queryRunner.query(connection, "select count(*) from testtable;", new ScalarHandler<>());
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(connection);
        }
    }

    private static DruidDataSource getDruidDataSource() {
        // 数据源配置
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:19327/test");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); //这个可以缺省的，会根据url自动识别
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        // 下面都是可选的配置
        dataSource.setInitialSize(10);  // 初始连接数，默认0
        dataSource.setMaxActive(30);  // 最大连接数，默认8
        dataSource.setMinIdle(10);  // 最小闲置数
        dataSource.setMaxWait(2000);  // 获取连接的最大等待时间，单位毫秒
        dataSource.setPoolPreparedStatements(true); // 缓存PreparedStatement，默认false
        dataSource.setMaxOpenPreparedStatements(20); // 缓存PreparedStatement的最大数量，默认-1（不缓存）。大于0时会自动开启缓存PreparedStatement，所以可以省略上一句代码
        return dataSource;
    }
}

java
// test.java
package org.example.webstructure.experiment4;

public class test {
    private String key1;
    private String value1;

    public String getKey1() {
        return key1;
    }

    public void setKey1(String key1) {
        this.key1 = key1;
    }

    public String getValue1() {
        return value1;
    }

    public void setValue1(String value1) {
        this.value1 = value1;
    }

    public test() {}
}

image.png

2 实验要求7

java
// SQLConnectTest.java
package org.example.webstructure.experiment4;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.*;

public class SQLConnectTest {

    DruidDataSource dataSource;

    public SQLConnectTest() {
        this.dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:19327/test");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setInitialSize(10);
        dataSource.setMaxActive(30);
        dataSource.setMinIdle(10);
        dataSource.setMaxWait(2000);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setMaxOpenPreparedStatements(20);
    }

    public String UseTraditionalConnection() throws SQLException {
        StringBuilder r = new StringBuilder();
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:19327/test", "root", "123456");
        for (int i = 2; i <= 50; i++) {
            PreparedStatement preparedStatement = connection.prepareStatement("select value1 from testtable where key1 = 'testKey1'");
            ResultSet result = preparedStatement.executeQuery();
            result.next();
            r.append(result.getString("value1")).append(", ");
        }
        return r.toString();
    }

    public String UseConnectPool() throws SQLException {
        StringBuilder r = new StringBuilder();
        Connection connectionFromPool = dataSource.getConnection();
        for (int i = 2; i <= 50; i++) {
            PreparedStatement preparedStatement = connectionFromPool.prepareStatement("select value1 from testtable where key1 = 'testKey1'");
            ResultSet result = preparedStatement.executeQuery();
            result.next();r.append(result.getString("value1")).append(", ");
        }
        return r.toString();
    }
}

先新建并且复制上面的代码，然后右键找Generate/生成，里面有个Test...：

image.png

点进去之后指定类名和对应JUnit版本（上面引入依赖时引入的是JUnit 4.13），再把要生成测试方法的两个方法勾上：

image.png

之后生成的文件参照下面的代码：

java
// SQLConnectTestTest.java
package org.example.webstructure.experiment4;

import org.junit.Test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;

public class SQLConnectTestTest {

    SQLConnectTest test = new SQLConnectTest();

    @Test
    public void test() throws InterruptedException {
        Thread.sleep(20000); // 20秒连接jConsole
    }

    @Test
    public void useTraditionalConnection() throws InterruptedException {
        List<Thread> threadSet = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            Thread t = new Thread(() -> {
                try {
                    test.UseTraditionalConnection();
                } catch (SQLException e) {
                    e.getErrorCode();
                }
            });
            threadSet.add(t);
            t.start();
        }
        for (Thread thread : threadSet) {
            thread.join();
        }
    }

    @Test
    public void useConnectPool() throws InterruptedException {
        List<Thread> threadSet = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            Thread t = new Thread(() -> {
                try {
                    test.UseConnectPool();
                } catch (SQLException e) {
                    e.getErrorCode();
                }
            });
            threadSet.add(t);
            t.start();
        }
        for (Thread thread : threadSet) {
            thread.join();
        }
    }
}

类名旁边有个运行的按钮，点它就可以直接运行全部的测试方法。但是这会先别急运行，先找到Java安装目录下的bin文件夹下的jconsole.exe文件，双击运行：

image.png

image.png

找到之后回到IDEA中运行全部测试方法，再回来找左上角的Connection -> New Connection，找到运行测试的那个进程进行连接。test中阻塞线程的20秒就是用来连接这个的，如果时间不够可以自己调整。

image.png

从这里可以看到Java进程所有的线程，加载的类，堆内存使用情况，占用的CPU和内存资源的使用情况，在IDEA中可以看到两个测试方法的运行时间（对应实验要求的响应时间和资源利用率）：

image.png

本文作者:御坂19327号

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！