让JAVA的JDBC支持命名参数的SQL语句

------------恢复内容开始------------

第三方依赖

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-lang3</artifactId>
  <version>3.9</version>
  <scope>compile</scope>
</dependency>

命名参数解析类

package com.plopco.jdbc.core.sql;

import com.plopco.jdbc.core.SQLParameter;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @program: com.plopco
 * @description: java命名参数解析器
 * @author: plopco
 **/
public class SqlNamedParamParser {
    private String sql;
    private List<String> names;

    private NamedParamParser(String sql, List<String> names) {
        this.sql = sql;
        this.names = names;
    }

    public String getSql() {
        return sql;
    }

    public List<String> getNames() {
        return names;
    }

    /**
     * 校验是否为 NoSql
     *
     * @param sql
     * @return
     */
    public static boolean isNamedParamSql(String sql) {
        //去除单引号内的内容与单个问好
        sql = sql.replaceAll("(\\'(.*?)\\')|(\\?$)", "");

        Pattern pattern = Pattern.compile(".*((\\?|:).+)");
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            return true;
        }

        return false;
    }

    /**
     * 解析 ?xx 单值命名占位符，:xx 集合命名占位符（目前仅支持List）
     *
     * @param sql
     * @param sqlParameters
     * @return
     */
    public static NamedParamParser parse(String sql, SQLParameter[] sqlParameters) {
        if (sql == null) {
            throw new NullPointerException("SQL String is null");
        }

        Map<String, SQLParameter> sqlParameterMap = null;

        if (sqlParameters != null) {
            sqlParameterMap = Arrays.asList(sqlParameters).stream().collect(Collectors.toMap(SQLParameter::getName, param -> param));
        }

        char c;
        List<String> names = new ArrayList<>();
        StringBuilder sql_builder = new StringBuilder();
        StringBuilder name_builder = new StringBuilder();
        boolean isGroup = false;
        for (int index = 0; index < sql.length(); index++) {
            c = sql.charAt(index);
            sql_builder.append(c);

            if ('\'' == c) {//固定值

                boolean existEndChar = false;//固定值结束符
                //进入 if 条件为字符=“'”故此处默认值得1
                int endCharAmount = 1;
                while (++index < sql.length()) {
                    c = sql.charAt(index);
                    sql_builder.append(c);

                    if (c == '\'') {
                        existEndChar = true;
                        ++endCharAmount;
                    }

                    if (/*处理固定值参数内存在单引号的情况*/endCharAmount % 2 == 0 && ((/*出现固定值结束符，且存在列或参数分隔符*/existEndChar && c == ',')
                            || (/*非处于分组内，出现固定值结束符*/!isGroup && existEndChar)
                            || (/*处于分组内，出现分组结束符且存在固定值结束符*/isGroup && existEndChar && c == ')'))) {
                        break;
                    }
                }
            } else if (c == '(') {//组开始
                isGroup = true;
            } else if (c == ')') {//组结束
                isGroup = false;
            } else if ('?' == c || ':' == c) {

                boolean is_param_array = false;
                if (':' == c) {
                    is_param_array = true;
                    sql_builder.replace(sql_builder.length() - 1, sql_builder.length(), new Character('?').toString());
                }

                StringBuilder sql_inner_builder = new StringBuilder();

                while (++index < sql.length()) {
                    c = sql.charAt(index);
                    if ((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || c == '_' || (c >= '0' && c <= '9')) {
                        name_builder.append(c);
                    } else {
                        sql_inner_builder.append(c);
                        break;
                    }
                }

                names.add(name_builder.toString());
                name_builder.setLength(0);

                if (is_param_array && sqlParameterMap != null) {
                    SQLParameter param_value = sqlParameterMap.get(names.get(names.size() - 1));

                    if (null != param_value) {
                        List<?> param_list = (List<?>) param_value.getValue();
                        List<String> pre_char_list = new ArrayList<>();

                        for (int i = 0, len = param_list.size() - 1; i < len; i++) {
                            pre_char_list.add(new Character('?').toString());
                        }

                        if (pre_char_list.size() > 0) {
                            sql_builder.append(',' + StringUtils.join(pre_char_list, ','));
                        }
                    }
                }

                sql_builder.append(sql_inner_builder.toString());
            }
        }

        return new NamedParamParser(sql_builder.toString(), names);
    }


    public static void main(String[] args) {
        System.out.println(NamedParamParser.parse("select a in(:aaa)", new SQLParameter[]{
                new SQLParameter("aaa", Arrays.asList(new String[]{"1", "2", "3"}))
        }).getSql());
        System.out.println("==================================");
        System.out.println("select a= '?' isNamedParamSql======" + isNamedParamSql("select a= '?'"));
        System.out.println("select a= ? isNamedParamSql======" + isNamedParamSql("select a= ?"));
        System.out.println("select a = :a isNamedParamSql======" + isNamedParamSql("select a = :a"));
        System.out.println("select a = ?a isNamedParamSql======" + isNamedParamSql("select a = ?a"));
        System.out.println("==================================");

        System.out.println(NamedParamParser.parse("select 'a',b,c from dual where a = ?c", null).getSql());
        System.out.println(NamedParamParser.parse("select 'a',b,c from dual where a = ?c", null).getNames());
        System.out.println("==================================");
        System.out.println(NamedParamParser.parse("select 'a','?b',c from dual where a = ?c", null).getSql());
        System.out.println(NamedParamParser.parse("select 'a','?b',c from dual where a = ?c", null).getNames());
        System.out.println("==================================");
        System.out.println(NamedParamParser.parse("insert into(a,b,c) select '<?xml charset='utf-8' version='1.1'><body></body>',?b,?c from dual where d = ?d)", null).getSql());
        System.out.println(NamedParamParser.parse("insert into(a,b,c) select '<?xml charset='utf-8' version='1.1'><body></body>',?b,?c from dual where d = ?d)", null).getNames());
        System.out.println("==================================");
        System.out.println(NamedParamParser.parse("insert into(a,b,c) values('<?xml charset='utf-8' version='1.1'><body></body>',?b,?c)", null).getSql());
        System.out.println(NamedParamParser.parse("insert into(a,b,c) values('<?xml charset='utf-8' version='1.1'><body></body>',?b,?c)", null).getNames());

        System.out.println("==================================");
        String sql = "select b.table_name as slave_table_name,c.table_name as master_table_name from user_constraints a left join" +
                " user_cons_columns b on a.constraint_name = b.constraint_name" +
                " left join user_cons_columns c on a.r_constraint_name = c.constraint_name" +
                " where a.constraint_type = 'R' and a.table_name in(:tableNames)";

        System.out.println(NamedParamParser.parse(sql, null).getSql());
        System.out.println(NamedParamParser.parse(sql, null).getNames());
   
        System.out.println("==================================");
        String sql2 = "SELECT  '456789' AS USERID,\n" +
                "    'GZ-1' AS FACTORYCODE,\n" +
                "    '20201126102001' AS CREATEDATE,\n" +
                "    '1111111' AS PAPERNO,\n" +
                "    '200516065-0-7-6' AS WORKNO,\n" +
                "    '1' AS DEFECT,\n" +
                "    'GRTRBC' AS SCRAPPROCCODE,\n" +
                "    'GDCCB' AS DUTYPROCCODE,\n" +
                "    '5' AS STEP,\n" +
                "    '测试' AS REMARK,\n" +
                "    '10' AS SCRAPQTY,\n" +
                "    '10' AS SCRAPSETQTY,\n" +
                "    '10' AS SCRAPPNLQTY\n" +
                "FROM DUAL";
        System.out.println(NamedParamParser.parse(sql2, null).getSql());
        System.out.println(NamedParamParser.parse(sql2, null).getNames());
    }
}

参数类

package com.plopco.jdbc.core;

import java.sql.SQLException;

 /**
 * @program: com.plopco
 * @description: 数据库参数类
 * @author: plopco
 **/
public class SQLParameter {

	public static final int INPUT = 1;
	public static final int OUT = 2;
	public static final int INPUT_OUT= 3;
	public static final int RETURN_VALUE = 6;

	private int type;//参数类型，输出|输入
	private String name;//名称
	private Object value;//值
	private Integer sqlType;//对应的sql类型java.sql.Types，输入参数可选
	
	public SQLParameter() {
		super();
	}

	/**
	 * 输入参数可用
	 * @param name 名称
	 * @param value 值
	 */
	public SQLParameter(String name, Object value)
	{
		this.name = name;
		this.value = value;
	}

	/**
	 * 输入参数可用
	 * @param type 参数类型，输出|输入
	 * @param name 名称
	 * @param value 值
	 */
	public SQLParameter(int type, String name, Object value)
	{
		this.type = type;
		this.name = name;
		this.value = value;
	}
	
	/**
	 * 通用
	 * @param type //参数类型，输出|输入
	 * @param name //名称
	 * @param value //值
	 * @param sqlType 对应的sql类型java.sql.Types，输入参数可选
	 * @throws SQLException 
	 */
	public SQLParameter(int type, String name, Object value, Integer sqlType)
	{
		this.type = type;
		this.name = name;
		this.value = value;
		this.sqlType = sqlType;
	}
	
	public int getType() {
		return type;
	}
	public void setType(int type) {
		this.type = type;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Object getValue() {
		return value;
	}
	public void setValue(Object value) {
		this.value = value;
	}
	public Integer getSqlType() {
		return sqlType;
	}
	public void setSqlType(Integer sqlType) {
		this.sqlType = sqlType;
	}

	@Override
	public String toString() {
		return "SQLParameter [type=" + type + ", name=" + name + ", value="
				+ value + ", sqlType=" + sqlType + "]";
	}
}

数据库操作类

    /**
     * 扩展实现 ?xx 命名占位符
     * <p>
     *  简单值占位符：“?xxx”，集合占位符：“:xxx”
     * </p>
     * @param connection
     * @param sql
     * @param sqlParameters
     * @return
     * @throws SQLException
     */
    private PreparedStatement createNamedParamSqlPreparedStatement(final Connection connection, final String sql, final SQLParameter[] sqlParameters) throws SQLException {
        NamedParamParser noSql = NamedParamParser.parse(sql, sqlParameters);
        PreparedStatement preparedStatement = connection.prepareStatement(noSql.getSql());

        if (sqlParameters != null) {
            Map<String, SQLParameter> sqlParameterMap = Arrays.asList(sqlParameters).stream().collect(Collectors.toMap(SQLParameter::getName, param -> param));

            int paramIndex = 1;

            for (int i = 0, len = noSql.getNames().size(); i < len; i++) {
                SQLParameter param = sqlParameterMap.get(noSql.getNames().get(i));
                if (param == null) {
                    log.info("noSql.getNames().get(i):" + noSql.getNames().get(i));
                }
                Object value = param.getValue();

                if (value instanceof List) {

                    if (null != value) {
                        List<?> param_list = (List<?>) value;

                        for (int j = 0, len2 = param_list.size(); j < len2; j++) {
                            preparedStatement.setObject(paramIndex, param_list.get(j));

                            paramIndex++;
                        }
                    }

                    continue;

                } else {
                    if (param.getValue() instanceof byte[]) {
                        preparedStatement.setBytes(paramIndex, (byte[]) param.getValue());
                    } else {
                        preparedStatement.setObject(paramIndex, param.getValue());
                    }
                }

                paramIndex++;
            }
        }
        return preparedStatement;
    }

　　

------------恢复内容结束------------