SpringBoot整合MyBatis联表查询完整过程

最新推荐文章于 2024-05-19 10:45:02 发布

Xeon_CC

最新推荐文章于 2024-05-19 10:45:02 发布

阅读量2.6k

点赞数 3

分类专栏： SpringBoot 文章标签： mysql MyBatis 联合查询配置

本文链接：https://blog.csdn.net/Xeon_CC/article/details/107219206

版权

SpringBoot 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.创建SpringBoot工程

勾选以下项目，版本选择2.2.8

2.导入依赖

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

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

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

        <!--加载mybatis 整合springboot-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>

        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--引入内嵌的Tomcat对JSP的解析包-->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
        </dependency>

        <!--servlet依赖jar包-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
        </dependency>

        <!--jsp依赖jar包-->
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>javax.servlet.jsp-api</artifactId>
            <version>2.3.1</version>
        </dependency>

        <!--jstl标签依赖的jar包-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>

3.加入MyBatis插件

在<build>标签内的<plugins>标签内加入plugin，即MyBatis插件，代码如下

            <!--mybatis插件-->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.6</version>
                <configuration>
                    <configurationFile>GeneratorMapper.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>

在<build>标签内加入resources配置，确保映射文件被加载

        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>

4.配置application.properties文件

server.port=9966
###指定sql映射文件的位置
mybatis.mapper-locations=classpath:com/example/mapper/*.xml

###防止中文乱码
server.servlet.encoding.charset=UTF-8
server.servlet.encoding.enabled=true
server.servlet.encoding.force=true

###连接mysql
spring.datasource.username=root
spring.datasource.password=1001001
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis_test?useUnicode=true&characterEncoding=utf8&useSSL=false

5.在工程的根目录下添加GeneratorMapper.xml文件，并执行逆向工程，自动生成sql映射文件以及dao接口

首先在工程目录下创建mapper和model包

在工程根目录创建GeneratorMapper.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>
    <!--在本地放个mysql驱动的jar包，必须要，因为我这台是mac电脑，所有路劲是没有盘符的的-->
    <classPathEntry location="/Users/driver/mysql-connector-java-5.1.43.jar"></classPathEntry>

    <context id="DB2Tables" targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <!-- 配置数据库连接信息 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis_test"
                        userId="root"
                        password="1001001">
        </jdbcConnection>

        <javaTypeResolver>
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!-- 指定javabean生成的位置 -->
        <javaModelGenerator targetPackage="com.example.model" targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!-- 指定sql映射文件生成的位置 -->
        <sqlMapGenerator targetPackage="com.example.mapper"  targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>

        <!-- 指定dao接口生成的位置，mapper接口 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.example.mapper" targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!-- 指定每个表的生成策略 -->
        <table tableName="emp" domainObjectName="Emp"></table>
        <table tableName="dept" domainObjectName="Dept"></table>

    </context>
</generatorConfiguration>

数据库结构：

双击它，执行逆向工程

6.编辑EmpMapper.xml文件

在合适的位置上添加以下的代码：

说明：这是自定义返回字段，返回字段多了一个，使用association标签来配置另一个表的字段

 <resultMap id="WithDeptResultMap" type="com.example.model.Emp">
    <id column="emp_id" jdbcType="INTEGER" property="empId" />
    <result column="emp_name" jdbcType="VARCHAR" property="empName" />
    <result column="emp_age" jdbcType="INTEGER" property="empAge" />
    <result column="emp_gender" jdbcType="CHAR" property="empGender" />
    <result column="dept_id" jdbcType="INTEGER" property="deptId" />
    <association property="dept" javaType="com.example.model.Dept">
      <id column="dept_id" property="deptId"/>
      <result column="dept_name" property="deptName"/>
    </association>
  </resultMap>

说明：指定返回的字段有哪些，e表示emp表，d表示dept表，为什么可以简写？这个与select查询配置有关，往下看就知道了

  <sql id="WithDept_Column_List">
  	e.emp_id, e.emp_name, emp_age ,e.emp_gender, e.dept_id, d.dept_id, d.dept_name
  </sql>

说明：以主键联表查询

  <select id="selectByPrimaryKeyWithDept"  resultMap="WithDeptResultMap">
    select
    <include refid="WithDept_Column_List" />
    from emp e
    left join dept d on e.`dept_id`=d.`dept_id`
    where emp_id = #{empId,jdbcType=INTEGER}
  </select>

说明：全查联表查询，这个EmpExamp类可以用来全查

  <select id="selectByExampleWithDept" parameterType="com.example.model.EmpExample" resultMap="WithDeptResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="WithDept_Column_List" />
    from emp e
    left join dept d on e.`dept_id`=d.`dept_id`
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>

7.在model包的Emp类中，新增一个属性dept，记得加上getter setter方法

8.在mapper包下的EmpMapper接口中，新增联表查询的两种方法

    //联表查询
    List<Emp> selectByExampleWithDept(EmpExample example);

    Emp selectByPrimaryKeyWithDept(Integer empId);

9.在主启动类上加上两行注解

@MapperScan(basePackages = "com.example.mapper")
@EnableTransactionManagement //开启springboot事务支持

10.创建controller包，并创建MyBatisController类，测试它

package com.example.controller;

import com.example.mapper.EmpMapper;
import com.example.model.EmpExample;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;


@Controller
public class MyBatisController {


    @Autowired
    EmpMapper empMapper;

    @GetMapping("/findAllEmp")
    public String findAllEmp(Model model){
        EmpExample empExample = new EmpExample();
        empExample.setOrderByClause("emp_id ASC");
        model.addAttribute("allEmp",empMapper.selectByExampleWithDept(empExample));
        return "index";
    }
}

11.在templates包下创建index.html，并加上以下代码

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

tsdkljfhius
<p th:each="Emp : ${allEmp}">
    <span th:text="${Emp.getEmpId()}"></span>
    <span th:text="${Emp.getEmpName()}"></span>
    <span th:text="${Emp.getEmpAge()}"></span>
    <span th:text="${Emp.getEmpGender()}"></span>
    <span th:text="${Emp.getDeptId()}"></span>
    <span th:text="${Emp.getDept().getDeptName()}"></span>
</p>
</body>
</html>

12.测试

联表查询成功

Xeon_CC

关注

3
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot整合MyBatis联表查询完整过程

1.创建SpringBoot工程勾选以下项目，版本选择2.2.82.导入依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <opti...
复制链接

扫一扫

专栏目录