Mybatis 调用 Oracle 过程整理

目标

1. 通过 mybatis 获取最大业务号码
2. 采用 oracle 数据库，通过序列方式
3. 传入自定义序列参数，如有已有序列，则使用序列；如无则创建序列

创建过程

CREATE OR REPLACE PROCEDURE CREATEMAXNO (NOTYPE IN VARCHAR2, NOLIMIT IN VARCHAR2, SEQ OUT VARCHAR2) 
Authid Current_User AS
GetNoSql varchar(200);
BEGIN
  dbms_output.put_line('输入：'||NOTYPE||','||NOLIMIT);
	GetNoSql := 'select SEQ_'||NOTYPE||'_'||NOLIMIT||'.nextval from dual';
	execute immediate GetNoSql into SEQ;
	EXCEPTION
		WHEN OTHERS THEN
		execute immediate 'create sequence SEQ_'||NOTYPE||'_'||NOLIMIT||' minvalue 1 maxvalue 99999999999999999999 start with 1 increment by 1 nocache';
		execute immediate GetNoSql into SEQ;
END;

1. dbms_output.put_line(),控制台输出
2. execute immediate GetNoSql into SEQ，动态修改 sql 语句，并将结果输出到 SEQ 中
3. 当前序列不存在时，进入 EXCEPTION，然后创建序列，再查询序列

Java 代码

实体类

public class MaxNo extends DataEntity<MaxNo> {
    private static final long serialVersionUID = 1L;
    private String noType;
    private String noLimit;
    private String maxNo;
    public MaxNo() {
    }
    public MaxNo(String noType, String noLimit) {
        this.noType = noType;
        this.noLimit = noLimit;
    }
    public String getNoType() {
        return noType;
    }
    public void setNoType(String noType) {
        this.noType = noType;
    }
    public String getNoLimit() {
        return noLimit;
    }
    public void setNoLimit(String noLimit) {
        this.noLimit = noLimit;
    }
    public String getMaxNo() {
        return maxNo;
    }
    public void setMaxNo(String maxNo) {
        this.maxNo = maxNo;
    }
}

mybatisDAO

@MyBatisDao
public interface MaxNoDao extends CrudDao<MaxNo> {
    void createMaxNo(MaxNo tMaxNo);
}

---
<?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="xxx.dao.MaxNoDao">
    
	<select id="createMaxNo" parameterType="xxx.entity.MaxNo" statementType="CALLABLE">
		{call createmaxno(#{noType,mode=IN},#{noLimit,mode=IN},#{maxNo,mode=OUT,jdbcType=CHAR})
	</select>
</mapper>

1. 无返回对象，序列将会被赋值在 MaxNo.manNo 上
2. 输出值需要配置数据类型，jdbcType=CHAR

使用

    /**
     * 通过表+function实现
     *
     * @param cNoType
     * @param cNoLength
     * @return
     */
    public String CreateMaxNo(String cNoType, int cNoLength) {
        if ((cNoType == null) || (cNoType.trim().length() <= 0) || (cNoLength <= 0)) {
            logger.error("NoType长度错误或NoLength错误");
            return null;
        }
        cNoType = cNoType.toUpperCase();
        BigInteger tMaxNo ;
        try {
            MaxNo maxNo = new MaxNo(cNoType,"SN");
            maxNoDao.createMaxNo(maxNo);
            tMaxNo = new BigInteger(maxNo.getMaxNo());
        } catch (Exception Ex) {
            logger.error(Ex);
            return null;
        }
        return PubFun.LCh(tMaxNo.toString(), "0", cNoLength);
    }