在mybatis中调用mysql存储过程和存储函数

时间：2024-03-13 11:05:47

 

  • 数据库表

1、tb_base_company

在mybatis中调用mysql存储过程和存储函数

 

2、tb_base_manager

在mybatis中调用mysql存储过程和存储函数

 

  • 创建存储过程、存储函数，并测试

/*insert存储过程，返回数据主键*/

CREATE PROCEDURE insert_com_by_code_name (OUT com_pkid INT,IN com_code VARCHAR(20),IN com_name VARCHAR(100))

BEGIN

       INSERT INTO tb_base_company (company_code,company_name) VALUES (com_code,com_name);

       SET com_pkid = LAST_INSERT_ID();

END

/*delete存储过程。

（参数名和字段名千万别写成一样的，否则会删库，能不能跑路就看你的本事了）*/

CREATE PROCEDURE delete_com_by_id (IN com_pkid INT)

BEGIN

       DELETE FROM tb_base_company where pkid = com_pkid;

END

/* select存储过程*/

CREATE PROCEDURE select_com_by_id (IN com_pkid INT)

READS SQL DATA

BEGIN

       SELECT * FROM tb_base_company c WHERE pkid = com_pkid;

END

/*求和存储函数*/

CREATE FUNCTION add_decimal_param (num1 DECIMAL(10,2),num2 DECIMAL(10,2))

returns DECIMAL(10,2)

BEGIN

       RETURN num1+num2;

END

/*select count(1) 存储过程*/

CREATE PROCEDURE com_count_by_managername (IN managername VARCHAR(20),OUT com_count INT)

READS SQL DATA

BEGIN

       SELECT count(c.pkid) INTO com_count

       FROM tb_base_company c

       LEFT JOIN tb_base_manager m

       ON c.company_code = m.company_code

       WHERE INSTR(m.manager_name,managername)>0;

END

 

-- 测试

-- set @com_code='com008',@com_name='企业008';  CALL insert_com_by_code_name(@com_pkid,@com_code,@com_name);  select @com_pkid com_pkid;

-- set @com_pkid='1';  CALL delete_com_by_id(@com_pkid);

-- set @com_pkid='2';  CALL select_com_by_id(@com_pkid);

-- select add_decimal_param(1.1,2.2);

-- set @com_name='张';  CALL com_count_by_name(@com_name,@com_count);  select @com_count com_count;

 

  •  

1、mappper.xml

  <!-- 调用存储过程 -->

  <!-- 返回结果会放在参数map中 -->

  <insert id="insertProcedure" parameterType="map" statementType="CALLABLE">

    {call insert_com_by_code_name(#{pkid,mode=OUT,jdbcType=INTEGER},#{companyCode,mode=IN,jdbcType=VARCHAR},#{companyName,mode=IN,jdbcType=VARCHAR})}

  </insert>

  <!--  -->

  <delete id="deleteByPrimaryKeyProcedure" parameterType="java.lang.Integer" statementType="CALLABLE">

    {call delete_com_by_id(#{pkid,mode=IN,jdbcType=INTEGER})}

  </delete>

  <!--  -->

  <select id="selectByIdProcedure" parameterType="java.lang.Integer" resultMap="BaseResultMap" statementType="CALLABLE">

    {call select_com_by_id(#{pkid,mode=IN,jdbcType=INTEGER})}

  </select>

 

  <!-- 调用存储函数，返回结果会放在参数map中。

注意：不要写成jdbcType=DECIMAL(10,2) -->

  <select id="selectAddDecimalProcedure" parameterType="map" statementType="CALLABLE">

    {#{resultNum,mode=OUT,jdbcType=DECIMAL}=call add_decimal_param(#{num1,mode=IN,jdbcType=DECIMAL},#{num2,mode=IN,jdbcType=DECIMAL})}

  </select>  

 

2、mapper.java

    int deleteByPrimaryKeyProcedure(Integer pkid);

    int insertProcedure(Map<String, Object> map);

BaseCompany selectByIdProcedure(Integer pkid);

void selectAddDecimalProcedure(Map<String, Object> map);

 

3、serviceImpl.java

    @Override

    public int insertProcedure(Map<String, Object> map) throws Exception {

       int insertResult = companyMapper.insertProcedure(map);

       return insertResult;

    }

 

    @Override

    public int deleteByPrimaryKeyProcedure(Integer pkid) throws Exception {

       int deleteResult = companyMapper.deleteByPrimaryKeyProcedure(pkid);

       return deleteResult;

    }

 

    @Override

    public BaseCompany selectByIdProcedure(Integer pkid) throws Exception {

       BaseCompany company = companyMapper.selectByIdProcedure(pkid);

       return company;

    }

 

    @Override

    public void selectAddDecimalProcedure(Map<String, Object> map) {

       companyMapper.selectAddDecimalProcedure(map);

    }

 

4、serviceTest.java

    @Test

    public void testInsertProcedure() throws Exception {

       String companyCode = "com008";String companyName = "企业008";

       Map<String, Object> map = new HashMap<String, Object>();

       map.put("companyCode", companyCode);

       map.put("companyName", companyName);

       int insertResult = companyService.insertProcedure(map);

        logger.warn("insertResult={};map={}",insertResult,JSON.toJSON(map));

    }

 

    @Test

    public void testDeleteByPrimaryKeyProcedure() throws Exception {

       Integer pkid = 67;

       int deleteResult = companyService.deleteByPrimaryKeyProcedure(pkid);

       logger.warn("deleteResult={}",deleteResult);

    }

 

    @Test

    public void testSelectByIdProcedure() throws Exception {

       Integer pkid = 6;

       BaseCompany company = companyService.selectByIdProcedure(pkid);

       logger.warn("company={}",JSON.toJSON(company));

    }

   

    @Test

    public void testSelectAddDecimalProcedure() throws Exception {

       Map<String, Object> map = new HashMap<String, Object>();

       map.put("num1", 1.2);

       map.put("num2", 2.6);

       companyService.selectAddDecimalProcedure(map);

       logger.warn("map={}",JSON.toJSON(map));

    }

   

5、测试结果

testInsertProcedure()测试结果如下：

insertResult=1;map={"companyCode":"com008","pkid":76,"companyName":"企业008"}

 

testDeleteByPrimaryKeyProcedure()测试结果如下：

deleteResult=1

 

testSelectByIdProcedure()测试结果如下：

company={"companyCode":"com006","firstManagerName":"","certificateCode":"","pkid":6,"regionCode":"","firstManagerTelephone":"","companyAddress":"","companyName":"企业006","groupCode":""}

 

testSelectAddDecimalProcedure()测试结果如下：

map={"num1":1.2,"resultNum":3.80,"num2":2.6}

 

 

 

相关文章