Mybatis之注解实现动态sql

通过注解实现动态sql一共需要三部：1.创建表，2.创建entity类，3.创建mapper类, 4.创建动态sql的Provider类。1.和2.可以参见该系列其他文章，这里主要对3和4进行演示，并编写测试。（单元测试是一个好习惯，可以减少bug，避免后期修复的时间损耗）

创建mapper.java

package com.blueStarWei.mappers;

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

import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;

import com.blueStarWei.entity.TPersonInfo;

public interface PersonMapper {

    
    @SelectProvider(type=PersonDynamicSqlProvider.class, method="select")
    List<TPersonInfo> findByNameAndAge(Map<String,Object> map);
    
    @InsertProvider(type=PersonDynamicSqlProvider.class, method="insert")
    void insert(TPersonInfo person);
    
    @UpdateProvider(type=PersonDynamicSqlProvider.class, method="update")
    void update(TPersonInfo person);
    
}

2. 创建provider.java

package com.blueStarWei.mappers;

import java.util.Map;

import org.apache.ibatis.jdbc.SQL;

import com.blueStarWei.entity.TPersonInfo;

public class PersonDynamicSqlProvider {

    public String insert(TPersonInfo person){
        return new SQL(){
            {
                INSERT_INTO("T_PERSON_INFO");
                if(person.getName() != null){
                    VALUES("name", "#{name}");
                    
                    //VALUES("name", person.getName());
                    //Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Java' in 'field list'
                }
                if(person.getAge() != null){
                    VALUES("age", "#{age}");
                }
            }
        }.toString();
    }
    
    public String update(TPersonInfo person){
        return new SQL(){
            {
                UPDATE("T_PERSON_INFO");
                if(person.getName() != null){
                    SET("name=#{name}");
                }
                if(person.getAge() != null){
                    SET("age=#{age}");
                }
                WHERE("id=#{id}");
            }
        }.toString();
    }
    
    public String select(Map<String,Object> map){
        return new SQL(){
            {
                SELECT("*");
                FROM("T_PERSON_INFO");
                
                StringBuilder whereClause = new StringBuilder();
                if(map.get("name") != null){
                    whereClause.append(" and name like '%").append(map.get("name")).append("%' ");
                }
                if(map.get("age") != null){
                    whereClause.append(" and age = ").append(map.get("age"));
                }
                if(!"".equals(whereClause.toString())){
                    WHERE(whereClause.toString().replaceFirst("and", ""));
                }
            }
        }.toString();
    }
}

 

3.UntilTest

package com.blueStarWei.mappers;

import java.util.Map;

import org.apache.ibatis.jdbc.SQL;

import com.blueStarWei.entity.TPersonInfo;

public class PersonDynamicSqlProvider {

    public String insert(TPersonInfo person){
        return new SQL(){
            {
                INSERT_INTO("T_PERSON_INFO");
                if(person.getName() != null){
                    VALUES("name", "#{name}");
                    
                    //VALUES("name", person.getName());
                    //Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Java' in 'field list'
                }
                if(person.getAge() != null){
                    VALUES("age", "#{age}");
                }
            }
        }.toString();
    }
    
    public String update(TPersonInfo person){
        return new SQL(){
            {
                UPDATE("T_PERSON_INFO");
                if(person.getName() != null){
                    SET("name=#{name}");
                }
                if(person.getAge() != null){
                    SET("age=#{age}");
                }
                WHERE("id=#{id}");
            }
        }.toString();
    }
    
    public String select(Map<String,Object> map){
        return new SQL(){
            {
                SELECT("*");
                FROM("T_PERSON_INFO");
                
                StringBuilder whereClause = new StringBuilder();
                if(map.get("name") != null){
                    whereClause.append(" and name like '%").append(map.get("name")).append("%' ");
                }
                if(map.get("age") != null){
                    whereClause.append(" and age = ").append(map.get("age"));
                }
                if(!"".equals(whereClause.toString())){
                    WHERE(whereClause.toString().replaceFirst("and", ""));
                }
            }
        }.toString();
    }
}