动态sql

set标签的使用

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>org.example</groupId>
        <artifactId>mybatis02</artifactId>
        <version>1.0-SNAPSHOT</version>
     
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.6</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.28</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.24</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
        </dependencies>
     
    </project>

mybatis.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>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC" />
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                    <property name="url"    value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
                    <property name="username" value="root" />
                    <property name="password" value="**********" />
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="mapper/UserMapper.xml"/>
            <mapper resource="mapper/StudentMapper.xml"/>
            <mapper resource="mapper/AccountMapper.xml"/>
        </mappers>
    </configuration>

log4j.properties添加日志

 log4j.rootLogger=DEBUG, Console
    #Console
    log4j.appender.Console=org.apache.log4j.ConsoleAppender
    log4j.appender.Console.layout=org.apache.log4j.PatternLayout
    log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
    log4j.logger.java.sql.ResultSet=INFO
    log4j.logger.org.apache=INFO
    log4j.logger.java.sql.Connection=DEBUG
    log4j.logger.java.sql.Statement=DEBUG
    log4j.logger.java.sql.PreparedStatement=DEBUG

Account

package com.zsb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
    private Integer id;
    private String name;
    private Double money;
    private Integer isdeleted;
    private Date created;
    private Date updated;
}

AccountDao

package com.zsb.dao;

import com.zsb.entity.Account;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface AccountDao {
    public List<Account> findByCondition(@Param("name")String name,@Param("money")Double money);

    public List<Account> findByCondition02(@Param("name")String name,@Param("money")Double money);

    public void update(Account account);

}

AccountMapper

<?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.zsb.dao.AccountDao">
    <update id="update">
        update account
        <set>
            <if test="name!=null and name!=''">
                name =#{name},
            </if>
            <if test="money!=null">
                money=#{money},
            </if>
            <if test="isdeleted!=null">
                isdeleted=#{isdeleted},
            </if>
            <if test="created!=null">
                created=#{created},
            </if>
            <if test="updated!=null">
                updated=#{updated}
            </if>
        </set>
        where id=#{id}
    </update>

    <!--    <select id="findByCondition" resultType="com.zsb.entity.Account">-->
<!--        select * from account where name =#{name}-->
<!--    </select>-->

    <select id="findByCondition" resultType="com.zsb.entity.Account">
        select * from account where 1=1
        <if test="name!=null and name!=''">
            and name=#{name}
        </if>
        <if test="money!=null">
            and money=#{money}
        </if>
    </select>


<!--    <select id="findByCondition" resultType="com.zsb.entity.Account">-->
<!--        select * from account-->
<!--    <where>-->
<!--        <if test="name!=null and name!=''">-->
<!--            and name=#{name}-->
<!--        </if>-->
<!--        <if test="money!=null">-->
<!--            and money=#{money}-->
<!--        </if>-->
<!--    </where>-->
<!--    </select>-->


    <select id="findByCondition02" resultType="com.zsb.entity.Account">
          <choose>
              <when test="name!=null and name!=''">
                  and name=#{name}
              </when>
              <when test="money!=null">
                  and money=#{money}
              </when>
              <otherwise>
                  and isdeleted=0
              </otherwise>
          </choose>
    </select>
</mapper>

Test测试

package com.zsb.test;

import com.zsb.dao.AccountDao;
import com.zsb.dao.StudentDao;
import com.zsb.entity.Account;
import com.zsb.entity.Student;
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.Test;

import java.io.Reader;
import java.util.Date;
import java.util.List;

public class Test04 {
    @Test
    public void testUpdate() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        AccountDao accountDao = session.getMapper(AccountDao.class);

        Account account = new Account();
        account.setId(1);
        account.setCreated(new Date());
        accountDao.update(account);

        session.commit();
        session.close();
    }

    @Test
    public void testFindOne02() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        //获取接口的映射类
        AccountDao accountDao = session.getMapper(AccountDao.class);

        List<Account> abc = accountDao.findByCondition("",null);

        session.commit();
        session.close();
    }


    @Test
    public void testFindOne() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        //获取接口的映射类
        AccountDao accountDao = session.getMapper(AccountDao.class);

        List<Account> abc = accountDao.findByCondition(null,null);

        session.commit();
        session.close();
    }
}

foreach标签

AccountDao

package com.zsb.dao;

import com.zsb.entity.Account;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface AccountDao {
    

    public List<Account> findByIds(Integer[] ids);

}

AccountMypper

<?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.zsb.dao.AccountDao">
    <select id="findByIds" resultType="com.zsb.entity.Account">
        select * from account where id in
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
</mapper>

Test

@Test
    public void testFindByids() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        AccountDao accountDao = session.getMapper(AccountDao.class);

        Integer[] ids={3,5,6,8,9};
        List<Account> byIds = accountDao.findByIds(ids);
        System.out.println(byIds);

        session.commit();
        session.close();
    }

sql片段

 <sql id="baseColumn">
            id,name,money,isdeleted,created,updated
        </sql>
        <select id="findByCondition" resultType="com.zsb.entity.Account">
            select 
            <include refid="baseColumn"/>
                from account
            <where>
                <if test="name!=null and name!=''">
                    and name =#{name}
                </if>
                <if test="money!=null">
                    and money=#{money}
                </if>
            </where>
        </select>

mybatis映射文件处理特殊字符

第一种:转义标签  <

<select id="findByMaxmin" resultType="com.zsb.entity.Account">
        select * from account where id >#{min} and id &lt;#{max}
    </select>

第二种: <![CDATA[sql]]>

<select id="findByMaxmin" resultType="com.zsb.entity.Account">
        <![CDATA[select * from account where id >#{min} and id <#{max}]]>
    </select>

mybatis完成模糊查询

AccountDao

package com.zsb.dao;

import com.zsb.entity.Account;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface AccountDao {
    public List<Account> findByLike(String name);

}

1)使用字符串函数完成拼接concat

<select id="findByLike" resultType="com.zsb.entity.Account">
        select * from account where name like concat('%',#{name},'%')
    </select>

2)使用${}

<select id="findByLike" resultType="com.zsb.entity.Account">
        select * from account where name like '%{name}%'
    </select>

Test测试

@Test
    public void testLike() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        AccountDao accountDao = session.getMapper(AccountDao.class);

        List<Account> byMaxMin = accountDao.findByLike("s");
        System.out.println(byMaxMin);
        session.commit();
        session.close();
    }

使用${}实际上是字符串拼接，它不能防止sql注入， 而#{}它是预编译，它可以防止sql注入问题，#{}实际使用的PreparedStatement.

MyBatis中#{}和${}的区别是什么?

\#{}是预编译处理,${}是字符替换

在使用 #{}时, MyBatis会将 SQL中的#{}替换成"?”,配合 PreparedStatement 的set方法赋值,这样可以有效的防止SQL注入,保证程序的运行安全。