数据库
[toc]
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.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; } }创建接口继承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(); } } -
单表根据年龄查询
PersonRespository.java
增加一个根据年龄查询的抽象方法
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("开发部");
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 @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 根据 类名+参数 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 操作。@Service public 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@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，不存在再查询数据库同时添加到 redis/** * 先查询 redis，不存在再查询数据库同时添加到 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); } }
提高数据库访问性能
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:
#系统默认的 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，该方法就有事务管理了。
@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);
}
}
