View Javadoc
1   /*
2    * This file is part of Waarp Project (named also Waarp or GG).
3    *
4    *  Copyright (c) 2019, Waarp SAS, and individual contributors by the @author
5    *  tags. See the COPYRIGHT.txt in the distribution for a full listing of
6    * individual contributors.
7    *
8    *  All Waarp Project is free software: you can redistribute it and/or
9    * modify it under the terms of the GNU General Public License as published by
10   * the Free Software Foundation, either version 3 of the License, or (at your
11   * option) any later version.
12   *
13   * Waarp is distributed in the hope that it will be useful, but WITHOUT ANY
14   * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15   * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16   *
17   *  You should have received a copy of the GNU General Public License along with
18   * Waarp . If not, see <http://www.gnu.org/licenses/>.
19   */
20  package org.waarp.common.database;
21  
22  import org.waarp.common.database.exception.WaarpDatabaseNoConnectionException;
23  import org.waarp.common.database.exception.WaarpDatabaseSqlException;
24  import org.waarp.common.logging.WaarpLogger;
25  import org.waarp.common.logging.WaarpLoggerFactory;
26  
27  import java.sql.PreparedStatement;
28  import java.sql.ResultSet;
29  import java.sql.SQLException;
30  
31  /**
32   * Class to handle PrepareStatement
33   */
34  public class DbPreparedStatement {
35    /**
36     * Internal Logger
37     */
38    private static final WaarpLogger logger =
39        WaarpLoggerFactory.getLogger(DbPreparedStatement.class);
40    private static final String SQL_EXCEPTION_PREPARED_STATEMENT_NO_SESSION =
41        "SQL Exception PreparedStatement no session";
42    private static final String PREPARED_STATEMENT_NO_SESSION =
43        "PreparedStatement no session";
44    private static final String PREPARED_STATEMENT_NO_REQUEST =
45        "PreparedStatement no request";
46  
47    /**
48     * Internal PreparedStatement
49     */
50    private PreparedStatement preparedStatement;
51  
52    /**
53     * The Associated request
54     */
55    private String request;
56  
57    /**
58     * Is this PreparedStatement ready
59     */
60    private boolean isReady;
61  
62    /**
63     * The associated resultSet
64     */
65    private ResultSet rs;
66  
67    /**
68     * The associated DB session
69     */
70    private final DbSession ls;
71  
72    /**
73     * Create a DbPreparedStatement from DbSession object
74     *
75     * @param ls
76     *
77     * @throws WaarpDatabaseNoConnectionException
78     */
79    public DbPreparedStatement(final DbSession ls)
80        throws WaarpDatabaseNoConnectionException {
81      if (ls == null) {
82        logger.error(SQL_EXCEPTION_PREPARED_STATEMENT_NO_SESSION);
83        throw new WaarpDatabaseNoConnectionException(
84            PREPARED_STATEMENT_NO_SESSION);
85      }
86      if (ls.isDisActive()) {
87        logger.debug("DisActive: {}", ls.getAdmin().getServer());
88        ls.checkConnection();
89      }
90      this.ls = ls;
91      rs = null;
92      preparedStatement = null;
93      setReady(false);
94    }
95  
96    /**
97     * Create a DbPreparedStatement from DbSession object and a request
98     *
99     * @param ls
100    * @param request
101    *
102    * @throws WaarpDatabaseNoConnectionException
103    * @throws WaarpDatabaseSqlException
104    */
105   public DbPreparedStatement(final DbSession ls, final String request)
106       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
107     if (ls == null) {
108       logger.error(SQL_EXCEPTION_PREPARED_STATEMENT_NO_SESSION);
109       throw new WaarpDatabaseNoConnectionException(
110           PREPARED_STATEMENT_NO_SESSION);
111     }
112     if (ls.isDisActive()) {
113       ls.checkConnection();
114     }
115     this.ls = ls;
116     rs = null;
117     setReady(false);
118     preparedStatement = null;
119     if (request == null) {
120       logger.error(PREPARED_STATEMENT_NO_REQUEST);
121       throw new WaarpDatabaseNoConnectionException(
122           PREPARED_STATEMENT_NO_REQUEST);
123     }
124     try {
125       preparedStatement = this.ls.getConn().prepareStatement(request);
126       this.request = request;
127       setReady(true);
128     } catch (final SQLException e) {
129       ls.checkConnection();
130       try {
131         preparedStatement = this.ls.getConn().prepareStatement(request);
132         this.request = request;
133         setReady(true);
134       } catch (final SQLException e1) {
135         logger.error("SQL Exception PreparedStatement: " + request + ' ' +
136                      e.getMessage());
137         DbConstant.error(e);
138         preparedStatement = null;
139         setReady(false);
140         throw new WaarpDatabaseSqlException("SQL Exception PreparedStatement",
141                                             e);
142       }
143     }
144   }
145 
146   /**
147    * Create a DbPreparedStatement from DbSession object and a request
148    *
149    * @param ls
150    * @param request
151    * @param nbFetch the number of pre fetch rows
152    *
153    * @throws WaarpDatabaseNoConnectionException
154    * @throws WaarpDatabaseSqlException
155    */
156   public DbPreparedStatement(final DbSession ls, final String request,
157                              final int nbFetch)
158       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
159     if (ls == null) {
160       logger.error(SQL_EXCEPTION_PREPARED_STATEMENT_NO_SESSION);
161       throw new WaarpDatabaseNoConnectionException(
162           PREPARED_STATEMENT_NO_SESSION);
163     }
164     if (ls.isDisActive()) {
165       ls.checkConnection();
166     }
167     this.ls = ls;
168     rs = null;
169     setReady(false);
170     preparedStatement = null;
171     if (request == null) {
172       logger.error(PREPARED_STATEMENT_NO_REQUEST);
173       throw new WaarpDatabaseNoConnectionException(
174           PREPARED_STATEMENT_NO_SESSION);
175     }
176     try {
177       preparedStatement = this.ls.getConn().prepareStatement(request);
178       this.request = request;
179       preparedStatement.setFetchSize(nbFetch);
180       setReady(true);
181     } catch (final SQLException e) {
182       ls.checkConnection();
183       try {
184         preparedStatement = this.ls.getConn().prepareStatement(request);
185         this.request = request;
186         preparedStatement.setFetchSize(nbFetch);
187         setReady(true);
188       } catch (final SQLException e1) {
189         logger.error("SQL Exception PreparedStatement: " + request + ' ' +
190                      e.getMessage());
191         DbConstant.error(e);
192         preparedStatement = null;
193         setReady(false);
194         throw new WaarpDatabaseSqlException("SQL Exception PreparedStatement",
195                                             e);
196       }
197     }
198   }
199 
200   /**
201    * Create a preparedStatement from request
202    *
203    * @param requestarg
204    *
205    * @throws WaarpDatabaseNoConnectionException
206    * @throws WaarpDatabaseSqlException
207    */
208   public final void createPrepareStatement(final String requestarg)
209       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
210     if (requestarg == null) {
211       logger.error(PREPARED_STATEMENT_NO_REQUEST);
212       throw new WaarpDatabaseNoConnectionException(
213           PREPARED_STATEMENT_NO_REQUEST);
214     }
215     if (preparedStatement != null) {
216       realClose();
217     }
218     if (rs != null) {
219       close();
220     }
221     if (ls.isDisActive()) {
222       logger.debug("DisActive: {}", ls.getAdmin().getServer());
223       ls.checkConnection();
224     }
225     try {
226       preparedStatement = ls.getConn().prepareStatement(requestarg);
227       request = requestarg;
228       setReady(true);
229     } catch (final SQLException e) {
230       ls.checkConnection();
231       try {
232         preparedStatement = ls.getConn().prepareStatement(requestarg);
233         request = requestarg;
234         setReady(true);
235       } catch (final SQLException e1) {
236         logger.error(
237             "SQL Exception createPreparedStatement from {}:" + requestarg +
238             ' ' + e.getMessage(), ls.getAdmin().getServer());
239         DbConstant.error(e);
240         realClose();
241         preparedStatement = null;
242         setReady(false);
243         throw new WaarpDatabaseSqlException(
244             "SQL Exception createPreparedStatement: " + requestarg, e);
245       }
246     }
247   }
248 
249   /**
250    * In case of closing database connection, it is possible to reopen a long
251    * term preparedStatement as it was at
252    * creation.
253    *
254    * @throws WaarpDatabaseSqlException
255    * @throws WaarpDatabaseNoConnectionException
256    */
257   public final void recreatePreparedStatement()
258       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
259     createPrepareStatement(request);
260   }
261 
262   /**
263    * Execute a Select preparedStatement
264    *
265    * @throws WaarpDatabaseNoConnectionException
266    * @throws WaarpDatabaseSqlException
267    */
268   public final void executeQuery()
269       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
270     if (preparedStatement == null) {
271       logger.error("executeQuery no request");
272       throw new WaarpDatabaseNoConnectionException("executeQuery no request");
273     }
274     if (rs != null) {
275       close();
276     }
277     if (ls.isDisActive()) {
278       ls.checkConnection();
279       throw new WaarpDatabaseSqlException(
280           "Request cannot be executed since connection was recreated between: " +
281           request);
282     }
283     try {
284       rs = preparedStatement.executeQuery();
285     } catch (final SQLException e) {
286       logger.error(
287           "SQL Exception executeQuery:" + request + ' ' + e.getMessage());
288       DbConstant.error(e);
289       close();
290       rs = null;
291       ls.checkConnectionNoException();
292       throw new WaarpDatabaseSqlException(
293           "SQL Exception executeQuery: " + request, e);
294     }
295   }
296 
297   /**
298    * Execute the Update/Insert/Delete preparedStatement
299    *
300    * @return the number of row
301    *
302    * @throws WaarpDatabaseNoConnectionException
303    * @throws WaarpDatabaseSqlException
304    */
305   public final int executeUpdate()
306       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
307     if (preparedStatement == null) {
308       logger.error("executeUpdate no request");
309       throw new WaarpDatabaseNoConnectionException("executeUpdate no request");
310     }
311     if (rs != null) {
312       close();
313     }
314     if (ls.isDisActive()) {
315       ls.checkConnection();
316       throw new WaarpDatabaseSqlException(
317           "Request cannot be executed since connection was recreated between:" +
318           request);
319     }
320     final int retour;
321     try {
322       retour = preparedStatement.executeUpdate();
323     } catch (final SQLException e) {
324       logger.error(
325           "SQL Exception executeUpdate:" + request + ' ' + e.getMessage());
326       logger.debug("SQL Exception full stack trace", e);
327       DbConstant.error(e);
328       ls.checkConnectionNoException();
329       throw new WaarpDatabaseSqlException(
330           "SQL Exception executeUpdate: " + request, e);
331     }
332     return retour;
333   }
334 
335   /**
336    * Close the resultSet if any
337    */
338   public final void close() {
339     if (rs != null) {
340       try {
341         rs.close();
342       } catch (final SQLException ignored) {
343         // nothing
344       }
345       rs = null;
346     }
347   }
348 
349   /**
350    * Really close the preparedStatement and the resultSet if any
351    */
352   public final void realClose() {
353     close();
354     if (preparedStatement != null) {
355       if (ls.isDisActive()) {
356         ls.checkConnectionNoException();
357       }
358       try {
359         preparedStatement.close();
360       } catch (final SQLException e) {
361         ls.checkConnectionNoException();
362       }
363       preparedStatement = null;
364     }
365     setReady(false);
366   }
367 
368   /**
369    * Move the cursor to the next result
370    *
371    * @return True if there is a next result, else False
372    *
373    * @throws WaarpDatabaseNoConnectionException
374    * @throws WaarpDatabaseSqlException
375    */
376   public final boolean getNext()
377       throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
378     if (rs == null) {
379       logger.error("SQL ResultSet is Null into getNext");
380       throw new WaarpDatabaseNoConnectionException(
381           "SQL ResultSet is Null into getNext");
382     }
383     if (ls.isDisActive()) {
384       ls.checkConnection();
385       throw new WaarpDatabaseSqlException(
386           "Request cannot be executed since connection was recreated between");
387     }
388     try {
389       return rs.next();
390     } catch (final SQLException e) {
391       logger.error("SQL Exception to getNextRow" +
392                    (request != null? " [" + request + ']' : "") + ' ' +
393                    e.getMessage());
394       DbConstant.error(e);
395       ls.checkConnectionNoException();
396       throw new WaarpDatabaseSqlException(
397           "SQL Exception to getNextRow: " + request, e);
398     }
399   }
400 
401   /**
402    * @return The resultSet (can be used in conjunction of getNext())
403    *
404    * @throws WaarpDatabaseNoConnectionException
405    */
406   public final ResultSet getResultSet()
407       throws WaarpDatabaseNoConnectionException {
408     if (rs == null) {
409       throw new WaarpDatabaseNoConnectionException(
410           "SQL ResultSet is Null into getResultSet");
411     }
412     return rs;
413   }
414 
415   /**
416    * @return The preparedStatement (should be used in conjunction of
417    *     createPreparedStatement)
418    *
419    * @throws WaarpDatabaseNoConnectionException
420    */
421   public final PreparedStatement getPreparedStatement()
422       throws WaarpDatabaseNoConnectionException {
423     if (preparedStatement == null) {
424       throw new WaarpDatabaseNoConnectionException(
425           "SQL PreparedStatement is Null into getPreparedStatement");
426     }
427     return preparedStatement;
428   }
429 
430   /**
431    * @return the dbSession
432    */
433   public final DbSession getDbSession() {
434     return ls;
435   }
436 
437   /**
438    * @return the isReady
439    */
440   public final boolean isReady() {
441     return isReady;
442   }
443 
444   /**
445    * @param isReady the isReady to set
446    */
447   private void setReady(final boolean isReady) {
448     this.isReady = isReady;
449   }
450 
451   @Override
452   public String toString() {
453     return request;
454   }
455 }