一、简介
有的时候,我们不方便自己写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