1 <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
2 <dependency>
3 <groupId>com.alibaba</groupId>
4 <artifactId>druid</artifactId>
5 <version>1.0.29</version>
6 </dependency>
1 server.port=80
2 server.tomcat.uri-encoding=utf-8
3
4 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
5 spring.datasource.driver-class-name=com.mysql.jdbc.Driver
6 spring.datasource.url=jdbc:mysql://localhost:3306/dev?characterEncoding=utf-8
7 spring.datasource.username=root
8 spring.datasource.password=123456
9 #初始化大小
10 spring.datasource.initial-size=5
11 #最小
12 spring.datasource.min-idle=5
13 #最大
14 spring.datasource.max-active=20
15 #配置获取连接等待超时的时间
16 spring.datasource.max-wait=60000
17 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
18 spring.datasource.time-between-eviction-runs-millis=60000
19 #配置一个连接在池中最小生存的时间，单位是毫秒
20 spring.datasource.min-evictable-idle-time-millis=300000
21 spring.datasource.validation-query=SELECT 1 FROM DUAL
22 spring.datasource.test-while-idle=true
23 spring.datasource.test-on-borrow=false
24 spring.datasource.test-on-return=false
25 #打开PSCache,并且指定每个连接上的PSCache的大小
26 spring.datasource.pool-prepared-statements=true
27 spring.datasource.max-pool-prepared-statement-per-connection-size=20
28 #配置监控统计拦截的filters,去掉后监控界面sql无法统计 wall用于防火墙
29 spring.datasource.filters=stat,wall,log4j
30 spring.datasource.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
31
32
33 spring.jpa.database=mysql
34 spring.jpa.show-sql=true
35 spring.jpa.hibernate.ddl-auto=update
36 spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
37 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
38
39 spring.redis.host=localhost
40 spring.redis.port=6379
41 spring.redis.pool.max-idle=8
42 spring.redis.pool.min-idle=0
43 spring.redis.pool.max-active=8
44 spring.redis.pool.max-wait=-1
1 package com.slp.dbexpand;
2
3 import com.alibaba.druid.support.http.StatViewServlet;
4 import com.alibaba.druid.support.http.WebStatFilter;
5 import org.springframework.boot.context.embedded.FilterRegistrationBean;
6 import org.springframework.boot.context.embedded.ServletRegistrationBean;
7 import org.springframework.context.annotation.Bean;
8 import org.springframework.context.annotation.Configuration;
9
10 /**
11 * Created by sangliping on 2017/9/1.
12 */
13 @Configuration
14 public class DruidConfiguration {
15 @Bean
16 public ServletRegistrationBean statViewServlet(){
17 ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
18 //白名单
19 servletRegistrationBean.addInitParameter("allow","127.0.01");
20 //IP黑名单（共同存在时，deny优先于allow）
21 servletRegistrationBean.addInitParameter("deny","192.168.1.100");
22 //登陆查看新的是账户密码
23 servletRegistrationBean.addInitParameter("loginUsername","druid");
24 servletRegistrationBean.addInitParameter("loginPassword","123456");
25 //是否能够重置数据
26 servletRegistrationBean.addInitParameter("resetEnable","false");
27 return servletRegistrationBean;
28 }
29
30 @Bean
31 public FilterRegistrationBean statFilter(){
32 FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
33 //添加过滤规则
34 filterRegistrationBean.addUrlPatterns("/*");
35 //添加不需要忽略的格式信息
36 filterRegistrationBean.addInitParameter("exclusions","*.js.*.gif,*.png,*.css,*.ico,/druid/*");
37 return filterRegistrationBean;
38 }
39 }
1 package com.slp.dbexpand.jpa.repository;
2
3 import com.slp.dbexpand.jpa.parameter.Operator;
4 import com.slp.dbexpand.jpa.parameter.Predicate;
5 import org.springframework.data.domain.Page;
6 import org.springframework.data.domain.Pageable;
7 import org.springframework.data.domain.Sort;
8 import org.springframework.data.jpa.repository.JpaRepository;
9 import org.springframework.data.repository.NoRepositoryBean;
10
11 import java.io.Serializable;
12 import java.util.List;
13 import java.util.Map;
14 /**
15 * Created by sangliping on 2017/9/1.
16 */
17 @NoRepositoryBean
18 public interface ExpandJpaRepository <T, ID extends Serializable> extends JpaRepository<T,ID> {
19 T findOne(String condition, Object... objects);
20
21 List<T> findAll(String condition, Object... objects);
22
23 List<T> findAll(Iterable<Predicate> predicates, Operator operator);
24
25 List<T> findAll(Iterable<Predicate> predicates, Operator operator, Sort sort);
26
27 Page<T> findAll(Iterable<Predicate> predicates, Operator operator, Pageable pageable);
28
29 long count(Iterable<Predicate> predicates, Operator operator);
30
31 List<T> findAll(String condition, Sort sort, Object... objects);
32
33 Page<T> findAll(String condition, Pageable pageable, Object... objects);
34
35 long count(String condition, Object... objects);
36
37 void deleteByIds(Iterable<ID> ids);
38
39 Class<T> getEntityClass();
40
41 List<Map<String,Object>> nativeQuery4Map(String sql);
42
43 Page<Map> nativeQuery4Map(String sql, Pageable pageable);
44
45 Object nativeQuery4Object(String sql);
46 }
package com.slp.dbexpand.jpa.repository;
import com.slp.dbexpand.jpa.Exception.DataException;
import com.slp.dbexpand.jpa.parameter.Operator;
import com.slp.dbexpand.jpa.parameter.Predicate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.query.QueryUtils;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import javax.persistence.*;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.util.*;
import static org.apache.commons.lang3.StringUtils.*;
/**
* Created by sangliping on 2017/9/1.
*/
public class ExpandJpaRepositoryImpl<T,ID extends Serializable> extends SimpleJpaRepository<T,ID> implements ExpandJpaRepository<T,ID> {
private final EntityManager entityManager;
private final JpaEntityInformation<T, ?> entityInformation;
public ExpandJpaRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
super(entityInformation, entityManager);
this.entityManager = entityManager;
this.entityInformation = entityInformation;
}
@Override
public T findOne(String condition, Object... values) {
if(isEmpty(condition)){
throw new NullPointerException("条件不能为空!");
}
T result = null;
try {
result = (T) createQuery(condition, values).getSingleResult();
} catch (NoResultException e) {
e.printStackTrace();
}
return result;
}
@Override
public List<T> findAll(Iterable<Predicate> predicates, Operator operator) {
return new JpqlQueryHolder(predicates,operator).createQuery().getResultList();
}
@Override
public List<T> findAll(Iterable<Predicate> predicates, Operator operator, Sort sort) {
return new JpqlQueryHolder(predicates,operator,sort).createQuery().getResultList();
}
@Override
public Page<T> findAll(Iterable<Predicate> predicates, Operator operator, Pageable pageable) {
if(pageable==null){
return new PageImpl<T>((List<T>) findAll(predicates,operator));
}
Long total = count(predicates,operator);
Query query = new JpqlQueryHolder(predicates,operator,pageable.getSort()).createQuery();
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<T> content = total > pageable.getOffset() ? query.getResultList() : Collections.<T> emptyList();
return new PageImpl<T>(content, pageable, total);
}
@Override
public List<T> findAll(String condition, Object... objects) {
return createQuery(condition, objects).getResultList();
}
@Override
public List<T> findAll(String condition, Sort sort, Object... objects) {
return createQuery(condition, sort, objects).getResultList();
}
@Override
public Page<T> findAll(String condition, Pageable pageable, Object... objects) {
if(pageable==null){
return new PageImpl<T>((List<T>) findAll(condition,objects));
}
Long total = count(condition,objects);
Query query = createQuery(condition, pageable.getSort(), objects);
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<T> content = total > pageable.getOffset() ? query.getResultList() : Collections.<T> emptyList();
return new PageImpl<T>(content, pageable, total);
}
@Override
public Page<T> findAll(Pageable pageable) {
return this.findAll("", pageable);
}
@Override
public List<T> findAll(Iterable<ID> ids) {
return this.findAll("x."+getIdName()+" in ?1",ids);
}
@Override
public long count(String condition, Object... objects) {
return new JpqlQueryHolder(condition,objects).createCountQuery().getSingleResult();
}
@Override
public long count(Iterable<Predicate> predicates, Operator operator) {
return new JpqlQueryHolder(predicates,operator).createCountQuery().getSingleResult();
}
@Override
public void deleteByIds(Iterable<ID> ids) {
List<T> tlist = super.findAll(ids);
super.deleteInBatch(tlist);
}
@Override
public Class<T> getEntityClass() {
return entityInformation.getJavaType();
}
public String getIdName(){
Class<?> entityClass = getEntityClass();
do{
Field[] fields = entityClass.getDeclaredFields();
for(Field field:fields){
if(field.getAnnotation(Id.class)!=null){
return field.getName();
}
}
entityClass = entityClass.getSuperclass();
}while (entityClass != Object.class);
throw new DataException("未设置主键",DataException.noID);
}
@Override
public List<Map<String, Object>> nativeQuery4Map(String sql) {
Query nativeQuery = entityManager.createNativeQuery(sql);
nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return nativeQuery.getResultList();
}
@Override
public Page<Map> nativeQuery4Map(String sql, Pageable pageable) {
Query nativeQuery = entityManager.createNativeQuery(sql);
nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
nativeQuery.setFirstResult(pageable.getOffset());
nativeQuery.setMaxResults(pageable.getPageSize());
Query countNativeQuery = entityManager.createNativeQuery("select count(*) from ("+sql+") a");
long total = Long.valueOf(String.valueOf(countNativeQuery.getSingleResult()));
return new PageImpl<Map>(nativeQuery.getResultList(),pageable,total);
}
@Override
public Object nativeQuery4Object(String sql) {
Query nativeQuery = entityManager.createNativeQuery(sql);
List results=nativeQuery.getResultList();
if(results==null || results.size()==0){
return null;
}
try{
if(results.size()>1){
throw new RuntimeException("结果应当只有一个，但是发现了"+results.size()+"个。");
}
return results.get(0);
}catch (NoResultException e){
return null ;
}
}
private TypedQuery createCountQuery(String condition, Object[] objects){
JpqlQueryHolder queryHolder = new JpqlQueryHolder(condition,objects);
return queryHolder.createCountQuery();
}
/**
* 声明entityClass的查询
*/
private Query createQuery(String condition, Sort sort, Object[] objects) {
JpqlQueryHolder queryHolder = new JpqlQueryHolder(condition,sort,objects);
return queryHolder.createQuery();
}
/**
* 声明entityClass的查询
*/
private Query createQuery(String condition, Object[] objects) {
return createQuery(condition, null, objects);
}
private class JpqlQueryHolder {
//别名
private final String ALIAS = "x";
//QUERY ALL
private final String FIND_ALL_QUERY_STRING = "from %s "+ALIAS;
//传入的condition 排除列表
private final String[] IGNORE_CONSTAINS_CHARSEQUENCE = {"where","WHERE","from","FROM"};
private String condition = null;
private Sort sort;
private Object[] objects;
private Iterable<Predicate> predicates;
private Operator operator = Operator.AND;
private JpqlQueryHolder(Iterable<Predicate> predicates, Operator operator, Sort sort) {
this.predicates = predicates;
this.operator = operator;
this.sort = sort;
}
private JpqlQueryHolder(Iterable<Predicate> predicates , Operator operator ) {
this.operator = operator;
this.predicates = predicates;
}
private JpqlQueryHolder(String condition, Sort sort, Object[] objects) {
this(condition,objects);
this.sort = sort;
}
private JpqlQueryHolder(String condition, Object[] objects) {
if(startsWithAny(condition,IGNORE_CONSTAINS_CHARSEQUENCE)){
throw new DataException("查询条件中只能包含WHERE条件表达式!",DataException.noPermission);
}
this.condition = trimToNull(condition);
this.objects = objects;
}
private Query createQuery(){
StringBuilder sb = new StringBuilder();
// select x from table
sb.append(QueryUtils.getQueryString(FIND_ALL_QUERY_STRING, entityInformation.getEntityName()))
//where
.append(applyCondition());
Query query = entityManager.createQuery(QueryUtils.applySorting(sb.toString(), sort, ALIAS));
applyQueryParameter(query);
return query;
}
private TypedQuery<Long> createCountQuery(){
String ql = String.format(QueryUtils.COUNT_QUERY_STRING, ALIAS, "%s");
ql = QueryUtils.getQueryString(ql, entityInformation.getEntityName());
ql += applyCondition();
TypedQuery<Long> query = entityManager.createQuery(ql,Long.class);
applyQueryParameter(query);
return query;
}
private List<String> map2Conditions(){
if(predicates==null||!predicates.iterator().hasNext()){
return new ArrayList<String>();
}
List<String> conditions = new ArrayList<String>();
Iterator<Predicate> iterator = predicates.iterator();
int index = 0 ;
while (iterator.hasNext()){
Predicate predicate = iterator.next();
if(predicate.getKey()==null){
continue;
}
conditions.add(predicate.toCondition(String.valueOf(index)));
index++ ;
}
return conditions;
}
private String applyCondition(){
List<String> conditions = map2Conditions();
if(condition!=null) {
conditions.add(condition);
}
condition = join(conditions, " " + operator.name() + " ");
return isEmpty(condition)?"":" where "+condition;
}
private void applyQueryParameter(Query query){
if(objects!=null){
int i = 0;
for(Object value:objects){
i++;
query.setParameter(i,value);
}
}
if(predicates!=null&&predicates.iterator().hasNext()){
int index = 0 ;
Iterator<Predicate> iterator = predicates.iterator();
while (iterator.hasNext()){
Predicate predicate = iterator.next();
predicate.setParameter(query,String.valueOf(index));
index++ ;
}
}
}
}
}
package com.slp.dbexpand.jpa.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactory;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean;
import org.springframework.data.repository.core.RepositoryMetadata;
import org.springframework.data.repository.core.support.RepositoryFactorySupport;
import javax.persistence.EntityManager;
import java.io.Serializable;
/**
* Created by sangliping on 2017/9/1.
*/
public class ExpandJpaRepositoryBean<R extends JpaRepository<T, ID>, T, ID extends Serializable>extends JpaRepositoryFactoryBean<R, T, ID> {
protected RepositoryFactorySupport createRepositoryFactory(
EntityManager entityManager) {
return new ExpandJpaRepositoryFactory<T, ID>(entityManager);
}
private static class ExpandJpaRepositoryFactory<T, ID extends Serializable>
extends JpaRepositoryFactory {
private final EntityManager entityManager;
public ExpandJpaRepositoryFactory(EntityManager entityManager) {
super(entityManager);
this.entityManager = entityManager;
}
protected Object getTargetRepository(RepositoryMetadata metadata) {
JpaEntityInformation<T, Serializable> entityInformation = (JpaEntityInformation<T, Serializable>) getEntityInformation(metadata.getDomainType());
return new ExpandJpaRepositoryImpl<T, ID>(entityInformation, entityManager);
}
protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
return ExpandJpaRepositoryImpl.class;
}
}
}
1 package com.slp.dbexpand;
2
3 import com.slp.dbexpand.jpa.repository.ExpandJpaRepositoryBean;
4 import org.springframework.boot.orm.jpa.EntityScan;
5 import org.springframework.context.annotation.Bean;
6 import org.springframework.context.annotation.Configuration;
7 import org.springframework.core.Ordered;
8 import org.springframework.core.annotation.Order;
9 import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
10 import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
11 import org.springframework.transaction.annotation.EnableTransactionManagement;
12
13 /**
14 * Created by sangliping on 2017/9/1.
15 */
16 @Order(Ordered.HIGHEST_PRECEDENCE)
17 @Configuration
18 @EnableTransactionManagement(proxyTargetClass = false)
19 @EnableJpaRepositories(basePackages = "com.slp.repository",repositoryFactoryBeanClass = ExpandJpaRepositoryBean.class)
20 @EntityScan(basePackages = "com.slp.entity")
21 public class JpaConfiguration {
22 @Bean
23 PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor(){
24 return new PersistenceExceptionTranslationPostProcessor();
25 }
26 }
1 package com.slp.dbexpand.jpa;
2 import com.fasterxml.jackson.annotation.JsonAutoDetect;
3 import com.fasterxml.jackson.annotation.PropertyAccessor;
4 import com.fasterxml.jackson.databind.ObjectMapper;
5 import org.springframework.cache.CacheManager;
6 import org.springframework.cache.annotation.CachingConfigurerSupport;
7 import org.springframework.cache.annotation.EnableCaching;
8 import org.springframework.cache.interceptor.KeyGenerator;
9 import org.springframework.context.annotation.Bean;
10 import org.springframework.context.annotation.Configuration;
11 import org.springframework.data.redis.cache.RedisCacheManager;
12 import org.springframework.data.redis.connection.RedisConnectionFactory;
13 import org.springframework.data.redis.core.RedisTemplate;
14 import org.springframework.data.redis.core.StringRedisTemplate;
15 import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
16
17 import java.lang.reflect.InvocationTargetException;
18 import java.lang.reflect.Method;
19 /**
20 * Created by sangliping on 2017/9/1.
21 */
22 @Configuration
23 @EnableCaching
24 public class RedisConfig extends CachingConfigurerSupport{
25 @Bean
26 public KeyGenerator simpleKey(){
27 return new KeyGenerator() {
28 @Override
29 public Object generate(Object target, Method method, Object... params) {
30 StringBuilder sb = new StringBuilder();
31 sb.append(target.getClass().getName()+":");
32 for (Object obj : params) {
33 sb.append(obj.toString());
34 }
35 return sb.toString();
36 }
37 };
38 }
39
40 @Bean
41 public KeyGenerator objectId(){
42 return new KeyGenerator() {
43 @Override
44 public Object generate(Object target, Method method, Object... params){
45 StringBuilder sb = new StringBuilder();
46 sb.append(target.getClass().getName()+":");
47 try {
48 sb.append(params[0].getClass().getMethod("getId", null).invoke(params[0], null).toString());
49 }catch (NoSuchMethodException no){
50 no.printStackTrace();
51 }catch(IllegalAccessException il){
52 il.printStackTrace();
53 }catch(InvocationTargetException iv){
54 iv.printStackTrace();
55 }
56 return sb.toString();
57 }
58 };
59 }
60
61 @Bean
62 public CacheManager cacheManager(@SuppressWarnings("rawtypes") RedisTemplate redisTemplate) {
63 RedisCacheManager manager = new RedisCacheManager(redisTemplate);
64 manager.setDefaultExpiration(43200);//12小时
65 return manager;
66 }
67
68 @Bean
69 public RedisTemplate<String, String> redisTemplate(
70 RedisConnectionFactory factory) {
71 StringRedisTemplate template = new StringRedisTemplate(factory);
72 Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer(Object.class);
73 ObjectMapper om = new ObjectMapper();
74 om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
75 om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
76 jackson2JsonRedisSerializer.setObjectMapper(om);
77 template.setValueSerializer(jackson2JsonRedisSerializer);
78 template.afterPropertiesSet();
79 return template;
80 }
81
82 }
1 package com.slp.redis;
2 import com.google.gson.Gson;
3 import com.google.gson.reflect.TypeToken;
4 import com.slp.entity.User;
5 import org.springframework.beans.factory.annotation.Autowired;
6 import org.springframework.data.redis.core.RedisTemplate;
7 import org.springframework.stereotype.Repository;
8 import org.springframework.util.StringUtils;
9
10 import java.util.List;
11 import java.util.concurrent.TimeUnit;
12 /**
13 * Created by sangliping on 2017/9/1.
14 */
15 @Repository
16 public class UserRedis {
17 @Autowired
18 private RedisTemplate<String, String> redisTemplate;
19
20
21 public void add(String key, Long time, User user) {
22 Gson gson = new Gson();
23 redisTemplate.opsForValue().set(key, gson.toJson(user), time, TimeUnit.MINUTES);
24 }
25
26 public void add(String key, Long time, List<User> users) {
27 Gson gson = new Gson();
28 redisTemplate.opsForValue().set(key, gson.toJson(users), time, TimeUnit.MINUTES);
29 }
30
31
32 public User get(String key) {
33 Gson gson = new Gson();
34 User user = null;
35 String json = redisTemplate.opsForValue().get(key);
36 if(!StringUtils.isEmpty(json))
37 user = gson.fromJson(json, User.class);
38 return user;
39 }
40
41 public List<User> getList(String key) {
42 Gson gson = new Gson();
43 List<User> ts = null;
44 String listJson = redisTemplate.opsForValue().get(key);
45 if(!StringUtils.isEmpty(listJson))
46 ts = gson.fromJson(listJson, new TypeToken<List<User>>(){}.getType());
47 return ts;
48 }
49
50 public void delete(String key){
51 redisTemplate.opsForValue().getOperations().delete(key);
52 }
53 }
