oracle存储过程+mybatis实现动态表名取数

笔记仅记录最简单的实现，个性化需求可自行改造：

 

1. 存储过程：

create or replace PROCEDURE GET_DATA_BY_TABLENM(tableName VARCHAR2,resultSet out SYS_REFCURSOR)
is
sqlStr clob;
fieldSqlStr clob;
fieldStr clob;
BEGIN
fieldSqlStr:='select LISTAGG(decode(data_type,''DATE'',''to_char(''||COLUMN_NAME||'',''''yyyyMMdd'''')'',''TIMESTAMP(6)'',''to_char(''||COLUMN_NAME||'',''''YYYY-MM-DD hh24:mi:ss.ff6'''')'',COLUMN_NAME),''||''''#@#''''||'') within group(order BY column_id) from user_tab_columns where table_name='''||tableName||'''';
dbms_output.put_line(fieldSqlStr);
EXECUTE IMMEDIATE fieldSqlStr into fieldstr;
dbms_output.put_line(fieldstr);
sqlStr:='select '||fieldstr||' as data from '||tableName;
dbms_output.put_line(sqlstr);
open resultSet for sqlstr;
end;

 

2. mybatis mapper.xml代码：

<?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="**.Mapper">
    <!--namespace根据自己需要创建的的mapper的路径和名称填写-->

    <resultMap id="dataMap" type="**.Dto">
        <result property="data" column="data"></result>
    </resultMap>

    <select id="getDataByTableNm" resultType="java.util.List" statementType="CALLABLE">
        {call GET_DATA_BY_TABLENM(#{tableNm},#{resultSet,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=dataMap})}
    </select>

</mapper>

 

3. java代码调用

　　1）返回结果集实体类

public class Dto {

    private String data;

    public Dto(){
    }
    public Dto(String data){
        this.data=data;
    }
    public String getData() {
        return data;
    }
    public void setData(String data) {
        this.data = data;
    }
}

　　2）mapper接口

public interface Mapper {
    Map<String,Object> getDataByTableNm(Map<String, Object> paraMap);
}

　　3）调用

//查询参数
Map<String,Object> paraMap=new HashMap<>();
//用于接收存储过程返回结果集
paraMap.put("resultSet","");
//表名
paraMap.put("tableNm",tableNm);
mapper.getDataByTableNm(paraMap);
//输出结果
for(Dto dto:(List<Dto>)paraMap.get("resultSet")){
    System.out.println(dto);
}