SpringBoot中使用Mybatis调用Oracle存储过程
1. 添加Maven依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
2. Yaml中配置好Db、Mybatis相关配置
spring:
datasource:
url: jdbc:oracle:thin:@127.0.0.1:1521/orcl
username: system
password: 123456
driver-class-name: oracle.jdbc.OracleDriver
mybatis-plus:
configuration:
map-underscore-to-camel-case: false
mapper-locations: classpath*:/mapper/**/*.xml
type-aliases-package: com.mediinfo.entity
3. 测试的 entity实体类
@SuppressWarnings("serial")
@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel("人员信息")
public class GyBrjbxxk extends Model<GyBrjbxxk> {
@TableId(value = "JZKH")
@ApiModelProperty(value = "就诊卡号")
private String jzkh;
@ApiModelProperty(value = "姓名")
private String xm;
@ApiModelProperty(value = "性别")
private String xb;
@Override
protected Serializable pkVal() {
return this.jzkh;
}
}
4. Mapper(Dao)接口
@Mapper
public interface GyBrjbxxkDao extends BaseMapper<GyBrjbxxk> {
void findDeptNameById(Map<String,Object> map);
void findAllDeptProcedure(Map<String,Object> map);
}
5. GyBrjbxxkDao.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" >
<mapper namespace="com.mediinfo.dao.GyBrjbxxkDao">
<resultMap id="deptResultMap" type="com.mediinfo.entity.GyBrjbxxk">
</resultMap>
<select id="findDeptNameById" statementType="CALLABLE" parameterType="map">
<![CDATA[
{call findDeptNameById(
#{deptNo,mode=IN,jdbcType=VARCHAR},
#{d_Name,mode=OUT,jdbcType=VARCHAR}
)}
]]>
</select>
<select id="findAllDeptProcedure" statementType="CALLABLE">
<![CDATA[
call findAllDeptProcedure(
#{deptList, mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=deptResultMap})
]]>
</select>
</mapper>
6. Oracle中的存储过程
create or replace procedure findDeptNameById(v_deptno in String, v_dname out String) is
begin
select xm into v_dname from gy_brjbxxk where jzkh = v_deptno;
end findDeptNameById;
create or replace procedure findAllDeptProcedure(v_dept out sys_refcursor) is
begin
open v_dept for select * from gy_brjbxxk where rownum <= 100;
end findAllDeptProcedure;
7. 测试类Mybatisplus_OracleProcedure_自测通过
package com.mediinfo.healthtzplatform;
import com.alibaba.fastjson.JSONArray;
import com.mediinfo.dao.GyBrjbxxkDao;
import com.mediinfo.entity.GyBrjbxxk;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SpringBootTest
class Mybatisplus_OracleProcedure {
@Resource
GyBrjbxxkDao gyBrjbxxkDao;
@Test
public void testFindDeptNameById() {
HashMap<String, Object> map = new HashMap<>();
map.put("deptNo","A10014599");
gyBrjbxxkDao.findDeptNameById(map);
System.out.println(map.get("d_Name"));
}
@Test
public void testFindAllDeptProcedure() {
Map<String, Object> param = new HashMap<String, Object>();
gyBrjbxxkDao.findAllDeptProcedure(param);
List<GyBrjbxxk> list = (List<GyBrjbxxk>) param.get("deptList");
JSONArray jsonArray = (JSONArray) JSONArray.toJSON(list);
System.out.println(jsonArray);
}
}