使用字符串拼接方式构造SQL语句的问题
本文提供的分析以Java语言为例,但是原理和语言没有太大关系。
字符串拼接方式的问题:
1. 效率
字符串拼接方式的效率非常低。
首先我们需要知道数据库执行SQL的过程。当数据库收到一个Statement后,数据库引擎会先解析该Statement。在数据库引擎解析一条SQL语句时,先要读取这条SQL语句以提交这条语句要求数据库实现什么操作,接着为执行指令建立一个计划列表,这个过程叫做建立一个查询计划。
建立查询计划的开销非常大。如果每一条SQL语句都需要建立一次,对执行效率的影响将相当的大。而“字符串拼接方式”基本上每一次都要建立新的查询计划,因此它是非常不可取的一种方式。
其次,每一条不同的SQL语句,系统都会建立缓存,字符串拼接方式则由于每次的SQL语句都不同,就需要不断的建立新的SQL语句,同时进行缓存,既浪费时间又浪费系统资源。
对于这两个缺点数据库的设计者早已经提供了解决这个问题的办法,那就是预制语句(Prepared Statements)。
预制语句有两个优点:
l 在做其他应用逻辑的时候,数据库可以提前取得SQL语句,并为它们创建查询计划。
l 为每一个SQL语句建立一个通用的引用,这样在重复访问SQL的时候就可以重用先前的查询计划。
为什么预制语句有这样的优点呢?因为很多情况下需要多次执行同一句SQL语句,只是参数不同。如果使用PreparedStatement,只需要在第一次执行是编译SQL语句,之后的执行效率可以提高。
基于上面两点,建议除非特殊情况,尽量的使用PreparedStatement。
注:实际上数据库中提供了两种预制SQL:PreparedStatement(预制语句),CallableStatement(已存储过程)。对CallableStatement这里不作介绍。
2. 安全性
字符串拼接SQL语句容易遭受SQL注入攻击。
例如下面的SQL语句,其中varname和varpasswd是java变量:
String SQL = "select * from user where name= '"+varname+"' and passwd='"+varpasswd+"'";
如果我们把“' or '1' = '1”作为varpasswd传入进来,那么语句将不辩别实际的参数而直接返回所有的信息。这会导致怎样的后果就不言而喻了。
解决这个问题的方法仍然是PreparedStatement。使用PreparedStatement,这样传入的任何参数内容都不会和原来的语句发生任何匹配的关系。就不会产生上面的注入攻击的问题了。
3. 难以优化
字符串拼接SQL语句优化起来非常的困难。例如要修改SQL语句,在where条件里多加一个条件,这个时候就会发现用字符串拼接是一个灾难,尤其是在SQL语句比较复杂的时候。
解决这个问题的方法仍然是使用PreparedStatement。它可以将SQL与数据分离。这样可以将SQL语句独立出来,专心于SQL语句的编写,而不必担心参数类型、转化等问题,同时增强了SQL语句的可读性。
4. 无法在编译阶段发现更多问题
字符串拼接SQL语句,在编译阶段是不可能发现SQL语句错误的,因为编译阶段它仅仅是一个字符串。这个时候如果SQL语句中where条件的参数类型发生了错误那么只有在执行阶段才能够被发现,对于调试程序,发现问题相当不方便。
解决这个问题的方法仍然是 PreparedStatement。使用它可以在编译阶段发现这些参数类型不匹配等问题,提高编程效率和编码质量。
从上面的分析中可以了解到,解决字符串拼接SQL语句的关键之处在于使用PreparedStatement。它可以在代码的阅读、维护、执行等方面提供更优的特性。
到此为止讨论了使用“字符串拼接方式”的种种缺陷和解决办法,都于PreparedStatement相关,那么究竟有没有使用Statement的地方呢?谈到这个问题就涉及到SQL语句执行的次数的问题了。一般情况下如果对应的SQL语句只执行一次,那么最好使用Statement,因为PreparedStatement在第一次使用的时候花费的代价是非常大的。而PreparedStatement的优点是体现在重复使用的过程中。
典型代码缺陷
典型缺陷一:
缺陷代码如下:
StringBuffer sqlStr = new StringBuffer();
sqlStr.append("update " + t_content_budget + " set");
sqlStr.append("(F_OTHERCITY" + f_table_con + ", ");
...
//省略若干行
sqlStr.append(" from " + t_variation_budget + " where F_ID" + f_table_var + " in (");
sqlStr.append(params);
问题1: StringBuffer或者StringBuilder最重要的作用就是减少字符串拼接时内存碎片的产生。 如果append方法的参数中有“字符串1+字符串2”的操作,内存碎片会随着“+”操作的增加而增加。
这样再使用StringBuffer或者StringBuilder就毫无意义了。
问题2: 如果StringBuffer或者StringBuilder在初始化时没有给出指定长度,虚拟机会给出一个较小的默认长度,当运行时长度不够时,再进行扩容。如果字符串总长较大时,就要经历多次的扩容和复制操作,这样对于时间和空间都是很大的浪费。当可以预测字符串总长时,最好在初始化时给出指定长度。
问题3: StringBuilder是非线程安全的,不过其运行速度要比StringBuffer快。如果不存在线程安全问题,可以考虑使用StringBuilder。
修改后代码如下:
StringBuilder sqlStr = new StringBuilder(128);//初始化时给定长度
//去掉所有字符串”+”的操作
sqlStr.append("update ");
sqlStr.append( t_content_budget);
sqlStr.append( " set");
sqlStr.append("(F_OTHERCITY" );
sqlStr.append( f_table_con );
sqlStr.append(",");
...
sqlStr.append(" from ");
sqlStr.append( t_variation_budget );
sqlStr.append( " where F_ID" );
sqlStr.append( f_table_var );
sqlStr.append(" in (");
sqlStr.append(params);
典型缺陷二:
缺陷代码如下:
//将日期转化为yyyy-MM-dd HH:mm:ss 格式
public static String getDateFormat(Calendar calendar)
{
String strRetValue = calendar.get(Calendar.YEAR) + "-";
String strMonth = "";
…//省略若干变更定义
if (calendar.get(Calendar.MONTH) + 1 <= 9)
{
strMonth = "0" + (calendar.get(Calendar.MONTH) + 1) + "-";
}
else
{
strMonth = (calendar.get(Calendar.MONTH) + 1) + "-";
}
…//省略若干行,时间格式判断操作
strRetValue += strMonth + strDay + " " + strHour + strMinute + strSecond;
return strRetValue;
}
问题1:自己实现日期格式转换,代码量庞大且效率低下。JAVA类库中有一个SimpleDateFormat 类,它是专门用于处理日期格式转换的。使用它可以准确、快捷、方便的实现日期格式转换。
额外的建议:对于日期操作,尽量用日期类自带的方法,如日期加减操作:
Calendar cal=Calendar.getInstance();
Calendar.add(Calendar.HOUR, -6);//六小时之前的日期
修改后代码如下:
//将日期转化为yyyy-MM-dd HH:mm:ss 格式
public static String getDateFormat(Calendar calendar)
{
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar.getTime());
}
典型缺陷三:
缺陷代码如下:
for (int i = 0; i 〈 f_id.size(); i++)
{
obj = (Object[]) f_id.get(i);
params += "'" + obj[0].toString() + "',";
}
params = params.substring(0, params.length() - 1);
问题1:类似这种用特定分隔符拼接字符串的操作,工程中到处都有。自己实现效率低,而且容易出错。可以考虑使用org.apache.commons.lang.StringUtils类的join方法来简化此操作。
修改后代码如下:
import org.apache.commons.lang.StringUtils;
params = "'" + StringUtils.join(f_id.iterator(), "',") + "'";
典型缺陷四:
缺陷代码如下:
private String[] columnNames;
public String[] getColumnNames()
{
return columnNames;
}
问题1:JAVA在传递数组时,传递的是数组句柄的值。也就是说我们实际拿到的是数组的引用。如果我们直接把数组的引用传递给调用者,那调用者就可以任意修改数组中的数据。如果数组是公共的,那么“调用者2”会拿到“调用者1”修改后的数据,调用者之间会互相干扰。因此,返回数组的clone,就可以屏蔽上述问题。
额外的建议:JAVA在传递对象(基本类型和String除外)时,传递的也是句柄的值。所以在处理对象的传递时,也要注意该特性对程序的影响。如果确实需要返回clone对象时,必须重写类中的clone方法。
修改后代码如下:
private String[] columnNames;
public String[] getColumnNames()
{
return columnNames.clone();
}
典型缺陷五:
缺陷代码如下:
SELECT a.f_id_market, a.f_state_market, ...
FROM t_market_budget a,
t_marketdetail_budget b,...
FROM t_marketdetail_budget
WHERE f_addmode_mkdetail = '102001') c
WHERE a.f_eventnum_market = b.f_eventnum_mkdetail
AND a.f_eventtypenum_market = b.f_eventtypenum_mkdetail
AND b.f_unitnum_mkdetail IN ('', '002001001001', '002001001002',… )
AND b.f_eventnum_mkdetail = c.f_eventnum_mkdetail_sy(+)
AND b.f_eventtypenum_mkdetail = c.f_eventtypenum_mkdetail_sy(+)
AND TO_CHAR (ADD_MONTHS (b.f_month_mkdetail, -1), 'yyyy-mm') = c.f_month_mkdetail_sy(+)
AND TO_CHAR (b.f_month_mkdetail, 'yyyy-mm') >= '2010-01'
AND TO_CHAR (b.f_month_mkdetail, 'yyyy-mm') <= '2010-02'
AND b.f_currenencyunit_mkdetail = '103001'
ORDER BY TO_CHAR (b.f_month_mkdetail, 'yyyy-mm') DESC, b.f_id_mkdetail DESC
问题1:上述的SQL语句在实际运行过程中速度慢,调试时其COST为1400。这是一个SQL执行效率优化的问题,通常这种问题可以参考下面两点:
1) 如果函数加在字段上,则该字段的索引就会失效,所以将to_char(b.F_MONTH_MKDETAIL,'yyyy-mm') >= '2010-01'改为b.F_MONTH_MKDETAIL >= to_date('2010-01','yyyy-mm')。
2) 在where条件中使用较多的字段上加索引,如果有几个字段经常在一起使用的,可以考虑加联合索引。
修改后代码1-函数用法:
SELECT a.f_id_market, a.f_state_market, ...
FROM t_market_budget a,
t_marketdetail_budget b,...
FROM t_marketdetail_budget
WHERE f_addmode_mkdetail = '102001') c
WHERE a.f_eventnum_market = b.f_eventnum_mkdetail
AND a.f_eventtypenum_market = b.f_eventtypenum_mkdetail
AND b.f_unitnum_mkdetail IN ('', '002001001001', '002001001002',… )
AND b.f_eventnum_mkdetail = c.f_eventnum_mkdetail_sy(+)
AND b.f_eventtypenum_mkdetail = c.f_eventtypenum_mkdetail_sy(+)
AND TO_CHAR (ADD_MONTHS (b.f_month_mkdetail, -1), 'yyyy-mm') = c.f_month_mkdetail_sy(+)
AND b.F_MONTH_MKDETAIL >= to_date('2010-01','yyyy-mm')
AND b.F_MONTH_MKDETAIL <= to_date('2010-02','yyyy-mm') AND b.f_currenencyunit_mkdetail = '103001'
ORDER BY order by b.F_MONTH_MKDETAIL desc, b.f_id_mkdetail DESC
修改后代码2-加索引:
-T_MARKET_BUDGET增加索引
create index IDX_MARKET_EVENTNUM on T_MARKET_BUDGET (F_EVENTNUM_MARKET);
create index IDX_MARKET_EVENTTYPENUM on T_MARKET_BUDGET (F_EVENTTYPENUM_MARKET);
--T_MARKETDETAIL_BUDGET增加索引
create index IDX_MARKDETAIL_ENUM on T_MARKETDETAIL_BUDGET (F_EVENTNUM_MKDETAIL);
create index IDX_MARKDETAIL_MON on T_MARKETDETAIL_BUDGET (F_MONTH_MKDETAIL);
create index IDX_MARKDETAIL_UNITNUM on T_MARKETDETAIL_BUDGET (F_UNITNUM_MKDETAIL);