有时候我们需要根据输入条件动态地构建SQL语句。Mybatis提供了各种注解比如InsertProvider等来构建SQL语句。
@SelectProvider
创建一个SqlProvider类,以及提供Sql的方法。
public class TutorDynaSqlProvider
{
public String findTutorByIdSql(int tutorId)
{
return "SELECT TUTOR_ID AS tutorId, NAME, EMAIL FROM TUTORS
WHERE TUTOR_ID=" + tutorId;
}
}
在我们的Mapper中有如下操作:
@SelectProvider(type=TutorDynaSqlProvider.class, method="findTutorByIdSql")
Tutor findTutorById(int tutorId);
这里我们使用了@SelectProvider来指定一个类,及其内部的方法的方法,用来提供需要执行的SQL语句。
这里使用字符串构建SQL语句是非常困难的,并且容易出错。所以Mybatis提供了一个SQL工具类不使用字符串拼接的方式,简化构造动态SQL语句。如下
public class TutorDynaSqlProvider
{
public String findTutorByIdSql(final int tutorId)
{
return new SQL()
{
{
SELECT("tutor_id as tutorId, name, email");
FROM("tutors");
WHERE("tutor_id=" + tutorId);
}
} .toString();
}
}
如果有多个参数可以采取用Map装载:
@SelectProvider(type = TutorDynaSqlProvider.class,
method = "findTutorByNameAndEmailSql")
Tutor findTutorByNameAndEmail(String name, String email);
public String findTutorByNameAndEmailSql(Map<String, Object> map)
{
String name = (String) map.get("param1");
String email = (String) map.get("param2");
//you can also get those values using 0,1 keys
//String name = (String) map.get("0");
//String email = (String) map.get("1");
return new SQL()
{
{
SELECT("tutor_id as tutorId, name, email");
FROM("tutors");
WHERE("name=#{name} AND email=#{email}");
}
} .toString();
}
对于我们join,orderby和groupby:
public class TutorDynaSqlProvider
{
public String selectTutorById()
{
return new SQL()
{
{
SELECT("t.tutor_id, t.name as tutor_name, email");
SELECT("a.addr_id, street, city, state, zip, country");
SELECT("course_id, c.name as course_name, description,
start_date, end_date");
FROM("TUTORS t");
LEFT_OUTER_JOIN("addresses a on t.addr_id=a.addr_id");
LEFT_OUTER_JOIN("courses c on t.tutor_id=c.tutor_id");
WHERE("t.TUTOR_ID = #{id}");
}
} .toString();
}
}