springboot-数据库

MySQL

• 准备工作，加入数据库的配置和依赖

  为了使用 jpa 和 mysql，在 pom.xml文件中增加依赖。

  <!--jpa--><dependency>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-starter-data-jpa</artifactId></dependency><!--mysql--><dependency>  <groupId>mysql</groupId>  <artifactId>mysql-connector-java</artifactId></dependency>

  application.yml 配置 mysql 连接信息

  spring:  datasource:    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8    username: root    password: root# jpa  jpa:    database: MYSQL    show-sql: true# Hibernate ddl auto (validate|create|create-drop|update)    hibernate:      ddl-auto: update      naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy    properties:      hibernate:        dialect: org.hibernate.dialect.MySQL5Dialect

  Person.java

  @Entitypublic class Person {    @Id    @GeneratedValue    private Integer id;    private String name;    private Integer age;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }}

  创建接口继承JpaRepository，泛型使用的是

  public interface PersonRespository extends JpaRepository<Person, Integer> {}

• 单表简单查询

  HelloController.java

  @RestControllerpublic class HelloController {    @Autowired    private PersonRespository personRespository;    @PostMapping(value = "/addPerson")    public Person addPerson(@RequestParam(value = "name") String name, @RequestParam(value = "age") Integer age) {        Person person = new Person();        person.setName(name);        person.setAge(age);        return personRespository.save(person);    }        @DeleteMapping(value = "/delPerson/{id}")    public void delPerson(@PathVariable(value = "id") Integer id) {        personRespository.delete(id);    }    @PutMapping(value = "/updatePerson")    public Person updatePerson(@RequestParam(value = "id") Integer id, @RequestParam(value = "name") String name, @RequestParam(value = "age") Integer age) {        Person person = new Person();        person.setId(id);        person.setName(name);        person.setAge(age);        return personRespository.save(person);    }    @GetMapping(value = "/findPerson/{id}")    public Person findPerson(@PathVariable(value = "id") Integer id) {        return personRespository.findOne(id);    }    @GetMapping(value = "/findPersons")    public List<Person> findPerson() {        return personRespository.findAll();    }}

• 单表根据年龄查询

  PersonRespository.java

  增加一个根据年龄查询的抽象方法

  public interface PersonRespository extends JpaRepository<Person, Integer> {    Person findByAge(Integer age);}

  HelloController.java

  @RestControllerpublic class HelloController {    @Autowired    private PersonRespository personRespository;    @GetMapping(value = "/findPersonByAge/{age}")    public Person findPersonByAge(@PathVariable(value = "age") Integer age) {        return personRespository.findByAge(age);    }}

---

集成 Redis

pom.xml

<!--redis--><dependency>    <groupId>redis.clients</groupId>    <artifactId>jedis</artifactId></dependency><dependency>    <groupId>org.springframework.data</groupId>    <artifactId>spring-data-redis</artifactId></dependency><!--gson--><dependency>    <groupId>com.google.code.gson</groupId>    <artifactId>gson</artifactId>    <version>2.2.4</version></dependency>

RedisTemplate.java Redis 配置

/** * RedisTemplate 初始化 */@Configurationpublic class RedisConfig {    @Bean    public RedisTemplate<String, String> redisTemplate(RedisConnectionFactory factory) {        StringRedisTemplate template = new StringRedisTemplate(factory);        Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer(Object.class);        ObjectMapper om = new ObjectMapper();        om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);        om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);        jackson2JsonRedisSerializer.setObjectMapper(om);        template.setValueSerializer(jackson2JsonRedisSerializer);        template.afterPropertiesSet();        return template;    }}

application.yml 配置 redis 连接信息

spring:# redis 配置，没有密码的话，可以不写 password 或者 password 值不填写  redis:    host: 192.168.18.130    port: 6379    pool:      max-idle: 8      min-idle: 0      max-active: 8      max-wait: -1

UserRedis.java 用户模块的 redis 增删改查

@Repositorypublic class UserRedis {    @Autowired    private RedisTemplate<String, String> redisTemplate;    public void add(String key, Long time, User user) {        Gson gson = new Gson();        redisTemplate.opsForValue().set(key, gson.toJson(user), time, TimeUnit.MINUTES);    }    public void add(String key, Long time, List<User> users) {        Gson gson = new Gson();        redisTemplate.opsForValue().set(key, gson.toJson(users), time, TimeUnit.MINUTES);    }    public User get(String key) {        Gson gson = new Gson();        User user = null;        String json = redisTemplate.opsForValue().get(key);        if (!StringUtils.isEmpty(json))            user = gson.fromJson(json, User.class);        return user;    }    public List<User> getList(String key) {        Gson gson = new Gson();        List<User> ts = null;        String listJson = redisTemplate.opsForValue().get(key);        if (!StringUtils.isEmpty(listJson))            ts = gson.fromJson(listJson, new TypeToken<List<User>>() {            }.getType());        return ts;    }    public void delete(String key) {        redisTemplate.opsForValue().getOperations().delete(key);    }}

RedisController.java 接口类测试

@RestController@RequestMapping(value = "/springboot")public class RedisController {    private static Logger logger = LoggerFactory.getLogger(RedisController.class);    @Autowired    UserRedis userRedis;    @Autowired    RoleService roleService;    @RequestMapping(value = "/getRedis")    public void getRedis() {        Department deparment = new Department();        deparment.setName("开发部");        Role role = new Role();        role.setName("admin");        User user = new User();        user.setName("user");        user.setCreatedate(new Date());        user.setDeparment(deparment);        List<Role> roles = new ArrayList<>();        roles.add(role);        user.setRoles(roles);        userRedis.delete(this.getClass().getName() + ":userByname:" + user.getName());        userRedis.add(this.getClass().getName() + ":userByname:" + user.getName(), 10L, user);        User userRes = userRedis.get(this.getClass().getName() + ":userByname:user");        Assert.notNull(userRes);        logger.info("======userRes====== name:{}, deparment:{}, role:{}",                userRes.getName(), userRes.getDeparment().getName(), userRes.getRoles().get(0).getName());    }    @RequestMapping(value = "/springcache_redis_create")    public Role springcache_redis_create() {        Role role = new Role();        role.setName("jack");        return roleService.create(role);    }    @RequestMapping(value = "/springcache_redis_find/{id}", method = RequestMethod.GET)    public Role springcache_redis_find(@PathVariable(value = "id") Long id) {        return roleService.findById(id);    }    @RequestMapping(value = "/springcache_redis_update/{id}", method = RequestMethod.GET)    public Role springcache_redis_update(@PathVariable(value = "id") Long id) {        Role role = roleService.findById(id);        role.setName("rose");        return roleService.update(role);    }    @RequestMapping(value = "/springcache_redis_delete/{id}", method = RequestMethod.GET)    public void springcache_redis_delete(@PathVariable(value = "id") Long id) {        roleService.delete(id);    }}

---

Redis 缓存优化

Spring Cache 注解

结构简单的对象，即没有包含其他对象的实体，可以用 spring cache 的方式使用 redis 缓存，前提是打开 spring cache。

• Spring Cache 配置

  /** * 结构简单的对象，既没有包含其他对象的实体，可以用 spring cache 的方式使用 redis 缓存 * 前提是打开 spring cache */@Configuration@EnableCachingpublic class RedisConfig extends CachingConfigurerSupport {    @Bean    public RedisTemplate<String, String> redisTemplate(RedisConnectionFactory factory) {        StringRedisTemplate template = new StringRedisTemplate(factory);        Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer(Object.class);        ObjectMapper om = new ObjectMapper();        om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);        om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);        jackson2JsonRedisSerializer.setObjectMapper(om);        template.setValueSerializer(jackson2JsonRedisSerializer);        template.afterPropertiesSet();        return template;    }    /**     * Spring Cache 配置     * @param redisTemplate     * @return     */    @Bean    public CacheManager cacheManager(@SuppressWarnings("rawtypes") RedisTemplate redisTemplate) {        RedisCacheManager manager = new RedisCacheManager(redisTemplate);        manager.setDefaultExpiration(43200);//12小时        return manager;    }    /**     * simpleKey 根据 类名+参数 id 作为唯一标识     * @Cacheable 存取缓存     * @CachePut 更新缓存     * @CacheEvict 删除缓存     * 注解中的 value 参数是一个 key 的前缀，     * 并由 keyGenerator 按照一定的规则生成一个唯一的标识     */    @Bean    public KeyGenerator simpleKey() {        return new KeyGenerator() {            @Override            public Object generate(Object target, Method method, Object... params) {                StringBuilder sb = new StringBuilder();                sb.append(target.getClass().getName() + ":");                for (Object obj : params) {                    sb.append(obj.toString());                }                return sb.toString();            }        };    }    /**     * objectId 根据 类名+参数 id 作为唯一标识     * @Cacheable 存取缓存     * @CachePut 更新缓存     * @CacheEvict 删除缓存     * 注解中的 value 参数是一个 key 的前缀，     * 并由 keyGenerator 按照一定的规则生成一个唯一的标识     */    @Bean    public KeyGenerator objectId() {        return new KeyGenerator() {            @Override            public Object generate(Object target, Method method, Object... params) {                StringBuilder sb = new StringBuilder();                sb.append(target.getClass().getName() + ":");                try {                    sb.append(params[0].getClass().getMethod("getId", null).invoke(params[0], null).toString());                } catch (NoSuchMethodException no) {                    no.printStackTrace();                } catch (IllegalAccessException il) {                    il.printStackTrace();                } catch (InvocationTargetException iv) {                    iv.printStackTrace();                }                return sb.toString();            }        };    }}

  RoleService.java 使用 Spring Cache 注解来用 redis 操作。

  @Servicepublic class RoleService {    @Autowired    private RoleRepository roleRepository;    /**     * @Cacheable(value = "mysql:findById:role", keyGenerator = "simpleKey")     * value = "mysql:findById:role" 是一个key 的前缀，并由 keyGenerator 按照一定的规则生成一个唯一的标识     * @Cacheable 存取缓存     * @CachePut 更新缓存     * @CacheEvict 删除缓存     */    @Cacheable(value = "mysql:findById:role", keyGenerator = "simpleKey")    public Role findById(Long id) {        return roleRepository.findOne(id);    }    @CachePut(value = "mysql:findById:role", keyGenerator = "objectId")    public Role create(Role role) {        return roleRepository.save(role);    }    @CachePut(value = "mysql:findById:role", keyGenerator = "objectId")    public Role update(Role role) {        return roleRepository.save(role);    }    @CacheEvict(value = "mysql:findById:role", keyGenerator = "simpleKey")    public void delete(Long id) {        roleRepository.delete(id);    }}

• 使用 Redis Template，配置可以参考第2章的RedisConfig.java

  @Repositorypublic class UserRedis {    @Autowired    private RedisTemplate<String, String> redisTemplate;    public void add(String key, Long time, User user) {        Gson gson = new Gson();        redisTemplate.opsForValue().set(key, gson.toJson(user), time, TimeUnit.MINUTES);    }    public void add(String key, Long time, List<User> users) {        Gson gson = new Gson();        redisTemplate.opsForValue().set(key, gson.toJson(users), time, TimeUnit.MINUTES);    }    public User get(String key) {        Gson gson = new Gson();        User user = null;        String json = redisTemplate.opsForValue().get(key);        if (!StringUtils.isEmpty(json))            user = gson.fromJson(json, User.class);        return user;    }    public List<User> getList(String key) {        Gson gson = new Gson();        List<User> ts = null;        String listJson = redisTemplate.opsForValue().get(key);        if (!StringUtils.isEmpty(listJson))            ts = gson.fromJson(listJson, new TypeToken<List<User>>() {            }.getType());        return ts;    }    public void delete(String key) {        redisTemplate.opsForValue().getOperations().delete(key);    }}

  UserService.java 存储和查询策略，先查询 redis，不存在再查询数据库同时添加到 redis

  /** * 先查询 redis，不存在再查询数据库同时添加到 redis */@Servicepublic class UserService {    @Autowired    private UserRepository userRepository;    @Autowired    private UserRedis userRedis;    private static final String keyHead = "mysql:get:user:";    public User findById(Long id) {        User user = userRedis.get(keyHead + id);        if (user == null) {            user = userRepository.findOne(id);            if (user != null)                userRedis.add(keyHead + id, 30L, user);        }        return user;    }    public User create(User user) {        User newUser = userRepository.save(user);        if (newUser != null)            userRedis.add(keyHead + newUser.getId(), 30L, newUser);        return newUser;    }    public User update(User user) {        if (user != null) {            userRedis.delete(keyHead + user.getId());            userRedis.add(keyHead + user.getId(), 30L, user);        }        return userRepository.save(user);    }    public void delete(Long id) {        userRedis.delete(keyHead + id);        userRepository.delete(id);    }}

---

提高数据库访问性能

Druid

pom.xml 增加 druid 依赖

<dependency>  <groupId>com.alibaba</groupId>  <artifactId>druid</artifactId>  <version>1.0.9</version></dependency>

server:  port: 8080  tomcat:    uri-encoding: utf-8spring:#系统默认的 DataSource#  datasource:#    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8#    username: root#    password: root#阿里的 DruidDataSourc  datasource:    type: com.alibaba.druid.pool.DruidDataSource    driver-class-name: com.mysql.jdbc.Driver    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8    username: root    password: 12345678    # 初始化大小，最小，最大    initialSize: 5    minIdle: 5    maxActive: 20    # 配置获取连接等待超时的时间    maxWait: 60000    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒    timeBetweenEvictionRunsMillis: 60000    # 配置一个连接在池中最小生存的时间，单位是毫秒    minEvictableIdleTimeMillis: 300000    validationQuery: SELECT 1 FROM DUAL    testWhileIdle: true    testOnBorrow: false    testOnReturn: false    # 打开PSCache，并且指定每个连接上PSCache的大小    poolPreparedStatements: true    maxPoolPreparedStatementPerConnectionSize: 20    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙    filters: stat,wall,log4j    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000    # 合并多个DruidDataSource的监控数据    #useGlobalDataSourceStat=true  # jpa  jpa:    database: MYSQL    show-sql: true  #Hibernate ddl auto (validate|create|create-drop|update)    hibernate:      ddl-auto: update      naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy    properties:      hibernate:        dialect: org.hibernate.dialect.MySQL5Dialect  # redis 配置，没有密码的话，可以不写 password 或者 password 值不填写  redis:    host: 192.168.18.130    port: 6379    password:    pool:      max-idle: 8      min-idle: 0      max-active: 8      max-wait: -1

---

事务管理

在方法名称上增加@Transactional，该方法就有事务管理了。

@Servicepublic class PersonService {    @Autowired    private PersonRespository personRespository;    @Transactional    public void insertTwo() {        Person person = new Person();        person.setName("jack");        person.setAge(40);        personRespository.save(person);        Person person1 = new Person();        person1.setName("rose");        person1.setAge(30000);        int a = 1 / 0;        personRespository.save(person1);    }}

---