上海长宁建设和交通门户网站,工布江达网站建设,有没有做catalog的网站,搜索引擎优化名词解释题目要求 1.新建一张user表#xff0c;在表内插入10000条数据。 2.①通过jdbc查询这10000条数据#xff0c;记录查询时间。 ②通过redis查询这10000条数据#xff0c;记录查询时间。 3.再次查询这一万条数据#xff0c;要求根据年龄进行排序#xff0c;mysql和redis各实现…题目要求 1.新建一张user表在表内插入10000条数据。 2.①通过jdbc查询这10000条数据记录查询时间。 ②通过redis查询这10000条数据记录查询时间。 3.再次查询这一万条数据要求根据年龄进行排序mysql和redis各实现一次。 4.上面排序后的前5人可进行抽奖每人有一次抽奖机会抽奖奖品随意设计抽奖方式通过redis实现。 1.环境准备
准备相关依赖当前项目为Maven项目方便导入依赖。 测试junitmysql-jdbc驱动jedis ?xml version1.0 encodingUTF-8?project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instance xsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd modelVersion4.0.0/modelVersion groupIdcom.hsc/groupId artifactIdmaven_java1/artifactId version1.0-SNAPSHOT/version packagingwar/packagingproperties maven.compiler.source17/maven.compiler.source maven.compiler.target17/maven.compiler.target project.build.sourceEncodingUTF-8/project.build.sourceEncoding /propertiesdependenciesdependencygroupIdjunit/groupIdartifactIdjunit/artifactIdversion4.13.1/version/dependencydependencygroupIdredis.clients/groupIdartifactIdjedis/artifactIdversion3.7.0/version/dependency!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion5.1.49/version/dependencydependencygroupIdch.qos.logback/groupIdartifactIdlogback-classic/artifactIdversion1.2.3/version/dependency/dependencies
/project
学生表
-- auto-generated definition
create table student
(id int auto_increment comment idprimary 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?useSSLfalseuseUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltrue;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);SetString keys jedis.keys(student:*);
// for (String key : keys) {
// MapString, 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);SetTuple 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人);SetTuple 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人);SetTuple 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?useSSLfalseuseUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltrue;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);SetTuple 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人);SetTuple 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人);SetTuple 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);SetString keys jedis.keys(student:*);
// for (String key : keys) {
// MapString, 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);SetString keys jedis.keys(student:*);for (String key : keys) {MapString, String student jedis.hgetAll(key);System.out.println(Key: key , Value: student);}jedis.close();}}
