This paper introduces how to configure multiple data sources to connect to the database, set the secondary cache function of sql text, and configure multiple data sources and data connection pool in MyBatis project. For everyone to develop and practice at ordinary times to provide reference and query tool articles.
Code download Baidu online disk download: https://pan.baidu.com/s/1Q6pbC2SR70HKCeVXgOGUPQ
Extraction code: 1234
Download resources from this site
Springboot + MyBatis introductory training 1 project running environment configuration
Springboot + MyBatis introductory training 2 addition, modification, deletion and query in like foreach
MyBatis multi data source settings
In actual project development, we usually encounter the situation that business data is saved in different databases, and we need to operate multiple data databases for business operations. In MyBati, multiple data sources can be used to connect to the database at the same time. The connection of multiple data sources can be configured by importing the dynamic datasource plug-in in the project. It is very simple to use and has high operation and maintenance. Dynamic datasource can be encapsulated and introduced in Spring Starter mode. It supports Mybatis read-write separation and dynamic data source switching through annotations.
pom.xml
<!--Multiple data sources require--> <dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <version>3.3.6</version> </dependency>
spring boot application.yml text configuration
The only technical difficulty in configuring multiple data sources is configuration. It is very easy to call when the configuration is correct.
Structure of data source in yml file
- datasource: data source root node
- dynamic: multi data source configuration header node
- primary: default master data name
- datasource: data node
- master: default node
- Child node: Custom node name
Configuration level
spring: spring Top level tag
datasource: 1 level
dynamic: 2 level
primary: master 3 level
strict: false 3 level
datasource: 3 level
master: 4 level
Child node name: 4 level
Find the application. In the directory YML configuration file, add the following content to application In the YML file.
- The data source configuration should be established under the spring node
- Configure database connection contents under the default node and sub database node
Configure two data sources. In the project, you can use MyBatis to access these two databases for sql operation.
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
master:
url: jdbc:mysql://localhost:3306/db1 # database I
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
zht:
url: jdbc:mysql://localhost:3306/db2 # database II
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
It supports configuring data connections of different databases in the project at the same time
- oracle Database
- mysql database
- postgresql database
- SQL Server database
- h2 database
Multi database configuration
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
master:
url: jdbc:mysql://localhost:3306/db1 #mysql
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
zht:
url: jdbc:oracle:thin:@localhost:1521:orcl #oracle
username: root
password: 123456
driver-class-name: oracle.jdbc.driver.OracleDriver
Configure two different database connection sources at the same time.
Switch data source @ Mapper container operation
Locate usersql. In the file directory XML file to define the sql method for connecting the two databases.
- The use table in the UserList method is in the database localhost:3306/db1
- The userdb2 table in the towlist method is in the database localhost:3306/db2
UserSql.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.core.my.app.dao.UserDao">
<select id="UserList" resultType="map">
select * from user
</select>
<select id="towlist" resultType="map">
select * from userdb2
</select>
</mapper>
@The DS annotation is printed on the MyBatis execution method to be executed@ DS marks the name of the database connection to be executed. The marked database connection is executed when the method is executed.
If @ DS is not marked on the MyBatis execution method, the method executes the default database connection.
UserDao interface
@Mapper
public interface UserDao {
//No @ DS is defined to execute the default database
List<Map> UserList(Map map);
//Force the method to access the database localhost:3306/db2 for execution
@DS("zht")
List<Map> towlist(Map map);
}
MyBatisTest test class
@RunWith(SpringRunner.class)
@SpringBootTest(classes=OnApp.class)
public class MyBatisTest {
@Test
public void TestAddDB(){
//The first database connection table tests the user table
userdao.UserList(new HashMap());
//The second database connection table tests the userdb2 table
userdao.towlist(new HashMap());
}
}
Define the corresponding test method in the test class.
Operation results
-----------Master database user surface ----------------- ==> Preparing: select * from user WHERE id in (?) and user like '%%' ==> Parameters: null <== Total: 0 -----------Sub database sys_user surface----------------- ==> Preparing: select * from sys_user ==> Parameters: <== Columns: id, acc, paw, u_name, u_post, u_edu, birthday, notes, deptid, isamdin
Different database operations are the same as table @ Service container operations
If there is the same table in two databases, in usersql How do we define sql document business operations for the same table in XML? We need to define a spring @Service container and use * * @ DS * * annotation in the container to distinguish the operations of different databases.
UserSql.xml
<select id="addlist" resultType="map">
select * from user
</select>
Create a user table in database 1 and database 2 at the same time, in usersql XML defines an sql document method for select * from user business.
UserDao interface
@Mapper
public interface UserDao {
//All databases share one table
List<Map> addlist(Map map);
}
The MyBatis execution method of this table query is defined in the UserDao interface
UserServer business class
Define two execution methods in the @ Service container class, one without * * @ DS * * annotation to query the user table in the default database, and the other defines @ DS("zht") to query the user table information in database 2.
@Service
public class UserServer {
@Autowired
UserDao user;
public List getOne(Map map){
return user.addlist(map);
}
@DS("zht")
public List getTow(Map map){
return user.addlist(map);
}
}
MyBatisTest test class
@RunWith(SpringRunner.class)
@SpringBootTest(classes=OnApp.class)
public class MyBatisTest {
@Autowired
UserServer us;//Business container import
@Test
public void AddDB(){
//First database connection user table
us.getOne(new HashMap());
//Second database connection user table
us.getTow(new HashMap());
}
}
Operation results
-----------Master database query ----------------- ==> Preparing: select * from user ==> Parameters: <== Columns: id, user <== Row: 1, username <== Row: 2, zht <== Total: 2 -----------Sub database query ----------------- ==> Preparing: select * from user ==> Parameters: <== Columns: id, name <== Row: 1, Database 2 <== Total: 1
MyBatis cache ehcache settings
In order to improve the use efficiency of the database, we usually use the secondary cache to operate the number of commonly used services through the secondary cache to reduce the pressure on the database and improve the efficiency. There are a large number of caching frameworks in java. We should choose ehcache because it is very mature. It is the best combination with MyBatis. The most important thing is that it is very simple to operate. It is basically easy to start at a glance. It is very convenient to use in practical projects.
pom.xml
<dependency>
<groupId>org.mybatis.caches</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.2.1</version>
</dependency>
In the MyBatis business that needs to use cache, SQL Write the ehcache cache configuration xml element in xml. There are two kinds of caches: 1 ordinary cache EhcacheCache and 2 blocking cache EhBlockingCache.
-
org.mybatis.caches.ehcache.EhcacheCache normal cache
-
org.mybatis.caches.ehcache.EhBlockingCache blocking cache
UserSql.xml join cache
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.core.my.app.dao.UserDao">
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
<select id="addlist" resultType="map">
select * from user
</select>
</mapper>
MyBatis business SQL Default settings in XML, so you can simply declare caching.
The cache is applied to each statement
- Once the cache is declared in xml, the cache is applied to all sql statements in xml.
- If you do not want to use caching, add useCache = "false" as the element attribute on the statement.
<!-- If you don't want to use caching, simply set it up useCache="false" -->
<select id="addlist" resultType="map" useCache="false">
select * from user
</select>
<!-- Not set useCache="false" The data information of the operation will be kept in the cache -->
<insert id="saveuser">
INSERT INTO user(id, user)
VALUES(#{id}, #{name})
</insert>
ehcache cache pool size setting
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.core.my.app.dao.UserDao">
<cache type="org.mybatis.caches.ehcache.EhcacheCache">
<property name="timeToIdleSeconds" value="3600"/>
<property name="timeToLiveSeconds" value="3600"/>
<property name="maxEntriesLocalHeap" value="10000"/>
<property name="maxEntriesLocalDisk" value="10000000"/>
<property name="memoryStoreEvictionPolicy" value="LRU"/>
</cache>
Generally, in small and medium-sized projects, the above cache pool configuration can basically meet the business requirements. In common business SQL Just add the following configuration to XML.
parameter list
| name | content |
|---|---|
| timeToIdleSeconds | Sets the maximum time an object is allowed to be idle |
| timeToLiveSeconds | Sets the maximum time an object is allowed to exist in the cache |
| maxEntriesLocalHeap | Cache the maximum cache entries that can be used in local heap memory |
| maxEntriesLocalDisk | The maximum cache entry that a standalone cache can use on the local disk |
| memoryStoreEvictionPolicy | When the maxElementsInMemory limit is reached, Ehcache will clean up the memory according to the specified policy. The optional policies are: LRU (least recently used, default policy), FIFO (first in first out), LFU (least access times) |
Data connection pool settings
We use Alibaba's druid as the data connection pool. Needless to say, Alibaba's things are really strong, easy to use and powerful. I have used so many data pools. Its comprehensive cost performance is the highest, and the starting cost is very low. As long as the yml file is paired with the data pool, it can be used, Its learning and maintenance cost depends on how to configure the corresponding parameters in yml.
pom.xml
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.1</version> </dependency>
Datapool application Hierarchy in YML
- Global data pool setting, which is the same level as the dynamic element under datasource
- Independent database connection pool settings. Set your own independent data connection pool under the database connection of multiple data sources. This pool can only be used by the corresponding data
Global pool hierarchy
spring:
datasource:
dynamic:
type: com.alibaba.druid.pool.DruidDataSource
druid://The same level as dynamic indicates that it is used by all data sources of the global data pool
Independent pool hierarchy
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
Master database:
type: com.alibaba.druid.pool.DruidDataSource
druid://Separate data connection pool settings
Sub database:
type: com.alibaba.druid.pool.DruidDataSource
druid://Separate data connection pool settings
Datapool application Settings in YML
Global pool example
server:
port: 8888
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
master:
url: jdbc:mysql://localhost:3306/systext?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
zht:
url: jdbc:mysql://localhost:3306/zhtsys?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
mybatis:
mapperLocations: classpath*:/mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
- The type element indicates the connected data pool driver. If it is another pool, the label under it will be replaced with the element label of the corresponding pool
An example of a separate pool of databases
server:
port: 8888
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
master:
url: jdbc:mysql://localhost:3306/systext?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
zht:
url: jdbc:mysql://localhost:3306/zhtsys?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
mybatis:
mapperLocations: classpath*:/mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
druid configuration parameters
| attribute | explain | remarks |
|---|---|---|
| initialSize | How many connections are initialized in the connection pool when the program is started | 100 |
| maxActive | How many active sessions are supported in the connection pool | |
| maxWait | When the program requests a connection from the connection pool, after exceeding the maxWait value, it considers the request failed, that is, there is no available connection in the connection pool, in milliseconds. Setting - 1 indicates infinite waiting | 100 |
| minEvictableIdleTimeMillis | After the idle time of a connection in the pool reaches N milliseconds, the connection pool will recycle the connection when checking the idle connection next time, which should be less than the firewall timeout setting net netfilter. nf_ conntrack_ tcp_ timeout_ Established settings | |
| timeBetweenEvictionRunsMillis | The frequency of checking idle connections, in milliseconds. Non positive integers mean no checking | |
| keepAlive | If the program has no close connection and the idle time exceeds minEvictableIdleTimeMillis, the SQL specified in validationQuery will be executed to ensure that the connection pool of the program will not be kill ed, and its range will not exceed the number of connections specified in minIdle. | true |
| minIdle | When reclaiming idle connections, at least minIdle connections will be guaranteed | Same as initialSize |
| removeAbandoned | It is required that the program must close after N seconds after get ting the connection from the pool, otherwise the druid will forcibly reclaim the connection, regardless of whether the connection is active or idle, so as to prevent the process from closing and occupying the connection. | Set to true when the program is found to have an abnormal close connection |
| removeAbandonedTimeout | Set the time limit for druid to forcibly recycle the connection. When the program get s the connection from the pool to the beginning of the connection, beyond this value, Druid will forcibly recycle the connection, in seconds. | It should be longer than the longest service running time |
| logAbandoned | Whether to record stack trace in the log after druid forcibly reclaims the connection | true |
| testWhileIdle | When a program requests a connection, does the pool first check whether the connection is valid when allocating the connection. (high efficiency) | true |
| validationQuery | Check whether the connection in the pool is still available. drui will connect to the database and execute the SQL. If | |
| testOnBorrow | When the program applies for a connection, it checks the validity of the connection (inefficient, affecting performance) | false |
| testOnReturn | When the program returns the connection, check the connection validity (low efficiency, affecting performance) | false |
| poolPreparedStatements | Cache SQL initiated through the following two methods | |
| maxPoolPrepareStatementPerConnectionSize how many SQL are cached per connection | 20 | |
| connectProperties | Connection properties. For example, set some configuration of connection pool statistics. |
druid is a powerful data pool that provides many other functions. If you need to add other functions, you can refer to the following configuration.
server:
port: 8888
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
master:
url: jdbc:mysql://localhost:3306/systext?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
zht:
url: jdbc:mysql://localhost:3306/zhtsys?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
# Configuration information of connection pool
# Initialization size, min, Max
initial-size: 5
min-idle: 5
maxActive: 20
# Configure the time to get the connection wait timeout
maxWait: 60000
# Configure how often to detect idle connections that need to be closed. The unit is milliseconds
timeBetweenEvictionRunsMillis: 60000
# Configure the minimum lifetime of a connection in the pool, in milliseconds
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# Open PSCache and specify the size of PSCache on each connection
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# Configure the filters for monitoring statistics interception. After removal, the sql in the monitoring interface cannot be counted, 'wall' is used for firewall
filters: stat,wall,slf4j
# Open the mergeSql function through the connectProperties property; Slow SQL record
connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
# Configure DruidStatFilter
web-stat-filter:
url-pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
# Configure DruidStatViewServlet
stat-view-servlet:
enabled: true
url-pattern: "/druid/*"
# IP white list (if it is not configured or empty, all access is allowed)
allow: 127.0.0.1,192.168.3.25
# IP blacklist (deny takes precedence over allow when there is a common)
# deny: localhost
# Disable the "Reset All" feature on HTML pages
reset-enable: false
# Login name
login-username: admin
# Login password
login-password: 123456
mybatis:
mapperLocations: classpath*:/mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl