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 }