Spring Boot2.0系列教程之MyBatis Druid 多数据源（十一）

最新推荐文章于 2022-08-13 16:12:38 发布
最新推荐文章于 2022-08-13 16:12:38 发布
阅读量680 收藏 1
点赞数
分类专栏： Spring Boot Spring Boot2.0系列教程 文章标签：  Spring Boot2.0系列教程  Spring Boot多数据源 MyBatis Druid 多数据源 mybatis多数据源
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zjh_746140129/article/details/81255033
版权

Druid 介绍

Druid 是阿里巴巴开源平台上的一个项目，整个项目由数据库连接池、插件框架和 SQL 解析器组成。该项目主要是为了扩展 JDBC 的一些限制，可以让程序员实现一些特殊的需求，比如向密钥服务请求凭证、统计 SQL 信息、SQL 性能收集、SQL 注入检查、SQL 翻译等，程序员可以通过定制来实现自己需要的功能。

Druid 首先是一个数据库连接池，但它不仅仅是一个数据库连接池，它还包含一个 ProxyDriver，一系列内置的 JDBC 组件库，一个 SQL Parser。在 Javad 的世界中 Druid 是目前最好的数据库连接池，在功能、性能、扩展性方面，都超过其他数据库连接池，包括 DBCP、C3P0、BoneCP、Proxool、JBoss DataSource。

Druid 可以做什么

  • 替换其他 Java 连接池，Druid 提供了一个高效、功能强大、可扩展性好的数据库连接池。
  • 可以监控数据库访问性能，Druid 内置提供了一个功能强大的 StatFilter 插件，能够详细统计 SQL 的执行性能，这对于线上分析数据库访问性能有帮助。
  • 数据库密码加密。直接把数据库密码写在配置文件中，这是不好的行为，容易导致安全问题。DruidDruiver 和 DruidDataSource 都支持 PasswordCallback。
  • SQL 执行日志，Druid 提供了不同的 LogFilter，能够支持 Common-Logging、Log4j 和 JdkLog，可以按需要选择相应的 LogFilter，监控应用的数据库访问情况。
  • 扩展 JDBC，如果你要对 JDBC 层有编程的需求，可以通过 Druid 提供的 Filter 机制，很方便编写 JDBC 层的扩展插件。

Spring Boot 集成 Druid

Druid Spring Boot Starter

非常令人高兴的是，阿里为 Druid 也提供了 Spring Boot Starter 的支持。官网这样解释：Druid Spring Boot Starter 用于帮助在 Spring Boot 项目中轻松集成 Druid 数据库连接池和监控。

Druid Spring Boot Starter 主要做了哪些事情呢？其实这个组件包很简单，主要是提供了很多自动化的配置，按照 Spring Boot 的理念对很多的内容进行了预配置，让我们在使用的时候更加的简单和方便。

 

注：第一部分为代码，第二部分为增删改查操作的截图，完整代码可在github下载。

github地址：https://github.com/zjh746140129/Spring-Boot2.0

项目结构截图：

 

一、代码片段

1、编写成绩类、学生类、分页类

package com.boot.model;

import java.io.Serializable;

/**
 * @Description: 成绩类
 * @Author: zhoujh
 * @CreateDate: 2018/6/20$ 下午9:27$
 * @Version: 1.0
 */
public class Score implements Serializable {

    private static final long serialVersionUID = 1L;
    private String id;
    private Long sid;
    private Double chineseScore;
    private Double mathScore;
    private Double englishScore;

    public Score() {
    }

    public Score(String id, Long sid, Double chineseScore, Double mathScore, Double englishScore) {
        this.id = id;
        this.sid = sid;
        this.chineseScore = chineseScore;
        this.mathScore = mathScore;
        this.englishScore = englishScore;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public Long getSid() {
        return sid;
    }

    public void setSid(Long sid) {
        this.sid = sid;
    }

    public Double getChineseScore() {
        return chineseScore;
    }

    public void setChineseScore(Double chineseScore) {
        this.chineseScore = chineseScore;
    }

    public Double getMathScore() {
        return mathScore;
    }

    public void setMathScore(Double mathScore) {
        this.mathScore = mathScore;
    }

    public Double getEnglishScore() {
        return englishScore;
    }

    public void setEnglishScore(Double englishScore) {
        this.englishScore = englishScore;
    }
}

package com.boot.model;

import java.io.Serializable;
import java.util.List;

/**
 * @Description: 学生类
 * @Author: zhoujh
 * @CreateDate: 2018/6/20$ 下午9:27$
 * @Version: 1.0
 */
public class Student implements Serializable {

    private static final long serialVersionUID = 1L;
    private Long id;
    private String name;
    private Integer age;
    private String email;
    private String address;
    private List<Score> scoreList;

    public Student() {
    }

    public Student(Long id, String name, Integer age, String email, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.email = email;
        this.address = address;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public List<Score> getScoreList() {
        return scoreList;
    }

    public void setScoreList(List<Score> scoreList) {
        this.scoreList = scoreList;
    }
}

package com.boot.model;


import com.boot.common.PageParam;
import org.apache.commons.lang3.builder.ToStringBuilder;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;


/**
 * @Description: 分页封装数据
 * @Author: zhoujh
 * @CreateDate: 2018/6/20$ 下午10:11$
 * @Version: 1.0
 */
public class Page<E> implements Serializable {
    private static final long serialVersionUID = -2020350783443768083L;

    private int currentPage = 1; //当前页数
    private long totalPage;       //总页数
    private long totalNumber;    //总记录数
    private List<E> list;        //数据集

    public static Page NULL = new Page(0, 0, 15, new ArrayList());

    public Page() {
        super();
    }

    /**
     * @param beginLine   当前页数
     * @param totalNumber 总记录数
     * @param pageSize    页大小
     * @param list        页数据
     */
    public Page(int beginLine, long totalNumber, int pageSize, List<E> list) {
        super();
        this.currentPage = beginLine / pageSize + 1;
        this.totalNumber = totalNumber;
        this.list = list;
        this.totalPage = totalNumber % pageSize == 0 ? totalNumber
                / pageSize : totalNumber / pageSize + 1;
    }

    public Page(PageParam pageParam, long totalNumber, List<E> list){
        super();
        this.currentPage = pageParam.getCurrentPage();
        this.totalNumber = totalNumber;
        this.list = list;
        this.totalPage = totalNumber % pageParam.getPageSize() == 0 ? totalNumber
                / pageParam.getPageSize() : totalNumber / pageParam.getPageSize() + 1;
    }


    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public long getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(long totalPage) {
        this.totalPage = totalPage;
    }

    public long getTotalNumber() {
        return totalNumber;
    }

    public void setTotalNumber(long totalNumber) {
        this.totalNumber = totalNumber;
    }

    public List<E> getList() {
        return list;
    }

    public void setList(List<E> list) {
        this.list = list;
    }

    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }
}

2、编写mapper.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.boot.mapper.one.ScoreMapper" >
  <resultMap id="BaseResultMap" type="com.boot.model.Score" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="chinese_score" property="chineseScore" jdbcType="DOUBLE" />
    <result column="english_score" property="englishScore" jdbcType="DOUBLE" />
    <result column="math_score" property="mathScore" jdbcType="DOUBLE" />
    <result column="sid" property="sid" jdbcType="BIGINT" />
  </resultMap>
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause" >
    <where >
      <foreach collection="example.oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List" >
    id, chinese_score, english_score, math_score, sid
  </sql>

  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from score
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from score
    where id = #{id,jdbcType=VARCHAR}
  </delete>

  <insert id="insert" parameterType="com.boot.model.Score" >
    insert into score (id, chinese_score, english_score, 
      math_score, sid)
    values (#{id,jdbcType=VARCHAR}, #{chineseScore,jdbcType=DOUBLE}, #{englishScore,jdbcType=DOUBLE}, 
      #{mathScore,jdbcType=DOUBLE}, #{sid,jdbcType=BIGINT})
  </insert>
  <insert id="insertSelective" parameterType="com.boot.model.Score" >
    insert into score
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="chineseScore != null" >
        chinese_score,
      </if>
      <if test="englishScore != null" >
        english_score,
      </if>
      <if test="mathScore != null" >
        math_score,
      </if>
      <if test="sid != null" >
        sid,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="chineseScore != null" >
        #{chineseScore,jdbcType=DOUBLE},
      </if>
      <if test="englishScore != null" >
        #{englishScore,jdbcType=DOUBLE},
      </if>
      <if test="mathScore != null" >
        #{mathScore,jdbcType=DOUBLE},
      </if>
      <if test="sid != null" >
        #{sid,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>


  <update id="updateByExampleSelective" parameterType="map" >
    update score
    <set >
      <if test="record.id != null" >
        id = #{record.id,jdbcType=VARCHAR},
      </if>
      <if test="record.chineseScore != null" >
        chinese_score = #{record.chineseScore,jdbcType=DOUBLE},
      </if>
      <if test="record.englishScore != null" >
        english_score = #{record.englishScore,jdbcType=DOUBLE},
      </if>
      <if test="record.mathScore != null" >
        math_score = #{record.mathScore,jdbcType=DOUBLE},
      </if>
      <if test="record.sid != null" >
        sid = #{record.sid,jdbcType=BIGINT},
      </if>
    </set>
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map" >
    update score
    set id = #{record.id,jdbcType=VARCHAR},
      chinese_score = #{record.chineseScore,jdbcType=DOUBLE},
      english_score = #{record.englishScore,jdbcType=DOUBLE},
      math_score = #{record.mathScore,jdbcType=DOUBLE},
      sid = #{record.sid,jdbcType=BIGINT}
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.boot.model.Score" >
    update score
    <set >
      <if test="chineseScore != null" >
        chinese_score = #{chineseScore,jdbcType=DOUBLE},
      </if>
      <if test="englishScore != null" >
        english_score = #{englishScore,jdbcType=DOUBLE},
      </if>
      <if test="mathScore != null" >
        math_score = #{mathScore,jdbcType=DOUBLE},
      </if>
      <if test="sid != null" >
        sid = #{sid,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.boot.model.Score" >
    update score
    set chinese_score = #{chineseScore,jdbcType=DOUBLE},
      english_score = #{englishScore,jdbcType=DOUBLE},
      math_score = #{mathScore,jdbcType=DOUBLE},
      sid = #{sid,jdbcType=BIGINT}
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <select id="getListScore" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from score
    where 1=1
    order by id desc
  </select>

</mapper>
<?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.boot.mapper.two.StudentMapper" >
  <resultMap id="BaseResultMap" type="com.boot.model.Student" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="address" property="address" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <!--<collection column="sid" property="scoreList"  javaType="java.util.List" resultMap="scoreResultMap" />-->

    <collection column="sid" property="scoreList"  ofType="com.boot.model.Score">
      <id column="id" property="id" jdbcType="VARCHAR" />
      <result column="chinese_score" property="chineseScore" jdbcType="DOUBLE" />
      <result column="english_score" property="englishScore" jdbcType="DOUBLE" />
      <result column="math_score" property="mathScore" jdbcType="DOUBLE" />
      <result column="sid" property="sid" jdbcType="BIGINT" />
    </collection>
  </resultMap>


  <select id="selectStudentScore" resultMap="BaseResultMap">
   SELECT
student.address AS address,
student.age AS age,
student.`name` AS `name`,
student.email AS email,
score.chinese_score AS chinese_score,
score.english_score AS english_score,
score.math_score AS math_score,
student.id,
score.sid
FROM
score ,
student
WHERE
score.sid = student.id


  </select>
 <!-- <resultMap id="scoreResultMap" type="com.boot.model.Score" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="chinese_score" property="chineseScore" jdbcType="DOUBLE" />
    <result column="english_score" property="englishScore" jdbcType="DOUBLE" />
    <result column="math_score" property="mathScore" jdbcType="DOUBLE" />
    <result column="sid" property="sid" jdbcType="BIGINT" />
  </resultMap>-->
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause" >
    <where >
      <foreach collection="example.oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List" >
    id, address, age, email, name
  </sql>

  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from student
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from student
    where id = #{id,jdbcType=BIGINT}
  </delete>

  <insert id="insert" parameterType="com.boot.model.Student" >
    insert into student (id, address, age, 
      email, name)
    values (#{id,jdbcType=BIGINT}, #{address,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, 
      #{email,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.boot.model.Student" >
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="address != null" >
        address,
      </if>
      <if test="age != null" >
        age,
      </if>
      <if test="email != null" >
        email,
      </if>
      <if test="name != null" >
        name,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="address != null" >
        #{address,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
      <if test="email != null" >
        #{email,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

  <update id="updateByExampleSelective" parameterType="map" >
    update student
    <set >
      <if test="record.id != null" >
        id = #{record.id,jdbcType=BIGINT},
      </if>
      <if test="record.address != null" >
        address = #{record.address,jdbcType=VARCHAR},
      </if>
      <if test="record.age != null" >
        age = #{record.age,jdbcType=INTEGER},
      </if>
      <if test="record.email != null" >
        email = #{record.email,jdbcType=VARCHAR},
      </if>
      <if test="record.name != null" >
        name = #{record.name,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map" >
    update student
    set id = #{record.id,jdbcType=BIGINT},
      address = #{record.address,jdbcType=VARCHAR},
      age = #{record.age,jdbcType=INTEGER},
      email = #{record.email,jdbcType=VARCHAR},
      name = #{record.name,jdbcType=VARCHAR}
    <if test="_parameter != null" >
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.boot.model.Student" >
    update student
    <set >
      <if test="address != null" >
        address = #{address,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="email != null" >
        email = #{email,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.boot.model.Student" >
    update student
    set address = #{address,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER},
      email = #{email,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR}
    where id = #{id,jdbcType=BIGINT}
  </update>


  <select id="getList" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from student
    where 1=1
    order by id desc
  </select>

  <select id="getCount" resultType="Integer">
    select
    count(1)
    from student
    where 1=1
  </select>
</mapper>

3、编写mapper

package com.boot.mapper.one;

import com.boot.model.Score;

import java.util.List;

/**
 * Created by zhoujh on 2018/6/29.
 */
public interface ScoreMapper {

    int deleteByPrimaryKey(String id);

    int insert(Score record);

    int insertSelective(Score record);

    Score selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(Score record);

    int updateByPrimaryKey(Score record);

    List<Score> getListScore();
}

package com.boot.mapper;

import com.boot.model.Score;

import java.util.List;

/**
 * Created by zhoujh on 2018/6/29.
 */
public interface ScoreMapper {

    int deleteByPrimaryKey(String id);

    int insert(Score record);

    int insertSelective(Score record);

    Score selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(Score record);

    int updateByPrimaryKey(Score record);

    List<Score> getListScore();
}

 

4、编写service

package com.boot.service;

import com.boot.mapper.one.ScoreMapper;
import com.boot.model.Score;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * 学生成绩类service
 * Created by zhoujh on 2018/6/25.
 */
@Service
public class ScoreService {

    @Resource
    private ScoreMapper scoreMapper;


    public List<Score> findStudentScore(){
        return scoreMapper.getListScore();
    }

    public Score findByKeyScore(String id){
        return scoreMapper.selectByPrimaryKey(id);
    }
}

package com.boot.service;

import com.boot.mapper.two.StudentMapper;
import com.boot.model.Student;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * 学生类service
 * Created by zhoujh on 2018/6/25.
 */
@Service
public class StudentService {

    @Resource
    private StudentMapper studentMapper;


    /**
     * 新增学生
     * @param student
     * @return
     */
    public int addStudent(Student student){
        return this.studentMapper.insert(student);
    }


    /**
     * 删除学生
     * @param id
     * @return
     */
    public int deleteStudent(Long id){
        return this.studentMapper.deleteByPrimaryKey(id);
    }


    /**
     * 修改学生信息
     * @param student
     * @return
     */
    public int updateStudent(Student student){
        return this.studentMapper.updateByPrimaryKey(student);
    }

    /**
     * 查询学生信息
     * @param id
     * @return
     */
    public Student selectStudentById(Long id){
        return this.studentMapper.selectByPrimaryKey(id);
    }


    /**
     * 查询所有学生(分页)
     * @return
     */
    public PageInfo<Student> selectAllStudent(int pageNum, int pageSize){
        PageHelper.startPage(pageNum, pageSize);
        List<Student> studentList = this.studentMapper.getList();
        PageInfo result = new PageInfo(studentList);
        return result;
    }

    /**
     * 查询所有学生成绩(分页)
     * @return
     */
    public PageInfo<Student> findAllStudentScore(int pageNum, int pageSize){
        PageHelper.startPage(pageNum, pageSize);
        List<Student> studentList = this.studentMapper.selectStudentScore();
        PageInfo result = new PageInfo(studentList);
        return result;
    }


    /**
     * 查询所有学生总数(用于分页)
     * @return
     */
    public int selectStudentCount(){
        return this.studentMapper.getCount();
    }


    /**
     * 查询所有学生
     * @return
     */
    public List<Student> findAllStudent(){
        return this.studentMapper.getList();
    }
}

 

5、分页工具类

package com.boot.common;

import org.apache.commons.lang3.builder.ToStringBuilder;

/**
 * @Description: 分页工具类
 * @Author: zhoujh
 * @CreateDate: 2018/6/20$ 下午10:15$
 * @Version: 1.0
 */
public class PageParam {
    private int beginLine;       //起始行
    private Integer pageSize = 3;
    private Integer currentPage=0; 	   // 当前页

    public int getBeginLine() {
        return pageSize*currentPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
    }


    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }
}

6、 编写controller

package com.boot.controller;

import com.boot.model.Score;
import com.boot.model.Student;
import com.boot.service.ScoreService;
import com.boot.service.StudentService;
import com.google.gson.Gson;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生controller
 * Created by zhoujh on 2018/6/25.
 */
@RestController
public class StudentController {


    @Resource
    private StudentService studentService;


    @Resource
    private ScoreService scoreService;

    /**
     * 新增学生
     * @param student
     * @return
     */
    @RequestMapping("/add")
    public int saveStudent(Student student){
        return this.studentService.addStudent(student);
    }


    /**
     * 删除学生
     * @param id
     * @return
     */
    @RequestMapping("/delete")
    public int deleteStudent(Long id){
        return this.studentService.deleteStudent(id);
    }


    /**
     * 修改学生信息
     * @param student
     * @return
     */
    @RequestMapping("/update")
    public int updateStudent(Student student){
        return this.studentService.updateStudent(student);
    }

    /**
     * 查询学生信息
     * @param id
     * @return
     */
    @RequestMapping("/select")
    public Student findStudentById(Long id){
        return this.studentService.selectStudentById(id);
    }


    @ResponseBody
    @RequestMapping("/getList")
    public Object findAllStudent(
            @RequestParam(name = "pageNum", required = false, defaultValue = "1")
                    int pageNum,
            @RequestParam(name = "pageSize", required = false, defaultValue = "10")
                    int pageSize){
        return studentService.selectAllStudent(pageNum,pageSize);
    }

    @ResponseBody
    @RequestMapping("/getAllStudentScore")
    public Object findAllStudentScore(
            @RequestParam(name = "pageNum", required = false, defaultValue = "1")
                    int pageNum,
            @RequestParam(name = "pageSize", required = false, defaultValue = "10")
                    int pageSize){
        return studentService.findAllStudentScore(pageNum,pageSize);
    }


    /**
     * 测试多数据获取数据
     * @return
     */
    @ResponseBody
    @RequestMapping("/multiDataSourceQueryDB")
    public String multiDataSourceFindDB(){

        Score score = this.scoreService.findByKeyScore("1");

        System.out.println("成绩"+score.getChineseScore());

        String rs = "";

        List<Student> studentList = this.studentService.findAllStudent();

        List<Score> scoreList = this.scoreService.findStudentScore();

        Gson gson = new Gson();

        String stuStr = gson.toJson(studentList).toString();

        String scoreStr = gson.toJson(scoreList).toString();

        rs += "学生数据库："+stuStr+"\n"+"成绩库数据"+scoreStr;

        return rs;
    }

//    @RequestMapping("/getList")
//    public Page<Student> getList(HttpServletRequest request) {
//        Map<String,String> map = new HashMap<>();
//        map.put("beginLine","0");
//        map.put("pageSize","2");
//        List<Student> users = studentService.selectAllStudent(map);
//        long count = this.studentService.selectStudentCount();
//        Page page = new Page(0,count,1,users);
//        return page;
//    }

}

7、多数据源配置类

package com.boot.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * 多数据源配置读取
 * Created by zhoujh on 2018/6/29.
 */
@Configuration
public class MultiDataSourceConfig {
    @Primary
    @Bean(name = "oneDataSource")
    @ConfigurationProperties("spring.datasource.druid.one")
    public DataSource dataSourceOne(){
        return DruidDataSourceBuilder.create().build();
    }
    @Bean(name = "twoDataSource")
    @ConfigurationProperties("spring.datasource.druid.two")
    public DataSource dataSourceTwo(){
        return DruidDataSourceBuilder.create().build();
    }
}

package com.boot.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.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;

import javax.sql.DataSource;


/**
 * 获取第一个数据源配置
 * Created by zhoujh on 2018/6/29.
 */
@Configuration
@MapperScan(basePackages = "com.boot.mapper.one", sqlSessionTemplateRef  = "oneSqlSessionTemplate")
public class OneDataSourceConfig {

    @Bean(name = "oneSqlSessionFactory")
    @Primary
    public SqlSessionFactory oneSqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "oneTransactionManager")
    @Primary
    public DataSourceTransactionManager oneTransactionManager(@Qualifier("oneDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

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

}
package com.boot.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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 * 获取第二个数据源配置
 * Created by zhoujh on 2018/6/29.
 */
@Configuration
@MapperScan(basePackages = "com.boot.mapper.two", sqlSessionTemplateRef  = "twoSqlSessionTemplate")
public class TwoSourceConfig {

    @Bean(name = "twoSqlSessionFactory")
    public SqlSessionFactory twoSqlSessionFactory(@Qualifier("twoDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "twoTransactionManager")
    public DataSourceTransactionManager twoTransactionManager(@Qualifier("twoDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "twoSqlSessionTemplate")
    public SqlSessionTemplate twoSqlSessionTemplate(@Qualifier("twoSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

 8、启动类

package com.boot;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
public class BootDruidMybatisApplication {

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

9、配置文件

mybatis.type-aliases-package=com.boot.model
server.port=8099


spring.datasource.druid.one.driverClassName = com.mysql.jdbc.Driver
spring.datasource.druid.one.url = jdbc:mysql://localhost:3306/school_score?useUnicode=true&characterEncoding=utf-8
spring.datasource.druid.one.username = root
spring.datasource.druid.one.password = root

spring.datasource.druid.two.driverClassName = com.mysql.jdbc.Driver
spring.datasource.druid.two.url = jdbc:mysql://localhost:3306/school_student?useUnicode=true&characterEncoding=utf-8
spring.datasource.druid.two.username = root
spring.datasource.druid.two.password = root

#pagehelper
pagehelper.helper-dialect=mysql
pagehelper.reasonable=true
pagehelper.support-methods-arguments=true
pagehelper.params=count=countSql
pagehelper.returnPageInfo = check


# 初始化大小，最小，最大链接数
spring.datasource.druid.initial-size=3
spring.datasource.druid.min-idle=3
spring.datasource.druid.max-active=10

# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000

#  监控后台的账户和密码
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin

# 配置StatFilter
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000

10、完整pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.boot</groupId>
	<artifactId>boot-druid-mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>boot-druid-mybatis</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.3.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>

		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.6</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<!-- 分页插件 -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.5</version>
		</dependency>

		<!--引用 Druid-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.10</version>
		</dependency>

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

		<dependency>
			<groupId>com.google.code.gson</groupId>
			<artifactId>gson</artifactId>
			<version>2.8.0</version>
		</dependency>


	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

二、演示增删改查(只演示查询)

1、查询(查询2个库，使用postman测试)

http://localhost:8099/multiDataSourceQueryDB

 

 

好了，到这里 Spring Boot2.0系列教程之MyBatis Druid 多数据源就完成了，读者在实践过程中有问题，评论私信即可，回第一时间回复。

 

zjh_746140129
关注
专栏目录
Spring Boot2.0 Mybatis Druid 多数据源配置及相关问题解决
bobo_bc的博客
07-21 404
注意：需要查询语句中数据为null，并返回其key的，需要在下方数据源配置类中的SqlSessionFactory 中配置 org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration(); configuration.setCa...
Springboot2.0x 多数据源+Durid 使用注解动态切换
qq_39971775的博客
06-03 1552
Spring boot提供了AbstractRoutingDataSource 根据用户定义的规则选择当前的数据源，这样我们可以在执行查询之前，设置使用的数据源。实现可动态路由的数据源，在每次数据库查询操作前执行。它的抽象方法 determineCurrentLookupKey() 决定使用哪个数据源。
搭建基于Springboot2.0,Druid 1.1,Jpa demo
03-12
这是一个基于Spring 2.0，基于Mysql8.0,Springboot2.0,Druid 1.1,Jpa demo搭建的，本资源对应的博客链接https://blog.csdn.net/qq_37925580/article/details/88398066
spring boot2.0 +Mybatis + druid搭建一个最简单的多数据源
Hello World ^—^
11-16 4005
在大部分情况下，搭建单数据源就能够满足需求了，但是特殊情况下也需要使用多数据源，这里就写了一个demo搭建多数据源，注意这里没有使用AbstractRoutingDataSource 来实现动态切换 实操 文件结构 先上pom，这里只截取了关键的依赖 &amp;lt;parent&amp;gt; &amp;lt;groupId&amp;gt;org.springframework.boot&amp;lt;/groupId&amp;gt;..
springboot + mybatis + druid 最简单的框架搭建
qq_36005199的博客
01-02 293
做了很多代码的时候往往让你重新搭建一个最简单的框架的时候就很有自信，卧槽，简单。随便就搭建了架子，后来发现一堆问题！今天给一个springboot + mybatis + druid搭建一个最简单的架子，供初学者使用。 前提：有jdk环境 + gradle构建工具 因为我是用gradle构建的，所以学习的同学可以把gradle变成maven。（gradle 写更少的代码，我用的是gradle ...
SpringBoot 2.0 整合 Druid 连接池
kmswilliam的博客
08-06 233
SpringBoot 2.0 整合 Druid 连接池 首先导入一个 Druid 和日志的 Maven 依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.9</version> </dependency> 还要导入以下依赖, 否则会报下面的错, 因为 Dru
SpringBoot2.0数据访问之整合数据源（Druid）
lyy_sss的博客
08-13 610
1. SQL 1.1 数据源的自动配置 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> 为什么导入JDBC场景，官方不导入驱动？官方不知道我们接下要操作什
spring boot+Druid+mybatis搭建多数据源java项目
02-16
本项目基于Spring Boot、Druid和MyBatis框架，提供了一种灵活的多数据源解决方案。下面将详细阐述这个项目的核心技术和实现步骤。 1. **Spring Boot**： Spring Boot简化了Spring应用的初始设置和配置，通过自动...
SpringBoot 2 MyBatis Druid 多数据源（一）
liwenyang1992的专栏
01-20 285
Spring Boot 2 + MyBatis + Druid 同时连接PostgreSQL和MySQL数据库。 分别查看本地安装的 PostgreSQL中r_user表，MySQL中user两张系统表的列信息数据。SQL参考如下： SELECT info_t.table_catalog AS tableCatalog, info_t.table_schema AS tableSchema, info_t.table_name AS tableName, info_t.column_name
spring boot+mybatis+druid多数据源.zip
07-03
"spring boot+mybatis+druid多数据源.zip"的项目就是针对这样的场景设计的，它结合了Spring Boot、MyBatis和Druid这三个强大的Java框架，实现了对MySQL数据库的动态数据源管理。下面将详细解释这些技术及其在多数据...
springboot2.0整合druid，以及springboot自动装配DataSource原理
热门推荐
鸭鸭的博客
01-30 5万+
1.springboot 目前的推荐版是2.1.2.RELEASE，我们就以当前最新的推荐版为例 2.创建项目，引入对应的jar包，我是以maven的形式 父级的pom &lt;parent&gt; &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt; &lt;artifactId&gt;spri...
springboot 2.0整合mybatis+druid 详解
weixin_43892890的博客
12-26 718
springboot据说与1.x版本区别很大，我也是刚刚开始学习springboot，并没有接触过1.x的版本，直接开始学习springboot 2.1.1的版本，小版本之间的差别应该不是很大。这几天开始整合mybatis，在网上找的教程发现多多少少都有些问题，中间也遇到了很多坑，写下这篇文章帮助其他朋友。 1.修改pom文件 在pom文件中导入以下几个坐标： //mybatis的启动器，我这里...
DruidSpringBootStarter将帮助你在SpringBoot中使用Druid
08-07
Druid Spring Boot Starter 将帮助你在 Spring Boot 中使用 Druid
Spring Boot 2.0.5 配置Druid数据库连接池
学亮编程手记
09-03 346
配置文件: 配置类: pom文件:
SpringBoot 2 MyBatis Druid 多数据源（二）
liwenyang1992的专栏
09-17 236
SpringBoot 2 MyBatis Druid 多数据源（二） 建库建表等准备请看：https://blog.csdn.net/liwenyang1992/article/details/120311490 Druid使用文档：https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98 （关注 32. 如何在 Spring Boot 中配置数据库密码加密？） Druid加密指导：https://github.co
SpringBoot2.0.6整合Druid
andwey的博客
04-03 278
1.pom.xml文件引入依赖 <!--Druid关系型数据库连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1....
Springboot+Mybatis+Druid多数据源配置
01-14
多数据源配置,使用了数据库连接池,并且是多数据源,分也不要多了,2分,有什么疑问可以指出,或者不足也可以一起学习
SpringBoot+Druid+MyBatis实现多数据源
睁眼看世界
03-18 1413
我们使用动态的方式进行多数据源的配置，更加灵活方便。 1. 配置文件 spring: datasource: druid: one: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdb...
springboot + mybatis + druid + 多数据源
请叫我猿叔叔的博客
08-17 4万+
一. 简介 俩个数据库db1,db2， db1数据库的mapper.xml和db2数据库的mapper.xml分别放到不同的目录下, 通过给不同的目录配置不同的数据源，并分别监控各自的事务。 二. sql脚本 db1数据库的user表： CREATE TABLE `user` ( `id` int(11) NOT NULL, `name...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值