MyBatis练习：查询表中单个数据列中用分隔符隔开的数据

我的电脑操作系统版本为Win7旗舰版（ServicePack1），Oracle版本为Oracle11g，PL/SQL版本为7.0.1.1066

程序使用的jar包有：mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar

本例中使用的配置文件mybatis-config.xml，可以参见我的另一篇Blog《一个简单的MyBatis连接Oracle数据库的例子》（http://my.oschina.net/Tsybius2014/blog/626206）

---

最近在工作中遇到一个场景，即一张表的一个数据列（设为字段A）存储了多个数据项，每个数据项都用逗号隔开。现给出一个存放了一或多个数据项的List，如果某一行数据的字段A中有任一单个数据项也在于List中。如果给出的List为空或长度为0，则查出所有数据。

如下图所示，表AUTHORITY_LIST下的列USER_AUTH，其中有1-5共计5项，分隔符为逗号（,），使用PL/SQL看到的内容如下：

该表的建表脚本为：

CREATE TABLE AUTHORITY_LIST
(
    ID NUMBER(12, 0) PRIMARY KEY,
    USER_NAME VARCHAR(100),
    USER_AUTH VARCHAR(100)
);

INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (1, 'Jupiter',     ',1,2,3,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (2, 'Juno',        ',2,3,4,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (3, 'Neptune',     ',3,4,5,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (4, 'Ceres',       ',5,4,3,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (5, 'Minerva',     ',4,3,2,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (6, 'Apollo',      ',3,2,1,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (7, 'Diana',       ',1,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (8, 'Mars',        ',2,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (9, 'Venus',       ',3,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (10, 'Vulcan',     ',4,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (11, 'Mercury',    ',5,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (12, 'Vesta',      ',1,3,5,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (13, 'Bacchus',    ',5,3,1,');

INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (14, 'Orcus',      ',1,3,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (15, 'Hercules',   ',2,3,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (16, 'Proserpina', ',2,5,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (17, 'Vejovis',    ',4,2,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (18, 'Cupid',      ',3,1,');
INSERT INTO AUTHORITY_LIST (ID, USER_NAME, USER_AUTH) VALUES (19, 'Juventas',   ',5,1,');

COMMIT;
/

现在我要做的是，给定0项、1项或多项，查询符合条件条目的USER_NAME并返回。

如我要查询具备条件1或5的条目，传入的List中带有1、5两项，则在USER_AUTH中具有1或4的条目都应被查出。如Diana的USER_AUTH为“,1,”，Mercury的USER_AUTH为“,5,”，Apollo的 USER_AUTH为“3,2,1”，他们都可被查出，但Vejovis的USER_AUTH为“,4,2,”，因为其中没有1也没有5，就不能被查出了。

为此我先写了一个SQL语句，用于查询USER_AUTH中包含1或5的条目：

SELECT *
FROM   AUTHORITY_LIST
WHERE  USER_AUTH LIKE '%,1,%' OR
       USER_AUTH LIKE '%,5,%';

查询结果如下：

现在将这个SQL语句移植到MyBatis中，main函数调用代码如下：

import java.io.InputStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * MyBatis使用测试
 * @author Tsybius2014
 * @date 2016年5月21日
 * @time 下午3:45:08
 * @remark
 */
public class MyBatisTest {
    public static void main(String[] args) {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sqlSessionFactory.openSession();
            try {
                AuthorityListMapper mapper = session.getMapper(AuthorityListMapper.class);
                List<String> authList = new ArrayList<String>();
                authList.add("1");
                authList.add("5");
                List<String> userList = mapper.getUsersByAuth(authList);
                System.out.println(String.join(",\n", userList));
            } finally {
                session.close();
            }
        } catch (Exception ex) {
            StringWriter stringWriter = new StringWriter();
            PrintWriter printWriter = new PrintWriter(stringWriter);
            ex.printStackTrace(printWriter);
            System.out.println(stringWriter.toString());
        }
    }
}

AuthorityListMapper.java代码如下：

import java.util.List;

public interface AuthorityListMapper {
    List<String> getUsersByAuth(List<String> authList);
}

AuthorityListMapper.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="AuthorityListMapper"> 
  <select id="getUsersByAuth" parameterType="java.util.List" resultType="java.lang.String" >
    SELECT USER_NAME FROM AUTHORITY_LIST 
    <if test="list != null and list.size() > 0">
      WHERE 
      <foreach item="item" index="index" collection="list" separator="OR" >
          USER_AUTH LIKE '%'||#{item}||'%'
      </foreach>
    </if>
  </select>
</mapper>

程序运行结果如下：

Jupiter,
Neptune,
Ceres,
Apollo,
Diana,
Mercury,
Vesta,
Bacchus,
Orcus,
Proserpina,
Cupid,
Juventas

PS：其实这张表的表结构设计得并不好，这种一对多的关系如果采用两张表存储，在查询的时候使用关联查询，会方便得多。本文中的办法只是一个思路，当老的数据表中已经存有一些数据，且无法轻易更改老数据表的表结构时，可参考本文的方法。

END

转载于:https://my.oschina.net/Tsybius2014/blog/679079