HelloWorld
登录

Java 实现简单的SQL动态组装工具类

Wesley13

2021-10-11

1338 0 0

第一版

1package com.zh.oukele.util;
2
3import java.util.HashMap;
4import java.util.Iterator;
5import java.util.Map;
6
7public class CreateSqlUtil {
8
9    public static void main(String[] args) {
10
11        Map<String ,Object> map = new HashMap<>();
12        map.put("stuName","欧可乐");
13        map.put("stuAge",20);
14        map.put("stuSex","男");
15        map.put("Key_stuId","ASDF");
16        map.put("Key_stuSex","ASDF");
17        try {
18            System.out.println(getSql("table_name", "delete", map, false, ""));
19        } catch (Exception e) {
20            e.printStackTrace();
21        }
22
23    }
24
25    /**
26     * 动态组装 简单sql语法
27     * @param tableName 表名
28     * @param operation 操作标识符 select|delete|update ,默认为 select
29     * @param mapData 数据的map集合
30     * @param useMySQL true|false , false 为使用动态组装SQL，true为使用自已的sql
31     * @param mySql 自已的sql
32     * 注意：update 这里，where xxx = xxx ,的时候，mapData 里的键必须要有 Key_ 前缀（其他的 并不影响到）
33     *
34     * @return
35     * @throws Exception
36     */
37    public static String getSql(String tableName, String operation, Map<?,?> mapData,boolean useMySQL,String mySql) throws Exception {
38        String sql = null;
39        // 使用组装sql的功能
40        if( !useMySQL){
41            if( !(tableName != null && !tableName.equals("") && tableName.length() > 0 ) ){
42                throw new Exception(" 参数 tableName 的值为空！");
43            }else if( !(mapData != null && !mapData.equals("") && mapData.size() > 0 ) ){
44                throw new Exception(" 参数 mapData 的值为空！");
45            }
46            // 操作标识 默认为 select
47            String operations = "select";
48            String condition = " a.* from " + tableName + " a where ";
49            if( operation != null && !operation.equals("") ){
50                if( operation.equals("update") || operation.equals("UPDATE") ){
51                    operations = "update";
52                    condition = " " + tableName + " a set ";
53                }else if( operation.equals("delete") || operation.equals("DELETE") ){
54                    operations = "delete";
55                    condition = " from " + tableName + " a where ";
56                }else if( operation.equals("insert") || operation.equals("INSERT") ){
57                    operations = "insert";
58                    condition = " into " + tableName + " values (";
59                    String link = "";
60                    Iterator<?> iterator = mapData.keySet().iterator();
61                    while (iterator.hasNext()) {
62                        String next = (String) iterator.next();
63                        condition += link + next;
64                        link = ",";
65                    }
66                    condition += ") values( ";
67                }
68            }
69            String value= "";
70            String link ="";
71            String keyValueOperations = " where ";
72            Iterator<? extends Map.Entry<?, ?>> iterator = mapData.entrySet().iterator();
73            while (iterator.hasNext()) {
74                Map.Entry<?, ?> next = iterator.next();
75                if( next.getValue() instanceof String ){
76                    value = "'" + next.getValue() +"'";
77                }else {
78                    value = "" + next.getValue() +"";
79                }
80                if( next.getKey().toString().lastIndexOf("Key_") == -1 ){
81                    if( !operations.equals("insert")){
82                        if( operations.equals("select") || operations.equals("delete")){
83                            condition += link + "a." + next.getKey();
84                            condition += "=" + value;
85                            link = " and ";
86                        }else {
87                            condition += link + "a." + next.getKey();
88                            condition += "=" + value;
89                            link = ",";
90                        }
91                    }else {
92                        condition += link + value;
93                        link = ",";
94                    }
95                }else {
96                    continue;
97                }
98            }
99
100            // 组装 insert sql 的结尾
101            if( operations.equals("insert") ){
102                condition += ")";
103            }else if(operations.equals("update")){ // 组装 update sql 的结尾
104                condition += " where ";
105                String and = "";
106                Iterator<? extends Map.Entry<?, ?>> iterator1 = mapData.entrySet().iterator();
107                while (iterator1.hasNext()) {
108                    Map.Entry<?, ?> next = iterator1.next();
109                    if( next.getValue() instanceof String ){
110                        value = "'" + next.getValue() +"'";
111                    }else {
112                        value = "" + next.getValue() +"";
113                    }
114                    String key = next.getKey().toString();
115                    if( key.lastIndexOf("Key_") != -1 ){
116                        key =  key.substring(key.indexOf("Key_")+ 4,key.length());
117                       condition += and +"a." +key + "=" + value;
118                       and = " and ";
119                    }
120                }
121            }
122
123            sql = operations + condition;
124        }else { // 不使用组装sql的功能
125            sql = mySql;
126        }
127        return sql;
128    }
129}

使用案例：

1public static void main(String[] args) throws Exception {
2
3        Map<String ,Object> map = new HashMap<>();
4        map.put("stuName","欧可乐");
5        map.put("stuAge",20);
6        map.put("stuSex","");
7        map.put("Key_stuId","XXX");
8        map.put("Key_stuSex","VVV");
9
10        String select = getSql1("table_name", "select", map, false, "");
11        System.out.println(select);
12
13        System.out.println();
14
15        String insert = getSql1("table_name", "insert", map, false, "");
16        System.out.println(insert);
17
18        System.out.println();
19
20        String delete = getSql1("table_name", "delete", map, false, "");
21        System.out.println(delete);
22
23        System.out.println();
24
25        String update = getSql1("table_name", "update", map, false, "");
26        System.out.println(update);
27
28    }

生成的SQL语句：

第二版

修改 版本一组装insert语法时的一些bug，新增组装查询SQL时, 可使用 a.xxx is not null 条件查询

1/**
2     * 动态组装 简单sql语法
3     * @param tableName 表名
4     * @param operation 操作标识符 select|delete|update ,默认为 select
5     * @param mapData 数据的map集合
6     * @param useMySQL true|false , false 为使用动态组装SQL，true为使用自已的sql
7     * @param mySql 自已的sql
8     * 注意：update 这里，where xxx = xxx ,的时候，mapData 里的键必须要有 Key_ 前缀（其他的 并不影响到）
9     *
10     * @return
11     * @throws Exception
12     */
13    public static String getSql2(String tableName, String operation, Map<?,?> mapData,boolean useMySQL,String mySql) throws Exception {
14        String sql = null;
15        // 使用组装sql的功能
16        if( !useMySQL){
17            if( !(tableName != null && !tableName.equals("") && tableName.length() > 0 ) ){
18                throw new Exception(" 参数 tableName 的值为空！");
19            }else if( !(mapData != null && !mapData.equals("") && mapData.size() > 0 ) ){
20                throw new Exception(" 参数 mapData 的值为空！");
21            }
22            // 键组装
23            // 操作标识 默认为 select
24            String operations = "select";
25            String condition = " a.* from " + tableName + " a where ";
26            if( operation != null && !operation.equals("") ){
27                if( operation.equals("update") || operation.equals("UPDATE") ){
28                    operations = "update";
29                    condition = " " + tableName + " a set ";
30                }else if( operation.equals("delete") || operation.equals("DELETE") ){
31                    operations = "delete";
32                    condition = " from " + tableName + " a where ";
33                }else if( operation.equals("insert") || operation.equals("INSERT") ){
34                    operations = "insert";
35                    condition = " into " + tableName + " values (";
36                    String link = "";
37                    Iterator<?> iterator = mapData.keySet().iterator();
38                    while (iterator.hasNext()) {
39                        String next = (String) iterator.next();
40                        if( next.lastIndexOf("Key_") == -1){
41                            condition += link + next;
42                            link = ",";
43                        }
44                    }
45                    condition += ") values( ";
46                }
47            }
48
49            // 值组装
50            String value= "";
51            String link ="";
52            String keyValueOperations = " where ";
53            Iterator<? extends Map.Entry<?, ?>> iterator = mapData.entrySet().iterator();
54            while (iterator.hasNext()) {
55                Map.Entry<?, ?> next = iterator.next();
56                if( next.getValue() instanceof String ){
57                    value = "'" + next.getValue() +"'";
58                }else {
59                    if( next.getValue() == null ){
60                        value = "";
61                    }else {
62                        value = "" + next.getValue() +"";
63                    }
64                }
65                if( next.getKey().toString().lastIndexOf("Key_") == -1 ){
66                    if( !operations.equals("insert")){
67                        if( operations.equals("select") || operations.equals("delete")){
68                            condition += link + "a." + next.getKey();
69                            if( value.equals("") ){
70                                condition += value;
71                            }else {
72                                condition += "=" + value;
73                            }
74                            link = " and ";
75                        }else {
76                            condition += link + " a." + next.getKey();
77                            condition += "=" + value;
78                            link = ",";
79                        }
80                    }else {
81                        condition += link + value;
82                        link = ",";
83                    }
84                }else {
85                    continue;
86                }
87            }
88
89            // 组装 insert sql 的结尾
90            if( operations.equals("insert") ){
91                condition += ")";
92            }else if(operations.equals("update")){ // 组装 update sql 的结尾
93                condition += " where ";
94                String and = "";
95                Iterator<? extends Map.Entry<?, ?>> iterator1 = mapData.entrySet().iterator();
96                while (iterator1.hasNext()) {
97                    Map.Entry<?, ?> next = iterator1.next();
98                    if( next.getValue() instanceof String ){
99                        value = "'" + next.getValue() +"'";
100                    }else {
101                        value = "" + next.getValue() +"";
102                    }
103                    String key = next.getKey().toString();
104                    if( key.lastIndexOf("Key_") != -1 ){
105                        key =  key.substring(key.indexOf("Key_")+ 4,key.length());
106                        condition += and +"a." +key + "=" + value;
107                        and = " and ";
108                    }
109                }
110            }
111
112            sql = operations + condition;
113        }else { // 不使用组装sql的功能
114            sql = mySql;
115        }
116        return sql;
117    }

View Code

使用案例：

1public static void main(String[] args) throws Exception {
2
3        Map<String ,Object> map = new HashMap<>();
4        map.put("stuName","欧可乐");
5        map.put("stuAge",20);
6        map.put("stuSex","");
7        map.put("Key_stuId","XXX");
8        map.put("Key_stuSex","VVV");
9
10        String select = getSql2("table_name", "select", map, false, "");
11        System.out.println(select);
12
13        System.out.println();
14
15        String insert = getSql2("table_name", "insert", map, false, "");
16        System.out.println(insert);
17
18        System.out.println();
19
20        String delete = getSql2("table_name", "delete", map, false, "");
21        System.out.println(delete);
22
23        System.out.println();
24
25        String update = getSql2("table_name", "update", map, false, "");
26        System.out.println(update);
27
28    }

生成的SQL语句：

 简单动态组装select语法案例：

1public static void main(String[] args) throws Exception {
2
3        Map<String ,Object> map = new HashMap<>();
4        map.put("stuName","欧可乐");
5        map.put("stuSex","男");
6        map.put("stuSex is not null or a.stuAge > 19 ",null);
7        
8        String select = getSql2("table_name", "select", map, false, "");
9        System.out.println(select);
10
11    }

生成的SQL语句：

点赞
收藏

评论区

加载中...

相关推荐

MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1

文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s

Oracle 分组与拼接字符串同时使用

SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(

MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_

皕杰报表之UUID

​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为

手写Java HashMap源码

HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22

2020年前端实用代码段，为你的工作保驾护航

有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )

Java 实现简单的SQL动态组装工具类 - HelloWorld