mybatis调用mysql存储过程_秒会mybatis调用存储过程（MySQL）

一、简介

有的时候，我们不方便自己写SQL，而是只能调用别人提供的存储过程，那如何使用mybatis调用存储过程呢？

二、示例步骤(MySQL)

2.1 准备一张表

DROP TABLE IF EXISTS cus_device;

CREATE TABLE cus_device (

device_sn varchar(20) NOT NULL COMMENT '设备编号',

device_cat_id int(1) DEFAULT NULL COMMENT '设备类型',

device_name varchar(64) DEFAULT NULL COMMENT '设备名称',

device_type varchar(64) DEFAULT NULL COMMENT '设备型号',

PRIMARY KEY (device_sn)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1

2

3

4

5

6

7

8

DROPTABLEIFEXISTScus_device;

CREATETABLEcus_device(

device_snvarchar(20)NOTNULLCOMMENT'设备编号',

device_cat_idint(1)DEFAULTNULLCOMMENT'设备类型',

device_namevarchar(64)DEFAULTNULLCOMMENT'设备名称',

device_typevarchar(64)DEFAULTNULLCOMMENT'设备型号',

PRIMARYKEY(device_sn)

)ENGINE=InnoDBDEFAULTCHARSET=utf8;

2.2 准备一个存储过程

以设备的名称为输入参数，以统计到设备的总数为输出参数

DROP PROCEDURE IF EXISTS countDevicesName;

DELIMITER ;;

CREATE PROCEDURE countDevicesName(IN dName VARCHAR(12),OUT deviceCount INT)

BEGIN

SELECT COUNT(*) INTO deviceCount FROM cus_device WHERE device_name = dName;

END

;;

DELIMITER ;

1

2

3

4

5

6

7

8

9

DROPPROCEDUREIFEXISTScountDevicesName;

DELIMITER;;

CREATEPROCEDUREcountDevicesName(INdNameVARCHAR(12),OUTdeviceCountINT)

BEGIN

SELECTCOUNT(*)INTOdeviceCountFROMcus_deviceWHEREdevice_name=dName;

END

;;

DELIMITER;

2.3 mybatis-config.xml

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

2.4 CusDevice.java

public class DevicePOJO{

private String devoceName;//设备名称

private String deviceCount;//设备总数

public String getDevoceName() {

return devoceName;

}

public void setDevoceName(String devoceName) {

this.devoceName = devoceName;

}

public String getDeviceCount() {

return deviceCount;

}

public void setDeviceCount(String deviceCount) {

this.deviceCount = deviceCount;

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

publicclassDevicePOJO{

privateStringdevoceName;//设备名称

privateStringdeviceCount;//设备总数

publicStringgetDevoceName(){

returndevoceName;

}

publicvoidsetDevoceName(StringdevoceName){

this.devoceName=devoceName;

}

publicStringgetDeviceCount(){

returndeviceCount;

}

publicvoidsetDeviceCount(StringdeviceCount){

this.deviceCount=deviceCount;

}

}

2.5 DeviceDAO的实现

package com.producedemo;

public interface DeviceDAO {

/**

* 调用存储过程 获取设备的总数

* @param devicePOJO

*/

public void count(DevicePOJO devicePOJO);

}

1

2

3

4

5

6

7

8

9

10

11

packagecom.producedemo;

publicinterfaceDeviceDAO{

/**

* 调用存储过程 获取设备的总数

* @param devicePOJO

*/

publicvoidcount(DevicePOJOdevicePOJO);

}

2.6 Mapper的实现

device_sn, device_name,device_mac

statementType="CALLABLE">

call countDevicesName(

#{devoceName,mode=IN,jdbcType=VARCHAR},

#{deviceCount,mode=OUT,jdbcType=INTEGER});

]]>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

device_sn,device_name,device_mac

statementType="CALLABLE">

callcountDevicesName(

#{devoceName,mode=IN,jdbcType=VARCHAR},

#{deviceCount,mode=OUT,jdbcType=INTEGER});

]]>

注意：statementType=”CALLABLE” 必须为CALLABLE，告诉MyBatis去执行存储过程， 否则会报错

Exception in thread “main” org.apache.ibatis.exceptions.PersistenceException

mode=IN 输入参数 mode=OUT输出参数 jdbcType为数据库定义的字段类型。

这样写 Mybatis会帮助我们自动回填输出的deviceCount的值。

2.7 测试

package com.producedemo;

import java.io.IOException;

import java.io.Reader;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**

* MyBatis 执行存储过程

* @author Administrator

*

*/

public class TestProduce {

private static SqlSessionFactoryBuilder sqlSessionFactoryBuilder;

private static SqlSessionFactory sqlSessionFactory;

private static void init() throws IOException {

String resource = "mybatis-config.xml";

Reader reader = Resources.getResourceAsReader(resource);

sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();

sqlSessionFactory = sqlSessionFactoryBuilder.build(reader);

}

public static void main(String[] args) throws Exception {

testCallProduce();

}

/**

* @throws IOException

*/

private static void testCallProduce() throws IOException {

init();

SqlSession session= sqlSessionFactory.openSession();

DeviceDAO deviceDAO = session.getMapper(DeviceDAO.class);

DevicePOJO device = new DevicePOJO();

device.setDevoceName("设备名称");

deviceDAO.count(device);

System.out.println("获取"+device.getDevoceName()+"设备的总数="+device.getDeviceCount());

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

packagecom.producedemo;

importjava.io.IOException;

importjava.io.Reader;

importorg.apache.ibatis.io.Resources;

importorg.apache.ibatis.session.SqlSession;

importorg.apache.ibatis.session.SqlSessionFactory;

importorg.apache.ibatis.session.SqlSessionFactoryBuilder;

/**

* MyBatis 执行存储过程

* @author Administrator

*

*/

publicclassTestProduce{

privatestaticSqlSessionFactoryBuildersqlSessionFactoryBuilder;

privatestaticSqlSessionFactorysqlSessionFactory;

privatestaticvoidinit()throwsIOException{

Stringresource="mybatis-config.xml";

Readerreader=Resources.getResourceAsReader(resource);

sqlSessionFactoryBuilder=newSqlSessionFactoryBuilder();

sqlSessionFactory=sqlSessionFactoryBuilder.build(reader);

}

publicstaticvoidmain(String[]args)throwsException{

testCallProduce();

}

/**

* @throws IOException

*/

privatestaticvoidtestCallProduce()throwsIOException{

init();

SqlSessionsession=sqlSessionFactory.openSession();

DeviceDAOdeviceDAO=session.getMapper(DeviceDAO.class);

DevicePOJOdevice=newDevicePOJO();

device.setDevoceName("设备名称");

deviceDAO.count(device);

System.out.println("获取"+device.getDevoceName()+"设备的总数="+device.getDeviceCount());

}

}

三、结果

可以看到mybatis调用存储过程测试成功了。

浏览量:

994

0