KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > caucho > quercus > lib > db > JdbcStatementResource


1 /*
2  * Copyright (c) 1998-2006 Caucho Technology -- all rights reserved
3  *
4  * This file is part of Resin(R) Open Source
5  *
6  * Each copy or derived work must preserve the copyright notice and this
7  * notice unmodified.
8  *
9  * Resin Open Source is free software; you can redistribute it and/or modify
10  * it under the terms of the GNU General Public License as published by
11  * the Free Software Foundation; either version 2 of the License, or
12  * (at your option) any later version.
13  *
14  * Resin Open Source is distributed in the hope that it will be useful,
15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, or any warranty
17  * of NON-INFRINGEMENT. See the GNU General Public License for more
18  * details.
19  *
20  * You should have received a copy of the GNU General Public License
21  * along with Resin Open Source; if not, write to the
22  *
23  * Free Software Foundation, Inc.
24  * 59 Temple Place, Suite 330
25  * Boston, MA 02111-1307 USA
26  *
27  * @author Charles Reich
28  */

29
30 package com.caucho.quercus.lib.db;
31
32 import com.caucho.quercus.env.BooleanValue;
33 import com.caucho.quercus.env.Env;
34 import com.caucho.quercus.env.NullValue;
35 import com.caucho.quercus.env.UnsetValue;
36 import com.caucho.quercus.env.Value;
37 import com.caucho.util.L10N;
38 import com.caucho.util.Log;
39
40 import java.lang.reflect.Constructor JavaDoc;
41 import java.lang.reflect.Method JavaDoc;
42 import java.sql.Connection JavaDoc;
43 import java.sql.PreparedStatement JavaDoc;
44 import java.sql.ResultSet JavaDoc;
45 import java.sql.ResultSetMetaData JavaDoc;
46 import java.sql.SQLException JavaDoc;
47 import java.sql.Types JavaDoc;
48 import java.util.logging.Level JavaDoc;
49 import java.util.logging.Logger JavaDoc;
50
51
52 /**
53  * Represents a JDBC Statement value.
54  */

55 public class JdbcStatementResource {
56   private static final Logger JavaDoc log = Log.open(JdbcStatementResource.class);
57   private static final L10N L = new L10N(JdbcStatementResource.class);
58
59   private JdbcConnectionResource _conn;
60   private ResultSet JavaDoc _rs;
61   private String JavaDoc _query;
62   private PreparedStatement JavaDoc _stmt;
63   private ResultSetMetaData JavaDoc _metaData;
64   private JdbcResultResource _resultResource = null;
65
66   private char[] _types;
67   private Value[] _params;
68   private Value[] _results;
69
70   private String JavaDoc _errorMessage = "";
71   private int _errorCode;
72
73   // Statement type
74
// (SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, ALTER, BEGIN, DECLARE, UNKNOWN)
75
private String JavaDoc _stmtType;
76
77   /**
78    * Constructor for JdbcStatementResource
79    *
80    * @param connV a JdbcConnectionResource connection
81    * @param query a query string to prepare this statement
82    */

83   public JdbcStatementResource(JdbcConnectionResource connV,
84                                String JavaDoc query)
85     throws SQLException JavaDoc
86   {
87     _conn = connV;
88     prepareStatement(query);
89   }
90
91   /**
92    * Constructor for JdbcStatementResource
93    *
94    * @param connV a JdbcConnectionResource connection
95    */

96   public JdbcStatementResource(JdbcConnectionResource connV)
97   {
98     _conn = connV;
99   }
100
101   /**
102    * Creates _types and _params array for this prepared statement.
103    *
104    * @param types = string of i,d,s,b (ie: "idds")
105    * @param params = array of values (probably Vars)
106    * @return true on success ir false on failure
107    */

108   protected boolean bindParams(Env env,
109                                String JavaDoc types,
110                                Value[] params)
111   {
112     // This will create the _types and _params arrays
113
// for this prepared statement.
114

115     final int size = types.length();
116
117     // Check to see that types and params have the same length
118
if (size != params.length) {
119       env.warning(L.l("number of types does not match number of parameters"));
120       return false;
121     }
122
123     // Check to see that types only contains i,d,s,b
124
for (int i = 0; i < size; i++) {
125       if ("idsb".indexOf(types.charAt(i)) < 0) {
126         env.warning(L.l("invalid type string {0}", types));
127         return false;
128       }
129     }
130
131     _types = new char[size];
132     _params = new Value[size];
133
134     for (int i = 0; i < size; i++) {
135       _types[i] = types.charAt(i);
136       _params[i] = params[i];
137     }
138
139     return true;
140   }
141
142   /**
143    * Associate (bind) columns in the result set to variables.
144    * <p/>
145    * NB: we assume that the statement has been executed and
146    * compare the # of outParams w/ the # of columns in the
147    * resultset because we cannot know in advance how many
148    * columns "SELECT * FROM TableName" can return.
149    * <p/>
150    * PHP 5.0 seems to provide some rudimentary checking on # of
151    * outParams even before the statement has been executed
152    * and only issues a warning in the case of "SELECT * FROM TableName".
153    * <p/>
154    * Our implementation REQUIRES the execute happen first.
155    *
156    * @param env the PHP executing environment
157    * @param outParams the output variables
158    * @return true on success or false on failure
159    */

160   public boolean bindResults(Env env,
161                              Value[] outParams)
162   {
163     int size = outParams.length;
164     int numColumns;
165
166     try {
167       ResultSetMetaData JavaDoc md = getMetaData();
168
169       numColumns = md.getColumnCount();
170     } catch (SQLException JavaDoc e) {
171       log.log(Level.FINE, e.toString(), e);
172       return false;
173     }
174
175     if (size != numColumns) {
176       env.warning(L.l("number of bound variables does not equal number of columns"));
177       return false;
178     }
179
180     _results = new Value[size];
181
182     System.arraycopy(outParams, 0, _results, 0, size);
183
184     return true;
185   }
186
187   /**
188    * Closes the result set, if any, and closes this statement.
189    */

190   public void close()
191   {
192     try {
193       if (_rs != null)
194         _rs.close();
195
196       if (_stmt != null)
197         _stmt.close();
198
199     } catch (SQLException JavaDoc e) {
200       _errorMessage = e.getMessage();
201       _errorCode = e.getErrorCode();
202       log.log(Level.FINE, e.toString(), e);
203     }
204   }
205
206   /**
207    * Advance the cursor the number of rows given by offset.
208    *
209    * @param offset the number of rows to move the cursor
210    * @return true on success or false on failure
211    */

212   protected boolean dataSeek(int offset)
213   {
214     return JdbcResultResource.setRowNumber(_rs, offset);
215   }
216
217   /**
218    * Returns the error number for the last error.
219    *
220    * @return the error number
221    */

222   public int errorCode()
223   {
224     return _errorCode;
225   }
226
227   /**
228    * Returns the error message for the last error.
229    *
230    * @return the error message
231    */

232   public String JavaDoc errorMessage()
233   {
234     return _errorMessage;
235   }
236
237   /**
238    * Executes a prepared Query.
239    *
240    * @param env the PHP executing environment
241    * @return true on success or false on failure
242    */

243   public boolean execute(Env env)
244   {
245     try {
246       if (_types != null) {
247         int size = _types.length;
248         for (int i = 0; i < size; i++) {
249           switch (_types[i]) {
250           case 'i':
251             _stmt.setInt(i + 1, _params[i].toInt());
252             break;
253           case 'd':
254             _stmt.setDouble(i + 1, _params[i].toDouble());
255             break;
256             // XXX: blob needs to be redone
257
// Currently treated as a string
258
case 'b':
259             _stmt.setString(i + 1, _params[i].toString());
260             break;
261           case 's':
262             _stmt.setString(i + 1, _params[i].toString());
263             break;
264           default:
265             break;
266           }
267         }
268       }
269
270       return executeStatement();
271
272     } catch (SQLException JavaDoc e) {
273       env.warning(L.l(e.toString()));
274       log.log(Level.FINE, e.toString(), e);
275       _errorMessage = e.getMessage();
276       _errorCode = e.getErrorCode();
277       return false;
278     }
279   }
280
281   /**
282    * Executes underlying statement
283    * Known subclasses: see PostgresStatement.execute
284    */

285   protected boolean executeStatement()
286     throws SQLException JavaDoc
287   {
288     try {
289
290       if (_stmt.execute()) {
291         _conn.setAffectedRows(0);
292         _rs = _stmt.getResultSet();
293       } else {
294         _conn.setAffectedRows(_stmt.getUpdateCount());
295       }
296
297       return true;
298
299     } catch (SQLException JavaDoc e) {
300       _errorMessage = e.getMessage();
301       _errorCode = e.getErrorCode();
302       throw e;
303     }
304   }
305
306   /**
307    * Fetch results from a prepared statement into bound variables.
308    *
309    * @return true on success, false on error null if no more rows
310    */

311   public Value fetch(Env env)
312   {
313     try {
314       if (_rs.next()) {
315         if (_metaData == null)
316           _metaData = _rs.getMetaData();
317
318         int size = _results.length;
319         for (int i = 0; i < size; i++) {
320           _results[i].set(_resultResource.getColumnValue(env, _rs, _metaData, i + 1));
321         }
322         return BooleanValue.TRUE;
323       } else
324         return BooleanValue.FALSE;
325
326     } catch (SQLException JavaDoc e) {
327       log.log(Level.FINE, e.toString(), e);
328       return NullValue.NULL;
329     }
330   }
331
332   /**
333    * Frees the associated result.
334    *
335    * @return true on success or false on failure
336    */

337   public boolean freeResult()
338   {
339     if (_rs == null)
340       return true;
341
342     try {
343       _rs.close();
344       _rs = null;
345       if (_resultResource != null) {
346         _resultResource.close();
347         _resultResource = null;
348       }
349       return true;
350     } catch (SQLException JavaDoc e) {
351       _errorMessage = e.getMessage();
352       _errorCode = e.getErrorCode();
353       log.log(Level.FINE, e.toString(), e);
354       return false;
355     }
356   }
357
358   /**
359    * Returns the meta data for corresponding to the current result set.
360    *
361    * @return the result set meta data
362    */

363   protected ResultSetMetaData JavaDoc getMetaData()
364     throws SQLException JavaDoc
365   {
366     if (_metaData == null)
367       _metaData = _rs.getMetaData();
368
369     return _metaData;
370   }
371
372   /**
373    * Returns the number of rows in the result set.
374    *
375    * @return the number of rows in the result set
376    */

377   public int getNumRows()
378     throws SQLException JavaDoc
379   {
380     if (_rs != null)
381       return JdbcResultResource.getNumRows(_rs);
382     else
383       return 0;
384   }
385
386   /**
387    * Returns the internal prepared statement.
388    *
389    * @return the internal prepared statement
390    */

391   protected PreparedStatement JavaDoc getPreparedStatement()
392   {
393     return _stmt;
394   }
395
396   /**
397    * Resets _fieldOffset in _resultResource
398    *
399    * @return null if _resultResource == null, otherwise _resultResource
400    */

401   public JdbcResultResource getResultMetadata()
402   {
403     if (_resultResource != null) {
404       _resultResource.setFieldOffset(0);
405       return _resultResource;
406     }
407
408     if ((_stmt == null) || (_rs == null))
409       return null;
410
411     _resultResource = new JdbcResultResource(_stmt, _rs, _conn);
412     return _resultResource;
413   }
414
415   /**
416    * Returns the internal result set.
417    *
418    * @return the internal result set
419    */

420   protected ResultSet JavaDoc getResultSet()
421   {
422     return _rs;
423   }
424
425   /**
426    * Returns the underlying SQL connection
427    * associated to this statement.
428    */

429   protected Connection JavaDoc getJavaConnection()
430     throws SQLException JavaDoc
431   {
432     return validateConnection().getJavaConnection();
433   }
434
435   /**
436    * Returns this statement type.
437    *
438    * @return this statement type:
439    * SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, ALTER, BEGIN, DECLARE, or UNKNOWN.
440    */

441   public String JavaDoc getStatementType()
442   {
443     // Oracle Statement type
444
// Also used internally in Postgres (see PostgresModule)
445
// (SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, ALTER, BEGIN, DECLARE, UNKNOWN)
446

447     _stmtType = _query;
448     _stmtType = _stmtType.replaceAll("\\s+.*", "");
449     if (_stmtType.equals("")) {
450       _stmtType = "UNKNOWN";
451     } else {
452       _stmtType = _stmtType.toUpperCase();
453       String JavaDoc s = _stmtType.replaceAll("(SELECT|UPDATE|DELETE|INSERT|CREATE|DROP|ALTER|BEGIN|DECLARE)", "");
454       if (!s.equals("")) {
455         _stmtType = "UNKNOWN";
456       }
457     }
458
459     return _stmtType;
460   }
461
462   /**
463    * Counts the number of parameter markers in the query string.
464    *
465    * @return the number of parameter markers in the query string
466    */

467   public int paramCount()
468   {
469     if (_query == null)
470       return -1;
471
472     int count = 0;
473     int length = _query.length();
474     boolean inQuotes = false;
475     char c;
476
477     for (int i = 0; i < length; i++) {
478       c = _query.charAt(i);
479
480       if (c == '\\') {
481         if (i < length - 1)
482           i++;
483         continue;
484       }
485
486       if (inQuotes) {
487         if (c == '\'')
488           inQuotes = false;
489         continue;
490       }
491
492       if (c == '\'') {
493         inQuotes = true;
494         continue;
495       }
496
497       if (c == '?') {
498         count++;
499       }
500     }
501
502     return count;
503   }
504
505   /**
506    * Prepares this statement with the given query.
507    *
508    * @param query SQL query
509    * @return true on success or false on failure
510    */

511   public boolean prepare(String JavaDoc query)
512   {
513     try {
514
515       if (_stmt != null)
516         _stmt.close();
517
518       _query = query;
519
520       if (this instanceof OracleStatement) {
521         _stmt = _conn.getConnection().prepareCall(query,
522                                                   ResultSet.TYPE_SCROLL_INSENSITIVE,
523                                                   ResultSet.CONCUR_READ_ONLY);
524       } else {
525         _stmt = _conn.getConnection().prepareStatement(query,
526                                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
527                                                        ResultSet.CONCUR_READ_ONLY);
528       }
529
530       return true;
531
532     } catch (SQLException JavaDoc e) {
533       log.log(Level.FINE, e.toString(), e);
534       _errorMessage = e.getMessage();
535       _errorCode = e.getErrorCode();
536       return false;
537     }
538   }
539
540   /**
541    * Prepares statement with the given query.
542    *
543    * @param query SQL query
544    * @return true on success or false on failure
545    */

546   public boolean prepareStatement(String JavaDoc query)
547   {
548     try {
549
550       if (_stmt != null)
551         _stmt.close();
552
553       _query = query;
554
555       if (this instanceof OracleStatement) {
556         _stmt = _conn.getConnection().prepareCall(query,
557                                                   ResultSet.TYPE_SCROLL_INSENSITIVE,
558                                                   ResultSet.CONCUR_READ_ONLY);
559       } else {
560         _stmt = _conn.getConnection().prepareStatement(query,
561                                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
562                                                        ResultSet.CONCUR_READ_ONLY);
563       }
564
565       return true;
566
567     } catch (SQLException JavaDoc e) {
568       log.log(Level.FINE, e.toString(), e);
569       _errorMessage = e.getMessage();
570       _errorCode = e.getErrorCode();
571       return false;
572     }
573   }
574
575   /**
576    * Returns a parameter value
577    * Known subclasses: see PostgresStatement.execute
578    */

579   protected Value getParam(int i)
580   {
581     if (i >= _params.length)
582       return UnsetValue.UNSET;
583
584     return _params[i];
585   }
586
587   /**
588    * Returns the number of parameters available to binding
589    * Known subclasses: see PostgresStatement.execute
590    */

591   protected int getParamLength()
592   {
593     return _params.length;
594   }
595
596   /**
597    * Changes the internal statement.
598    */

599   protected void setPreparedStatement(PreparedStatement JavaDoc stmt)
600   {
601     _stmt = stmt;
602   }
603
604   /**
605    * Changes the internal result set.
606    */

607   protected void setResultSet(ResultSet JavaDoc rs)
608   {
609     _rs = rs;
610   }
611
612   /**
613    * Sets the given parameter
614    * Known subclasses: see PostgresStatement.execute
615    */

616   protected void setObject(int i, Object JavaDoc param)
617     throws Exception JavaDoc
618   {
619     try {
620       // See php/4358, php/43b8, php/43d8, and php/43p8.
621
java.sql.ParameterMetaData JavaDoc pmd = _stmt.getParameterMetaData();
622       int type = pmd.getParameterType(i);
623
624       switch (type) {
625
626       case Types.OTHER:
627         {
628           // See php/43b8
629
String JavaDoc typeName = pmd.getParameterTypeName(i);
630           if (typeName.equals("interval")) {
631             _stmt.setObject(i, param);
632           } else {
633             Class JavaDoc cl = Class.forName("org.postgresql.util.PGobject");
634             Constructor JavaDoc constructor = cl.getDeclaredConstructor(null);
635             Object JavaDoc object = constructor.newInstance();
636
637             Method JavaDoc method = cl.getDeclaredMethod("setType", new Class JavaDoc[] {String JavaDoc.class});
638             method.invoke(object, new Object JavaDoc[] {typeName});
639
640             method = cl.getDeclaredMethod("setValue", new Class JavaDoc[] {String JavaDoc.class});
641             method.invoke(object, new Object JavaDoc[] {param});
642
643             _stmt.setObject(i, object, type);
644           }
645           break;
646         }
647
648       case Types.DOUBLE:
649         {
650           // See php/43p8.
651
String JavaDoc typeName = pmd.getParameterTypeName(i);
652           if (typeName.equals("money")) {
653             String JavaDoc s = param.toString();
654
655             if (s.length() == 0) {
656               throw new IllegalArgumentException JavaDoc(L.l("argument `{0}' cannot be empty", param));
657             } else {
658
659               String JavaDoc money = s;
660
661               if (s.charAt(0) == '$')
662                 s = s.substring(1);
663               else
664                 money = "$"+money;
665
666               try {
667                 // This will throw an exception if not double while
668
// trying to setObject() would not. The error would
669
// come late, otherwise. See php/43p8.
670
Double.parseDouble(s);
671               } catch (Exception JavaDoc ex) {
672                 throw new IllegalArgumentException JavaDoc(L.l("cannot convert argument `{0}' to money", param));
673               }
674
675               Class JavaDoc cl = Class.forName("org.postgresql.util.PGmoney");
676               Constructor JavaDoc constructor = cl.getDeclaredConstructor(new Class JavaDoc[] {String JavaDoc.class});
677               Object JavaDoc object = constructor.newInstance(new Object JavaDoc[] {money});
678
679               _stmt.setObject(i, object, Types.OTHER);
680
681               break;
682             }
683           }
684           // else falls to default case
685
}
686
687       default:
688         _stmt.setObject(i, param, type);
689       }
690     }
691     catch (SQLException JavaDoc e) {
692       _errorMessage = e.getMessage();
693       _errorCode = e.getErrorCode();
694       throw e;
695     }
696     catch (Exception JavaDoc e) {
697       _stmt.clearParameters();
698       throw e;
699     }
700   }
701
702   /**
703    * Returns a string representation for this object.
704    *
705    * @return the string representation for this object
706    */

707   public String JavaDoc toString()
708   {
709     return getClass().getName() + "[" + _conn + "]";
710   }
711
712   /**
713    * Validates the connection resource.
714    *
715    * @return the validated connection resource
716    */

717   public JdbcConnectionResource validateConnection()
718   {
719     return _conn;
720   }
721 }
722
723
Popular Tags