Mybatis之注解实现动态sql

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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

1. 创建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. UnitTest

@Test
public void testInsertPerson(){
SqlSession session = SqlSessionFactoryUtil.openSession();
PersonMapper mapper = session.getMapper(PersonMapper.class);
TPersonInfo person = new TPersonInfo();
person.setName("Java");
person.setAge(18);
mapper.insert(person);
session.commit(); session.close();
} @Test
public void testUpdatePerson(){
SqlSession session = SqlSessionFactoryUtil.openSession();
PersonMapper mapper = session.getMapper(PersonMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("name", "Java");
map.put("age", 18);
List<TPersonInfo> persons = mapper.findByNameAndAge(map);
for (TPersonInfo person : persons) {
person.setName("Mybatis");
person.setAge(22);
mapper.update(person);
}
session.commit(); session.close();
}