java如何在日志中打印sql 日志打印sql语句

转载
cnolnic 2023-09-06 10:59:24
文章标签 java如何在日志中打印sql java 数据库 runtime sql 文章分类 Java 后端开发
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);
}
---------------------------------------------------------------------------------------------------------------------
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。