spring-boot整合mybatis的两种方式（基于oracle的存储过程返回结果集）

spring-boot整合druid

spring-boot在整合好druid后，接着来整合mybatis.

整合mybatis有两种方式，注解版和配置文件版

首先我们在pom.xml引入mybatis的相关依赖

  <!--Mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>

针对oracle，scott用户下的emp表，先建一个对应的javabean

package com.oracle.bean;

import org.springframework.context.annotation.Bean;

import javax.xml.crypto.Data;
import java.sql.Date;


public class emp {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private double sal;
    private double comm;
    private int deptno;

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public double getSal() {
        return sal;
    }

    public void setSal(double sal) {
        this.sal = sal;
    }

    public double getComm() {
        return comm;
    }

    public void setComm(double comm) {
        this.comm = comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public emp() {

    }

    @Override
    public String toString() {
        return "emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }

}

 注解版：（sql语句与方法名一体化）

 （1）在java包的的com包下，新建一个mapper包，在mapper包下新建一个empMapper接口

//指定这是一个操作数据库的mapper
@Mapper
public interface empMapper {


    //普通查询
    @Select("select * from emp where empno=#{empno}")
    public emp getEmpbyDeptno(@Param("empno") int empno);

    //调用存储过程，返回一个结果集
    @Select("call queryEmpInfo(#{map.eno,mode=IN},#{map.pename,mode=OUT,jdbcType=VARCHAR}," +
            "#{map.psal,mode=OUT,jdbcType=DECIMAL},#{map.pjob,mode=OUT,jdbcType=VARCHAR})")
    @Options(statementType= StatementType.CALLABLE)
    void testProcedure(@Param("map") Map map);


}

 （2）在java包的的com包下，新建一个controller包，在controller包下新建一个empController类

@RestController
public class empController {

    @Autowired
    empMapper empMapper;

    
    @GetMapping("/emp/{empno}")
    public emp getEmp(@PathVariable("empno") int empno){
        return empMapper.getEmpbyDeptno(empno);
    }

  

    //@GetMapping("/emp1/{eno}")
    public Map testProcedure(@PathVariable("eno") int eno) {
         Map map = new HashMap();
         map.put("eno",eno);
         empMapper.testProcedure(map);
         return map;
    }

   
}

 

配置文件版：（sql语句与方法名分离）

（1）在mapper包下新建一个empMapper1接口

//指定这是一个操作数据库的mapper
@Mapper
public interface empMapper1 {

    public emp getEmpByEmpno(@Param("empno") Integer empno);

    public void getEmpList(@Param("map") Map map);
}

（2）在source包下，新建新建一个Mybatis包，在MyBatis包下新建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>

</configuration>

（3） 在MyBatis包下新建mapper包，在mapper新建一个empMapper1.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">

<!-- namespace：需要和mapper接口的全限定名一致 -->
<mapper namespace="com.oracle.mapper.empMapper1">

    <resultMap id="empList" type="com.oracle.bean.emp">
        <result column="ENAME" property="ename" />
        <result column="JOB" property="job" />
        <result column="MGR" property="mgr" />
        <result column="HIREDATE" property="hiredate" />
        <result column="SAL" property="sal" />
        <result column="DEPTNO" property="deptno" />
    </resultMap>

    <select id="getEmpByEmpno" resultType="com.oracle.bean.emp">
        select * from emp where empno=#{empno}
    </select>

    //调用存储过程返回游标结果集
    <select id="getEmpList" statementType="CALLABLE" parameterType="java.util.Map">
        {call mypackage.queryEmpList(#{map.dno,mode=IN},#{map.empList,mode=OUT,jdbcType=CURSOR,resultMap=empList})}
    </select>
</mapper>

（4）在application.yml文件中 指定这个两个映射文件的位置

mybatis:
  config-location: classpath:Mybatis/mybatis-config.xml
  mapper-locations: classpath:Mybatis/mapper/*.xml

（5）在新建一个empController类进行调用

@RestController
public class empController {

    
    @Autowired
    empMapper1 empMapper1;

   

    @GetMapping("/emp2/{empno}")
    public emp getEmp1(@PathVariable("empno") int empno){
        return empMapper1.getEmpByEmpno(empno);
    }



    @GetMapping("/emp3/{dno}")
    public Map testProcedure1(@PathVariable("dno") int dno) {
        Map map = new HashMap();
        map.put("dno",dno);
        map.put("empList",null);
        empMapper1.getEmpList(map);
        return map;
    }

}

   注：如果不想在Mapper文件上注上@Mapper的字样，也可以在主程序注上@MapperScan(value = "com/oracle/mapper")，对mapper包下的mapper文件进行扫描