Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

Time:2020-11-24

We are all using JPA and mybatis as database links. Here we share a more excellent high-performance combination.

We usehttps://start.spring.io/Generate the basic project, select the necessary components to download.

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

HikariCP

Choosing a good database connection pool is very important for database access. Spring boot comes with hikaricp database connection pool, and it is recommended to use hikaricp first. It can be verified from the following figure by default.

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

Installing hikaricp

Java 8 thru 11 maven artifact:

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>3.4.3</version>
    </dependency>

Java 7 maven artifact (maintenance mode):

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP-java7</artifactId>
        <version>2.4.13</version>
    </dependency>

Java 6 maven artifact (maintenance mode):

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP-java6</artifactId>
        <version>2.3.13</version>
    </dependency>

BeetlSQL

Beetsql is a full-featured Dao tool with hibernate advantages & mybatis advantages. It is suitable for applications that recognize SQL as the center and require tools to automatically generate a large number of commonly used SQL. The following figure compares common ORM frameworks from different latitudes.

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

install

Maven method:

<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetlsql</artifactId>
    <version>2.11.0</version>
</dependency>
<dependency>
  <groupId>com.ibeetl</groupId>
  <artifactId>beetl</artifactId>
  < version > ${latest version} < / version >
</dependency>

Introduction to official website http://ibeetl.com/guide/#/beetlsql/

preparation

Create library table structure

Use the following statement to create the database and table structure

CREATE DATABASE first;
USE first;
CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(64) DEFAULT NULL,
      `email` varchar(64) DEFAULT NULL,
      `age` int(4) DEFAULT NULL,
      `create_date` datetime NULL DEFAULT NULL,
      `update_date` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE second;
USE second;
CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(64) DEFAULT NULL,
      `email` varchar(64) DEFAULT NULL,
      `age` int(4) DEFAULT NULL,
      `create_date` datetime NULL DEFAULT NULL,
      `update_date` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Install necessary dependencies

<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetlsql</artifactId>
    <version>2.12.28.RELEASE</version>
</dependency>
<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetl</artifactId>
    <version>3.1.3.RELEASE</version>
</dependency>

Multi data source configuration

We adopt the way recommended by spring official website

@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.first")
    public DataSourceProperties firstDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.first.configuration")
    public HikariDataSource firstDataSource() {
        return firstDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("app.datasource.second")
    public DataSourceProperties secondDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("app.datasource.second.configuration")
    public HikariDataSource secondDataSource() {
        return secondDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
    @Bean
    public BeetlSqlDataSource beetlSqlDataSource(@Qualifier("firstDataSource") DataSource firstDataSource,
            @Qualifier("secondDataSource") DataSource secondDataSource) {
        BeetlSqlDataSource source = new BeetlSqlDataSource();
        source.setMasterSource(firstDataSource);
        source.setSlaves(new DataSource[]{secondDataSource});
        return source;
    }
}

application.propertiesThe configuration is as follows

app.datasource.first.url=jdbc:mysql://localhost:3306/first?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true&allowPublicKeyRetrieval=true
app.datasource.first.username=root
app.datasource.first.password=root

app.datasource.first.configuration.minimum-idle=10
app.datasource.first.configuration.maximum-pool-size=100
app.datasource.first.configuration.connection-timeout=8000
app.datasource.first.configuration.leak-detection-threshold=60000
app.datasource.first.configuration.connection-test-query=SELECT 1

app.datasource.second.url=jdbc:mysql://localhost:3306/second?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true&allowPublicKeyRetrieval=true
app.datasource.second.username=root
app.datasource.second.password=root

app.datasource.second.configuration.minimum-idle=10
app.datasource.second.configuration.maximum-pool-size=100
app.datasource.second.configuration.connection-timeout=8000
app.datasource.second.configuration.leak-detection-threshold=60000
app.datasource.second.configuration.read-only=true

Paging query

Paging query is a function we often use. BeetlSql supports multiple data, automatically adaptable the current database to generate paging statements, and calls limit method in beeltSql to pagination.

If you want to use the paging query directly and get the total number of rows at the same time, you can call the page method at the end and return a PageQuery object.  Note that page, like select, is called at the end, and cannot repeatedly call select, page, update, delete and so onThe usage is as follows:

LambdaQuery<User> query = userDao.createLambdaQuery();
PageQuery<User> page = query.page(1, 2);
System.out.println(page.getTotalRow());
System.out.println(page.getList());

Pagination has never been so smooth~

Userdao inherits org.beetl.sql . core.mapper.BaseMapper <User>

functional verification

We use unit tests to verify the functionality. The code is as follows

@Test
public void testSaveData() {
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        User u = new User();
        u.setAge(102);
        u. Setname ("exception master -" + I) of a program;
        u.setEmail("[email protected]");
        userList.add(u);
    }
    userDao.insertBatch(userList);
}

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

Then we checkfirstLibrary, data is inserted normally

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

Now let’s gosecondThe query found that the data does not exist

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

We use scripts in thesecondInsert the data and query again. You will find that the data already exists

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

Default description of Beetl framework starter

  • beetlsql.sqlPath , which is / SQL by default. As the root directory for storing SQL files, it is located in the / resources / SQL directory
  • beetlsql.nameConversion : the default is org.beetl.sql . core.UnderlinedNameConversion , which can convert the database naming style of underline segmentation into Java hump naming style, as well as the commonly used default name conversion. Database naming is completely consistent with Java Naming, and jpa2nameconversion is compatible with JPA naming
  • beetl- beetlsql.dev : the default value is true, that is, the SQL, parameters, execution time, and execution location are output to the console. Each time the SQL file is modified, the SQL file modification is automatically detected
  • beetlsql.daoSuffix : Dao by default.
  • beetlsql.basePackage : the default is com. This option is configured beetlsql.daoSuffix To automatically scan all mapper classes ending with Dao in com package and its sub packages. For example in this chapter, you can configure“ com.bee.sample .ch5.dao”
  • beetlsql.dbStyle : database style, default is org.beetl.sql . core.db.MySqlStyle . corresponding to different databases, others include Oracle style, postgresstyle, sqlserverstyle, db2sqlstyle, sqlitestyle, and h2style

Beetlql is powerful, and find out the rest

Pay attention to official account replyBTSGet full code

Spring boot + hikaricp + beetlql high performance combination, elegant implementation of multiple data sources and paging

Pay attention to official account replyBTSGet full code

—-END—