redis实战流程
springboot项目构建
1.maven引入spring-boot-starter-web包
2.写个测试类，启动项目，检查是否可以正常访问
3.导入springboot整合redis的starter：org.springframework.boot spring-boot-starter-data-redis
4.编写redisService类，用来操作redis的
@Service
public class RedisService {
@Autowired
private RedisTemplate redisTemplate;
private static double size = Math.pow(2, 32);
/**
* 写入缓存
*
* @param key
* @param offset 位 8Bit=1Byte
* @return
*/
public boolean setBit(String key, long offset, boolean isShow) {
boolean result = false;
try {
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
operations.setBit(key, offset, isShow);
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 写入缓存
*
* @param key
* @param offset
* @return
*/
public boolean getBit(String key, long offset) {
boolean result = false;
try {
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
result = operations.getBit(key, offset);
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 写入缓存
*
* @param key
* @param value
* @return
*/
public boolean set(final String key, Object value) {
boolean result = false;
try {
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
operations.set(key, value);
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 写入缓存设置时效时间
*
* @param key
* @param value
* @return
*/
public boolean set(final String key, Object value, Long expireTime) {
boolean result = false;
try {
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
operations.set(key, value);
redisTemplate.expire(key, expireTime, TimeUnit.SECONDS);
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 批量删除对应的value
*
* @param keys
*/
public void remove(final String... keys) {
for (String key : keys) {
remove(key);
}
}
/**
* 删除对应的value
*
* @param key
*/
public void remove(final String key) {
if (exists(key)) {
redisTemplate.delete(key);
}
}
/**
* 判断缓存中是否有对应的value
*
* @param key
* @return
*/
public boolean exists(final String key) {
return redisTemplate.hasKey(key);
}
/**
* 读取缓存
*
* @param key
* @return
*/
public Object get(final String key) {
Object result = null;
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
result = operations.get(key);
return result;
}
/**
* 哈希 添加
*
* @param key
* @param hashKey
* @param value
*/
public void hmSet(String key, Object hashKey, Object value) {
HashOperations<String, Object, Object> hash = redisTemplate.opsForHash();
hash.put(key, hashKey, value);
}
/**
* 哈希获取数据
*
* @param key
* @param hashKey
* @return
*/
public Object hmGet(String key, Object hashKey) {
HashOperations<String, Object, Object> hash = redisTemplate.opsForHash();
return hash.get(key, hashKey);
}
/**
* 列表添加
*
* @param k
* @param v
*/
public void lPush(String k, Object v) {
ListOperations<String, Object> list = redisTemplate.opsForList();
list.rightPush(k, v);
}
/**
* 列表获取
*
* @param k
* @param l
* @param l1
* @return
*/
public List<Object> lRange(String k, long l, long l1) {
ListOperations<String, Object> list = redisTemplate.opsForList();
return list.range(k, l, l1);
}
/**
* 集合添加
*
* @param key
* @param value
*/
public void add(String key, Object value) {
SetOperations<String, Object> set = redisTemplate.opsForSet();
set.add(key, value);
}
/**
* 集合获取
*
* @param key
* @return
*/
public Set<Object> setMembers(String key) {
SetOperations<String, Object> set = redisTemplate.opsForSet();
return set.members(key);
}
/**
* 有序集合添加
*
* @param key
* @param value
* @param scoure
*/
public void zAdd(String key, Object value, double scoure) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
zset.add(key, value, scoure);
}
/**
* 有序集合获取
*
* @param key
* @param scoure
* @param scoure1
* @return
*/
public Set<Object> rangeByScore(String key, double scoure, double scoure1) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
redisTemplate.opsForValue();
return zset.rangeByScore(key, scoure, scoure1);
}
//第一次加载的时候将数据加载到redis中
public void saveDataToRedis(String name) {
double index = Math.abs(name.hashCode() % size);
long indexLong = new Double(index).longValue();
boolean availableUsers = setBit("availableUsers", indexLong, true);
}
//第一次加载的时候将数据加载到redis中
public boolean getDataToRedis(String name) {
double index = Math.abs(name.hashCode() % size);
long indexLong = new Double(index).longValue();
return getBit("availableUsers", indexLong);
}
/**
* 有序集合获取排名
*
* @param key 集合名称
* @param value 值
*/
public Long zRank(String key, Object value) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
return zset.rank(key,value);
}
/**
* 有序集合获取排名
*
* @param key
*/
public Set<ZSetOperations.TypedTuple<Object>> zRankWithScore(String key, long start,long end) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
Set<ZSetOperations.TypedTuple<Object>> ret = zset.rangeWithScores(key,start,end);
return ret;
}
/**
* 有序集合添加
*
* @param key
* @param value
*/
public Double zSetScore(String key, Object value) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
return zset.score(key,value);
}
/**
* 有序集合添加分数
*
* @param key
* @param value
* @param scoure
*/
public void incrementScore(String key, Object value, double scoure) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
zset.incrementScore(key, value, scoure);
}
/**
* 有序集合获取排名
*
* @param key
*/
public Set<ZSetOperations.TypedTuple<Object>> reverseZRankWithScore(String key, long start,long end) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
Set<ZSetOperations.TypedTuple<Object>> ret = zset.reverseRangeByScoreWithScores(key,start,end);
return ret;
}
/**
* 有序集合获取排名
*
* @param key
*/
public Set<ZSetOperations.TypedTuple<Object>> reverseZRankWithRank(String key, long start, long end) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
Set<ZSetOperations.TypedTuple<Object>> ret = zset.reverseRangeWithScores(key, start, end);
return ret;
}
}
redisTemplate用法：
opsForValue： 操作String，Key，Value，包含过期key，setBit位操作等
opsForSet ：操作set
opsForHash ：操作hash
opsForZset： 操作SortSet
opsForList ：操作list队列
opsForHash ：操作hash
opsForZset ：操作SortSet
opsForList： 操作list队列
5.引入连接redis的配置文件：application.properties
# Redis的数据库索引，默认为0（总共16个库）
spring.redis.database=0
# Redis服务器地址
spring.redis.host=localhost
# Redis服务器连接端口
spring.redis.port=6379
# Redis服务器连接密码，默认为空
spring.redis.password=
6.编写测试类进行测试，访问：
127.0.0.1:8080/redis/setAndGet
127.0.0.1:8080/redis/setAndGet1
@RestController
public class RedisController {
@Resource
private RedisTemplate redisTemplate;
@Resource
private RedisService service;
@RequestMapping("/redis/setAndGet")
@ResponseBody
public String setAndGetValue(String name,String value){
redisTemplate.opsForValue().set(name,value);
return (String) redisTemplate.opsForValue().get(name);
}
@RequestMapping("/redis/setAndGet1")
@ResponseBody
public String setAndGetValueV2(String name,String value){
service.set(name,value);
return service.get(name).toString();
}
}
把redis作为mybatis缓存，SpringBoot整合
1.导入maven依赖pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<scope>test</scope>
<version>1.3.2</version>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.7</version>
</dependency>
2.引入数据库的配置文件
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&connectTimeout=3000&socketTimeout=1000
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource #采用的是阿里的Druid连接池
driver-class-name: com.mysql.jdbc.Driver
minIdle: 5
maxActive: 100
initialSize: 10
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 50
removeAbandoned: true
filters: stat # ,wall,log4j # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
useGlobalDataSourceStat: true # 合并多个DruidDataSource的监控数据
druidLoginName: wjf # 登录druid的账号
druidPassword: wjf # 登录druid的密码
cachePrepStmts: true # 开启二级缓存
mybatis:
typeAliasesPackage: com.vincent.redis.mapper
mapperLocations: classpath:/com/vincnet/redis/mapper/*.xml
mapperScanPackage: com.vincent.redis.mapper
configLocation: classpath:/mybatis-config.xml
3.引入mybatis-config.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- 使全局的映射器启用或禁用缓存。 -->
<setting name="cacheEnabled" value="true" />
<!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。 -->
<setting name="lazyLoadingEnabled" value="true" />
<!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。 -->
<setting name="aggressiveLazyLoading" value="true"/>
<!-- 是否允许单条sql 返回多个数据集 (取决于驱动的兼容性) default:true -->
<setting name="multipleResultSetsEnabled" value="true" />
<!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true -->
<setting name="useColumnLabel" value="true" />
<!-- 允许JDBC 生成主键。需要驱动器支持。如果设为了true，这个设置将强制使用被生成的主键，有一些驱动器不兼容不过仍然可以执行。 default:false -->
<setting name="useGeneratedKeys" value="false" />
<!-- 指定 MyBatis 如何自动映射 数据基表的列 NONE：不隐射 PARTIAL:部分 FULL:全部 -->
<setting name="autoMappingBehavior" value="PARTIAL" />
<!-- 这是默认的执行类型 （SIMPLE: 简单； REUSE: 执行器可能重复使用prepared statements语句；BATCH: 执行器可以重复执行语句和批量更新） -->
<setting name="defaultExecutorType" value="SIMPLE" />
<setting name="defaultStatementTimeout" value="25" />
<setting name="defaultFetchSize" value="100" />
<setting name="safeRowBoundsEnabled" value="false" />
<!-- 使用驼峰命名法转换字段。 -->
<setting name="mapUnderscoreToCamelCase" value="true" />
<!-- 设置本地缓存范围 session:就会有数据的共享 statement:语句范围 (这样就不会有数据的共享 ) defalut:session -->
<setting name="localCacheScope" value="SESSION" />
<!-- 默认为OTHER,为了解决oracle插入null报错的问题要设置为NULL -->
<setting name="jdbcTypeForNull" value="NULL" />
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
</settings>
</configuration>
4.编写数据库信息获取的配置
@Configuration
@MapperScan("com.vincent.redis.mapper")
@EnableRedisHttpSession(maxInactiveIntervalInSeconds= 50)
public class DataSourceConfig {
private Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.type}")
private String dbType;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.filters}")
private String filters;
@Value("${spring.datasource.connectionProperties}")
private String connectionProperties;
@Value("${spring.datasource.useGlobalDataSourceStat}")
private boolean useGlobalDataSourceStat;
@Value("${spring.datasource.druidLoginName}")
private String druidLoginName;
@Value("${spring.datasource.druidPassword}")
private String druidPassword;
@Bean(name="dataSource",destroyMethod = "close", initMethod="init")
@Primary //不要漏了这
public DataSource dataSource(){
DruidDataSource datasource = new DruidDataSource();
try {
datasource.setUrl(this.dbUrl);
datasource.setDbType(dbType);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
return datasource;
}
///////// 下面是druid 监控访问的设置 /////////////////
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*"); //url 匹配
reg.addInitParameter("allow", "192.168.1.111,127.0.0.1"); // IP白名单 (没有配置或者为空，则允许所有访问)
reg.addInitParameter("deny", "192.168.1.112"); //IP黑名单 (存在共同时，deny优先于allow)
reg.addInitParameter("loginUsername", this.druidLoginName);//登录名
reg.addInitParameter("loginPassword", this.druidPassword);//登录密码
reg.addInitParameter("resetEnable", "false"); // 禁用HTML页面上的“Reset All”功能
return reg;
}
@Bean(name="druidWebStatFilter")
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); //忽略资源
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
return filterRegistrationBean;
}
}
5.编写测试代码（查询为例）
- 数据库新建sys_user表
CREATE TABLE `sys_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(11) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '用户名',
`image` varchar(11) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '用户头像',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
- user类
public class User implements Serializable {
private static final long serialVersionUID = -4415438719697624729L;
private String id;
private String userName;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
}
- UserMapper
@Mapper
@Component
public interface UserMapper {
@Insert("insert sys_user(id,user_name) values(#{id},#{userName})")
void insert(User u);
@Update("update sys_user set user_name = #{userName} where id=#{id} ")
void update(User u);
@Delete("delete from sys_user where id=#{id} ")
void delete(@Param("id") String id);
@Select("select id,user_name from sys_user where id=#{id} ")
User find(@Param("id") String id);
//注：方法名和要UserMapper.xml中的id一致
List<User> query(@Param("userName") String userName);
@Delete("delete from sys_user")
void deleteAll();
}
- UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.vincent.redis.mapper.UserMapper">
<select id="query" resultType="com.vincent.redis.domain.User">
select id ,user_name
from sys_user
where 1=1
<if test="userName != null">
and user_name like CONCAT('%',#{userName},'%')
</if>
</select>
</mapper>
- UserService
@Service
@Transactional(propagation=Propagation.REQUIRED,readOnly=false,rollbackFor=Exception.class)
public class UserService {
@Autowired
private UserMapper userMapper;
public User insertUser(User u){
this.userMapper.insert(u);
return this.userMapper.find(u.getId());
}
public User updateUser(User u){
this.userMapper.update(u);
return this.userMapper.find(u.getId());
}
public User findById(String id){
System.err.println("根据id=" + id +"获取用户对象，从数据库中获取");
Assert.notNull(id,"id不用为空");
return this.userMapper.find(id);
}
public void deleteById(String id){
this.userMapper.delete(id);
}
public void deleteAll(){
this.userMapper.deleteAll();
}
public User findByIdTtl(String id){
System.err.println("根据id=" + id +"获取用户对象，从数据库中获取");
Assert.notNull(id,"id不用为空");
return this.userMapper.find(id);
}
}
- UserController
@RestController
public class UserController {
private static final String key = "userCache_";
@Resource
private UserMapper userMapper;
@Resource
private UserService userService;
@Resource
private RedisService redisService;
@RequestMapping("/getUser")
@ResponseBody
public User getUser(String id) {
User user = userMapper.find(id);
return user;
}
/**
* set值和get值的时候序列化方式必须保持一致
* @param id
* @return
*/
@RequestMapping("/getUserCache")
@ResponseBody
public User getUseCache(String id) {
//step1 先从redis里面取值
User user = (User)redisService.get(key + id);
//step2 如果拿不到则从DB取值
if (user == null) {
User userDB = userMapper.find(id);
System.out.println("fresh value from DB id:" + id);
//step3 DB非空情况刷新redis值
if (userDB != null) {
redisService.set(key + id, userDB);
return userDB;
}
}
return user;
}
@RequestMapping("/getByCache")
@ResponseBody
public User getByCache(String id) {
User user = userService.findById(id);
return user;
}
@ResponseBody
@RequestMapping(value = "/getexpire", method = RequestMethod.GET)
public User findByIdTtl(String id) {
User u = new User();
try{
u = userService.findByIdTtl(id);
}catch (Exception e){
System.err.println(e.getMessage());
}
return u;
}
}
6.访问测试http://127.0.0.1:8080/getUserCache
后台打断点可以进行验证，第一次访问的时候，肯定是去查询数据库的，只要存到redis的数据不过期，以后访问都是直接从redis去拿数据显示，这样就减轻了数据库的压力了，提示性能
7.可以用abtest 来对redis缓存和数据库进行压力测试
apache abtest：ab是Apache HTTP server benchmarking tool，可以用以测试HTTP请求的服务器性能
使用：
ab -n1000 -c10 http://127.0.0.1:8080/getByCache?id=2
ab -n1000 -c10 http://127.0.0.1:8080/getUser?id=2
参数解析
-n:进行http请求的总个数
-c:请求的client个数，也就是请求并发数
统计qps：qps即每秒并发数，request per second
结果：
10个并发的情况下
redis qps：963.85[#/sec] (mean)
DB qps： 766.75 [#/sec] (mean)
100个并发的情况下 1000个
redis qps：1130.60 [#/sec] (mean)
DB qps：956.15 [#/sec] (mean)
100个并发的情况下，进行10000个请求
redsi qps: 2102.39 [#/sec] (mean)
DB qps: 679.07 [#/sec] (mean)
500个并发的情况下，进行10000个请求
redis qps：374.91 [#/sec] (mean)
DB qps：扛不住
排行榜功能实战
继续用上面的代码进行拓展
排行榜：一个很普遍的需求，比如“用户积分榜”，游戏中活跃度排行榜，游戏装备排行榜等。
面临的问题：数据库设计复杂，并发数较高，数据要求实时性高
解决办法：一般排行榜都是有实效性的，所以使用 Redis 中有序集合的特性来实现排行榜是又好又快的选择。
实现
1.表的设计：用户积分表总表(score_flow)、积分流水表(user_score)
用于：1、查top10 2、查用户的排名
CREATE TABLE `score_flow` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`score` bigint(19) unsigned NOT NULL COMMENT '用户积分流水',
`user_id` int(11) unsigned NOT NULL COMMENT '用户主键id',
`user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户姓名',
PRIMARY KEY (`id`),
KEY `idx_userid` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_score` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(11) unsigned NOT NULL COMMENT '用户ID',
`user_score` bigint(19) unsigned NOT NULL COMMENT '用户积分',
`name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
表设计过程中应该注意的点：即数据类型
1）更小的通常更好， 控制字节长度
2）使用合适的数据类型： 如tinyint只占8个位，char(1024)与varchar(1024)的对比,char用于类似定长数据存储比varchar节省空间，如：uuid（32），可以用char(32).
3）尽量避免NULL建议使用NOT NULL DEFAULT ''
4）NULL的列会让索引统计和值比较都更复杂。可为NULL的列会占据更多的磁盘空间，在Mysql中也需要更多复杂的处理程序
索引设计过程中应该注意的点：
1）选择唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录,保证物理上面唯一
2）为经常需要排序、分组和联合操作的字段建立索引 ，经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间
3）常作为查询条件的字段建立索引 ，如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度
4）数据少的地方不必建立索引
2.利用mbg进行代码生成
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<scope>test</scope>
<version>1.3.2</version>
<optional>true</optional>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
3.代码实现
- ScoreFlow ScoreFlowExample UserScore UserScoreExample
public class ScoreFlow {
private Integer id;
private Long score;
private Integer userId;
private String userName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Long getScore() {
return score;
}
public void setScore(Long score) {
this.score = score;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", id=").append(id);
sb.append(", score=").append(score);
sb.append(", userId=").append(userId);
sb.append(", userName=").append(userName);
sb.append("]");
return sb.toString();
}
public ScoreFlow(Long score, Integer userId, String userName) {
this.score = score;
this.userId = userId;
this.userName = userName;
}
}
@Generated("score_flow")
public class ScoreFlowExample {
protected String orderByClause;
protected boolean distinct;
protected List<Criteria> oredCriteria;
private Integer limit;
private Integer offset;
public ScoreFlowExample() {
oredCriteria = new ArrayList<Criteria>();
}
public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
}
public String getOrderByClause() {
return orderByClause;
}
public void setDistinct(boolean distinct) {
this.distinct = distinct;
}
public boolean isDistinct() {
return distinct;
}
public List<Criteria> getOredCriteria() {
return oredCriteria;
}
public void or(Criteria criteria) {
oredCriteria.add(criteria);
}
public Criteria or() {
Criteria criteria = createCriteriaInternal();
oredCriteria.add(criteria);
return criteria;
}
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria);
}
return criteria;
}
protected Criteria createCriteriaInternal() {
Criteria criteria = new Criteria();
return criteria;
}
public void clear() {
oredCriteria.clear();
orderByClause = null;
distinct = false;
}
public void setLimit(Integer limit) {
this.limit = limit;
}
public Integer getLimit() {
return limit;
}
public void setOffset(Integer offset) {
this.offset = offset;
}
public Integer getOffset() {
return offset;
}
protected abstract static class GeneratedCriteria {
protected List<Criterion> criteria;
protected GeneratedCriteria() {
super();
criteria = new ArrayList<Criterion>();
}
public boolean isValid() {
return criteria.size() > 0;
}
public List<Criterion> getAllCriteria() {
return criteria;
}
public List<Criterion> getCriteria() {
return criteria;
}
protected void addCriterion(String condition) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
protected void addCriterion(String condition, Object value, String property) {
if (value == null) {
throw new RuntimeException("Value for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value));
}
protected void addCriterion(String condition, Object value1, Object value2, String property) {
if (value1 == null || value2 == null) {
throw new RuntimeException("Between values for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value1, value2));
}
public Criteria andIdIsNull() {
addCriterion("id is null");
return (Criteria) this;
}
public Criteria andIdIsNotNull() {
addCriterion("id is not null");
return (Criteria) this;
}
public Criteria andIdEqualTo(Integer value) {
addCriterion("id =", value, "id");
return (Criteria) this;
}
public Criteria andIdNotEqualTo(Integer value) {
addCriterion("id <>", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThan(Integer value) {
addCriterion("id >", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThanOrEqualTo(Integer value) {
addCriterion("id >=", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThan(Integer value) {
addCriterion("id <", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThanOrEqualTo(Integer value) {
addCriterion("id <=", value, "id");
return (Criteria) this;
}
public Criteria andIdIn(List<Integer> values) {
addCriterion("id in", values, "id");
return (Criteria) this;
}
public Criteria andIdNotIn(List<Integer> values) {
addCriterion("id not in", values, "id");
return (Criteria) this;
}
public Criteria andIdBetween(Integer value1, Integer value2) {
addCriterion("id between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andIdNotBetween(Integer value1, Integer value2) {
addCriterion("id not between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andScoreIsNull() {
addCriterion("score is null");
return (Criteria) this;
}
public Criteria andScoreIsNotNull() {
addCriterion("score is not null");
return (Criteria) this;
}
public Criteria andScoreEqualTo(Long value) {
addCriterion("score =", value, "score");
return (Criteria) this;
}
public Criteria andScoreNotEqualTo(Long value) {
addCriterion("score <>", value, "score");
return (Criteria) this;
}
public Criteria andScoreGreaterThan(Long value) {
addCriterion("score >", value, "score");
return (Criteria) this;
}
public Criteria andScoreGreaterThanOrEqualTo(Long value) {
addCriterion("score >=", value, "score");
return (Criteria) this;
}
public Criteria andScoreLessThan(Long value) {
addCriterion("score <", value, "score");
return (Criteria) this;
}
public Criteria andScoreLessThanOrEqualTo(Long value) {
addCriterion("score <=", value, "score");
return (Criteria) this;
}
public Criteria andScoreIn(List<Long> values) {
addCriterion("score in", values, "score");
return (Criteria) this;
}
public Criteria andScoreNotIn(List<Long> values) {
addCriterion("score not in", values, "score");
return (Criteria) this;
}
public Criteria andScoreBetween(Long value1, Long value2) {
addCriterion("score between", value1, value2, "score");
return (Criteria) this;
}
public Criteria andScoreNotBetween(Long value1, Long value2) {
addCriterion("score not between", value1, value2, "score");
return (Criteria) this;
}
public Criteria andUserIdIsNull() {
addCriterion("user_id is null");
return (Criteria) this;
}
public Criteria andUserIdIsNotNull() {
addCriterion("user_id is not null");
return (Criteria) this;
}
public Criteria andUserIdEqualTo(Integer value) {
addCriterion("user_id =", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdNotEqualTo(Integer value) {
addCriterion("user_id <>", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdGreaterThan(Integer value) {
addCriterion("user_id >", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdGreaterThanOrEqualTo(Integer value) {
addCriterion("user_id >=", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdLessThan(Integer value) {
addCriterion("user_id <", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdLessThanOrEqualTo(Integer value) {
addCriterion("user_id <=", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdIn(List<Integer> values) {
addCriterion("user_id in", values, "userId");
return (Criteria) this;
}
public Criteria andUserIdNotIn(List<Integer> values) {
addCriterion("user_id not in", values, "userId");
return (Criteria) this;
}
public Criteria andUserIdBetween(Integer value1, Integer value2) {
addCriterion("user_id between", value1, value2, "userId");
return (Criteria) this;
}
public Criteria andUserIdNotBetween(Integer value1, Integer value2) {
addCriterion("user_id not between", value1, value2, "userId");
return (Criteria) this;
}
public Criteria andUserNameIsNull() {
addCriterion("user_name is null");
return (Criteria) this;
}
public Criteria andUserNameIsNotNull() {
addCriterion("user_name is not null");
return (Criteria) this;
}
public Criteria andUserNameEqualTo(String value) {
addCriterion("user_name =", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameNotEqualTo(String value) {
addCriterion("user_name <>", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameGreaterThan(String value) {
addCriterion("user_name >", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameGreaterThanOrEqualTo(String value) {
addCriterion("user_name >=", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameLessThan(String value) {
addCriterion("user_name <", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameLessThanOrEqualTo(String value) {
addCriterion("user_name <=", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameLike(String value) {
addCriterion("user_name like", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameNotLike(String value) {
addCriterion("user_name not like", value, "userName");
return (Criteria) this;
}
public Criteria andUserNameIn(List<String> values) {
addCriterion("user_name in", values, "userName");
return (Criteria) this;
}
public Criteria andUserNameNotIn(List<String> values) {
addCriterion("user_name not in", values, "userName");
return (Criteria) this;
}
public Criteria andUserNameBetween(String value1, String value2) {
addCriterion("user_name between", value1, value2, "userName");
return (Criteria) this;
}
public Criteria andUserNameNotBetween(String value1, String value2) {
addCriterion("user_name not between", value1, value2, "userName");
return (Criteria) this;
}
}
public static class Criteria extends GeneratedCriteria {
protected Criteria() {
super();
}
}
public static class Criterion {
private String condition;
private Object value;
private Object secondValue;
private boolean noValue;
private boolean singleValue;
private boolean betweenValue;
private boolean listValue;
private String typeHandler;
public String getCondition() {
return condition;
}
public Object getValue() {
return value;
}
public Object getSecondValue() {
return secondValue;
}
public boolean isNoValue() {
return noValue;
}
public boolean isSingleValue() {
return singleValue;
}
public boolean isBetweenValue() {
return betweenValue;
}
public boolean isListValue() {
return listValue;
}
public String getTypeHandler() {
return typeHandler;
}
protected Criterion(String condition) {
super();
this.condition = condition;
this.typeHandler = null;
this.noValue = true;
}
protected Criterion(String condition, Object value, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.typeHandler = typeHandler;
if (value instanceof List<?>) {
this.listValue = true;
} else {
this.singleValue = true;
}
}
protected Criterion(String condition, Object value) {
this(condition, value, null);
}
protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.secondValue = secondValue;
this.typeHandler = typeHandler;
this.betweenValue = true;
}
protected Criterion(String condition, Object value, Object secondValue) {
this(condition, value, secondValue, null);
}
}
}
public class UserScore {
private Integer id;
private Integer userId;
private Long userScore;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Long getUserScore() {
return userScore;
}
public void setUserScore(Long userScore) {
this.userScore = userScore;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", id=").append(id);
sb.append(", userId=").append(userId);
sb.append(", userScore=").append(userScore);
sb.append(", name=").append(name);
sb.append("]");
return sb.toString();
}
public UserScore(Integer userId, Long userScore, String name) {
this.userId = userId;
this.userScore = userScore;
this.name = name;
}
public UserScore(){
}
}
@Generated("user_score")
public class UserScoreExample {
protected String orderByClause;
protected boolean distinct;
protected List<Criteria> oredCriteria;
private Integer limit;
private Integer offset;
public UserScoreExample() {
oredCriteria = new ArrayList<Criteria>();
}
public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
}
public String getOrderByClause() {
return orderByClause;
}
public void setDistinct(boolean distinct) {
this.distinct = distinct;
}
public boolean isDistinct() {
return distinct;
}
public List<Criteria> getOredCriteria() {
return oredCriteria;
}
public void or(Criteria criteria) {
oredCriteria.add(criteria);
}
public Criteria or() {
Criteria criteria = createCriteriaInternal();
oredCriteria.add(criteria);
return criteria;
}
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria);
}
return criteria;
}
protected Criteria createCriteriaInternal() {
Criteria criteria = new Criteria();
return criteria;
}
public void clear() {
oredCriteria.clear();
orderByClause = null;
distinct = false;
}
public void setLimit(Integer limit) {
this.limit = limit;
}
public Integer getLimit() {
return limit;
}
public void setOffset(Integer offset) {
this.offset = offset;
}
public Integer getOffset() {
return offset;
}
protected abstract static class GeneratedCriteria {
protected List<Criterion> criteria;
protected GeneratedCriteria() {
super();
criteria = new ArrayList<Criterion>();
}
public boolean isValid() {
return criteria.size() > 0;
}
public List<Criterion> getAllCriteria() {
return criteria;
}
public List<Criterion> getCriteria() {
return criteria;
}
protected void addCriterion(String condition) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
protected void addCriterion(String condition, Object value, String property) {
if (value == null) {
throw new RuntimeException("Value for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value));
}
protected void addCriterion(String condition, Object value1, Object value2, String property) {
if (value1 == null || value2 == null) {
throw new RuntimeException("Between values for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value1, value2));
}
public Criteria andIdIsNull() {
addCriterion("id is null");
return (Criteria) this;
}
public Criteria andIdIsNotNull() {
addCriterion("id is not null");
return (Criteria) this;
}
public Criteria andIdEqualTo(Integer value) {
addCriterion("id =", value, "id");
return (Criteria) this;
}
public Criteria andIdNotEqualTo(Integer value) {
addCriterion("id <>", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThan(Integer value) {
addCriterion("id >", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThanOrEqualTo(Integer value) {
addCriterion("id >=", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThan(Integer value) {
addCriterion("id <", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThanOrEqualTo(Integer value) {
addCriterion("id <=", value, "id");
return (Criteria) this;
}
public Criteria andIdIn(List<Integer> values) {
addCriterion("id in", values, "id");
return (Criteria) this;
}
public Criteria andIdNotIn(List<Integer> values) {
addCriterion("id not in", values, "id");
return (Criteria) this;
}
public Criteria andIdBetween(Integer value1, Integer value2) {
addCriterion("id between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andIdNotBetween(Integer value1, Integer value2) {
addCriterion("id not between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andUserIdIsNull() {
addCriterion("user_id is null");
return (Criteria) this;
}
public Criteria andUserIdIsNotNull() {
addCriterion("user_id is not null");
return (Criteria) this;
}
public Criteria andUserIdEqualTo(Integer value) {
addCriterion("user_id =", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdNotEqualTo(Integer value) {
addCriterion("user_id <>", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdGreaterThan(Integer value) {
addCriterion("user_id >", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdGreaterThanOrEqualTo(Integer value) {
addCriterion("user_id >=", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdLessThan(Integer value) {
addCriterion("user_id <", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdLessThanOrEqualTo(Integer value) {
addCriterion("user_id <=", value, "userId");
return (Criteria) this;
}
public Criteria andUserIdIn(List<Integer> values) {
addCriterion("user_id in", values, "userId");
return (Criteria) this;
}
public Criteria andUserIdNotIn(List<Integer> values) {
addCriterion("user_id not in", values, "userId");
return (Criteria) this;
}
public Criteria andUserIdBetween(Integer value1, Integer value2) {
addCriterion("user_id between", value1, value2, "userId");
return (Criteria) this;
}
public Criteria andUserIdNotBetween(Integer value1, Integer value2) {
addCriterion("user_id not between", value1, value2, "userId");
return (Criteria) this;
}
public Criteria andUserScoreIsNull() {
addCriterion("user_score is null");
return (Criteria) this;
}
public Criteria andUserScoreIsNotNull() {
addCriterion("user_score is not null");
return (Criteria) this;
}
public Criteria andUserScoreEqualTo(Long value) {
addCriterion("user_score =", value, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreNotEqualTo(Long value) {
addCriterion("user_score <>", value, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreGreaterThan(Long value) {
addCriterion("user_score >", value, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreGreaterThanOrEqualTo(Long value) {
addCriterion("user_score >=", value, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreLessThan(Long value) {
addCriterion("user_score <", value, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreLessThanOrEqualTo(Long value) {
addCriterion("user_score <=", value, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreIn(List<Long> values) {
addCriterion("user_score in", values, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreNotIn(List<Long> values) {
addCriterion("user_score not in", values, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreBetween(Long value1, Long value2) {
addCriterion("user_score between", value1, value2, "userScore");
return (Criteria) this;
}
public Criteria andUserScoreNotBetween(Long value1, Long value2) {
addCriterion("user_score not between", value1, value2, "userScore");
return (Criteria) this;
}
public Criteria andNameIsNull() {
addCriterion("name is null");
return (Criteria) this;
}
public Criteria andNameIsNotNull() {
addCriterion("name is not null");
return (Criteria) this;
}
public Criteria andNameEqualTo(String value) {
addCriterion("name =", value, "name");
return (Criteria) this;
}
public Criteria andNameNotEqualTo(String value) {
addCriterion("name <>", value, "name");
return (Criteria) this;
}
public Criteria andNameGreaterThan(String value) {
addCriterion("name >", value, "name");
return (Criteria) this;
}
public Criteria andNameGreaterThanOrEqualTo(String value) {
addCriterion("name >=", value, "name");
return (Criteria) this;
}
public Criteria andNameLessThan(String value) {
addCriterion("name <", value, "name");
return (Criteria) this;
}
public Criteria andNameLessThanOrEqualTo(String value) {
addCriterion("name <=", value, "name");
return (Criteria) this;
}
public Criteria andNameLike(String value) {
addCriterion("name like", value, "name");
return (Criteria) this;
}
public Criteria andNameNotLike(String value) {
addCriterion("name not like", value, "name");
return (Criteria) this;
}
public Criteria andNameIn(List<String> values) {
addCriterion("name in", values, "name");
return (Criteria) this;
}
public Criteria andNameNotIn(List<String> values) {
addCriterion("name not in", values, "name");
return (Criteria) this;
}
public Criteria andNameBetween(String value1, String value2) {
addCriterion("name between", value1, value2, "name");
return (Criteria) this;
}
public Criteria andNameNotBetween(String value1, String value2) {
addCriterion("name not between", value1, value2, "name");
return (Criteria) this;
}
}
public static class Criteria extends GeneratedCriteria {
protected Criteria() {
super();
}
}
public static class Criterion {
private String condition;
private Object value;
private Object secondValue;
private boolean noValue;
private boolean singleValue;
private boolean betweenValue;
private boolean listValue;
private String typeHandler;
public String getCondition() {
return condition;
}
public Object getValue() {
return value;
}
public Object getSecondValue() {
return secondValue;
}
public boolean isNoValue() {
return noValue;
}
public boolean isSingleValue() {
return singleValue;
}
public boolean isBetweenValue() {
return betweenValue;
}
public boolean isListValue() {
return listValue;
}
public String getTypeHandler() {
return typeHandler;
}
protected Criterion(String condition) {
super();
this.condition = condition;
this.typeHandler = null;
this.noValue = true;
}
protected Criterion(String condition, Object value, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.typeHandler = typeHandler;
if (value instanceof List<?>) {
this.listValue = true;
} else {
this.singleValue = true;
}
}
protected Criterion(String condition, Object value) {
this(condition, value, null);
}
protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.secondValue = secondValue;
this.typeHandler = typeHandler;
this.betweenValue = true;
}
protected Criterion(String condition, Object value, Object secondValue) {
this(condition, value, secondValue, null);
}
}
}
- ScoreFlowMapper ScoreFlowSqlProvider
public interface ScoreFlowMapper {
@SelectProvider(type=ScoreFlowSqlProvider.class, method="countByExample")
int countByExample(ScoreFlowExample example);
@DeleteProvider(type=ScoreFlowSqlProvider.class, method="deleteByExample")
int deleteByExample(ScoreFlowExample example);
@Delete({
"delete from score_flow",
"where id = #{id,jdbcType=INTEGER}"
})
int deleteByPrimaryKey(Integer id);
@Insert({
"insert into score_flow (score, user_id, ",
"user_name)",
"values (#{score,jdbcType=BIGINT}, #{userId,jdbcType=INTEGER}, ",
"#{userName,jdbcType=VARCHAR})"
})
@Options(useGeneratedKeys=true,keyProperty="id")
int insert(ScoreFlow record);
@InsertProvider(type=ScoreFlowSqlProvider.class, method="insertSelective")
@Options(useGeneratedKeys=true,keyProperty="id")
int insertSelective(ScoreFlow record);
@SelectProvider(type=ScoreFlowSqlProvider.class, method="selectByExample")
@Results({
@Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="score", property="score", jdbcType=JdbcType.BIGINT),
@Result(column="user_id", property="userId", jdbcType=JdbcType.INTEGER),
@Result(column="user_name", property="userName", jdbcType=JdbcType.VARCHAR)
})
List<ScoreFlow> selectByExample(ScoreFlowExample example);
@Select({
"select",
"id, score, user_id, user_name",
"from score_flow",
"where id = #{id,jdbcType=INTEGER}"
})
@Results({
@Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="score", property="score", jdbcType=JdbcType.BIGINT),
@Result(column="user_id", property="userId", jdbcType=JdbcType.INTEGER),
@Result(column="user_name", property="userName", jdbcType=JdbcType.VARCHAR)
})
ScoreFlow selectByPrimaryKey(Integer id);
@UpdateProvider(type=ScoreFlowSqlProvider.class, method="updateByExampleSelective")
int updateByExampleSelective(@Param("record") ScoreFlow record, @Param("example") ScoreFlowExample example);
@UpdateProvider(type=ScoreFlowSqlProvider.class, method="updateByExample")
int updateByExample(@Param("record") ScoreFlow record, @Param("example") ScoreFlowExample example);
@UpdateProvider(type=ScoreFlowSqlProvider.class, method="updateByPrimaryKeySelective")
int updateByPrimaryKeySelective(ScoreFlow record);
@Update({
"update score_flow",
"set score = #{score,jdbcType=BIGINT},",
"user_id = #{userId,jdbcType=INTEGER},",
"user_name = #{userName,jdbcType=VARCHAR}",
"where id = #{id,jdbcType=INTEGER}"
})
int updateByPrimaryKey(ScoreFlow record);
}
public class ScoreFlowSqlProvider {
public String countByExample(ScoreFlowExample example) {
BEGIN();
SELECT("count(*)");
FROM("score_flow");
applyWhere(example, false);
return SQL();
}
public String deleteByExample(ScoreFlowExample example) {
BEGIN();
DELETE_FROM("score_flow");
applyWhere(example, false);
return SQL();
}
public String insertSelective(ScoreFlow record) {
BEGIN();
INSERT_INTO("score_flow");
if (record.getScore() != null) {
VALUES("score", "#{score,jdbcType=BIGINT}");
}
if (record.getUserId() != null) {
VALUES("user_id", "#{userId,jdbcType=INTEGER}");
}
if (record.getUserName() != null) {
VALUES("user_name", "#{userName,jdbcType=VARCHAR}");
}
return SQL();
}
public String selectByExample(ScoreFlowExample example) {
BEGIN();
if (example != null && example.isDistinct()) {
SELECT_DISTINCT("id");
} else {
SELECT("id");
}
SELECT("score");
SELECT("user_id");
SELECT("user_name");
FROM("score_flow");
applyWhere(example, false);
if (example != null && example.getOrderByClause() != null) {
ORDER_BY(example.getOrderByClause());
}
return SQL();
}
public String updateByExampleSelective(Map<String, Object> parameter) {
ScoreFlow record = (ScoreFlow) parameter.get("record");
ScoreFlowExample example = (ScoreFlowExample) parameter.get("example");
BEGIN();
UPDATE("score_flow");
if (record.getId() != null) {
SET("id = #{record.id,jdbcType=INTEGER}");
}
if (record.getScore() != null) {
SET("score = #{record.score,jdbcType=BIGINT}");
}
if (record.getUserId() != null) {
SET("user_id = #{record.userId,jdbcType=INTEGER}");
}
if (record.getUserName() != null) {
SET("user_name = #{record.userName,jdbcType=VARCHAR}");
}
applyWhere(example, true);
return SQL();
}
public String updateByExample(Map<String, Object> parameter) {
BEGIN();
UPDATE("score_flow");
SET("id = #{record.id,jdbcType=INTEGER}");
SET("score = #{record.score,jdbcType=BIGINT}");
SET("user_id = #{record.userId,jdbcType=INTEGER}");
SET("user_name = #{record.userName,jdbcType=VARCHAR}");
ScoreFlowExample example = (ScoreFlowExample) parameter.get("example");
applyWhere(example, true);
return SQL();
}
public String updateByPrimaryKeySelective(ScoreFlow record) {
BEGIN();
UPDATE("score_flow");
if (record.getScore() != null) {
SET("score = #{score,jdbcType=BIGINT}");
}
if (record.getUserId() != null) {
SET("user_id = #{userId,jdbcType=INTEGER}");
}
if (record.getUserName() != null) {
SET("user_name = #{userName,jdbcType=VARCHAR}");
}
WHERE("id = #{id,jdbcType=INTEGER}");
return SQL();
}
protected void applyWhere(ScoreFlowExample example, boolean includeExamplePhrase) {
if (example == null) {
return;
}
String parmPhrase1;
String parmPhrase1_th;
String parmPhrase2;
String parmPhrase2_th;
String parmPhrase3;
String parmPhrase3_th;
if (includeExamplePhrase) {
parmPhrase1 = "%s #{example.oredCriteria[%d].allCriteria[%d].value}";
parmPhrase1_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
parmPhrase2 = "%s #{example.oredCriteria[%d].allCriteria[%d].value} and #{example.oredCriteria[%d].criteria[%d].secondValue}";
parmPhrase2_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{example.oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
parmPhrase3 = "#{example.oredCriteria[%d].allCriteria[%d].value[%d]}";
parmPhrase3_th = "#{example.oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
} else {
parmPhrase1 = "%s #{oredCriteria[%d].allCriteria[%d].value}";
parmPhrase1_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
parmPhrase2 = "%s #{oredCriteria[%d].allCriteria[%d].value} and #{oredCriteria[%d].criteria[%d].secondValue}";
parmPhrase2_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
parmPhrase3 = "#{oredCriteria[%d].allCriteria[%d].value[%d]}";
parmPhrase3_th = "#{oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
}
StringBuilder sb = new StringBuilder();
List<Criteria> oredCriteria = example.getOredCriteria();
boolean firstCriteria = true;
for (int i = 0; i < oredCriteria.size(); i++) {
Criteria criteria = oredCriteria.get(i);
if (criteria.isValid()) {
if (firstCriteria) {
firstCriteria = false;
} else {
sb.append(" or ");
}
sb.append('(');
List<Criterion> criterions = criteria.getAllCriteria();
boolean firstCriterion = true;
for (int j = 0; j < criterions.size(); j++) {
Criterion criterion = criterions.get(j);
if (firstCriterion) {
firstCriterion = false;
} else {
sb.append(" and ");
}
if (criterion.isNoValue()) {
sb.append(criterion.getCondition());
} else if (criterion.isSingleValue()) {
if (criterion.getTypeHandler() == null) {
sb.append(String.format(parmPhrase1, criterion.getCondition(), i, j));
} else {
sb.append(String.format(parmPhrase1_th, criterion.getCondition(), i, j,criterion.getTypeHandler()));
}
} else if (criterion.isBetweenValue()) {
if (criterion.getTypeHandler() == null) {
sb.append(String.format(parmPhrase2, criterion.getCondition(), i, j, i, j));
} else {
sb.append(String.format(parmPhrase2_th, criterion.getCondition(), i, j, criterion.getTypeHandler(), i, j, criterion.getTypeHandler()));
}
} else if (criterion.isListValue()) {
sb.append(criterion.getCondition());
sb.append(" (");
List<?> listItems = (List<?>) criterion.getValue();
boolean comma = false;
for (int k = 0; k < listItems.size(); k++) {
if (comma) {
sb.append(", ");
} else {
comma = true;
}
if (criterion.getTypeHandler() == null) {
sb.append(String.format(parmPhrase3, i, j, k));
} else {
sb.append(String.format(parmPhrase3_th, i, j, k, criterion.getTypeHandler()));
}
}
sb.append(')');
}
}
sb.append(')');
}
}
if (sb.length() > 0) {
WHERE(sb.toString());
}
}
}
- UserScoreMapper UserScoreSqlProvider
public interface UserScoreMapper {
@SelectProvider(type=UserScoreSqlProvider.class, method="countByExample")
int countByExample(UserScoreExample example);
@DeleteProvider(type=UserScoreSqlProvider.class, method="deleteByExample")
int deleteByExample(UserScoreExample example);
@Delete({
"delete from user_score",
"where id = #{id,jdbcType=INTEGER}"
})
int deleteByPrimaryKey(Integer id);
@Insert({
"insert into user_score (user_id, user_score, ",
"name)",
"values (#{userId,jdbcType=INTEGER}, #{userScore,jdbcType=BIGINT}, ",
"#{name,jdbcType=VARCHAR})"
})
@Options(useGeneratedKeys=true,keyProperty="id")
int insert(UserScore record);
@InsertProvider(type=UserScoreSqlProvider.class, method="insertSelective")
@Options(useGeneratedKeys=true,keyProperty="id")
int insertSelective(UserScore record);
@SelectProvider(type=UserScoreSqlProvider.class, method="selectByExample")
@Results({
@Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="user_id", property="userId", jdbcType=JdbcType.INTEGER),
@Result(column="user_score", property="userScore", jdbcType=JdbcType.BIGINT),
@Result(column="name", property="name", jdbcType=JdbcType.VARCHAR)
})
List<UserScore> selectByExample(UserScoreExample example);
@Select({
"select",
"id, user_id, user_score, name",
"from user_score",
"where id = #{id,jdbcType=INTEGER}"
})
@Results({
@Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="user_id", property="userId", jdbcType=JdbcType.INTEGER),
@Result(column="user_score", property="userScore", jdbcType=JdbcType.BIGINT),
@Result(column="name", property="name", jdbcType=JdbcType.VARCHAR)
})
UserScore selectByPrimaryKey(Integer id);
@UpdateProvider(type=UserScoreSqlProvider.class, method="updateByExampleSelective")
int updateByExampleSelective(@Param("record") UserScore record, @Param("example") UserScoreExample example);
@UpdateProvider(type=UserScoreSqlProvider.class, method="updateByExample")
int updateByExample(@Param("record") UserScore record, @Param("example") UserScoreExample example);
@UpdateProvider(type=UserScoreSqlProvider.class, method="updateByPrimaryKeySelective")
int updateByPrimaryKeySelective(UserScore record);
@Update({
"update user_score",
"set user_id = #{userId,jdbcType=INTEGER},",
"user_score = #{userScore,jdbcType=BIGINT},",
"name = #{name,jdbcType=VARCHAR}",
"where id = #{id,jdbcType=INTEGER}"
})
int updateByPrimaryKey(UserScore record);
}
public class UserScoreSqlProvider {
public String countByExample(UserScoreExample example) {
BEGIN();
SELECT("count(*)");
FROM("user_score");
applyWhere(example, false);
return SQL();
}
public String deleteByExample(UserScoreExample example) {
BEGIN();
DELETE_FROM("user_score");
applyWhere(example, false);
return SQL();
}
public String insertSelective(UserScore record) {
BEGIN();
INSERT_INTO("user_score");
if (record.getUserId() != null) {
VALUES("user_id", "#{userId,jdbcType=INTEGER}");
}
if (record.getUserScore() != null) {
VALUES("user_score", "#{userScore,jdbcType=BIGINT}");
}
if (record.getName() != null) {
VALUES("name", "#{name,jdbcType=VARCHAR}");
}
return SQL();
}
public String selectByExample(UserScoreExample example) {
BEGIN();
if (example != null && example.isDistinct()) {
SELECT_DISTINCT("id");
} else {
SELECT("id");
}
SELECT("user_id");
SELECT("user_score");
SELECT("name");
FROM("user_score");
applyWhere(example, false);
if (example != null && example.getOrderByClause() != null) {
ORDER_BY(example.getOrderByClause());
}
return SQL();
}
public String updateByExampleSelective(Map<String, Object> parameter) {
UserScore record = (UserScore) parameter.get("record");
UserScoreExample example = (UserScoreExample) parameter.get("example");
BEGIN();
UPDATE("user_score");
if (record.getId() != null) {
SET("id = #{record.id,jdbcType=INTEGER}");
}
if (record.getUserId() != null) {
SET("user_id = #{record.userId,jdbcType=INTEGER}");
}
if (record.getUserScore() != null) {
SET("user_score = #{record.userScore,jdbcType=BIGINT}");
}
if (record.getName() != null) {
SET("name = #{record.name,jdbcType=VARCHAR}");
}
applyWhere(example, true);
return SQL();
}
public String updateByExample(Map<String, Object> parameter) {
BEGIN();
UPDATE("user_score");
SET("id = #{record.id,jdbcType=INTEGER}");
SET("user_id = #{record.userId,jdbcType=INTEGER}");
SET("user_score = #{record.userScore,jdbcType=BIGINT}");
SET("name = #{record.name,jdbcType=VARCHAR}");
UserScoreExample example = (UserScoreExample) parameter.get("example");
applyWhere(example, true);
return SQL();
}
public String updateByPrimaryKeySelective(UserScore record) {
BEGIN();
UPDATE("user_score");
if (record.getUserId() != null) {
SET("user_id = #{userId,jdbcType=INTEGER}");
}
if (record.getUserScore() != null) {
SET("user_score = #{userScore,jdbcType=BIGINT}");
}
if (record.getName() != null) {
SET("name = #{name,jdbcType=VARCHAR}");
}
WHERE("id = #{id,jdbcType=INTEGER}");
return SQL();
}
protected void applyWhere(UserScoreExample example, boolean includeExamplePhrase) {
if (example == null) {
return;
}
String parmPhrase1;
String parmPhrase1_th;
String parmPhrase2;
String parmPhrase2_th;
String parmPhrase3;
String parmPhrase3_th;
if (includeExamplePhrase) {
parmPhrase1 = "%s #{example.oredCriteria[%d].allCriteria[%d].value}";
parmPhrase1_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
parmPhrase2 = "%s #{example.oredCriteria[%d].allCriteria[%d].value} and #{example.oredCriteria[%d].criteria[%d].secondValue}";
parmPhrase2_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{example.oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
parmPhrase3 = "#{example.oredCriteria[%d].allCriteria[%d].value[%d]}";
parmPhrase3_th = "#{example.oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
} else {
parmPhrase1 = "%s #{oredCriteria[%d].allCriteria[%d].value}";
parmPhrase1_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
parmPhrase2 = "%s #{oredCriteria[%d].allCriteria[%d].value} and #{oredCriteria[%d].criteria[%d].secondValue}";
parmPhrase2_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
parmPhrase3 = "#{oredCriteria[%d].allCriteria[%d].value[%d]}";
parmPhrase3_th = "#{oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
}
StringBuilder sb = new StringBuilder();
List<Criteria> oredCriteria = example.getOredCriteria();
boolean firstCriteria = true;
for (int i = 0; i < oredCriteria.size(); i++) {
Criteria criteria = oredCriteria.get(i);
if (criteria.isValid()) {
if (firstCriteria) {
firstCriteria = false;
} else {
sb.append(" or ");
}
sb.append('(');
List<Criterion> criterions = criteria.getAllCriteria();
boolean firstCriterion = true;
for (int j = 0; j < criterions.size(); j++) {
Criterion criterion = criterions.get(j);
if (firstCriterion) {
firstCriterion = false;
} else {
sb.append(" and ");
}
if (criterion.isNoValue()) {
sb.append(criterion.getCondition());
} else if (criterion.isSingleValue()) {
if (criterion.getTypeHandler() == null) {
sb.append(String.format(parmPhrase1, criterion.getCondition(), i, j));
} else {
sb.append(String.format(parmPhrase1_th, criterion.getCondition(), i, j,criterion.getTypeHandler()));
}
} else if (criterion.isBetweenValue()) {
if (criterion.getTypeHandler() == null) {
sb.append(String.format(parmPhrase2, criterion.getCondition(), i, j, i, j));
} else {
sb.append(String.format(parmPhrase2_th, criterion.getCondition(), i, j, criterion.getTypeHandler(), i, j, criterion.getTypeHandler()));
}
} else if (criterion.isListValue()) {
sb.append(criterion.getCondition());
sb.append(" (");
List<?> listItems = (List<?>) criterion.getValue();
boolean comma = false;
for (int k = 0; k < listItems.size(); k++) {
if (comma) {
sb.append(", ");
} else {
comma = true;
}
if (criterion.getTypeHandler() == null) {
sb.append(String.format(parmPhrase3, i, j, k));
} else {
sb.append(String.format(parmPhrase3_th, i, j, k, criterion.getTypeHandler()));
}
}
sb.append(')');
}
}
sb.append(')');
}
}
if (sb.length() > 0) {
WHERE(sb.toString());
}
}
}
- RangingService
@Service
public class RangingService implements InitializingBean {
private static final String RANKGNAME = "user_score";
private static final String SALESCORE = "sale_score_rank:";
@Autowired
private RedisService redisService;
@Autowired
private UserMapper userMapper;
@Autowired
private ScoreFlowMapper scoreFlowMapper;
@Autowired
private UserScoreMapper userScoreMapper;
public void rankAdd(String uid, Integer score) {
redisService.zAdd(RANKGNAME, uid, score);
}
public void increSocre(String uid, Integer score) {
redisService.incrementScore(RANKGNAME, uid, score);
}
public Long rankNum(String uid) {
return redisService.zRank(RANKGNAME, uid);
}
public Long score(String uid) {
Long score = redisService.zSetScore(RANKGNAME, uid).longValue();
return score;
}
public Set<ZSetOperations.TypedTuple<Object>> rankWithScore(Integer start, Integer end) {
return redisService.zRankWithScore(RANKGNAME, start, end);
}
public void rankSaleAdd() {
UserScoreExample example = new UserScoreExample();
example.setOrderByClause("id desc");
List<UserScore> userScores = userScoreMapper.selectByExample(example);
userScores.forEach(userScore -> {
String key = userScore.getUserId() + ":" + userScore.getName();
redisService.zAdd(SALESCORE, key, userScore.getUserScore());
});
}
/**
* 添加用户积分
*
* @param uid
* @param score
*/
public void increSaleSocre(String uid, Integer score) {
User user = userMapper.find(uid);
if (user == null) {
return;
}
int uidInt = Integer.parseInt(uid);
long socreLong = Long.parseLong(score + "");
String name = user.getUserName();
String key = uid + ":" + name;
scoreFlowMapper.insertSelective(new ScoreFlow(socreLong, uidInt, name));
userScoreMapper.insertSelective(new UserScore(uidInt, socreLong, name));
redisService.incrementScore(SALESCORE, key, score);
}
public Map<String, Object> userRank(String uid, String name) {
Map<String, Object> retMap = new LinkedHashMap<>();
String key = uid + ":" + name;
Integer rank = redisService.zRank(SALESCORE, key).intValue();
Long score = redisService.zSetScore(SALESCORE, key).longValue();
retMap.put("userId", uid);
retMap.put("score", score);
retMap.put("rank", rank);
return retMap;
}
public List<Map<String, Object>> reverseZRankWithRank(long start, long end) {
Set<ZSetOperations.TypedTuple<Object>> setObj = redisService.reverseZRankWithRank(SALESCORE, start, end);
List<Map<String, Object>> mapList = setObj.stream().map(objectTypedTuple -> {
Map<String, Object> map = new LinkedHashMap<>();
map.put("userId", objectTypedTuple.getValue().toString().split(":")[0]);
map.put("userName", objectTypedTuple.getValue().toString().split(":")[1]);
map.put("score", objectTypedTuple.getScore());
return map;
}).collect(Collectors.toList());
return mapList;
}
public List<Map<String, Object>> saleRankWithScore(Integer start, Integer end) {
Set<ZSetOperations.TypedTuple<Object>> setObj = redisService.reverseZRankWithScore(SALESCORE, start, end);
List<Map<String, Object>> mapList = setObj.stream().map(objectTypedTuple -> {
Map<String, Object> map = new LinkedHashMap<>();
map.put("userId", objectTypedTuple.getValue().toString().split(":")[0]);
map.put("userName", objectTypedTuple.getValue().toString().split(":")[1]);
map.put("score", objectTypedTuple.getScore());
return map;
}).collect(Collectors.toList());
return mapList;
}
@Override
public void afterPropertiesSet() throws Exception {
System.out.println("======enter init bean=======");
this.rankSaleAdd();
}
}
- RankingController
@RestController
public class RankingController {
@Autowired
private RangingService rankingService;
@ResponseBody
@RequestMapping("/addScore")
public String addRank(String uid, Integer score) {
rankingService.rankAdd(uid, score);
return "success";
}
@ResponseBody
@RequestMapping("/increScore")
public String increScore(String uid, Integer score) {
rankingService.increSocre(uid, score);
return "success";
}
@ResponseBody
@RequestMapping("/rank")
public Map<String, Long> rank(String uid) {
Map<String, Long> map = new HashMap<>();
map.put(uid, rankingService.rankNum(uid));
return map;
}
@ResponseBody
@RequestMapping("/score")
public Long rankNum(String uid) {
return rankingService.score(uid);
}
@ResponseBody
@RequestMapping("/scoreByRange")
public Set<ZSetOperations.TypedTuple<Object>> scoreByRange(Integer start, Integer end) {
return rankingService.rankWithScore(start,end);
}
@ResponseBody
@RequestMapping("/sale/increScore")
public String increSaleScore(String uid, Integer score) {
rankingService.increSaleSocre(uid, score);
return "success";
}
@ResponseBody
@RequestMapping("/sale/userScore")
public Map<String,Object> userScore(String uid,String name) {
return rankingService.userRank(uid,name);
}
@ResponseBody
@RequestMapping("/sale/top")
public List<Map<String,Object>> reverseZRankWithRank(long start,long end) {
return rankingService.reverseZRankWithRank(start,end);
}
@ResponseBody
@RequestMapping("/sale/scoreByRange")
public List<Map<String,Object>> saleScoreByRange(Integer start, Integer end) {
return rankingService.saleRankWithScore(start,end);
}
}
初始化缓存加载解析
1）springboot实现初始化加载配置（实现缓存预热）
采用实现springboot ApplicationRunner 该方法仅在SpringApplication.run(…)完成之前调用
2）这里implements InitializingBean是为了：
InitializingBean接口为bean提供了初始化方法的方式，它只包括afterPropertiesSet()方法。 在spring初始化bean的时候，如果bean实现了InitializingBean接口， 在对象的所有属性被初始化后之后才会调用afterPropertiesSet()方法，
3）初始化同步redis数据
4）初始化完成再放入请求
