SQL片段

①插入集合查询数据 

SELECT * from USER where uid IN (1,5,8)

List<Integer> uids = new ArrayList<>();
       uids.add(1);
       uids.add(5);
       uids.add(8);

 
   <select id="querySomeUsers" parameterType="list" resultMap="userRM">
        SELECT * from USER where uid IN
        <foreach collection="list" item="uid" open="(" close=")" separator=",">
            #{uid}
        </foreach>
    </select>

parameterType：插入的类型
collection：list 或map
item ：给每条数据赋一个变量名

②插入数组查询数据

@Test
    public void test3() throws IOException {
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        int[] uids = {1,5,8};
        List<User> users = userDao.querySomeUsersArray(uids);
        System.out.println("users = " + users);
    }
           。。。

public List<User> querySomeUsersArray(int[] uids);

           。。。
 
<select id="querySomeUsersArray" parameterType="int[]" resultMap="userRM">
        SELECT * from USER where uid IN
        <foreach collection="array" item="uid" open="(" close=")" separator=",">
            #{uid}
        </foreach>
    </select>

③插入map 并且条件查询 如果某个键或者值为空则不查询 

   @Test
    public void test5() throws IOException {
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("uid",2);
        map.put("username","%dahuang%");
        List<User> users = userDao.queryUserBySome(map);
        System.out.println("users = " + users);
    }

              。。。

public List<User> queryUserBySome(Map<String, Object> map);
 
              。。。

   <select id="queryUserBySome" parameterType="map" resultMap="userRM">
        SELECT * from USER
         <where>
             <if test="username!=null">
                  username LIKE #{username}
             </if>

             <if test="uid!=null">
                 AND uid=#{uid}
             </if>
         </where>

    </select>

④更新一条数据操作 

  @Test
        public void test4() throws IOException {
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            Map<String,Object> map = new HashMap<>();
            map.put("uid",2);
            map.put("userpwd","123555555456");
            userDao.updateUserByUid(map);
        }

     。。。

 public User selectUserById(Integer uid);

     。。。

 

  <update id="updateUserByUid" parameterType="map" >
        UPDATE USER

        <trim prefix="set" suffixOverrides=",">
            <if test="userpwd!=null">
                userpwd=#{userpwd} ,
            </if>

            <if test="username!=null">
                username=#{username}
            </if>
        </trim>

          where uid=#{uid}
    </update>

⑤传入List<String> 插入数据

  业务：
 List<List<String>> datas = poiResult.getDatas();

            for (List<String> data : datas) {
                planDao.insertSta(data);
                System.out.println(data);
            }

控制台数据：
[北京, a, 小黄, maradona1, 国外, 1379916255, 三期]
[北京, a, 小黄, maradona1, 国外, 1379916255, 三期]
[北京, a, 小黄, maradona1, 国外, 1379916255, 三期]


dao接口：
    public void insertSta(List<String> titles);

dao.xml：

<insert id="insertSta" parameterType="java.util.List" useGeneratedKeys="false">
        insert into elec_station
        ( jctid,attributionground,stationcode,stationname,stationtype,contacttype,jcfrequency)
        values
        <foreach collection="list" item="item" open="(" close=")"  index="index" separator=",">
            #{item}
        </foreach>
    </insert>