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    
006    package fc.jdbc;
007    
008    import java.io.*;
009    import java.util.*;
010    import java.math.*;
011    import java.util.regex.*;
012    import java.sql.*;
013    import javax.servlet.*;
014    
015    import fc.io.*;
016    import fc.web.*;
017    import fc.util.*;
018    
019    /** 
020    This class wraps around {@link PreparedStatement} and allows the programmer to set
021    parameters by name instead of by question mark index.
022    <p>
023    Inspired 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>
026    Named 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>
028    select * from foo where search = @search-term and radius = '@radius::int'
029    </pre></blockquote>
030    This 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    
033    Connection con = getConnection(); //some way to get a connection
034    String query = 
035      "select * from foo where search = <font color=blue>@search-term</font> and radius = '<font color=blue>@radius</font>::int'";
036    
037    NamedParamStatement ps = NamedParamStatement.get(con, query);
038    ps.setString("<b>search-term</b>", "hello world");
039    ps.setInt("<b>radius</b>", 42);
040    
041    ResultSet rs = ps.executeQuery();
042    </pre></blockquote>
043    <p>
044    <b>Note</b>: When setting a named paramter, the "@" must be omitted.
045    <p>
046    The same named parameter can appear multiple times in the query, and is replaced
047    wherever it appears by its value.
048    <p>
049    The {@link #close} method should be called to release resources and help with
050    garbage collection (right around the time close is called on the 
051    associated connection, which is after any retrieved data/resultset has
052    been fully read).
053    
054    @author hursh jain
055    **/
056    public final class NamedParamStatement
057    {
058    private 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    //
067    private 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}
078    private static final Map queryMap = new HashMap(); //static initialized
079    
080    private PreparedStatement   wrappedPS; 
081    private Map         indexMap; //a convenient ref to query->ParseData->indexMap
082    private NamedParamStatement(PreparedStatement wrappedPS, Map indexMap)
083      {
084      this.wrappedPS = wrappedPS;
085      this.indexMap = indexMap;
086      }
087      
088    /**
089    Returns a new instance of NamedParamStatement. This instance, internally, 
090    creates/wraps a new prepared statement. (the query string is not reparsed 
091    every time, just the first time this method is invoked for any particular
092    query). The returned query is not scrollable (use {@link #getScrollable} for
093    a scrollable result set).
094    */
095    public static NamedParamStatement get(Connection con, String query) throws SQLException
096      {
097      return add(con, query, false);
098      }
099    
100    /**
101    Returns a new instance of NamedParamStatement. This instance, internally, 
102    creates/wraps a new {@link PreparedStatement}. (the query string is not reparsed 
103    every time, just the first time this method is invoked for any particular
104    query). 
105    <p>
106    This method ensures that any {@link ResultSet} returned by the wrapped
107    PreparedStatement is scrollable (the  <tt>ResultSet.TYPE_SCROLL_INSENSITIVE</tt> 
108    flag is used when creating the PreparedStatement).
109    */
110    public 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
116    static final Pattern pat = Pattern.compile("(\\s*)@([a-zA-Z_0-9.]+)");
117    
118    private 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    
157    private static final NamedParamStatement add(Connection con, String query, boolean scrollable) 
158    throws 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    
192    private 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    
203    public void close() throws SQLException 
204      {
205      wrappedPS.close();
206      wrappedPS = null;
207      }
208    
209    public String toString() 
210      {
211      return wrappedPS.toString();
212      }
213    
214    //========================== wrapped methods ===========================
215    
216    public ResultSet executeQuery() throws SQLException
217      {
218      return wrappedPS.executeQuery();
219      }
220      
221    public int executeUpdate() throws SQLException
222      {
223      return wrappedPS.executeUpdate(); 
224      }
225    
226    public 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    
234    public 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      
242    public 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    
250    public 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      
258    public 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      
266    public 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      
274    public 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    
282    public 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    
290    public 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    
298    public 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    
306    public 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    
314    public 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    
322    public 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    
330    public 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    
338    public 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    
346    public 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    
354    public void clearParameters() throws SQLException
355      {
356      wrappedPS.clearParameters();
357      }
358    
359    public 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    
367    public 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    
375    public 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    
383    public boolean execute() throws SQLException
384      {
385      return wrappedPS.execute();
386      }
387    
388    public void addBatch() throws SQLException
389      {
390      wrappedPS.addBatch(); 
391      }
392      
393    public 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    
401    public 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    
409    public 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    
417    public 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    
425    public 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    
433    public ResultSetMetaData getMetaData() throws SQLException
434      {
435      return wrappedPS.getMetaData();
436      }
437    
438    public 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    
446    public 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    
454    public 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    
462    public 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    
470    public 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    
478    public ParameterMetaData getParameterMetaData() throws SQLException
479      {
480      return wrappedPS.getParameterMetaData();
481      }
482    
483    }