package zeh.mybatis.code02mybatis.dao;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import zeh.mybatis.code00common.entity.ZehUser;
import zeh.mybatis.code00common.po.singleton.CommonPo;

import java.io.File;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

// 测试mybatis的foreach标签
public class MybatisForeachDao {

    // 根据in关键字执行查询操作，主要观察${}和#{}的区别，本次使用#{}，传递的是一个list对象。
    public List<ZehUser> findUserByInForeach(List<ZehUser> paramList) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<ZehUser> list = sqlSession.selectList("myforeach.findUserByInForeach", paramList);
        sqlSession.close();
        return list;
    }


    // 根据in关键在执行查询操作，主要观察${}和#{}的区别。本次使用#{}，传递一个Map，map中引用一个list。
    public List<ZehUser> findUserByIn2Foreach(Map<String, List<String>> map) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<ZehUser> list = sqlSession.selectList("myforeach.findUserByIn2Foreach", map);
        sqlSession.close();
        return list;
    }

    // 根据in关键在执行查询操作，主要观察${}和#{}的区别
    // 本次使用#{}，但是同样接收的是一个大字符串，主要是在statementid里面进行分割处理。
    public List<ZehUser> findUserByIn4(Map<String, String> map) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<ZehUser> list = sqlSession.selectList("myforeach.findUserByIn4Foreach", map);
        sqlSession.close();
        return list;
    }

    // 直接传递简单类型
    public List<ZehUser> findUserByIn5(String[] parameter) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<ZehUser> list = sqlSession.selectList("myforeach.findUserByIn5Foreach", parameter);
        sqlSession.close();
        return list;
    }

    // 直接传递一个map，map中包装简单类型，测试foreach标签。
    public List<ZehUser> findUserByIn6(Map<String, String> map) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<ZehUser> list = sqlSession.selectList("myforeach.findUserByIn6Foreach", map);
        sqlSession.close();
        return list;
    }

    // 测试foreach强大的逻辑拼凑动态sql的能力。
    public List<CommonPo> findUserByIn7(Map<String, String> map) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<CommonPo> list = sqlSession.selectList("myforeach.findUserByIn7Foreach", map);
        sqlSession.close();
        return list;
    }

    // 传入list<Map>复杂条件，观察foreach标签的逻辑拼凑能力。
    // 切记：foreach的collection属性本质上只能接收list或者数组。
    public List<CommonPo> findUserByIn8(Map<String, Object> listMap) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<CommonPo> list = sqlSession.selectList("myforeach.findUserByIn8Foreach", listMap);
        sqlSession.close();
        return list;
    }


    // foreach循环map中的list的map
    public List<CommonPo> findUserByForeach(Map<String, List<Map<String, String>>> paramMap) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<CommonPo> list = sqlSession.selectList("myforeach.findUserByForeachForeach", paramMap);
        sqlSession.close();
        return list;
    }


    // foreach 在 insert 操作中的循环作用（批量插入）
    public void insertZehUserByForeach(List<CommonPo> userPos) throws Exception {
        String resource = "mybatis/sqlmap" + File.separator + "sqlmap-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.insert("myforeach.insertZehUserByForeach", userPos);
        sqlSession.commit();
        System.out.println("insert操作成功提交");
        sqlSession.close();
    }
}

package zeh.mybatis.code02mybatis.run;

import org.junit.Before;
import org.junit.Test;
import zeh.mybatis.code00common.entity.ZehUser;
import zeh.mybatis.code00common.po.singleton.CommonPo;
import zeh.mybatis.code02mybatis.dao.MybatisForeachDao;

import java.util.*;

// 测试mybatis的foreach标签。
public class MybatisForeachDaoRun {
    private MybatisForeachDao foreachDao;

    //实例化dao对象
    @Before
    public void newInstanceFirstDao() {
        foreachDao = new MybatisForeachDao();
    }

    @Test
    public void findUserByInForeach() throws Exception {
        List<ZehUser> paramList = new ArrayList<ZehUser>();
        ZehUser ssg = new ZehUser();
        ssg.setName("ssg");
        ZehUser ddf = new ZehUser();
        ddf.setName("ddf");
        ZehUser jjk = new ZehUser();
        jjk.setName("jjk");
        ZehUser iio = new ZehUser();
        iio.setName("getMyName");
        ZehUser zhaoeh = new ZehUser();
        zhaoeh.setName("zhaoeh");
        ZehUser yurui = new ZehUser();
        yurui.setName("yurui");
        paramList.add(ssg);
        paramList.add(ddf);
        paramList.add(jjk);
        paramList.add(iio);
        paramList.add(zhaoeh);
        paramList.add(yurui);
        // 下面故意将param输入参数设置为null值，测试_parameter是否是Mybatis默认的输入参数对象，结论：_parameter参数就是mybatis默认的输入参数对象。
        // paramList = null;
        // 将 paramList 传递进去
        List<ZehUser> list = foreachDao.findUserByInForeach(paramList);
        Iterator<ZehUser> iterator = list.iterator();
        System.out.println("根据用户名称使用#{}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            ZehUser zehUser = iterator.next();
            System.out.println("name = " + zehUser.getName() + ";age = " + zehUser.getAge() + ";sex = " + zehUser.getSex() + ";hobby = " + zehUser.getHobby());
        }
    }

    @Test
    public void findUserByIn2Foreach() throws Exception {
        List<String> param = new ArrayList<String>();
        Map<String, List<String>> paramMap = new HashMap<String, List<String>>();
        param.add("ssg");
        param.add("ddf");
        param.add("jjk");
        param.add("getMyName");
        param.add("zhaoeh");
        param.add("yurui");
        paramMap.put("myListMap", param);
        List<ZehUser> list = foreachDao.findUserByIn2Foreach(paramMap);
        Iterator<ZehUser> iterator = list.iterator();
        System.out.println("根据用户名称使用#{}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            ZehUser zehUser = iterator.next();
            System.out.println("name = " + zehUser.getName() + ";age = " + zehUser.getAge() + ";sex = " + zehUser.getSex() + ";hobby = " + zehUser.getHobby());
        }
    }

    // 根据name在某个集合中，进行查询，主要验证#{}和${}的区别。
    // 本次仍旧使用#{}，而且不传递list或者array，或者map中封装的list，array等，本次就传递一个大的字符串，但是同样使用#{}进行处理，同样借助mybatis的foreach标签和collection属性指定组合规则，动态组成sql片段！
    @Test
    public void testFindUserByIn4() throws Exception {
        Map<String, String> paramMap = new HashMap<String, String>();
        // String sss = "'ssg','ddf','jjk','iio','zhaoeh','yurui'";
        // String sss = "ssg,ddf,jjk,iio,zhaoeh,yurui";

        /**
         * 下面这个大字符串的含义：
         *
         * 其实需要实现的sql是：
         *
         * select * from zeh_user where name = '' or name = '' or name = ''。。。
         *
         * 即查询zeh_user表中name为某个动态变化值的结果，这个name的参数根据字符串传递进去，不知道会有一个值。
         *
         * 所以，不建议使用or或者in，因为性能低导致索引失效。而采用union进行求并集去重的方式。 即：
         *
         * select * from zeh_user where name = ''
         *
         * union
         *
         * select * from zeh_user where name = ''
         *
         * union
         *
         * select * from zeh_user where name = ''
         *
         * ...
         *
         * 当然并集求完之后，后边可能还要对整个并集的过滤条件，所以要实现成如下的样子：
         *
         * select * from (
         *
         * select * from zeh_user where name = ''
         *
         * union
         *
         * select * from zeh_user where name = ''
         *
         * union
         *
         * select * from zeh_user where name = ''
         *
         * ... ) t
         *
         * where t.sex = .....
         *
         * 现在有了新需求，即传递的这个大字符串，按照$$$$分割成每一个name字段的值后，这个值可能会包含特殊字符“####”结尾，
         * 如果这个name包含这个特殊字符
         * ####，则查询时，先去掉这个特殊字符，然后所有按照该name查询出来的记录都应该携带一个字段isExtend且值为0
         * ；相反的其他的name条件查询出来的同样携带isExtend字段但是值为1
         * 。（注意，isExtend字段zeh_user表中没有，是需要我们自己组装的虚拟字段）！！！！
         */
        // String sss = "ssg$$$$ddf$$$$jjk####$$$$iio$$$$zhaoeh$$$$yurui";
        String sss = "2c9005e847aa52fb0147aa537dd50002$$$$364ae996eae24aebb3ac03fbeea89786@@@@$$$$afd6dbc5136b4aba8c30e30c2316b392";
        paramMap.put("gggg", sss);
        List<ZehUser> list = foreachDao.findUserByIn4(paramMap);
        Iterator<ZehUser> iterator = list.iterator();
        System.out.println("根据用户名称使用${}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            ZehUser zehUser = iterator.next();
            System.out.println("name = " + zehUser.getName() + ";age = " + zehUser.getAge() + ";sex = " + zehUser.getSex() + ";hobby = " + zehUser.getHobby());
        }
    }

    // 直接传递一个数组，测试foreach标签。
    @Test
    public void testFindUserByIn5() throws Exception {
        String parameter[] = {"ssg", "ddf", "jjk", "iio", "zhaoeh"};
        List<ZehUser> list = foreachDao.findUserByIn5(parameter);
        Iterator<ZehUser> iterator = list.iterator();
        System.out.println("根据用户名称使用${}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            ZehUser zehUser = iterator.next();
            System.out.println("name = " + zehUser.getName() + ";age = " + zehUser.getAge() + ";sex = " + zehUser.getSex() + ";hobby = " + zehUser.getHobby());
        }
    }

    // 传递一个简单类型的map：foreach标签解析map标签使用_parameter.entrySet()则一劳永逸！
    @Test
    public void testFindUserByIn6() throws Exception {
        Map<String, String> parameterMap = new HashMap<String, String>();
        parameterMap.put("01", "ssg");
        parameterMap.put("02", "ddf");
        parameterMap.put("03", "jjk");
        parameterMap.put("04", "iio");
        parameterMap.put("05", "getMyName");
        List<ZehUser> list = foreachDao.findUserByIn6(parameterMap);
        Iterator<ZehUser> iterator = list.iterator();
        System.out.println("根据用户名称使用${}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            ZehUser zehUser = iterator.next();
            System.out.println("name = " + zehUser.getName() + ";age = " + zehUser.getAge() + ";sex = " + zehUser.getSex() + ";hobby = " + zehUser.getHobby());
        }
    }

    /**
     * 传递map，封装大字符串，利用foreach标签强大功能进行分割拼凑动态sql.
     * <p>
     * 需求是：传递一个大字符串，以$$$$分割，对应zeh_user表中的name字段；
     * <p>
     * 传递一个大字符串，以:分割，对应zeh_user表中的id字段。
     * <p>
     * 想要实现的sql是：
     * <p>
     * select * from zeh_user where 1=1 and name = "" and id = ""
     * <p>
     * union
     * <p>
     * select * from zeh_user where 1=1 and name = "" and id = ""
     * <p>
     * union
     * <p>
     * select * from zeh_user where 1=1 and name = "" and id = ""
     * <p>
     * union
     * <p>
     * select * from zeh_user where 1=1 and name = "" and id = ""
     * <p>
     * union并集的次数按照字符串只能中分割后的name字段使用foreach进行遍历。
     * <p>
     * 问题：mybatis的foreach标签虽然能够嵌套，也能够各自遍历对应的list或者数组，
     * 但是此处每次查询时都必须要保证name和id的关系是一一对应的。所以，只能将id字段以:的形式拼接在name那串长字符串中。
     *
     * @throws Exception
     */
    @Test
    public void testFindUserByIn7() throws Exception {
        Map<String, String> paramMap = new HashMap<String, String>();
        String corpIds = "ssg$$$$ddf$$$$jjk$$$$iio$$$$zhaoeh$$$$yurui$$$$";
        // String corpIds = "";
        // 对原始包含name的字符串进行拆分
        String[] corpIdArrays = corpIds.split("\\$\\$\\$\\$");
        corpIds = "";
        System.out.println(corpIdArrays.length);
        for (int i = 0; i < corpIdArrays.length; i++) {
            // 拆分后重组，重组的格式：ssg:1$$$$ddf:2$$$$jjk:3$$$$iio:4$$$$zhaoeh:5$$$$yurui:6
            corpIds = corpIds + corpIdArrays[i] + ":" + (i + 1) + "$$$$";
            System.out.println(corpIdArrays[i]);
        }
        System.out.println(corpIds);
        paramMap.put("corpIds", corpIds);
        List<CommonPo> list = foreachDao.findUserByIn7(paramMap);
        Iterator<CommonPo> iterator = list.iterator();
        System.out.println("根据用户名称使用${}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

    // 难点！！！
    // 假设现在通过union对很多条sql语句取并集，然后每个sql语句where里面有三个条件，且这三个条件具备一一对应的关系，此时就应该使用List<Map>然后结合foreach标签去操作！
    // 三个要存储的key是：corpId,dataAuthFlag,dataType。
    // 这三个key必须是一个维度的。
    // 所以传递一个map，里面放置一个List<Map>对象。
    @Test
    public void testFindUserByIn8() throws Exception {
        Map<String, Object> parameterMap = new HashMap<String, Object>();
        // new一个list，其中放map
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        String corpIds = "ssg$$$$ddf$$$$jjk$$$$iio$$$$zhaoeh$$$$yurui$$$$";
        //对原始包含name的字符串进行拆分，拆分后和对应的
        String[] corpIdArrays = corpIds.split("\\$\\$\\$\\$");
        for (int i = 0; i < corpIdArrays.length; i++) {
            // 每次进来要新建一个map对象，握草，日了狗了，2019-4-14在厦门排查这个问题搞了几个小时！！！！
            Map<String, String> paramMap = new HashMap<String, String>();
            String corpId = corpIdArrays[i];
            System.out.println(corpId);
            // 封装map
            paramMap.put("corpId", corpId);
            if (i == 3) {
                paramMap.put("corpId", null);
                paramMap.put("dataAuthFlag", Integer.toString(i + 1));
                paramMap.put("dataType", Integer.toString(i + 1) + "Test");
            }
            // 塞进list中
            listMap.add(paramMap);
        }
        System.out.println("=============:" + listMap);
        parameterMap.put("listMap", listMap);
        parameterMap.put("age", "22");
        List<CommonPo> list = foreachDao.findUserByIn8(parameterMap);
        Iterator<CommonPo> iterator = list.iterator();
        System.out.println("根据用户名称使用${}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

    @Test
    public void testFindUserByForeach() throws Exception {
        List<Map<String, String>> param = new ArrayList<Map<String, String>>();
        Map<String, List<Map<String, String>>> paramMap = new HashMap<String, List<Map<String, String>>>();
        Map<String, String> map1 = new HashMap<String, String>();
        map1.put("name", "ssg");
        map1.put("sex", "女");
        Map<String, String> map2 = new HashMap<String, String>();
        map2.put("name", "jjk");
        map2.put("sex", "女");
        param.add(map1);
        param.add(map2);
        paramMap.put("myParamMap", param);
        List<CommonPo> list = foreachDao.findUserByForeach(paramMap);
        Iterator<CommonPo> iterator = list.iterator();
        System.out.println("根据用户名称使用#{}查询到的users表的record如下：");
        while (iterator.hasNext()) {
            System.out.println(iterator.next());
        }
    }

    @Test
    public void testInsertZehUserByForeach() throws Exception {
        List<CommonPo> userPos = new ArrayList<>();
        CommonPo user1 = new CommonPo();
        CommonPo user2 = new CommonPo();
        user1.setId(110);
        user1.setName("批量1");
        user1.setAge(9);
        user1.setHobby("kanshu");
        user1.setSex("男");
        user2.setId(111);
        user2.setName("批量2");
        user2.setAge(12);
        user2.setHobby("kanshu");
        user2.setSex("男");
        userPos.add(user1);
        userPos.add(user2);
        foreachDao.insertZehUserByForeach(userPos);
    }
}