系列博文：　　

　　　　JavaWeb_(Mybatis框架)JDBC操作数据库和Mybatis框架操作数据库区别_一　　　　传送门

　　　　JavaWeb_(Mybatis框架)使用Mybatis对表进行增、删、改、查操作_二　　　　　　  传送门

　　　　JavaWeb_(Mybatis框架)Mapper动态代理开发_三 　　　　　　　　　　　　　　　 传送门

　　　　JavaWeb_(Mybatis框架)主配置文件介绍_四　　　　　　　　　　　　　　　 　　   传送门

　　　　JavaWeb_(Mybatis框架)输入和输出参数_五　　　　　　　　　　　　　　　　　　 传送门

　　　　JavaWeb_(Mybatis框架)关联查询_六传送门　　　　　　　　　　　　　　　　　 　传送门

　　　　JavaWeb_(Mybatis框架)动态sql_七传送门　　　　　　　　　　　　　　　　　　　传送门

　　1、if标签 - 多条件查询，获取用户列表；　　
　　2、where标签 - 解决if标签拼接字符串AND符号问题；
　　3、trim标签 - 定制where标签的规则
　　4、set标签 - 解决更新数据表时字符串拼接逗号”,”问题；
　　5、foreach标签 – 如果需要使用IN查询多条相同数据，可以使用foreach遍历；
　　6、sql标签 – 可以提取重复sql语句片段；

1、if标签 - 多条件查询，获取用户列表

　　模糊查询用户名中带有"王"字的用户

　　UserMapper.java中编写通过用户模糊查询用户列表接口selectUserListByUser()

package com.Gary.mapper;

import java.util.List;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

public interface UserMapper {

    //通过id查询一个用户

    public User selectUserById(Integer id);

    //通过用户名模糊查询 获取用户列表

    public List<User> selectUserByName(String name);

    //通过UserVo id 查询一个用户

    public User selectUserByVoId(UserVo id);

    //查询用户总条数

    public Integer selectUserCount();

    //查询所有用户的包装类

    public List<UserVo> selectAllUserVo();

    //多条件查询  通过用户对象中的条件查询用户列表

    public List<User> selectUserListByUser(User u);

}

UserMapper.java

　　UserMapper.xml中实现这个模糊查询的方法

<select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user WHERE u_sex = #{u_sex}

                  AND u_username like "%"#{u_username}"%"

                  AND u_cid = #{u_cid}

     </select>
<?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="com.Gary.mapper.UserMapper">

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user WHERE u_sex = #{u_sex}

                  AND u_username like "%"#{u_username}"%"

                  AND u_cid = #{u_cid}

     </select>

</mapper>

UserMapper.xml

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest8 {  

    @Test

    public void Test8() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User u = new User();

        u.setU_sex("1");

        u.setU_username("王");

        u.setU_cid(1);

        List<User> list = mapper.selectUserListByUser(u);

        for(User user : list){

            System.out.println(user);

        }

    }

}

MapperTest8.java

　　但此时，如果用户未输入三个参数，只输入了sex、username两个参数

    UserMapper mapper = session.getMapper(UserMapper.class);

    User u = new User();

    u.setU_sex("1");

    u.setU_username("王");

    //u.setU_cid(1);

    List<User> list = mapper.selectUserListByUser(u);

    for(User user : list){

        System.out.println(user);

    }

　　此时会无法准确模糊查询到数据库中的值

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest8 {  

    @Test

    public void Test8() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User u = new User();

        u.setU_sex("1");

        u.setU_username("王");

        //u.setU_cid(1);

        List<User> list = mapper.selectUserListByUser(u);

        for(User user : list){

            System.out.println(user);

        }

    }

}

MapperTest8.java

　　为防止照成这种情况，可以使用在查询时使用<if>标签【也可以防止用户添加空串】

<select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user WHERE 

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

     </select>
<?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="com.Gary.mapper.UserMapper">

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user WHERE 

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

     </select>

</mapper>

UserMapper.xml

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest8 {  

    @Test

    public void Test8() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User u = new User();

        u.setU_sex("1");

        u.setU_username("王");

        //u.setU_cid(1);

        List<User> list = mapper.selectUserListByUser(u);

        for(User user : list){

            System.out.println(user);

        }

    }

}

MapperTest8.java

　　此时如果是未输入第一个u_sex值，会产生bug，数据库执行语句就会变成 SELECT * FROM user WHERE  AND u_username like "%"#{u_username}"%"

<?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="com.Gary.mapper.UserMapper">

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user WHERE 

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

     </select>

</mapper>

UserMapper.xml

　　所以，为了解决上面这种情况，我们可以where标签

2、where标签 - 解决if标签拼接字符串AND符号问题；

　　在原有的selectUserListByUser查询用户性别 模糊用户名 查询用户cid 国际ID上添加<where>标签 <where>标签可以去掉前边的AND符号

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <where>

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

             </where>

     </select>
<?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="com.Gary.mapper.UserMapper">

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <where>

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

             </where>

     </select>

</mapper>

UserMapper.xml

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest8 {  

    @Test

    public void Test8() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User u = new User();

        //u.setU_sex("1");

        u.setU_username("王");

        //u.setU_cid(1);

        List<User> list = mapper.selectUserListByUser(u);

        for(User user : list){

            System.out.println(user);

        }

    }

}

MapperTest8.java

3、trim标签 - 定制where标签的规则

　　在<trim>标签中默认使用prefixOverrides去掉前AND，也可以使用suffixOverrides去掉后AND

<select id="selectUserListByUserTrim" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <!-- 默认使用prefixOverrides去掉前AND 也可以使用suffixOverrides去掉后AND -->

             <trim prefix="where" prefixOverrides="">

                 <if test="u_sex != null">

                     u_sex = #{u_sex}

                 </if>

                 <if test="u_username != null">

                     AND u_username like "%"#{u_username}"%"

                 </if>

                 <if test="u_cid != null">

                     AND u_cid = #{u_cid}

                 </if>

             </trim>

     </select>
<?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="com.Gary.mapper.UserMapper">

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <where>

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

             </where>

     </select>

     <select id="selectUserListByUserTrim" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <!-- 默认使用prefixOverrides去掉前AND 也可以使用suffixOverrides去掉后AND -->

             <trim prefix="where" suffixOverrides="AND|OR">

                 <if test="u_sex != null">

                     u_sex = #{u_sex} AND

                 </if>

                 <if test="u_username != null">

                     u_username like "%"#{u_username}"%" AND

                 </if>

                 <if test="u_cid != null">

                     u_cid = #{u_cid} AND

                 </if>

             </trim>

     </select>

</mapper>

UserMapper.xml

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest9 {  

    @Test

    public void Test8() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User u = new User();

        //u.setU_sex("1");

        u.setU_username("王");

        //u.setU_cid(1);

        List<User> list = mapper.selectUserListByUserTrim(u);

        for(User user : list){

            System.out.println(user);

        }

    }

}

MapperTest9.java

4、set标签 - 解决更新数据表时字符串拼接逗号”,”问题；

　　编写一个更新用户updateSetUser的SQL语句

　　<update id="updateSetUser" parameterType="User">

         <!-- 用户名  用户密码 性别 用id来限制 -->

         update user

         Set

         <if test="u_username != null and u_username != '' ">

             u_username = #{u_username},

         </if>

          <if test="u_password != null and u_password != '' ">

             u_password = #{u_password},

         </if>

          <if test="u_sex != null and u_sex != '' ">

             u_sex = #{u_sex},

         </if>

         Where u_id= #{u_id}

     </update>

　　发现如果最后一个u_sex条件不成立的时候，会把#{u_password}后边的逗号，遗留下

　　

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest10 {  

    @Test

    public void Test10() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User u = new User();

        u.setU_id(1);

        u.setU_username("Gary王");

        u.setU_password("aaa");

        //u.setU_sex("1");    主拼其那两个，会录下一个逗号

        //未提交事务 不会改变数据库

        mapper.updateSetUser(u);

    }

}

MapperTest10.java

5、foreach标签 – 如果需要使用IN查询多条相同数据，可以使用foreach遍历；

　　数据库中使用in标签查询u_id为1，3，5的数据

　　

select * from user where u_id in (1,3,5)

Gary.sql

　　 编写一个使用id更新用户列表selectUserListByIds的SQL语句

<!-- 使用多个id获得用户列表 (1,3,5) -->

     <select id="selectUserListByIds" resultType="User">

         select *

         from user

         where u_id

         in

         <!-- (1，3，5) separator可以组拼逗号 -->

         <foreach collection="array" item="id" open="(" close=")" separator=",">

             #{id}

         </foreach>

     </select>
<?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="com.Gary.mapper.UserMapper">

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <where>

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

             </where>

     </select>

     <select id="selectUserListByUserTrim" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <!-- 默认使用prefixOverrides去掉前AND 也可以使用suffixOverrides去掉后AND -->

             <trim prefix="where" suffixOverrides="AND|OR">

                 <if test="u_sex != null">

                     u_sex = #{u_sex} AND

                 </if>

                 <if test="u_username != null">

                     u_username like "%"#{u_username}"%" AND

                 </if>

                 <if test="u_cid != null">

                     u_cid = #{u_cid} AND

                 </if>

             </trim>

     </select>

     <update id="updateSetUser" parameterType="User">

         <!-- 用户名  用户密码 性别 用id来限制 -->

         update user

         <set>

             <if test="u_username != null and u_username != '' ">

                 u_username = #{u_username},

             </if>

              <if test="u_password != null and u_password != '' ">

                 u_password = #{u_password},

             </if>

              <if test="u_sex != null and u_sex != '' ">

                 u_sex = #{u_sex},

             </if>

         </set>

         Where u_id= #{u_id}

     </update>

     <!-- 使用多个id获得用户列表 (1,3,5) -->

     <select id="selectUserListByIds" resultType="User">

         select *

         from user

         where u_id

         in

         <!-- (1，3，5) separator可以组拼逗号 -->

         <foreach collection="array" item="id" open="(" close=")" separator=",">

             #{id}

         </foreach>

     </select>

</mapper>

UserMapper.xml

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest11 {  

    @Test

    public void Test11() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        Integer[] ids = {1,3,5,7};

        List<User> list = mapper.selectUserListByIds(ids);

        for(User user:list) {

            System.out.println(user);

        }

    }

}

MapperTest11.java

6、sql标签 – 可以提取重复sql语句片段；

　　在Userapper.xml中实现<sql>标签

     <sql id="myselect">

         select * from user

     </sql>

　　之后在UserMapper.xml中可以用<include>把重复引用的sql语句片段加入进去

     <!-- 使用多个id获取用户列表 by userVO -->

     <select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">

         <include refid="myselect"/>

         where u_id

         in

         <!-- (1，3，5) separator可以组拼逗号 -->

         <foreach collection="idList" item="id" open="(" close=")" separator=",">

             #{id}

         </foreach>

     </select>
<?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="com.Gary.mapper.UserMapper">

     <sql id="myselect">

         select * from user

     </sql>

     <select id="selectUserById" parameterType="Integer" resultType="user">

         select * from user where u_id = #{id}

     </select>

     <!-- #{}占位符 尽量使用#{}来解决问题 -->

     <!-- ${}字符串拼接   容易sql注入 (or 1 = 1) -->

     <!-- ${value}中间的字符串一定需要使用value -->

     <select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">

         <!-- select * from user where u_username like '%${value}%' -->

              select * from user where u_username like "%"#{name}"%"

     </select>

     <!-- 添加用户 参数为全包名 -->

     <insert id="insertUser" parameterType="com.Gary.bean.User">

         insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})

     </insert>

    <!-- 根据id修改username字段的语句 -->

     <update id="updateUser" parameterType="com.Gary.bean.User">

         update user set u_username = #{u_username} where u_id = #{u_id}

     </update>

     <!-- 根据id删除用户 -->

     <delete id="deleteUserById" parameterType="Integer">

         delete from user where u_id = #{id}

     </delete>

     <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->

     <select id="selectUserByVoId" parameterType="UserVo" resultType="user">

         select * from user where u_id = #{user.u_id}

     </select>

     <!-- 查询用户的总条数 -->

     <select id="selectUserCount" resultType="Integer">

         select count(*) from user

     </select>

     <!-- 查询所有用户包装类 -->

     <resultMap type="UserVo" id="uservolist">

         <!-- 必须把想要查询数据库的语句都写上 -->

         <id property="u_id" column="u_id"/>

         <id property="u_username" column="u_username"/>

         <id property="u_sex" column="u_sex"/>

         <association property="country" javaType="Country">

             <!-- 必须把想要查询数据库的语句都写上 -->

             <result property="id" column="c_id"/>

             <result property="c_countryname" column="c_countryname"/>

         </association>

     </resultMap>

     <select id="selectAllUserVo" resultMap="uservolist">

         SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id

     </select>

     <select id="selectUserListByUser" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <where>

             <if test="u_sex != null">

                 u_sex = #{u_sex}

             </if>

             <if test="u_username != null">

                 AND u_username like "%"#{u_username}"%"

             </if>

             <if test="u_cid != null">

                 AND u_cid = #{u_cid}

             </if>

             </where>

     </select>

     <select id="selectUserListByUserTrim" parameterType="User" resultType="User">

         <!-- 查询用户性别 模糊用户名 查询用户cid 国际ID -->

         SELECT * FROM user

             <!-- 默认使用prefixOverrides去掉前AND 也可以使用suffixOverrides去掉后AND -->

             <trim prefix="where" suffixOverrides="AND|OR">

                 <if test="u_sex != null">

                     u_sex = #{u_sex} AND

                 </if>

                 <if test="u_username != null">

                     u_username like "%"#{u_username}"%" AND

                 </if>

                 <if test="u_cid != null">

                     u_cid = #{u_cid} AND

                 </if>

             </trim>

     </select>

     <update id="updateSetUser" parameterType="User">

         <!-- 用户名  用户密码 性别 用id来限制 -->

         update user

         <set>

             <if test="u_username != null and u_username != '' ">

                 u_username = #{u_username},

             </if>

              <if test="u_password != null and u_password != '' ">

                 u_password = #{u_password},

             </if>

              <if test="u_sex != null and u_sex != '' ">

                 u_sex = #{u_sex},

             </if>

         </set>

         Where u_id= #{u_id}

     </update>

     <!-- 使用多个id获得用户列表 (1,3,5) -->

     <select id="selectUserListByIds" resultType="User">

         select *

         from user

         where u_id

         in

         <!-- (1，3，5) separator可以组拼逗号 -->

         <foreach collection="array" item="id" open="(" close=")" separator=",">

             #{id}

         </foreach>

     </select>

     <!-- 使用多个id获取用户列表 by userVO -->

     <select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">

         <include refid="myselect"/>

         where u_id

         in

         <!-- (1，3，5) separator可以组拼逗号 -->

         <foreach collection="idList" item="id" open="(" close=")" separator=",">

             #{id}

         </foreach>

     </select>

</mapper>

UserMapper.xml

package com.Gary.test;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Collection;

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;

import org.junit.Test;

import com.Gary.bean.Country;

import com.Gary.bean.CountryVo;

import com.Gary.bean.User;

import com.Gary.bean.UserVo;

import com.Gary.mapper.CountryMapper;

import com.Gary.mapper.UserMapper;

public class MapperTest12 {  

    @Test

    public void Test12() throws IOException {

        //读取配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory

        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);

        //生产一个sqlSession

        SqlSession session = ssf.openSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        List<Integer> idList = new ArrayList<Integer>();

        idList.add(1);

        idList.add(4);

        idList.add(8);

        UserVo vo = new UserVo();

        vo.setIdList(idList);

        List<User> list =mapper.selectUserListByUserVo(vo);

        for(User user:list) {

            System.out.println(user);

        }

    }

}

MapperTest12.java

最新文章

  1. .Net Core Linux centos7行—IOC模块
  2. elk查询语法
  3. Java常见的几种排序算法-插入、选择、冒泡、快排、堆排等
  4. HDU - 5685 Problem A（逆元）
  5. [AngularJS] Test an Angular Component with $componentController
  6. [cc150] check palindrome of a singly linked list
  7. tophat输出结果junction.bed
  8. Javascript学习一
  9. Laravel学习笔记（三）--在CentOS上配置Laravel
  10. ubuntu16.04 安装常见问题解决方案------输入法黑框
  11. NodeJs之定时器与队列
  12. ThinkPHP 数据库操作(一) : 连接数据库
  13. PLS-00357: Table,View Or Sequence reference &#39;SEQ_TRADE_RECODE.NEXTVAL&#39; not allowed in this context
  14. bugfree3.0.1-修改“优先级”为中文引起的PHP Error
  15. Luogu3199 HNOI2009 最小圈 分数规划、SPFA
  16. 洛谷P1135 奇怪的电梯 BFS例题
  17. duilib进阶教程 -- 在MFC中使用duilib (1)
  18. JavaScript笔记 #05# 用Regex辅助生成文章目录
  19. hdu 2065 &quot;红色病毒&quot;问题(快速幂求模)
  20. iOS设置tableViewCell之间的间距（去掉UItableview headerview黏性）

热门文章

  1. （三）调用web服务
  2. Vs2019 C# .net core 将证书添加到受信任的根证书存储失败，出现以下错误：访问控制列表（ACL）结构无效
  3. Joy OI【走廊泼水节】题解--最小生成树推论变式
  4. 6.显示锁Lock 和 线程通信Condition
  5. 【大数据】SmallFile-Analysis-Script
  6. mysql控制台常用命令
  7. vue.js 初步学习
  8. 《数据结构与算法之美》 &lt;03&gt;数组：为什么很多编程语言中数组都从0开始编号？
  9. python基础应用---列表应用
  10. 用正则表达式处理一个复杂字符串（类似json格式）