Springboot MyBatis定义数据源

最新推荐文章于 2023-08-04 10:03:24 发布

lijietao

最新推荐文章于 2023-08-04 10:03:24 发布

阅读量788

点赞数

分类专栏：后端数据库文章标签： spring mybatis

本文链接：https://blog.csdn.net/weixin_39587278/article/details/104238699

版权

后端同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

Springboot MyBatis整合笔记

springboot 与mybatis整合笔记，方便以后在线查看。首先，是整合mybatis的单数据源配置方式，然后，增加多数据源配置，不同的数据源使用不同的连接方式。使用默认的HikariDataSource和DruidDataSource。数据源不使用springboot默认装载的配置，自定义了两个datasource配置。

1.使用的依赖包

主要是mybatis和mysql的包，下面是整个project的依赖。

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.2.0.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.2.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.62</version>
        </dependency>

		<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.18</version>
		</dependency>

		<dependency>
			<groupId> org.springframework.boot </groupId>
			<artifactId> spring-boot-configuration-processor </artifactId>
			<optional> true </optional>
		</dependency>



	</dependencies>

2.project目录结构

在这里插入图片描述

3.准备工作

准备一个可以用的mysql，建好要用的数据库，可以建两个，方便后面的多数据源测试。表可以随便建，只要能把数据查询出来就可以。

4.application.yaml



spring:
  profiles:
    active: dev
  datasource:
    device:
      username: lijietao
      password: lijietao
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      # url: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      connection-test-query: SELECT 1 FROM DUAL
      type: com.alibaba.druid.pool.DruidDataSource

    user:
      username: lijietao
      password: lijietao
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      # url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      connection-test-query: SELECT 1 FROM DUAL
      jdbc-type: com.alibaba.druid.pool.DruidDataSource
    # type: com.alibaba.druid.pool.DruidDataSource
    # type: com.zaxxer.hikari.HikariDataSource

server:
  port: 8090
# mybatis:
#   mapper-locations: classpath:mapping/*.xml
logging:
  level:
   com.jietao.mybatis: debug
  # type-aliases-package: com.jietao.mybatis.mybatisstduy.entitys

5.定义两个实体类

Device.java

package com.jietao.mybatis.mybatisstduy.entitys;


import com.alibaba.fastjson.annotation.JSONField;
import lombok.Getter;
import lombok.Setter;
import org.springframework.stereotype.Component;

import java.io.Serializable;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@Setter
@Getter
public class Device implements Serializable{

    private static final long serialVersionUID = 3958848717201575449L;

    //@JSONField(name = "gw_id")
    private String gwId;

    //@JSONField(name = "type_id")
    private String typeId;

    //@JSONField(name = "gw_mac")
    private String mac;

    //@JSONField(name = "gw_name")
    private String deviceName;

}

User.java

package com.jietao.mybatis.mybatisstduy.entitys;

import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
import java.util.Date;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@Setter
@Getter
public class User implements Serializable {

    private static final long serialVersionUID = 7241476624325561916L;

    private Integer id;

    private String userName;
    private String sex;
    private String address;
    private Date updateTime;

}

6.定义两个Mapper

实际是定义两个接口，使用Mybatis的Mapper注解。
DeviceMapper.java

package com.jietao.mybatis.mybatisstduy.mapper;

import com.jietao.mybatis.mybatisstduy.entitys.Device;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@Mapper
public interface DeviceMapper {

    Device findOne(String id);

    List<Device> listDevice();


}

UserMapper.java

package com.jietao.mybatis.mybatisstduy.mapper2;

import com.jietao.mybatis.mybatisstduy.entitys.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@Mapper
public interface UserMapper {
    User FindOne(int id);
    List<User> UserList();
}

7.定义mapping资源文件

<?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.jietao.mybatis.mybatisstduy.mapper.DeviceMapper">

    <resultMap id="BaseResultMap" type="com.jietao.mybatis.mybatisstduy.entitys.Device">
        <result column="gw_id" jdbcType="VARCHAR" property="gwId" />
        <result column="type_id" jdbcType="VARCHAR" property="typeId" />
        <result column="gw_mac" jdbcType="VARCHAR" property="mac" />
        <result column="gw_name" jdbcType="VARCHAR" property="deviceName" />
    </resultMap>

    <select id="FineOne" resultType="com.jietao.mybatis.mybatisstduy.entitys.Device">
        select * from user where gw_id = #{id}
    </select>

    <select id="listDevice" resultType="com.jietao.mybatis.mybatisstduy.entitys.Device" resultMap="BaseResultMap">
        select * from iot_gw_public WHERE 1=1 limit 5
    </select>

</mapper>

mapper 根节点定义namespace属性，指定该mapping 文件关联到哪个mapper类上，就是上面定义的操作接口类。namespace定义的值很重要，关系到后面的数据操作。
resultMap定义的是结果的映射类，定义了哪些基本结果属性及基本数据类型。需要注意数据表的列名和属性名的对应关系。
本例只做了查询的操作，所以定义了两个select节点。每个select要有一个id属性，resultMap和resultType的属性。然后在select里面定义一个sql语句。select的id其实就是mapper接口的方法名，包括大小写要一致。

8.定义两个service

DeviceService.java

package com.jietao.mybatis.mybatisstduy.service;

import com.jietao.mybatis.mybatisstduy.entitys.Device;
import com.jietao.mybatis.mybatisstduy.mapper.DeviceMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/


@Service
public class DeviceService {

    @Autowired
    private DeviceMapper deviceMapper;

    public Device findOne(String id){
        return deviceMapper.findOne(id);
    }

    public List<Device> listDevice(){
        return deviceMapper.listDevice();
    }

}

UserService.java

package com.jietao.mybatis.mybatisstduy.service;

import com.jietao.mybatis.mybatisstduy.entitys.User;
import com.jietao.mybatis.mybatisstduy.mapper2.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public User FindOne(int id){
        return userMapper.FindOne(id);
    }

    public List<User> userList(){
        return userMapper.UserList();
    }

}

9.定义controller

DeviceController.java

package com.jietao.mybatis.mybatisstduy.controller;

import com.jietao.mybatis.mybatisstduy.entitys.Device;
import com.jietao.mybatis.mybatisstduy.service.DeviceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@RestController
@RequestMapping(value = "/api/device")
public class DeviceQuery {

    @Autowired
    private DeviceService deviceService;

    @RequestMapping(value = "/one", method = RequestMethod.GET)
    public Device getOne(@RequestParam String id){

        return deviceService.findOne(id);

    }

    @RequestMapping(value = "list", method = RequestMethod.GET)
    public List<Device> listDeivce(){
        return deviceService.listDevice();
    }


}

QueryUser.java

package com.jietao.mybatis.mybatisstduy.controller;

import com.fasterxml.jackson.databind.util.JSONPObject;
import com.jietao.mybatis.mybatisstduy.entitys.User;
import com.jietao.mybatis.mybatisstduy.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/2
 **/

@RestController
@RequestMapping(value = "/api/user")
public class QueryUser {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "one", method = RequestMethod.GET)
    public User oneUser(@RequestParam(value = "id", required = false, defaultValue = "1") Integer id){
        return userService.FindOne(id);
    }

    @RequestMapping(value = "list", method = RequestMethod.GET)
    public List<User> userList(){
        return userService.userList();
    }


}

10.定义数据源配置

DataSourceConfig.java

package com.jietao.mybatis.mybatisstduy.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.jietao.mybatis.mybatisstduy.config.mysql.MysqlConfigProperties;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.Resource;

import javax.sql.DataSource;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/8
 **/

@Configuration
@MapperScan(basePackages = "com.jietao.mybatis.mybatisstduy.mapper", sqlSessionTemplateRef = "sqlsessiontemplate1")
public class DataSource1Config {

    @Value("classpath:mapping/DeviceMapper.xml")
    private Resource[] mapperLocations;

    @javax.annotation.Resource
    private MysqlConfigProperties mysqlConfigProperties;

    /**
     * 定义datasource数据源
     * **/
    @Primary
    @Bean(name = "datasource1")
    // @ConfigurationProperties(prefix = "spring.datasource.device")
    public DataSource dataSource1() {
        // return new DruidDataSource();
        // return DataSourceBuilder.create().build();
        DruidDataSource dataSource = new DruidDataSource();
//        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/iot_manage?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC");
//        dataSource.setUsername("lijietao");
//        dataSource.setPassword("lijietao");
//        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(mysqlConfigProperties.getJdbcUrl());
        dataSource.setUsername(mysqlConfigProperties.getUsername());
        dataSource.setPassword(mysqlConfigProperties.getPassword());
        dataSource.setDriverClassName(mysqlConfigProperties.getDriverClassName());

        return dataSource;
    }

    @Bean(name = "sqlsessionFactory1")
    @Primary
    public SqlSessionFactory sqlSessionFactory1(@Qualifier(value = "datasource1") DataSource dataSource) throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        // sqlSessionFactoryBean.setConfigLocation();
        sqlSessionFactoryBean.setMapperLocations(mapperLocations);
        return sqlSessionFactoryBean.getObject();

    }

    @Bean(name = "sqlsessiontemplate1")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier(value = "sqlsessionFactory1") SqlSessionFactory sqlSessionFactory) throws Exception{
        return new SqlSessionTemplate(sqlSessionFactory);

    }
}

主要是定义datasource，sqlsessionfactory和sqlsessiontemplate，如果是多数据源还需要加上Primary注解，以区分主数据源。datasource用到了DruidDataSource。

MysqlConfigPropertis.java配置类

package com.jietao.mybatis.mybatisstduy.config.mysql;

import lombok.Getter;
import lombok.Setter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/8
 **/

@Setter
@Getter
@ConfigurationProperties(prefix = "spring.datasource.device")
@Configuration
public class MysqlConfigProperties {

    //@Value("${spring.datasource.device.jdbc-url}")
    private String jdbcUrl;

    private String username;

    private String password;

    //@Value("${spring.datasource.device.driver-class-name}")
    private String driverClassName;



}

DataSource2Config.java

package com.jietao.mybatis.mybatisstduy.config;

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.beans.factory.annotation.Value;
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.Resource;

import javax.sql.DataSource;

/**
 * Description: springboot-stduy
 * Created by Tao.Lee on 2020/2/8
 **/


@Configuration
@MapperScan(basePackages = "com.jietao.mybatis.mybatisstduy.mapper2", sqlSessionTemplateRef = "sqlsessiontemplate2")
public class DataSource2Config {

    @Value("classpath:mapping/UserMapper.xml")
    private Resource[] mapperLocations;

    // @Primary
    @Bean(name = "datasource2")
    @ConfigurationProperties(prefix = "spring.datasource.user")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlsessionFactory2")
    // @Primary
    public SqlSessionFactory sqlSessionFactory1(@Qualifier(value = "datasource2") DataSource dataSource) throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        // sqlSessionFactoryBean.setConfigLocation();
        sqlSessionFactoryBean.setMapperLocations(mapperLocations);
        return sqlSessionFactoryBean.getObject();

    }

    @Bean(name = "sqlsessiontemplate2")
    // @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier(value = "sqlsessionFactory2") SqlSessionFactory sqlSessionFactory) throws Exception{
        return new SqlSessionTemplate(sqlSessionFactory);

    }

}

这里的datasource用的是datasourcebuild创建的，默认的是HikariDataSource。
由于是使用自定义的多数据源，因此springbootapplication自动数据源配置部分要关掉，在springbootapplication启动类加上@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})，表示排除默认的datasource配置，否则这种多数据源写法会报错。

最后启动项目。
在浏览器输入调试的url
在这里插入图片描述
如果打开的日志，可以看到sql执行状况。

总结

通过这个demo可以体会到框架使用sql与原始方法的不同。这种分层式的设计在项目运维后期显得尤为重要。sql与业务逻辑拆分，对开发人员有一定的要求，怎么拆分，在哪个模块组装数据，都得精心策划一番。前期没有深挖mybatis框架，在demo代码中只是做了简单的使用配置，就可以达到基本的效果，印象最深的是通过在xml配置mapping文件，然后定义mapper接口，就可以通过sql查询出数据，并映射到实体类。Amazing!!!

demo代码

lijietao

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Springboot MyBatis定义数据源

Springboot MyBatis整合笔记springboot 与mybatis整合笔记，方便以后在线查看。首先，是整合mybatis的单数据源配置方式，然后，增加多数据源配置，不同的数据源使用不同的连接方式。使用默认的HikariDataSource和DruidDataSource。数据源不使用springboot默认装载的配置，自定义了两个datasource配置。1.使用的依赖包主要...
复制链接

扫一扫