6

SQL_Parser_Parameterize

温绍 edited this page 2017-10-15 13:00:02 -05:00

Table of Contents

• 1. 功能介绍
• 2. SQL参数化
• 2.1 SQL参数化API
• 2.2 SQL参数化DEMO
• 3. 获取具体参数化后的常量值

1. 功能介绍

如果要对SQL做各种统计，通常需要对SQL进行参数化再做统计。比如：

// 原始SQL
select * from t where id = 1
select * from t where id = 2

// 参数化SQL
select * from t where id = ?

2. SQL参数化

2.1 SQL参数化API

package com.alibaba.druid.sql.visitor;

public class ParameterizedOutputVisitorUtils {
    public static String parameterize(String sql, String dbType);
}

2.2 SQL参数化DEMO

import com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils;

final String dbType = JdbcConstants.MYSQL;

String sql = "select * from t where id = 1 or id = 2 or id = 3";
String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
assertEquals("SELECT *\n" +
        "FROM t\n" +
        "WHERE id = ?", psql);

3. 获取具体参数化后的常量值

final String dbType = JdbcConstants.MYSQL;

// 参数化SQL是输出的参数保存在这个List中
List<Object> outParameters = new ArrayList<Object>();

String sql = "select * from t where id = 101 and age = 102 or name = 'wenshao'";
String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType, outParameters);
assertEquals("SELECT *\n" +
        "FROM t\n" +
        "WHERE id = ?\n" +
        "\tAND age = ?\n" +
        "\tOR name = ?", psql);

assertEquals(3, outParameters.size());
assertEquals(101, outParameters.get(0));
assertEquals(102, outParameters.get(1));
assertEquals("wenshao", outParameters.get(2));