动态Sql

动态Sql

准备

依赖

User .java

package com.sty.pojo1;

import lombok.Data;

@Data
public class User {
    private int id;
    private String name;
    private String password;


}

UserDao.java

package com.sty.Mapper;

import com.sty.pojo1.User;

import java.util.List;

public interface UserDao {
    /*int  add(User user);
    int  delete(int id);*/
    int  update(User user);
    List<User> queryUserById(List list);
    List<User>  queryUserByName(User user);
    List<User> queryUserByNameAndPassword(User user);
}

Mybatis-config.xml

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--dataBase-->
    <properties resource="db.properties"/>

    <!--log4J-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--Aliases-->
    <typeAliases>
        <typeAlias type="com.sty.pojo1.User" alias="user"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="com/sty/Mapper/UserMapper.xml"/>
    </mappers>

</configuration>

db.properties

driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&userSSL=true
url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&userSSL=true
password=root
username=root

log4j.properties

#将等级为BUG的日志文件输出到控制泰console和文件file中#
log4j.rootLogger=DEBUG,console,file

#控制台输出配置#
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出相关设置#
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/sty.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}[%c]%m%n]

#日志输出级别#
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PrepareStatement=DEBUG

MybatisUtils.java(工具类，获取SqlSession)

package com.sty.Utils;

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;


public class MybatisUtils {
private  static SqlSessionFactory   sqlSessionFactoryBuilder=null;

    static{
        String resource="Mybatis-config.xml";
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream(resource);
            sqlSessionFactoryBuilder = new                      SqlSessionFactoryBuilder().build(resourceAsStream);

        } catch (IOException e) {
            e.printStackTrace();
        }


    }


    public static SqlSession getSession(){


        return   sqlSessionFactoryBuilder.openSession();
    }
}

UserMapper.xml

if

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sty.Mapper.UserDao">
    <!--<if> element-->

    <select id="queryUserByName" parameterType="com.sty.pojo1.User" resultType="user">
        select *
        from mybatis.user
        where 1=1
        <if test="name !=null and name != '' ">
            and name like concat ('%',#{name},'%')
        </if>

    </select>
    </mapper>

Test

  @Test
    public void TestIf(){
        SqlSession session = MybatisUtils.getSession();

        UserDao mapper = session.getMapper(UserDao.class);

        User user = new User();
        user.setName("朱");


        List<User> users = mapper.queryUserByName(user);
        for (User user1 : users) {
            System.out.println(user1);
        }


        session.close();
    }

choose

 <!--choose-->
    <select id="queryUserByNameAndPassword" resultType="user" parameterType="com.sty.pojo1.User">
        select * from mybatis.user where 1=1
        <choose>
            <when test="password != null and password !='' ">
                and password=#{password}
            </when>
            <when test="name != null and name != '' ">
                and name like concat ('%',#{name},'%')
            </when>

            <otherwise>
                and id is not null
            </otherwise>
        </choose>

    </select>

@Test
    public void TestChoose(){
        SqlSession session = MybatisUtils.getSession();

        User user = new User();
        user.setName("朱元璋 ");

        user.setPassword("1996");

        UserDao mapper = session.getMapper(UserDao.class);
        List<User> users = mapper.queryUserByNameAndPassword(user);
        for (User user1 : users) {
            System.out.println(user1);
        }

        session.close();
    }

set

 <!--set,后面，不要忘记-->
    <update id="update" parameterType="com.sty.pojo1.User">
        update mybatis.user
        <set>
            <if test="name != null and name != '' ">
                name=#{name},
            </if>

            <if test="password != null and password != '' ">
                password=#{password},
            </if>
        </set>
        where id = #{id}

    </update>

 @Test
    public void TestSet(){
        SqlSession session = MybatisUtils.getSession();

        UserDao mapper = session.getMapper(UserDao.class);
        User user = new User();
        user.setName("朱允炆");
        user.setPassword("123456");
       user.setId(7);

        int update = mapper.update(user);
        if(update>0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }

        session.commit();


        session.close();
    }

foreach

collection:配置的list是传递过来的参数类型（首字母小写）可以是一个array,list,collection,Map集合键，pojo包装类中数组或者集合类型的属性名

item:配置的是循环中当前的元素

index:配置的是当前元素在集合中夫人位置下标

open，close:配置的是以什么符号将这些集合元素包裹

separator:配置的是各个元素的间隔符

   <!--foreach-->
  <select id="queryUserById" parameterType="list" resultType="user">
      select  *
      from mybatis.user
      where id in
      <foreach collection="list" item="id" index="index"  open="(" separator="," close=")">
          #{id}
      </foreach>
  </select>

   @Test
    public void TestForeach(){
        SqlSession session = MybatisUtils.getSession();

        UserDao mapper = session.getMapper(UserDao.class);
        List<Integer> ids = new ArrayList<Integer>();
          ids.add(0);

          ids.add(5);

          List<User> users = mapper.queryUserById(ids);
        for (User user : users) {
            System.out.println(user);
        }


        session.close();


    }