這篇文章主要介紹“如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離”,在日常操作中,相信很多人在如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離”的疑惑有所幫助!接下來,請跟著小編一起來學習吧!
巴南網(wǎng)站建設公司創(chuàng)新互聯(lián)建站,巴南網(wǎng)站設計制作,有大型網(wǎng)站制作公司豐富經(jīng)驗。已為巴南1000+提供企業(yè)網(wǎng)站建設服務。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站制作要多少錢,請找那個售后服務好的巴南做網(wǎng)站的公司定做!
1、新建一個spring boot工程,添加依賴
<dependencies> <!--核心服務--> <dependency> <groupId>top.qrainly</groupId> <artifactId>bj_core</artifactId> <version>0.0.1-SNAPSHOT</version> </dependency> <!--mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <!--sharding-jdbc--> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.1.0.M1</version> </dependency> <!--jmockdata--> <dependency> <groupId>com.github.jsonzou</groupId> <artifactId>jmockdata</artifactId> <version>4.1.2</version> </dependency> </dependencies>
這里友情推薦一下,依賴里用到了朋友開源的一個工具插件JMockData,此乃開發(fā)測試之利器,強烈推薦?。?!
2、在master庫執(zhí)行sql腳本,創(chuàng)建用戶表
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;
配置主從復制的內(nèi)容請參考windows上MySQL的主從配置
3、配置生成dao/domain文件mybatis-generator配置
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"> <generatorConfiguration> <context id="DB2Tables" targetRuntime="MyBatis3"> <commentGenerator> <property name="suppressDate" value="true"/> <property name="suppressAllComments" value="true"/> </commentGenerator> <!--數(shù)據(jù)庫鏈接地址賬號密碼--> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true" userId="root" password="123456"> </jdbcConnection> <javaTypeResolver> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!--生成Model類存放位置--> <javaModelGenerator targetPackage="top.qrainly.sharding.jdbc.domain" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> <property name="trimStrings" value="true"/> </javaModelGenerator> <!--生成映射文件存放位置--> <sqlMapGenerator targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </sqlMapGenerator> <!--生成Dao類存放位置--> <!-- 客戶端代碼,生成易于使用的針對Model對象和XML配置文件 的代碼 type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper對象 type="MIXEDMAPPER",生成基于注解的Java Model 和相應的Mapper對象 type="XMLMAPPER",生成SQLMap XML文件和獨立的Mapper接口 --> <javaClientGenerator type="XMLMAPPER" targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!--生成對應表及類名--> <table tableName="user" domainObjectName="User" mapperName="UserDAO" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true"></table> </context> </generatorConfiguration>
4、提供一個查詢和添加的接口
controller
/** * @author v_liuwen * @date 2019-07-10 */ @RestController @RequestMapping(value = "/user") @Slf4j public class UserController { @Autowired private IUserService iUserService; @GetMapping("/list") public JsonResult<List<User>> list() { List<User> userList = iUserService.findUserList(); return JsonResult.okJsonResultWithData(userList); } @GetMapping("/add") public JsonResult<Boolean> add(@RequestParam(name = "user",required = false) User user) { log.info("新增用戶信息-->{}", JSONObject.toJSONString(user)); boolean result = iUserService.addUser(); return JsonResult.okJsonResultWithData(result); } @GetMapping("/batchAdd") public JsonResult<Boolean> batchAdd() { boolean result = iUserService.batchAddUser(); return JsonResult.okJsonResultWithData(result); } }
service
/** * @author v_liuwen * @date 2019-07-10 */ @Service @Slf4j public 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<User> findUserList() { List<User> userList = userDAO.findUserList(); return userList; } @Override public boolean batchAddUser() { try{ for (int i =100;i<150;i++){ User user = JMockData.mock(User.class); user.setId(num.getAndIncrement()); userDAO.insertSelective(user); } return true; }catch (Exception e){ log.error("批量插入失敗 失敗原因-->{}",e.getMessage()); return false; } } }
5、配置文件(重點在這里)
基礎(chǔ)配置-->application.yml
server: port: 8018 spring: application: name: bj-sharding-jdbc main: allow-bean-definition-overriding: true profiles: # rw-讀寫分離配置 table-數(shù)據(jù)分表+讀寫分離 dt-分庫分表+讀寫分離 active: dt mybatis: mapper-locations: classpath:/top/qrainly/**/dao/**/*.xml
讀寫分離配置-->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: # 僅配置讀寫分離時打開此配置 masterslave: # 配置從庫選擇策略,提供輪詢與隨機,這里選擇用輪詢//random 隨機 //round_robin 輪詢 load-balance-algorithm-type: round_robin name: db1s2 master-data-source-name: db-test0 slave-data-source-names: db-test1 props: sql: # 開啟SQL顯示,默認值: false,注意:僅配置讀寫分離時不會打印日志?。?! show: true
數(shù)據(jù)分表+讀寫分離配置-->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: # 配置數(shù)據(jù)分表 sharding: tables: user: table-strategy: standard: sharding-column: id precise-algorithm-class-name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm # 讀取ds_0數(shù)據(jù)源的user_0、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: # 開啟SQL顯示,默認值: false,注意:僅配置讀寫分離時不會打印日志?。?! show: true
分庫分表+讀寫分離配置-->application-dt.yml
--- sharding: jdbc: datasource: names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-1-slave-0 # 主庫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 # 主庫0-從庫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 # 主庫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 # 主庫1-從庫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: # 置的分庫的字段,本案例是根據(jù)id進行分 sharding-column: id # 置的分庫的邏輯,根據(jù)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
注:分庫分表配置下需要在@SpringBootApplication上添加參數(shù)exclude={DataSourceAutoConfiguration.class}
ok,切換spring.profiles.active在不同配置模式下耍吧!
到此,關(guān)于“如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離”的學習就結(jié)束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習,快去試試吧!若想繼續(xù)學習更多相關(guān)知識,請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
新聞名稱:如何用springboot+mybatis+Shardingjdbc實現(xiàn)的分庫分表、讀寫分離
本文網(wǎng)址:http://m.rwnh.cn/article12/pedddc.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供靜態(tài)網(wǎng)站、微信公眾號、網(wǎng)站設計公司、品牌網(wǎng)站建設、動態(tài)網(wǎng)站、用戶體驗
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)