This article is participating in the Java Theme Month – Java Debug Notes Event, see the event link for details
Hello everyone, I am a xing who failed to catch fish
background
The superior leader of A Star is responsible for recording information. The estimated daily data volume is about 150,000, so sharding-JDBC is introduced to make sub-tables.
After the superior leader finished the business development, he went through a wave of self-testing, and after git push, he was busy with other things.
The framework of the project is SpringBoot+Mybaits
There is something wrong with
The business that A Xing is responsible for has also been developed. He is skilled in Git pull, ready for self-test, unit test run and call it a day after going to the toilet. He is so confident.
When I came back, I looked at the console, and everyone was so stupid and red that I couldn’t help but sigh “If this were a stock fund, it would be great”.
The problem should be solved, with the investigation, I found a frown things are not simple, how some of the previous code are wrong?
With the screen, and finally to the Mybatis source code, find the culprit is sharding – JDBC, because the data source is sharding – JDBC, lead to the subsequent execute SQL is ShardingPreparedStatement.
This means that the sharding – JDBC effect all the business project list, because eventually ShardingPreparedStatement to do all the database interaction, some of the history of the SQL statement because SQL function or other writing, Prevented ShardingPreparedStatement processing and abnormal.
The key code is as follows
After finding the problem, A Xing immediately reported it to the leader.
Alas, originally wanted to touch the fish, it seems to touch the fish time is gone, but also more than a task.
Analysis of the
Even handed over to a xing to do, let’s roll up our sleeves and start to work, first look at the functional requirements of the table
- Supports custom sub-table policies
- Can control the area of influence
- generality
The sub-table will be established in advance, so there is no need to consider the problem that the table does not exist, the core logic implementation, through the sub-table strategy to get the sub-table name, and then the dynamic replacement of the sub-table name to SQL.
Table strategy
To support the split table policy, we need to define the abstract interface of the split table policy as follows
/ * * *@AuthorProgram monkey star *@DescriptionTable policy interface *@Date2021/5/9 * /
public interface ITableShardStrategy {
/ * * *@author: Program Monkey star *@description: Generates the table name *@paramTableNamePrefix Specifies the table prefix *@paramThe value value *@date: 2021/5/9
* @return: java.lang.String
*/
String generateTableName(String tableNamePrefix,Object value);
/** * Verify tableNamePrefix */
default void verificationTableNamePrefix(String tableNamePrefix){
if (StrUtil.isBlank(tableNamePrefix)) {
throw new RuntimeException("tableNamePrefix is null"); }}}Copy the codeThe generateTableName function generates a sub-table name. The input arguments are tableNamePrefix and value. TableNamePrefix is the prefix of the sub-table, and value is the logical parameter used to generate the sub-table name.
VerificationTableNamePrefix tableNamePrefix required function test and verify, provide for the use of the implementation class.
For easy to understand, the following is the id mold policy code, mold two tables
/ * * *@AuthorProgram monkey star *@DescriptionTable policy ID *@Date2021/5/9 * /
@Component
public class TableShardStrategyId implements ITableShardStrategy {
@Override
public String generateTableName(String tableNamePrefix, Object value) {
verificationTableNamePrefix(tableNamePrefix);
if (value == null || StrUtil.isBlank(value.toString())) {
throw new RuntimeException("value is null");
}
long id = Long.parseLong(value.toString());
// Cache tuning can be done here
return tableNamePrefix + "_" + (id % 2); }}Copy the codeThe value passed in is the id value. Use tableNamePrefix to concatenate the value of the id modulo to get the sub-table name.
Control area of influence
Table division strategy has been abstract, the following should be considered to control the scope of influence, we all know that in Mybatis specification each Mapper class corresponds to a business subject table, Mapper class function corresponds to the relevant SQL of the business subject table.
A xing thought, can be annotated to the Mapper class, on behalf of the Mpaaer class corresponding to the business subject table has sub-table requirements, from the specification of Mapper class each function corresponding to the subject table is correct, but some students may not according to the specification to write.
If the Mpaaer class corresponds to the B table, A function of the Mpaaer class writes the SQL of the A table, or even historical legacy problems, so annotations can not only be played on the Mapper class, but also can be played on any function of the Mapper class, and ensure that small granularity overwrites coarse granularity.
A star here custom table annotation, the code is as follows
/ * * *@AuthorProgram monkey star *@DescriptionTable notes *@Date2021/5/9 * /
@Target(value = {ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TableShard {
// Table prefix name
String tableNamePrefix(a);
/ / value
String value(a) default "";
// If yes, change the value of the field name (default no)
boolean fieldFlag(a) default false;
// The corresponding subtable policy class
Class<? extends ITableShardStrategy> shardStrategy();
}
Copy the codeAnnotations are scoped to classes, interfaces, and functions, and take effect at run time.
The tableNamePrefix and shardStrategy attributes are easy to understand. The table prefix name and the sorting strategy are easy to understand. The sorting strategy is divided into two types: the first type depends on a field value in the table, and the second type does not depend on a field value in the table.
Value here sets the name of the enterprise ID input parameter field. FieldFlag is true, which means that the value corresponding to the enterprise ID field name will be parsed and obtained.
Value and fieldFlag are not required. You can also set the time format for value. For details, see the logic of the strategy implementation class.
generality
Abstract sub-table strategy and sub-table annotation are done, the last step is according to the sub-table annotation information, to execute the sub-table strategy to get the sub-table name, and then dynamic replacement of the sub-table name into SQL, at the same time with universal.
StatementHandler#prepare = StatementHandle; StatementHandle = StatementHandle; StatementHandle = StatementHandle;
The Mybatis sub-table interceptor flow chart is shown below
Mybatis sub-table interceptor code is as follows, a bit long ha, the main process to see the Intercept function is good.
/ * * *@AuthorProgrammer XING *@DescriptionTable interceptor *@Date2021/5/9 * /
@Intercepts({ @Signature( type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class} ) })
public class TableShardInterceptor implements Interceptor {
private static final ReflectorFactory defaultReflectorFactory = new DefaultReflectorFactory();
@Override
public Object intercept(Invocation invocation) throws Throwable {
// MetaObject is a tool class provided in Mybatis, similar to reflection effect
MetaObject metaObject = getMetaObject(invocation);
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
MappedStatement mappedStatement = (MappedStatement)
metaObject.getValue("delegate.mappedStatement");
// Get the Mapper execution method
Method method = invocation.getMethod();
// get the table annotation
TableShard tableShard = getTableShard(method,mappedStatement);
// If neither Method nor class has a TableShard annotation or the execution method does not exist, execute the next plug-in logic
if (tableShard == null) {
return invocation.proceed();
}
/ / get the value
String value = tableShard.value();
If yes, the value of the request parameter field name needs to be resolved
boolean fieldFlag = tableShard.fieldFlag();
if (fieldFlag) {
// Get the request parameters
Object parameterObject = boundSql.getParameterObject();
if (parameterObject instanceof MapperMethod.ParamMap) { //ParamMap Logical processing
MapperMethod.ParamMap parameterMap = (MapperMethod.ParamMap) parameterObject;
// Get parameter values based on field names
Object valueObject = parameterMap.get(value);
if (valueObject == null) {
throw new RuntimeException(String.format("Input parameter field %s does not match", value));
}
/ / replace SQL
replaceSql(tableShard, valueObject, metaObject, boundSql);
} else { // Single-parameter logic
// Throw an exception if it is an underlying type
if (isBaseType(parameterObject)) {
throw new RuntimeException("Single parameter is illegal. Please use @param annotation.");
}
if (parameterObject instanceof Map){
Map<String,Object> parameterMap = (Map<String,Object>)parameterObject;
Object valueObject = parameterMap.get(value);
/ / replace SQL
replaceSql(tableShard, valueObject, metaObject, boundSql);
} else {
// Non-base type objectsClass<? > parameterObjectClass = parameterObject.getClass(); Field declaredField = parameterObjectClass.getDeclaredField(value); declaredField.setAccessible(true);
Object valueObject = declaredField.get(parameterObject);
/ / replace SQLreplaceSql(tableShard, valueObject, metaObject, boundSql); }}}else {// There is no need to handle parameterField
/ / replace SQL
replaceSql(tableShard, value, metaObject, boundSql);
}
// Execute the next plug-in logic
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
// If the target class is of type StatementHandler, the target class is wrapped. Otherwise, the target is returned directly to reduce the number of times the target is propped
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
returntarget; }}/ * * *@param object
* @methodName: isBaseType
* @author: Programmer Xing *@description: Basic data type validation, true yes, false No *@date: 2021/5/9
* @return: boolean
*/
private boolean isBaseType(Object object) {
if (object.getClass().isPrimitive()
|| object instanceof String
|| object instanceof Integer
|| object instanceof Double
|| object instanceof Float
|| object instanceof Long
|| object instanceof Boolean
|| object instanceof Byte
|| object instanceof Short) {
return true;
} else {
return false; }}/ * * *@paramTableShard Table Comments *@paramThe value value *@paramMetaObject Mybatis Reflection object *@paramBoundSql Indicates the SQL information object *@author: Program Monkey star *@description: Replaces SQL *@date: 2021/5/9
* @return: void
*/
private void replaceSql(TableShard tableShard, Object value, MetaObject metaObject, BoundSql boundSql) {
String tableNamePrefix = tableShard.tableNamePrefix();
// Get the policy class
Class<? extends ITableShardStrategy> strategyClazz = tableShard.shardStrategy();
// Get the policy class from the Spring IOC container
ITableShardStrategy tableShardStrategy = SpringUtil.getBean(strategyClazz);
// Generate table names
String shardTableName = tableShardStrategy.generateTableName(tableNamePrefix, value);
/ / access to SQL
String sql = boundSql.getSql();
// Complete table name replacement
metaObject.setValue("delegate.boundSql.sql", sql.replaceAll(tableNamePrefix, shardTableName));
}
/ * * *@param invocation
* @author: Program Monkey star *@descriptionMybatis: MetaObject object -mybatis provides a tool class, similar to the effect of reflection *@date: 2021/5/9
* @return: org.apache.ibatis.reflection.MetaObject
*/
private MetaObject getMetaObject(Invocation invocation) {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
// MetaObject is a tool class provided in Mybatis, similar to reflection effect
MetaObject metaObject = MetaObject.forObject(statementHandler,
SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
defaultReflectorFactory
);
return metaObject;
}
/ * * *@author: Program Monkey star *@description: Get table annotation *@param method
* @param mappedStatement
* @date: 2021/5/9
* @return: com.xing.shard.interceptor.TableShard
*/
private TableShard getTableShard(Method method, MappedStatement mappedStatement) throws ClassNotFoundException {
String id = mappedStatement.getId();
/ / for the Class
final String className = id.substring(0, id.lastIndexOf("."));
// table annotation
TableShard tableShard = null;
// Get TableShard annotation for the Mapper execution method
tableShard = method.getAnnotation(TableShard.class);
// If the method does not set annotations, obtain a TableShard annotation from the Mapper interface
if (tableShard == null) {
// Get TableShard annotation
tableShard = Class.forName(className).getAnnotation(TableShard.class);
}
returntableShard; }}Copy the codeHere, in fact table function has been completed, we just need to put the table strategy abstract interface, table annotation, table interceptor smoke into a general jar package, you need to use the project into the jar, then register table interceptors, their realization table strategy according to business demand, to the corresponding Mpaaer plus table annotation.
Practice running
Here a star alone wrote a set of demo, the scene is there are two table strategy, the table has been established in advance
- According to the
idtabletb_log_id_0tb_log_id_1
- According to the date table
tb_log_date_202105tb_log_date_202106
Warning: the following are code operation links, please read the reader greatly patience (except non-Java development).
TableShardStrategy definition
/ * * *@Author wx
* @DescriptionTable policy date *@Date2021/5/9 * /
@Component
public class TableShardStrategyDate implements ITableShardStrategy {
private static final String DATE_PATTERN = "yyyyMM";
@Override
public String generateTableName(String tableNamePrefix, Object value) {
verificationTableNamePrefix(tableNamePrefix);
if (value == null || StrUtil.isBlank(value.toString())) {
return tableNamePrefix + "_" +DateUtil.format(new Date(), DATE_PATTERN);
} else {
return tableNamePrefix + "_" +DateUtil.format(newDate(), value.toString()); }}} ** *@AuthorProgram monkey star *@DescriptionTable policy ID *@Date 2021/5/9* /@Component
public class TableShardStrategyId implements ITableShardStrategy {
@Override
public String generateTableName(String tableNamePrefix, Object value) {
verificationTableNamePrefix(tableNamePrefix);
if (value == null || StrUtil.isBlank(value.toString())) {
throw new RuntimeException("value is null");
}
long id = Long.parseLong(value.toString());
// Local cache optimization can be added
return tableNamePrefix + "_" + (id % 2); }}Copy the codeMapper definition
Mapper interfaces
/ * * *@AuthorProgram monkey star *@Description
* @Date2021/5/8 * /
@TableShard(tableNamePrefix = "tb_log_date",shardStrategy = TableShardStrategyDate.class)
public interface LogDateMapper {
/** * query list by date */
List<LogDate> queryList(a);
/** * single insert - by date table */
void save(LogDate logDate); } -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --/ * * *@AuthorProgram monkey star *@Description
* @Date2021/5/8 * /
@TableShard(tableNamePrefix = "tb_log_id",value = "id",fieldFlag = true,shardStrategy = TableShardStrategyId.class)
public interface LogIdMapper {
/** * Query by ID - Fragment by ID */
LogId queryOne(@Param("id") long id);
/** * single insert - Fragment by ID */
void save(LogId logId);
}
Copy the codeMapper.xml
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xing.shard.mapper.LogDateMapper">// this corresponds to LogDateMapper#queryList<select id="queryList" resultType="com.xing.shard.entity.LogDate">
select
id as id,
comment as comment,
create_date as createDate
from
tb_log_date
</select>// corresponds to LogDateMapper#save<insert id="save" >
insert into tb_log_date(id, comment,create_date)
values (#{id}, #{comment},#{createDate})
</insert>
</mapper>
-------------------------------------------------------------------------------------------------
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xing.shard.mapper.LogIdMapper">// this corresponds to LogIdMapper#queryOne<select id="queryOne" resultType="com.xing.shard.entity.LogId">
select
id as id,
comment as comment,
create_date as createDate
from
tb_log_id
where
id = #{id}
</select>// Corresponds to the save function<insert id="save" >
insert into tb_log_id(id, comment,create_date)
values (#{id}, #{comment},#{createDate})
</insert>
</mapper>
Copy the codeRun the unit tests
Date table unit test execution
@Test
void test(a) {
LogDate logDate = new LogDate();
logDate.setId(snowflake.nextId());
logDate.setComment("Test content");
logDate.setCreateDate(new Date());
/ / insert
logDateMapper.save(logDate);
/ / query
List<LogDate> logDates = logDateMapper.queryList();
System.out.println(JSONUtil.toJsonPrettyStr(logDates));
}
Copy the codeThe output
Id sub-table unit test execution
@Test
void test(a) {
LogId logId = new LogId();
long id = snowflake.nextId();
logId.setId(id);
logId.setComment("Test");
logId.setCreateDate(new Date());
/ / insert
logIdMapper.save(logId);
/ / query
LogId logIdObject = logIdMapper.queryOne(id);
System.out.println(JSONUtil.toJsonPrettyStr(logIdObject));
}
Copy the codeThe output
The subtotal
This article can be regarded as the advanced use of Mybatis tutorial, through Mybatis interceptor to achieve the function of sub-table, to meet the basic business needs, although relatively simple, but Mybatis this extension mechanism and design is worth learning and thinking.
Interested readers can also write their own, or based on a star to do the transformation, after all, is a simple version, or a lot of scenes are not considered.
In addition to the table of demo projects, A Star in Gitee and the public account, we need to take
- Gitee address: gitee.com/jxncwx/shar…
- The official account replied to fb
Project Structure:
Good historical articles recommended
- Word long | 16 picture AbstractQueuedSynchronizer
- LockSupport for small white look
- 13 pictures, in-depth understanding of Synchronized
- From shallow to deep CAS, Xiao Bai can also align with BAT interviewers
- Small white also can understand the Java memory model
- Nanny Level teaching, 22 images uncover ThreadLocal
- Can process, thread and coroutine be confused? A penny for you to know!
- What is thread safety? This article will give you insight
About me
Here is A star, a Java program ape who loves technology. The public account “program ape A Star” will regularly share the operating system, computer network, Java, distributed, database and other high-quality original articles. 2021, grow together with you on the road of Be Better!
Thank you very much for your little brothers and sisters to see here, the original is not easy, the article can be helpful to pay attention to, point a like, share and comment, are support (don’t want white whoring)!
May you and I both go where we want to go. See you in the next article