通过注解实现动态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();
}
}