Sharding JDBC operations are divided into configuration and use, read and write separation, database table and application, etc. Today we will mainly learn about the database table operation, if you are interested in it, let’s start.
Environment to prepare
pom.xml
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
< version > 2.1.3. RELEASE < / version > < / parent >
<properties>
< Java version > 1.8 < / Java. Version >
< sharding version > 3.1.0 < / sharding version > < / properties >
<dependencies>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding.version}</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
The < version > 1.1.10 < / version >
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
The < version > 3.4.5 < / version >
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
The < version > 1.3.1 < / version >
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
The < version > 5.1.46 < / version >
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency></dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins></build>
domain
// create domain@Setter@Getter@ToString@NoArgsConstructor@AllArgsConstructorpublic class Employee {
private Long id;
private String name; }
The configuration class
@SpringBootApplication@MapperScan(“cn.wolfcode.sharding.mapper”)public class ShardingApplication { }
Depots table
Case model
Build table
Select * from employee_0; select * from employee_1; select * from employee_1; create TABLE ’employee_0′ (
`id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; — ###################################CREATE TABLE `employee_1` (
`id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
application.properties
# define the connection pool
sharding.jdbc.datasource.names=db0,db1
Format of # sharding.. JDBC datasource. The connection pool. XXX: set 4 elements of information
sharding.jdbc.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db0.url=jdbc:mysql://db0Ip:port/sharing
sharding.jdbc.datasource.db0.username=xxx
sharding.jdbc.datasource.db0.password=xxx
sharding.jdbc.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db1.url=jdbc:mysql://db1Ip:port/sharing
sharding.jdbc.datasource.db1.username=xxx
sharding.jdbc.datasource.db1.password=xxx
# set branch rules
# sharding. JDBC. Config. Sharding. Default – database – strategy. The inline. Sharding – column: depots
# sharding. JDBC. Config. Sharding. Default – database – strategy. The inline. Algorithm – expression: depots algorithm
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 2}
# bind logical table
sharding.jdbc.config.sharding.binding-tables=employee
Set table rules
# sharding.jdbc.config.sharding.tables. Actual -data-nodes: indicates the actual table corresponding to a logical table
# sharding.jdbc.config.sharding.tables. Table – strategy.inline-sharding -column: indicates a sub-table column
# sharding.jdbc.config.sharding.tables. Logical table. The table – strategy. The inline. Algorithm – expression: table algorithm
# sharding.jdbc.config.sharding.tables. Key-generator-column-name: indicates the primary key column
sharding.jdbc.config.sharding.tables.employee.actual-data-nodes=db$->{0.. 1}.employee_$->{0.. 1}
sharding.jdbc.config.sharding.tables.employee.table-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.employee.table-strategy.inline.algorithm-expression=employee_$->{id % 2}
sharding.jdbc.config.sharding.tables.employee.key-generator-column-name=id
# print log
sharding.jdbc.config.props.sql.show=true
mapper
/ * *
* The Employee table written here is the logical table configured above
* The underlying layer will rewrite our logical table to the real table in the database according to the sharding rules
*/@Mapperpublic interface EmployeeMapper {
@Select(“select * from employee”)
List<Employee> selectAll();
@Insert(“insert into employee (name) values (#{name})”)
void inser(Employee entity); }
test
@RunWith(SpringRunner.class)@SpringBootTest(classes=ShardingApplication.class)public class ShardingApplicationTests {
@Autowired
private EmployeeMapper employeeMapper;
@Test
public void save() {
for (int i = 0; i < 10; i++) {
Employee employee = new Employee();
employee.setName(“xx”+i);
employeeMapper.inser(employee);
}
}
@Test
public void list() {
employeeMapper.selectAll().forEach(System.out::println);
}}
The advantages and disadvantages
,
.
,
.
,
.
,
DB