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);
}
---------------------------------------------------------------------------------------------------------------------
相关推荐
本文将详细讲解如何在iBatis日志信息中打印SQL语句，以便更好地理解和优化数据库操作。 首先，iBatis提供了一个强大的日志系统，支持多种日志实现，如Log4j、SLF4J、Java Util Logging等。选择哪种日志实现取决于你...
标题 "idea插件mybaits log 打印sql语句" 涉及的是一个针对IntelliJ IDEA的MyBatis日志插件，它的主要功能是帮助开发者在开发过程中实时查看并打印出MyBatis执行的SQL语句。这个功能对于调试和优化数据库查询非常...
3. **Spring整合MyBatis配置**：在Spring的配置文件（如`applicationContext.xml`）中，配置SqlSessionFactoryBean时，启用日志打印。这可以通过设置`configLocation`属性指向一个MyBatis的配置文件，例如`mybatis-...
本文将详细介绍如何在IntelliJ IDEA中配置MyBatis日志打印，以便完整地输出SQL。 首先，我们需要了解MyBatis的日志系统。MyBatis支持多种日志实现，包括Log4j、Logback和Java内置的日志API。默认情况下，MyBatis会...
把 mybatis 输出的sql日志还原成完整的sql语句。 将日志输出的sql语句中的问号 ? 替换成真正的参数值。 通过 "Tools -> MyBatis Log Plugin" 菜单或快捷键 "Ctrl+Shift+Alt+O" 启用。 点击窗口左边的 "Filter" ...
Entity Framework的`DbContext`类提供了`Log`属性，可以设置一个TextWriter对象，将生成的SQL语句记录到指定的目标，如控制台、日志文件等。例如： ```csharp db.Database.Log = Console.Write; var customers =...
2. **JdbcTemplate的使用**：在使用`JdbcTemplate`执行SQL时，其内部会自动创建SQL执行的Logger对象，如果日志级别设置为`DEBUG`，则会打印出SQL语句及其参数。例如，对于一个简单的查询操作： ```java ...
我们可以在 `Log` 方法中，通过分类名称，只输出 `Microsoft.EntityFrameworkCore.Database.Command` 分类下的日志，该日志即生成的 SQL 语句部分。 最后，我们还需要考虑如何显示敏感数据。例如，假如我们尝试插入...
"打印SQL日志插件"就是这样一款增强Eclipse功能的工具，它可以集成到Eclipse环境中，实现在控制台实时输出应用程序执行的SQL语句，让开发者无需离开IDE就能进行数据库相关的调试工作。 安装此插件的过程通常包括...
本篇将深入探讨如何在若依微服务中执行SQL语句，以及与之相关的数据库管理和微服务设计原则。 首先，SQL（Structured Query Language）是用于管理和处理关系型数据库的标准语言，包括数据查询、更新、插入和删除等...
在开发过程中，为了调试SQL语句，通常需要查看执行的SQL日志。"MyBatis Log Plugin"就是为了满足这一需求而设计的，它能直接在IDEA中显示MyBatis执行的SQL，免去了手动开启日志或者查看控制台的繁琐步骤。 接下来，...
一种可能的方法是通过日志配置，例如，启用MySQL的`Statement`和`PreparedStatement`日志，这样在执行时会打印出详细的SQL语句。 另外，开发工具如IntelliJ IDEA、Eclipse等，或者数据库管理工具如MySQL Workbench...
总结来说，CakePHP中打印SQL语句的方法主要包括使用内置日志系统、手动打印SQL语句、使用调试工具和合理配置日志记录参数。这些方法可以帮助开发者更加高效地进行问题诊断和性能优化。当需要打印SQL语句时，可以根据...
下面我们将详细讨论如何在Django中实现终端打印SQL语句。 首先，你需要在`settings.py`文件中配置日志模块（logging）。Django使用Python的内置`logging`模块来记录各种级别的消息，包括SQL语句。在`settings.py`中...
在执行`getUserByUsernameAndStatus`方法时，Mybatis会自动填充SQL参数，并将完整的SQL语句（包括参数值）打印到控制台。例如，如果传入的参数是`username='test'`和`status='active'`，控制台将输出如下内容： ```...
这篇文章主要介绍了调试Django时打印SQL语句的日志代码实例,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 设置里面添加如下代码： LOGGING = { 'version': ...
1. **SQL日志打印**：插件会在日志中详细输出每个SQL语句，包括原始的SQL模板、实际的参数值以及执行后的结果，这对于理解代码与数据库交互的情况非常有帮助。 2. **执行时间统计**：MybatisX还提供了SQL执行时间的...
Log4jdbc 是一个 Logging 工具，能够打印完整的 SQL 语句，包括参数 replacement，从而帮助开发者更好地维护和调试项目。下面我们将详细介绍如何在 SpringBoot 项目中使用 Log4jdbc 打印完整的 SQL 语句。 一、...
8. 之后，P6Spy将在指定的日志文件（如`c:/spy.log`）中记录所有的数据库操作，包括完整的SQL语句和执行详情。 通过P6Spy，我们可以得到诸如SQL语句的原始格式、执行时间、异常信息等详细信息，这对于分析和优化...
【标题】"mybatis查询入门(log4j控制台sql语句日记输出的)"涉及的知识点主要集中在MyBatis框架的基础使用以及如何通过Log4j在控制台输出SQL语句，以便于调试和优化数据库操作。MyBatis是一个轻量级的Java持久层框架...