Mybatis的动态sql

前言：本项目是使用idea进行开发，数据库使用的是Mysql。
本案例是基于对users表进行动态的增删改查的案例。
项目模块图

mysql数据库的users表：
users表的排版（uid主键自增不为空）

uid	uname	usex	birthday	address
1	张三	男	1998-07-09 11:22:33	湖南长沙

1.搭建maven项目,引入mybatis需要的依赖
默认没有java、resources、test等文件夹，习惯性建立这几个文件夹，建立文件夹的时候注意标记文件夹的用途（不然编译器无法识别，无法编译代码）
pom.xml(注意要将实体包下面的xml文件设置可编译)
pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>Mybatis_04</groupId>
  <artifactId>Mybatis_04</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>Mybatis_04 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!--引入Mybatis依赖 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.1</version>
    </dependency>
    <!--引入MySql依赖 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.43</version>
    </dependency>
    <!-- 引入log4j依赖，可以查看报错信息和sql语句，值的接收与返回等，记得将它的配置文件log4j.properties导入-->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
  </dependencies>

  <build>
    <!--设置java目录下的com.zs.entity的映射文件作为资源文件编译-->
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
        </includes>
      </resource>
    </resources>
    <finalName>Mybatis_04</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.7.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.20.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

Mybatis的和核心配置文件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>
    <!--给com.zs.entity包取别名-->
    <typeAliases>
        <package name="com.zs.entity"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!--配置获取数据库连接实例的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url"
                          value="jdbc:mysql://localhost:3306/zs?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="1234" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/zs/entity/Users.xml"></mapper>
    </mappers>
</configuration>

本案例加入的log4j（显示运行时的详细信息），先在pom.xml导入log4j的依赖，在写一个log4j的简单配置文件log4j.properties

### 把日志信息输出到控制台 ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss} [%c]-[%p] %m%n



### 设置优先级别，以及输出源###
log4j.rootLogger=debug,stdout

2.建立实体包、实体类
com.zs.entity/Users.java

package com.zs.entity;

/**
 * @author 小思
 * @PackageName:com.zs.entity
 * @ClassName: Users
 * @Description:对应数据库的Users表的实体类
 * @date 2018/10/31 20:14
 */
public class Users {
    private Integer uid;//用户的Id
    private String uname;//用户的姓名
    private String usex;//用户的性别
    private String birthday;//用户的性别
    private String address;//用户的地址

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUsex() {
        return usex;
    }

    public void setUsex(String usex) {
        this.usex = usex;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Users(String uname, String usex, String birthday, String address) {
        this.uname = uname;
        this.usex = usex;
        this.birthday = birthday;
        this.address = address;
    }

    public Users(Integer uid, String uname, String usex, String birthday, String address) {
        this.uid = uid;
        this.uname = uname;
        this.usex = usex;
        this.birthday = birthday;
        this.address = address;
    }

    public Users() {
    }

    @Override
    public String toString() {
        return "Users{" +
                "uid=" + uid +
                ", uname='" + uname + '\'' +
                ", usex='" + usex + '\'' +
                ", birthday='" + birthday + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

3.建立实体类对应的方法的接口和映射文件

com.zs.dao/UsersDao.java

package com.zs.dao;

import com.zs.entity.Users;

import java.util.List;
import java.util.Map;

/**
 * @author 小思
 * @PackageName:com.zs.dao
 * @ClassName: UsersDao
 * @Description:映射Users.xml文件中的sql语句的接口
 * @date 2018/10/31 20:20
 */
public interface UsersDao {
    //动态添加用户
    public int addUsers(Users users);
    //动态删除（批量删除，参数为数组）
    public int deleteUsersByArray(int[] uids);
    //动态删除（批量删除，参数为List）
    public int deleteUsersByList(List<Integer> ids);
    //根据Users表的列段动态查询数据
    public List<Users> findUsersByColumn(Users users);
    //动态修改
    public int updateUsers(Map<String,Object> map);


}

Users.xml(注意实体包下面的xml文件需要在pom.xml文件加入可编译的配置，详情见pom.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.zs.dao.UsersDao">

    <!--动态增加-->
    <insert id="addUsers" parameterType="Users">
        insert into users(<include refid="key"></include>) values(<include refid="values"></include>)
    </insert>
    <!--设置动态添加的列段名-->
    <sql id="key">
        <trim suffixOverrides=",">
            <if test="uname!=null">
                uname,
            </if>
            <if test="usex!=null">
                usex,
            </if>
            <if test="birthday!=null">
                birthday,
            </if>
            <if test="address!=null">
                address,
            </if>
        </trim>
    </sql>
    <!--设置动态添加的属性值-->
    <sql id="values">
        <trim suffixOverrides=",">
            <if test="uname!=null">
                #{uname},
            </if>
            <if test="usex!=null">
                #{usex},
            </if>
            <if test="birthday!=null">
                #{birthday},
            </if>
            <if test="address!=null">
                #{address},
            </if>
        </trim>
    </sql>

    <!--动态删除（批量删除,参数为数组）-->
    <delete id="deleteUsersByArray">
        delete from users
        <where>
            uid in
            <!--collection:集合类型；item：集合的变量名；open循环以什么开始；close循环以什么结束,separator:以什么分割 -->
            <foreach collection="array" item="uids" open="(" close=")" separator=",">
                  #{uids}
            </foreach>
        </where>
    </delete>

    <!--动态删除（批量删除,参数为List）-->
    <delete id="deleteUsersByList">
        delete from users
        <where>
            uid in
            <foreach collection="list" item="ids" open="(" close=")" separator=",">
                #{ids}
            </foreach>
        </where>
    </delete>

    <!--动态修改-->
    <update id="updateUsers" parameterType="map">
        update users
        <set>
            <trim suffixOverrides=",">
                <if test="users.uname!=null">
                    uname=#{users.uname},
                </if>
                <if test="users.usex!=null">
                    usex=#{users.usex},
                </if>
                <if test="users.birthday!=null">
                    birthday=#{birthday.usex},
                </if>
                <if test="users.address!=null">
                    address=#{address.usex},
                </if>
            </trim>
        </set>
        <if test="users.uid!=null">
            <where>
                uid=#{users.uid}
            </where>
        </if>


    </update>
    <!--动态查询-->
    <!--id:对应com.zs.dao.UsersDao接口里面的方法名；resultType：对应返回结果类型；parameterType对应参数类型-->
    <select id="findUsersByColumn" resultType="Users" parameterType="Users">
        <!--基础查询-->
        select * from Users
        <!--通过Id查询-->
        <where>
            <trim prefixOverrides="and">
                <!--users是参数Users实例化的变量名-->
                <if test="uid!=null">
                    and uid=#{uid}
                </if>
                <if test="uname!=null and uname!=''">
                    <!--concat：模糊查询的拼接-->
                    and uname like concat('%',#{uname},'%')
                </if>
            </trim>
        </where>
    </select>

</mapper>

4.接口写好后，在对应的Users.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.zs.dao.UsersDao">

<!--动态增加-->
<insert id="addUsers" parameterType="Users">
    insert into users(<include refid="key"></include>) values(<include refid="values"></include>)
</insert>
<!--设置动态添加的列段名-->
<sql id="key">
    <trim suffixOverrides=",">
        <if test="uname!=null">
            uname,
        </if>
        <if test="usex!=null">
            usex,
        </if>
        <if test="birthday!=null">
            birthday,
        </if>
        <if test="address!=null">
            address,
        </if>
    </trim>
</sql>
<!--设置动态添加的属性值-->
<sql id="values">
    <trim suffixOverrides=",">
        <if test="uname!=null">
            #{uname},
        </if>
        <if test="usex!=null">
            #{usex},
        </if>
        <if test="birthday!=null">
            #{birthday},
        </if>
        <if test="address!=null">
            #{address},
        </if>
    </trim>
</sql>

<!--动态删除（批量删除,参数为数组）-->
<delete id="deleteUsersByArray">
    delete from users
    <where>
        uid in
        <!--collection:集合类型；item：集合的变量名；open循环以什么开始；close循环以什么结束,separator:以什么分割 -->
        <foreach collection="array" item="uids" open="(" close=")" separator=",">
            #{uids}
        </foreach>
    </where>
</delete>

<!--动态删除（批量删除,参数为List）-->
<delete id="deleteUsersByList">
    delete from users
    <where>
        uid in
        <foreach collection="list" item="ids" open="(" close=")" separator=",">
            #{ids}
        </foreach>
    </where>
</delete>

<!--动态修改-->
<update id="updateUsers" parameterType="map">
    update users
    <set>
        <trim suffixOverrides=",">
            <if test="users.uname!=null">
                uname=#{users.uname},
            </if>
            <if test="users.usex!=null">
                usex=#{users.usex},
            </if>
            <if test="users.birthday!=null">
                birthday=#{birthday.usex},
            </if>
            <if test="users.address!=null">
                address=#{address.usex},
            </if>
        </trim>
    </set>
    <if test="users.uid!=null">
        <where>
            uid=#{users.uid}
        </where>
    </if>


</update>
<!--动态查询-->
<!--id:对应com.zs.dao.UsersDao接口里面的方法名；resultType：对应返回结果类型；parameterType对应参数类型-->
<select id="findUsersByColumn" resultType="Users" parameterType="Users">
    <!--基础查询-->
    select * from Users
    <!--通过Id查询-->
    <where>
        <trim prefixOverrides="and">
            <!--users是参数Users实例化的变量名-->
            <if test="uid!=null">
                and uid=#{uid}
            </if>
            <if test="uname!=null and uname!=''">
                <!--concat：模糊查询的拼接-->
                and uname like concat('%',#{uname},'%')
            </if>
        </trim>
    </where>
</select>

</mapper>

5.使用单元测试测试动态语句是否成功
在test文件夹下新建com.zs.test包与TestSql.java类
TestSql.java
需要测试哪个方法，就直接取消哪一块的注释，运行即可

package com.zs.test;

import com.zs.dao.UsersDao;
import com.zs.entity.Users;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * @author 小思
 * @PackageName:com.zs.test
 * @ClassName: TestSql
 * @Description:
 * @date 2018/10/31 20:45
 */
public class TestSql {

    private SqlSessionFactory sessionFactory;
    private SqlSession session;
    private Users users;

    @Before
    public void before() {
        sessionFactory = new SqlSessionFactoryBuilder().build(getClass().getClassLoader().getResourceAsStream("mybatis-config.xml"));
        session = sessionFactory.openSession();
    }

    @Test
    public void test() {
        UsersDao ud = session.getMapper(UsersDao.class);
//        //方式一：参数为实体类对象
//        //动态增加
//        Users users = new Users();
//        users.setUname("三");
//        users.setAddress("湖南长沙");
//        int a=ud.addUsers(users);
//        //输出数据库影响条数
//        System.out.println(a);

//        //动态删除（批量删除，参数为数组）
//        int[] uids={4,5};
//        int y=ud.deleteUsersByArray(uids);
//        System.out.println(y);

//        //动态删除（批量删除，参数为List）
//        List<Integer> ids=new ArrayList<>();
//        ids.add(1);
//        ids.add(2);
//        int r=ud.deleteUsersByList(ids);
//        System.out.println(r);


//        //根据Users表的列段动态查询数据
//        Users users = new Users();
//        users.setUid(1);
//        users.setUname("三");
//        List<Users> list = ud.findUsersByColumn(users);
//        for (Users users1 : list) {
//            System.out.println(users1);
//        }

//        //方式二：参数为Map集合
//        Map<String,Object> map=new HashMap<>();
//        Users users = new Users();
//        users.setUid(4);
//        users.setUname("大三");
//        map.put("users",users);
//        int t=ud.updateUsers(map);
//        System.out.println(t);
    }

    @After
    public void after() {
        session.commit();
        session.close();
    }
}

6.例如动态测试添加users
取消这片注释运行项目，然后它会根据用户的列段的值，做动态的添加，何谓动态添加，就是我们设置它的实体类哪些属性有设置值，它数据库语句会自动根据值的有无来拼接sql语句，这里需要注意的是，因为有些属性你没给它设置值，那么数据库的列段是需要设置可以为NULL的

控制台输出:(因为用到了log4j的配置文件，所以输出信息会根据你的配置来输出，想要什么格式输出，可以修改log,properties文件)

查看数据库的数据：

本博文是关于users的动态增删改查，可以结合官网参看这个例子，如果不想自己搭建项目，有完整版的项目在
可以自行下载，配置好数据库方面，直接导入project是可以直接运行的。

说在最后的话：编写实属不易，若喜欢或者对你有帮助记得点赞+关注或者收藏哦~