Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how dynamic data sources integrate with Sharding JDBC". In daily operation, I believe many people have doubts about how dynamic data sources integrate with Sharding JDBC. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how dynamic data sources integrate with Sharding JDBC". Next, please follow the editor to study!

Why keep dynamic data sources?

Originally scheduled tasks have already used dynamic data sources, and one data source is mysql, the other two are Amazon's db, and Sharding-JDBC does not support Amazon's two db, obviously can not get rid of dynamic data sources, can only find a way to make both coexist.

How to make the two coexist without affecting each other

My idea is to configure the mysql data source to the Sharding-JDBC data source and let Sharding-JDBC manage it, while the dynamic data source manages the Sharding-JDBC data source. The configuration does not need to be changed. Transaction managers are still configured using dynamic data sources.

If you don't understand the configuration of dynamic data sources, you can take a look at my previous article: Spring Boot Project Multi-data Source configuration

You only need to change the configuration of the original dynamic data source to the following and replace the location of the original mysql data source with the Sharding-JDBC data source. The configuration of the transaction does not need to be changed.

/ * dynamic data sources: dynamically switch between different data sources through AOP * * @ return * / @ Primary @ Bean (name = "dynamicDataSource") public DataSource dynamicDataSource (@ Qualifier ("shardingDataSource") DataSource shardingDataSource, @ Qualifier ("athena-database") DataSource athenaDatabase) {DynamicDataSource dynamicDataSource = new DynamicDataSource () / / default data source, used when @ DataSource annotation is not used, / / and @ DataSource annotation is used. If beanName is not set, Aop should configure the default bean dynamicDataSource.setDefaultTargetDataSource (shardingDataSource); / / configure multiple data sources / / key-> bean Map dsMap = new HashMap (); dsMap.put (DataSourceContextHolder.getDefaultDataSource (), shardingDataSource) DsMap.put ("athenaDatabase", athenaDatabase); dynamicDataSource.setTargetDataSources (dsMap); return dynamicDataSource;}

/ * * configure @ Transactional things annotations * use dynamic data sources * * @ return * / @ Bean ("dynamicDataSourceTransactionManager") public PlatformTransactionManager transactionManager (@ Qualifier ("dynamicDataSource") DataSource dynamicDataSource) {return new DataSourceTransactionManager (dynamicDataSource);}

Configure the mysql data source to the Sharding-JDBC data source.

@ Bean (name = "shardingDataSource") public DataSource dataSource (@ Qualifier ("mysql-database") DataSource mysqlDatabase) throws SQLException {Map dataSourceMap = new HashMap (); dataSourceMap.put ("ds01", mysqlDatabase); return ShardingDataSourceConfig.getShardingDataSource (dataSourceMap, "ds01");}

Sharding-JDBC data source configuration see the previous part: after optimization and then optimization, it is still necessary to split the table, today's protagonist Sharding-JDBC

Why doesn't the configuration of transactions need to be changed?

If you are not configuring multiple data sources and there is no unified manager among multiple data sources, you need to configure a transaction manager for each data source.

Take a look at the source code of DataSourceTransactionManager and you will understand. DataSourceTransactionManager obtains the connection Connection through the data source, and the commit and rollback of the transaction call the commit and rollback methods of Connection. So, using a dynamic data source, the transaction manager gets the connection Connection returned by the target data source.

Now only the Sharding-JDBC data source is configured to the dynamic data source, while the mysql data source is configured to the Sharding-JDBC data source. Sharding-JDBC data sources, like dynamic data sources, dynamically select the target data source when getConnection is called, and then call the getConnection method of the selected data source. This is a design pattern, and the outside world does not need to care about exactly how to get the Connection of the correct data source.

Whether to use snowflake algorithm to automatically generate ID for inserting data

I specified in the configuration that the self-incrementing primary key uses the id generated by the snowflake algorithm. Because you can no longer use the self-increasing primary key of the database after dividing the table, otherwise you don't know which table to look up according to the id lookup data, and the associated table also needs to use this id to divide the table.

When configuring the routing rules of the table, in addition to configuring the slicing policy of the table, if you need to modify the generation of the primary key, you also need to add a primary key generator to the routing configuration of the table, as configured below. Officials provide two distributed primary key generation methods: uuid and Snowflake algorithm.

/ / configure distributed id generation algorithm. Snowflake algorithm must be used, otherwise report_click_info table cannot be associated with Properties properties = new Properties (); properties.setProperty ("worker.id", "10"); result.setKeyGeneratorConfig (new KeyGeneratorConfiguration ("SNOWFLAKE", "id", properties))

Why use Snowflake algorithm:

1. The id generated by snowflake algorithm is growing, that is, orderly. There is no need to adjust the index B+ tree at insert time.

two。 The original id in the database is integer, BIGINT (20), so using the snowflake algorithm does not need to change the structure of the table or add additional columns.

3. The snowflake algorithm can be used to calculate the date, and this feature can be used to implement sub-tables for associated tables.

The verification result is shown in the following figure. Not surprisingly, Sharding-JDBC is used to insert the data to rewrite the sql, add the id field, and use the snowflake algorithm to generate an id.

The billid in the figure is the id I queried after I finished inserting the data.

Will the table be created automatically when the table does not exist?

I looked through the official documentation from beginning to end, but I couldn't find an introduction to automatically creating tables, so Sharding-JDBC wasn't smart enough to help us create tables. When the physical table calculated by the sub-table algorithm does not exist, a pile of abnormal information will appear.

Table 'cayman.report_click_info_201906' doesn't exist

My current practice is to create tables for the next few months in advance. The disadvantage is that if you forget sometimes, the whole service will collapse.

All I can think of right now is to determine whether the table exists (in ShardingAlgorithm's doSharding method), and create it if it doesn't exist, but it's performance-consuming every time. A single library is fine, but it is even worse in the case of multiple libraries.

Will inserting a new record be routed to the table based on the sharded field?

When inserting a new record, you must ensure that the value of the shard field cannot be empty, otherwise an error will be reported directly. Because the shard field has no value, there is no way to route to the physical table, and you can't figure out which table to insert, so you have to give up throwing an exception.

Under normal circumstances, it is not allowed to use fields that can be null. If you are using a field of date type, such as the record creation time create_datetime, as a sharding (split table) field, you cannot declare that the current time of the system is used by default when creating the table. The value should be specified when the data is inserted and set to not empty. Avoid Murphy's law.

At this point, the study on "how to integrate dynamic data sources with Sharding JDBC" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.