Mybatis的动态sql

傷城~ 2022-04-22 02:14 444阅读 0赞

前言：本项目是使用idea进行开发，数据库使用的是Mysql。  
本案例是基于对users表进行动态的增删改查的案例。  
项目模块图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70]  
mysql数据库的users表：  
users表的排版（uid主键自增不为空）

<table> 
 <thead> 
  <tr> 
   <th>uid</th> 
   <th>uname</th> 
   <th>usex</th> 
   <th>birthday</th> 
   <th>address</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>张三</td> 
   <td>男</td> 
   <td>1998-07-09 11:22:33</td> 
   <td>湖南长沙</td> 
  </tr> 
 </tbody> 
</table>

**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>
      
      <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>
        
        <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis</artifactId>
          <version>3.4.1</version>
        </dependency>
        
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.43</version>
        </dependency>
        
        <dependency>
          <groupId>log4j</groupId>
          <artifactId>log4j</artifactId>
          <version>1.2.17</version>
        </dependency>
      </dependencies>
    
      <build>
        
        <resources>
          <resource>
            <directory>src/main/java</directory>
            <includes>
              <include>**/*.xml</include>
            </includes>
          </resource>
        </resources>
        <finalName>Mybatis_04</finalName>
        <pluginManagement>
          <plugins>
            <plugin>
              <artifactId>maven-clean-plugin</artifactId>
              <version>3.0.0</version>
            </plugin>
            
            <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>
        
        <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
                
                <foreach collection="array" item="uids" open="(" close=")" separator=",">
                      #{uids}
                </foreach>
            </where>
        </delete>
    
        
        <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>
        
        
        <select id="findUsersByColumn" resultType="Users" parameterType="Users">
            
            select * from Users
            
            <where>
                <trim prefixOverrides="and">
                    
                    <if test="uid!=null">
                        and uid=#{uid}
                    </if>
                    <if test="uname!=null and uname!=''">
                        
                        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
            
            <foreach collection="array" item="uids" open="(" close=")" separator=",">
                #{uids}
            </foreach>
        </where>
    </delete>
    
    
    <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>
    
    
    <select id="findUsersByColumn" resultType="Users" parameterType="Users">
        
        select * from Users
        
        <where>
            <trim prefixOverrides="and">
                
                <if test="uid!=null">
                    and uid=#{uid}
                </if>
                <if test="uname!=null and uname!=''">
                    
                    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的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 1]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 3]  
查看数据库的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 4]

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70]: /images/20220422/c25f9987bc074f5c96c0613207d2d511.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 1]: /images/20220422/dd9571fc7e97422da6d0f91e32e085f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 2]: /images/20220422/9cb3a8ac59fe4110b080860665938ebb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 3]: /images/20220422/2168242de8ee4945b9685fdbdf5adb08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plYWw5cw_size_16_color_FFFFFF_t_70 4]: /images/20220422/3bc7f3855c124bf8bfd2e77f63de004a.png