jedis mysql_06_Jedis完成MySQL的条件查询案例

【概述】

假设现在有一个User表，其中有id,name,age,sex等字段，完成如下要求的SQL语句为：

1.查找所有age=18 的User

select * from user where age=18 ;

2.查找所有sex="M"("M"代表男性)的User

select * from user where sex="M" ;

3.查找所有sex="M" and age=18 的User

select * from user where age=18 and sex="M" ;

在MySQL关系型数据库中，这些操作是非常轻松的，但是在Redis这种非关系型数据库中，我们需要经过设计才能完成上述的功能。

【工程截图】

【redis.properties 配置文件】

ip=127.0.0.1port=6379maxActive=100maxIdle=5maxWait=100isTestOnBorrow=true

【RedisUtils.java】

package com.higgin.util;

import java.io.InputStream;

import java.util.Properties;

import redis.clients.jedis.Jedis;

import redis.clients.jedis.JedisPool;

import redis.clients.jedis.JedisPoolConfig;public classRedisUtils {private staticString ip;private static intport;private static int maxActive; //最大连接数

private static intmaxIdle;private static longmaxWait;private staticboolean isTestOnBorrow;private staticJedisPool jedisPool;private staticJedisPoolConfig config;private staticJedis jedis;static{ //静态代码块中完成解析redis.properties各种配置的工作

try{

InputStreamin=RedisUtils.class.getClassLoader().getResourceAsStream("redis.properties");

Properties prop=newProperties();

prop.load(in);

ip=prop.getProperty("ip");

port=Integer.parseInt(prop.getProperty("port"));

maxActive=Integer.parseInt(prop.getProperty("maxActive"));

maxIdle=Integer.parseInt(prop.getProperty("maxIdle"));

maxWait=Long.parseLong(prop.getProperty("maxWait"));

isTestOnBorrow=Boolean.parseBoolean(prop.getProperty("isTestOnBorrow"));

config=new JedisPoolConfig(); //实例化一个Jedis连接池配置 的对象实例

config.setMaxActive(maxActive); //控制一个pool可以分配多少个jedis实例，通过JedisPool.getResource()获取

config.setMaxIdle(maxIdle); //控制一个pool最多有多少个状态为idle(空闲)的jedis实例

config.setMaxWait(1000*maxWait); //当borrow(引入)一个jedis实例时，最大的等待时间，如果超过等待时间，直接抛出JedisConnectionException

config.setTestOnBorrow(isTestOnBorrow);

jedisPool=newJedisPool(config,ip,port);

}catch(Exception e){

e.printStackTrace();

}

}/**

* 从连接池中得到一个jedis实例

* @return*/

public staticJedis getJedis(){

jedis=jedisPool.getResource();returnjedis;

}/**

* 将jedis返还到连接池*/

public static voidreturnResource(Jedis jedis){if(jedis!=null){

jedisPool.returnResource(jedis);

}

}

}

【JsonUtils.java】

package com.higgin.util;

import com.alibaba.fastjson.JSON;public classJsonUtils {/**

* 对象obj转成Json字符串*/

public staticString getJsonString(Object obj){returnJSON.toJSONString(obj);

}/**

* json字符串转成 Object对象*/

public static T getObjectFromJsonString(String json,Classclazz){returnJSON.parseObject(json,clazz);

}

}

【User.java】

package com.higgin.domain;public classUser {

finalpublic static String USER_KEY = "user_key";

finalpublic static String USER_AGE_KEY_18 = "user_age_key18";

finalpublic static String USER_SEX_KEY_M ="user_sex_key_m";

finalpublic static String USER_SEX_KEY_W ="user_sex_key_w";privateString id;privateString name;private intage;privateString sex;//注意：要想被FastJson的JsonString-->Object，对应的Object必须有构造方法

publicUser(){

}public User(String id, String name, intage, String sex) {this.id =id;this.name =name;this.age =age;this.sex =sex;

}publicString getId() {returnid;

}public voidsetId(String id) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}public intgetAge() {returnage;

}public void setAge(intage) {this.age =age;

}publicString getSex() {returnsex;

}public voidsetSex(String sex) {this.sex =sex;

}

@OverridepublicString toString() {return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="

+ sex + "]";

}

}

【UserRedisDao.java 接口】

package com.higgin.dao;

import java.util.List;

import com.higgin.domain.User;public interfaceUserRedisDao {public voidsetUserObject(String key,String id,User user);public voidsetUserJsonStr(String key,String id, String userJsonStr);public voidsetKeyId(String key,String id);publicUser getUser(String key,String id);public ListgetIds(String key);public List getIdsInter(String key1,String key2); //取出两个key所在的集合的交集

public List getUsersByFields(String key,String[] fields); //返回对应的key中，满足fiedls数组的所有value

}

【UserRedisDaoImpl.java】

package com.higgin.dao.impl;

import java.util.ArrayList;

import java.util.List;

import java.util.Set;

import redis.clients.jedis.Jedis;

import com.higgin.dao.UserRedisDao;

import com.higgin.domain.User;

import com.higgin.util.JsonUtils;

import com.higgin.util.RedisUtils;public classUserRedisDaoImpl implements UserRedisDao{

Jedis jedis=RedisUtils.getJedis();

@Overridepublic voidsetUserObject(String key, String id, User user) {

setUserJsonStr(key,id,JsonUtils.getJsonString(user));

}

@Overridepublic voidsetUserJsonStr(String key,String id, String userJsonStr) {

jedis.hset(key, id, userJsonStr);

}

@OverridepublicUser getUser(String key,String userId) {

String userJsonStr=jedis.hget(key, userId);return JsonUtils.getObjectFromJsonString(userJsonStr, User.class);

}

@Overridepublic ListgetIds(String key) {

Set ids=jedis.smembers(key);

List idsList=new ArrayList(ids);returnidsList;

}

@Overridepublic voidsetKeyId(String key, String id) {

jedis.sadd(key, id);

}

@Overridepublic ListgetIdsInter(String key1, String key2) {

Set ids=jedis.sinter(key1,key2); //得到两个id集合的交集

List idsList=new ArrayList(ids);returnidsList;

}

@Overridepublic ListgetUsersByFields(String key, String[] fields) {returnjedis.hmget(key, fields);

}

}

【TestUserRedisDao.java】

package com.higgin.dao;

import java.util.List;

import redis.clients.jedis.Jedis;

import com.higgin.dao.impl.UserRedisDaoImpl;

import com.higgin.domain.User;

import com.higgin.util.RedisUtils;public classTestUserRedisDao {public static voidmain(String[] args) {

Jedis jedis=RedisUtils.getJedis();

User u1=new User("10001", "zhangsan", 18, "M");

User u2=new User("10002", "lisi", 20, "W");

User u3=new User("10003", "wangwu", 18, "W");

User u4=new User("10004", "maliu", 30, "W");

User u5=new User("10005", "zhengqi", 18, "M");

User u6=new User("10006", "songba", 35, "M");

UserRedisDao userDao=newUserRedisDaoImpl();

userDao.setUserObject(User.USER_KEY, u1.getId(), u1);

userDao.setUserObject(User.USER_KEY, u2.getId(), u2);

userDao.setUserObject(User.USER_KEY, u3.getId(), u3);

userDao.setUserObject(User.USER_KEY, u4.getId(), u4);

userDao.setUserObject(User.USER_KEY, u5.getId(), u5);

userDao.setUserObject(User.USER_KEY, u6.getId(), u6);

userDao.setKeyId(User.USER_AGE_KEY_18, u1.getId());

userDao.setKeyId(User.USER_AGE_KEY_18, u3.getId());

userDao.setKeyId(User.USER_AGE_KEY_18, u5.getId());

userDao.setKeyId(User.USER_SEX_KEY_M, u1.getId());

userDao.setKeyId(User.USER_SEX_KEY_M, u5.getId());

userDao.setKeyId(User.USER_SEX_KEY_M, u6.getId());//完成select * from user where age=18

List ageIdsList=userDao.getIds(User.USER_AGE_KEY_18);

System.out.println(ageIdsList);

String[] ageIdsArray=(String[]) ageIdsList.toArray(new String[ageIdsList.size()]); //id的List --> 数组

List userJsonList1=userDao.getUsersByFields(User.USER_KEY,ageIdsArray); //从user的hash类型中获取多个filed的value,传入的field可以为字符串数组

System.out.println(userJsonList1); //打印出所有的满足条件的user

System.out.println("------------------------------------------------------");//完成select * from user where sex="M"

List sexManList=userDao.getIds(User.USER_SEX_KEY_M);

System.out.println(sexManList);

String[] sexManArray=sexManList.toArray(newString[sexManList.size()]);

List userJsonList2=userDao.getUsersByFields(User.USER_KEY, sexManArray);

System.out.println(userJsonList2);

System.out.println("------------------------------------------------------");//完成select * from user where age=18 and sex="M"

List ageAndSexIdsList=userDao.getIdsInter(User.USER_AGE_KEY_18, User.USER_SEX_KEY_M); //取交集

System.out.println(ageAndSexIdsList);

String[] ageAndSexArray=(String[])ageAndSexIdsList.toArray(newString[ageAndSexIdsList.size()]);

List userJsonList3=userDao.getUsersByFields(User.USER_KEY, ageAndSexArray);

System.out.println(userJsonList3);

jedis.quit();

}

}

【运行结果】