AbstractQueryManager.java

00001 /*
00002  * OpenMobileIS - a free Java(TM) Framework for mobile applications Java(TM)
00003  * Copyright (C) 2004-2006 Philippe Delrieu
00004  * All rights reserved.
00005  * Contact: pdelrieu@openmobileis.org
00006  *
00007  * This library is free software; you can redistribute it and/or
00008  * modify it under the terms of the GNU Lesser General Public
00009  * License as published by the Free Software Foundation; either
00010  * version 2.1 of the License, or any later version.
00011  *
00012  * This library is distributed in the hope that it will be useful,
00013  * but WITHOUT ANY WARRANTY; without even the implied warranty of
00014  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00015  * Lesser General Public License for more details.
00016  *
00017  * You should have received a copy of the GNU Lesser General Public
00018  * License along with this library; if not, write to the Free Software
00019  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
00020  * USA
00021  *
00022  *  Author : Philippe Delrieu
00023  *  
00024  *  Modifications :
00025  *  2004 Creation P.Delrieu
00026  * 
00027  */
00028 
00029 package org.openmobileis.common.util.database;
00030 
00031 import java.sql.ResultSet;
00032 import org.openmobileis.common.util.exception.DatabaseException;
00033 import org.openmobileis.common.util.log.LogManager;
00034 
00035 import java.sql.Connection;
00036 import java.sql.PreparedStatement;
00037 import java.sql.SQLException;
00038 import java.sql.Statement;
00039 
00053 public abstract class AbstractQueryManager {
00054 
00055   // Reference the DB manager and provide a statement to perform queries
00056   //  static protected dbManager Manager;
00057 
00058   // separator for query variables
00059   static char Separator = '%';
00060 
00061   private ManagerDB dbManager;
00062 
00063   public AbstractQueryManager() {
00064   }
00065 
00066   public void registerManagerDB(ManagerDB manager) {
00067     dbManager = manager;
00068   }
00069 
00077   /*
00078    * public static void connectDBManager (ManagerDB db) throws Exception { if (db != null) Manager = db; }
00079    */
00080 
00088   public void close() {
00089     this.getDbManager().disconnect();
00090   }
00091 
00105   protected void executeBlobUpdate(String queryPattern, String parameters[], byte[][] blobs) throws DatabaseException {
00106         String query = "";
00107         try {
00108       query = this.buildQuery(queryPattern, parameters);
00109       Connection connection = this.getDbManager().getConnection();
00110       if (connection != null) {
00111         try {
00112           PreparedStatement stat = connection.prepareStatement(query);
00113           for (int i = 0; i < blobs.length; i++) {
00114             stat.setBytes(i + parameters.length, blobs[i]);
00115           }
00116           stat.execute();
00117         } finally {
00118           this.getDbManager().garbageOpenedConnection();
00119         }
00120       } else {
00121         throw new DatabaseException("Error during Query execution. No connection available.");
00122       }
00123     } catch (SQLException ex) {
00124       LogManager.traceError(0, "ERROR executeBlobUpdate query : "+query);
00125       throw new DatabaseException(ex);
00126     }
00127 
00128   }
00129 
00141   protected ResultSet executeQuery(String queryPattern, String parameters[]) throws DatabaseException {
00142     //  LogManager.traceDebug(0, "executeQuery date:"+System.currentTimeMillis());
00143 
00144     String query = "";
00145     try {
00146       ResultSet result = null;
00147       query = this.buildQuery(queryPattern, parameters);
00148       //   System.out.println("query : "+query);
00149 
00150       //     LogManager.traceWarning(0, "AbstractQueryManager executeQuery query :"+query);
00151 
00152       Connection connection = this.getDbManager().getConnection();
00153       if (connection != null) {
00154         Statement stat = connection.createStatement();
00155         result = stat.executeQuery(query);
00156       } else {
00157         throw new DatabaseException("Error during Query execution. No connection available.");
00158       }
00159       //        LogManager.traceDebug(0, "executeQuery END date:"+System.currentTimeMillis());
00160       return result;
00161     } catch (java.sql.SQLException ex) {
00162      LogManager.traceError(0, "ERROR executeQuery query : "+query);
00163      throw new DatabaseException(ex);
00164     }
00165   }
00166 
00176   protected ResultSet executeDynamicQuery(String queryPattern, java.util.Hashtable variables) throws DatabaseException {
00177     String query = "";
00178     try {
00179       ResultSet result = null;
00180       query = this.buildDynamicQuery(queryPattern, variables);
00181       Connection connection = this.getDbManager().getConnection();
00182       if (connection != null) {
00183         Statement stat = connection.createStatement();
00184         result = stat.executeQuery(query);
00185       } else {
00186         throw new DatabaseException("Error during Query execution. No connection available.");
00187       }
00188       return result;
00189     } catch (java.sql.SQLException ex) {
00190       LogManager.traceError(0, "ERROR executeDynamicQuery query : "+query);
00191       throw new DatabaseException(ex);
00192     }
00193   }
00194 
00205   protected void executeUpdate(String queryPattern, String parameters[]) throws DatabaseException {
00206     String query = "";
00207     try {
00208       query = this.buildQuery(queryPattern, parameters);
00209       Connection connection = this.getDbManager().getConnection();
00210       if (connection != null) {
00211         try {
00212           Statement stat = connection.createStatement();
00213           stat.executeUpdate(query);
00214           stat.close();
00215 
00216         } finally {
00217           this.getDbManager().garbageOpenedConnection();
00218         }
00219       } else {
00220         throw new DatabaseException("Error during Query execution. No connection available.");
00221       }
00222     } catch (java.sql.SQLException ex) {
00223       LogManager.traceError(0, "ERROR update query : "+query);
00224       throw new DatabaseException(ex);
00225     }
00226   }
00227 
00237   protected void executeDynamicUpdate(String queryPattern, java.util.Hashtable variables) throws DatabaseException {
00238     String query = "";
00239     try {
00240       query = this.buildDynamicQuery(queryPattern, variables);
00241       Connection connection = this.getDbManager().getConnection();
00242       if (connection != null) {
00243         try {
00244           Statement stat = connection.createStatement();
00245           stat.executeUpdate(query);
00246           stat.close();
00247         } finally {
00248           this.getDbManager().garbageOpenedConnection();
00249         }
00250       } else {
00251         throw new DatabaseException("Error during Query execution. No connection available.");
00252       }
00253     } catch (java.sql.SQLException ex) {
00254       LogManager.traceError(0, "ERROR executeDynamicUpdate query : "+query);
00255       throw new DatabaseException(ex);
00256     }
00257   }
00258 
00259   /*
00260    * Build dynamically the query from a query pattern. The variables in the pattern are set as ${var} and the hashtable contains an entry var-> value.
00261    */
00262   protected String buildDynamicQuery(String pattern, java.util.Hashtable variables) {
00263     StringBuffer result = new StringBuffer();
00264     int beginIndex = 0;
00265     int endIndex = 0;
00266     while (endIndex != -1) {
00267       endIndex = pattern.indexOf("${", beginIndex);
00268       if (endIndex == -1)
00269         result.append(pattern.substring(beginIndex, pattern.length()));
00270       else { // find one variable
00271         result.append(pattern.substring(beginIndex, endIndex));
00272         beginIndex = endIndex;
00273         endIndex = pattern.indexOf("}", beginIndex);
00274         if (endIndex != -1) {
00275           String varName = pattern.substring(beginIndex + 2, endIndex);
00276           if (variables.containsKey(varName))
00277             result.append((String) variables.get(varName));
00278           beginIndex = endIndex + 1; // skip '}' character
00279           //} // end if containsKey
00280         } // end if index != -1
00281       } // end if find variable
00282     } // end while
00283     return result.toString();
00284   }
00285 
00296   protected String buildQuery(String queryPattern, String parameters[]) throws DatabaseException {
00297     // Case of zero parameters
00298     if (parameters == null) {
00299       return queryPattern;
00300     }
00301 
00302     StringBuffer newQuery = new StringBuffer();
00303     int currentIndex = 0;
00304     int startIndex = 0;
00305     int lastIndex = 0;
00306     String variable;
00307     String value;
00308 
00309     for (int i = 0; i < parameters.length; i++) {
00310       currentIndex = queryPattern.indexOf(Separator, startIndex);
00311       //remove % tag for SQL like search
00312       if (queryPattern.charAt(currentIndex + 1) == Separator) {
00313         currentIndex += 1;
00314       }
00315       lastIndex = queryPattern.indexOf(Separator, currentIndex + 1);
00316       variable = queryPattern.substring(currentIndex + 1, lastIndex);
00317 
00318       if (variable.equals(String.valueOf(i))) {
00319         // replace %i by the real parameter or NULL
00320         newQuery.append(queryPattern.substring(startIndex, currentIndex));
00321         value = parameters[i];
00322         if ((value == null)) { // || (value.length() == 0)
00323           newQuery.append("NULL");
00324         } else {
00325           //        newQuery.append("\"");
00326           newQuery.append(parameters[i]);
00327           //        newQuery.append("\"");
00328         } // end test null
00329         startIndex = lastIndex + 1;
00330         //remove % tag for SQL like search
00331         if ((startIndex < queryPattern.length()) && (queryPattern.charAt(startIndex) == Separator)) {
00332           startIndex += 1;
00333           newQuery.append(Separator);
00334         }
00335       } else {
00336         throw new DatabaseException("Parsing error in query : " + queryPattern);
00337       }
00338     } // end for
00339     // copy the end of the pattern
00340     newQuery.append(queryPattern.substring(startIndex));
00341     return newQuery.toString();
00342   }
00343 
00344   /*
00345    * build a string in SQL list format : ('item1', 'item2',...) if vector size == 0, then return empty string
00346    */
00347   protected String makeSQLList(String[] items) {
00348     if (items == null)
00349       return "";
00350     StringBuffer buffer = new StringBuffer("(");
00351     for (int i = 0; i < items.length; i++) {
00352       //     buffer.append("'");
00353       buffer.append((String) items[i]);
00354       //     buffer.append("'");
00355       if (i < items.length - 1)
00356         buffer.append(",");
00357     } // end for
00358     buffer.append(")");
00359     return buffer.toString();
00360   }
00361 
00367   public ManagerDB getDbManager() {
00368     if (dbManager == null)
00369       dbManager = ManagerDB.getManager();
00370     return dbManager;
00371   }
00372 
00373 }

Generated on Mon Jan 11 21:19:13 2010 for OpenMobileIS by  doxygen 1.5.4