IT源码网

mybatis注解开发-动态SQL讲解

flyfish 2021年02月13日 编程语言 173 0

mybatis注解开发-动态SQL

实体类以及表结构

在mybatis-config.xml中注册mapper接口

--------------------------

[email protected]

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();
     }

测试:

------------------------------

[email protected]

 

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();
}

 测试:

 

原文地址:https://www.cnblogs.com/Joke-Jay/p/8524722.html

发布评论

分享到:

IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

ssm分页讲解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。