mysql与redis数据测试

题目要求

1.新建一张user表，在表内插入10000条数据。
2.①通过jdbc查询这10000条数据，记录查询时间。
②通过redis查询这10000条数据，记录查询时间。
3.再次查询这一万条数据，要求根据年龄进行排序，mysql和redis各实现一次。
4.上面排序后的前5人可进行抽奖，每人有一次抽奖机会，抽奖奖品随意设计，抽奖方式通过redis实现。

1.环境准备

准备相关依赖，当前项目为Maven项目，方便导入依赖。

测试junit，mysql-jdbc驱动，jedis

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  <modelVersion>4.0.0</modelVersion>  <groupId>com.hsc</groupId>  <artifactId>maven_java1</artifactId>  <version>1.0-SNAPSHOT</version>  <packaging>war</packaging><properties> <maven.compiler.source>17</maven.compiler.source>  <maven.compiler.target>17</maven.compiler.target>  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties><dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.1</version></dependency><dependency><groupId>redis.clients</groupId><artifactId>jedis</artifactId><version>3.7.0</version></dependency><!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.49</version></dependency><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-classic</artifactId><version>1.2.3</version></dependency></dependencies>
</project>

学生表

-- auto-generated definition
create table student
(id   int auto_increment comment 'id'primary key,name varchar(10) null comment '姓名',age  int         null comment '年龄'
);

3.mysql数据库与redis存储数据准备

随机出数据通过jdbc插入

//获取数据库连接public  Connection  getConnection(){System.out.println("获取数据库连接");String url = "jdbc:mysql://localhost:3306/db_test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";String username = "root";String password = "1234";Connection conn = null;try {conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}return conn;}//mysql添加数据@Testpublic void addMysql(){System.out.println("mysql添加数据");Connection conn = getConnection();try  {Random random = new Random();for (int i = 0; i < 10000; i++) {String name = "Name" + random.nextInt(10000);int age = random.nextInt(100);PreparedStatement pstmt = conn.prepareStatement("INSERT INTO student (name, age) VALUES (?, ?)");pstmt.setString(1, name);pstmt.setInt(2, age);pstmt.executeUpdate();}} catch (SQLException e) {e.printStackTrace();}}

将mysql数据库数据转储到redis

//添加redis数据@Testpublic void addRedis(){System.out.println("redis添加数据");Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ");Jedis jedis = new Jedis("localhost");while (rs.next()) {String id = String.valueOf(rs.getInt("id"));String name = rs.getString("name");int age = rs.getInt("age");// 存储学生数据jedis.hset("student:" + id, "name", name);jedis.hset("student:" + id, "age", String.valueOf(age));// 使用有序集合存储学生ID和年龄，以便进行排序jedis.zadd("studentsByAge", age, id);}jedis.close();} catch (SQLException e) {e.printStackTrace();}}

2 进行查询时间对比

思路
通过控制变量写出对应的查询方法，在测试过程中获取到对应的数据集即可

查询方法

//mysql查询@Testpublic void queryDataWithJDBC() {Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ");
//            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));
//            }} catch (SQLException e) {e.printStackTrace();}}//redis查询@Testpublic void queryDataWithRedis() {Jedis jedis = new Jedis("localhost");Set<String> keys = jedis.keys("student:*");
//        for (String key : keys) {
//            Map<String, String> student = jedis.hgetAll(key);
            System.out.println("Key: " + key + ", Value: " + student);
//        }jedis.close();}

对比方法

//对比查询时间@Testpublic void compareTime(){// 通过jdbc查询这10000条数据，记录查询时间long start = System.currentTimeMillis();queryDataWithJDBC();long end = System.currentTimeMillis();System.out.println("JDBC查询时间: " + (end - start) + "ms");// 通过redis查询这10000条数据，记录查询时间start = System.currentTimeMillis();queryDataWithRedis();end = System.currentTimeMillis();System.out.println("Redis查询时间: " + (end - start) + "ms");}

结果

3 根据年龄排序

mysql中通过order by子句

  //mysql实现@Testpublic void queryAndSortDataWithJDBC() {Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ORDER BY age");while (rs.next()) {System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));}} catch (SQLException e) {e.printStackTrace();}}

在redis中采取了有序集合进行存储，直接获取即可

//redis实现@Testpublic void queryAndSortDataWithRedis() {Jedis jedis = new Jedis("localhost");Set<Tuple> students = jedis.zrangeWithScores("studentsByAge", 0, -1);for (Tuple student : students) {String id = student.getElement();double age = student.getScore();String name = jedis.hget("student:" + id, "name");System.out.println("ID: " + id + ", Name: " + name + ", Age: " + (int)age);}jedis.close();}

4 抽奖

代码

 //抽奖@Testpublic void lottery() {Jedis jedis = new Jedis("localhost");// 添加奖品String[] prizes = {"锅", "碗", "瓢", "盆", "金元宝"};for (String prize : prizes) {jedis.sadd("prizes", prize);}// 年龄最小的前5人System.out.println("年龄最小的前5人：");Set<Tuple> youngestStudents = jedis.zrangeWithScores("studentsByAge", 0, 4);for (Tuple student : youngestStudents) {String id = student.getElement();double age = student.getScore();String name = jedis.hget("student:" + id, "name");String prize = jedis.srandmember("prizes");System.out.println("恭喜 " + name + " 获得了抽奖机会！奖品是：" + prize);}// 年龄最大的后5人System.out.println("年龄最大的后5人：");Set<Tuple> oldestStudents = jedis.zrevrangeWithScores("studentsByAge", 0, 4);for (Tuple student : oldestStudents) {String id = student.getElement();double age = student.getScore();String name = jedis.hget("student:" + id, "name");String prize = jedis.srandmember("prizes");System.out.println("恭喜 " + name + " 获得了抽奖机会！奖品是：" + prize);}jedis.close();}

结果

5 完整测试代码

import org.junit.Test;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.Tuple;import java.sql.*;
import java.util.Map;
import java.util.Random;
import java.util.Set;/*** ClassName: RedisAndMysqlTest* Package: PACKAGE_NAME* Description:** @Author 夜蕴冰阳* @Create 2024/3/17 12:11* @Version 1.0*/public class RedisAndMysqlTest {//获取数据库连接public  Connection  getConnection(){System.out.println("获取数据库连接");String url = "jdbc:mysql://localhost:3306/db_test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";String username = "root";String password = "1234";Connection conn = null;try {conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}return conn;}//mysql添加数据@Testpublic void addMysql(){System.out.println("mysql添加数据");Connection conn = getConnection();try  {Random random = new Random();for (int i = 0; i < 10000; i++) {String name = "Name" + random.nextInt(10000);int age = random.nextInt(100);PreparedStatement pstmt = conn.prepareStatement("INSERT INTO student (name, age) VALUES (?, ?)");pstmt.setString(1, name);pstmt.setInt(2, age);pstmt.executeUpdate();}} catch (SQLException e) {e.printStackTrace();}}//添加redis数据@Testpublic void addRedis(){System.out.println("redis添加数据");Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ");Jedis jedis = new Jedis("localhost");while (rs.next()) {String id = String.valueOf(rs.getInt("id"));String name = rs.getString("name");int age = rs.getInt("age");// 存储学生数据jedis.hset("student:" + id, "name", name);jedis.hset("student:" + id, "age", String.valueOf(age));// 使用有序集合存储学生ID和年龄，以便进行排序jedis.zadd("studentsByAge", age, id);}jedis.close();} catch (SQLException e) {e.printStackTrace();}}//对比查询时间@Testpublic void compareTime(){// 通过jdbc查询这10000条数据，记录查询时间long start = System.currentTimeMillis();queryDataWithJDBC();long end = System.currentTimeMillis();System.out.println("JDBC查询时间: " + (end - start) + "ms");// 通过redis查询这10000条数据，记录查询时间start = System.currentTimeMillis();queryDataWithRedis();end = System.currentTimeMillis();System.out.println("Redis查询时间: " + (end - start) + "ms");}//根据年龄排序//mysql实现@Testpublic void queryAndSortDataWithJDBC() {Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ORDER BY age");while (rs.next()) {System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));}} catch (SQLException e) {e.printStackTrace();}}//redis实现@Testpublic void queryAndSortDataWithRedis() {Jedis jedis = new Jedis("localhost");Set<Tuple> students = jedis.zrangeWithScores("studentsByAge", 0, -1);for (Tuple student : students) {String id = student.getElement();double age = student.getScore();String name = jedis.hget("student:" + id, "name");System.out.println("ID: " + id + ", Name: " + name + ", Age: " + (int)age);}jedis.close();}//抽奖@Testpublic void lottery() {Jedis jedis = new Jedis("localhost");// 添加奖品String[] prizes = {"锅", "碗", "瓢", "盆", "金元宝"};for (String prize : prizes) {jedis.sadd("prizes", prize);}// 年龄最小的前5人System.out.println("年龄最小的前5人：");Set<Tuple> youngestStudents = jedis.zrangeWithScores("studentsByAge", 0, 4);for (Tuple student : youngestStudents) {String id = student.getElement();double age = student.getScore();String name = jedis.hget("student:" + id, "name");String prize = jedis.srandmember("prizes");System.out.println("恭喜 " + name + " 获得了抽奖机会！奖品是：" + prize);}// 年龄最大的后5人System.out.println("年龄最大的后5人：");Set<Tuple> oldestStudents = jedis.zrevrangeWithScores("studentsByAge", 0, 4);for (Tuple student : oldestStudents) {String id = student.getElement();double age = student.getScore();String name = jedis.hget("student:" + id, "name");String prize = jedis.srandmember("prizes");System.out.println("恭喜 " + name + " 获得了抽奖机会！奖品是：" + prize);}jedis.close();}//mysql查询@Testpublic void queryDataWithJDBC() {Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ");
//            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));
//            }} catch (SQLException e) {e.printStackTrace();}}//redis查询@Testpublic void queryDataWithRedis() {Jedis jedis = new Jedis("localhost");Set<String> keys = jedis.keys("student:*");
//        for (String key : keys) {
//            Map<String, String> student = jedis.hgetAll(key);
            System.out.println("Key: " + key + ", Value: " + student);
//        }jedis.close();}//清空mysql表数据和redis数据@Testpublic void clearData() {Connection conn = getConnection();try  {Statement stmt = conn.createStatement();stmt.executeUpdate("TRUNCATE TABLE student");} catch (SQLException e) {e.printStackTrace();}Jedis jedis = new Jedis("localhost");jedis.flushAll();jedis.close();}//mysql数据遍历@Testpublic void DataWithJDBC() {Connection conn = getConnection();try  {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM student ");while (rs.next()) {System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));}} catch (SQLException e) {e.printStackTrace();}}//redis数据遍历@Testpublic void DataWithRedis() {Jedis jedis = new Jedis("localhost");Set<String> keys = jedis.keys("student:*");for (String key : keys) {Map<String, String> student = jedis.hgetAll(key);System.out.println("Key: " + key + ", Value: " + student);}jedis.close();}}