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.1 port=6379 maxActive=100 maxIdle=5 maxWait=100 isTestOnBorrow=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 class RedisUtils { private static String ip; private static int port; private static int maxActive; //最大连接数 private static int maxIdle; private static long maxWait; private static boolean isTestOnBorrow; private static JedisPool jedisPool; private static JedisPoolConfig config; private static Jedis jedis; static{ //静态代码块中完成解析redis.properties各种配置的工作 try{ InputStream in=RedisUtils.class.getClassLoader().getResourceAsStream("redis.properties"); Properties prop =new Properties(); 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=new JedisPool(config,ip,port); }catch(Exception e){ e.printStackTrace(); } } /** * 从连接池中得到一个jedis实例 * @return */ public static Jedis getJedis(){ jedis=jedisPool.getResource(); return jedis; } /** * 将jedis返还到连接池 */ public static void returnResource(Jedis jedis){ if(jedis!=null){ jedisPool.returnResource(jedis); } } }
【JsonUtils.java】
package com.higgin.util; import com.alibaba.fastjson.JSON; public class JsonUtils { /** * 对象obj转成Json字符串 */ public static String getJsonString(Object obj){ return JSON.toJSONString(obj); } /** * json字符串转成 Object对象 */ public static<T> T getObjectFromJsonString(String json,Class<T> clazz){ return JSON.parseObject(json,clazz); } }
【User.java】
package com.higgin.domain; public class User { final public static String USER_KEY = "user_key"; final public static String USER_AGE_KEY_18 = "user_age_key18"; final public static String USER_SEX_KEY_M ="user_sex_key_m"; final public static String USER_SEX_KEY_W ="user_sex_key_w"; private String id; private String name; private int age; private String sex; //注意：要想被FastJson的JsonString-->Object，对应的Object必须有构造方法 public User(){ } public User(String id, String name, int age, String sex) { this.id = id; this.name = name; this.age = age; this.sex = sex; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } @Override public String 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 interface UserRedisDao { public void setUserObject(String key,String id,User user); public void setUserJsonStr(String key,String id, String userJsonStr); public void setKeyId(String key,String id); public User getUser(String key,String id); public List<String> getIds(String key); public List<String> getIdsInter(String key1,String key2); //取出两个key所在的集合的交集 public List<String> 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 class UserRedisDaoImpl implements UserRedisDao{ Jedis jedis=RedisUtils.getJedis(); @Override public void setUserObject(String key, String id, User user) { setUserJsonStr(key,id,JsonUtils.getJsonString(user)); } @Override public void setUserJsonStr(String key,String id, String userJsonStr) { jedis.hset(key, id, userJsonStr); } @Override public User getUser(String key,String userId) { String userJsonStr=jedis.hget(key, userId); return JsonUtils.getObjectFromJsonString(userJsonStr, User.class); } @Override public List<String> getIds(String key) { Set<String> ids= jedis.smembers(key); List<String> idsList=new ArrayList<String>(ids); return idsList; } @Override public void setKeyId(String key, String id) { jedis.sadd(key, id); } @Override public List<String> getIdsInter(String key1, String key2) { Set<String> ids=jedis.sinter(key1,key2); //得到两个id集合的交集 List<String> idsList=new ArrayList<String>(ids); return idsList; } @Override public List<String> getUsersByFields(String key, String[] fields) { return jedis.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 class TestUserRedisDao { public static void main(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=new UserRedisDaoImpl(); 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<String> ageIdsList=userDao.getIds(User.USER_AGE_KEY_18); System.out.println(ageIdsList); String[] ageIdsArray =(String[]) ageIdsList.toArray(new String[ageIdsList.size()]); //id的List --> 数组 List<String> 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<String> sexManList=userDao.getIds(User.USER_SEX_KEY_M); System.out.println(sexManList); String[] sexManArray=sexManList.toArray(new String[sexManList.size()]); List<String> userJsonList2=userDao.getUsersByFields(User.USER_KEY, sexManArray); System.out.println(userJsonList2); System.out.println("------------------------------------------------------"); //完成select * from user where age=18 and sex="M" List<String> ageAndSexIdsList=userDao.getIdsInter(User.USER_AGE_KEY_18, User.USER_SEX_KEY_M); //取交集 System.out.println(ageAndSexIdsList); String[] ageAndSexArray=(String[])ageAndSexIdsList.toArray(new String[ageAndSexIdsList.size()]); List<String> userJsonList3=userDao.getUsersByFields(User.USER_KEY, ageAndSexArray); System.out.println(userJsonList3); jedis.quit(); } }
【运行结果】
