Mybatis 通过注解进行数据库操作

java 所需要引入的jar

		<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.2</version>
			<scope>compile</scope>
		</dependency>

执行Oracle 存储过程返回cursor 类型。

procedure spec

    PROCEDURE p_get_letter_detail(in_ord_letter_code        in number,
                                  out_cursor                out sys_refcursor,
                                  out_status                out varchar2,
                                  out_msg                   out varchar2
                                  )

java 代码

package com.xxxxx.dao;

import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.StatementType;
import org.springframework.stereotype.Repository;

import java.util.Map;

@Repository
@Mapper
public interface OrdMybatisDao {
    //get Letter Ready For Gen
        @Select("{ call pkg_ord_letter.p_get_letter_detail(#{ordLetterCode,mode=IN,jdbcType=INTEGER}," +
                " #{ordLetterDetails,mode=OUT,jdbcType=CURSOR,resultMap=ordLetterDetails }," +
                " #{status,mode=OUT,jdbcType=VARCHAR}," +
                " #{msg,mode=OUT,jdbcType=VARCHAR}) }")

        @Results(id="ordLetterDetails",value={
			@Result(column="ORD_LETTER_DETAIL_CODE",property="ordLetterDetailCode"),
			@Result(column="ORD_LETTER_CODE",property="ordLetterCode"),
			@Result(column="DETAIL_TYPE_1",property="detailType1"),
			@Result(column="DETAIL_VALUE_1",property="detailValue1"),
			@Result(column="DETAIL_TYPE_2",property="detailType2"),
			@Result(column="DETAIL_VALUE_2",property="detailValue2"),
            @Result(column="MASTER_SERVICE_TYPE",property="masterServiceType"),
            @Result(column="SERVICE_TYPE",property="serviceType"),
            @Result(column="SERVICE_CODE",property="serviceCode"),
            @Result(column="MASTER_ORD_LETTER_DETAIL_CODE",property="masterOrdLetterDetailCode"),
            @Result(column="ACTION",property="action"),
            @Result(column="STATUS",property="status"),
            @Result(column="STAFF_CREATE",property="staffCreate"),
            @Result(column="CREATE_DATE",property="createDate"),
            @Result(column="STAFF_AMEND",property="staffAmend"),
            @Result(column="AMEND_DATE",property="amendDate")
        })
        @ResultType(com.hkbn.itbss.entity.bo.CurOrdLetterDetail.class)
        //get_letter_detail
        @Options(statementType = StatementType.CALLABLE)
        void getLetterDetail(Map map);
}

查询之后映射结果集对象

package com.hkbn.itbss.entity.bo;

import java.util.Date;

public class CurOrdLetterDetail {
	private   Integer    ordLetterDetailCode;
	private   Integer    ordLetterCode;
	private   String     detailType1;
	private   String     detailValue1;
	private   String     detailType2;
	private   String     detailValue2;
	private   String     masterServiceType;
	private   String     serviceType;
	private   Integer    serviceCode;
	private   String     action;
	private   Integer    masterOrdLetterDetailCode;
	private   String     status;
	private   String     staffCreate;
	private   Date       createDate;
	private   String     staffAmend;
	private   Date       amendDate;

	public Integer getOrdLetterDetailCode() {
		return ordLetterDetailCode;
	}

	public void setOrdLetterDetailCode(Integer ordLetterDetailCode) {
		this.ordLetterDetailCode = ordLetterDetailCode;
	}

	public Integer getOrdLetterCode() {
		return ordLetterCode;
	}

	public void setOrdLetterCode(Integer ordLetterCode) {
		this.ordLetterCode = ordLetterCode;
	}

	public String getDetailType1() {
		return detailType1;
	}

	public void setDetailType1(String detailType1) {
		this.detailType1 = detailType1;
	}

	public String getDetailValue1() {
		return detailValue1;
	}

	public void setDetailValue1(String detailValue1) {
		this.detailValue1 = detailValue1;
	}

	public String getDetailType2() {
		return detailType2;
	}

	public void setDetailType2(String detailType2) {
		this.detailType2 = detailType2;
	}

	public String getDetailValue2() {
		return detailValue2;
	}

	public void setDetailValue2(String detailValue2) {
		this.detailValue2 = detailValue2;
	}

	public String getMasterServiceType() {
		return masterServiceType;
	}

	public void setMasterServiceType(String masterServiceType) {
		this.masterServiceType = masterServiceType;
	}

	public String getServiceType() {
		return serviceType;
	}

	public void setServiceType(String serviceType) {
		this.serviceType = serviceType;
	}

	public Integer getServiceCode() {
		return serviceCode;
	}

	public void setServiceCode(Integer serviceCode) {
		this.serviceCode = serviceCode;
	}

	public String getAction() {
		return action;
	}

	public void setAction(String action) {
		this.action = action;
	}

	public Integer getMasterOrdLetterDetailCode() {
		return masterOrdLetterDetailCode;
	}

	public void setMasterOrdLetterDetailCode(Integer masterOrdLetterDetailCode) {
		this.masterOrdLetterDetailCode = masterOrdLetterDetailCode;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getStaffCreate() {
		return staffCreate;
	}

	public void setStaffCreate(String staffCreate) {
		this.staffCreate = staffCreate;
	}

	public Date getCreateDate() {
		return createDate;
	}

	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}

	public String getStaffAmend() {
		return staffAmend;
	}

	public void setStaffAmend(String staffAmend) {
		this.staffAmend = staffAmend;
	}

	public Date getAmendDate() {
		return amendDate;
	}

	public void setAmendDate(Date amendDate) {
		this.amendDate = amendDate;
	}

	@Override
	public String toString() {
		return "CurOrdLetterDetail{" +
				"ordLetterDetailCode=" + ordLetterDetailCode +
				", ordLetterCode=" + ordLetterCode +
				", detailType1='" + detailType1 + '\'' +
				", detailValue1='" + detailValue1 + '\'' +
				", detailType2='" + detailType2 + '\'' +
				", detailValue2='" + detailValue2 + '\'' +
				", masterServiceType='" + masterServiceType + '\'' +
				", serviceType='" + serviceType + '\'' +
				", serviceCode=" + serviceCode +
				", action='" + action + '\'' +
				", masterOrdLetterDetailCode=" + masterOrdLetterDetailCode +
				", status='" + status + '\'' +
				", staffCreate='" + staffCreate + '\'' +
				", createDate=" + createDate +
				", staffAmend='" + staffAmend + '\'' +
				", amendDate=" + amendDate +
				'}';
	}
}

简单query查询,update insert

package com.xxxxxxx.dao;

import hk.com.hkbn.itrbss.lib.job.ctl.dao.entity.JobCtlActionEntity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface JobCtlActionDao {

    @Insert("Insert into JOB_CTL_ACTION (action_id,group_id,ctl_id,task_id,stock_id,action_config_id,process_status,process_message,staff_create,create_date)" +
        "Values(seq_job_ctl_action.nextval,#{groupId}, #{ctlId, jdbcType=BIGINT} , #{taskId, jdbcType=BIGINT}, #{stockId, jdbcType=BIGINT}, #{actCfgId, jdbcType=BIGINT},'0' ,#{message, jdbcType=VARCHAR},#{staffCreate},sysdate)")
    void store(JobCtlActionEntity e);

    @Select("Select * from JOB_CTL_ACTION where process_status = '0' and create_Date > sysdate - 30 ")
    List<JobCtlActionEntity> selectPendingActions();

    @Select("select act.* from job_ctl_action act, job_ctl_action_config config where act.action_config_id = config.action_config_id and config.action_name = #{name} and act.process_status = '0'")
    @Results(id = "JobCtlActionEntity", value = {
        @Result(column = "action_id", property = "actionId"),
        @Result(column = "group_id", property = "groupId"),
        @Result(column = "ctl_id", property = "ctlId"),
        @Result(column = "task_id", property = "taskId"),
        @Result(column = "stock_id", property = "stockId"),
        @Result(column = "action_config_id", property = "actCfgId"),
        @Result(column = "process_status", property = "status"),
        @Result(column = "process_message", property = "message"),
        @Result(column = "staff_create", property = "staffCreate"),
        @Result(column = "create_date", property = "createDate"),
        @Result(column = "staff_amend", property = "staffAmend"),
        @Result(column = "amend_date", property = "amendDate"),
    })
    List<JobCtlActionEntity> findPendingActionByName(@Param("name") String name);

    @Update("UPDATE JOB_CTL_ACTION SET process_STATUS = #{processStatus}, process_MESSAGE = #{processMsg}, STAFF_AMEND = USER, AMEND_DATE = SYSDATE WHERE ACTION_ID = #{actionId}")
    void updateStatusByActionId(@Param("actionId") Long actionId, @Param("processStatus") String processStatus, @Param("processMsg") String processMsg);

    @Select(" SELECT a.* FROM job_ctl_action a, job_ctl_action_config c " +
        " Where a.action_config_id = c.action_config_id " +
        " And trunc(a.create_date) = trunc(sysdate) " +
        " And a.process_status = #{status} " +
        " And c.action_name = #{actName}"
    )
    @ResultMap("JobCtlActionEntity")
    List<JobCtlActionEntity> findTodayListByActionNameAndStatus(@Param("actName") String actName, @Param("status") String status);

    @Update(" Update job_ctl_action a Set a.process_status = #{status}, a.process_message = #{msg} Where a.action_id in " +
        "(" +
        " SELECT a.action_id " +
        " FROM job_ctl_action a,job_ctl_master m, JOB_CTL_ACTION_CONFIG config " +
        " Where a.ctl_id = m.ctl_id " +
        " And config.ACTION_CONFIG_ID = a.ACTION_CONFIG_ID " +
        " And m.job_num = #{jobNum} " +
        " And config.action_name = 'DISPATCH_TO_EXPRESS_APP'" +
        ")")
    void updateToIPADStatusByJobNum(@Param("jobNum")String jobNum,@Param("status") String status,@Param("msg") String msg);
}