MyBatis基础-04-动态sql

MyBatis_dynamicSQL

代码示例：

Teacher.java:

package com.atguigu.bean;

import java.util.Date;

public class Teacher {

    private Integer id;
    private String name;
    private String course;
    private String address;
    private Date birth;
    /**
     * @return the id
     */
    public Integer getId() {
        return id;
    }
    /**
     * @param id the id to set
     */
    public void setId(Integer id) {
        this.id = id;
    }
    /**
     * @return the name
     */
    public String getName() {
        return name;
    }
    /**
     * @param name the name to set
     */
    public void setName(String name) {
        this.name = name;
    }
    /**
     * @return the course
     */
    public String getCourse() {
        return course;
    }
    /**
     * @param course the course to set
     */
    public void setCourse(String course) {
        this.course = course;
    }
    /**
     * @return the address
     */
    public String getAddress() {
        return address;
    }
    /**
     * @param address the address to set
     */
    public void setAddress(String address) {
        this.address = address;
    }
    /**
     * @return the birth
     */
    public Date getBirth() {
        return birth;
    }
    /**
     * @param birth the birth to set
     */
    public void setBirth(Date birth) {
        this.birth = birth;
    }
    /* (non-Javadoc)
     * @see java.lang.Object#toString()
     */
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + ", course=" + course
                + ", address=" + address + ", birth=" + birth + "]";
    }

}

TeacherDao.java:

package com.atguigu.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.atguigu.bean.Teacher;

public interface TeacherDao {
    
    public Teacher getTeacherById(Integer id);
    
    public List<Teacher> getTeacherByCondition(Teacher teacher);

    //添加@Param("ids")后，配置文件中foreach可以使用collection="ids"
    public List<Teacher> getTeacherByIdIn(@Param("ids")List<Integer> ids);
    
    public List<Teacher> getTeacherByConditionChoose(Teacher teacher);
    
    public int updateTeacher(Teacher teacher);

}

TeacherDao.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.atguigu.dao.TeacherDao">
    <resultMap type="com.atguigu.bean.Teacher" id="teacherMap">
        <id property="id" column="id" />
        <result property="address" column="address" />
        <result property="birth" column="birth_date" />
        <result property="course" column="class_name" />
        <result property="name" column="teacherName" />
    </resultMap>

    <!--抽取可重用的sql语句  -->
    <sql id="selectSql">select * from t_teacher</sql>

    <!--public Teacher getTeacherById(Integer id); -->
    <select id="getTeacherById" resultMap="teacherMap">
        <include refid="selectSql"></include>
        where id=#{id}
    </select>

    <!-- if：判断 -->
    <!--public List<Teacher> getTeacherByCondition(Teacher teacher); -->
    <select id="getTeacherByCondition" resultMap="teacherMap">
        select * from t_teacher
        <!-- test=""：编写判断条件 id!=null：取出传入的javaBean属性中的id的值，判断其是否为空 -->
        <!-- where可以帮我们去除掉前面的and; -->

        <!-- trim：截取字符串 
            prefix=""：前缀；为我们下面的sql整体添加一个前缀 
            prefixOverrides=""： 去除整体字符串前面多余的字符
            suffix=""：为整体添加一个后缀 
            suffixOverrides=""：后面哪个多了可以去掉; -->
        <!-- 我们的查询条件就放在where标签中；每个and写在前面，
            where帮我们自动去除前面多余的and -->
        <trim prefix="where" prefixOverrides="and" suffixOverrides="and">
            <if test="id!=null">
                id > #{id} and
            </if>
            <!-- 空串 "" and； && or: ||； if()：传入非常强大的判断条件；
            OGNL表达式；对象导航图
                Person
                    ===lastName
                    ===email
                    ===Address
                        ===city
                        ===province
                        ===Street
                            ===adminName
                            ===info
                            ===perCount
            方法、静态方法、构造器、xxx
            在mybatis中，传入的参数可以用来做判断；
            额外还有两个东西；
            _parameter：代表传入来的参数；
                1）、传入了单个参数：_parameter就代表这个参数
                2）、传入了多个参数：_parameter就代表多个参数集合起来的map
            _databaseId：代表当前环境
                如果配置了databaseIdProvider：_databaseId就有值
                
             -->
            <!-- 绑定一个表达式的值到一个变量 -->
            <!-- <bind name="_name" value="'%'+name+'%'"/> -->
            <if test="name!=null &amp;&amp; !name.equals(&quot;&quot;)">
                teacherName like #{_name} and
            </if>
            <if test="birth!=null">
                birth_date &lt; #{birth} and
            </if>
        </trim>
    </select>

    <!-- public List<Teacher> getTeacherByIdIn(List<Integer> ids); -->
    <select id="getTeacherByIdIn" resultMap="teacherMap">
        
        SELECT * FROM t_teacher WHERE id IN
        <!-- 帮我们遍历集合的； collection=""：指定要遍历的集合的key 
        close=""：以什么结束 
        index="i"：索引； 
            如果遍历的是一个list； 
                index：指定的变量保存了当前索引 
                item：保存当前遍历的元素的值 
            如果遍历的是一个map： 
                index：指定的变量就是保存了当前遍历的元素的key 
                item：就是保存当前遍历的元素的值
        item="变量名"：每次遍历出的元素起一个变量名方便引用 
        open=""：以什么开始 
        separator=""：每次遍历的元素的分隔符 
            (#{id_item},#{id_item},#{id_item} -->
        <if test="ids.size >0">
            <foreach collection="ids" item="id_item" separator="," open="(" close=")">
                #{id_item}
            </foreach>
        </if>
    </select>

    <!--public List<Teacher> getTeacherByConditionChoose(Teacher teacher); -->
    <select id="getTeacherByConditionChoose" resultMap="teacherMap">
        select * from t_teacher
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="name!=null and !name.equals(&quot;&quot;)">
                    teacherName=#{name}
                </when>
                <when test="birth!=null">
                    birth_date = #{birth}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

    <!-- public int updateTeacher(Teacher teacher); -->
    <update id="updateTeacher">
        UPDATE t_teacher
        <set>
            <if test="name!=null and !name.equals(&quot;&quot;)">
                teacherName=#{name},
            </if>
            <if test="course!=null and !course.equals(&quot;&quot;)">
                class_name=#{course},
            </if>
            <if test="address!=null and !address.equals(&quot;&quot;)">
                address=#{address},
            </if>
            <if test="birth!=null">
                birth_date=#{birth}
            </if>
        </set>
        <where>
            id=#{id}
        </where>
         
    </update>
</mapper>

dbconfig.properties:

username=root
password=root
jdbcUrl=jdbc:mysql://localhost:3306/mybatis_0325?characterEncoding=utf-8&serverTimezone=GMT%2B8
driverClass=com.mysql.cj.jdbc.Driver

log4j.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
 <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
   <param name="Encoding" value="UTF-8" />
   <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) 
" />
   </layout>
 </appender>
 <logger name="java.sql">
   <level value="debug" />
 </logger>
 <logger name="org.apache.ibatis">
   <level value="info" />
 </logger>
 <root>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

mybatis-config.xml:

<?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="dbconfig.properties"></properties>

    <settings>
        <!--驼峰命名    name：配置项的key，value：配置项的值-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 开启延迟加载开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 开启属性按需加载 -->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <typeAliases>
        <!--批量起别名   name：指定包名，默认别名就是类名，不区分大小写-->
        <package name="com.atguigu.bean"/>
        <!--推荐使用全类名-->
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 配置连接池 -->
            <dataSource type="POOLED">
                <!--${}取出配置文件中的值-->
                <property name="driver" value="${driverClass}"/>
                <property name="url" value="${jdbcUrl}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="mybatis/TeacherDao.xml"/>
        <!--<package name="com.atguigu.dao"/>-->
    </mappers>
</configuration>

MyBatisTest.java:

package com.atguigu.test;

import static org.junit.Assert.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.Before;
import org.junit.Test;

import com.atguigu.bean.Teacher;
import com.atguigu.dao.TeacherDao;


public class MyBatisTest {

    // 工厂一个
    SqlSessionFactory sqlSessionFactory;

    @Before
    public void initSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    
    @Test
    public void test() {
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            
            TeacherDao mapper = openSession.getMapper(TeacherDao.class);
            
            //teacher2.setBirth(new Date());
            /*    List<Teacher> list = mapper.getTeacherByCondition(teacher2);*/
            
            
            //List<Teacher> list = mapper.getTeacherByIdIn(Arrays.asList(1,2,3,4,5));
            
            
            Teacher teacher2 = new Teacher();
            //teacher2.setId(1);
            //teacher2.setName("admin");
            List<Teacher> list = mapper.getTeacherByConditionChoose(teacher2);
            
            System.out.println(list);
        } finally {
            openSession.close();
        }
    }

    @Test
    public void test02() {
        SqlSession openSession = sqlSessionFactory.openSession();
        try {

            TeacherDao mapper = openSession.getMapper(TeacherDao.class);

            Teacher teacher = new Teacher();
            teacher.setId(1);
            teacher.setName("张老师");
            mapper.updateTeacher(teacher);

            openSession.commit();
        } finally {
            openSession.close();
        }
    }

}