We are all using JPA, Mybatis to do database links, here to share a more excellent high-performance combination.

We use https://start.spring.io/ to generate the base project and select the necessary components to download.

HikariCP

Choosing a good database connection pool is critical to database access. Spring Boot comes with a HikariCP database connection pool and recommends using HikariCP first. You can verify the Spring Boot default support from the following figure.

Install HikariCP

Java 8 thru 11 maven artifact:

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>Rule 3.4.3</version>
    </dependency>
Copy the code

Java 7 maven artifact (maintenance mode):

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

Java 6 maven artifact (maintenance mode):

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

BeetlSQL

BeetSql is a fully functional DAO tool, with Hibernate advantages & Mybatis advantages function, suitable for recognizing SQL as the center, but also demand tools can automatically generate a large number of commonly used SQL applications. The figure below compares common ORM frameworks from different latitudes.

The installation

Maven:

<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>
Copy the code

ibeetl.com/guide/#/bee…

The preparatory work

Create the library table structure

Use the following statements to create the database and table structures

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;
Copy the code

Install required 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>
Copy the code

Multi-data Source Configuration

We adopted the method recommended by Spring’s official website

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

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

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

	@Bean
	@ConfigurationProperties("app.datasource.second.configuration")
	public HikariDataSource secondDataSource(a) {
		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});
		returnsource; }}Copy the code

The configuration of application.properties is as follows

app.datasource.first.url=jdbc:mysql://localhost:3306/first? useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true&allowPublicKeyRetr ieval=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&allowPublicKeyRetr ieval=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=trueCopy the code

Paging query

Paging query is a feature that we often use. BeetlSql supports multiple data and will automatically adapt the current database to generate paging statements, and call the limit method in beeltSql for paging.

If you want to use a paging query directly and get the total number of rows, you can call the Page method at the end and return a PageQuery object. Note that page, like SELECT, is called at the end of the call. Do not repeat select, page, update, delete, etc.

LambdaQuery<User> query = userDao.createLambdaQuery();
PageQuery<User> page = query.page(1.2);
System.out.println(page.getTotalRow());
System.out.println(page.getList());
Copy the code

Paging has never been so smooth

UserDao inherited org. Beetl. SQL. Core. Mapper. BaseMapper

Functional verification

We use unit tests to verify the functionality as shown below

@Test
public void testSaveData(a) {
	List<User> userList = new ArrayList<>();
	for (int i = 0; i < 100; i++) {
		User u = new User();
		u.setAge(102);
		u.setName("Exception of a programming ape -master-" + i);
		u.setEmail("[email protected]");
		userList.add(u);
	}
	userDao.insertBatch(userList);
}
Copy the code

We then query the first library, and the data is inserted normally

At this time, we went to second to query and found that the data did not exist

Insert data in second with the script and query again. The data already exists

Beetl-framework-starter Default description

  • Beetlsql. sqlPath, which is the root directory for storing SQL files, is located in the /resources/ SQL directory
  • beetlsql.nameConversion: Default is org. Beetl. SQL. Core. UnderlinedNameConversion, will underline segmentation database naming style into Java hump naming style, and the commonly used DefaultNameConversion, database name and Java naming has been completely, And JPA2NameConversion, compatible with JPA naming
  • Beetl-beetlsql. dev: the default value is true, which is to output the SQL, parameters, execution time, and execution location to the console. The system automatically detects the SQL file modification every time the SQL file is modified.
  • Beetlsql. DaoSuffix: The default is Dao.
  • Beetlsql. basePackage: The default is com. Beetlsql. daoSuffix is configured to automatically scan all Dao ending Mapper classes in com packages and subpackages. For the example in this chapter, you can configure “com.bee.sample.ch5.dao”.
  • Beetlsql. DbStyle: database style, default is org. Beetl. SQL. Core. The MySqlStyle. Corresponding to different databases, and other OracleStyle, PostgresStyle, SqlServerStyle, DB2SqlStyle, SQLiteStyle, H2Style

BeetlSQL is powerful, find out the rest for yourself

Follow the public to reply to BTS for the complete code

Follow the public to reply to BTS for the complete code

—-END—