- Dependencias Maven =====================
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-dbcp</artifactId>
<version>10.0.16</version>
</dependency>
- Configuración application.yml ================================
spring:
sharding-sphere:
datasource:
names: master
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/production_dev?serverTimezone=UTC&characterEncoding=utf-8&stringtype=unspecified
username: postgres
password: 123456
rules:
sharding:
sharding-algorithms:
month-partition-algorithm:
props:
strategy: standard
algorithmClassName: com.tech.partition.DateRangeShardingAlgorithm
type: CLASS_BASED
tables:
work_procedure:
actual-data-nodes: master.$->{com.tech.partition.PartitionTableHelper.getCachedTableNames()}
table-strategy:
standard:
sharding-column: create_time
sharding-algorithm-name: month-partition-algorithm
work_result:
actual-data-nodes: master.$->{com.tech.partition.PartitionTableHelper.getCachedTableNames()}
table-strategy:
standard:
sharding-column: create_time
sharding-algorithm-name: month-partition-algorithm
bindingTables:
- work_procedure, work_result
props:
sql-show: true
- Algoritmo de particionamiento por fecha ==========================================
package com.tech.partition;
import com.google.common.collect.Range;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class DateRangeShardingAlgorithm implements StandardShardingAlgorithm<Date> {
private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
/**
* Particionamiento preciso para operaciones puntuales
* @param availableTables Conjunto de tablas físicas disponibles en el shard
* @param preciseShardingValue Valor de particionamiento con nombre de tabla lógica, columna y valor
* @return Nombre de la tabla física
*/
@Override
public String doSharding(Collection<String> availableTables, PreciseShardingValue<Date> preciseShardingValue) {
Object value = preciseShardingValue.getValue();
String tableSuffix;
if (value instanceof Date) {
LocalDate localDate = ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
tableSuffix = localDate.format(DateTimeFormatter.ofPattern("yyyy_MM"));
} else {
String columnValue = (String) value;
tableSuffix = LocalDateTime.parse(columnValue, formatter).format(DateTimeFormatter.ofPattern("yyyy_MM"));
}
String logicTableName = preciseShardingValue.getLogicTableName();
String actualTableName = logicTableName.concat("_").concat(tableSuffix);
if (!availableTables.contains(actualTableName)) {
availableTables.add(actualTableName);
}
return PartitionTableHelper.validateAndCreateTable(logicTableName, actualTableName);
}
/**
* Particionamiento para consultas con rangos de fecha
* @param availableTables Conjunto de tablas físicas disponibles
* @param rangeShardingValue Rango de valores para particionamiento
* @return Conjunto de tablas físicas que cubren el rango
*/
@Override
public Collection<String> doSharding(Collection<String> availableTables, RangeShardingValue<Date> rangeShardingValue) {
String logicTableName = rangeShardingValue.getLogicTableName();
Range<Date> valueRange = rangeShardingValue.getValueRange();
LocalDateTime startDate;
LocalDateTime endDate;
Object lowerEndpoint = valueRange.lowerEndpoint();
Object upperEndpoint = valueRange.upperEndpoint();
if (lowerEndpoint instanceof String) {
String lowerString = (String) lowerEndpoint;
String upperString = (String) upperEndpoint;
startDate = LocalDateTime.parse(lowerString, formatter);
endDate = LocalDateTime.parse(upperString, formatter);
} else {
startDate = valueRange.lowerEndpoint().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
endDate = valueRange.upperEndpoint().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
}
if (endDate.isAfter(LocalDateTime.now())) {
endDate = LocalDateTime.now();
}
Set<String> rangeTables = computeRangeTables(logicTableName, startDate, endDate);
HashSet<String> cachedTables = PartitionTableHelper.getCachedTableNames();
if (availableTables.size() != cachedTables.size()) {
availableTables.clear();
availableTables.addAll(cachedTables);
}
ArrayList<String> resultTables = new ArrayList<>(cachedTables);
resultTables.retainAll(rangeTables);
return resultTables;
}
@Override
public String getType() {
return null;
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init() {
}
/**
* Calcula los nombres de tablas que cubren un rango de fechas
* @param logicTableName Nombre de la tabla lógica
* @param startDate Fecha de inicio del rango
* @param endDate Fecha de fin del rango
* @return Conjunto de nombres de tablas físicas
*/
private Set<String> computeRangeTables(String logicTableName, LocalDateTime startDate, LocalDateTime endDate) {
Set<String> tablesInRange = new HashSet<>();
while (startDate.isBefore(endDate)) {
String tableName = generateTableName(logicTableName, startDate);
tablesInRange.add(tableName);
startDate = startDate.plusMonths(1);
}
String finalTableName = generateTableName(logicTableName, endDate);
tablesInRange.add(finalTableName);
return tablesInRange;
}
/**
* Genera el nombre de la tabla física a partir de una fecha
* @param dateTime Fecha para generar el sufijo
* @param logicTableName Nombre de la tabla lógica
* @return Nombre de la tabla física
*/
private String generateTableName(String logicTableName, LocalDateTime dateTime) {
String suffix = dateTime.format(DateTimeFormatter.ofPattern("yyyy_MM"));
return logicTableName.concat("_").concat(suffix);
}
}
- Utilitario de gestión de tablas particoinadas ================================================
package com.tech.partition;
import com.tech.event.BaseApplicationEvent;
import com.tech.utils.SpringContextHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.shardingsphere.infra.config.RuleConfiguration;
import org.apache.shardingsphere.mode.manager.ContextManager;
import org.apache.shardingsphere.sharding.algorithm.config.AlgorithmProvidedShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.ShardingStrategyConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import static com.tech.constants.AppConstants.CREATE_INDEX;
@Slf4j
@Component
public class PartitionTableHelper {
private static final String schemaName = "schema_logic";
@Resource
private ShardingSphereDataSource shardDataSource;
@Autowired
private ApplicationContext appContext;
private static ApplicationContext context;
private static ShardingSphereDataSource dataSourceRef;
private static final HashSet<String> tableNamesCache = new HashSet<>();
private static final List<String> managedTables = Arrays.asList("work_procedure", "work_manage", "work_procedure_file", "work_result");
static {
managedTables.forEach(table -> {
String suffix = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy_MM"));
String fullTableName = table.concat("_").concat(suffix);
tableNamesCache.add(fullTableName);
});
}
@PostConstruct
public void initialize() {
dataSourceRef = shardDataSource;
context = appContext;
}
/**
* Recupera todos los nombres de tablas del esquema
* @return Lista de nombres de tablas
*/
public static List<String> fetchAllTableNames() {
List<String> tableNames = new ArrayList<>();
String query = "SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename ~ '_\\d{4}_\\d{2}$';";
DataSource dataSource = SpringContextHelper.getBean(DataSource.class);
try (Connection conn = dataSource.getConnection()) {
Statement stmt = conn.createStatement();
try (ResultSet rs = stmt.executeQuery(query)) {
while (rs.next()) {
tableNames.add(rs.getString(1));
}
}
} catch (SQLException e) {
log.error("Error al ejecutar consulta: {}", e.getMessage());
throw new RuntimeException(e);
}
return tableNames;
}
/**
* Verifica si la tabla física existe, si no la crea automáticamente
* @param logicalTableName Nombre de la tabla lógica
* @param physicalTableName Nombre de la tabla física
* @return Nombre de la tabla física confirmada en la base de datos
*/
public static String validateAndCreateTable(String logicalTableName, String physicalTableName) {
synchronized (logicalTableName.intern()) {
if (tableNamesCache.contains(physicalTableName)) {
return physicalTableName;
}
String createSql = "CREATE TABLE " + physicalTableName + " (LIKE " + logicalTableName + " );";
String primaryKeySql = "ALTER TABLE " + physicalTableName + " ADD CONSTRAINT " + physicalTableName + "_pk PRIMARY KEY (id);";
DataSource dataSource = SpringContextHelper.getBean(DataSource.class);
try {
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate(createSql);
} catch (SQLException e) {
log.warn("Tabla ya existe o error al crear: {}", e.getMessage());
}
try {
stmt.executeUpdate(primaryKeySql);
} catch (SQLException e) {
log.warn("Clave primaria ya existe: {}", e.getMessage());
}
List<String> tableInfo = new ArrayList<>(2);
tableInfo.add(logicalTableName);
tableInfo.add(physicalTableName);
context.publishEvent(new BaseApplicationEvent<>(tableInfo, AppConstants.CREATE_INDEX));
} catch (SQLException e) {
log.error("Error de base de datos: {}", e.getMessage());
throw new RuntimeException(e);
}
refreshCache(true);
}
return physicalTableName;
}
/**
* Recarga el caché de nombres de tablas
*/
public static void refreshCache(boolean updateRules) {
List<String> allTables = fetchAllTableNames();
tableNamesCache.clear();
tableNamesCache.addAll(allTables);
if (updateRules) {
reloadShardingRules(dataSourceRef, schemaName);
}
}
/**
* Obtiene las tablas en caché
*/
public static HashSet<String> getCachedTableNames() {
return tableNamesCache;
}
/**
* Genera nombres de tablas a partir de expresiones
*/
public static List<String> generateTableNames(String expression) {
List<String> tableNames = new ArrayList<>();
List<String> resultNames = new ArrayList<>();
List<String> extractedValues = extractExpressionValues(expression);
if (expression.contains(".")) {
expression = expression.substring(expression.indexOf(".") + 1);
}
String processedExpression = expression.replaceAll("\\$\\{.*?}", "#");
for (int i = 0; i < extractedValues.size(); i++) {
String value = extractedValues.get(i).replaceAll("\\.", "#");
String[] parts = value.split("##");
int startVal = Integer.parseInt(parts[0]);
int endVal = Integer.parseInt(parts[1]);
if (i == 0) {
for (int j = startVal; j <= endVal; j++) {
String replaced = processedExpression.replaceFirst("#", String.valueOf(j));
tableNames.add(replaced);
}
} else {
for (int k = 0; k < tableNames.size(); k++) {
for (int j = startVal; j <= endVal; j++) {
String s1 = tableNames.get(k).replaceFirst("#", String.valueOf(j));
resultNames.add(s1);
}
}
}
}
return resultNames;
}
/**
* Extrae valores de expresiones como ${2023..2050}
*/
private static List<String> extractExpressionValues(String expression) {
List<String> values = new ArrayList<>();
Pattern pattern = Pattern.compile("\\$\\{([^}]*)\\}");
Matcher matcher = pattern.matcher(expression);
while (matcher.find()) {
values.add(matcher.group(1));
}
return values;
}
/**
* Recarga las reglas de particionamiento
*/
private static void reloadShardingRules(ShardingSphereDataSource dataSource, String schema) {
ContextManager ctxManager = dataSource.getContextManager();
Collection<RuleConfiguration> ruleConfigs = dataSource.getContextManager()
.getMetaDataContexts()
.getMetaData(schema)
.getRuleMetaData()
.getConfigurations();
for (RuleConfiguration config : ruleConfigs) {
Collection<ShardingTableRuleConfiguration> tables = ((AlgorithmProvidedShardingRuleConfiguration) config).getTables();
tables.forEach(table -> {
String logicName = table.getLogicTable();
if (managedTables.contains(logicName)) {
updateActualDataNodes(table, "master.$->{com.tech.partition.PartitionTableHelper.getCachedTableNames()}");
}
});
}
ctxManager.alterRuleConfiguration(schemaName, ruleConfigs);
}
private static void updateActualDataNodes(ShardingTableRuleConfiguration config, String dataNodes) {
try {
Field field = ShardingTableRuleConfiguration.class.getDeclaredField("actualDataNodes");
field.setAccessible(true);
field.set(config, dataNodes);
} catch (NoSuchFieldException | IllegalAccessException e) {
log.error("Error al actualizar nodos de datos: {}", e.getMessage());
}
}
}
- Inicializador de caché de tablas ===================================
package com.tech.partition;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.YamlPropertiesFactoryBean;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@Order(value = 1)
@Component
public class PartitionTableLoader implements CommandLineRunner {
@Autowired
private Environment env;
private static LocalDateTime firstTableDate;
private static final List<String> managedTables = Arrays.asList("work_procedure", "work_manage", "work_procedure_file", "work_result");
@Override
public void run(String... args) {
synchronizeTables();
}
@Scheduled(cron = "0 0 23 * * ?")
public void scheduledSync() {
Calendar cal = Calendar.getInstance();
if (cal.get(Calendar.DATE) == cal.getActualMaximum(Calendar.DATE)) {
synchronizeTables();
}
}
/**
* Sincroniza las tablas lógicas con las físicas, creando las que falten
*/
public void synchronizeTables() {
PartitionTableHelper.refreshCache(false);
HashSet<String> existingTables = PartitionTableHelper.getCachedTableNames();
HashSet<String> expectedTables = new HashSet<>();
if (existingTables.size() > 0) {
if (firstTableDate == null) {
List<String> sortedDates = existingTables.stream()
.map(table -> table.substring(table.length() - 7))
.distinct()
.sorted()
.collect(Collectors.toList());
if (!sortedDates.isEmpty()) {
String dateStr = sortedDates.get(0);
String[] parts = dateStr.split("_");
firstTableDate = LocalDateTime.of(Integer.parseInt(parts[0]), Integer.parseInt(parts[1]), 1, 0, 0, 0);
}
}
if (firstTableDate != null) {
LocalDateTime iterator = firstTableDate;
while (iterator.isBefore(LocalDateTime.now().plusMonths(1))) {
String suffix = iterator.format(DateTimeFormatter.ofPattern("yyyy_MM"));
managedTables.forEach(table -> expectedTables.add(table.concat("_").concat(suffix)));
iterator = iterator.plusMonths(1);
}
}
} else {
managedTables.forEach(table -> {
String suffix = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy_MM"));
expectedTables.add(table.concat("_").concat(suffix));
});
}
expectedTables.removeAll(existingTables);
if (!expectedTables.isEmpty()) {
expectedTables.forEach(table ->
PartitionTableHelper.validateAndCreateTable(
table.substring(0, table.length() - 8),
table
)
);
}
}
/**
* Genera lista de tablas particionadas desde configuración
*/
private void initializeFromConfig() {
List<String> generatedTables = parseTableNamesFromConfig();
PartitionTableHelper.refreshCache(false);
generatedTables.forEach(tableName -> {
String[] parts = tableName.split("_");
StringBuilder builder = new StringBuilder();
Pattern numberPattern = Pattern.compile("^(\\d+)(.*)");
for (String part : parts) {
Matcher matcher = numberPattern.matcher(part);
if (!matcher.matches()) {
builder.append(part).append("_");
}
}
String logicalTable = builder.toString();
if (logicalTable.endsWith("_")) {
logicalTable = logicalTable.substring(0, logicalTable.length() - 1);
}
PartitionTableHelper.validateAndCreateTable(logicalTable, tableName);
});
}
/**
* Extrae nombres de tablas desde el archivo de configuración
*/
private List<String> parseTableNamesFromConfig() {
List<String> propertyValues = new ArrayList<>();
List<String> expressions = new ArrayList<>();
List<String> generatedNames = new ArrayList<>();
YamlPropertiesFactoryBean factory = new YamlPropertiesFactoryBean();
String[] activeProfiles = env.getActiveProfiles();
factory.setResources(new ClassPathResource("application-" + activeProfiles[0] + ".yml"));
Properties props = factory.getObject();
assert props != null;
props.keySet().forEach(key -> {
if (key.toString().contains("actual-data-nodes")) {
propertyValues.add((String) props.get(key));
}
});
propertyValues.forEach(value -> {
if (value.contains(",")) {
expressions.addAll(Arrays.asList(value.split(",")));
}
});
expressions.forEach(expr -> {
List<String> names = PartitionTableHelper.generateTableNames(expr);
generatedNames.addAll(names);
});
return generatedNames;
}
}
- Utilitario de contexto de Spring ===================================
package com.tech.utils;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
@Component
@Slf4j
public class SpringContextHelper implements ApplicationContextAware {
private static ApplicationContext appCtx;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if (appCtx == null) {
appCtx = applicationContext;
}
}
public static ApplicationContext getApplicationContext() {
return appCtx;
}
public static <T> T getBean(Class<T> clazz) {
return appCtx.getBean(clazz);
}
public static <T> T getBean(String name, Class<T> clazz) {
return appCtx.getBean(name, clazz);
}
public static String getProperty(String key) {
return appCtx.getBean(Environment.class).getProperty(key);
}
public static Object getBean(String name) {
return appCtx.getBean(name);
}
}
- Consideraciones importantes ==============================
1. Las consultas SQL deben incluir en su cláusula WHERE la columna designada como clave de particionamiento para que el algoritmo personalizado sea invocado.
2. En las consultas SQL, evitar el uso de comillas dobles en nombres de tablas y columnas de particionamiento.
3. Los atributos marcados en rojo en el archivo de configuración de IDEA no afectan la ejecución correcta de la aplicación.
4. Las consultas sin clave de particionameinto se enrutan mediante broadcast a todas las tablas.
5. Antes de ejecutar consultas, las tablas particionadas deben estar creadas en la base de datos, de lo contrario se producirán errores de tabla inexistente.
6. El uso de palabras reservadas del motor de base de datos como nombres de columnas puede ganerar errores de sintaxis SQL - utilizar anotaciones como @TableField("\"path\"") para resolver conflictos.
7. Las tablas lógicas deben existir físicamente o utilizan un interceptor Mybatis-plus para forzar la inclusión de la columna de particionamiento en las consultas.
8. En operaciones JOIN entre múltiples tablas, el conjunto de tablas físicas existentes debe coincidir exactamente con las tablas configuradas en actual-data-nodes para utilizar ORDER BY, GROUP BY o COUNT(*).
9. Al iniciar la aplicación, el conjunto resultante de actual-data-nodes no puede estar vacío.
- Interceptor personalizado de Mybatis-plus ============================================
package com.tech.mybatis;
import com.tech.partition.PartitionTableHelper;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashSet;
import java.util.Properties;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@Intercepts({
@Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(method = "update", type = Executor.class, args = {MappedStatement.class, Object.class})
})
public class TableInterceptor implements Interceptor {
private static String startDate = "";
private static final List<String> targetTables = Arrays.asList("work_procedure", "work_manage", "work_procedure_file", "work_result");
@Override
public Object intercept(Invocation invocation) throws Throwable {
String sql = extractSql(invocation);
if (StringUtils.isBlank(sql)) {
return invocation.proceed();
}
boolean needsInjection = false;
String tableName = "";
String lowerSql = sql.toLowerCase();
for (String name : targetTables) {
if (lowerSql.contains(name)) {
String condition = "";
if (lowerSql.contains("where")) {
condition = lowerSql.substring(lowerSql.indexOf("where"));
}
condition = condition.replace("order by create_time", "");
if (!condition.contains("create_time")) {
needsInjection = true;
tableName = name;
break;
}
}
}
if (needsInjection) {
String modifiedSql = "";
final String pattern = tableName + "_\\d{4}_\\d{2}$";
String endDate = LocalDateTime.now().withHour(23).withMinute(59).withSecond(59)
.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
LocalDateTime calculatedDate = LocalDateTime.now().withDayOfMonth(1).withHour(0).withMinute(0).withSecond(0);
if (startDate.equals("")) {
HashSet<String> cachedTables = PartitionTableHelper.getCachedTableNames();
List<String> filteredTables = cachedTables.stream()
.filter(t -> Pattern.matches(pattern, t))
.sorted()
.collect(Collectors.toList());
if (!filteredTables.isEmpty()) {
String datePart = filteredTables.get(0).replace(tableName + "_", "");
String[] parts = datePart.split("_");
calculatedDate = LocalDateTime.of(Integer.parseInt(parts[0]), Integer.parseInt(parts[1]), 1, 0, 0, 0);
}
startDate = calculatedDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
if (sql.contains("where")) {
modifiedSql = sql.replace("where", "where (create_time between '" + startDate + "' and '" + endDate + "' ) and");
} else if (sql.contains("WHERE")) {
modifiedSql = sql.replace("WHERE", "WHERE (create_time between '" + startDate + "' and '" + endDate + "' ) and");
}
if (!modifiedSql.equals("")) {
rebuildSql(invocation, modifiedSql);
}
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
private String extractSql(Invocation invocation) {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object param = args[1];
BoundSql boundSql = ms.getBoundSql(param);
return boundSql.getSql();
}
private void rebuildSql(Invocation invocation, String sql) throws SQLException {
Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
Object param = args[1];
BoundSql boundSql = statement.getBoundSql(param);
MappedStatement newStatement = createStatement(statement, new BoundSqlSource(boundSql));
MetaObject metaObj = MetaObject.forObject(newStatement,
new DefaultObjectFactory(),
new DefaultObjectWrapperFactory(),
new DefaultReflectorFactory());
metaObj.setValue("sqlSource.boundSql.sql", sql);
args[0] = newStatement;
}
private MappedStatement createStatement(MappedStatement original, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(
original.getConfiguration(), original.getId(), newSqlSource, original.getSqlCommandType());
builder.resource(original.getResource());
builder.fetchSize(original.getFetchSize());
builder.statementType(original.getStatementType());
builder.keyGenerator(original.getKeyGenerator());
if (original.getKeyProperties() != null && original.getKeyProperties().length != 0) {
StringBuilder keyProps = new StringBuilder();
for (String prop : original.getKeyProperties()) {
keyProps.append(prop).append(",");
}
keyProps.deleteCharAt(keyProps.length() - 1);
builder.keyProperty(keyProps.toString());
}
builder.timeout(original.getTimeout());
builder.parameterMap(original.getParameterMap());
builder.resultMaps(original.getResultMaps());
builder.resultSetType(original.getResultSetType());
builder.cache(original.getCache());
builder.flushCacheRequired(original.isFlushCacheRequired());
builder.useCache(original.isUseCache());
return builder.build();
}
private String getOperationType(Invocation invocation) {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
SqlCommandType cmdType = ms.getSqlCommandType();
if (cmdType.compareTo(SqlCommandType.SELECT) == 0) return "select";
if (cmdType.compareTo(SqlCommandType.INSERT) == 0) return "insert";
if (cmdType.compareTo(SqlCommandType.UPDATE) == 0) return "update";
if (cmdType.compareTo(SqlCommandType.DELETE) == 0) return "delete";
return null;
}
class BoundSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
- Configuración de Mybatis-plus ================================
package com.tech.mybatis;
import cn.hutool.core.util.ArrayUtil;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.tech.config.TenantProperties;
import com.tech.utils.MachineUtil;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.NullValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.schema.Column;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.Arrays;
import java.util.List;
@Configuration
public class MybatisConfiguration {
@Autowired
private TenantProperties tenantProperties;
@Autowired
private MachineUtil machineUtil;
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
@Override
public Expression getTenantId() {
String code = machineUtil.getMachineCode();
return StringUtils.isEmpty(code) ? new NullValue() : new StringValue(code);
}
@Override
public boolean ignoreTable(String tableName) {
String[] ignoreList = tenantProperties.getExcludeTables();
final boolean hasList = !ArrayUtil.isEmpty(ignoreList);
final boolean contains = Arrays.stream(ignoreList).anyMatch(t -> t.equals(tableName.replace("\"", "").replace("`", "")));
return hasList && contains;
}
@Override
public String getTenantIdColumn() {
return "machine_code";
}
@Override
public boolean ignoreInsert(List<Column> columns, String tenantCol) {
return columns.stream().map(Column::getColumnName).anyMatch(n -> n.equalsIgnoreCase(tenantCol));
}
}));
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
@Bean
public TableInterceptor sqlInterceptor() {
return new TableInterceptor();
}
}
- Creación de índices para tablas particionadas =================================================
package com.tech.partition;
import com.tech.event.BaseApplicationEvent;
import com.tech.utils.TimeUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.event.EventListener;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;
import java.beans.Transient;
import java.sql.*;
import java.util.*;
@Slf4j
@Component
public class IndexCreationHandler {
private static Connection dbConnection;
@Value("${spring.shardingsphere.datasource.master.url}")
public String dbUrl;
@Value("${spring.shardingsphere.datasource.master.username}")
public String dbUser;
@Value("${spring.shardingsphere.datasource.master.password}")
public String dbPassword;
@Async
@Transient
@EventListener(classes = {BaseApplicationEvent.class},
condition = "T(com.tech.constants.AppConstants).CREATE_INDEX.equals(#event.topic)")
public synchronized void handleTableCreation(BaseApplicationEvent<List<String>> event) {
TimeUtils.sleep(3);
List<String> tableData = event.getData();
String logicalTable = tableData.get(0);
String physicalTable = tableData.get(1);
String indexQuery = "SELECT i.relname AS index_name, a.attname AS column_name " +
"FROM pg_index idx " +
"JOIN pg_class t ON t.oid = idx.indrelid " +
"JOIN pg_class i ON i.oid = idx.indexrelid " +
"JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(idx.indkey) " +
"WHERE t.relname = '" + logicalTable + "';";
Map<String, List<String>> indexMap = new HashMap<>();
try {
if (dbConnection == null || dbConnection.isClosed()) {
dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
}
Statement stmt = dbConnection.createStatement();
ResultSet rs = stmt.executeQuery(indexQuery);
while (rs.next()) {
String idxName = rs.getString(1);
String colName = rs.getString(2);
if (indexMap.containsKey(idxName)) {
indexMap.get(idxName).add(colName);
} else {
ArrayList<String> cols = new ArrayList<>();
cols.add(colName);
indexMap.put(idxName, cols);
}
}
if (!indexMap.isEmpty()) {
indexMap.keySet().forEach(idx -> {
List<String> columns = indexMap.get(idx);
String colList = String.join(",", columns);
String newIndexName = physicalTable + "_" + String.join("_", columns);
String createIdxSql = "CREATE INDEX " + newIndexName + " ON " + physicalTable + " (" + colList + ");";
try {
stmt.executeUpdate(createIdxSql);
} catch (SQLException e) {
log.warn("Índice ya existe: {}", e.getMessage());
}
});
}
} catch (SQLException e) {
log.error("Error al crear índice: {}", e.getMessage());
throw new RuntimeException(e);
}
}
}