Introduction to the

In the previous article, we set up the running environment locally to experience the three functions of ShardingSphere JDBC: library and table, read and write separation, and data encryption

The sample run

To get the concept straight, refer to the following documentation:

  • Data fragmentation
  • Reading and writing separation
  • Data encryption

Reference instructions for configuration are also to be read, refer to the following documentation:

  • Data Fragment Configuration
  • Read/write Separation Configuration
  • Data Encryption Configuration

Examples /shardingsphere-jdbc-example/sharding-example/sharding-spring-boot-mybatis-example

Separate library and table from read and write

1. Initialize the database

First of all, the relevant data fragmentation and read and write separation of the required tables in the database

Mysql > select * from docker where username and password are root;

docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:latest
Copy the code

Run the following SQL statement to create the associated database:

CREATE SCHEMA IF NOT EXISTS demo_write_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_write_ds_0_read_0;
CREATE SCHEMA IF NOT EXISTS demo_write_ds_0_read_1;
CREATE SCHEMA IF NOT EXISTS demo_write_ds_1;
CREATE SCHEMA IF NOT EXISTS demo_write_ds_1_read_0;
CREATE SCHEMA IF NOT EXISTS demo_write_ds_1_read_1;
Copy the code

2. Modify the configuration

Modify the first: examples/shardingsphere-jdbc-example/sharding-example/sharding-spring-boot-mybatis-example/src/main/resources/applicatio n.properties

mybatis.config-location=classpath:META-INF/mybatis-config.xml

#spring.profiles.active=sharding-databases
#spring.profiles.active=sharding-tables
#spring.profiles.active=sharding-databases-tables
#spring.profiles.active=readwrite-splitting
spring.profiles.active=sharding-readwrite-splittin
Copy the code

Modify its configuration file to perform read/write split plus sharding

Revise: examples/shardingsphere-jdbc-example/sharding-example/sharding-spring-boot-mybatis-example/src/main/resources/applicatio n-sharding-readwrite-splitting.properties

Simply change the database password and add the following configuration at the end to print the SQL statement:

spring.shardingsphere.props.sql-show=true
Copy the code

3. Run

After running, it will look like the picture below:

Insert statements and query statements are printed as follows:

---------------------------- Insert Data ---------------------------- Logic SQL: INSERT INTO t_order (user_id, address_id, status) VALUES (? ,? ,?) ; SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty) Actual SQL: write-ds-1 ::: INSERT INTO t_order_0 (user_id, address_id, status, order_id) VALUES (? ,? ,? ,?) ; ::: [1, 1, INSERT_TEST, 636678694781825024] Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (? ,? ,?) ; SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty) Actual SQL: write-ds-1 ::: INSERT INTO t_order_item_0 (order_id, user_id, status, order_item_id) VALUES (? ,? ,? ,?) ; ::: [636678694781825024, 1, INSERT_TEST, 636678695339667457] SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty) Actual SQL: write-ds-0 ::: INSERT INTO t_order_0 (user_id, address_id, status, order_id) VALUES (? ,? ,? ,?) ; ::: [2, 2, INSERT_TEST, 636678695436136448] Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (? ,? ,?) ;Copy the code

The insert statement is roughly as shown above, and as you can see from Actual SQL, the data generation insert is indeed fragmented

But with read-write separation, it’s not so sure:

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Print Order Data -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- [INFO] 21:33:50 2021-08-23, 057, the main [ShardingSphere-SQL] Logic SQL: SELECT * FROM t_order; [INFO] 2021-08-23 21:33:50,057 --main-- [shardingsphere-sql] SQLStatement MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, Window =Optional. Empty) [INFO] 2021-08-23 21:33:50,057 --main-- [shardingsphere-sql] Actual SQL: write-ds-0 :: SELECT * FROM t_order_0 ORDER BY order_id ASC ; [INFO] 2021-08-23 21:33:50,057 --main-- [shardingsphere-SQL] Actual SQL: read-DS-0 :: SELECT * FROM t_order_1 ORDER BY order_id ASC ; [INFO] 2021-08-23 21:33:50,057 --main-- [shardingsphere-SQL] Actual SQL: read-DS-1 :: SELECT * FROM t_order_0 ORDER BY order_id ASC ; [INFO] 2021-08-23 21:33:50,057 --main-- [shardingsphere-SQL] Actual SQL: read-DS-1 :: SELECT * FROM t_order_1 ORDER BY order_id ASC ;Copy the code

Write ds-0-read-0,write ds-0-read-1,write ds-1-read-0,write ds-1-read-1,write ds-1-read-1, and write ds-1-read-1

I ran a separate read/write separation example configuration again, but the statement was the same as above, with no obvious read/slave library

There are several possible feelings:

  • 1. The read library has been removed, only the printed statement is set like this
  • 2. The database requires additional configuration

This will be studied at…..

Data encryption

Examples /shardingsphere-jdbc-example/governance-example/governance-spring-boot-mybatis-example

1. Database establishment

CREATE SCHEMA IF NOT EXISTS demo_ds;
Copy the code

2. Modify the configuration

Modify the configuration: examples/shardingsphere-jdbc-example/governance-example/governance-spring-boot-mybatis-example/src/main/resources/applic ation.properties

spring.profiles.active=local-zookeeper-encryp
Copy the code

Modify the configuration: examples/shardingsphere-jdbc-example/governance-example/governance-spring-boot-mybatis-example/src/main/resources/applic ation-local-zookeeper-encrypt.properties

Change the database password

3. Run

The sample will clean up the data after it runs, starting with the code to clean up the relevant database:

examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/ExampleExecuteTemplate.java

public final class ExampleExecuteTemplate {
    
    public static void run(final ExampleService exampleService) throws SQLException {
        try {
            exampleService.initEnvironment();
            exampleService.processSuccess();
        } finally {
	    / / comment out
// exampleService.cleanEnvironment();}}public static void runFailure(final ExampleService exampleService) throws SQLException {
        try {
            exampleService.initEnvironment();
            exampleService.processFailure();
        } finally{ exampleService.cleanEnvironment(); }}}Copy the code

examples/example-core/example-spring-mybatis/src/main/java/org/apache/shardingsphere/example/core/mybatis/service/OrderS erviceImpl.java

@Service
@Primary
public class OrderServiceImpl implements ExampleService {...@Override
    @Transactional
    public void processSuccess(a) throws SQLException {
        System.out.println("-------------- Process Success Begin ---------------");
        List<Long> orderIds = insertData();
        printData();
	/ / comment out
// deleteData(orderIds);
        printData();
        System.out.println("-------------- Process Success Finish --------------"); }... }Copy the code

Run, you see a lot of logs, from the above mentioned SQL and so on

Above, we closed the data cleaning code related to order. Let’s take a look at the fields in the database, which are roughly as follows. The last field can be seen to be encrypted

conclusion

This file runs the source code example of data sharding (sub-library sub-table), read and write separation, data encryption related

Among them, database sharding and data encryption meet our initial expectations, but read and write separation is a little strange, a little less expected, this will be studied later