题目要求
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添加数据
@Test
public 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数据
@Test
public 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查询
@Test
public 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查询
@Test
public 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();
}
- 对比方法
//对比查询时间
@Test
public 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实现
@Test
public 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实现
@Test
public 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 抽奖
- 代码
//抽奖
@Test
public 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: JDBCTest
* 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添加数据
@Test
public 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数据
@Test
public 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();
}
}
//对比查询时间
@Test
public 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实现
@Test
public 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实现
@Test
public 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();
}
//抽奖
@Test
public 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查询
@Test
public 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查询
@Test
public 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数据
@Test
public 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数据遍历
@Test
public 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数据遍历
@Test
public 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();
}
}