數(shù)據(jù)庫
[toc]
MySQL
-
準(zhǔn)備工作，加入數(shù)據(jù)庫的配置和依賴
為了使用
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.MySQL5DialectPerson.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> { } -
單表簡單查詢
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
增加一個根據(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 配置迹辐，沒有密碼的話枪孩，可以不寫 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 增刪改查
@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 接口類測試
@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("開發(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)簡單的對象憔晒，即沒有包含其他對象的實體，可以用 spring cache 的方式使用 redis 緩存蔑舞，前提是打開 spring cache拒担。
-
Spring Cache配置/** * 結(jié)構(gòu)簡單的對象，既沒有包含其他對象的實體攻询，可以用 spring cache 的方式使用 redis 緩存 * 前提是打開 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小時 return manager; } /** * simpleKey 根據(jù) 類名+參數(shù) id 作為唯一標(biāo)識 * @Cacheable 存取緩存 * @CachePut 更新緩存 * @CacheEvict 刪除緩存 * 注解中的 value 參數(shù)是一個 key 的前綴从撼， * 并由 keyGenerator 按照一定的規(guī)則生成一個唯一的標(biāo)識 */ @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)識 * @Cacheable 存取緩存 * @CachePut 更新緩存 * @CacheEvict 刪除緩存 * 注解中的 value 參數(shù)是一個 key 的前綴， * 并由 keyGenerator 按照一定的規(guī)則生成一個唯一的標(biāo)識 */ @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 操作钧栖。@Service public class RoleService { @Autowired private RoleRepository roleRepository; /** * @Cacheable(value = "mysql:findById:role", keyGenerator = "simpleKey") * value = "mysql:findById:role" 是一個key 的前綴低零，并由 keyGenerator 按照一定的規(guī)則生成一個唯一的標(biāo)識 * @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存儲和查詢策略拯杠，先查詢 redis掏婶，不存在再查詢數(shù)據(jù)庫同時添加到 redis/** * 先查詢 redis，不存在再查詢數(shù)據(jù)庫同時添加到 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ù)庫訪問性能
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
# 配置獲取連接等待超時的時間
maxWait: 60000
# 配置間隔多久才進行一次檢測，檢測需要關(guān)閉的空閑連接依溯，單位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一個連接在池中最小生存的時間老厌，單位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打開PSCache，并且指定每個連接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置監(jiān)控統(tǒng)計攔截的filters黎炉，去掉后監(jiān)控界面sql無法統(tǒng)計枝秤，'wall'用于防火墻
filters: stat,wall,log4j
# 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多個DruidDataSource的監(jiān)控數(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 配置慷嗜，沒有密碼的話淀弹，可以不寫 password 或者 password 值不填寫
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);
}
}
