利用spring改造传统jdbc使其支持命名参数形式的SQL

传统JDBC SQL调用形式一般为

   update customer set customerName=?, email=? where id=?
   insert into customer (customerName, email ) values(?, ?)

这种由于？与参数关系的不直观，带来的修改bug以及维护麻烦折磨着后续程序猿。那么，有没有可能通过对代码的改造，实现类似于命名参数SQL支持呢？

     update customer set customerName=:customerName, email=:email  where id=:id
     insert into customer (customerName, email ) values(:customerName, :email)

答案是肯定的， 本文通过对 NamedParameterJdbcTemplate 实现机制源码的解读，借鉴了NamedParameterUtils 源码的方法对上面需求的实现做了可行性验证。

关键代码

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.TreeMap;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

/**
* 
* 将namedParamSQL转换为带参数列表的传统SQL执行
* 
* @author 00fly
* @version [版本号, 2018年11月10日]
* @see [相关类/方法]
* @since [产品/模块版本]
*/
public class ConvertSQLTest
{
   private static final Logger logger = LoggerFactory.getLogger(ConvertSQLTest.class);
   
   private static QueryRunner queryRunner;
   
   static
   {
       MysqlDataSource dataSource = new MysqlDataSource();
       ResourceBundle config = ResourceBundle.getBundle("jdbc");
       dataSource.setUrl(config.getString("jdbc.url"));
       dataSource.setUser(config.getString("jdbc.username"));
       dataSource.setPassword(config.getString("jdbc.password"));
       queryRunner = new QueryRunner(dataSource);
       logger.info("QueryRunner = {}", queryRunner);
   }
   
   @Test
   public void test1()
       throws SQLException
   {
       // 将namedParamSQL转换为带参数列表的传统SQL
       String namedParamSQL = "select id, name from student where id<>:id and (id=:id1 or id=:id2)";
       Map<String, Object> paramMap = new TreeMap<>();
       paramMap.put("id", 1);
       paramMap.put("id1", 2);
       paramMap.put("id2", 3);
       paramMap.put("name", "001");
       paramMap.put("name1", "002");
       paramMap.put("name2", "003");
       String realSql = buildRealSQL(namedParamSQL, paramMap);
       Object[] paramArr = buildValueArray(namedParamSQL, paramMap);
       List<Map<String, Object>> list = queryRunner.query(realSql, new MapListHandler(), paramArr);
       logger.info("★★★★ before: namedParamSQL  = {}", namedParamSQL);
       logger.info("★★★★ before: paramMap = {}", paramMap);
       logger.info("★★★★ execute: realSql  = {}", realSql);
       logger.info("★★★★ execute: paramArr = {}", Arrays.asList(paramArr));
       logger.info("★★★★ execute: result   = {}", list);
   }
   
   @Test
   public void test2()
       throws SQLException
   {
       // IN条件
       String namedParamSQL = "select id, name from student where id in (:ids)";
       Object[] ids = new Integer[] {1, 2, 3, 4, 5};
       Map<String, Object> paramMap = new TreeMap<>();
       paramMap.put("ids", Arrays.asList(ids));
       String realSql = buildRealSQL(namedParamSQL, paramMap);
       Object[] paramArr = buildValueArray(namedParamSQL, paramMap);
       List<Map<String, Object>> list = queryRunner.query(realSql, new MapListHandler(), paramArr);
       logger.info("★★★★ before: namedParamSQL  = {}", namedParamSQL);
       logger.info("★★★★ before: paramMap = {}", paramMap);
       logger.info("★★★★ execute: realSql  = {}", realSql);
       logger.info("★★★★ execute: paramArr = {}", Arrays.asList(paramArr));
       logger.info("★★★★ execute: result   = {}", list);
       
   }
   
   @Test
   public void test3()
       throws SQLException
   {
       // IN条件&&其他条件
       String namedParamSQL = "select id, name from student where id=:id or id in (:ids)";
       Object[] ids = new Integer[] {1, 2, 3, 4, 5};
       Map<String, Object> paramMap = new TreeMap<>();
       paramMap.put("id", 1);
       paramMap.put("ids", Arrays.asList(ids));
       String realSql = buildRealSQL(namedParamSQL, paramMap);
       Object[] paramArr = buildValueArray(namedParamSQL, paramMap);
       List<Map<String, Object>> list = queryRunner.query(realSql, new MapListHandler(), paramArr);
       logger.info("★★★★ before: namedParamSQL  = {}", namedParamSQL);
       logger.info("★★★★ before: paramMap = {}", paramMap);
       logger.info("★★★★ execute: realSql  = {}", realSql);
       logger.info("★★★★ execute: paramArr = {}", Arrays.asList(paramArr));
       logger.info("★★★★ execute: result   = {}", list);
   }
   
   /**
    * 将namedParamSQL转换为带?的传统SQL
    * 
    * @param namedParamSQL 命名参数SQL
    * @param paramMap Map参数
    * @return
    * @see [类、类#方法、类#成员]
    */
   private String buildRealSQL(String namedParamSQL, Map<String, Object> paramMap)
   {
       return NamedParameterUtils.substituteNamedParameters(NamedParameterUtils.parseSqlStatement(namedParamSQL), new MapSqlParameterSource(paramMap));
   }
   
   /**
    * 滤除无效参数列表后，以Object[]返回
    * 
    * @param namedParamSQL 命名参数SQL
    * @param paramMap Map参数
    * @return
    * @see [类、类#方法、类#成员]
    */
   private Object[] buildValueArray(String namedParamSQL, Map<String, Object> paramMap)
   {
       Object[] params = NamedParameterUtils.buildValueArray(namedParamSQL, paramMap);
       List<Object> paramList = new ArrayList<>();
       for (Object obj : params)
       {
           if (List.class.isInstance(obj))
           {
               paramList.addAll((List<?>)obj);
           }
           else
           {
               paramList.add(obj);
           }
       }
       return paramList.toArray();
   }
}

运行结果

2018-11-10 13:33:24 |INFO |main|★★★★ before: namedParamSQL  = select id, name from student where id<>:id and (id=:id1 or id=:id2)|
2018-11-10 13:33:24 |INFO |main|★★★★ before: paramMap = {id=1, id1=2, id2=3, name=001, name1=002, name2=003}|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: realSql  = select id, name from student where id<>? and (id=? or id=?)|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: paramArr = [1, 2, 3]|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: result   = [{id=2, name=Phil}, {id=3, name=Jenny}]|

2018-11-10 13:33:24 |INFO |main|★★★★ before: namedParamSQL  = select id, name from student where id in (:ids)|
2018-11-10 13:33:24 |INFO |main|★★★★ before: paramMap = {ids=[1, 2, 3, 4, 5]}|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: realSql  = select id, name from student where id in (?, ?, ?, ?, ?)|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: paramArr = [1, 2, 3, 4, 5]|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: result   = [{id=1, name=Jack}, {id=2, name=Phil}, {id=3, name=Jenny}]|

2018-11-10 13:33:24 |INFO |main|★★★★ before: namedParamSQL  = select id, name from student where id=:id or id in (:ids)|
2018-11-10 13:33:24 |INFO |main|★★★★ before: paramMap = {id=1, ids=[1, 2, 3, 4, 5]}|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: realSql  = select id, name from student where id=? or id in (?, ?, ?, ?, ?)|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: paramArr = [1, 1, 2, 3, 4, 5]|
2018-11-10 13:33:24 |INFO |main|★★★★ execute: result   = [{id=1, name=Jack}, {id=2, name=Phil}, {id=3, name=Jenny}]|

完整的项目代码请参考：
https://gitee.com/00fly/java-code-frame/tree/master/jdbc