实例要求:
1.建一张user表,在表内插入10000条数据。
2.分别使用jdbc、redis查询这10000条数据,记录查询时间。
3.再分别使用mysql和redis查询这一万条数据,根据年龄进行排序。
4.上面排序后的前5人可进行抽奖,每人有一次抽奖机会,抽奖奖品随意设计,抽奖方式通过redis实现。
1、JDBC
1-1JDBC——数据库与java的连接
utils工具类具体代码:
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
* jdbc.properties配置文件读取,只用执行一次,使用静态代码块
*/
static {
try {
//1. 创建Properties集合类。
Properties properties = new Properties();
//获取src路径下的文件的方式--->ClassLoader 类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
//2. 加载文件
properties.load(new FileReader(path));
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return 连接对象
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
/**
* 释放资源
* @param statement
* @param connection
*/
public static void close(Statement statement, Connection connection) {
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* 释放资源
* @param resultSet
* @param statement
* @param connection
*/
public static void close(Statement statement, Connection connection, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
1-2 JDBC
步骤:
代码展示:
public class JDBC {
Connection connection = null;
Statement statement =null;
ResultSet rs = null;
User user=new User();
public int dmlSQL(String sql) throws Exception {
int i = 0;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
i = statement.executeUpdate(sql);
return i;
} catch (SQLException e) {
e.printStackTrace();
return -1;
} finally {
JDBCUtils.close(statement,connection);
}
}
public List<User> dqlSQL(String sql) throws Exception {
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
rs = statement.executeQuery(sql);
LinkedList<User> list = new LinkedList<>();
while(rs.next()){
int age = rs.getInt("age");
String name = rs.getString("name");
user = new User(name,age);
list.add(user);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
JDBCUtils.close(statement,connection,rs);
}
}
}
2、Redis
2-1 建立依赖
2-2 Jedis流程
public class Redis {
private Jedis jedis;
@BeforeEach
void setUp(){
jedis = new Jedis("localhost",6379);
//jedis.auth("1234");
jedis.select(0);
}
@Test
void jedisTest() throws Exception {
}
@AfterEach
void tearDown(){
if(jedis!=null){
jedis.close();
}
}
}
3、代码展示
3-1 插入数据
使用jdbc
//--1--新建一张user表,在表内插入10000条数据。
int res = insert();
System.out.println("被改变的行数为"+res);
------------
public static int insert() throws Exception {
User user = new User();
ArrayList<User> list = new ArrayList<>();
int res=0;
//插入数据,在表内插入10000条数据
for (int i = 0; i < 10; i++) {
int age = rand.nextInt(100)+1;
String name = setName();
/*user = new User(name,age);
list.add(user);*/
int r = jdbc.dmlSQL("INSERT INTO user VALUES ('"+name+"','"+age+"' ) ; ");
res=res+r;
}
return res;
}
public static String setName(){
String name1 = String.valueOf((char)(rand.nextInt(26)+65));
String name2 = String.valueOf((char)(rand.nextInt(26)+97));
String name3 = String.valueOf((char)(rand.nextInt(26)+97));
String name4 = String.valueOf((char)(rand.nextInt(26)+97));
String name = name1+name2+name3+name4;
return name;
}
使用redis
想要存入对象时,可以通过JSON字符串
- 导包:
- 使用toJSONString方法
//向redis中插入数据
List<User> list = Work.findAll();
int age = 0 ;
String name = " ";
int count =0;
User user = new User();
for (User u : list) {
age = u.getAge();
name = u.getName();
user = new User(name,age);
String jsonString = JSON.toJSONString(user);
Long keyTest = jedis.zadd("keyTest", age, jsonString);
count=(int)(count+keyTest);
}
System.out.println(count);
3-2 查询数据,记录时间
JDBC
//--2.1--通过jdbc查询这10000条数据,记录查询时间。
LocalDateTime before = LocalDateTime.now();
List<User> list = findAll();
LocalDateTime after = LocalDateTime.now();
long resTime =( Duration.between(before, after)).toMillis();
System.out.println(resTime);
list.forEach(user -> System.out.println(user));
-------------
public static int insert() throws Exception {
User user = new User();
ArrayList<User> list = new ArrayList<>();
int res=0;
//插入数据,在表内插入10000条数据
for (int i = 0; i < 10; i++) {
int age = rand.nextInt(100)+1;
String name = setName();
/*user = new User(name,age);
list.add(user);*/
int r = jdbc.dmlSQL("INSERT INTO user VALUES ('"+name+"','"+age+"' ) ; ");
res=res+r;
}
return res;
}
public static List<User> findAll() throws Exception {
String sql="SELECT name,age FROM user;";
List<User> resList = jdbc.dqlSQL(sql);
return resList;
}
Jedis
zrange中,使用byscore和不使用的区别:
//查看数据
LocalDateTime before = LocalDateTime.now();
//Set<Tuple> set = jedis.zrangeWithScores("keyTest", 0, 5);
Set<Tuple> set = jedis.zrangeByScoreWithScores("keyTest", 0, 100);
LocalDateTime after = LocalDateTime.now();
long resTime =( Duration.between(before, after)).toMillis();
System.out.println(resTime);
for (Tuple tuple : set) {
System.out.println(tuple);
}
3-3 抽奖
//抽奖
//存id1-5,name,两个字段
//spop随机一个人
Set<String> lucky = jedis.zrange("keyTest", 0, 4);
for (String s : lucky) {
//把参与抽奖的人存入新的键中
jedis.sadd("keyLucky", s);
}
Set<String> keyLucky = jedis.smembers("keyLucky");
keyLucky.forEach(luck-> System.out.println(luck));
//随机取出一个人
System.out.println("中奖的是");
System.out.println(jedis.spop("keyLucky"));