在整合ssm的时候,遇到一个多表联合(多表,多条件组合,分页)查询的问题,需要自定义mapper.xml, 但本人又比较反感写xml文件,总感觉配置文件没有注解来的简洁,于是用到了 “注解实现动态sql” ,所以总结一下供日后回顾。
//简单的查询
第一步:定义一个实体类Student用作映射。
public class Student {
private Integer id;
private String username;
private String password;
private String addr;
//Generate Getter and Setter 此处省略
}
第二步:定义mapper, 在对应接口的类上和方法加对应的注解 type指定生成sql的类,method指定用哪个方法。
/**
* Created with IDEA
* author:bigStone
* Date:2019/5/2
**/
@MapperScan //把Mapper交给spring管理
public interface StudentMapper {
@SelectProvider(type = StudentSqlProvider.class, method="select")
List<Student> findAll(Student student) throws Exception;
@InsertProvider(type = StudentSqlProvider.class , method = "insertStudent" )
void insert(Student student) throws Exception;
@DeleteProvider(type = StudentSqlProvider.class, method = "delete")
void delete(Student student) throws Exception;
@UpdateProvider(type = StudentSqlProvider.class, method = "update")
void update(Student student) throws Exception;
}
第三步:定义生成sql的类StudentSqlProvider,并定义需要的方法
import org.apache.ibatis.jdbc.SQL;
import yycg.base.domain.test.Student;
/**
* Created with IDEA
* author:bigStone
* Date:2019/5/2
**/
public class StudentSqlProvider {
//插入
public String insertStudent(Student student) {
SQL sql = new SQL();
sql.INSERT_INTO("student");
if (student.getId() != null) {
sql.VALUES("id", "#{id}");
}
if (student.getUsername() != null) {
sql.VALUES("username", "#{username}");
}
if (student.getPassword() != null) {
sql.VALUES("password", "#{password}");
}
if (student.getAddr() != null) {
sql.VALUES("addr", "#{addr}");
}
return sql.toString();
}
//查询
public String select(Student student) {
return new SQL() {{
SELECT("id, username, password, addr");
FROM("student");
if (student != null) {
if (student.getId() != null) {
WHERE("id = #{id}");
}
if (student.getUsername() != null) {
WHERE("username like '%' #{username} '%'");
}
if (student.getPassword() != null) {
WHERE("password = #{password}");
}
if (student.getAddr() != null) {
WHERE("addr like '%' #{addr} '%' ");
}
}
}}.toString();
}
//删除
public String delete(Student student) {
return new SQL() {{
DELETE_FROM("student");
if (student.getId() != null) {
WHERE("id = #{id}");
}
}}.toString();
}
//更新
public String update(Student student) {
return new SQL() {{
UPDATE("student");
if (student.getUsername() != null) {
SET("username = #{username}");
}
if (student.getPassword() != null) {
SET("password = #{password}");
}
if (student.getAddr() != null) {
SET("addr = #{addr}");
}
WHERE("id = #{id}");
}}.toString();
}
}
然后调用mapper接口的方法即可.