实体类以及表结构
在mybatis-config.xml中注册mapper接口
--------------------------
动态查询@SelectProvider
EmployeeMapper接口
package Intefaceproxy.Dyno; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.SelectProvider; import model.Employee; public interface EmployeeMapper { //动态查询 type:指定一个类 method:使用这个类中的selectWhitParamSql方法返回的sql字符串 作为查询的语句 @SelectProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="selectWhitParamSql") List<Employee> selectWithParam(Map<String,Object> param); }
返回sql语句的类
package Intefaceproxy.Dyno; import java.util.Map; import org.apache.ibatis.jdbc.SQL; public class EmployeeDynaSqlProvider { //方法中的关键字是区分大小写的 SQL SELECT WHERE //该方法会根据传递过来的map中的参数内容 动态构建sql语句 public String selectWhitParamSql(Map<String, Object> param) { return new SQL() { { SELECT("*"); FROM("tb_employee"); if (param.get("id")!=null) { WHERE("id=#{id}"); } if(param.get("loginname")!=null) { WHERE("loginname=#{loginname}"); } if(param.get("password")!=null) { WHERE("password=#{password}"); } if(param.get("name")!=null) { WHERE("name=#{name}"); } if(param.get("sex")!=null) { WHERE("sex=#{sex}"); } if(param.get("age")!=null) { WHERE("age=#{age}"); } if(param.get("phone")!=null) { WHERE("phone=#{phone}"); } if(param.get("sal")!=null) { WHERE("sal=#{sal}"); } if(param.get("state")!=null) { WHERE("state=#{state}"); } } }.toString(); } }
测试:
当然也可以传递employee对象
接口:
//传递employee对象
@SelectProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="selectWhitEmployeeSql")
List<Employee>selectWithEmployee(Employee employee);
返回sql的类
//selectWhitEmployeeSql
public String selectWhitEmployeeSql(Employee employee) {
return new SQL() {
{
SELECT("*");
FROM("tb_employee");
if (employee.getId()!=null) {
WHERE("id=#{id}");
}
if(employee.getLoginname()!=null) {
WHERE("loginname=#{loginname}");
}
if(employee.getPassword()!=null) {
WHERE("password=#{password}");
}
if(employee.getName()!=null) {
WHERE("name=#{name}");
}
if(employee.getSex()!=null) {
WHERE("sex=#{sex}");
}
}
}.toString();
}
测试:
------------------------------
动态插入@InsertProvider
//动态插入
@InsertProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="insertEmployeeSql")
@Options(useGeneratedKeys=true,keyProperty="id")
int insertEmployee(Employee employee);
//insertEmployeeSql
public String insertEmployeeSql(Employee employee) {
return new SQL() {
{
INSERT_INTO("tb_employee");
if(employee.getLoginname()!=null) {
VALUES("loginname","#{loginname}");
}
if(employee.getPassword()!=null) {
VALUES("password", "#{password}");
}
if(employee.getName()!=null) {
VALUES("name", "#{name}");
}
if(employee.getSex()!=null) {
VALUES("sex", "#{sex}");
}
if(employee.getAge()!=null) {
VALUES("age", "#{age}");
}
if(employee.getPhone()!=null) {
VALUES("phone", "#{phone}");
}
if(employee.getSal()!=null) {
VALUES("sal", "#{sal}");
}
if(employee.getState()!=null) {
VALUES("state", "#{state}");
}
}
}.toString();
}
测试:
-------------------------
@UpdateProvider
//动态更新
@UpdateProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="updateEmployeeSql")
void updateEmployee(Employee employee);
//updateEmployeeSql
public String updateEmployeeSql(Employee employee) {
return new SQL() {
{
UPDATE("tb_employee");
if(employee.getLoginname()!=null) {
SET("loginname=#{loginname}");
}
if(employee.getPassword()!=null) {
SET("password=#{password}");
}
if(employee.getName()!=null) {
SET("name=#{name}");
}
if(employee.getSex()!=null) {
SET("sex=#{sex}");
}
if(employee.getAge()!=null) {
SET("age=#{age}");
}
if(employee.getPhone()!=null) {
SET("phone=#{phone}");
}
if(employee.getSal()!=null) {
SET("sal=#{sal}");
}
if(employee.getState()!=null) {
SET("state=#{state}");
}
WHERE("id=#{id}");
}
}.toString();
}
测试:
----------------------------
@DeleteProvider
//动态删除
@DeleteProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="deleteEmployeeSql")
void deleteEmployee(Employee employee);
//deleteEmployeeSql
public String deleteEmployeeSql(Employee employee) {
return new SQL() {
{
DELETE_FROM("tb_employee");
if(employee.getLoginname()!=null) {
WHERE("loginname=#{loginname}");
}
if(employee.getPassword()!=null) {
WHERE("password=#{password}");
}
if(employee.getName()!=null) {
WHERE("name=#{name}");
}
}
}.toString();
}
测试:
----------------------------------