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

Sharding-JDBC automatically achieve MySQL read-write separation of sample code how to write, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

I. Overview of ShardingSphere and Sharding-JDBC 1.1, introduction to ShardingSphere

Before introducing Sharding-JDBC, it is necessary to introduce ShardingSphere, a large family of Sharding-JDBC. After introducing ShardingSphere, I believe you will have a better understanding of the overall architecture of ShardingSphere and the role played by Sharding-JDBC.

ShardingSphere was planned later, and at first only Sharding-JDBC was the only product, based on the client-side form of sub-database and sub-table. The later development has become the current Apache ShardingSphere (Incubator), which is an ecological circle of open source distributed database middleware solutions, which is made up of Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar (planned), which are independent of each other but can be deployed together. They all provide standardized data sharding, distributed transaction and database governance functions, and can be applied to a variety of application scenarios, such as Java isomorphism, heterogeneous languages, containers, cloud natives, and so on.

ShardingSphere is positioned as relational database middleware, which aims to make full use of the computing and storage capacity of relational database in distributed scenarios, but not to achieve a new relational database. It is coexisting rather than mutually exclusive with NoSQL and NewSQL. As the forefront of new technology exploration, NoSQL and NewSQL are highly recommended for looking to the future and embracing change. On the contrary, we can also look at the problem in another way, look to the future, pay attention to the same things, and then grasp the essence of things. Relational database still occupies a huge market today, and it is the cornerstone of each company's core business, and it is difficult to shake in the future. at this stage, we pay more attention to the increment on the original basis rather than subversion.

1.2. introduction to Sharding-JDBC

Positioned as a lightweight Java framework, additional services provided at the JDBC layer of Java. It uses a client-side directly connected database and provides services in the form of jar packages without additional deployment and dependency. It can be understood as an enhanced version of the JDBC driver, fully compatible with JDBC and various ORM frameworks.

Suitable for any Java-based ORM framework, such as: JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.

Based on any third-party database connection pool, such as: DBCP, C3P0, BoneCP, Druid, HikariCP, etc.

Supports any database that implements the JDBC specification. MySQL,Oracle,SQLServer and PostgreSQL are currently supported.

1.3.The effect of Sharding-JDBC

1.4.The schematic diagram of ShardingSphere

1.5. the difference between the three products of ShardingSphere

II. Database middleware

The main function of read-write separation database middleware is to transparent the impact of read-write separation, allowing users to use master-slave database as much as possible like using a database.

2.1. Brief introduction of database middleware

Database middleware can simplify the operation of read-write separation and sub-database sub-table, and hide the underlying implementation details. It can operate multi-database and multi-table like a single database and single table. There are two main design schemes:

Server agent: a proxy service needs to be deployed independently, which manages multiple database instances, establishes a connection with the proxy server through a data source in the application, and the agent operates the underlying database and returns the corresponding results. The advantage is that it supports multiple languages and is transparent to the business, while the disadvantage is that it is complex and difficult to implement, and the agent needs to ensure its own high availability.

Client agent: encapsulates the connection pool or database driver in a layer, establishes internal connections with different databases, and performs necessary operations on SQL, such as separation of reads and writes, selection of master or slave libraries, and how to aggregate results after sub-database and sub-table select. The advantages are simple implementation and natural decentralization, while the disadvantage is that there are few languages to support and the version upgrade is difficult.

Some common database middleware are as follows:

Cobar: Ali's open source relational database distributed service middleware, which has been changed

DRDS: born in Cobar, the full name of distributed relational database service

MyCat: open source database middleware, currently updated MyCat2 version

A data middle tier project based on MySQL protocol developed and maintained by the infrastructure team of Web platform Department of Atlas:Qihoo 360. there is also a version of NoSQL called Pika.

Tddl: a distributed database service independently developed by Alibaba

A sub-product of Sharding-JDBC:ShardingShpere, a lightweight Java framework

2.2, difference between Sharding-JDBC and MyCat

1) mycat is a third-party application of middleware, and sharding-jdbc is a jar package

2) you don't need to change the code when using mycat, but you need to modify the code when using sharding-jdbc

Mycat (proxy middleware layer):

Sharding-jdbc (application layer represented by TDDL):

It can be seen that sharding-jdbc is integrated within the application as a component, while mycat as a separate application needs to be deployed separately. From the architecture point of view, sharding-jdbc is more in line with the design of distributed architecture, directly connected to the database, there is no intermediate application, the theoretical performance is the highest (the actual performance needs to be combined with specific code implementation, the theoretical performance can be understood as the upper limit, by constantly optimizing the code implementation, gradually close to the theoretical performance). At the same time, the disadvantage is obvious, because as a component, it needs to be integrated into the application, which means that as the user, it must be integrated into the code, which makes the development cost relatively high; on the other hand, because it needs to be integrated into the application, it needs to be aimed at different languages (java, C, PHP). There are different implementations (in fact, sharding-jdbc currently only supports Java), so the maintenance cost of the component itself can be high. Finally, the application scenario is limited to the application developed by Java.

Compared with MyCat, I think the biggest advantage of Sharding-JDBC is that sharding-jdbc is a lightweight third-party tool, directly connected to the database, there is no intermediate application, we only need to reference the specified jar package in the project, and then configure the rules and methods of sub-database and sub-table or read-write separation according to the business needs of the project.

3. Sharding-JDBC+MyBatisPlus implements read-write separation, project code structure and table-building SQL statements.

(1) Project code structure

(2) create table SQL statement

DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (`user_ id` int (11) NOT NULL AUTO_INCREMENT, `nickt` varchar (45) NOT NULL, `nickname` varchar (18) NOT NULL, `password` varchar (45) NOT NULL, `headimage_ url` varchar (45) DEFAULT NULL, `introduce`varchar (45) DEFAULT NULL, PRIMARY KEY (`user_ id`), UNIQUE KEY `account_ UNIQUE` (`nickt`), UNIQUE KEY `nickname_ UNIQUE` (`nickname`) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 3.1 、 Introduction of Maven dependency org.apache.shardingsphere sharding-jdbc-core 4.1.1 com.baomidou mybatis-plus-boot-starter 3.4.2 mysql mysql-connector-java Org.springframework.boot spring-boot-starter-web org.projectlombok lombok 1.18.4 provided 3.2 、 Yml file configuration

In Spring Boot 2.x, the choice of data sources is also in line with the trend, and HikariCP with the best performance is adopted by default.

Spring: shardingsphere: datasource: names: master Slave # data source name master: type: com.zaxxer.hikari.HikariDataSource # connection pool driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://xxxxxx:3306/test?allowMultiQueries=true&useSSL=false&useUnicode=true&characterEncoding=utf-8 # main library address username: root password: xxxxxx Hikari: maximum-pool-size: 20 # maximum number of connections minimum-idle: 10 # minimum idle connections max-lifetime: 0 # maximum life cycle 0 does not expire. Not equal to 0 and less than 30 seconds, will be reset to the default value of 30 minutes. The setting should be shorter than the timeout set by mysql idle-timeout: 30000 # idle connection timeout Default value 600000 (10 minutes) connection-timeout: 60000 # connection timeout data-source-properties: prepStmtCacheSize: 250 prepStmtCacheSqlLimit: 2048 cachePrepStmts: true useServerPrepStmts: true slave: type: com.zaxxer.hikari.HikariDataSource # connection pool driver-class-name: com.mysql. Cj.jdbc.Driver jdbc-url: jdbc:mysql://xxxxxx:3306/test?allowMultiQueries=true&useSSL=false&useUnicode=true&characterEncoding=utf-8 # from the library address username: root password: xxxxxx hikari: maximum-pool-size: 20 minimum-idle: 10 max-lifetime: 0 idle-timeout: 30000 connection-timeout: 60000 data-source-properties: PrepStmtCacheSize: 250prepStmtCacheSqlLimit: 2048 cachePrepStmts: true useServerPrepStmts: true masterslave: load-balance-algorithm-type: round_robin # load balancing algorithm Used to configure the type of slave load balancing algorithm. Available value: ROUND_ROBIN (polling) RANDOM (random) name: ms # final data source name master-data-source-name: master # main library data source name slave-data-source-names: slave # from the list of database data source names Multiple commas separated props: sql: show: true # when executing SQL, SQL is printed and the name of the execution library is displayed. Default false3.3, UserEntity entity class package com.hs.sharingjdbc.entity Import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data @ Data// defines the table name: when the database name is inconsistent with the entity class name or does not conform to the hump name, you need to specify the table name @ TableName (value = "user") public class UserEntity {/ / specify the primary key self-increasing policy: value is the same as the database primary key column name. If the entity class attribute name is consistent with the table primary key column name, you can omit value @ TableId (type = IdType.AUTO) private Integer user_id; private String account; private String nickname. Private String password; private String headimage_url; private String introduce;} 3.4, UserMapper interface

The written interface needs to inherit the BaseMapper interface. The source code of this interface defines some general methods for operating the database. Most of the CRUD operations in a single table can be done directly, which is much more efficient than the native MyBatis.

Package com.hs.sharingjdbc.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.hs.sharingjdbc.entity.UserEntity; public interface UserMapper extends BaseMapper {/ / int insert (T entity); / int deleteById (Serializable id); / int deleteByMap (@ Param ("cm") Map columnMap); / int delete (@ Param ("ew") Wrapper queryWrapper); / int deleteBatchIds (@ Param ("coll") Collection

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.