View Javadoc

1   /*
2    *  soapUI, copyright (C) 2004-2010 eviware.com 
3    *
4    *  soapUI is free software; you can redistribute it and/or modify it under the 
5    *  terms of version 2.1 of the GNU Lesser General Public License as published by 
6    *  the Free Software Foundation.
7    *
8    *  soapUI is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without 
9    *  even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
10   *  See the GNU Lesser General Public License for more details at gnu.org.
11   */
12  package com.eviware.soapui.impl.wsdl.panels.teststeps.support;
13  
14  import java.sql.Connection;
15  import java.sql.PreparedStatement;
16  import java.sql.ResultSet;
17  import java.sql.SQLException;
18  import java.sql.Statement;
19  import java.sql.Timestamp;
20  import java.util.HashMap;
21  import java.util.Iterator;
22  import java.util.LinkedList;
23  import java.util.List;
24  import java.util.Map;
25  
26  public class NamedParameterStatement
27  {
28  	/*** The statement this object is wrapping. */
29  	private final PreparedStatement statement;
30  
31  	/*** Maps parameter names to arrays of ints which are the parameter indices. */
32  	private final Map indexMap;
33  
34  	/***
35  	 * Creates a NamedParameterStatement. Wraps a call to c.
36  	 * {@link Connection#prepareStatement(java.lang.String) prepareStatement}.
37  	 * 
38  	 * @param connection
39  	 *           the database connection
40  	 * @param query
41  	 *           the parameterized query
42  	 * @throws SQLException
43  	 *            if the statement could not be created
44  	 */
45  	public NamedParameterStatement( Connection connection, String query ) throws SQLException
46  	{
47  		indexMap = new HashMap();
48  		String parsedQuery = parse( query, indexMap );
49  		statement = connection.prepareStatement( parsedQuery );
50  	}
51  
52  	/***
53  	 * Parses a query with named parameters. The parameter-index mappings are put
54  	 * into the map, and the parsed query is returned. DO NOT CALL FROM CLIENT
55  	 * CODE. This method is non-private so JUnit code can test it.
56  	 * 
57  	 * @param query
58  	 *           query to parse
59  	 * @param paramMap
60  	 *           map to hold parameter-index mappings
61  	 * @return the parsed query
62  	 */
63  	 private static final String parse( String query, Map paramMap )
64  	{
65  		// I was originally using regular expressions, but they didn't work well
66  		// for ignoring
67  
68  		// parameter-like strings inside quotes.
69  		int length = query.length();
70  		StringBuffer parsedQuery = new StringBuffer( length );
71  		boolean inSingleQuote = false;
72  		boolean inDoubleQuote = false;
73  		int index = 1;
74  
75  		for( int i = 0; i < length; i++ )
76  		{
77  			char c = query.charAt( i );
78  			if( inSingleQuote )
79  			{
80  				if( c == '\'' )
81  				{
82  					inSingleQuote = false;
83  				}
84  			}
85  			else if( inDoubleQuote )
86  			{
87  				if( c == '"' )
88  				{
89  					inDoubleQuote = false;
90  				}
91  			}
92  			else
93  			{
94  				if( c == '\'' )
95  				{
96  					inSingleQuote = true;
97  				}
98  				else if( c == '"' )
99  				{
100 					inDoubleQuote = true;
101 				}
102 				else if( c == ':' && i + 1 < length && Character.isJavaIdentifierStart( query.charAt( i + 1 ) ) )
103 				{
104 					int j = i + 2;
105 					while( j < length && Character.isJavaIdentifierPart( query.charAt( j ) ) )
106 					{
107 						j++ ;
108 					}
109 					String name = query.substring( i + 1, j );
110 					c = '?'; // replace the parameter with a question mark
111 					i += name.length(); // skip past the end if the parameter
112 
113 					List indexList = ( List )paramMap.get( name );
114 					if( indexList == null )
115 					{
116 						indexList = new LinkedList();
117 						paramMap.put( name, indexList );
118 					}
119 					indexList.add( new Integer( index ) );
120 
121 					index++ ;
122 				}
123 			}
124 			parsedQuery.append( c );
125 		}
126 
127 		// replace the lists of Integer objects with arrays of ints
128 		for( Iterator itr = paramMap.entrySet().iterator(); itr.hasNext(); )
129 		{
130 			Map.Entry entry = ( Map.Entry )itr.next();
131 			List list = ( List )entry.getValue();
132 			int[] indexes = new int[list.size()];
133 			int i = 0;
134 			for( Iterator itr2 = list.iterator(); itr2.hasNext(); )
135 			{
136 				Integer x = ( Integer )itr2.next();
137 				indexes[i++ ] = x.intValue();
138 			}
139 			entry.setValue( indexes );
140 		}
141 
142 		return parsedQuery.toString();
143 	}
144 
145 	/***
146 	 * Returns the indexes for a parameter.
147 	 * 
148 	 * @param name
149 	 *           parameter name
150 	 * @return parameter indexes
151 	 * @throws IllegalArgumentException
152 	 *            if the parameter does not exist
153 	 */
154 	private int[] getIndexes( String name )
155 	{
156 		int[] indexes = ( int[] )indexMap.get( name );
157 		if( indexes == null )
158 		{
159 			throw new IllegalArgumentException( "Parameter not found: " + name );
160 		}
161 		return indexes;
162 	}
163 
164 	/***
165 	 * Sets a parameter.
166 	 * 
167 	 * @param name
168 	 *           parameter name
169 	 * @param value
170 	 *           parameter value
171 	 * @throws SQLException
172 	 *            if an error occurred
173 	 * @throws IllegalArgumentException
174 	 *            if the parameter does not exist
175 	 * @see PreparedStatement#setObject(int, java.lang.Object)
176 	 */
177 	public void setObject( String name, Object value ) throws SQLException
178 	{
179 		int[] indexes = getIndexes( name );
180 		for( int i = 0; i < indexes.length; i++ )
181 		{
182 			statement.setObject( indexes[i], value );
183 		}
184 	}
185 
186 	/***
187 	 * Sets a parameter.
188 	 * 
189 	 * @param name
190 	 *           parameter name
191 	 * @param value
192 	 *           parameter value
193 	 * @throws SQLException
194 	 *            if an error occurred
195 	 * @throws IllegalArgumentException
196 	 *            if the parameter does not exist
197 	 * @see PreparedStatement#setString(int, java.lang.String)
198 	 */
199 	public void setString( String name, String value ) throws SQLException
200 	{
201 		int[] indexes = getIndexes( name );
202 		for( int i = 0; i < indexes.length; i++ )
203 		{
204 			statement.setString( indexes[i], value );
205 		}
206 	}
207 
208 	/***
209 	 * Sets a parameter.
210 	 * 
211 	 * @param name
212 	 *           parameter name
213 	 * @param value
214 	 *           parameter value
215 	 * @throws SQLException
216 	 *            if an error occurred
217 	 * @throws IllegalArgumentException
218 	 *            if the parameter does not exist
219 	 * @see PreparedStatement#setInt(int, int)
220 	 */
221 	public void setInt( String name, int value ) throws SQLException
222 	{
223 		int[] indexes = getIndexes( name );
224 		for( int i = 0; i < indexes.length; i++ )
225 		{
226 			statement.setInt( indexes[i], value );
227 		}
228 	}
229 
230 	/***
231 	 * Sets a parameter.
232 	 * 
233 	 * @param name
234 	 *           parameter name
235 	 * @param value
236 	 *           parameter value
237 	 * @throws SQLException
238 	 *            if an error occurred
239 	 * @throws IllegalArgumentException
240 	 *            if the parameter does not exist
241 	 * @see PreparedStatement#setInt(int, int)
242 	 */
243 	public void setLong( String name, long value ) throws SQLException
244 	{
245 		int[] indexes = getIndexes( name );
246 		for( int i = 0; i < indexes.length; i++ )
247 		{
248 			statement.setLong( indexes[i], value );
249 		}
250 	}
251 
252 	/***
253 	 * Sets a parameter.
254 	 * 
255 	 * @param name
256 	 *           parameter name
257 	 * @param value
258 	 *           parameter value
259 	 * @throws SQLException
260 	 *            if an error occurred
261 	 * @throws IllegalArgumentException
262 	 *            if the parameter does not exist
263 	 * @see PreparedStatement#setTimestamp(int, java.sql.Timestamp)
264 	 */
265 	public void setTimestamp( String name, Timestamp value ) throws SQLException
266 	{
267 		int[] indexes = getIndexes( name );
268 		for( int i = 0; i < indexes.length; i++ )
269 		{
270 			statement.setTimestamp( indexes[i], value );
271 		}
272 	}
273 
274 	/***
275 	 * Returns the underlying statement.
276 	 * 
277 	 * @return the statement
278 	 */
279 	public PreparedStatement getStatement()
280 	{
281 		return statement;
282 	}
283 
284 	/***
285 	 * Executes the statement.
286 	 * 
287 	 * @return true if the first result is a {@link ResultSet}
288 	 * @throws SQLException
289 	 *            if an error occurred
290 	 * @see PreparedStatement#execute()
291 	 */
292 	public boolean execute() throws SQLException
293 	{
294 		return statement.execute();
295 	}
296 
297 	/***
298 	 * Executes the statement, which must be a query.
299 	 * 
300 	 * @return the query results
301 	 * @throws SQLException
302 	 *            if an error occurred
303 	 * @see PreparedStatement#executeQuery()
304 	 */
305 	public ResultSet executeQuery() throws SQLException
306 	{
307 		return statement.executeQuery();
308 	}
309 
310 	/***
311 	 * Executes the statement, which must be an SQL INSERT, UPDATE or DELETE
312 	 * statement; or an SQL statement that returns nothing, such as a DDL
313 	 * statement.
314 	 * 
315 	 * @return number of rows affected
316 	 * @throws SQLException
317 	 *            if an error occurred
318 	 * @see PreparedStatement#executeUpdate()
319 	 */
320 	public int executeUpdate() throws SQLException
321 	{
322 		return statement.executeUpdate();
323 	}
324 
325 	/***
326 	 * Closes the statement.
327 	 * 
328 	 * @throws SQLException
329 	 *            if an error occurred
330 	 * @see Statement#close()
331 	 */
332 	public void close() throws SQLException
333 	{
334 		statement.close();
335 	}
336 
337 	/***
338 	 * Adds the current set of parameters as a batch entry.
339 	 * 
340 	 * @throws SQLException
341 	 *            if something went wrong
342 	 */
343 	public void addBatch() throws SQLException
344 	{
345 		statement.addBatch();
346 	}
347 
348 	/***
349 	 * Executes all of the batched statements.
350 	 * 
351 	 * See {@link Statement#executeBatch()} for details.
352 	 * 
353 	 * @return update counts for each statement
354 	 * @throws SQLException
355 	 *            if something went wrong
356 	 */
357 	public int[] executeBatch() throws SQLException
358 	{
359 		return statement.executeBatch();
360 	}
361 }