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

This article mainly introduces "how to use springboot+mybatis+Sharding jdbc to achieve sub-library sub-table, read-write separation". In daily operation, I believe that many people have doubts about how to use springboot+mybatis+Sharding jdbc to achieve sub-library sub-table and read-write separation. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "how to use springboot+mybatis+Sharding jdbc to achieve sub-database sub-table, read-write separation". Next, please follow the editor to study!

Project building

1. Create a new spring boot project and add dependencies

Top.qrainly bj_core 0.0.1-SNAPSHOT org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.2 com.alibaba druid-spring-boot-starter 1.1.10 Io.shardingsphere sharding-jdbc-spring-boot-starter 3.1.0.M1 com.github.jsonzou jmockdata 4.1.2

Here is a friendly recommendation, dependent on the use of a friend's open source tool plug-in JMockData, this is a sharp tool for development and testing, highly recommended!

2. Execute sql script in master library to create user table

DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (`id` int (12) NOT NULL AUTO_INCREMENT, `username` varchar (12) NOT NULL, `password` varchar (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx- username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `user_ 0`; CREATE TABLE `user_ 0` (`id`int (12) NOT NULL AUTO_INCREMENT, `username` varchar (12) NOT NULL, `password` varchar (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx- username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `user_ 1` CREATE TABLE `user_ 1` (`id`int (12) NOT NULL AUTO_INCREMENT, `username` varchar (12) NOT NULL, `password` varchar (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx- username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `user_ 2`; CREATE TABLE `user_ 2` (`id`int (12) NOT NULL AUTO_INCREMENT, `username` varchar (12) NOT NULL, `password` varchar (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx- username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `user_ 3` CREATE TABLE `user_ 3` (`id`int (12) NOT NULL AUTO_INCREMENT, `username` varchar (12) NOT NULL, `password` varchar (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx- username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `user_ 4`; CREATE TABLE `user_ 4` (`id`INT (12) NOT NULL AUTO_INCREMENT, `username` VARCHAR (12) NOT NULL, `password` VARCHAR (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx- username` (`username`)) ENGINE=INNODB DEFAULT CHARSET=utf8

To configure master-slave replication, please refer to the master-slave configuration of mysql on windows.

3. Configuration generation dao/domain file mybatis-generator configuration

4. Provide an interface for querying and adding

Controller

/ * * @ author v_liuwen * @ date 2019-07-10 * / @ RestController@RequestMapping (value = "/ user") @ Slf4jpublic class UserController {@ Autowired private IUserService iUserService; @ GetMapping ("/ list") public JsonResult list () {List userList = iUserService.findUserList (); return JsonResult.okJsonResultWithData (userList) } @ GetMapping ("/ add") public JsonResult add (@ RequestParam (name = "user", required = false) User user) {log.info ("add user information-- > {}", JSONObject.toJSONString (user)); boolean result = iUserService.addUser (); return JsonResult.okJsonResultWithData (result);} @ GetMapping ("/ batchAdd") public JsonResult batchAdd () {boolean result = iUserService.batchAddUser () Return JsonResult.okJsonResultWithData (result);}}

Service

/ * @ author v_liuwen * @ date 2019-07-10 * / @ Service@Slf4jpublic class IUserServiceImpl implements IUserService {private AtomicInteger num = new AtomicInteger (1); @ Resource private UserDAO userDAO; @ Override public boolean addUser () {User user = JMockData.mock (User.class); int I = userDAO.insertSelective (user); if (I = = 1) {return true;} return false } @ Override public List findUserList () {List userList = userDAO.findUserList (); return userList;} @ Override public boolean batchAddUser () {try {for (int I = 100ash iapplication.yml)

Server: port: 8018spring: application: name: bj-sharding-jdbc main: allow-bean-definition-overriding: true profiles: # rw- read-write separation configuration table- data sub-table + read-write separation dt- database sub-table + read-write separation active: dtmybatis: mapper-locations: classpath:/top/qrainly/**/dao/**/*.xml

Read-write separation configuration-- > application-rw.yml

Sharding: jdbc: dataSource: names: db-test0 Db-test1 db-test0: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 db-test1: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: Jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 config: # Open this configuration only when you configure read-write separation masterslave: # configure selection policy from the library Polling and random polling is provided. Here select polling / / random random / / round_robin polling load-balance-algorithm-type: round_robin name: db1s2 master-data-source-name: db-test0 slave-data-source-names: db-test1 props: sql: # enable SQL display. Default: false. Note: log will not be printed when only read-write separation is configured! Show: true

Data subtable + read-write separation configuration-- > application-table.yml

Sharding: jdbc: dataSource: names: db-test0 Db-test1 db-test0: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 db-test1: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: Jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 config: # configuration data subtable sharding: tables: user: table-strategy: standard: sharding-column: id precise-algorithm-class- Name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm # reads user_0 of ds_0 data source, User_1, user_2, user_3 actual-data-nodes: ds_0.user_$- > {0.3} master-slave-rules: ds_0: master-data-source-name: db-test0 slave-data-source-names: db-test1 props: sql: # enable SQL display Default value: false, note: logs will not be printed only when you configure read-write separation! Show: true

Sub-library and sub-table + read-write separation configuration-- > application-dt.yml

-sharding: jdbc: datasource: names: ds-master-0,ds-master-1,ds-master-0-slave-0 Ds-master-1-slave-0 # Master 0 ds-master-0: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root # Master 0-Slave 0 ds-master-0-slave-0: Password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root # main Library 1 ds-master-1: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com .mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root # Master Library 1-Slave Library 0 ds-master-1-slave-0: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding1? UseUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root config: sharding: tables: user: table-strategy: inline: sharding-column: id algorithm-expression: user_$- > {id% 5} key-generator-column-name: id actual-data-nodes: ds_$ -> {0.1} .user _ $- > {0.4} default-database-strategy: inline: # field of the sub-library This case is the logic of dividing sharding-column: id # according to id. Divide according to id%2 algorithm-expression: ds_$- > {id%2} master-slave-rules: ds_1: slave-data-source-names: ds-master-1-slave-0 master-data-source-name: ds-master-1 ds_0: slave-data-source-names: ds-master-0-slave-0 Master-data-source-name: ds-master-0

Note: under the configuration of sub-library and sub-table, the parameter exclude= {DataSourceAutoConfiguration.class} needs to be added to @ SpringBootApplication.

Ok, switch spring.profiles.active and play in different configuration modes!

At this point, the study on "how to use springboot+mybatis+Sharding jdbc to achieve sub-database sub-table, read-write separation" 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.