MyBatis注解动态SQL
属性 | 说明 |
---|---|
T SELECT(String… columns) | 开始或追加select子句 |
T FROM(String… tables) | 启动或追加from子句 |
T JOIN(String… joins) | |
T INNER_JOIN(String… joins) | |
T LEFT_OUTER_JOIN(String… joins) | |
T RIGHT_OUTER_JOIN(String… joins) | |
T WHERE(String… conditions) | |
T OR() | |
T AND() | |
T GROUP_BY(String… columns) | |
T HAVING(String… conditions) | |
T ORDER_BY(String… columns) | |
T INSERT_INTO(String tableName) | |
T VALUES(String columns, String values) | |
T DELETE_FROM(String table) | |
T UPDATE(String table) | |
T SET(String… sets) |
##数据表 amd bean
tb_employee
CREATE TABLE tb_employee ( ID INT(11) PRIMARY KEY AUTO_INCREMENT, loginname VARCHAR(18), PASSWORD VARCHAR(18), NAME VARCHAR(18) DEFAULT NULL, SEX CHAR(2) DEFAULT NULL, AGE INT(11) DEFAULT NULL, phone VARCHAR(21), sal DOUBLE, state VARCHAR(18) )
INSERT INTO tb_employee(loginname,PASSWORD,NAME,sex,age,phone,sal,state) VALUES('jack','123456','杰克','男',26,'13902019999',9800,'ACTIVE');
INSERT INTO tb_employee(loginname,PASSWORD,NAME,sex,age,phone,sal,state) VALUES('rose','123456','露丝','女',21,'13902018888',6800,'ACTIVE');
Employee
public class Employee implements Serializable {
private Integer id; // 主键id
private String loginname; // 登录名
private String password; // 密码
private String name; // 真实姓名
private String sex; // 性别
private Integer age; // 年龄
private String phone; // 电话
private Double sal; // 薪水
private String state; // 状态
}
mapper
public interface EmployeeMapper {
// 动态查询
@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
List<Employee> selectWhitParam(Map<String, Object> param);
// 动态插入
@InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insertEmployee(Employee employee);
// 根据id查询
@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
Employee selectEmployeeWithId(Map<String, Object> param);
// 动态更新
@UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
void updateEmployee(Employee employee);
// 动态删除
@DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
void deleteEmployee(Map<String, Object> param);
}
DynamicSQL
public class EmployeeDynaSqlProvider {
// 动态查询
// @SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
// List<Employee> selectWhitParam(Map<String, Object> param);
public String selectWhitParam(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();
}
// 动态插入
// @InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
// @Options(useGeneratedKeys = true, keyProperty = "id")
// int insertEmployee(Employee employee);
public String insertEmployee(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(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
// void updateEmployee(Employee employee);
public String updateEmployee(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(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
// void deleteEmployee(Map<String, Object> param);
public String deleteEmployee(Map<String, Object> param){
return new SQL(){
{
DELETE_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();
}
}