发表文章

[最新] mybatis 注解开发-动态SQL

weixin37264997 1月前 0

实体类以及表结构

在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的类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//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

 

1
2
3
4
//动态插入
    @InsertProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="insertEmployeeSql")
    @Options(useGeneratedKeys=true,keyProperty="id")
    int insertEmployee(Employee employee);

 

  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
//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

 

1
2
3
//动态更新
    @UpdateProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="updateEmployeeSql")
    void updateEmployee(Employee employee);

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
//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

1
2
3
//动态删除
@DeleteProvider(type=Intefaceproxy.Dyno.EmployeeDynaSqlProvider.class,method="deleteEmployeeSql")
void deleteEmployee(Employee employee);

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//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();
}

 测试:

 

相关推荐
最新评论 (0)
返回
发表文章
weixin37264997
文章数
32
评论数
0
注册排名
716741