001// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
002// The Molly framework is freely distributable under the terms of an
003// MIT-style license. For details, see the molly pages web site at:
004// http://www.mollypages.org/. Use, modify, have fun !
005
006package fc.jdbc;
007
008import java.io.*;
009import java.util.*;
010import java.math.*;
011import java.util.regex.*;
012import java.sql.*;
013import javax.servlet.*;
014
015import fc.io.*;
016import fc.web.*;
017import fc.util.*;
018
019/** 
020This class wraps around {@link PreparedStatement} and allows the programmer to set
021parameters by name instead of by question mark index.
022<p>
023Inspired by a similar concept at: <a href="http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html">javaworld</a> 
024(although this class was rewritten from scratch).
025<p>
026Named parameters are written as <i>@foo</i> (that is, they start with a <i>@</i> character). Named parameters can only contain <i>alphanumeric</i>, <i>underscore</i> and <i>dashes</i>, any character not in this allowed list automatically ends the named parameter and continues normal SQL. For example:
027<blockquote><pre>
028select * from foo where search = @search-term and radius = '@radius::int'
029</pre></blockquote>
030This contains two named parameters, <font color=blue>@search-term</font> and <font color=blue>@radius</font>. To use this in code, we say:
031<blockquote><pre>
032
033Connection con = getConnection(); //some way to get a connection
034String query = 
035  "select * from foo where search = <font color=blue>@search-term</font> and radius = '<font color=blue>@radius</font>::int'";
036
037NamedParamStatement ps = NamedParamStatement.get(con, query);
038ps.setString("<b>search-term</b>", "hello world");
039ps.setInt("<b>radius</b>", 42);
040
041ResultSet rs = ps.executeQuery();
042</pre></blockquote>
043<p>
044<b>Note</b>: When setting a named paramter, the "@" must be omitted.
045<p>
046The same named parameter can appear multiple times in the query, and is replaced
047wherever it appears by its value.
048<p>
049The {@link #close} method should be called to release resources and help with
050garbage collection (right around the time close is called on the 
051associated connection, which is after any retrieved data/resultset has
052been fully read).
053
054@author hursh jain
055**/
056public final class NamedParamStatement
057{
058private static final boolean dbg = false;
059
060//associated with each original query, is this parse data.
061//for example, an original query of:
062//  select * from foo where bar = @bar
063//becomes
064//  parsedQuery = select * from foo where bar = ?
065//  indexMap    = {bar: 1}
066//
067private static class ParseData
068  {
069  String parsedQuery;
070  Map    indexMap;
071  
072  public String toString() {
073    return "parsedQuery:" + parsedQuery + "; indexMap:" + indexMap;
074    }
075  }
076  
077//{unparsed_query: parsedata}
078private static final Map queryMap = new HashMap(); //static initialized
079
080private PreparedStatement   wrappedPS; 
081private Map         indexMap; //a convenient ref to query->ParseData->indexMap
082private NamedParamStatement(PreparedStatement wrappedPS, Map indexMap)
083  {
084  this.wrappedPS = wrappedPS;
085  this.indexMap = indexMap;
086  }
087  
088/**
089Returns a new instance of NamedParamStatement. This instance, internally, 
090creates/wraps a new prepared statement. (the query string is not reparsed 
091every time, just the first time this method is invoked for any particular
092query). The returned query is not scrollable (use {@link #getScrollable} for
093a scrollable result set).
094*/
095public static NamedParamStatement get(Connection con, String query) throws SQLException
096  {
097  return add(con, query, false);
098  }
099
100/**
101Returns a new instance of NamedParamStatement. This instance, internally, 
102creates/wraps a new {@link PreparedStatement}. (the query string is not reparsed 
103every time, just the first time this method is invoked for any particular
104query). 
105<p>
106This method ensures that any {@link ResultSet} returned by the wrapped
107PreparedStatement is scrollable (the  <tt>ResultSet.TYPE_SCROLL_INSENSITIVE</tt> 
108flag is used when creating the PreparedStatement).
109*/
110public static NamedParamStatement getScrollable(Connection con, String query) throws SQLException
111  {
112  return add(con, query, true);
113  }
114
115//dont capture the "@", name are put in the map without the @ char
116static final Pattern pat = Pattern.compile("(\\s*)@([a-zA-Z_0-9.]+)");
117
118private static final ParseData parse(String query)
119  {
120  if (dbg) System.out.println("Analyzing query: \n" + query);
121
122  Map indexMap = new HashMap();
123  
124  Matcher match = pat.matcher(query);
125  StringBuffer sb = new StringBuffer(query.length());
126  
127  int pos = 1;  //? positions start from 1  
128  while (match.find()) 
129    {
130    match.appendReplacement(sb, "$1?");
131    String paramName = match.group(2);
132    if (dbg) System.out.println("Found replacement name: @" + paramName);
133
134    ArrayList indexes = (ArrayList) indexMap.get(paramName);
135    if (indexes == null) {
136      indexes = new ArrayList();    
137      indexMap.put(paramName, indexes);
138      }
139    indexes.add(pos++);
140    }
141  match.appendTail(sb);
142
143  String parsedQuery = sb.toString();
144
145  if (dbg) System.out.println("Replacement index map: \n" + indexMap);
146  if (dbg) System.out.println("Replaced query: " + parsedQuery);  
147  
148  ParseData pd = new ParseData();
149  pd.indexMap = indexMap;
150  pd.parsedQuery = parsedQuery;
151  
152  queryMap.put(query, pd);
153  
154  return pd;
155  }
156
157private static final NamedParamStatement add(Connection con, String query, boolean scrollable) 
158throws SQLException
159  {
160  if (query == null) {
161    throw new IllegalArgumentException("'query' parameter was null"); 
162    }
163
164  //not synchronized, because in worst case, we will add (reparse) the
165  //statement, which is no biggie. 
166
167  ParseData pd = (ParseData) queryMap.get(query);
168  if (pd == null) {
169    pd = parse(query); //this also populates queryMap for next time
170    }
171    
172  PreparedStatement ps = null;
173  
174  if (scrollable) {
175    ps = con.prepareStatement(pd.parsedQuery, 
176                ResultSet.TYPE_SCROLL_INSENSITIVE,
177                ResultSet.CONCUR_READ_ONLY);
178    }
179  else{
180    ps = con.prepareStatement(pd.parsedQuery);
181    }
182
183  //cannot save these, these wrap a connection specific preparedstatement, which
184  //must be recreated per connection. We cache the expensive parts (indexMap)
185  //above tho, which does not have to be recreated if the querystring is the
186  //same.
187  NamedParamStatement np = new NamedParamStatement(ps, pd.indexMap);
188  
189  return np;
190  }
191
192private List getIndexes(String name) 
193  {
194  List indexes = (List) indexMap.get(name);
195
196  if(indexes == null) {
197    throw new IllegalArgumentException("NamedParamStatement [" + wrappedPS.toString() + "], replacement parameter not found, parameter name=" + name);
198    }
199    
200  return indexes;
201  }
202
203public void close() throws SQLException 
204  {
205  wrappedPS.close();
206  wrappedPS = null;
207  }
208
209public String toString() 
210  {
211  return wrappedPS.toString();
212  }
213
214//========================== wrapped methods ===========================
215
216public ResultSet executeQuery() throws SQLException
217  {
218  return wrappedPS.executeQuery();
219  }
220  
221public int executeUpdate() throws SQLException
222  {
223  return wrappedPS.executeUpdate(); 
224  }
225
226public void setNull(String name, int sqlType) throws SQLException
227  {
228  final List indexes = getIndexes(name);
229  for(int i = 0; i < indexes.size(); i++) {
230    wrappedPS.setNull(((Integer)indexes.get(i)).intValue(), sqlType);
231    }
232  }
233
234public void setBoolean(String name, boolean x) throws SQLException
235  {
236  final List indexes = getIndexes(name);
237  for(int i = 0; i < indexes.size(); i++) {
238    wrappedPS.setBoolean(((Integer)indexes.get(i)).intValue(), x);
239    } 
240  }
241  
242public void setByte(String name, byte x) throws SQLException 
243  {
244  final List indexes = getIndexes(name);
245  for(int i = 0; i < indexes.size(); i++) {
246    wrappedPS.setByte(((Integer)indexes.get(i)).intValue(), x);
247    } 
248  }
249
250public void setShort(String name, short x) throws SQLException
251  {
252  final List indexes = getIndexes(name);
253  for(int i = 0; i < indexes.size(); i++) {
254    wrappedPS.setShort(((Integer)indexes.get(i)).intValue(), x);
255    } 
256  }
257  
258public void setInt(String name, int x) throws SQLException
259  {
260  final List indexes = getIndexes(name);
261  for(int i = 0; i < indexes.size(); i++) {
262    wrappedPS.setInt(((Integer)indexes.get(i)).intValue(), x);
263    }   
264  }
265  
266public void setLong(String name, long x) throws SQLException
267  {
268  final List indexes = getIndexes(name);
269  for(int i = 0; i < indexes.size(); i++) {
270    wrappedPS.setLong(((Integer)indexes.get(i)).intValue(), x);
271    }   
272  }
273  
274public void setFloat(String name, float x) throws SQLException
275  {
276  final List indexes = getIndexes(name);
277  for(int i = 0; i < indexes.size(); i++) {
278    wrappedPS.setFloat(((Integer)indexes.get(i)).intValue(), x);
279    }   
280  }
281
282public void setDouble(String name, double x) throws SQLException
283  {
284  final List indexes = getIndexes(name);
285  for(int i = 0; i < indexes.size(); i++) {
286    wrappedPS.setDouble(((Integer)indexes.get(i)).intValue(), x);
287    }   
288  }
289
290public void setBigDecimal(String name, BigDecimal x) throws SQLException
291  {
292  final List indexes = getIndexes(name);
293  for(int i = 0; i < indexes.size(); i++) {
294    wrappedPS.setBigDecimal(((Integer)indexes.get(i)).intValue(), x);
295    }   
296  }
297
298public void setString(String name, String x) throws SQLException
299  {
300  final List indexes = getIndexes(name);
301  for(int i = 0; i < indexes.size(); i++) {
302    wrappedPS.setString(((Integer)indexes.get(i)).intValue(), x);
303    }   
304  }
305
306public void setBytes(String name, byte x[]) throws SQLException
307  {
308  final List indexes = getIndexes(name);
309  for(int i = 0; i < indexes.size(); i++) {
310    wrappedPS.setBytes(((Integer)indexes.get(i)).intValue(), x);
311    }   
312  }
313
314public void setDate(String name, java.sql.Date x) throws SQLException
315  {
316  final List indexes = getIndexes(name);
317  for(int i = 0; i < indexes.size(); i++) {
318    wrappedPS.setDate(((Integer)indexes.get(i)).intValue(), x);
319    }   
320  }
321
322public void setTime(String name, java.sql.Time x) throws SQLException
323  {
324  final List indexes = getIndexes(name);
325  for(int i = 0; i < indexes.size(); i++) {
326    wrappedPS.setTime(((Integer)indexes.get(i)).intValue(), x);
327    }   
328  }
329
330public void setTimestamp(String name, java.sql.Timestamp x) throws SQLException
331  {
332  final List indexes = getIndexes(name);
333  for(int i = 0; i < indexes.size(); i++) {
334    wrappedPS.setTimestamp(((Integer)indexes.get(i)).intValue(), x);
335    }   
336  }
337
338public void setAsciiStream(String name, java.io.InputStream x, int length) throws SQLException
339  {
340  final List indexes = getIndexes(name);
341  for(int i = 0; i < indexes.size(); i++) {
342    wrappedPS.setAsciiStream(((Integer)indexes.get(i)).intValue(), x, length);
343    }   
344  }
345
346public void setBinaryStream(String name, java.io.InputStream x, int length) throws SQLException
347  {
348  final List indexes = getIndexes(name);
349  for(int i = 0; i < indexes.size(); i++) {
350    wrappedPS.setBinaryStream(((Integer)indexes.get(i)).intValue(), x, length);
351    }   
352  }
353
354public void clearParameters() throws SQLException
355  {
356  wrappedPS.clearParameters();
357  }
358
359public void setObject(String name, Object x, int targetSqlType, int scale) throws SQLException
360  {
361  final List indexes = getIndexes(name);
362  for(int i = 0; i < indexes.size(); i++) {
363    wrappedPS.setObject(((Integer)indexes.get(i)).intValue(), x, targetSqlType, scale);
364    }   
365  }
366
367public void setObject(String name, Object x, int targetSqlType) throws SQLException
368  {
369  final List indexes = getIndexes(name);
370  for(int i = 0; i < indexes.size(); i++) {
371    wrappedPS.setObject(((Integer)indexes.get(i)).intValue(), x, targetSqlType);
372    }   
373  }
374
375public void setObject(String name, Object x) throws SQLException
376  {
377  final List indexes = getIndexes(name);
378  for(int i = 0; i < indexes.size(); i++) {
379    wrappedPS.setObject(((Integer)indexes.get(i)).intValue(), x);
380    }   
381  }
382
383public boolean execute() throws SQLException
384  {
385  return wrappedPS.execute();
386  }
387
388public void addBatch() throws SQLException
389  {
390  wrappedPS.addBatch(); 
391  }
392  
393public void setCharacterStream(String name, java.io.Reader reader, int length) throws SQLException
394  {
395  final List indexes = getIndexes(name);
396  for(int i = 0; i < indexes.size(); i++) {
397    wrappedPS.setCharacterStream(((Integer)indexes.get(i)).intValue(), reader, length);
398    }   
399  }
400
401public void setRef (String name, Ref x) throws SQLException
402  {
403  final List indexes = getIndexes(name);
404  for(int i = 0; i < indexes.size(); i++) {
405    wrappedPS.setRef(((Integer)indexes.get(i)).intValue(), x);
406    }   
407  }
408
409public void setBlob (String name, Blob x) throws SQLException
410  {
411  final List indexes = getIndexes(name);
412  for(int i = 0; i < indexes.size(); i++) {
413    wrappedPS.setBlob(((Integer)indexes.get(i)).intValue(), x);
414    }   
415  }
416
417public void setClob (String name, Clob x) throws SQLException
418  {
419  final List indexes = getIndexes(name);
420  for(int i = 0; i < indexes.size(); i++) {
421    wrappedPS.setClob(((Integer)indexes.get(i)).intValue(), x);
422    }   
423  }
424
425public void setArray (String name, Array x) throws SQLException
426  {
427  final List indexes = getIndexes(name);
428  for(int i = 0; i < indexes.size(); i++) {
429    wrappedPS.setArray(((Integer)indexes.get(i)).intValue(), x);
430    }   
431  }
432
433public ResultSetMetaData getMetaData() throws SQLException
434  {
435  return wrappedPS.getMetaData();
436  }
437
438public void setDate(String name, java.sql.Date x, Calendar cal) throws SQLException
439  {
440  final List indexes = getIndexes(name);
441  for(int i = 0; i < indexes.size(); i++) {
442    wrappedPS.setDate(((Integer)indexes.get(i)).intValue(), x, cal);
443    }   
444  }
445
446public void setTime(String name, java.sql.Time x, Calendar cal) throws SQLException
447  {
448  final List indexes = getIndexes(name);
449  for(int i = 0; i < indexes.size(); i++) {
450    wrappedPS.setTime(((Integer)indexes.get(i)).intValue(), x, cal);
451    }   
452  }
453
454public void setTimestamp(String name, java.sql.Timestamp x, Calendar cal) throws SQLException
455  {
456  final List indexes = getIndexes(name);
457  for(int i = 0; i < indexes.size(); i++) {
458    wrappedPS.setTimestamp(((Integer)indexes.get(i)).intValue(), x, cal);
459    }   
460  }
461
462public void setNull(String name, int sqlType, String typeName) throws SQLException
463  {
464  final List indexes = getIndexes(name);
465  for(int i = 0; i < indexes.size(); i++) {
466    wrappedPS.setNull(((Integer)indexes.get(i)).intValue(), sqlType, typeName);
467    }   
468  }
469
470public void setURL(String name, java.net.URL x) throws SQLException
471  {
472  final List indexes = getIndexes(name);
473  for(int i = 0; i < indexes.size(); i++) {
474    wrappedPS.setURL(((Integer)indexes.get(i)).intValue(), x);
475    }   
476  }
477
478public ParameterMetaData getParameterMetaData() throws SQLException
479  {
480  return wrappedPS.getParameterMetaData();
481  }
482
483}