Mybatis的映射文件的深入

Mybatis的映射文件的深入

1、动态sql语句

1.1动态sql语句概述

1.2、if

<?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.dao.UserMapper">
    <!--查询操作-->
    <select id="findAll" resultType="user">
        select  * from user
    </select>

    <!--根据id进行查询-->
    <select id="findById" parameterType="int" resultType="user">
        select * from user where id=#{id}
    </select>
    <!--if-->
    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>

package com.dao;

import com.domain.User;

import java.io.IOException;
import java.util.List;

public interface UserMapper {
    public List<User> findAll() throws IOException;
    public User findById(int id);
    public List<User> findByCondition(User user);
}

package com.dao.service;

import com.dao.UserMapper;

import com.domain.User;
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.List;

public class userService {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        UserMapper mapper=sqlSession.getMapper(UserMapper.class);
        List<User> all = mapper.findAll();
        System.out.println(all);


        User user = mapper.findById(1);
        System.out.println(user);

        //模拟条件user
        User condition=new User();
        condition.setId(1);
        condition.setUsername("zhangsan");
        condition.setPassword("123");
         List<User> byCondition = mapper.findByCondition(condition);
        System.out.println(byCondition);

    }
}

1.3、foreach

<!--foreach-->
    <!--select * from user where id in(1,2,4)-->
    <select id="findByIds" parameterType="list" resultType="user">
      select * from user
    <where>
        <foreach collection="list" open="id in (" close=")" separator=",">
            #{id}
        </foreach>
    </where>
    </select>

package com.dao;

import com.domain.User;

import java.io.IOException;
import java.util.List;

public interface UserMapper {
    public List<User> findAll() throws IOException;
    public User findById(int id);
    public List<User> findByCondition(User user);
    public List<User> findByIds(List<Integer> ids);
}

  //foreach
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(1);

        List<User> userList = mapper.findByIds(ids);
        System.out.println(userList);

1.4、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="com.dao.UserMapper">
    <!--sql抽取-->
    <sql id="selectUser">select  * from user</sql>
    <!--查询操作-->
    <select id="findAll" resultType="user">
    <include refid="selectUser"></include>
    </select>

    <!--根据id进行查询-->
    <select id="findById" parameterType="int" resultType="user">
        select * from user where id=#{id}
    </select>
    <!--if-->
    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
    <!--foreach-->
    <!--select * from user where id in(1,2,4)-->
    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectUser"></include>
    <where>
        <foreach collection="list" open="id in (" close=")" separator=",">
            #{id}
        </foreach>
    </where>
    </select>
</mapper>