springboot+mybatis調用oracle存儲過程

1 存儲過程參數為VARCHAR

代碼邏輯：controller層定義實體類對象entity，并entity.set給存儲過程的輸入參數指派，把指派後的實體類通過service層傳到dao層，然後通過dao層調用存儲過程

1-1 存儲過程參數

IN_STR IN VARCHAR,
OUT_STR OUT VARCHAR

View Code

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";
    }
}

View Code

1-3 service層

public HelloProEntity GetTestPro(HelloProEntity testproentity) {
    return dao.GetTestPro(testproentity);
}

View Code

1-4 dao層

public HelloProEntity GetTestPro(HelloProEntity testproentity);

View Code

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;
    }

}

View Code

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>

View Code

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

View Code

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;
}

View Code

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;
    }

}

View Code

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>

View Code