CAE开发日志（4）：SqlWithParams类

1、dao层的几类操作

对于一个应用来说，对数据库的操作的类型一共可以分为几类呢？我认为是六类：

1.getAll类

2.getCount类

3.getOne类

4.save类

5.delete类

6.update

如果再分得粗一点，就是我们经常说的CURD，增删查改了。在上面六类中，第4、5、6类对应的就是增、删、改，当然，按照实际业余需求的话还有可能出现批量增、删、改的需求，不过现在都把批量的操作也算进原来的种类里面吧。

而第1、2、3点其实就是对应查了。想必各位也知道，数据库最灵活最复杂的地方其实就是查了，数据库的一些入门书往往只会花三章的篇幅去介绍增、删、改，但是关于查询却是贯穿全书的，因为数据库的查询，本身就是一门学问。对于第1、2、3点，现在来详细地分别介绍一下它们分别代表的含义。

getAll类指的是查询出一个列表的情况，以CAE作为例子的话，“查询所有的call表”这样的请求往往就是getAll类。getAll类一般的返回值就是一个List，也就是说数据库会有多条匹配的结果。通常getAll类都会带有搜索条件，例如“查询所有歌名含有‘a’的call表，并按照歌曲发售时间排序”这样的请求其实并不少见，“歌名含有‘a’”说明要有WHERE，“按照歌曲发售时间排序”说明要有ORDER BY，这样的请求也算是getAll类。可以看出，getAll类是最复杂、最灵活的一种dao层操作了，我们等一下介绍的SqlWithParams很大程度上就是为了getAll类而服务的。

getCount类指的是查询出符合条件的结果数，以CAE作为例子的话，“查询总共有多少张call表”这样的请求就是getCount类，getCount类可以单独使用，也可以与getAll联合起来使用。其实这是一个很简单的道理，getAll往往需要分页，这时候往往就需要返回前端一个总页数totalPage（而且这个总页数是在某搜索条件下的总页数，不是简单地SELECT COUNT(*)），那么要计算这个这个总页数就需要知道总条数，总条数怎么求呢？就是把传给getAll的条件再一次传给getCount，让getCount去算出来即可。

getOne类比较简单，指的是查询出某条记录的详情信息，以CAE作为例子的话，“查看一个call表的具体信息”这样的请求就是getOne类。简单来说就是详情页，这个一般会使用getAll返回的结果的主键进行查询，所以sql较为固定，不算复杂的查询，这里就不用细说了。

2、getAll的条件解析和sql拼接

getAll类是最复杂，最需要处理的查询，为什么说它复杂呢？主要是在于它的动态性。

动态性的意思是，每次getAll产生的sql语句的结构，必须要按照前端传入的条件动态生成，所以有可能每次生成的sql

都是不同的。

举个例子，现在我们需要查询a表的所有记录，那么我们的sql会这么写：

    SELECT * FROM a;

这是一个最简单的getAll类的例子，但是假如现在条件变一下，我们增加一个condition_a，那么我们的sql会这么写：

    SELECT * FROM a WHERE condition_a = 'xxx';

现在的问题是，你不知道每次前端传过来的condition_a是不是空的，所以你就要加一层判断，伪代码如下：

    if(condition_a != null){        SELECT * FROM a WHERE condition_a = 'xxx';    }    else{        SELECT * FROM a;    }

如果需要判断的condition不止condition_a的话，就需要假如过个if-else了。

实际上我们可以再把问题分解，关键的点其实就在于WHERE子句，只有WHERE子句的拼接是复杂的，问题就转换成

了WHERE子句的字符串拼接问题了。

首先进入Java的世界，我们先把WHERE子句前的部分先定义出来：

    String sql = "SELECT * FROM a WHERE ";

然后，我们假设有三个可能传入的条件condition_a、condition_b、condition_c，于是一段if-else就可以写出来了。

if(condition_a != null){sql+="a = '"+a+"' AND ";}if(condition_b != null){sql+="b = '"+b+"' AND ";}if(condition_c != null){sql+="c = '"+c+"' AND ";}

这样有两个bug：

1、那就是如果condition_a、condition_b、condition_c都是null的情况下，sql就只有一个WHERE了，这也意味着这会

报sql语法错误。

一个解救的办法是，一开始在WHERE加上一个“1=1”的条件，这样如果搜索条件都为空时，sql的WHERE也有一个

“1=1”的参数了。

2、每个条件的AND如果放最后，那么全条件都有时最后一个AND就会多出来；如果放最前，那么全条件都有时第一个

AND就会多出来，还是报语法错误。

那么有没有解决第二点的方法呢？有，那就是不要在sql的最后加，而是使用插入，只要每次在WHERE后面插入条件

就可以解决这个bug，我们同样可以使用condition_a、condition_b、condition_c来模拟一下。

假设condition_a != null，那么有

String sql="SELECT * FROM a WHERE condition_a = 'xxx' AND 1=1";

继续，假设condition_b != null，那么有

String sql="SELECT * FROM a WHERE condition_b = 'xxx' AND condition_a = 'xxx' AND 1=1";

看出妙处了吗？继续，假设condition_c !=null，那么有

String sql="SELECT * FROM a WHERE condition_c = 'xxx' condition_b = 'xxx' AND condition_a = 'xxx' AND 1=1";

可以看到，每次后判断的新条件都会从WHERE后面，也就是WHERE子句的最前面插入，字符串插入的操作具体怎么做，不同语言有不同的操作类，例如Java就是用StringBuilder或者StringBuffer来做，下面是StringBuffer的例子：

StringBuffer buf=new StringBuffer();buf.append("WHERE 1=1");int insertIndex;if(condition_a != null){insertIndex=buf.indexOf("WHERE")+5;buf.insert(insertIndex,"condition_a = '"+condition_a+"' AND ");}if(condition_b != null){insertIndex=buf.indexOf("WHERE")+5;buf.insert(insertIndex,"condition_b = '"+condition_b+"' AND ");}if(condition_c != null){insertIndex=buf.indexOf("WHERE")+5;buf.insert(insertIndex,"condition_c = '"+condition_c+"' AND ");}

不错，动态性已经通过这几个if-else体现出来了，但是现在我们需要把眼光放在另一个地方，那就是sql注入。

sql注入是一种黑客攻击的方式，黑客通过向服务器端发送恶意的sql脚本，让服务器执行已达到破坏的目的，那么上面

这段代码会不会被sql注入呢？

会的。

主要的问题就在于WHERE子句的参数注入方式，目前参数的注入完全是把参数当做字符串的一部分进行传入的，但是

这样是会有sql注入的危险的，解决的方式是使用占位符。

你应该使用过jdbc的PreparedStatement，以及使用“WHERE a = ?”这种方式的查询，这里的“？”就是占位符了。

CAE使用了Spring JDBC作为ORM框架，它也有占位符方式的查询，只要把带“？”的sql语句以及参数数组传进去

query()方法即可，那么，我们在拼装sql的时候需要做些什么动作呢？

最重要的步骤就是，记录下每个“？”所对应的值。我们把上面的代码改进一下，变成下面这个代码：

StringBuffer buf=new StringBuffer();buf.append("WHERE 1=1");int insertIndex;Object[] params=new Object[3];int paramsIndex = 0;if(condition_a != null){insertIndex=buf.indexOf("WHERE")+5;buf.insert(insertIndex,"condition_a = ? ");params[paramsIndex]="'"+a+"'";paramsIndex++;}if(condition_b != null){insertIndex=buf.indexOf("WHERE")+5;buf.insert(insertIndex,"condition_b = ? ");params[paramsIndex]="'"+b+"'";paramsIndex++;}if(condition_c != null){insertIndex=buf.indexOf("WHERE")+5;buf.insert(insertIndex,"condition_c = ?");params[paramsIndex]="'"+c+"'";paramsIndex++;}

我们使用了一个Object数组来存储真实的参数的值，使用Object类型是因为参数的类型可能是String，可能是Integer，可能是Short等等等等。而数组的下标刚好就可以用来指明这是第几个“？”。

还有一个问题是，这个数组一开始要申请多大呢？上面我们申请了3个空间，其实是出于最长参数情况的考虑，当然也

有可能没有搜索条件，导致Object数组没有用武之地，只是白白申请了3个空间的情况。

为了节省空间，我们考虑在最后压缩一下Object数组，例如现在长度为3的Object数组我只用到了2个长度，那么就把

数组压缩成2个长度的数组就好了，于是可以考虑在最后加上如下代码：

Object[] params=new Object[paramsIndex];System.arraycopy(preParams, 0, params, 0, paramsIndex);

我们再new了一个数组，数组的长度就是前面的paramsIndex，也就是有效长度（实际上有多少个条件），然后调用System.arraycopy()来复制原来的数组进这个新数组中，之所以不单纯地使用for循环而是用System.arraycopy()是希望能够提高性能。

到目前为止，getAll类的dao层操作已经大部分解决了。

3、SqlWithParams类的使用

前面我详细介绍了getAll类的处理方法，可是这个题目中提到的SqlWithParams有什么关系呢？

SqlWithParams是我在CAE中封装的一个实体类，下面是它的UML图：

其实，这就是前面所提到的sql以及参数列表params数组的封装，有了SqlWithParams类后，我们在解析完条件、拼装

好sql、准备好参数列表后，就可以把它们全部放进一个从对象里面返回，代码如下：

return new SqlWithParams(sql,params);

然后在spring jdbc的query()中，就可以从SqlWithParams实例中获取WHERE子句和参数列表了。

在SqlWithParams类中还有一个order变量，这个是用来放ORDER BY子句的，有的时候ORDER BY也是需要前端动态指定，那么这个时候最好也是放到SqlWithParams中，具体的实现因为比WHERE要简单所以就不展开了。