- é¦å éè¦æ建两å°å·²ç»é 置好ç主ä»å¤å¶ççæ°æ®åºæå¡å¨ï¼æ¬æ¬¡æ¼ç¤ºç两å°æ°æ®åºåå«ä¸º192.168.9.174(主)å192.168.9.184(ä»)
-
å®è£ MySQLæ°æ®åºåæ建MySQL主ä»å¤å¶ç»æåè§
CentOSä¸å®è£ MySQL5.7(å¾æ)
ContOSä¸æ建MySQL主ä»å¤å¶
- åå¤æ°æ®è¡¨
DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `password` varchar(50) DEFAULT NULL, `birthday` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
- æ°å»ºä¸ä¸ªSpringBootå·¥ç¨springboot-mybatis-shardingjdbc
- 导å
¥ç¸å
³çmavenä¾èµåæ
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <!--shardingjdbcæ´åå --> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc</artifactId> <version>3.0.0.M3</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.5</version> <scope>provided</scope> </dependency> </dependencies>
- æ·»å mybatis-generatoræ件ï¼ç¨äºèªå¨çææ°æ®åºå¯¹åºç代ç
<plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.2</version> <configuration> <configurationFile>src/main/resources/generatorConfig.xml</configurationFile> <verbose>true</verbose> <overwrite>true</overwrite> </configuration> <executions> <execution> <id>Generate MyBatis Artifacts</id> <goals> <goal>generate</goal> </goals> </execution> </executions> <dependencies> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.2</version> </dependency> </dependencies> </plugin>
- å¨resources\mybatisä¸å建Mybatisé
ç½®æ件config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!--é ç½®å½åè§å--> <setting name="mapUnderscoreToCamelCase" value="true" /> </settings> <typeAliases> <typeAlias alias="Integer" type="java.lang.Integer" /> <typeAlias alias="Long" type="java.lang.Long" /> <typeAlias alias="HashMap" type="java.util.HashMap" /> <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" /> <typeAlias alias="ArrayList" type="java.util.ArrayList" /> <typeAlias alias="LinkedList" type="java.util.LinkedList" /> </typeAliases> </configuration>
- å¨resourcesä¸å建mybatis-generatoré
ç½®æ件generatorConfig.xml
<?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> <!--æ¬æºæ°æ®åºé©±å¨jarå åæ¾ç®å½--> <classPathEntry location="C:\Users\Administrator\.m2\repository\mysql\mysql-connector-java\5.1.42\mysql-connector-java-5.1.42.jar"/> <context id="DB2Tables" targetRuntime="MyBatis3"> <commentGenerator> <property name="suppressDate" value="true"/> <property name="suppressAllComments" value="true"/> </commentGenerator> <!--æ°æ®åºé©±å¨ï¼æ°æ®åºå°åå表åï¼è´¦å·ï¼å¯ç --> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://192.168.9.174:3306/test" userId="root" password="root"> </jdbcConnection> <javaTypeResolver> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!--çæModelç±»çå åååæ¾ä½ç½®--> <javaModelGenerator targetPackage="com.kangswx.springbootmybatisplusshardingjdbc.domain" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> <property name="trimStrings" value="true"/> </javaModelGenerator> <!--çææ å°æ件çå åååæ¾ä½ç½®--> <sqlMapGenerator targetPackage="com.kangswx.springbootmybatisplusshardingjdbc.configuration.mapper.mapping" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </sqlMapGenerator> <!--çæDaoç±»çå åååæ¾ä½ç½®--> <javaClientGenerator type="XMLMAPPER" targetPackage="com.kangswx.springbootmybatisplusshardingjdbc.configuration.mapper" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!--çæ对åºè¡¨åç±»å,domainObjectNameæ¯è®¾ç½®å®ä½ç±»çååç--> <table tableName="t_user" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table> </context> </generatorConfiguration>
- ä¿®æ¹applicationContext.xml为applicationContext.yml并添å ä¸é¢çé
置项
server: port: 8080 spring: datasource: type: com.alibaba.druid.pool.DruidDataSource application: name: springboot-mybatisplus-shardingjdbc sharding.jdbc: data-sources: ds_master: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.9.174:3306/test?useSSL=false #ä¸»åº username: root password: root ds_slave_0: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.9.184:3306/test?useSSL=false #ä»åº username: root password: root master-slave-rule: name: ds_ms master-data-source-name: ds_master slave-data-source-names: ds_slave_0 load-balance-algorithm-type: round_robin props: sql.show: true mybatis: config-location: classpath:mybatis/config.xml mapper-locations: - classpath:mapping/*.xml
- å¢ShardingJDBCçé
置类ShardingMasterSlaveConfig
package com.kangswx.springbootmybatisplusshardingjdbc.configuration.shardingjdbc; import com.alibaba.druid.pool.DruidDataSource; import io.shardingsphere.core.api.config.MasterSlaveRuleConfiguration; import org.springframework.boot.context.properties.ConfigurationProperties; import java.util.HashMap; import java.util.Map; @ConfigurationProperties(prefix ="sharding.jdbc") public class ShardingMasterSlaveConfig { private Map<String, DruidDataSource> dataSources = new HashMap<>(); private MasterSlaveRuleConfiguration masterSlaveRule; public Map<String, DruidDataSource> getDataSources() { return dataSources; } public void setDataSources(Map<String, DruidDataSource> dataSources) { this.dataSources = dataSources; } public MasterSlaveRuleConfiguration getMasterSlaveRule() { return masterSlaveRule; } public void setMasterSlaveRule(MasterSlaveRuleConfiguration masterSlaveRule) { this.masterSlaveRule = masterSlaveRule; } }
- æ°å¢ShardingJDBCçé
置类ShardingDataSourceConfig
package com.kangswx.springbootmybatisplusshardingjdbc.configuration.shardingjdbc; import com.alibaba.druid.pool.DruidDataSource; import com.google.common.collect.Maps; import io.shardingsphere.core.api.MasterSlaveDataSourceFactory; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; import org.springframework.boot.context.properties.EnableConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import java.util.Properties; @Configuration @EnableConfigurationProperties(ShardingMasterSlaveConfig.class) @ConditionalOnProperty({"sharding.jdbc.data-sources.ds_master.url", "sharding.jdbc.master-slave-rule.master-data-source-name"}) public class ShardingDataSourceConfig { private Logger logger = LoggerFactory.getLogger(ShardingDataSourceConfig.class); @Autowired(required = false) private ShardingMasterSlaveConfig shardingMasterSlaveConfig; @Bean("dataSource") public DataSource masterSlaveDataSource() throws SQLException { shardingMasterSlaveConfig.getDataSources().forEach((k, v) -> configDataSource(v)); Map<String, DataSource> dataSourceMap = Maps.newHashMap(); dataSourceMap.putAll(shardingMasterSlaveConfig.getDataSources()); DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, shardingMasterSlaveConfig.getMasterSlaveRule(), new HashMap<>(), new Properties()); logger.info("masterSlaveDataSource config complete"); return dataSource; } private void configDataSource(DruidDataSource druidDataSource) { druidDataSource.setMaxActive(20); druidDataSource.setInitialSize(1); druidDataSource.setMaxWait(60000); druidDataSource.setMinIdle(1); druidDataSource.setTimeBetweenEvictionRunsMillis(60000); druidDataSource.setMinEvictableIdleTimeMillis(300000); druidDataSource.setValidationQuery("select 'x'"); druidDataSource.setTestWhileIdle(true); druidDataSource.setTestOnBorrow(false); druidDataSource.setTestOnReturn(false); druidDataSource.setPoolPreparedStatements(true); druidDataSource.setMaxOpenPreparedStatements(20); druidDataSource.setUseGlobalDataSourceStat(true); try { druidDataSource.setFilters("stat,wall,slf4j"); } catch (SQLException e) { logger.error("druid configuration initialization filter", e); } } }
- å©ç¨mybatis-generatorèªå¨çææ°æ®åºå¯¹åºçMapperï¼mappingåå®ä½ç±»ï¼å¯¹äºä¸ç¥émybatis-generatorçå¯ä»¥åèSpringBootæ´åMybatisåMybatis-generatorå®ç°ä»£ç èªå¨çæ
- å®ä½ç±»ä»£ç
package com.kangswx.springbootmybatisplusshardingjdbc.domain; import java.util.Date; public class User { private Integer id; private String username; private String password; private Date birthday; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username == null ? null : username.trim(); } public String getPassword() { return password; } public void setPassword(String password) { this.password = password == null ? null : password.trim(); } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } }
- Mapper代ç
package com.kangswx.springbootmybatisplusshardingjdbc.mapper; import com.kangswx.springbootmybatisplusshardingjdbc.domain.User; import org.apache.ibatis.annotations.Mapper; import org.springframework.stereotype.Repository; @Repository @Mapper public interface UserMapper { int deleteByPrimaryKey(Integer id); int insert(User record); int insertSelective(User record); User selectByPrimaryKey(Integer id); int updateByPrimaryKeySelective(User record); int updateByPrimaryKey(User record); }
- mapping代ç
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.kangswx.springbootmybatisplusshardingjdbc.mapper.UserMapper" > <resultMap id="BaseResultMap" type="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="username" property="username" jdbcType="VARCHAR" /> <result column="password" property="password" jdbcType="VARCHAR" /> <result column="birthday" property="birthday" jdbcType="TIMESTAMP" /> </resultMap> <sql id="Base_Column_List" > id, username, password, birthday </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from t_user where id = #{id,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from t_user where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" > insert into t_user (id, username, password, birthday) values (#{id,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{birthday,jdbcType=TIMESTAMP}) </insert> <insert id="insertSelective" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" > insert into t_user <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null" > id, </if> <if test="username != null" > username, </if> <if test="password != null" > password, </if> <if test="birthday != null" > birthday, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="id != null" > #{id,jdbcType=INTEGER}, </if> <if test="username != null" > #{username,jdbcType=VARCHAR}, </if> <if test="password != null" > #{password,jdbcType=VARCHAR}, </if> <if test="birthday != null" > #{birthday,jdbcType=TIMESTAMP}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" > update t_user <set > <if test="username != null" > username = #{username,jdbcType=VARCHAR}, </if> <if test="password != null" > password = #{password,jdbcType=VARCHAR}, </if> <if test="birthday != null" > birthday = #{birthday,jdbcType=TIMESTAMP}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" > update t_user set username = #{username,jdbcType=VARCHAR}, password = #{password,jdbcType=VARCHAR}, birthday = #{birthday,jdbcType=TIMESTAMP} where id = #{id,jdbcType=INTEGER} </update> </mapper>
- æ°å¢UserServiceæ¥å£
package com.kangswx.springbootmybatisplusshardingjdbc.service; import com.kangswx.springbootmybatisplusshardingjdbc.domain.User; public interface UserService { int addUser(User user); User findById(Integer id); }
- æ°å¢UserServiceçå®ç°ç±»
package com.kangswx.springbootmybatisplusshardingjdbc.service.impl; import com.kangswx.springbootmybatisplusshardingjdbc.domain.User; import com.kangswx.springbootmybatisplusshardingjdbc.mapper.UserMapper; import com.kangswx.springbootmybatisplusshardingjdbc.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public int addUser(User user) { return userMapper.insert(user); } @Override public User findById(Integer id) { return userMapper.selectByPrimaryKey(id); } }
- å¨SpringBootçå¯å¨ç±»ä¸é¢éè¦æ·»å ä¸é¢ç注解
- ç¼åUserServiceImplçæµè¯ç±»UserServiceImplTest
package com.kangswx.springbootmybatisplusshardingjdbc.service.impl; import com.kangswx.springbootmybatisplusshardingjdbc.domain.User; import com.kangswx.springbootmybatisplusshardingjdbc.service.UserService; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.text.SimpleDateFormat; import java.util.Date; @RunWith(SpringRunner.class) @SpringBootTest public class UserServiceImplTest { @Autowired private UserService userService; /** * 读åå离åå ¥æµè¯ */ @Test public void addUser() { User user = new User(); user.setUsername("å¼ ä¸--+++"); user.setPassword("1234569090"); Date date = new Date(); user.setBirthday(date); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM--dd HH:mm:ss"); System.out.println(sdf.format(date)); int ret = userService.addUser(user); System.out.println("ret: "+ret); } /** * 读åå离读åæµè¯ */ @Test public void findById() { User user = userService.findById(20); System.out.println(user.getUsername() + "==" + user.getPassword()); } }
- æµè¯éè¦æ³¨æçç¹ï¼å 为ä¹å两å°æå¡å¨å·²ç»æ建为主ä»ç»æäºï¼æ以å¨è¯»åååå ¥çæ¶åä¸ç¥éæ¯ä»åªå°æ°æ®åºä¸é¢è¿è¡çï¼å¯ä»¥åæ¾ä¸å°åç¬çæå¡å¨æ¨¡æä»æ°æ®åºï¼å¨åå ¥çæ¶ååç°æ°æ®æåæå ¥å°äºä¸»ä»ä¸¤å°æ°æ®åºæå¡å¨ï¼é ç½®ä»åºä¸ºåç¬çæå¡å¨ï¼å¨æ¥è¯¢çæ¶åæ¾ä¸æ¡å¨ä¸»åºä¸æ²¡æçæ°æ®è¿è¡æ¥è¯¢ï¼å¦ææ°æ®è½æåæ¥è¯¢ï¼å说æ读ååçæä½åçå¨ä¸åçæå¡å¨ä¸é¢ï¼è³æ¤è¯»åå离æåå®ç°ã
- ä¸é¢ææç代ç å¯åè§ SpringBootæ´åMybatisåShardingJDBCå®ç°è¯»åå离