Introduction to SpringBoot 3: integrating Mybatis and multiple data sources

Spring boot introduction 3 data access and multiple data sources

1. Use JDBC template for spring boot integration

1.1 pom file introduction

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		</dependency>

	</dependencies>

1.2 yml

server :
  port : 8081
spring: 
  datasource: 
    url: jdbc:mysql://localhost:3307/springboot?useUnicode=true&characterEncoding=utf8
###springboot2 multi data source has a bug URL - "JDBC URL"
    username: root
    password: 
    driver-class-name: com.mysql.jdbc.Driver

1.3 Mapper code

2. springboot integrates mybatis

Mybatis is a persistence layer framework that supports complex SQL statements, stored procedures and advanced mapping. There are two ways to use mybatis, XML and annotations.

2.1 SpringBoot+Mybatis annotated version

Mybatis is troublesome to use in the early stage. It requires many configuration files, entity classes, dao layer mapping, and many other configurations. In the early stage of development, generator can automatically produce entity class and dao layer code according to the table structure, which can reduce part of the development amount; in the later stage, mybatis has made a lot of optimization, and now it can use annotation version to automatically manage dao layer and configuration file.

Mybatis spring boot start is the jar package of spring boot integrated with mybatis. It can be fully annotated without configuration files. It is easy to start with simple configuration.
2.1.1 add the jar package of Mybatis and MySQL to Maven pom.xml file

		<!-- integration mybatise Dependency needed -->
		<dependency>
		    <groupId>org.mybatis.spring.boot</groupId>
		    <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
		</dependency>

2.1.2 configure application.yml file

spring: 
  datasource: 
    url: jdbc:mysql://localhost:3307/springboot?useUnicode=true&characterEncoding=utf8
###springboot2 multi data source has a bug URL - "JDBC URL"
    username: root
    password: 
    driver-class-name: com.mysql.jdbc.Driver
    type : com.alibaba.druid.pool.DruidDataSource

springboot will automatically load spring.datasource. * related configurations, and the data source will be automatically injected into sqlSessionFactory, and sqlSessionFactory will be automatically injected into Mapper.
The 2.0.0 and 2.1.1 versions of mybatis spring boot starter are different. If you upgrade to 2.1.1, the same code will report the following errors
Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required
In order to solve the problem of multiple data sources, mybatis has cancelled the automatic injection of sqlSessionFactory and so on, which can be manually injected. It will not be expanded here in detail.

Add @ MapperScan to the Mapper package in the startup class or add the annotation @ Mapper above to each Mapper class. It is recommended to annotate the startup class so as not to annotate each Mapper class.

@MapperScan("sample.springboot.teach3.mapper")
@SpringBootApplication
public class SpringbootTeach3Application {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootTeach3Application.class, args);
    }

}

2.1.3 Mapper development
All sql is on the annotations.

@Select is the annotation of the query class. All queries use this

@Result decorates the returned result set. The associated entity class property corresponds to the database field one by one. If the entity class property and the database property name are consistent, this property is not needed to decorate.

@Insert is used to insert into the database. Directly passing in the entity class will automatically parse the property to the corresponding value

@Update is responsible for modification and can also pass in objects directly

@delete is responsible for deletion

For more information, please refer to mybatis official address

http://www.mybatis.org/mybatis-3/zh/java-api.html

2.1.4 use
Launch project successfully entered the local address in the browser.

In the source code, the controller layer has a complete addition, deletion, modification and query, so it will not be pasted here.

Common problem

Pay attention to the question of ා and $?

#The method can prevent sql injection to a great extent. If the incoming data is treated as a string, a double quotation mark will be added to the automatically incoming data. For example: order by ා user ා id 񖓿 if the passed in value is 111, the value when parsing into sql is order by "111"; if the passed in value is id, the parsed sql is order by "id"

Mode can't prevent SQL injection, mode can't prevent SQL injection, mode can't prevent SQL injection, and the incoming data is directly displayed and generated in SQL. For example: order by user id user ﹣ iduseri d. if the passed in value is 111, the value when parsing into SQL is order by user ﹣ id; if the passed in value is id, the parsed SQL is order by id. The $method is generally used to pass in database objects, such as table names.

When using the order by dynamic parameter to sort with MyBatis, you need to pay attention to using $, instead of ා.

#{} This value is obtained after compiling the SQL statement

${} This is to take a value and then compile the SQL statement

2.2 SpringBoot+Mybatis XML version

The xml version keeps the old tradition of mapping file. The optimization is mainly reflected in finding the corresponding sql statements in the mapped xml file instead of realizing dao.
2.2.1 add configuration to application.yml

#mybatis
mybatis:
  mapper-locations: classpath:sample.springboot.teach3.mapper/*.xml
  type-aliases-package: sample.springboot.teach3.model

2.2.2 Mapper XML uses the mybatis generator generator. The specific configuration method of the generator is not expanded here. The code is as follows

2.2.3Dao level
Dao layer is also generated automatically, and user-defined interface can be added

public interface UserMapper {

    User findMyName(@Param("name") String name);

    @Insert("INSERT INTO USERS(USER_NAME,AGE) VALUES(#{name},#{age})")
    int add(@Param("name") String naem, @Param("age") Integer age);

    int deleteByPrimaryKey(Integer id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
}

2.2.4
Startup, encountered the following error

 Cause: java.lang.IllegalArgumentException: Result Maps collection already contains value for xyx.dsw.dao.mapper.admin.quotationwish.TempTestTableMapper.TempTestTableResult 

Cause analysis:

Because the id of the method in the interface TempTestTableMapper.java implemented by TempTestTableMapper.xml of ibatis has duplicate values, check XXXMapper.xml and find that it is caused by multiple execution of mybatis generator

Query function, test in url page.

In the source code, the controller layer has a complete addition, deletion, modification and query, so it will not be pasted here
How to choose annotation and xml?
The annotated version is suitable for simple and fast mode.
xml version is more suitable for large-scale projects. It can generate SQL dynamically and easily adjust SQL.

3. Integrate the mybatis generator plug-in

Method

  1. Under eclipse, use maven plug-in + mabatis generator to generate mybatis files,
  2. Install the MybatisGenerator plug-in. This mode will not be expanded

3.1. Configure Maven pom.xml file

Add the following plug-ins to pom.xml:

<plugin>
				<groupId>org.mybatis.generator</groupId>
				<artifactId>mybatis-generator-maven-plugin</artifactId>
				<version>1.3.2</version><!--jar Package to generate corresponding class needs to connect to database data connection version consistent with that in project -->
				<dependencies>
					<dependency>
						<groupId>mysql</groupId>
						<artifactId>mysql-connector-java</artifactId>
						<version>5.1.45</version>
					</dependency>
				</dependencies>
				<configuration><!--MBG Path to profile -->
					<configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
					<overwrite>true</overwrite>
				</configuration>
			</plugin>

After configuring the Maven plug-in, you need to configure the configuration file of mybatis generator

3.2. Configure the configuration file of mybatis generator

Add configuration file to resource:

Contents of generatorConfig.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
 <generatorConfiguration>
	<context id = "test " targetRuntime="MyBatis3">
		<plugin type ="org.mybatis.generator.plugins.EqualsHashCodePlugin">
		</plugin>
		<plugin type ="org.mybatis.generator.plugins.SerializablePlugin">
		</plugin>
		<plugin type ="org.mybatis.generator.plugins.ToStringPlugin">
		</plugin>
		<commentGenerator><!-- This element is used to remove the generated date from the specified generated comment false:Representation includes --><!-- If a date is generated, even if a field is modified, all properties of the entire entity class will change, which is not conducive to version control. Therefore, it is set to true -->
			<property name = "suppressDate" value="true" /><!-- Remove automatically generated comments or not 			true: Yes, false:no -->
			<property name = "suppressAllComments" value="true" />
		</commentGenerator><!--Database links URL,User name, password -->
		<jdbcConnection driverClass ="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3307/springboot"		userId="root" password="">
		</jdbcConnection>
		<javaTypeResolver>
			<property name = "forceBigDecimals" value="false" />
		</javaTypeResolver>
		<!-- Package name and location of the generated entity class -->
		<javaModelGenerator targetPackage ="sample.springboot.teach3.model" targetProject="src/main/java">
			<property name = "enableSubPackages" value="true" />
			<property name = "trimStrings" value="true" />
		</javaModelGenerator>
		<!--Package name and location of the build mapping file com/lu/mapper -->
			<sqlMapGenerator targetPackage ="sample.springboot.teach3.mapper" targetProject="src/main/resources">
				<property name = "enableSubPackages" value="true" />
			</sqlMapGenerator><!-- generate DAO Package name and location for mybatis Two development models xml Annotation -->
			<!-- <javaClientGenerator type = "XMLMAPPER"	targetPackage="sample.springboot.teach3.mapper" targetProject="src/main/java">
				<property name = "enableSubPackages" value="true" />
			</javaClientGenerator> -->
			<!-- Which tables to generate -->
			<table tableName = "users" domainObjectName="User"		enableCountByExample="false" enableUpdateByExample="false"
				enableDeleteByExample="false" enableSelectByExample="false"
				selectByExampleQueryId="false">
			</table>
		</context>
</generatorConfiguration>

3.3 generate code

In eclipse, select pom.xml file, right-click Run AS - > Maven build ——>In the Goals box, type: mybatis generator: generate,

maven downloads the plug-in first, and then generates the code.

Look at the effect:

4. Configure multiple data sources

Spring boot multi data source means that there are many different jdbc database connections in a jar.
There are members, orders and payment modules in a project, only subcontracting, not distributed projects.
Distributed project is to divide a large project into many different subprojects, among which rpc remote call communication technology is used. Suppose our database is divided into member database, order database, payment database, etc. there are different jdbc; how to locate your own data source for multiple databases:

  1. Subcontract name
    sample.teach2.user -- the data is the member, or user
    sample.teach2.order - order database
  2. Annotation form
    It is not commonly used to introduce @ DataSource("XXX") in the service layer. This article only uses the first

4.1. Define data source configuration first

#=====================multiple database config============================
spring: 
  datasource: 
    user:
      jdbc-url: jdbc:mysql://localhost:3307/springboot?useUnicode=true&characterEncoding=utf8
  ###springboot2 multi data source has a bug URL->jdbc-url
      username: root
      password: 
      driver-class-name: com.mysql.jdbc.Driver
    order :
      jdbc-url: jdbc:mysql://localhost:3307/springboot2?useUnicode=true&characterEncoding=utf8
  ###springboot2 multi data source has a bug URL->jdbc-url
      username: root
      password: 
      driver-class-name: com.mysql.jdbc.Driver
###    type : com.alibaba.druid.pool.DruidDataSource

4.2. Configure related injection data source objects of data source

First data source configuration

@Configuration
@MapperScan(basePackages = "sample.springboot.teach3.user", sqlSessionTemplateRef = "userSqlSessionTemplate")
public class UserDataSourceConfig {

    @Bean("userDataSource")
    @ConfigurationProperties("spring.datasource.user")
    public DataSource userDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "userSqlSessionFactory")
    public SqlSessionFactory userSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(userDataSource());
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "userTransactionManager")
    public DataSourceTransactionManager userTrancationManager(@Qualifier("userDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "userSqlSessionTemplate")
    public SqlSessionTemplate
        userSqlSessionTemplate(@Qualifier("userSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Relevant knowledge points:
1. Use @ bean to create a bean object and submit it to the spring container for management
2. The name of the bean object created by @ bean defaults to the method name, which can also be specified
3. The @ bean method parameter indicates that it receives a bean object. By default, it receives the injected object according to the type. To change it to byName mode, you can inject the exact object with @ Qualifier annotation
4.@Primary indicates that the bean is the default bean of this type. When it is referenced elsewhere, it can be injected by type with @ Autowired, which is not affected by multiple objects of the same type

Other comments are not clear!

2. Configure the second data source

@Configuration
@MapperScan(basePackages = "sample.springboot.teach3.order", sqlSessionTemplateRef = "orderSqlSessionTemplate")
public class OrderDataSourceConfig {

    @Bean("orderDataSource")
    @ConfigurationProperties("spring.datasource.order")
    public DataSource orderDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "orderSqlSessionFactory")
    public SqlSessionFactory orderSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(orderDataSource());
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "orderTransactionManager")
    public DataSourceTransactionManager orderTrancationManager(@Qualifier("orderDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "orderSqlSessionTemplate")
    public SqlSessionTemplate
        orderSqlSessionTemplate(@Qualifier("orderSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4.3. Dao data persistence layer

public interface UserMapper {

    User findMyName(@Param("name") String name);

    @Insert("INSERT INTO USERS(USER_NAME,AGE) VALUES(#{name},#{age})")
    int add(@Param("name") String name, @Param("age") Integer age);

    int deleteByPrimaryKey(Integer id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
}
public interface OrderMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Order record);

    int insertSelective(Order record);

    Order selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Order record);

    int updateByPrimaryKey(Order record);
}

The above two interfaces belong to two data sources,

4.4 Service layer, pay attention to matters

@Service
public class OrderService {

    @Autowired
    private OrderMapper orderMapper;

    public boolean addOrder(Integer userId, Integer total) {
        Order order = new Order();
        order.setTotal(Float.valueOf(total));
        order.setUserId(userId);
        int result = orderMapper.insertSelective(order);
        return result > 0 ? true : false;
    }
}
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;

    public boolean addUser2(String userName, Integer age) {
        int result = userMapper.add(userName, age);
        return result > 0 ? true : false;
    }

    public User getById(Integer id) {
        return userMapper.selectByPrimaryKey(id);
    }
}

4.5 knowledge expansion

1. If the traditional jpa mode is adopted, @ enablejpaepositories does not need to be configured and has no impact. The implementation method is as follows:

ds1 related DaoImpl

@PersistenceContext
private EntityManager entityManager;

ds2 related DaoImpl

@PersistenceContext(unitName = "secondDs")
private EntityManager entityManager;

Because the entityManger of ds1 declares @ Primary, there is no need to specify unitname, and DS2 must. With the accurate entityManager injected, you can operate the database directly. The service layer is the same as the above, @ Transactional("xxxManager") indicates the thing manager!

2. Using JDBC template, directly inject into the Service layer object, so easy!

@Autowired
private JdbcTemplate jdbcTemplate;

@Autowired
private TransactionTemplate transactionTemplate;

@Resource(name="jdbcTemplate2")
private JdbcTemplate jdbcTemplate2;

@Resource(name="transactionTemplate2")
private TransactionTemplate transactionTemplate2;

All right, spring boot multi data source, perfect solution! And three database operation methods are supported, including things. It has been proved by practice! This is the official best practice, but the official documents are not detailed.

Code GitHub address to be uploaded
Please look forward to the source code
Annotated edition:
XML version:

Published 3 original articles, won praise 0, visited 844
Private letter follow

Tags: Mybatis JDBC Spring xml

Posted on Fri, 06 Mar 2020 00:45:26 -0800 by Eric!