springboot+mybatis调用oracle存储过程
1 存储过程参数为VARCHAR
代码逻辑:controller层定义实体类对象entity,并entity.set给存储过程的输入参数赋值,把赋值后的实体类通过service层传到dao层,然后通过dao层调用存储过程
1-1 存储过程参数
IN_STR IN VARCHAR, OUT_STR OUT VARCHAR
1-2 controller层
@RequestMapping("/TESTPRO") @ResponseBody public String TESTPRO() { // 执行oracle存储过程 // 实体类中把输入参数输出参数都写上 //少写参数会报错:(wrong number or types of arguments in call to '存储过程') HelloProEntity entity = new HelloProEntity(); entity.setIN_STR("1");// 配置输入参数 controllerService.GetTestPro(entity);// 把带输入参数的实体传到Service层,Service层调用Dao层,Dao层寻找实体对应的sql try { String str = entity.getOUT_STR(); return str; } catch (Exception e) { return "null"; } }
1-3 service层
public HelloProEntity GetTestPro(HelloProEntity testproentity) { return dao.GetTestPro(testproentity); }
1-4 dao层
public HelloProEntity GetTestPro(HelloProEntity testproentity);
1-5 实体类
public class HelloProEntity implements Serializable { private static final long serialVersionUID = -6556793741331167103L; private String IN_STR; private String OUT_STR; public String getIN_STR() { return IN_STR; } public void setIN_STR(String iN_STR) { IN_STR = iN_STR; } public String getOUT_STR() { return OUT_STR; } public void setOUT_STR(String oUT_STR) { OUT_STR = oUT_STR; } }
1-6 mapper
<select id="GetTestPro" parameterType="com.kh.zzkanban1.Entyties.HelloProEntity" useCache="false" statementType="CALLABLE"> <![CDATA[ {call JCG_TEST( #{IN_STR,mode=IN,jdbcType=VARCHAR}, #{OUT_STR,mode=OUT,jdbcType=VARCHAR} )} ]]> </select>
2 存储过程参数为DATE
基于第1点的配置,service层与dao层不需要改动,改动controller、实体类、mapper
主要注意的是:存储过程输入参数输出参数为DATE格式时,mapper定义jdbcType为DATE,实体类要用Timestamp格式定义属性,否则时分秒会变成00:00:00。
输出得到时间戳后,再用new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(entity.getM_GROUP_END())转成字符串格式的时间。
2-1 存储过程参数
M_DATETIME IN DATE, M_SEGMENT_BEGIN OUT DATE, M_SEGMENT_END OUT DATE, M_GROUP_BEGIN OUT DATE, M_GROUP_END OUT DATE, M_SEGMENT_NO OUT INTEGER, M_GROUP_CODE OUT VARCHAR2, RES OUT VARCHAR2
2-1 controller层
@RequestMapping("/GETPRO") @ResponseBody public PGetTimeSlotInfoEntity TESTPRO() throws SQLException { PGetTimeSlotInfoEntity entity = new PGetTimeSlotInfoEntity(); SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String time = df.format(new Date()); Timestamp ts = Timestamp.valueOf(time);//获取当前时间的时间戳 entity.setM_DATETIME(ts);//以时间戳给输入的DATE参数赋值 service.GetPro(entity); return entity; }
2-2 实体类
public class PGetTimeSlotInfoEntity implements Serializable { private static final long serialVersionUID = -6556793741331167103L; private Timestamp M_DATETIME; private Timestamp M_SEGMENT_BEGIN;// 这里不用Date,应为传不了时分秒 private Timestamp M_SEGMENT_END; private Timestamp M_GROUP_BEGIN; private Timestamp M_GROUP_END; private String M_SEGMENT_NO; private String M_GROUP_CODE; private String RES; public Timestamp getM_DATETIME() { return M_DATETIME; } public void setM_DATETIME(Timestamp m_DATETIME) { M_DATETIME = m_DATETIME; } public Date getM_SEGMENT_BEGIN() { return M_SEGMENT_BEGIN; } public void setM_SEGMENT_BEGIN(Timestamp m_SEGMENT_BEGIN) { M_SEGMENT_BEGIN = m_SEGMENT_BEGIN; } public Date getM_SEGMENT_END() { return M_SEGMENT_END; } public void setM_SEGMENT_END(Timestamp m_SEGMENT_END) { M_SEGMENT_END = m_SEGMENT_END; } public Timestamp getM_GROUP_BEGIN() { return M_GROUP_BEGIN; } public void setM_GROUP_BEGIN(Timestamp m_GROUP_BEGIN) { M_GROUP_BEGIN = m_GROUP_BEGIN; } public Timestamp getM_GROUP_END() { return M_GROUP_END; } public void setM_GROUP_END(Timestamp m_GROUP_END) { M_GROUP_END = m_GROUP_END; } public String getM_SEGMENT_NO() { return M_SEGMENT_NO; } public void setM_SEGMENT_NO(String m_SEGMENT_NO) { M_SEGMENT_NO = m_SEGMENT_NO; } public String getM_GROUP_CODE() { return M_GROUP_CODE; } public void setM_GROUP_CODE(String m_GROUP_CODE) { M_GROUP_CODE = m_GROUP_CODE; } public String getRES() { return RES; } public void setRES(String rES) { RES = rES; } }
2-3 mapper
<select id="GetPro" parameterType="com.kh.zzkanban1.Entyties.PGetTimeSlotInfoEntity" useCache="false" statementType="CALLABLE"> <![CDATA[ {call P_GET_TIME_SLOT_INFO( #{M_DATETIME,mode=IN,jdbcType=DATE}, #{M_SEGMENT_BEGIN,mode=OUT,jdbcType=DATE}, #{M_SEGMENT_END,mode=OUT,jdbcType=DATE}, #{M_GROUP_BEGIN,mode=OUT,jdbcType=DATE}, #{M_GROUP_END,mode=OUT,jdbcType=DATE}, #{M_SEGMENT_NO,mode=OUT,jdbcType=INTEGER}, #{M_GROUP_CODE,mode=OUT,jdbcType=VARCHAR}, #{RES,mode=OUT,jdbcType=VARCHAR} )} ]]> </select>