redis与JDBC与mysql数据库的应用

今天药忘吃喽~ 2024-04-06 14:53 55阅读 0赞

## 1.要求 ##

1.新建一张user表，在表内插入10000条数据。
    2：①通过jdbc或mybatis查询这10000条数据，记录查询时间；
        ②通过redis查询这10000条数据，记录查询时间，
    3：再次查询这一万条数据，要求根据年龄进行排序，mysql和redis各实现一次

## 2.实现 ##

package text;
    
    import redis.clients.jedis.Jedis;
    import redis.clients.jedis.JedisPool;
    import redis.clients.jedis.JedisPoolConfig;
    import redis.clients.jedis.Pipeline;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Random;
    
    /**
     * @author hua
     * @date 2023/10/24 14:40
     */
    public class user {
        static   Jedis jedis = new Jedis("localhost",6379);
        static Connection conn = null;
        Statement statement = null;
        public Connection getConnection(){
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql:///my_db1";
            String user = "root";
            String password = "123";
            Connection conn=null;
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, user, password);
            }
            catch (Exception e) {
                System.out.println("Sorry,can`t find the Driver!");
                e.printStackTrace();
            }
            return conn;
        }
    
        public int insertData( ) throws SQLException {
            user insertData=new  user();
           conn=insertData.getConnection();
            if(!conn.isClosed())
                System.out.println("begin insert!");
            statement = conn.createStatement();
            Random random = new Random();
            int n=0;
            for (int i = 0; i <=10000; i++) {
                String sql = "insert into user(age) values('"+random.nextInt(100)+"')";
                n=statement.executeUpdate(sql);
                n++;
            }
            System.out.println("insert end!");
            return n;
        }
        private int count() throws SQLException {
            int count = 0;
            PreparedStatement ps = null;
            ResultSet rs = null;
            String sql = "select count(*) from user";
            try {
                conn=this.getConnection();
                if (conn!=null) {
                    ps=conn.prepareStatement(sql);
                    rs=ps.executeQuery();
                    if(rs.next()) {
                        count=rs.getInt(1);
                        System.out.println("count:"+count);
                    }
                }
            } catch (Exception e) {
    
            }
            return count;
        }
    
        public static void main(String[] args) throws SQLException {
            user insertData=new  user();
            Connection conn=insertData.getConnection();
            if(!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!");
    
            int count = insertData.count();
            int n=0;
            if (count==0){
                n=insertData.insertData();
            }
    
            //jdbc查询数据
            Long beginTime = System.currentTimeMillis();
            Statement statement = conn.createStatement();
            String sql = "select * from user";
    //        String sql = "select * from user order by age";
            // 结果集
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("-----------------");
            System.out.println("执行结果如下所示:");
            System.out.println("-----------------");
    //        int age = 0;
              ArrayList<String> list =new ArrayList<String>();
    
            while(rs.next()) {
                rs.getString("id");
                rs.getString("age");
    //            System.out.println(rs.getString("id") + "\t" + rs.getString("age"));
                list.add(rs.getString("age"));
    
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("jdbc读取时间:"+(endTime-beginTime)+"豪秒");
            Long beginTime2 = System.currentTimeMillis();
    
    
            //jdbc排序
            jdbcsort();
            Long endTime2 = System.currentTimeMillis();
            System.out.println("jdbc排序读取时间:"+(endTime2-beginTime2)+"豪秒");
    
            //redis插入
            for (String age:list) {
                reidsinsert(age);
            }
    
            //reidis读取时间
            Long beginTime1 = System.currentTimeMillis();
            redisSearch();
            Long endTime1 = System.currentTimeMillis();
            System.out.println("redis读取时间:"+(endTime1-beginTime1)+"豪秒");
    
            rs.close();
            conn.close();
        }
    
    
    
        private static  void jdbcsort() throws SQLException {
            Statement statement = conn.createStatement();
            String sql = "select * from user order by age";
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("-----------------");
            System.out.println("排序结果如下所示:");
            System.out.println("-----------------");
    
            while(rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("age"));
    
            }
    
        }
    
    
        static void redisSearch(){
          jedis.lrange("ss",0,-1);
            jedis.sort("ss");
       }
        static  void reidsinsert(String age){
          jedis.rpush("ss",age);
           jedis.close();
        }
    
    }

## 3.运行结果 ##

![在这里插入图片描述][7bc37e1ebcc54649bf1571d2e5fcda2c.png]

![在这里插入图片描述][2071ed22f5be414ba61dce238fb62127.png]

[7bc37e1ebcc54649bf1571d2e5fcda2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b306ae203324423481f077c142b7c470.png
[2071ed22f5be414ba61dce238fb62127.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/6d8c02a09bd74612ad6e62fb2e1324b5.png