NamedParameterUtils


1   /*
2    * Copyright 2002-2006 the original author or authors.
3    *
4    * Licensed under the Apache License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    *
8    *      http://www.apache.org/licenses/LICENSE-2.0
9    *
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  
17  package org.springframework.jdbc.core.namedparam;
18  
19  import java.util.ArrayList  ;
20  import java.util.Collection  ;
21  import java.util.HashMap  ;
22  import java.util.List  ;
23  import java.util.Map  ;
24  
25  import org.springframework.dao.InvalidDataAccessApiUsageException;
26  import org.springframework.util.Assert;
27  
28  /**
29   * Helper methods for named parameter parsing.
30   * Only intended for internal use within Spring's JDBC framework.
31   *
32   * @author Thomas Risberg
33   * @author Juergen Hoeller
34   * @since 2.0
35   */
36  public abstract class NamedParameterUtils {
37  
38      /**
39       * Set of characters that qualify as parameter separators,
40       * indicating that a parameter name in a SQL String has ended.
41       */
42      private static final char[] PARAMETER_SEPARATORS =
43              new char[] {'"', '\'', ':', '&', ',', ';', '(', ')', '|', '=', '+', '-', '*', '%', '/', '\\', '<', '>', '^'};
44  
45  
46      /**
47       * Parse the SQL statement and locate any placeholders or named parameters.
48       * Named parameters are substituted for a JDBC placeholder.
49       * @param sql the SQL statement
50       */
51      public static String   parseSqlStatementIntoString(String   sql) {
52          return parseSqlStatement(sql).getNewSql();
53      }
54  
55      /**
56       * Parse the SQL statement and locate any placeholders or named parameters.
57       * Named parameters are substituted for a JDBC placeholder.
58       * @param sql the SQL statement
59       * @return the parsed statement, represented as ParsedSql instance
60       */
61      static ParsedSql parseSqlStatement(String   sql) {
62          Assert.notNull(sql, "SQL must not be null");
63  
64          List   parameters = new ArrayList  ();
65          Map   namedParameters = new HashMap  ();
66          ParsedSql parsedSql = new ParsedSql(sql);
67  
68          char[] statement = sql.toCharArray();
69          StringBuffer   newSql = new StringBuffer  ();
70          boolean withinQuotes = false;
71          char currentQuote = '-';
72          int namedParameterCount = 0;
73          int unnamedParameterCount = 0;
74          int totalParameterCount = 0;
75  
76          int i = 0;
77          while (i < statement.length) {
78              char c = statement[i];
79              if (withinQuotes) {
80                  if (c == currentQuote) {
81                      withinQuotes = false;
82                      currentQuote = '-';
83                  }
84                  newSql.append(c);
85              }
86              else {
87                  if (c == '"' || c == '\'') {
88                      withinQuotes = true;
89                      currentQuote = c;
90                      newSql.append(c);
91                  }
92                  else {
93                      if (c == ':' || c == '&') {
94                          int j = i + 1;
95                          while (j < statement.length && !isParameterSeparator(statement[j])) {
96                              j++;
97                          }
98                          if (j - i > 1) {
99                              String   parameter = sql.substring(i + 1, j);
100                             if (!namedParameters.containsKey(parameter)) {
101                                 namedParameters.put(parameter, parameter);
102                                 namedParameterCount++;
103                             }
104                             newSql.append("?");
105                             parameters.add(parameter);
106                             totalParameterCount++;
107                         } else {
108                             newSql.append(c);
109                         }
110                         i = j - 1;
111                     }
112                     else {
113                         newSql.append(c);
114                         if (c == '?') {
115                             unnamedParameterCount++;
116                             totalParameterCount++;
117                         }
118                     }
119                 }
120             }
121             i++;
122         }
123         parsedSql.setNewSql(newSql.toString());
124         parsedSql.setParameterNames((String  []) parameters.toArray(new String  [parameters.size()]));
125         parsedSql.setNamedParameterCount(namedParameterCount);
126         parsedSql.setUnnamedParameterCount(unnamedParameterCount);
127         parsedSql.setTotalParameterCount(totalParameterCount);
128         return parsedSql;
129     }
130 
131     /**
132      * Parse the SQL statement and locate any placeholders or named parameters.
133      * Named parameters are substituted for a JDBC placeholder and any select list
134      * is expanded to the required number of placeholders.
135      * <p>The parameter values passed in are used to determine the number of
136      * placeholder to be used for a select list. Select lists should be limited
137      * to 100 or fewer elements. A larger number of elements is not guaramteed to
138      * be supported by the database and is strictly vendor-dependent.
139      * @param sql the SQL statement
140      * @param paramSource the source for named parameters
141      * @return the SQL statement with substituted parameters
142      */
143     public static String   substituteNamedParameters(String   sql, SqlParameterSource paramSource) {
144         Assert.notNull(sql, "SQL must not be null");
145 
146         char[] statement = sql.toCharArray();
147         StringBuffer   newSql = new StringBuffer  ();
148         boolean withinQuotes = false;
149         char currentQuote = '-';
150 
151         int i = 0;
152         while (i < statement.length) {
153             char c = statement[i];
154             if (withinQuotes) {
155                 if (c == currentQuote) {
156                     withinQuotes = false;
157                     currentQuote = '-';
158                 }
159                 newSql.append(c);
160             }
161             else {
162                 if (c == '"' || c == '\'') {
163                     withinQuotes = true;
164                     currentQuote = c;
165                     newSql.append(c);
166                 }
167                 else {
168                     if (c == ':' || c == '&') {
169                         int j = i + 1;
170                         while (j < statement.length && !isParameterSeparator(statement[j])) {
171                             j++;
172                         }
173                         if (j - i > 1) {
174                             String   paramName = sql.substring(i + 1, j);
175                             if (paramSource != null && paramSource.hasValue(paramName)) {
176                                 Object   value = paramSource.getValue(paramName);
177                                 if (value instanceof Collection  ) {
178                                     Collection   entries = (Collection  ) value;
179                                     for (int k = 0; k < entries.size(); k++) {
180                                         if (k > 0) {
181                                             newSql.append(", ");
182                                         }
183                                         newSql.append("?");
184                                     }
185                                 }
186                                 else {
187                                     newSql.append("?");
188                                 }
189                             }
190                             else {
191                                 newSql.append("?");
192                             }
193                         }
194                         else {
195                             newSql.append(c);
196                         }
197                         i = j - 1;
198                     }
199                     else {
200                         newSql.append(c);
201                     }
202                 }
203             }
204             i++;
205         }
206         return newSql.toString();
207     }
208 
209     /**
210      * Convert a Map of named parameter values to a corresponding array.
211      * <p>This is necessary in order to reuse existing methods on JdbcTemplate.
212      * See below for additional info.
213      * @param sql the SQL statement
214      * @param paramMap the Map of parameters
215      */
216     public static Object  [] buildValueArray(String   sql, Map   paramMap) {
217         ParsedSql parsedSql = parseSqlStatement(sql);
218         return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap));
219     }
220 
221     /**
222      * Convert a Map of named parameter values to a corresponding array.
223      * This is necessary in order to reuse existing methods on JdbcTemplate.
224      * Any named parameters are placed in the correct position in the Object
225      * array based on the parsed SQL statement info.
226      * @param parsedSql the parsed SQL statement
227      * @param paramSource the source for named parameters
228      */
229     static Object  [] buildValueArray(ParsedSql parsedSql, SqlParameterSource paramSource) {
230         Object  [] paramArray = new Object  [parsedSql.getTotalParameterCount()];
231         if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) {
232             throw new InvalidDataAccessApiUsageException(
233                     "You can't mix named and traditional ? placeholders. You have " +
234                     parsedSql.getNamedParameterCount() + " named parameter(s) and " +
235                     parsedSql.getUnnamedParameterCount() + " traditonal placeholder(s) in [" +
236                     parsedSql.getSql() + "]");
237         }
238         String  [] paramNames = parsedSql.getParameterNames();
239         for (int i = 0; i < paramNames.length; i++) {
240             String   paramName = paramNames[i];
241             try {
242                 paramArray[i] = paramSource.getValue(paramName);
243             }
244             catch (IllegalArgumentException   ex) {
245                 throw new InvalidDataAccessApiUsageException(
246                         "No value supplied for the SQL parameter '" + paramName + "': " + ex.getMessage());
247             }
248         }
249         return paramArray;
250     }
251 
252     /**
253      * Convert a Map of parameter types to a corresponding int array.
254      * This is necessary in order to reuse existing methods on JdbcTemplate.
255      * Any named parameter types are placed in the correct position in the
256      * Object array based on the parsed SQL statement info.
257      * @param parsedSql the parsed SQL statement
258      * @param paramSource the source for named parameters
259      */
260     static int[] buildSqlTypeArray(ParsedSql parsedSql, SqlParameterSource paramSource) {
261         int[] sqlTypes = new int[parsedSql.getTotalParameterCount()];
262         String  [] paramNames = parsedSql.getParameterNames();
263         for (int i = 0; i < paramNames.length; i++) {
264             sqlTypes[i] = paramSource.getSqlType(paramNames[i]);
265         }
266         return sqlTypes;
267     }
268 
269     /**
270      * Determine whether a parameter name ends at the current position,
271      * that is, whether the given character qualifies as a separator.
272      */
273     private static boolean isParameterSeparator(char c) {
274         if (Character.isWhitespace(c)) {
275             return true;
276         }
277         for (int i = 0; i < PARAMETER_SEPARATORS.length; i++) {
278             if (c == PARAMETER_SEPARATORS[i]) {
279                 return true;
280             }
281         }
282         return false;
283     }
284 
285 }
286
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags