springboot-數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)

[toc]

MySQL

  • 準(zhǔn)備工作，加入數(shù)據(jù)庫(kù)的配置和依賴

    為了使用 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

    @Entity
public 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;
    }
}

    創(chuàng)建接口繼承JpaRepository，泛型使用的是

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

  • 單表簡(jiǎn)單查詢

    HelloController.java

    @RestController
public 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();
    }
}

  • 單表根據(jù)年齡查詢

    PersonRespository.java

    增加一個(gè)根據(jù)年齡查詢的抽象方法

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

    HelloController.java

    @RestController
public 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 初始化
 */
@Configuration
public 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 配置，沒(méi)有密碼的話，可以不寫(xiě) password 或者 password 值不填寫(xiě)
  redis:
    host: 192.168.18.130
    port: 6379
    pool:
      max-idle: 8
      min-idle: 0
      max-active: 8
      max-wait: -1

UserRedis.java 用戶模塊的 redis 增刪改查

@Repository
public 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 接口類測(cè)試

@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("開(kāi)發(fā)部");

        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 緩存優(yōu)化

Spring Cache 注解

結(jié)構(gòu)簡(jiǎn)單的對(duì)象，即沒(méi)有包含其他對(duì)象的實(shí)體，可以用 spring cache 的方式使用 redis 緩存，前提是打開(kāi) spring cache。

  • Spring Cache 配置

    /**
 * 結(jié)構(gòu)簡(jiǎn)單的對(duì)象，既沒(méi)有包含其他對(duì)象的實(shí)體，可以用 spring cache 的方式使用 redis 緩存
 * 前提是打開(kāi) spring cache
 */
@Configuration
@EnableCaching
public 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小時(shí)
        return manager;
    }

    /**
     * simpleKey 根據(jù) 類名+參數(shù) id 作為唯一標(biāo)識(shí)
     * @Cacheable 存取緩存
     * @CachePut 更新緩存
     * @CacheEvict 刪除緩存
     * 注解中的 value 參數(shù)是一個(gè) key 的前綴，
     * 并由 keyGenerator 按照一定的規(guī)則生成一個(gè)唯一的標(biāo)識(shí)
     */
    @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 根據(jù) 類名+參數(shù) id 作為唯一標(biāo)識(shí)
     * @Cacheable 存取緩存
     * @CachePut 更新緩存
     * @CacheEvict 刪除緩存
     * 注解中的 value 參數(shù)是一個(gè) key 的前綴，
     * 并由 keyGenerator 按照一定的規(guī)則生成一個(gè)唯一的標(biāo)識(shí)
     */
    @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 注解來(lái)用 redis 操作。

    @Service
public class RoleService {
    @Autowired
    private RoleRepository roleRepository;

    /**
     * @Cacheable(value = "mysql:findById:role", keyGenerator = "simpleKey")
     * value = "mysql:findById:role" 是一個(gè)key 的前綴，并由 keyGenerator 按照一定的規(guī)則生成一個(gè)唯一的標(biāo)識(shí)
     * @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

    @Repository
public 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 存儲(chǔ)和查詢策略，先查詢 redis，不存在再查詢數(shù)據(jù)庫(kù)同時(shí)添加到 redis

    /**
 * 先查詢 redis，不存在再查詢數(shù)據(jù)庫(kù)同時(shí)添加到 redis
 */
@Service
public 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);
    }

}


提高數(shù)據(jù)庫(kù)訪問(wèn)性能

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-8
spring:
#系統(tǒng)默認(rèn)的 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
    # 配置獲取連接等待超時(shí)的時(shí)間
    maxWait: 60000
    # 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打開(kāi)PSCache，并且指定每個(gè)連接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置監(jiān)控統(tǒng)計(jì)攔截的filters，去掉后監(jiān)控界面sql無(wú)法統(tǒng)計(jì)，'wall'用于防火墻
    filters: stat,wall,log4j
    # 通過(guò)connectProperties屬性來(lái)打開(kāi)mergeSql功能；慢SQL記錄
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多個(gè)DruidDataSource的監(jiān)控?cái)?shù)據(jù)
    #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 配置，沒(méi)有密碼的話，可以不寫(xiě) password 或者 password 值不填寫(xiě)
  redis:
    host: 192.168.18.130
    port: 6379
    password:
    pool:
      max-idle: 8
      min-idle: 0
      max-active: 8
      max-wait: -1


事務(wù)管理

在方法名稱上增加@Transactional，該方法就有事務(wù)管理了。

@Service
public 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);

    }
}


最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • Spring Cloud為開(kāi)發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見(jiàn)模式的工具（例如配置管理，服務(wù)發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,649評(píng)論 19贊 139
  • 1. Java基礎(chǔ)部分 基礎(chǔ)部分的順序：基本語(yǔ)法，類相關(guān)的語(yǔ)法，內(nèi)部類的語(yǔ)法，繼承相關(guān)的語(yǔ)法，異常的語(yǔ)法，線程的語(yǔ)...
    子非魚(yú)_t_閱讀 34,741評(píng)論 18贊 399
  • 一. Java基礎(chǔ)部分.................................................
    wy_sure閱讀 4,017評(píng)論 0贊 11
  • 第6章 Spring Boot數(shù)據(jù)庫(kù)層集成 6.1 Spring Boot集成mybatis 在SpringBoo...
    光劍書(shū)架上的書(shū)閱讀 1,097評(píng)論 0贊 9
  • （一） 記得高中階段，同學(xué)WJL數(shù)學(xué)成績(jī)很好，幾乎次次滿分，我則逢數(shù)學(xué)考試不佳。 向他取經(jīng)時(shí)，他很誠(chéng)懇地說(shuō): 其實(shí)...
    偷影子的人Renaissanc閱讀 297評(píng)論 0贊 4

友情鏈接更多精彩內(nèi)容