[大数据]mybatis入门学习(五)-动态SQL

< where > < if > < include > < foreach > < choose > < when > < otherwise > < trim > < bind > 九大标签

执行SQL语句

select * from userinfo where id in (?,?,?)

测试类

import entity.User;
import mapper.PersonMapper;
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        try {
            InputStream inputStream = Resources.getResourceAsStream("config");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
            queryOneByChoose(sqlSession, mapper);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 根据ID查询单一用户信息
     *
     * @param sqlSession
     * @param mapper
     */
    public static void queryOne(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user = new User();
            user.setId(1);
            user.setSex("0");
            List<User> users = mapper.queryOne(user);
            for (User user1 : users) {
                System.out.println(user1.getSex());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据ID查询单一用户信息
     *
     * @param sqlSession
     * @param mapper
     */
    public static void queryOneByChoose(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user = new User();
            user.setId(2);
            List<User> users = mapper.queryOneByChoose(user);
            for (User user1 : users) {
                System.out.println(user1.getPhone());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据ID查询单一用户信息
     *
     * @param sqlSession
     * @param mapper
     */
    public static void queryOneByList(SqlSession sqlSession, PersonMapper mapper) {
        try {
            List<Integer> listUser=new ArrayList<>();
            listUser.add(1);
            listUser.add(9);
            List<User> users = mapper.queryOneByList(listUser);
            System.out.println(users);
            for (User user : users) {
                System.out.println(user.getPhone());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据ID查询单一用户信息
     *
     * @param sqlSession
     * @param mapper
     */
    public static void queryOneByListObj(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user1=new User();
            User user2=new User();
            User user3=new User();
            user1.setId(1);
            user2.setId(2);
            user3.setId(5);
            List<User> listUser=new ArrayList<>();
            listUser.add(user1);
            listUser.add(user2);
            listUser.add(user3);
            List<User> users = mapper.queryOneByListObj(listUser);
            System.out.println(users);
            for (User user : users) {
                System.out.println(user.getPhone());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据ID查询单一用户信息
     *
     * @param sqlSession
     * @param mapper
     */
    public static void queryOneByArray(SqlSession sqlSession, PersonMapper mapper) {
        try {
            int ids[] ={2,10,9};
            List<User> users = mapper.queryOneByArray(ids);
            for (User user1 : users) {
                System.out.println(user1.getSex());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据ID查询单一用户信息
     *
     * @param sqlSession
     * @param mapper
     */
    public static void queryOneByArrayObj(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user1 =new User();
            User user2 =new User();
            User user3 =new User();
            user1.setId(1);
            user2.setId(4);
            user3.setId(2);
            User ids[] ={user1,user2,user3};
            List<User> users = mapper.queryOneByArrayObj(ids);
            for (User user11 : users) {
                System.out.println(user11.getSex());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据ID修改单一用户信息
     * @param sqlSession
     * @param mapper
     */
    public static void update(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user = new User();
            user.setId(1);
            user.setPhone("2");
//        Map map=new HashMap();
//        map.put("id",1);
//        map.put("phone","0");
            int i = mapper.updateOne(user);
            sqlSession.commit();
            System.out.println("row=" + i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据ID删除个人信息
     * @param sqlSession
     * @param mapper
     */
    public static void delete(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user = new User();
            user.setId(4);
            int i = mapper.deleteOne(user);
            sqlSession.commit();
            System.out.println("row=" + i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加个人信息
     * @param sqlSession
     * @param mapper
     */
    public static void add(SqlSession sqlSession, PersonMapper mapper) {
        try {
            User user = new User();
            user.setSn("沈");
            user.setCn("流清");
            user.setSex("1");
            user.setPhone("1123");
            int i = mapper.addOne(user);
            sqlSession.commit();
            System.out.println("row=" + i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Mapper接口

public interface PersonMapper {
    List<User> queryOne(User user);
    List<User> queryOneByList(List<Integer> listUser);
    List<User> queryOneByListObj(List<User> listUser);
    List<User> queryOneByArray(int[] ids);
    List<User> queryOneByArrayObj(User[] user);
    List<User> queryOneByChoose(User user);
    int updateOne(User user);
    int deleteOne(User user);
    int addOne(User user);
}

Mapper.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="mapper.PersonMapper">
    <!--  查询个人信息  -->
    <select id="queryOne" resultMap="sexc" >
        select id,sex,phone from userinfo
        <include refid="queryById"/>
    </select>

    <!--  查询个人信息动态sql  传入参数为list集合  -->
    <select id="queryOneByList" resultMap="sexc" parameterType="list">
        select id,sex,phone from userinfo
        <include refid="queryById_list"/>
    </select>
    <!--  查询个人信息动态sql  传入参数为list集合对象  -->
    <select id="queryOneByListObj" resultMap="sexc" parameterType="list">
        select id,sex,phone from userinfo
        <include refid="queryById_listObj"/>
    </select>

    <!--  查询个人信息动态sql  传入参数为array数组  -->
    <select id="queryOneByArray" resultMap="sexc" >
        select id,sex,phone from userinfo
        <include refid="queryById_array"/>
    </select>

    <!--  查询个人信息动态sql  传入参数为array对象数组  -->
    <select id="queryOneByArrayObj" resultMap="sexc" parameterType="arraylist">
        select id,sex,phone from userinfo
        <include refid="queryById_arrayObj"/>
    </select>

    <select id="queryOneByChoose" resultMap="sexc" >
        select id,sex,phone from userinfo
        <include refid="queryByIdChoose"/>
    </select>

    <!-- 性别类型转换   -->
    <resultMap id="sexc" type="User">
        <result column="sex" property="sex" javaType="String" jdbcType="BIT" typeHandler="BitAndStringConvertor"></result>
    </resultMap>

    <!--  更新个人信息  -->
    <update id="updateOne" parameterType="User" >
        update userinfo set phone=#{phone} where id=#{id}
    </update>

    <!--  删除个人信息  -->
    <delete id="deleteOne" parameterType="User" >
        delete from userinfo where id=#{id}
    </delete>

    <!--  添加个人信息  -->
    <insert id="addOne" parameterType="User" >
        insert into userinfo(sn,cn,sex,phone,note) values(#{sn},#{cn},#{sex,typeHandler=convertor.BitAndStringConvertor},#{phone},#{note})
    </insert>
</mapper>

include 标签指向的 sql 标签

<?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="mapper.PersonMapper">
    <sql id="queryById">
        <where>
            <if test="id != null and id != ''">
                or id =#{id}
            </if>
            <if test="sex != null and sex != '0'">
                or sex =#{sex}
            </if>
        </where>
    </sql>
    <!--  查询个人信息动态sql  参数为list集合  -->
    <sql id="queryById_list">
        <where>
            <if test="list != null and list.size>0">
                <foreach collection="list" item="id" open="and id in (" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </sql>
    <!--  查询个人信息动态sql  参数为list集合对象  -->
    <sql id="queryById_listObj">
        <where>
            <if test="list != null and list.size>0">
                <foreach collection="list" item="user" open="and id in (" close=")" separator=",">
                    #{user.id}
                </foreach>
            </if>
        </where>
    </sql>

    <!--  查询个人信息动态sql  参数为array数组  -->
    <sql id="queryById_array">
        <where>
            <if test="array != null and array.length>0">
                <foreach collection="array" item="id" open=" and id in (" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </sql>
    <!--  查询个人信息动态sql  参数为array对象数组  -->
    <sql id="queryById_arrayObj">
        <where>
            <if test="array != null and array.length>0">
                <foreach collection="array" item="user" open=" and id in (" close=")" separator=",">
                    #{user.id}
                </foreach>
            </if>
        </where>
    </sql>

    <sql id="queryByIdChoose">
        <where>
            <choose>
                <when test="id != null and id != ''">
                    and id =#{id}
                </when>
                <when test="sex != null and sex != ''">
                    and sex =#{sex}
                </when>
                <otherwise>
                    and phone ='43242'
                </otherwise>
            </choose>
        </where>
    </sql>

</mapper>

配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"></properties>
    <typeAliases>
        <package name="entity"/>
        <package name="convertor"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${db.driver}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.username}"/>
                <property name="password" value="${db.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/PersonMapper"/>
        <mapper resource="mapper/SqlMapper"/>
    </mappers>
</configuration>

项目截图