存储动态列的结果集（行列互转）--java、mybatis、orcale

业务上需要存储动态列的数据 通过行列互转的方式实现

数据库中动态列的几种设计思路

• 使用数据库DDL进行动态创建

• 使用数据库预留字段（宽表）

• 使用数据库中的json数据类型

• 使用非关系型数据库（MongoDB等）

这是网上给出的几个基本思路，考虑到我们业务上的功能不太适合这些方法，所有最后选择通过行列互转的方式实现。

数据库设计

数据库使用了4个字段，key是原表中的列名，value是对应的值，sort有两个作用，排序和分组，row_id用于关联主表。数据库结构如下表table。

名称	类型	注释
COL_KEY	VARCHAR2(50)	列名（key）
COL_VALUE	VARCHAR2(255)	列值（value）
ROW_ID	VARCHAR2(50)	所属主表id
SORT	NUMBER	排序

行转列（存储）

第一步是将动态列的结果集存储起来

列名1	列名2	列名3	...	列名N
a	b	c	...	e
1	2	3	...	5
A	B	C	...	E
6	7	8	...	10

结果集返回的数据是List<Map> 结构，这里不做太多赘述了

直接上代码

//存入列表（列转行）
List<Table> list= new ArrayList<>();//新建一个table类型的集合
long index = 1;//排序索引
for (Map map : list) { //list是返回的结果集，直接遍历
    // 通过keySet方法获取list中一个map的所有key
    Set<String> keySet = map.keySet(); 
    for (String s : keySet) { //继续遍历所有key
        //新建临时对象
        Table table = new Table();
        table.setColKey(s);//设置属性Key
        //设置属性value，通过get(key)的方式，同时将value的类型都处理成String
        table.setColValue(String.valueOf( map.get(s)));
        table.setSort(index);//设置分组条件的同时还能排序
        table.setRowId(“自定义”);//根据传过来的值设置属于那个主表
        list.add(table);//加入集合中
    }
    index++;
}

service.insertBeath(list);

Mapper.xml代码

//Oracle实现方式
<insert id="insertBeath">
    insert into table(col_key,col_value,row_id,sort)
    select A.* from (
    <foreach collection="list" item="item" index="index" separator="UNION ALL">
        select
        #{item.colKey,jdbcType=VARCHAR} col_key,
        #{item.colValue,jdbcType=VARCHAR} col_value,
        #{item.rowId,jdbcType=VARCHAR} row_id,
        #{item.sort,jdbcType=INTEGER} sort
        from DUAL
    </foreach>
    ) A
</insert>

列转行（显示）

上一步已经将数据存入table表中，数据如下

col_key	col_value	row_id	sort
列名1	a	id	1
列名2	b	id	1
列名3	c	id	1
列名4	d	id	1
列名1	1	id	2
列名2	2	id	2
列名3	3	id	2
列名4	4	id	2

存完之后下一步就该显示了，虽然我们是通过列的方式存的，但是我们显示的时候还是应该将列转换成行来显示，就和存之前的结果集一样

我用的数据库是Oracle，因此有两种方式来实现

SQL通用方式

SQL语句如下

select sort,
MAX(case col_key when '列名1' then col_value  end) as 列名1,
MAX(case col_key when '列名2' then col_value  end) as 列名2,  
MAX(case col_key when '列名3' then col_value  end) as 列名3,
MAX(case col_key when '列名4' then col_value  end) as 列名4
from table 
where row_id = id 
group by sort order by sort

这种方式是使用case when来实现，根据分组后的结果使用聚合函数MAX()查询将列换成行来显示。

Oracle专用方式

SQL语句如下

SELECT * FROM table
PIVOT (
 MAX(col_value) FOR col_key IN ('列名1', '列名2', '列名3','列名4')
)  
where row_id = id  
order by sort

这是用Oracle独有的关键字pivot实现，根据列名自动将列换成行显示，语法更加简单。

结合Java和Mybatis

上面两种方式都有个缺点，就是都只能将列名写死来分组。而实践上存储的结果集是动态的列，网上有很多都是通过数据库存储过程来实现的。

下面是我通过Mybatis框架实现的代码，大致思路是先根据分组查询出所有的列名，再将列名作为查询条件去行转列中查询

Java

List<String> group = service.selectGroup(rowId);
List<Map> list= service.selectDetailView(rowId, group);
System.err.println(list);

Mapper.xml

//查询当前表中所有的列名（根据分组来查）
<select id="selectGroup" resultType="java.lang.String">
    select col_key from monitor_detail where monitor_id = #{monitorId} group by col_key
</select>


//SQL通用方式
<select id="selectDetailView" resultType="Map">
     select
     <foreach item="item" collection="group"  separator=","  index="index">
         MAX(case col_key when '${item}' then col_value  end) as ${item}
     </foreach>
     from monitor_detail where  monitor_id = #{monitorId} group by sort order by sort
</select>

//Orcale专用方式
<select id="selectDetailView" resultType="Map">
    SELECT * FROM monitor_detail
    PIVOT (
       MAX(col_value) FOR col_key IN
        <foreach item="item" collection="group" open="(" separator="," close=")" index="index">
            '${item}' as ${item}
        </foreach>
    )
    where  monitor_id = #{monitorId} order by sort
</select>

到这里有人可能会问，那直接将分组查询列名的语句，作为子查询在pivot in中不是更好。当时我就是这么想的，结果发现pivot in中不能直接写子查询，也不能写变量。