springboot mysql redis集成

以下写一个demo，以mysql作存储，以redis作缓存。
当服务器接收到数据请求时，先从redis搜索是否有相应数据，没有的话才会去mysql数据库查询。

---

先为我们的数据库准备好一张表和一条数据用来测试：

CREATE TABLE `user` (
  `username` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('name', 'pwd');

即如下：（username主键）

---

然后打开我们的springboot项目：
首先在pom.xml添加依赖：

<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-redis</artifactId>
	</dependency>

	<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.1.1</version>
	</dependency>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-test</artifactId>
		<version>2.0.5.RELEASE</version>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-test</artifactId>
		<version>5.0.9.RELEASE</version>
		<scope>test</scope>
	</dependency>
	<!-- JSONObject对象依赖的jar包 -->
	<dependency>
		<groupId>commons-beanutils</groupId>
		<artifactId>commons-beanutils</artifactId>
		<version>1.9.3</version>
	</dependency>
	<dependency>
		<groupId>commons-collections</groupId>
		<artifactId>commons-collections</artifactId>
		<version>3.2.1</version>
	</dependency>
	<dependency>
		<groupId>commons-lang</groupId>
		<artifactId>commons-lang</artifactId>
		<version>2.6</version>
	</dependency>
	<dependency>
		<groupId>commons-logging</groupId>
		<artifactId>commons-logging</artifactId>
		<version>1.1.1</version>
	</dependency>
	<dependency>
		<groupId>net.sf.ezmorph</groupId>
		<artifactId>ezmorph</artifactId>
		<version>1.0.6</version>
	</dependency>
	<dependency>
		<groupId>net.sf.json-lib</groupId>
		<artifactId>json-lib</artifactId>
		<version>2.2.3</version>
		<classifier>jdk15</classifier><!-- 指定jdk版本 -->
	</dependency>
	<!-- Json依赖架包下载 -->
</dependencies>

---

更改配置文件applicaiton.properties：
笔者习惯yml格式，所以这里将配置文件改为了application.yml：

server:
  port: 8080
  tomcat:
    uri-encoding: UTF-8

spring:
  http:
    encoding:
      charset: UTF-8
      force: true
      enabled: true

  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    # mysql数据库位置
    url: jdbc:mysql://localhost:3306/redisdemo
    username: root
    password: root
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

  redis:
    # Redis数据库索引（默认为0）
    database: 0
    # Redis服务器地址
    host: 127.0.0.1
    # Redis服务器连接端口
    port: 6379
    # Redis服务器连接密码（默认为空）
    password:
    # 连接超时时间（毫秒）
    timeout: 1000
    jedis.pool:
    # 连接池最大连接数（使用负值表示没有限制）
      max-active: 8
    # 连接池最大阻塞等待时间（使用负值表示没有限制）
      max-wait: -1
    # 连接池中的最大空闲连接
      max-idle: 8
    # 连接池中的最小空闲连接
      min-idle: 0

---

创建对应数据库表信息的entity实体：

public class User {
    private String username;
    private String password;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public User(String username, String password) {

        this.username = username;
        this.password = password;
    }

    public User() {

    }
}

---

Mapper映射层：

@Mapper
public interface UserMapper {
    @Select("select * from user")
    List<User> getUserList();
}

---

Controller层：
在这里我们使用RedisTemplate来操作redis：

/**
 * @author yiannis
 */
@RestController
public class GetUser {

    @Autowired
    UserMapper userMapper;

    @Autowired
    private RedisTemplate redisTemplate;

    @PostMapping("/getUsers")
    String getUsers(){
        //获取redis中key="userJSON"的value
        String userJSON = (String)redisTemplate.opsForValue().get("userJSON");
        if(userJSON!=null){
            return userJSON;
        }

        //在redis中获取不到则执行以下代码
        List<User> userList = userMapper.getUserList();
        //将userList转为json字符串
        userJSON = JSONArray.fromObject(userList).toString();
        //将查询结果存入redis中作缓存
        //四个参数从左至右分别为：key，value，有效时间，时间单位
        //这里即：创建一个key="userJSON",value=#{userJSON}的键值对，其有效时间是10秒
        redisTemplate.opsForValue().set("userJSON",userJSON,10, TimeUnit.SECONDS);
        return userJSON;
    }
}

---

以上，demo就算是完成了。接下来我们做一个测试：
PS：首先请确保你的redis和mysql运行正常，并且已经在yml（properties）里配置好了。
修改GetUser的代码：

/**
 * @author yiannis
 */
@RestController
public class GetUser {

    @Autowired
    UserMapper userMapper;

    @Autowired
    private RedisTemplate redisTemplate;

    @PostMapping("/getUsers")
    String getUsers(){
        long startTime;
        long endTime;
        startTime = System.currentTimeMillis();
        //获取redis中key="userJSON"的value
        String userJSON = (String)redisTemplate.opsForValue().get("userJSON");
        if(userJSON!=null){
            endTime = System.currentTimeMillis();
            System.out.println("取缓存成功，耗时" + (endTime-startTime) + "ms");
            return userJSON;
        }

        //在redis中获取不到则执行以下代码
        startTime = System.currentTimeMillis();
        List<User> userList = userMapper.getUserList();
        //将userList转为json字符串
        userJSON = JSONArray.fromObject(userList).toString();
        endTime = System.currentTimeMillis();
        //将查询结果存入redis中作缓存
        //四个参数从左至右分别为：key，value，有效时间，时间单位
        //这里即：创建一个key="userJSON",value=#{userJSON}的键值对，其有效时间是10秒
        redisTemplate.opsForValue().set("userJSON",userJSON,10, TimeUnit.SECONDS);
        System.out.println("取缓存失败，耗时" + (endTime-startTime) + "ms");
        return userJSON;
    }
}

为便于测试，我们在user表中添加些字段：

INSERT INTO `user` VALUES ('AFG', 'Afghanistan');
INSERT INTO `user` VALUES ('AGO', 'Angola');
INSERT INTO `user` VALUES ('AIA', 'Anguilla');
INSERT INTO `user` VALUES ('ALB', 'Albania');
INSERT INTO `user` VALUES ('AND', 'Andorra');
INSERT INTO `user` VALUES ('ANT', 'Netherlands Antilles');
INSERT INTO `user` VALUES ('ARE', 'United Arab Emirates');
INSERT INTO `user` VALUES ('ARG', 'Argentina');
INSERT INTO `user` VALUES ('ARM', 'Armenia');
INSERT INTO `user` VALUES ('ASM', 'American Samoa');
INSERT INTO `user` VALUES ('ATA', 'Antarctica');
INSERT INTO `user` VALUES ('ATF', 'French Southern territories');
INSERT INTO `user` VALUES ('ATG', 'Antigua and Barbuda');
INSERT INTO `user` VALUES ('AUS', 'Australia');
INSERT INTO `user` VALUES ('AUT', 'Austria');
INSERT INTO `user` VALUES ('AZE', 'Azerbaijan');
INSERT INTO `user` VALUES ('BDI', 'Burundi');
INSERT INTO `user` VALUES ('BEL', 'Belgium');
INSERT INTO `user` VALUES ('BEN', 'Benin');
INSERT INTO `user` VALUES ('BFA', 'Burkina Faso');
INSERT INTO `user` VALUES ('BGD', 'Bangladesh');
INSERT INTO `user` VALUES ('BGR', 'Bulgaria');
INSERT INTO `user` VALUES ('BHR', 'Bahrain');
INSERT INTO `user` VALUES ('BHS', 'Bahamas');
INSERT INTO `user` VALUES ('BIH', 'Bosnia and Herzegovina');
INSERT INTO `user` VALUES ('BLR', 'Belarus');
INSERT INTO `user` VALUES ('BLZ', 'Belize');
INSERT INTO `user` VALUES ('BMU', 'Bermuda');
INSERT INTO `user` VALUES ('BOL', 'Bolivia');
INSERT INTO `user` VALUES ('BRA', 'Brazil');
INSERT INTO `user` VALUES ('BRB', 'Barbados');
INSERT INTO `user` VALUES ('BRN', 'Brunei');
INSERT INTO `user` VALUES ('BTN', 'Bhutan');
INSERT INTO `user` VALUES ('BVT', 'Bouvet Island');
INSERT INTO `user` VALUES ('BWA', 'Botswana');
INSERT INTO `user` VALUES ('CAF', 'Central African Republic');
INSERT INTO `user` VALUES ('CAN', 'Canada');
INSERT INTO `user` VALUES ('CCK', 'Cocos (Keeling) Islands');
INSERT INTO `user` VALUES ('CHE', 'Switzerland');
INSERT INTO `user` VALUES ('CHL', 'Chile');
INSERT INTO `user` VALUES ('CHN', 'China');
INSERT INTO `user` VALUES ('CIV', 'Côte d’Ivoire');
INSERT INTO `user` VALUES ('CMR', 'Cameroon');
INSERT INTO `user` VALUES ('COD', 'Congo, The Democratic Republic of the');
INSERT INTO `user` VALUES ('COG', 'Congo');
INSERT INTO `user` VALUES ('COK', 'Cook Islands');
INSERT INTO `user` VALUES ('COL', 'Colombia');
INSERT INTO `user` VALUES ('COM', 'Comoros');
INSERT INTO `user` VALUES ('CPV', 'Cape Verde');
INSERT INTO `user` VALUES ('CRI', 'Costa Rica');
INSERT INTO `user` VALUES ('CUB', 'Cuba');
INSERT INTO `user` VALUES ('CXR', 'Christmas Island');
INSERT INTO `user` VALUES ('CYM', 'Cayman Islands');
INSERT INTO `user` VALUES ('CYP', 'Cyprus');
INSERT INTO `user` VALUES ('CZE', 'Czech Republic');
INSERT INTO `user` VALUES ('DEU', 'Germany');
INSERT INTO `user` VALUES ('DJI', 'Djibouti');
INSERT INTO `user` VALUES ('DMA', 'Dominica');
INSERT INTO `user` VALUES ('DNK', 'Denmark');
INSERT INTO `user` VALUES ('DOM', 'Dominican Republic');
INSERT INTO `user` VALUES ('DZA', 'Algeria');
INSERT INTO `user` VALUES ('ECU', 'Ecuador');
INSERT INTO `user` VALUES ('EGY', 'Egypt');
INSERT INTO `user` VALUES ('ERI', 'Eritrea');
INSERT INTO `user` VALUES ('ESH', 'Western Sahara');
INSERT INTO `user` VALUES ('ESP', 'Spain');
INSERT INTO `user` VALUES ('EST', 'Estonia');
INSERT INTO `user` VALUES ('ETH', 'Ethiopia');
INSERT INTO `user` VALUES ('FIN', 'Finland');
INSERT INTO `user` VALUES ('FJI', 'Fiji Islands');
INSERT INTO `user` VALUES ('FLK', 'Falkland Islands');
INSERT INTO `user` VALUES ('FRA', 'France');
INSERT INTO `user` VALUES ('FRO', 'Faroe Islands');
INSERT INTO `user` VALUES ('FSM', 'Micronesia, Federated States of');
INSERT INTO `user` VALUES ('GAB', 'Gabon');
INSERT INTO `user` VALUES ('GBR', 'United Kingdom');
INSERT INTO `user` VALUES ('GEO', 'Georgia');
INSERT INTO `user` VALUES ('GHA', 'Ghana');
INSERT INTO `user` VALUES ('GIB', 'Gibraltar');
INSERT INTO `user` VALUES ('GIN', 'Guinea');
INSERT INTO `user` VALUES ('GLP', 'Guadeloupe');
INSERT INTO `user` VALUES ('GMB', 'Gambia');
INSERT INTO `user` VALUES ('GNB', 'Guinea-Bissau');
INSERT INTO `user` VALUES ('GNQ', 'Equatorial Guinea');
INSERT INTO `user` VALUES ('GRC', 'Greece');
INSERT INTO `user` VALUES ('GRD', 'Grenada');
INSERT INTO `user` VALUES ('GRL', 'Greenland');
INSERT INTO `user` VALUES ('GTM', 'Guatemala');
INSERT INTO `user` VALUES ('GUF', 'French Guiana');
INSERT INTO `user` VALUES ('GUM', 'Guam');
INSERT INTO `user` VALUES ('GUY', 'Guyana');
INSERT INTO `user` VALUES ('HKG', '*');
INSERT INTO `user` VALUES ('HMD', 'Heard Island and McDonald Islands');
INSERT INTO `user` VALUES ('HND', 'Honduras');
INSERT INTO `user` VALUES ('HRV', 'Croatia');
INSERT INTO `user` VALUES ('HTI', 'Haiti');
INSERT INTO `user` VALUES ('HUN', 'Hungary');
INSERT INTO `user` VALUES ('IDN', 'Indonesia');
INSERT INTO `user` VALUES ('IND', 'India');
INSERT INTO `user` VALUES ('IOT', 'British Indian Ocean Territory');
INSERT INTO `user` VALUES ('IRL', 'Ireland');
INSERT INTO `user` VALUES ('IRN', 'Iran');
INSERT INTO `user` VALUES ('IRQ', 'Iraq');
INSERT INTO `user` VALUES ('ISL', 'Iceland');
INSERT INTO `user` VALUES ('ISR', 'Israel');
INSERT INTO `user` VALUES ('ITA', 'Italy');
INSERT INTO `user` VALUES ('JAM', 'Jamaica');
INSERT INTO `user` VALUES ('JOR', 'Jordan');
INSERT INTO `user` VALUES ('JPN', 'Japan');
INSERT INTO `user` VALUES ('KAZ', 'Kazakstan');
INSERT INTO `user` VALUES ('KEN', 'Kenya');
INSERT INTO `user` VALUES ('KGZ', 'Kyrgyzstan');
INSERT INTO `user` VALUES ('KHM', 'Cambodia');
INSERT INTO `user` VALUES ('KIR', 'Kiribati');
INSERT INTO `user` VALUES ('KNA', 'Saint Kitts and Nevis');
INSERT INTO `user` VALUES ('KOR', 'South Korea');
INSERT INTO `user` VALUES ('KWT', 'Kuwait');
INSERT INTO `user` VALUES ('LAO', 'Laos');
INSERT INTO `user` VALUES ('LBN', 'Lebanon');
INSERT INTO `user` VALUES ('LBR', 'Liberia');
INSERT INTO `user` VALUES ('LBY', 'Libyan Arab Jamahiriya');
INSERT INTO `user` VALUES ('LCA', 'Saint Lucia');
INSERT INTO `user` VALUES ('LIE', 'Liechtenstein');
INSERT INTO `user` VALUES ('LKA', 'Sri Lanka');
INSERT INTO `user` VALUES ('LSO', 'Lesotho');
INSERT INTO `user` VALUES ('LTU', 'Lithuania');
INSERT INTO `user` VALUES ('LUX', 'Luxembourg');
INSERT INTO `user` VALUES ('LVA', 'Latvia');
INSERT INTO `user` VALUES ('MAC', 'Macao');
INSERT INTO `user` VALUES ('MAR', 'Morocco');
INSERT INTO `user` VALUES ('MCO', 'Monaco');
INSERT INTO `user` VALUES ('MDA', 'Moldova');
INSERT INTO `user` VALUES ('MDG', 'Madagascar');
INSERT INTO `user` VALUES ('MDV', 'Maldives');
INSERT INTO `user` VALUES ('MEX', 'Mexico');
INSERT INTO `user` VALUES ('MHL', 'Marshall Islands');
INSERT INTO `user` VALUES ('MKD', 'Macedonia');
INSERT INTO `user` VALUES ('MLI', 'Mali');
INSERT INTO `user` VALUES ('MLT', 'Malta');
INSERT INTO `user` VALUES ('MMR', 'Myanmar');
INSERT INTO `user` VALUES ('MNG', '*');
INSERT INTO `user` VALUES ('MNP', 'Northern Mariana Islands');
INSERT INTO `user` VALUES ('MOZ', 'Mozambique');
INSERT INTO `user` VALUES ('MRT', 'Mauritania');
INSERT INTO `user` VALUES ('MSR', 'Montserrat');
INSERT INTO `user` VALUES ('MTQ', 'Martinique');
INSERT INTO `user` VALUES ('MUS', 'Mauritius');
INSERT INTO `user` VALUES ('MWI', 'Malawi');
INSERT INTO `user` VALUES ('MYS', 'Malaysia');
INSERT INTO `user` VALUES ('MYT', 'Mayotte');
INSERT INTO `user` VALUES ('NAM', 'Namibia');
INSERT INTO `user` VALUES ('name', 'pwd');
INSERT INTO `user` VALUES ('NCL', 'New Caledonia');
INSERT INTO `user` VALUES ('NER', 'Niger');
INSERT INTO `user` VALUES ('NFK', 'Norfolk Island');
INSERT INTO `user` VALUES ('NGA', 'Nigeria');
INSERT INTO `user` VALUES ('NIC', 'Nicaragua');
INSERT INTO `user` VALUES ('NIU', 'Niue');
INSERT INTO `user` VALUES ('NLD', 'Netherlands');
INSERT INTO `user` VALUES ('NOR', 'Norway');
INSERT INTO `user` VALUES ('NPL', 'Nepal');
INSERT INTO `user` VALUES ('NRU', 'Nauru');
INSERT INTO `user` VALUES ('NZL', 'New Zealand');
INSERT INTO `user` VALUES ('OMN', 'Oman');
INSERT INTO `user` VALUES ('PAK', 'Pakistan');
INSERT INTO `user` VALUES ('PAN', 'Panama');
INSERT INTO `user` VALUES ('PCN', 'Pitcairn');
INSERT INTO `user` VALUES ('PER', 'Peru');
INSERT INTO `user` VALUES ('PHL', 'Philippines');
INSERT INTO `user` VALUES ('PLW', 'Palau');
INSERT INTO `user` VALUES ('PNG', 'Papua New Guinea');
INSERT INTO `user` VALUES ('POL', 'Poland');
INSERT INTO `user` VALUES ('PRI', 'Puerto Rico');
INSERT INTO `user` VALUES ('PRK', 'North Korea');
INSERT INTO `user` VALUES ('PRT', 'Portugal');
INSERT INTO `user` VALUES ('PRY', 'Paraguay');
INSERT INTO `user` VALUES ('PSE', 'Palestine');
INSERT INTO `user` VALUES ('PYF', 'French Polynesia');
INSERT INTO `user` VALUES ('QAT', 'Qatar');
INSERT INTO `user` VALUES ('REU', 'Réunion');
INSERT INTO `user` VALUES ('ROM', 'Romania');
INSERT INTO `user` VALUES ('RUS', 'Russian Federation');
INSERT INTO `user` VALUES ('RWA', 'Rwanda');
INSERT INTO `user` VALUES ('SAU', 'Saudi Arabia');
INSERT INTO `user` VALUES ('SDN', 'Sudan');
INSERT INTO `user` VALUES ('SEN', 'Senegal');
INSERT INTO `user` VALUES ('SGP', 'Singapore');
INSERT INTO `user` VALUES ('SGS', 'South Georgia and the South Sandwich Islands');
INSERT INTO `user` VALUES ('SHN', 'Saint Helena');
INSERT INTO `user` VALUES ('SJM', 'Svalbard and Jan Mayen');
INSERT INTO `user` VALUES ('SLB', 'Solomon Islands');
INSERT INTO `user` VALUES ('SLE', 'Sierra Leone');
INSERT INTO `user` VALUES ('SLV', 'El Salvador');
INSERT INTO `user` VALUES ('SMR', 'San Marino');
INSERT INTO `user` VALUES ('SOM', 'Somalia');
INSERT INTO `user` VALUES ('SPM', 'Saint Pierre and Miquelon');
INSERT INTO `user` VALUES ('STP', 'Sao Tome and Principe');
INSERT INTO `user` VALUES ('SUR', 'Suriname');
INSERT INTO `user` VALUES ('SVK', 'Slovakia');
INSERT INTO `user` VALUES ('SVN', 'Slovenia');
INSERT INTO `user` VALUES ('SWE', 'Sweden');
INSERT INTO `user` VALUES ('SWZ', 'Swaziland');
INSERT INTO `user` VALUES ('SYC', 'Seychelles');
INSERT INTO `user` VALUES ('SYR', 'Syria');
INSERT INTO `user` VALUES ('TCA', 'Turks and Caicos Islands');
INSERT INTO `user` VALUES ('TCD', 'Chad');
INSERT INTO `user` VALUES ('TGO', 'Togo');
INSERT INTO `user` VALUES ('THA', 'Thailand');
INSERT INTO `user` VALUES ('TJK', 'Tajikistan');
INSERT INTO `user` VALUES ('TKL', 'Tokelau');
INSERT INTO `user` VALUES ('TKM', 'Turkmenistan');
INSERT INTO `user` VALUES ('TMP', 'East Timor');
INSERT INTO `user` VALUES ('TON', 'Tonga');
INSERT INTO `user` VALUES ('TTO', 'Trinidad and Tobago');
INSERT INTO `user` VALUES ('TUN', 'Tunisia');
INSERT INTO `user` VALUES ('TUR', 'Turkey');
INSERT INTO `user` VALUES ('TUV', 'Tuvalu');
INSERT INTO `user` VALUES ('TWN', '*');
INSERT INTO `user` VALUES ('TZA', 'Tanzania');
INSERT INTO `user` VALUES ('UGA', 'Uganda');
INSERT INTO `user` VALUES ('UKR', 'Ukraine');
INSERT INTO `user` VALUES ('UMI', 'United States Minor Outlying Islands');
INSERT INTO `user` VALUES ('URY', 'Uruguay');
INSERT INTO `user` VALUES ('USA', 'United States');
INSERT INTO `user` VALUES ('UZB', 'Uzbekistan');
INSERT INTO `user` VALUES ('VAT', 'Holy See (Vatican City State)');
INSERT INTO `user` VALUES ('VCT', 'Saint Vincent and the Grenadines');
INSERT INTO `user` VALUES ('VEN', 'Venezuela');
INSERT INTO `user` VALUES ('VGB', 'Virgin Islands, British');
INSERT INTO `user` VALUES ('VIR', 'Virgin Islands, U.S.');
INSERT INTO `user` VALUES ('VNM', 'Vietnam');
INSERT INTO `user` VALUES ('VUT', 'Vanuatu');
INSERT INTO `user` VALUES ('WLF', 'Wallis and Futuna');
INSERT INTO `user` VALUES ('WSM', 'Samoa');
INSERT INTO `user` VALUES ('YEM', 'Yemen');
INSERT INTO `user` VALUES ('YUG', 'Yugoslavia');
INSERT INTO `user` VALUES ('ZAF', 'South Africa');
INSERT INTO `user` VALUES ('ZMB', 'Zambia');
INSERT INTO `user` VALUES ('ZWE', 'Zimbabwe');

---

运行我们的程序，以下利用postman模拟请求：
连续发起多次相同请求后，查看我们的控制台输出：

取缓存失败，耗时16ms
取缓存成功，耗时2ms
取缓存成功，耗时2ms
取缓存成功，耗时2ms
取缓存失败，耗时18ms

redis缓存为我们节省了约10ms出头的时间。

---

以上资源请参考springboot+mysql+redis集成Demo