java 数据库读写分离
最近有个新任务,研究数据库读写分离,网上找了很多,有不成熟的也有成熟的,不过总是有这个那个的问题,这个在汇总一遍。 数据库读写分离的优势显而易见,可以大大减轻服务器压力,写用master数据库,读用slave数据库,在查找资料的过程中发现了几种方式。
列表内容
1).利用MySQL自带的功能实现读写分离
查找资料过程中发现有大神提到可以直接使用MySQL自带的功能来解决读写分离,后来尝试了一下,确实能行。数据源:
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:replication://192.168.20.9:3306,192.168.20.8:3306/gm_dev?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
jdbc.search.url=jdbc:mysql:replication://192.168.20.9:3306,192.168.20.8:3306/gsearch08?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
jdbc.old.url=jdbc:mysql:replication://192.168.20.9:3306,192.168.20.8:3306/gm_old?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
jdbc.username=xxxxxx
jdbc.password=xxxxxxxxxx
jdbc.search.username=xxxxxxxxxxx
jdbc.search.password=xxxxxxxxx
jdbc.old.username=xxxxxxx
jdbc.old.password=xxxxxxxx
site.conf.path=xxxxxxx
如果一个service方法里,既有读的,又有写的,MYSQL还是可以读的用slave,写的用master数据库
优点:方便,简单.
缺点:不够灵活。
我这儿还有个问题,如果将切面定在service层,读写可以分离,不过放在dao层,读写不能分离,不知道是什么原因。
2)spring AOP 解决读写分离
具体原理可以看下http://www.cnblogs.com/surge/p/3582248.html
数据源
jdbc.driverClassName=com.mysql.jdbc.Driver
master.jdbc.url=jdbc:mysql://192.168.20.9:3306/gm_dev?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
master.jdbc.search.url=jdbc:mysql://192.168.20.9:3306/gsearch08?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
master.jdbc.old.url=jdbc:mysql://192.168.20.9:3306/gm_old?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
slave.jdbc.url=jdbc:mysql://192.168.20.8:3306/gm_dev?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
slave.jdbc.search.url=jdbc:mysql://192.168.20.8:3306/gsearch08?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
slave.jdbc.old.url=jdbc:mysql://192.168.20.8:3306/gm_old?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
java代码:
package com.gotobus.common;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSource {
String value();
}
-----------------------------------------------------------------------------------------------------------
package com.gotobus.common;
import org.aspectj.lang.JoinPoint;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
public class DataSourceAspect {
public static final ThreadLocal<String> holder = new ThreadLocal<String>();
public void before(JoinPoint point) {
Object target = point.getTarget();
String method = point.getSignature().getName();
Class<?>[] parameterTypes = ((MethodSignature) point.getSignature())
.getMethod().getParameterTypes();
try {
Method m = target.getClass().getMethod(method, parameterTypes);
if (m != null && m.isAnnotationPresent(DataSource.class)) {
DataSource data = m
.getAnnotation(DataSource.class);
putDataSource(data.value());
System.out.println(data.value());
}
} catch (Exception e) {
// TODO: handle exception
}
}
public static void putDataSource(String name){
holder.set(name);
}
public static String getDataSource(){
return holder.get();
}
}
1. 列表内容
-------------------------------------------------------------------------------------
package com.gotobus.common;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource{
@Override
protected Object determineCurrentLookupKey() {
Object dataSource=DataSourceAspect.getDataSource();
return dataSource;
}
}