mybatis 04 动态sql

myBatis-conf.xml

Paste_Image.png

userMapper.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="com.xxjqr.dyn_sql.userMapper">
    <resultMap type="User" id="userResultMap">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>
    
    <!-- if -->
    <select id="selectListUseIf" parameterType="User" resultMap="userResultMap" >
        select user_id,user_name,user_age from user_t where 1=1
        <if test="userId!=null">and user_id = #{userId}</if>
        <if test="userName!=null">and user_name = #{userName}</if>   
        <if test="userAge!=0">and user_age = #{userAge}</if>    
    </select>
    <!-- 1=1是为了让sql语句完整 -->
    
    
    <!-- where if -->
    <select id="selectListUseWhere" parameterType="User" resultMap="userResultMap" >
        select user_id,user_name,user_age from user_t
        <where>
            <if test="userId!=null">and user_id = #{userId}</if>
            <if test="userName!=null">and user_name = #{userName}</if>   
            <if test="userAge!=0">and user_age = #{userAge}</if>    
        </where>
    </select>
    <!-- 如果if中有语句满足，那么会自动加上where并且会自动去掉第一个and -->
    
    
    <!-- where choose when otherwise -->
    <select id="selectListUseChoose" parameterType="User" resultMap="userResultMap" >
        select user_id,user_name,user_age from user_t 
        <where>
            <choose>
                <when test="userId!=null">and user_id = #{userId}</when>
                <when test="userName!=null">and user_name = #{userName}</when>
                <when test="userAge!=0">and user_age = #{userAge}</when>
                <otherwise>and 1=2</otherwise>      
            </choose>
        </where>
    </select>
    <!-- 只要满足一个when那么后面的就不会再判断执行了;  and 1=2的作用是：当上面的条件不满足时,不再查询-->
    
    
    <!-- set -->
    <update id="updateUserUseSet" parameterType="User" >
        update user_t
        <set>
            <if test="userName!=null">user_name = #{userName},</if>
            <if test="userAge!=null">user_age=#{userAge},</if>  
        </set>  
        where user_id = #{userId}
    </update>
    <!-- set专用于更新数据;set标签下的条件判断如果满足，那么就会更新该字段数据 -->
    
    
    <!-- foreach -->
    <select id="selectListUseForeach" resultMap="userResultMap">
        select * from user_t where user_id in
            <foreach collection="list" item="userId" separator="," open="(" close=")">
                #{userId} <!-- 这里要等于item的值 -->
            </foreach>
    </select>
    <!--collection的值为"array"表示来的参数为数组（有[]）;值为list,表示来的参数是个list集合类型  -->
    <!-- 
    item:遍历的每个成员名
    separator:成员之间的分割
    open：开始符号
    close：结束符号
    如(1,2,3)
     -->
     
    
    <!-- include -->
    <insert id="insertUserUseInclude" parameterType="User">
        insert into user_t
        <include refid="key"></include>
        values
        <include refid="value"></include>
    </insert>

    <sql id="key">
        <trim suffixOverrides="," prefix="(" suffix=")">
            <if test="userName!=null">user_name,</if>
            <if test="userAge!=0">user_age,</if>
        </trim>
    </sql>
    
    <sql id="value">
        <trim suffixOverrides="," prefix="(" suffix=")"><!-- 先删除,再加) -->
            <if test="userName!=null">#{userName},</if>
            <if test="userAge!=0">#{userAge},</if>
        </trim>
    </sql>
    <!-- 会根据我们对象属性的值来动态创建sql -->
    <!-- trim:
    prefix:在sql语句首添加
    prefixOverrides:在sql语句首删除
    suffix:在sql语句末添加
    suffixOverrides:在sql语句末删除
     -->
</mapper>

类

public class UserDao {
    
    SqlSession session = null;
    // 使用if动态生成sql
    public List<User> selectListUseIf(User user) {
        List<User> users = null;
        try {
            session = MybatisSessionFactory.getSession();
            users = session.selectList(
                    "com.xxjqr.dyn_sql.userMapper.selectListUseIf", user);
            session.commit();
        } catch (Exception e) {
            
            e.printStackTrace();
            
        } finally {
            try {
                MybatisSessionFactory.closeSession();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }

        return users;
    }

    // 使用where动态生成sql
    public List<User> selectListUseWhere(User user) {
        List<User> users = null;
        try {
            session = MybatisSessionFactory.getSession();
            users = session.selectList(
                    "com.xxjqr.dyn_sql.userMapper.selectListUseWhere", user);
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                MybatisSessionFactory.closeSession();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }

        return users;
    }

    // 使用choose动态生成sql
    public List<User> selectListUseChoose(User user) {
        List<User> users = null;
        try {
            session = MybatisSessionFactory.getSession();
            users = session.selectList(
                    "com.xxjqr.dyn_sql.userMapper.selectListUseChoose", user);
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                MybatisSessionFactory.closeSession();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }

        return users;
    }
    
    
    // 使用Set动态生成sql
    public int updateUserUseChoose(User user) {
        int  i =0;
        try {
            session = MybatisSessionFactory.getSession();
            i = session.update("com.xxjqr.dyn_sql.userMapper.updateUserUseSet", user);
            session.commit();
        } catch (Exception e) {
            
            e.printStackTrace();
            session.rollback();
        } finally {
            try {
                MybatisSessionFactory.closeSession();
                session.rollback();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }

        return i;
    }
    
    
    // 使用foreach动态生成sql
    public List<User> selectListUseForeach(List<Integer> userIds) {
        List<User> users = null;
        try {
            session = MybatisSessionFactory.getSession();
            users = session.selectList(
                    "com.xxjqr.dyn_sql.userMapper.selectListUseForeach", userIds);
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                MybatisSessionFactory.closeSession();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }

        return users;
    }
    
    // 使用include动态生成sql
        public int insertUserUseInclude(User user) {
            int  i =0;
            try {
                session = MybatisSessionFactory.getSession();
                i = session.insert("com.xxjqr.dyn_sql.userMapper.insertUserUseInclude", user);
                session.commit();
            } catch (Exception e) {
                
                e.printStackTrace();
                session.rollback();
            } finally {
                try {
                    MybatisSessionFactory.closeSession();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }

            return i;
        }
}

public class TestUserDao {

    private UserDao userDao = new UserDao();
    
    @Test
    public void test() {
        User user = new User();
        int i = 0;

        
        List<User> list = null;
//      user.setUserId(1);
//      list= userDao.selectListUseIf(user);
        
//      list = userDao.selectListUseWhere(user);
        
//      list = userDao.selectListUseChoose(user);
        
        
//      List<Integer> queryList = new ArrayList<Integer>();
//      queryList.add(1);
//      queryList.add(15);
//      queryList.add(16);
//      list = userDao.selectListUseForeach(queryList);
        
//      System.out.println(list);
        
        
//      user.setUserAge(121);
//      user.setUserName("丁老头");
//      i = userDao.updateUserUseChoose(user);
        
        user.setUserAge(232);
        user.setUserName("张三丰");
        i = userDao.insertUserUseInclude(user);
        System.out.println("受影响行数："+i);
    }

}

@Data
public class User {
    private int userId;
    private String userName;
    private int userAge;
}

补充：输出mybatis中的sql语句

1. 导入jar包

Paste_Image.png
2. 创建log4j.properties文件放在src下

# Rules reminder:
# DEBUG < INFO < WARN < ERROR < FATAL

# Global logging configuration
log4j.rootLogger=debug,stdout

# My logging configuration...
log4j.logger.cn.jbit.mybatisdemo=DEBUG


## Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n

log4j.logger.org.apache.ibatis=DEBUG
## log4j.logger.org.apache.jdbc.SimpleDataSource=DEBUG
log4j.logger.org.apache.ibatis.jdbc.ScriptRunner=DEBUG
## log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapclientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG