MyBatis练习2

在昨天的基础上完成多条件查询 ， 修改 的动态SQL编写并测试

1.多条件查询

OwnerMapper.java

    // 动态查询
    public List<Owner> findOwnerByIdOrName(Owner owner);

OwnerMapper.xml

<select id="findOwnerByIdOrName" resultType="com.qiku.pojo.Owner" resultMap="ownerResultMap" parameterType="owner">
        select * from ostore
        <where>
            <if test="id != 0 and id != null">
                and id = #{id}
            </if>
            <if test="shop_owner != null">
                and owner = #{shop_owner}
            </if>
        </where>
    </select>

OwnerMapperTest.java

@Test
    public void findOwnerByIdOrName() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = factory.openSession();

        OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);

        Owner owner = new Owner();
        owner.setName("张三");
        owner.setId(5);
        List<Owner> ownerByIdOrName = mapper.findOwnerByIdOrName(owner);
        sqlSession.commit();
        for (Owner o : ownerByIdOrName){
            System.out.println(o);
        }

    }

结果：

放法2

OwnerMapper.java

    public List<Owner> findByIdAndOwneChoose(Owner owner);

OwnerMapper.xml

<select id="findByIdAndOwneChoose" resultType="com.qiku.pojo.Owner" parameterType="owner" resultMap="ownerResultMap">
        select * from ostore
        <where>
            <choose>
                <when test="id != 0 and id != null">
                    and id = #{id}
                </when>
                <when test="shop_owner != null">
                    and owner = #{shop_owner}
                </when>
                <otherwise>
                    and 1=1
                </otherwise>
            </choose>
        </where>
    </select>

OwnerMapperTest.java

@Test
    public void findByIdAndOwneChoose() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = factory.openSession();

        OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);

        Owner owner = new Owner();
        owner.setName("张三");
        owner.setId(5);
        List<Owner> ownerByIdOrName = mapper.findByIdAndOwneChoose(owner);
        sqlSession.commit();
        for (Owner o : ownerByIdOrName){
            System.out.println(o);
        }

    }

 2.动态修改

OwnerMapper.java

//    动态修改
    public void updateOwner(Owner owner);

OwnerMapper.xml

<!--    动态修改-->
    <update id="updateOwner" parameterType="owner" >
        update ostore
        <set>
            <if test="shop_owner != null">
                owner = #{shop_owner}
            </if>
            <if test="name != null">
                oname = #{name}
            </if>
            <if test="industry != null">
                oindustry = #{industry}
            </if>
            <if test="area != null">
                oarea = #{area}
            </if>
            <if test="iphone != null">
                ophone = #{phone}
            </if>
            <if test="statue != null">
                ostatus = #{statue}
            </if>
            <if test="audit_time != null">
                otime = #{audit_time}
            </if>
        </set>
        where onumber = #{id_number}
    </update>

OwnerMapperTest.java

//动态修改
    @Test
    public void updateOwnerTest() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = factory.openSession();

        OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);

        Owner owner = new Owner();
        owner.setId_number("123456789");
        owner.setShop_owner("呵呵哒");
        mapper.updateUser(owner);
        sqlSession.commit();

    }

结果：

 

3.动态循环遍历查询

3.1集合

OwnerMapper.java

    public List<Owner> findOwnerByList(List<Integer> ids);

OwnerMapper.xml

<select id="findOwnerByList" resultType="com.qiku.pojo.Owner" resultMap="ownerResultMap">
        select * from ostore
        <where>
            <foreach collection="collection" open="id in(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </select>

OwnerMapperTest.java

@Test
    public void findOwnerByListTest() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = factory.openSession();

        OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
        ArrayList<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        List<Owner> ownerByList = mapper.findOwnerByList(list);
        for (Owner o : ownerByList){
            System.out.println(o);
        }

    }

结果

 

3.2数组

OwnerMapper.java

    public List<Owner> findOwnerByArray(Integer[] ids);

OwnerMapper.xml

<select id="findOwnerByArray" resultType="com.qiku.pojo.Owner" parameterType="int" resultMap="ownerResultMap">
        select * from ostore
        <where>
            <foreach collection="array" open="id in(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </select>

OwnerMapperTest.java

@Test
    public void findOwnerByArrayTest() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = factory.openSession();

        OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);

        Integer[] ids = {1,2,3};
        List<Owner> ownerByList = mapper.findOwnerByArray(ids);
        for (Owner o : ownerByList){
            System.out.println(o);
        }

    }

结果：

 

3.3 pojo

先在pojo文件夹下建一个QueryVO.java文件

public class QueryVO {
    private List<Integer> ids;

    public QueryVO() {
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

 SqlMapConfig.xml

        <typeAlias type="com.qiku.pojo.QueryVO" alias="queryvo"></typeAlias>

OwnerMapper.java

    public List<Owner> findUserByVo(QueryVO queryVO);

OwnerMapper.xml

优化：这样可以减少代码的冗余

<!--    定义sql 片段 目的减少sql的冗余重复-->
    <sql id="findOwner">
        select id , owner , onumber , oname , oindustry , oarea , ophone , otime from ostore
    </sql>

 <include refid="findOwner"></include> ：调用上面的方法

<select id="findUserByVo" resultType="com.qiku.pojo.Owner"  parameterType="queryvo" resultMap="ownerResultMap">
        <include refid="findOwner"></include>
        <where>
            <foreach collection="ids" open="id in(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </select>

OwnerMapperTest.java

 @Test
    public void findUserByVoTest() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = factory.openSession();

        OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
        ArrayList<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        list.add(3);
        QueryVO queryVO = new QueryVO();
        queryVO.setIds(list);
        List<Owner> ownerByList = mapper.findUserByVo(queryVO);
        for (Owner o : ownerByList){
            System.out.println(o);
        }

    }