日志打印sql语句

druid-1.0.13.jar

-------------------------------

<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">

<!--慢SQL统计，如果SQL执行时间超过一定时间则记录为慢SQL -->

<property name="slowSqlMillis" value="3000" />

<!--慢SQL统计日志输出 -->

<property name="logSlowSql" value="true" />

<!--合并SQL统计 例如select * from table t where t.id =1，会被变为select * from table t where t.id =？来统计 -->

<property name="mergeSql" value="true" />

</bean>

 

<bean id="log-filter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">

<property name="statementLogEnabled" value="true"></property>

<property name="statementLoggerName" value="winbons.statementsql"></property>

</bean>

 

<bean id="sql-filter" class="saas.framework.mutitenant.SqlParseFilter"></bean>

 

<bean id="lobHandler" class="com.alibaba.druid.support.spring.DruidLobHandler"></bean>

 

<bean id="wall-filter-config" class="com.alibaba.druid.wall.WallConfig" init-method="init">

<!-- 指定配置装载的目录 -->

      <property name="dir" value="META-INF/druid/wall/mysql" />

      <property name="commentAllow" value="true" />

      <property name="multiStatementAllow" value="true" />

      <property name="noneBaseStatementAllow" value="true" />

      <property name="selectWhereAlwayTrueCheck" value="false" />

      <property name="conditionAndAlwayTrueAllow" value="true" />

</bean>

<bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">

      <property name="dbType" value="mysql" />

      <property name="config" ref="wall-filter-config" />

      <property name="logViolation" value="true" />

<property name="throwException" value="true" />

  </bean>

 

<bean id="saasDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">

<!-- 基本属性 url、user、password -->

<property name="url" value="${jdbc.url}" />

<property name="username" value="${jdbc.username}" />

<property name="password" value="${jdbc.password}" />

<!-- 配置初始化大小、最小、最大 -->

<property name="initialSize" value="10" />

<property name="minIdle" value="10" />

<property name="maxActive" value="50" />

<!-- maxWait获取连接等待超时的时间 -->

        <property name="maxWait" value="60000" />

<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->

<property name="timeBetweenEvictionRunsMillis" value="60000" />

<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->

<property name="minEvictableIdleTimeMillis" value="300000" />

<property name="validationQuery" value="SELECT 'x'" />

<property name="testWhileIdle" value="true" />

<property name="testOnBorrow" value="false" />

<property name="testOnReturn" value="false" />

<property name="poolPreparedStatements" value="false" />

<property name="proxyFilters">

<list>

<ref bean="stat-filter" />

<ref bean="log-filter" />

<ref bean="sql-filter" />

<ref bean="wall-filter" />

</list>

</property>

</bean>

--------------------------------------------------------------------------------------------------------------------------------1

package saas.framework.mutitenant;

 

import java.sql.ParameterMetaData;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

import org.apache.commons.lang.StringUtils;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.InitializingBean;

import org.springframework.context.ApplicationEventPublisher;

import org.springframework.context.ApplicationEventPublisherAware;

 

import saas.framework.dao.PageDataContext;

import saas.framework.dao.PageVO;

import saas.framework.event.SqlExecuteEvent;

import saas.framework.mutitenant.db.TenantDatabaseHelper;

import saas.framework.mutitenant.sql.TableCache;

import saas.framework.utils.Env;

import saas.framework.utils.SecurityUtils;

 

import com.alibaba.druid.filter.FilterAdapter;

import com.alibaba.druid.filter.FilterChain;

import com.alibaba.druid.proxy.jdbc.ConnectionProxy;

import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;

import com.alibaba.druid.proxy.jdbc.ResultSetProxy;

import com.alibaba.druid.proxy.jdbc.StatementProxy;

import com.alibaba.druid.sql.PagerUtils;

import com.alibaba.druid.util.JdbcConstants;

 

public class SqlParseFilter extends FilterAdapter implements ApplicationEventPublisherAware, InitializingBean {

 

private final static Logger logger = LoggerFactory.getLogger("SQLParser");

 

private final static String MYCAT_HINT = "/*!mycat:schema=%s*/";

 

private ApplicationEventPublisher eventPublisher;

 

private Env env;

 

private volatile boolean needParse = false;

 

// 是否需要分库代理

private boolean needProxy = false;

 

private static ThreadLocal<String> countSQLThreadLocal = new ThreadLocal<String>();

 

public void setEnv(Env env) {

this.env = env;

}

 

@Override

public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection, String sql)

throws SQLException {

String checkSQL = checkSQL(sql);

PageVO pageVO = PageDataContext.getCurrentPageVO();

String countSQL = null;

if (pageVO.isNeedPage()) {

if(checkSQL.startsWith(TenantConstant.MYCAT_HINT)){

int endPos = checkSQL.indexOf("*/");

// 用!mycat:内部的语句来做路由分析

String hint = checkSQL.substring(0, endPos + "*/".length()).trim();

String realSQL = checkSQL.substring(endPos + "*/".length()).trim();

 

if(needProxy){

countSQL = hint + PagerUtils.count(realSQL, JdbcConstants.MYSQL);

checkSQL = hint + PagerUtils.limit(realSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

}else{

countSQL = PagerUtils.count(checkSQL, JdbcConstants.MYSQL);

checkSQL = PagerUtils.limit(checkSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

}

}else{

countSQL = PagerUtils.count(checkSQL, JdbcConstants.MYSQL);

checkSQL = PagerUtils.limit(checkSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

}

pageVO.setNeedPage(false);

}

if (countSQL != null) {

countSQLThreadLocal.set(countSQL);

}

 

logger.debug("The COUNT SQL-1: " + countSQL);

logger.debug("The CHECK SQL-1: " + checkSQL);

 

return super.connection_prepareStatement(chain, connection, checkSQL);

}

 

@Override

public ResultSetProxy preparedStatement_executeQuery(FilterChain chain, PreparedStatementProxy statement)

throws SQLException {

ParameterMetaData parameterMetaData = statement.getParameterMetaData();

int size = parameterMetaData.getParameterCount();

String countSQL = countSQLThreadLocal.get();

if (countSQL != null && size > 0) {

logger.info("The Batch Page SQL: {}", statement.getBatchSql());

PreparedStatement preparedStatement = statement.getConnection().prepareStatement(countSQL);

for (int i = 0; i < size; i++) {

preparedStatement.setObject(i + 1, statement.getParameter(i).getValue());

}

ResultSet resultSet = preparedStatement.executeQuery();

if (resultSet.next()){

PageDataContext.getCurrentPageVO().setTotalCount(resultSet.getLong(1));

}

countSQLThreadLocal.remove();

}

return super.preparedStatement_executeQuery(chain, statement);

}

 

@Override

public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,

String sql, int autoGeneratedKeys) throws SQLException {

return super.connection_prepareStatement(chain, connection, checkSQL(sql), autoGeneratedKeys);

}

 

@Override

public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,

String sql, int resultSetType, int resultSetConcurrency) throws SQLException {

return super.connection_prepareStatement(chain, connection, checkSQL(sql), resultSetType, resultSetConcurrency);

}

 

@Override

public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,

String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {

// TODO Auto-generated method stub

return super.connection_prepareStatement(chain, connection, checkSQL(sql), resultSetType, resultSetConcurrency,

resultSetHoldability);

}

 

@Override

public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,

String sql, int[] columnIndexes) throws SQLException {

return super.connection_prepareStatement(chain, connection, checkSQL(sql), columnIndexes);

}

 

@Override

public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,

String sql, String[] columnNames) throws SQLException {

return super.connection_prepareStatement(chain, connection, checkSQL(sql), columnNames);

}

 

@Override

public boolean statement_execute(FilterChain chain, StatementProxy statement, String sql) throws SQLException {

return super.statement_execute(chain, statement, checkSQL(sql));

}

 

@Override

public boolean statement_execute(FilterChain chain, StatementProxy statement, String sql, int autoGeneratedKeys)

throws SQLException {

return super.statement_execute(chain, statement, checkSQL(sql), autoGeneratedKeys);

}

 

@Override

public boolean statement_execute(FilterChain chain, StatementProxy statement, String sql, int[] columnIndexes)

throws SQLException {

return super.statement_execute(chain, statement, checkSQL(sql), columnIndexes);

}

 

@Override

public boolean statement_execute(FilterChain chain, StatementProxy statement, String sql, String[] columnNames)

throws SQLException {

return super.statement_execute(chain, statement, checkSQL(sql), columnNames);

}

 

@Override

public ResultSetProxy statement_executeQuery(FilterChain chain, StatementProxy statement, String sql)

throws SQLException {

String checkSQL = checkSQL(sql);

PageVO pageVO = PageDataContext.getCurrentPageVO();

if (pageVO.isNeedPage()) {

String countSQL = null;

if(checkSQL.startsWith(TenantConstant.MYCAT_HINT)){

int endPos = checkSQL.indexOf("*/");

// 用!mycat:内部的语句来做路由分析

String hint = checkSQL.substring(0, endPos + "*/".length()).trim();

String realSQL = checkSQL.substring(endPos + "*/".length()).trim();

 

if(needProxy){

countSQL = hint + PagerUtils.count(realSQL, JdbcConstants.MYSQL);

checkSQL = hint + PagerUtils.limit(realSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

}else{

countSQL = PagerUtils.count(checkSQL, JdbcConstants.MYSQL);

checkSQL = PagerUtils.limit(checkSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

}

}else{

countSQL = PagerUtils.count(checkSQL, JdbcConstants.MYSQL);

checkSQL = PagerUtils.limit(checkSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

}

 

logger.debug("The COUNT SQL-2: {}", countSQL);

ResultSet resultSet = statement.getRawObject().executeQuery(countSQL);

resultSet.next();

pageVO.setTotalCount(resultSet.getLong(1));

resultSet.close();

pageVO.setNeedPage(false);// 只执行一次

//checkSQL = PagerUtils.limit(checkSQL, JdbcConstants.MYSQL, pageVO.getFirstResult(), pageVO.getLimit());

logger.debug("The CHECK SQL-2: {}", checkSQL);

}

return super.statement_executeQuery(chain, statement, checkSQL);

}

 

@Override

public int statement_executeUpdate(FilterChain chain, StatementProxy statement, String sql) throws SQLException {

return super.statement_executeUpdate(chain, statement, checkSQL(sql));

}

 

@Override

public int statement_executeUpdate(FilterChain chain, StatementProxy statement, String sql, int autoGeneratedKeys)

throws SQLException {

return super.statement_executeUpdate(chain, statement, checkSQL(sql), autoGeneratedKeys);

}

 

@Override

public int statement_executeUpdate(FilterChain chain, StatementProxy statement, String sql, int[] columnIndexes)

throws SQLException {

return super.statement_executeUpdate(chain, statement, checkSQL(sql), columnIndexes);

}

 

@Override

public int statement_executeUpdate(FilterChain chain, StatementProxy statement, String sql, String[] columnNames)

throws SQLException {

return super.statement_executeUpdate(chain, statement, checkSQL(sql), columnNames);

}

 

private String checkSQL(String originalSQL) {

if (!needParse) {

return originalSQL;

}

 

logger.debug("The Original SQL: " + originalSQL);

 

//boolean hasTenantID = true;

//FIXME 其它系统没有登录，此时租户ID会为空，例如电话服务，平台服务等

if (SecurityUtils.getTenantId() == null || SecurityUtils.getTenantId() == -1) {

//hasTenantID = false;

}

try {

if (originalSQL.startsWith(TenantConstant.NON_PARSER)) {

String sql = originalSQL.replaceAll("/\\*noformat\\*/", "");

sql = sql.trim();

if(needProxy){

//FIXME 强制指定走哪个分库了

if(!sql.startsWith(TenantConstant.MYCAT_HINT)){

String schema = TenantDatabaseHelper.getSchemaByTenantId(SecurityUtils.getTenantId());

sql = String.format(MYCAT_HINT, schema) + sql;

}

}else{

if(sql.startsWith(TenantConstant.MYCAT_HINT)){

int endPos = sql.indexOf("*/");

//FIXME 如果不需要分库，但是写了分库注解的，则将注解删除掉，因为灰度CRM是不需要分库的

sql = sql.substring(endPos + "*/".length()).trim();

}

}

 

logger.info("The No-Parsed Final SQL: {}", sql);

 

return sql;

}else if(originalSQL.startsWith(TenantConstant.TENANT_HINT_PREFIX)){//FIXME 支持跨租户获取数据，例如意见反馈，需要从别的租户里获取imAccount

int endPos = originalSQL.indexOf("*/");

String sql = originalSQL.substring(endPos + "*/".length()).trim();

if(needProxy){

String hint = originalSQL.substring(TenantConstant.TENANT_HINT_PREFIX_LEN, endPos).trim();

String tenantId = hint.split("=")[1].trim();

String schema = TenantDatabaseHelper.getSchemaByTenantId(Long.parseLong(tenantId));

sql = String.format(MYCAT_HINT, schema) + sql;

}

 

logger.info("The Tenant-Hint Final SQL: {}", sql);

 

return sql;

}

 

if (TableCache.checkParseSQL(originalSQL)) {

return originalSQL;

}

 

try {

// 其它系统没有登录，此时租户ID会为空，例如电话服务，平台服务等

if (SecurityUtils.getTenantId() == null || SecurityUtils.getTenantId() == -1) {

TableCache.addparseSql(originalSQL);

return originalSQL;

}

} catch (Exception ex) {

logger.warn(" {} desn't have a tenant id", originalSQL);

return originalSQL;

}

 

SqlExecuteEvent sqlExecuteEvent = new SqlExecuteEvent(originalSQL);

eventPublisher.publishEvent(sqlExecuteEvent);

String parsedSQL = sqlExecuteEvent.getParseSql();

 

if (StringUtils.isNotBlank(parsedSQL)) {

if(!needProxy){

if(parsedSQL.startsWith(TenantConstant.MYCAT_HINT)){

int endPos = parsedSQL.indexOf("*/");

//FIXME 如果不需要分库，但是写了分库注解的，则将注解删除掉，万一MyCat出问题了，可以快速切换回来不走中间件

parsedSQL = parsedSQL.substring(endPos + "*/".length()).trim();

}

}

 

logger.info("The Parsed Final SQL: {}", parsedSQL);

return parsedSQL;

}

 

if(needProxy){

//FIXME 强制指定走哪个分库了

if(!originalSQL.startsWith(TenantConstant.MYCAT_HINT)){

String schema = TenantDatabaseHelper.getSchemaByTenantId(SecurityUtils.getTenantId());

originalSQL = String.format(MYCAT_HINT, schema) + originalSQL;

}

}

return originalSQL;

} finally {

TenantConstant.clear();

}

}

 

public void setApplicationEventPublisher(ApplicationEventPublisher applicationEventPublisher) {

this.eventPublisher = applicationEventPublisher;

}

 

@Override

public void afterPropertiesSet() throws Exception {

needParse = Boolean.parseBoolean(env.getConfigProperty(TenantConstant.TENANT_SQL_NEED_PARSE, "true"));

needProxy = Boolean.parseBoolean(env.getConfigProperty(TenantConstant.TENANT_DB_NEED_PROXY, "false"));

 

logger.info("///////////////// ============ 是否开启连接数据库中间件: {} =========== /////////////////", needProxy);

}

}

-----------------------------------------------------------

logback.xml 日志级别

 

<logger name="winbons.statementsql" level="INFO" />

---------------------------------------------------------------------------------------------------------------------------------2

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: TenantTable.java 

 * @Package saas.framework.mutitenant 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 下午2:39:30 

 * @version 1.0 

 */

 

package saas.framework.mutitenant;

 

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

import java.lang.annotation.Target;

 

/**

 * @ClassName: TenantTable

 * @Description: TODO(这里用一句话描述这个类的作用)

 * @author yxx

 * @date 2014-3-17 下午2:39:30

 */

@Target(ElementType.TYPE)

@Retention(RetentionPolicy.RUNTIME)

public @interface TenantTable {

 

public static final String PUBLIC_TABLE = "0";

 

public static final String TENANT_TABLE = "1";

 

public String value() default "1";

}

----------------------------------------------------------------------------

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: TableCache.java 

 * @Package saas.framework.mutitenant.sql 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 下午2:48:44 

 * @version 1.0 

 */

 

package saas.framework.mutitenant.sql;

 

import java.util.HashSet;

import java.util.Set;

import java.util.concurrent.ConcurrentHashMap;

 

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

 

import saas.framework.mutitenant.TenantTable;

 

/**

 * @ClassName: TableCache

 * @author yxx

 * @date 2014-3-17 下午2:48:44

 */

 

public final class TableCache {

 

private static final Logger logger = LoggerFactory.getLogger(TableCache.class);

 

private static Set<String> tenantTable = new HashSet<String>();

 

private static Set<String> commonTable = new HashSet<String>();

 

private static ConcurrentHashMap<String, Boolean> tabelparseCache = new ConcurrentHashMap<String, Boolean>();

 

public static boolean isTenantTable(String tableName) {

return tenantTable.contains(tableName.toLowerCase());

}

 

public static boolean isCommonTable(String tableName) {

return commonTable.contains(tableName.toLowerCase());

}

 

public static void addTable(String type, String tableName) {

if (TenantTable.TENANT_TABLE.equals(type)) {

tenantTable.add(tableName.toLowerCase());

} else {

commonTable.add(tableName.toLowerCase());

}

logger.info("add a tenant table:{}", tableName);

}

 

public static boolean addparseSql(String sql){

return tabelparseCache.putIfAbsent(sql, Boolean.TRUE) == null;

}

 

public static boolean checkParseSQL(String sql){

return tabelparseCache.remove(sql, Boolean.TRUE);

}

}

 -------------------------------------------------------------------------------------------------------------------------------3

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: SqlExecuteEvent.java 

 * @Package saas.framework.event 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 上午10:30:05 

 * @version 1.0 

 */

 

package saas.framework.event;

 

import org.springframework.context.ApplicationEvent;

 

/**

 * @ClassName: SqlExecuteEvent

 * @Description: TODO(这里用一句话描述这个类的作用)

 * @author yxx

 * @date 2014-3-17 上午10:30:05

 */

 

public class SqlExecuteEvent extends ApplicationEvent {

 

public SqlExecuteEvent(Object source) {

super(source);

}

 

private static final long serialVersionUID = -3784670716281203870L;

 

private String parseSql;

 

public String getParseSql() {

return parseSql;

}

 

public void setParseSql(String parseSql) {

this.parseSql = parseSql;

}

 

}

--------------------------------------------------------------------------------------------------------------------------------

package saas.framework.dao;

 

public class PageVO {

 

private long totalCount;

 

private int currentPage = 0;

 

private int limit;

 

private boolean isPageInfo;

 

private boolean needPage = false;

 

/**

* @return the needPage //是否需要分页

*/

public boolean isNeedPage() {

 

return needPage;

}

 

/**

* @param needPage

*            the needPage to set

*/

 

public void setNeedPage(boolean needPage) {

 

this.needPage = needPage;

}

 

public boolean isPageInfo() {

return isPageInfo && currentPage > 0;

}

 

public void setPageInfo(boolean isPageInfo) {

this.isPageInfo = isPageInfo;

}

 

public long getTotalCount() {

return totalCount;

}

 

public void setTotalCount(long totalCount) {

if (currentPage == 0) {

currentPage = 1;

}

this.totalCount = totalCount;

}

 

public int getCurrentPage() {

return currentPage;

}

 

public void setCurrentPage(int currentPage) {

this.currentPage = currentPage;

}

 

public int getLimit() {

return limit;

}

 

public void setLimit(int limit) {

this.isPageInfo = true;

this.limit = limit;

}

 

@Override

public String toString() {

StringBuilder builder = new StringBuilder();

builder.append("PageVO [totalCount=");

builder.append(totalCount);

builder.append(", currentPage=");

builder.append(currentPage);

builder.append(", limit=");

builder.append(limit);

builder.append(", isPageInfo=");

builder.append(isPageInfo);

builder.append("]");

return builder.toString();

}

 

public int getFirstResult() {

if (currentPage == 0) {

return 0;

}

return (currentPage - 1) * limit;

}

 

public Boolean hasMore() {

int pages = (int) ((totalCount - 1) / limit + 1);

return currentPage < pages;

}

}

----------------------------------------------------------------------------------------------------------------------------

package saas.framework.dao;

 

import saas.framework.thread.ContextUtils;

 

public class PageDataContext {

 

private static final String KEY = PageDataContext.class.getName();

 

public static PageVO getCurrentPageVO() {

PageVO pageVO = ContextUtils.get(KEY);

if (pageVO == null) {

pageVO = new PageVO();

ContextUtils.set(KEY, pageVO);

}

return pageVO;

}

 

public static void setCurrentPage(int page) {

getCurrentPageVO().setCurrentPage(page);

}

 

public static void setTotalCount(int totalCount) {

getCurrentPageVO().setTotalCount(totalCount);

}

 

public static void setlimit(int limit) {

getCurrentPageVO().setLimit(limit);

}

 

public static void clear() {

ContextUtils.remove(KEY);

}

}

------------------------------------------------------------------------------------------------------------------------------

package saas.framework.thread;

 

import java.util.HashMap;

import java.util.Map;

 

public class ContextUtils {

 

private static ThreadLocal<Map<String, Object>> context = new ThreadLocal<Map<String, Object>>() {

@Override

protected Map<String, Object> initialValue() {

return new HashMap<String, Object>();

}

};

 

public static void set(String key, Object value) {

context.get().put(key, value);

}

 

@SuppressWarnings("unchecked")

public static <T> T get(String key) {

return (T) context.get().get(key);

}

 

public static void remove(String key) {

context.get().remove(key);

}

 

public static void clear() {

context.remove();

}

}

--------------------------------------------------------------------------------------------------------------------------------

 

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: SqlExecuteEvent.java 

 * @Package saas.framework.event 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 上午10:30:05 

 * @version 1.0 

 */

 

package saas.framework.event;

 

import org.springframework.context.ApplicationEvent;

 

/**

 * @ClassName: SqlExecuteEvent

 * @Description: TODO(这里用一句话描述这个类的作用)

 * @author yxx

 * @date 2014-3-17 上午10:30:05

 */

 

public class SqlExecuteEvent extends ApplicationEvent {

 

public SqlExecuteEvent(Object source) {

super(source);

}

 

private static final long serialVersionUID = -3784670716281203870L;

 

private String parseSql;

 

public String getParseSql() {

return parseSql;

}

 

public void setParseSql(String parseSql) {

this.parseSql = parseSql;

}

 

 

}

--------------------------------------------------------------------------------------------------------------------------

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: AbstractSqlExecteListerner.java 

 * @Package saas.framework.event 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 上午10:33:53 

 * @version 1.0 

 */

 

package saas.framework.event;

 

import org.apache.commons.lang.StringUtils;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.InitializingBean;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.ApplicationEvent;

 

import saas.framework.mutitenant.TenantConstant;

import saas.framework.utils.Env;

 

/**

 * @ClassName: AbstractSqlExecteListerner

 * @Description:sql 监听器

 * @author yxx

 * @date 2014-3-17 上午10:33:53

 */

 

public abstract class AbstractSqlExecteListerner implements SmartApplicationListener, InitializingBean {

 

private static final Logger logger = LoggerFactory.getLogger(AbstractSqlExecteListerner.class);

 

@Autowired

private Env env;

 

protected boolean needParse = false;

 

protected boolean needProxy = false;

 

/**

* 执行顺序

*/

@Override

public int getOrder() {

return 0;

}

 

@Override

public void onApplicationEvent(ApplicationEvent event) {

Boolean isNeedParse = TenantConstant.isNeedParse();

if (isNeedParse == null) {

isNeedParse = needParse;

}

if (isNeedParse) {

SqlExecuteEvent sqlExecuteEvent = (SqlExecuteEvent) event;

String oldSql = (String) sqlExecuteEvent.getSource();

String parseSql = sqlExecuteEvent.getParseSql();

String tempSql = parseSql;

if (StringUtils.isBlank(parseSql)) {

tempSql = oldSql;

}

sqlExecuteEvent.setParseSql(parseSQL(tempSql));

} else {

logger.debug("the entity is not need parse");

}

}

 

protected abstract String parseSQL(String sql);

 

@Override

public boolean supportsEventType(Class<? extends ApplicationEvent> eventType) {

return eventType.isAssignableFrom(SqlExecuteEvent.class);

}

 

@Override

public boolean supportsSourceType(Class<?> sourceType) {

return true;

}

 

@Override

public void afterPropertiesSet() throws Exception {

needParse = env.getConfigProperty("tenant_sql_parse", "false").equals("true");

needProxy = Boolean.parseBoolean(env.getConfigProperty(TenantConstant.TENANT_DB_NEED_PROXY, "false"));

}

}

 

----------------------------------------------------------------------------------------------------------------------------

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: TenantSqlListener.java 

 * @Package saas.framework.sql 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 上午10:43:28 

 * @version 1.0 

 */

 

package saas.framework.mutitenant;

 

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Component;

 

import saas.framework.event.AbstractSqlExecteListerner;

import saas.framework.mutitenant.db.TenantDatabaseHelper;

import saas.framework.mutitenant.sql.TableCache;

import saas.framework.utils.SecurityUtils;

 

/**

 * @ClassName: TenantSqlListener

 * @Description:多租戶数据库转化

 * @author yxx

 * @date 2014-3-17 上午10:43:28

 */

@Component

public class TenantSqlExecuteListener extends AbstractSqlExecteListerner {

 

private final static String MYCAT_HINT = "/*!mycat:schema=%s*/";

 

@Autowired

private ITenantSqlParser sqlParser;

 

@Override

public String parseSQL(String sql) {

Long tenantId = SecurityUtils.getTenantId();

String parseSQL = sqlParser.parse(sql, tenantId);

 

if(needProxy){

if(!parseSQL.startsWith(TenantConstant.MYCAT_HINT)){

String schema = TenantDatabaseHelper.getSchemaByTenantId(tenantId);

parseSQL = String.format(MYCAT_HINT, schema) + parseSQL;

}

}

TableCache.addparseSql(parseSQL);

return parseSQL;

}

 

}

----------------------------------------------------------------------------------------------------------------------

 /** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: TenantSqlParserImpl.java 

 * @Package saas.framework.mutitenant 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 上午11:11:37 

 * @version 1.0 

 */

 

package saas.framework.mutitenant;

 

import org.springframework.stereotype.Component;

 

import saas.framework.mutitenant.sql.SQLParserUtil;

import saas.framework.utils.Env;

import saas.framework.utils.SecurityUtils;

 

/**

 * @ClassName: TenantSqlParserImpl

 * @Description: sql 转化

 * @author yxx

 * @date 2014-3-17 上午11:11:37

 */

@Component

public class TenantSqlParserImpl implements ITenantSqlParser {

 

private static final String TENANT_COLUMNNAME = "tenantColumn";

 

@Override

public String parse(String sql, long CompanyId) {

String columnName = Env.getInstatnce().getConfigProperty(TENANT_COLUMNNAME, "serviceId");

return SQLParserUtil.parseSQL(sql, columnName, SecurityUtils.getTenantId() + "");

}

}

 

/** 

 * Copyright (C) 2013 Winbons Technology Software Co.,Ltd

 * All Rights Reserved.

 * Development of this softwareWinbons Technology Software Co.,Ltd.

 * Without the formal written consent of the Company, 

 * any other individuals, groups may not use, 

 * copy, modify, or distribute this software

 * @Title: ITenantSqlParser.java 

 * @Package saas.framework.mutitenant 

 * @Description: TODO(用一句话描述该文件做什么) 

 * @author yxx

 * @date 2014-3-17 上午11:10:00 

 * @version 1.0 

 */

 

package saas.framework.mutitenant;

 

/**

 * @ClassName: ITenantSqlParser

 * @Description: TODO(这里用一句话描述这个类的作用)

 * @author yxx

 * @date 2014-3-17 上午11:10:00

 */

 

public interface ITenantSqlParser {

 

String parse(String sql, long companyId);

}

---------------------------------------------------------------------------------------------------------------------