在JDBC中使用带参数的SQL语句

ADO.Net中,支持带参数的SQL语句,例如:Select * from Tables where column1=@column1,其中@column1为SQL参数,使用起来非常方便,而JDBC中没有找到此功能,感觉有点不便, 于是想自己实现一个.今天正好看见csdn中有一篇http://blog.csdn.net/wallimn/article/details/3734242 文章,有些感触,于是把自己的实现也写出来.

 

我的思路:

1: 在SQL语句中找到以@开始,以" ", "\t", "\n", "\r", ",", ")", ">", "<", "!", "'", "-", "+", "/"为结束的符号,则会认为是SQL参数.

2: 将SQL语句,按@拆分到一个List中,如果是SQL参数,则在使用的时候,替换为相应的参数值.

分析:

1: 该实现模拟了一个ADO.NET的SQL参数功能(SQLClient下)

2: 坏处是如果SQL语句中原来就包含@的非参数字符串,则会被误认为SQL参数.

 

实现:

1: 定义SQL语句拆分后的对象,应该包含字符串,以及是否是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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
package hij.cache.extension;
 
final class SQLStr {
 
    /**
     * 是否是SQL参数
     */
    private boolean Param;
     
    /**
     * 对应的文本
     */
    private String text;
     
    /**
     * 对应的值,一般为Text去除@
     */
    private String value;
     
    public String getValue() {
        return value;
    }
 
    public boolean isParam() {
        return Param;
    }
 
    public String getText() {
        return text;
    }
     
    public void setText(String text) {
        this.text = text;
        if(text== null) {
            return;
        }
        if (text.indexOf("@") >= 0) {
            Param = true;
        } else {
            Param = false;
        }
 
        this.text = this.text.replace("\r\n", " ").replace("\r", " ").replace("\t", " ").replace("\n", " ");
        if (Param) {
            value = this.text.substring(1);
        }
    }
}

  2: 解析SQL语句,按照@拆分SQL语句,并存储到List<SQLStr>中.

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
package hij.cache.extension;
 
import java.util.ArrayList;
import java.util.List;
 
import hij.util.generic.IFuncP1;
 
/**
 * 解析带参数的SQL语句
 * @author XuminRong
 *
 */
final class ParseSQL {
     
    /**
     * 根据@解析字符串,并存储到List中
     * @param sql
     * @return
     */
    public static List<SQLStr> parase(String sql) {
        List<SQLStr> lst = new ArrayList<SQLStr>();
        if (sql == null) {
            return lst;
        }
        int begin = 0;
        int end = sql.indexOf('@');
        while (end >= 0) {
            String text = sql.substring(begin, end);
            SQLStr param1 = new SQLStr();
            param1.setText(text);
            lst.add(param1);
            begin = end;
            end = getParamEnd(sql, end);
            if (end != -1) {
                text = sql.substring(begin, end);
                SQLStr param2 = new SQLStr();
                param2.setText(text);
                lst.add(param2);
            else {
                break;
            }
             
            begin = end;
            end = sql.indexOf('@', begin);
        }
         
        if (begin < sql.length()) {
            String text = sql.substring(begin, sql.length());
            SQLStr param = new SQLStr();
            param.setText(text);
            lst.add(param);
        }
        return lst;
    }
 
    /**
     * SQL语句中,SQL参数的结束符
     */
    static String[] arr = {" ", "\t", "\n", "\r", ",", ")", ">", "<", "!", "'", "-", "+", "/"};      
     
    /**
     * 查找下一个SQL参数的位置
     * @param sql
     * @param begin
     * @return
     */
    private static int getParamEnd(String sql, int begin) {
        int index = -1;
        for (int i = 0; i < arr.length; i++) {
            int pos = sql.indexOf(arr[i], begin);
            if (index == -1 && pos != -1) {
                index = pos;
                continue;
            }
            if (pos != -1 && pos < index) {
                index = pos;
            }
        }
         
        return index;
    }
 
    /**
     * 根据回调函数创建对象
     * @param lst
     * @param callback
     * @return
     */
    public static String createSQL(List<SQLStr> lst, IFuncP1<String, String> callback) {
        if (lst == null) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < lst.size(); i++) {
            SQLStr info = lst.get(i);
            if (!info.isParam()) {
                sb.append(info.getText());
                continue;
            }
            if (callback == null) {
                return "";
            }
            String ret = callback.handle(info.getValue());
            sb.append(ret == null? "": ret);
        }
        return sb.toString();
    }
}

  

测试代码:

下面是测试代码:

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package hij.cache.extension;
 
import java.util.List;
 
import org.junit.Assert;
import org.junit.Test;
 
import hij.util.generic.IFuncP1;
 
public class TestCacheProxy {
     
    @Test
    public void test_Parse_SQL() {
        String sql = "Select @a @b,@c>,@d<,@e!,@f),'@g',@h\r\n,@i-,@j+,@k/, @l";
        List<SQLStr> lst = ParseSQL.parase(sql);
         
        String target = "";
        for (int i = 0; i < lst.size(); i++) {
            target += lst.get(i).getText();
        }
         
        Assert.assertEquals(sql.replace("\r\n", " ").replace("\r", " ").replace("\t", " "), target);
         
        sql = "Select @a @b,@c>,@d<,@e!,@f),'@g',@h\r\n,@i-,@j+,@k/";
        lst = ParseSQL.parase(sql);
         
        target = "";
        for (int i = 0; i < lst.size(); i++) {
            target += lst.get(i).getText();
        }
 
        Assert.assertEquals(sql.replace("\r\n", " ").replace("\r", " ").replace("\t", " "), target);
        String sql2 = ParseSQL.createSQL(lst, new IFuncP1<String, String>(){
 
            @Override
            public String handle(String v) {
                switch (v) {
                case "a":
                {
                    return "a";
                }
                case "b":
                {
                    return "b";
                }
                case "c":
                {
                    return "c";
                }
                case "d":
                {
                    return "d";
                }
                case "e":
                {
                    return "e";
                }
                case "f":
                {
                    return "f";
                }
                case "g":
                {
                    return "g";
                }
                case "h":
                {
                    return "h";
                }
                case "i":
                {
                    return "i";
                }
                case "j":
                {
                    return null;
                }
                case "k":
                {
                    return "k";
                }
                default:
                {
                    return null;
                }
                }
            }
             
        });
        Assert.assertEquals(sql2, "Select a b,c>,d<,e!,f),'g',h ,i-,+,k/");
    }
    @Test
    public void test_Parse_SQL_2() {
        String sql = "Selecta, b, c, d";
        List<SQLStr> lst = ParseSQL.parase(sql);
        Assert.assertEquals(lst.size(), 1);
    }
}

  

 

备注:

1: IFuncP1:

这是一个接口,是我仿照.NET的委托IFunc定义的一个接口,主要是提供一个有返回值且有一个参数的接口,代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
package hij.util.generic;
 
/**
 * 单参有返回值接口
 * @author XuminRong
 *
 * @param <P>
 * @param <T>
 *
 */
public interface IFuncP1<P, T> {
    public T handle(P p);
}

  

备注2:

1: 看了http://blog.csdn.net/wallimn/article/details/3734242后,发现这个博客的思路比我的好,以后可以参考修改,使用PreparedStatement的内在机制,效率和复杂度应该比自己实现要好.

2: 我当前的实现有问题,我希望能实现:

1) 使用SQL参数

2) 同时可以使用String的format功能,这一点似乎不容易做到.

 

看了http://blog.csdn.net/wallimn/article/details/3734242后,对其进行重构及测试,下面是相关代码:

1: 抽象出一个SQL对象:SQLParams,包含SQL语句和参数Map

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package hij.cache.extension;
 
import java.util.HashMap;
import java.util.Map;
 
public final class SQLParams {
    String sql;
    public String getSql() {
        return sql;
    }
    public void setSql(String sql) {
        this.sql = sql;
    }
    public Map<Integer, String> getParams() {
        return params;
    }
    public void setParams(Map<Integer, String> params) {
        this.params = params;
    }
    Map<Integer, String> params = new HashMap<Integer, String>();
}

  2: 添加SQL参数辅助类:这是对NamedParamSqlUtil的重构.(以一个有单参返回值的接口代替fillParameters的pMap,以@代替:)

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package hij.cache.extension;
 
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
 
import hij.util.generic.IFuncP1;
 
/**
 * SQL参数处理辅助类
 *    参考自:http://blog.csdn.net/wallimn/article/details/3734242
 * @author XuminRong
 *
 */
public final class SQLParamsUtil {
 
    /**
     * 分析处理带命名参数的SQL语句。使用Map存储参数,然后将参数替换成?
     * @param sql
     * @return
     */
    public static SQLParams parse(String sql) {
        SQLParams param = new SQLParams();
        String regex = "(@(\\w+))";
        Pattern p = Pattern.compile(regex);
        Matcher m = p.matcher(sql);
        int idx=1;
        while (m.find()) {
            //参数名称可能有重复,使用序号来做Key
            param.getParams().put(new Integer(idx++), m.group(2));
            //System.out.println(m.group(2));
        }
        String result = sql.replaceAll(regex, "?");
        param.setSql(result);
        return param;
    }
    /**
     * 使用参数值Map,填充pStat
     * @param pStat
     * @param pMap 命名参数的值表,其中的值可以比较所需的参数多。
     * @return
     */
    public static boolean fillParameters(PreparedStatement pStat, SQLParams param, IFuncP1<String,Object> func){
        if (pStat == null || param == null) {
            return false;
        }
        if (param.getParams().size() > 0 && func == null) {
            return false;
        }
        for (Integer key : param.getParams().keySet()) { 
            String paramName = param.getParams().get(key);
            Object val = func.handle(paramName);
            try
            {
                pStat.setObject(key, val);                     
            }
            catch(Exception ex)
            {
                ex.printStackTrace();
                return false;
            }
        }
        return true;
    }
}

  3: 测试程序

1
2
3
4
5
6
@Test
public void test_SQLParams_parse() {
    String sql = "Select @a @b,@c>,@d<,@e!,@f),'@g',@h\r\n,@i-,@j+,@k/, @l";
    SQLParams params = SQLParamsUtil.parse(sql);
     
    Assert.assertEquals("Select ? ?,?>,?<,?!,?),'?',?\r\n,?-,?+,?/, ?", params.getSql()); }

                        Assert.assertEquals(params.getParams().get(3), "c");

  

posted @   荣--  阅读(9922)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示