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>

巴特西

springboot+mybatis调用oracle存储过程

最新文章

热门文章