动态sql

pom.xml

 
    
        4.0.0
     
        org.example
        mybatis02
        1.0-SNAPSHOT
     
        
            
                org.mybatis
                mybatis
                3.4.6
            
            
                mysql
                mysql-connector-java
                8.0.28
            
            
                org.projectlombok
                lombok
                1.18.24
            
            
                junit
                junit
                4.12
                test
            
            
                log4j
                log4j
                1.2.17
            
        
     
    

mybatis.xml

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 findByCondition(@Param("name")String name,@Param("money")Double money);

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

    public void update(Account account);

}

AccountMapper

    
        update account
        
            
                name =#{name},
            
            
                money=#{money},
            
            
                isdeleted=#{isdeleted},
            
            
                created=#{created},
            
            
                updated=#{updated}
            
        
        where id=#{id}
    

    



    
        select * from account where 1=1
        
            and name=#{name}
        
        
            and money=#{money}
        
    















    
          
              
                  and name=#{name}
              
              
                  and money=#{money}
              
              
                  and isdeleted=0
              
          
    

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 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 abc = accountDao.findByCondition(null,null);

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

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 findByIds(Integer[] ids);

}

AccountMypper

    
        select * from account where id in
        
            #{id}
        
    
 
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 byIds = accountDao.findByIds(ids);
        System.out.println(byIds);

        session.commit();
        session.close();
    }
 
sql片段
 
 
            id,name,money,isdeleted,created,updated
        
        
            select 
            
                from account
            
                
                    and name =#{name}
                
                
                    and money=#{money}
                
            
        

第一种:转义标签  <

        select * from account where id >#{min} and id <#{max}
     
第二种: 
 

        #{min} and id <#{max}]]>
    

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 findByLike(String name);

}

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

        select * from account where name like concat('%',#{name},'%')
     
2)使用${}
 

        select * from account where name like '%{name}%'
    

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 byMaxMin = accountDao.findByLike("s");
        System.out.println(byMaxMin);
        session.commit();
        session.close();
    }

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

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

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