SpringBoot + mybatis multiple data sources

1. Create project

For Eclipse platform, spring tool suite is installed

File - > New - > other - > spring boot - > spring starter project - > select jdk and other information - > Click Web, select Web, click SQL, select JPA, Mybatis, MYSQL, click next, Finish.

2. Document editing

2.1 modify pom.xml

Connection pool dependency changed to:

<dependency>
                <groupId>com.mchange</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.5.1</version>
           </dependency>

2.2 modify application.properties (src/main/resourcesapplication.properties)

mybatis.config-locations=classpath:mybatis-config.xml

first.datasource.jdbc-url=jdbc:MySQL://192.168.1.30:30006/nbsmt2?useUnicode=true&characterEncoding=utf-8
first.datasource.username=root
first.datasource.password=nbggxx123
first.datasource.driver-class-name=com.mysql.jdbc.Driver
first.datasource.type=mysql
#It should be written as JDBC url instead of url
second.datasource.jdbc-url=jdbc:MySQL://192.168.1.32:3306/test?useUnicode=true&characterEncoding=utf-8
second.datasource.username=score
second.datasource.password=score
second.datasource.driver-class-name=com.mysql.jdbc.Driver
second.datasource.type=mysql

When using the url, it will appear 2018-10-10 16:51:12.420 ERROR 5332 -- [[main] com.zaxxer.hikari.HikariConfig: hikaripool-1 - JDBC url is required with driverclassname

2.3 create entity class - same as single data source

2.4mybatis configuration (SRC / main / resources / mybatis config. XML)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<! -- set global properties -- >
	<settings>
		<! -- use getGeneratedKeys of jdbc to get the database auto increment primary key -- >
		<setting name="useGeneratedKeys" value="true" />

		<! -- replace column alias with column label -- >
		<setting name="useColumnLabel" value="true" />

		<! -- enable hump naming conversion table {create}- > entity {create time} -- >
		<setting name="mapUnderscoreToCamelCase" value="true" />

	</settings>
</configuration>

2.5 data source and sessionfactorybean configuration

2.5.1 datasource1 configuration

package com.lele.demo.mybatisDataSoure.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = "com.lele.demo.mapper.nbsmt2Mapper" , sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config{
	@Bean(name = "test1DataSource")
    @ConfigurationProperties(prefix = "first.datasource")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }
	@Bean(name = "test1SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mapper1/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "test1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

2.5.2 datasource2 configuration

package com.lele.demo.mybatisDataSoure.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = "com.lele.demo.mapper.testMapper" , sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
	@Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "second.datasource")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }
	@Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mapper2/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "test2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "test2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

2.6 DAO (mapper) creation

2.6.1

package com.lele.demo.mapper.nbsmt2Mapper;

import com.lele.demo.entity1.pisp_pub_user;

public interface pisp_pub_userMapper {
    int deleteByPrimaryKey(Integer pubUserId);

    int insert(pisp_pub_user record);

    int insertSelective(pisp_pub_user record);

    pisp_pub_user selectByPrimaryKey(Integer pubUserId);

    int updateByPrimaryKeySelective(pisp_pub_user record);

    int updateByPrimaryKey(pisp_pub_user record);
}

2.6.2

package com.lele.demo.mapper.testMapper;

import org.apache.ibatis.annotations.Mapper;

import com.lele.demo.entity2.userscore;
@Mapper
public interface userscoreMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(userscore record);

    int insertSelective(userscore record);

    userscore selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(userscore record);

    int updateByPrimaryKey(userscore record);
}

2.7 mapper.xml writing

2.7.1(src/main/resources/mapper/mapper1/pisp_pub_userMapper.xml)

You can use the "generator" tool to automatically build, and then modify the namespace, parameterType, type

<?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="com.lele.demo.mapper.nbsmt2Mapper.pisp_pub_userMapper" >
  <resultMap id="BaseResultMap" type="com.lele.demo.entity1.pisp_pub_user" >
    <id column="PUB_USER_ID" property="pubUserId" jdbcType="INTEGER" />
    <result column="LOGIN_NAME" property="loginName" jdbcType="VARCHAR" />
    <result column="PASSWORD" property="password" jdbcType="VARCHAR" />
    <result column="GENDER_TYPE" property="genderType" jdbcType="VARCHAR" />
    <result column="TEL" property="tel" jdbcType="VARCHAR" />
    <result column="E_MAIL" property="eMail" jdbcType="VARCHAR" />
    <result column="USER_TYPE" property="userType" jdbcType="CHAR" />
    <result column="STATE" property="state" jdbcType="CHAR" />
    <result column="CREATE_DATE" property="createDate" jdbcType="TIMESTAMP" />
    <result column="UPDATE_DATE" property="updateDate" jdbcType="TIMESTAMP" />
    <result column="NICKNAME" property="nickname" jdbcType="VARCHAR" />
    <result column="APP_ATCH_ID" property="appAtchId" jdbcType="INTEGER" />
    <result column="REAL_NAME_STATE" property="realNameState" jdbcType="CHAR" />
    <result column="SOURCE" property="source" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    PUB_USER_ID, LOGIN_NAME, PASSWORD, GENDER_TYPE, TEL, E_MAIL, USER_TYPE, STATE, CREATE_DATE, 
    UPDATE_DATE, NICKNAME, APP_ATCH_ID, REAL_NAME_STATE, SOURCE
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from pisp_pub_user
    where PUB_USER_ID = #{pubUserId,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from pisp_pub_user
    where PUB_USER_ID = #{pubUserId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.lele.demo.entity1.pisp_pub_user" >
    insert into pisp_pub_user (PUB_USER_ID, LOGIN_NAME, PASSWORD, 
      GENDER_TYPE, TEL, E_MAIL, 
      USER_TYPE, STATE, CREATE_DATE, 
      UPDATE_DATE, NICKNAME, APP_ATCH_ID, 
      REAL_NAME_STATE, SOURCE)
    values (#{pubUserId,jdbcType=INTEGER}, #{loginName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 
      #{genderType,jdbcType=VARCHAR}, #{tel,jdbcType=VARCHAR}, #{eMail,jdbcType=VARCHAR}, 
      #{userType,jdbcType=CHAR}, #{state,jdbcType=CHAR}, #{createDate,jdbcType=TIMESTAMP}, 
      #{updateDate,jdbcType=TIMESTAMP}, #{nickname,jdbcType=VARCHAR}, #{appAtchId,jdbcType=INTEGER}, 
      #{realNameState,jdbcType=CHAR}, #{source,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.lele.demo.entity1.pisp_pub_user" >
    insert into pisp_pub_user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="pubUserId != null" >
        PUB_USER_ID,
      </if>
      <if test="loginName != null" >
        LOGIN_NAME,
      </if>
      <if test="password != null" >
        PASSWORD,
      </if>
      <if test="genderType != null" >
        GENDER_TYPE,
      </if>
      <if test="tel != null" >
        TEL,
      </if>
      <if test="eMail != null" >
        E_MAIL,
      </if>
      <if test="userType != null" >
        USER_TYPE,
      </if>
      <if test="state != null" >
        STATE,
      </if>
      <if test="createDate != null" >
        CREATE_DATE,
      </if>
      <if test="updateDate != null" >
        UPDATE_DATE,
      </if>
      <if test="nickname != null" >
        NICKNAME,
      </if>
      <if test="appAtchId != null" >
        APP_ATCH_ID,
      </if>
      <if test="realNameState != null" >
        REAL_NAME_STATE,
      </if>
      <if test="source != null" >
        SOURCE,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="pubUserId != null" >
        #{pubUserId,jdbcType=INTEGER},
      </if>
      <if test="loginName != null" >
        #{loginName,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="genderType != null" >
        #{genderType,jdbcType=VARCHAR},
      </if>
      <if test="tel != null" >
        #{tel,jdbcType=VARCHAR},
      </if>
      <if test="eMail != null" >
        #{eMail,jdbcType=VARCHAR},
      </if>
      <if test="userType != null" >
        #{userType,jdbcType=CHAR},
      </if>
      <if test="state != null" >
        #{state,jdbcType=CHAR},
      </if>
      <if test="createDate != null" >
        #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="updateDate != null" >
        #{updateDate,jdbcType=TIMESTAMP},
      </if>
      <if test="nickname != null" >
        #{nickname,jdbcType=VARCHAR},
      </if>
      <if test="appAtchId != null" >
        #{appAtchId,jdbcType=INTEGER},
      </if>
      <if test="realNameState != null" >
        #{realNameState,jdbcType=CHAR},
      </if>
      <if test="source != null" >
        #{source,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.lele.demo.entity1.pisp_pub_user" >
    update pisp_pub_user
    <set >
      <if test="loginName != null" >
        LOGIN_NAME = #{loginName,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        PASSWORD = #{password,jdbcType=VARCHAR},
      </if>
      <if test="genderType != null" >
        GENDER_TYPE = #{genderType,jdbcType=VARCHAR},
      </if>
      <if test="tel != null" >
        TEL = #{tel,jdbcType=VARCHAR},
      </if>
      <if test="eMail != null" >
        E_MAIL = #{eMail,jdbcType=VARCHAR},
      </if>
      <if test="userType != null" >
        USER_TYPE = #{userType,jdbcType=CHAR},
      </if>
      <if test="state != null" >
        STATE = #{state,jdbcType=CHAR},
      </if>
      <if test="createDate != null" >
        CREATE_DATE = #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="updateDate != null" >
        UPDATE_DATE = #{updateDate,jdbcType=TIMESTAMP},
      </if>
      <if test="nickname != null" >
        NICKNAME = #{nickname,jdbcType=VARCHAR},
      </if>
      <if test="appAtchId != null" >
        APP_ATCH_ID = #{appAtchId,jdbcType=INTEGER},
      </if>
      <if test="realNameState != null" >
        REAL_NAME_STATE = #{realNameState,jdbcType=CHAR},
      </if>
      <if test="source != null" >
        SOURCE = #{source,jdbcType=VARCHAR},
      </if>
    </set>
    where PUB_USER_ID = #{pubUserId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.lele.demo.entity1.pisp_pub_user" >
    update pisp_pub_user
    set LOGIN_NAME = #{loginName,jdbcType=VARCHAR},
      PASSWORD = #{password,jdbcType=VARCHAR},
      GENDER_TYPE = #{genderType,jdbcType=VARCHAR},
      TEL = #{tel,jdbcType=VARCHAR},
      E_MAIL = #{eMail,jdbcType=VARCHAR},
      USER_TYPE = #{userType,jdbcType=CHAR},
      STATE = #{state,jdbcType=CHAR},
      CREATE_DATE = #{createDate,jdbcType=TIMESTAMP},
      UPDATE_DATE = #{updateDate,jdbcType=TIMESTAMP},
      NICKNAME = #{nickname,jdbcType=VARCHAR},
      APP_ATCH_ID = #{appAtchId,jdbcType=INTEGER},
      REAL_NAME_STATE = #{realNameState,jdbcType=CHAR},
      SOURCE = #{source,jdbcType=VARCHAR}
    where PUB_USER_ID = #{pubUserId,jdbcType=INTEGER}
  </update>
</mapper>

2.7.2 userscoreMapper.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="com.lele.demo.mapper.testMapper.userscoreMapper" >
  <resultMap id="BaseResultMap" type="com.lele.demo.entity2.userscore" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="userId" property="userid" jdbcType="VARCHAR" />
    <result column="totalScore" property="totalscore" jdbcType="INTEGER" />
    <result column="effectiveScore" property="effectivescore" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, userId, totalScore, effectiveScore
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from userscore
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from userscore
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.lele.demo.entity2.userscore" >
    insert into userscore (id, userId, totalScore, 
      effectiveScore)
    values (#{id,jdbcType=INTEGER}, #{userid,jdbcType=VARCHAR}, #{totalscore,jdbcType=INTEGER}, 
      #{effectivescore,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.lele.demo.entity2.userscore" >
    insert into userscore
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="userid != null" >
        userId,
      </if>
      <if test="totalscore != null" >
        totalScore,
      </if>
      <if test="effectivescore != null" >
        effectiveScore,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="userid != null" >
        #{userid,jdbcType=VARCHAR},
      </if>
      <if test="totalscore != null" >
        #{totalscore,jdbcType=INTEGER},
      </if>
      <if test="effectivescore != null" >
        #{effectivescore,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.lele.demo.entity2.userscore" >
    update userscore
    <set >
      <if test="userid != null" >
        userId = #{userid,jdbcType=VARCHAR},
      </if>
      <if test="totalscore != null" >
        totalScore = #{totalscore,jdbcType=INTEGER},
      </if>
      <if test="effectivescore != null" >
        effectiveScore = #{effectivescore,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.lele.demo.entity2.userscore" >
    update userscore
    set userId = #{userid,jdbcType=VARCHAR},
      totalScore = #{totalscore,jdbcType=INTEGER},
      effectiveScore = #{effectivescore,jdbcType=INTEGER}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

2.10 service (service interface and interface implementation serviceImpl) - it can be added according to actual needs, and can refer to the method of single data source

2.11 Controller layer

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.lele.demo.entity1.pisp_pub_user;
import com.lele.demo.mapper.nbsmt2Mapper.pisp_pub_userMapper;

@RestController
public class UserController {
	@Autowired
	private pisp_pub_userMapper userMapper;


	@RequestMapping(value="/selectByPrimaryKey/{id}")
	public String selectTel(@PathVariable ("id") Integer id) {
		pisp_pub_user user = userMapper.selectByPrimaryKey(id);
		return "user:" + user.toString();
	}

}

2.12 application entrance

package com.lele.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan("{com.lele.demo.mapper.nbsmt2Mapper},{com.lele.demo.mapper.testMapper}")
public class SpringbootMyBatisMoreMySqlApplication {

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

Attached project structure

Posted by shdt on Tue, 17 Dec 2019 15:18:11 -0800

Programmer Group

SpringBoot + mybatis multiple data sources

Hot Keywords