1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/java/sql-dk/src/main/java/info/globalcode/sql/dk/SQLCommandNamed.java Mon Mar 04 20:15:24 2019 +0100
1.3 @@ -0,0 +1,155 @@
1.4 +/**
1.5 + * SQL-DK
1.6 + * Copyright © 2013 František Kučera (frantovo.cz)
1.7 + *
1.8 + * This program is free software: you can redistribute it and/or modify
1.9 + * it under the terms of the GNU General Public License as published by
1.10 + * the Free Software Foundation, either version 3 of the License, or
1.11 + * (at your option) any later version.
1.12 + *
1.13 + * This program is distributed in the hope that it will be useful,
1.14 + * but WITHOUT ANY WARRANTY; without even the implied warranty of
1.15 + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
1.16 + * GNU General Public License for more details.
1.17 + *
1.18 + * You should have received a copy of the GNU General Public License
1.19 + * along with this program. If not, see <http://www.gnu.org/licenses/>.
1.20 + */
1.21 +package info.globalcode.sql.dk;
1.22 +
1.23 +import static info.globalcode.sql.dk.Functions.findByName;
1.24 +import java.sql.Connection;
1.25 +import java.sql.PreparedStatement;
1.26 +import java.sql.SQLException;
1.27 +import java.util.ArrayList;
1.28 +import java.util.List;
1.29 +import java.util.logging.Level;
1.30 +import java.util.logging.Logger;
1.31 +import java.util.regex.Matcher;
1.32 +import java.util.regex.Pattern;
1.33 +import java.util.regex.PatternSyntaxException;
1.34 +
1.35 +/**
1.36 + * Has named parameters.
1.37 + *
1.38 + * @author Ing. František Kučera (frantovo.cz)
1.39 + */
1.40 +public class SQLCommandNamed extends SQLCommand {
1.41 +
1.42 + private static final Logger log = Logger.getLogger(SQLCommandNamed.class.getName());
1.43 + private String namePrefix;
1.44 + private String nameSuffix;
1.45 + private List<NamedParameter> parameters;
1.46 + private List<NamedParameter> parametersUsed = new ArrayList<>();
1.47 + private StringBuilder updatedQuery;
1.48 + private Pattern pattern;
1.49 + private SQLCommandNumbered numbered;
1.50 +
1.51 + public SQLCommandNamed(String query, List<NamedParameter> parameters, String namePrefix, String nameSuffix) {
1.52 + super(query);
1.53 + this.updatedQuery = new StringBuilder(query.length());
1.54 + this.parameters = parameters;
1.55 + this.namePrefix = namePrefix;
1.56 + this.nameSuffix = nameSuffix;
1.57 + }
1.58 +
1.59 + @Override
1.60 + public PreparedStatement prepareStatement(Connection c) throws SQLException {
1.61 + return getSQLCommandNumbered().prepareStatement(c);
1.62 + }
1.63 +
1.64 + @Override
1.65 + public void parametrize(PreparedStatement ps) throws SQLException {
1.66 + getSQLCommandNumbered().parametrize(ps);
1.67 + }
1.68 +
1.69 + private void prepare() throws SQLException {
1.70 + try {
1.71 + buildPattern();
1.72 + placeParametersAndUpdateQuery();
1.73 + logPossiblyMissingParameters();
1.74 + } catch (PatternSyntaxException e) {
1.75 + throw new SQLException("Name prefix „" + namePrefix + "“ or suffix „" + nameSuffix + "“ contain a wrong regular expression. " + e.getLocalizedMessage(), e);
1.76 + }
1.77 + }
1.78 +
1.79 + /**
1.80 + * @return SQL command with named parameters converted to SQL command with numbered parameters
1.81 + */
1.82 + public SQLCommandNumbered getSQLCommandNumbered() throws SQLException {
1.83 + if (numbered == null) {
1.84 + prepare();
1.85 + numbered = new SQLCommandNumbered(updatedQuery.toString(), parametersUsed);
1.86 + }
1.87 +
1.88 + return numbered;
1.89 + }
1.90 +
1.91 + /**
1.92 + * Builds a regexp pattern that matches all parameter names (with prefix/suffix) and which has
1.93 + * one group: parameter name (without prefix/suffix)
1.94 + */
1.95 + private void buildPattern() throws PatternSyntaxException {
1.96 + StringBuilder patternString = new StringBuilder();
1.97 +
1.98 + patternString.append(namePrefix);
1.99 + patternString.append("(?<paramName>");
1.100 + for (int i = 0; i < parameters.size(); i++) {
1.101 + patternString.append(Pattern.quote(parameters.get(i).getName()));
1.102 + if (i < parameters.size() - 1) {
1.103 + patternString.append("|");
1.104 + }
1.105 + }
1.106 + patternString.append(")");
1.107 + patternString.append(nameSuffix);
1.108 +
1.109 + pattern = Pattern.compile(patternString.toString());
1.110 + }
1.111 +
1.112 + private void placeParametersAndUpdateQuery() {
1.113 + final String originalQuery = getQuery();
1.114 + Matcher m = pattern.matcher(originalQuery);
1.115 +
1.116 + int lastPosition = 0;
1.117 + while (m.find(lastPosition)) {
1.118 + String name = m.group("paramName");
1.119 +
1.120 + updatedQuery.append(originalQuery.substring(lastPosition, m.start()));
1.121 + updatedQuery.append("?");
1.122 +
1.123 + parametersUsed.add(findByName(parameters, name));
1.124 +
1.125 + lastPosition = m.end();
1.126 + }
1.127 + updatedQuery.append(originalQuery.substring(lastPosition, originalQuery.length()));
1.128 +
1.129 + for (NamedParameter definedParameter : parameters) {
1.130 + if (findByName(parametersUsed, definedParameter.getName()) == null) {
1.131 + /**
1.132 + * User can have predefined set of parameters and use them with different SQL
1.133 + * queries that use only subset of these parameters → just warning, not exception.
1.134 + */
1.135 + log.log(Level.WARNING, "Parameter „{0}“ is defined but not used in the query: „{1}“", new Object[]{definedParameter.getName(), originalQuery});
1.136 + }
1.137 + }
1.138 + }
1.139 +
1.140 + private void logPossiblyMissingParameters() {
1.141 + Pattern p = Pattern.compile(namePrefix + "(?<paramName>.+?)" + nameSuffix);
1.142 + Matcher m = p.matcher(updatedQuery);
1.143 + int lastPosition = 0;
1.144 + while (m.find(lastPosition)) {
1.145 + /**
1.146 + * We have not parsed and understood the SQL query; the parameter-like looking string
1.147 + * could be inside a literal part of the query → just warning, not exception.
1.148 + */
1.149 + log.log(Level.WARNING, "Possibly missing parameter „{0}“ in the query: „{1}“", new Object[]{m.group("paramName"), getQuery()});
1.150 + lastPosition = m.end();
1.151 + }
1.152 + }
1.153 +
1.154 + @Override
1.155 + public List<NamedParameter> getParameters() {
1.156 + return parameters;
1.157 + }
1.158 +}