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 
00034 import java.sql.Connection;
00035 import java.sql.PreparedStatement;
00036 import java.sql.SQLException;
00037 import java.sql.Statement;
00038 
00052 public abstract class AbstractQueryManager {
00053 
00054   // Reference the DB manager and provide a statement to perform queries
00055   //  static protected dbManager Manager;
00056 
00057   // separator for query variables
00058   static char Separator = '%';
00059 
00060   private ManagerDB dbManager;
00061 
00062   public AbstractQueryManager() {
00063   }
00064 
00065   public void registerManagerDB(ManagerDB manager) {
00066     dbManager = manager;
00067   }
00068 
00076   /*
00077    * public static void connectDBManager (ManagerDB db) throws Exception { if (db != null) Manager = db; }
00078    */
00079 
00087   public void close() {
00088     this.getDbManager().disconnect();
00089   }
00090 
00104   protected void executeBlobUpdate(String queryPattern, String parameters[], byte[][] blobs) throws DatabaseException {
00105     try {
00106       String query = this.buildQuery(queryPattern, parameters);
00107       Connection connection = this.getDbManager().getConnection();
00108       if (connection != null) {
00109         try {
00110           PreparedStatement stat = connection.prepareStatement(query);
00111           for (int i = 0; i < blobs.length; i++) {
00112             stat.setBytes(i + parameters.length, blobs[i]);
00113           }
00114           stat.execute();
00115         } finally {
00116           this.getDbManager().garbageOpenedConnection();
00117         }
00118       } else {
00119         throw new DatabaseException("Error during Query execution. No connection available.");
00120       }
00121     } catch (SQLException ex) {
00122       throw new DatabaseException(ex);
00123     }
00124 
00125   }
00126 
00138   protected ResultSet executeQuery(String queryPattern, String parameters[]) throws DatabaseException {
00139     //  LogManager.traceDebug(0, "executeQuery date:"+System.currentTimeMillis());
00140 
00141     String query = "";
00142     try {
00143       ResultSet result = null;
00144       query = this.buildQuery(queryPattern, parameters);
00145       //   System.out.println("query : "+query);
00146 
00147       //     LogManager.traceWarning(0, "AbstractQueryManager executeQuery query :"+query);
00148 
00149       Connection connection = this.getDbManager().getConnection();
00150       if (connection != null) {
00151         Statement stat = connection.createStatement();
00152         result = stat.executeQuery(query);
00153       } else {
00154         throw new DatabaseException("Error during Query execution. No connection available.");
00155       }
00156       //        LogManager.traceDebug(0, "executeQuery END date:"+System.currentTimeMillis());
00157       return result;
00158     } catch (java.sql.SQLException ex) {
00159       throw new DatabaseException(ex);
00160     }
00161   }
00162 
00172   protected ResultSet executeDynamicQuery(String queryPattern, java.util.Hashtable variables) throws DatabaseException {
00173     String query = "";
00174     try {
00175       ResultSet result = null;
00176       query = this.buildDynamicQuery(queryPattern, variables);
00177       Connection connection = this.getDbManager().getConnection();
00178       if (connection != null) {
00179         Statement stat = connection.createStatement();
00180         result = stat.executeQuery(query);
00181       } else {
00182         throw new DatabaseException("Error during Query execution. No connection available.");
00183       }
00184       return result;
00185     } catch (java.sql.SQLException ex) {
00186       throw new DatabaseException(ex);
00187     }
00188   }
00189 
00200   protected void executeUpdate(String queryPattern, String parameters[]) throws DatabaseException {
00201     String query = "";
00202     try {
00203       query = this.buildQuery(queryPattern, parameters);
00204       //System.out.println("update query : "+query);
00205       Connection connection = this.getDbManager().getConnection();
00206       if (connection != null) {
00207         try {
00208           Statement stat = connection.createStatement();
00209           stat.executeUpdate(query);
00210           stat.close();
00211 
00212         } finally {
00213           this.getDbManager().garbageOpenedConnection();
00214         }
00215       } else {
00216         throw new DatabaseException("Error during Query execution. No connection available.");
00217       }
00218     } catch (java.sql.SQLException ex) {
00219       throw new DatabaseException(ex);
00220     }
00221   }
00222 
00232   protected void executeDynamicUpdate(String queryPattern, java.util.Hashtable variables) throws DatabaseException {
00233     String query = "";
00234     try {
00235       query = this.buildDynamicQuery(queryPattern, variables);
00236       Connection connection = this.getDbManager().getConnection();
00237       if (connection != null) {
00238         try {
00239           Statement stat = connection.createStatement();
00240           stat.executeUpdate(query);
00241           stat.close();
00242         } finally {
00243           this.getDbManager().garbageOpenedConnection();
00244         }
00245       } else {
00246         throw new DatabaseException("Error during Query execution. No connection available.");
00247       }
00248     } catch (java.sql.SQLException ex) {
00249       throw new DatabaseException(ex);
00250     }
00251   }
00252 
00253   /*
00254    * 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.
00255    */
00256   protected String buildDynamicQuery(String pattern, java.util.Hashtable variables) {
00257     StringBuffer result = new StringBuffer();
00258     int beginIndex = 0;
00259     int endIndex = 0;
00260     while (endIndex != -1) {
00261       endIndex = pattern.indexOf("${", beginIndex);
00262       if (endIndex == -1)
00263         result.append(pattern.substring(beginIndex, pattern.length()));
00264       else { // find one variable
00265         result.append(pattern.substring(beginIndex, endIndex));
00266         beginIndex = endIndex;
00267         endIndex = pattern.indexOf("}", beginIndex);
00268         if (endIndex != -1) {
00269           String varName = pattern.substring(beginIndex + 2, endIndex);
00270           if (variables.containsKey(varName))
00271             result.append((String) variables.get(varName));
00272           beginIndex = endIndex + 1; // skip '}' character
00273           //} // end if containsKey
00274         } // end if index != -1
00275       } // end if find variable
00276     } // end while
00277     return result.toString();
00278   }
00279 
00290   protected String buildQuery(String queryPattern, String parameters[]) throws DatabaseException {
00291     // Case of zero parameters
00292     if (parameters == null) {
00293       return queryPattern;
00294     }
00295 
00296     StringBuffer newQuery = new StringBuffer();
00297     int currentIndex = 0;
00298     int startIndex = 0;
00299     int lastIndex = 0;
00300     String variable;
00301     String value;
00302 
00303     for (int i = 0; i < parameters.length; i++) {
00304       currentIndex = queryPattern.indexOf(Separator, startIndex);
00305       //remove % tag for SQL like search
00306       if (queryPattern.charAt(currentIndex + 1) == Separator) {
00307         currentIndex += 1;
00308       }
00309       lastIndex = queryPattern.indexOf(Separator, currentIndex + 1);
00310       variable = queryPattern.substring(currentIndex + 1, lastIndex);
00311 
00312       if (variable.equals(String.valueOf(i))) {
00313         // replace %i by the real parameter or NULL
00314         newQuery.append(queryPattern.substring(startIndex, currentIndex));
00315         value = parameters[i];
00316         if ((value == null)) { // || (value.length() == 0)
00317           newQuery.append("NULL");
00318         } else {
00319           //        newQuery.append("\"");
00320           newQuery.append(parameters[i]);
00321           //        newQuery.append("\"");
00322         } // end test null
00323         startIndex = lastIndex + 1;
00324         //remove % tag for SQL like search
00325         if ((startIndex < queryPattern.length()) && (queryPattern.charAt(startIndex) == Separator)) {
00326           startIndex += 1;
00327           newQuery.append(Separator);
00328         }
00329       } else {
00330         throw new DatabaseException("Parsing error in query : " + queryPattern);
00331       }
00332     } // end for
00333     // copy the end of the pattern
00334     newQuery.append(queryPattern.substring(startIndex));
00335     return newQuery.toString();
00336   }
00337 
00338   /*
00339    * build a string in SQL list format : ('item1', 'item2',...) if vector size == 0, then return empty string
00340    */
00341   protected String makeSQLList(String[] items) {
00342     if (items == null)
00343       return "";
00344     StringBuffer buffer = new StringBuffer("(");
00345     for (int i = 0; i < items.length; i++) {
00346       //     buffer.append("'");
00347       buffer.append((String) items[i]);
00348       //     buffer.append("'");
00349       if (i < items.length - 1)
00350         buffer.append(",");
00351     } // end for
00352     buffer.append(")");
00353     return buffer.toString();
00354   }
00355 
00361   public ManagerDB getDbManager() {
00362     if (dbManager == null)
00363       dbManager = ManagerDB.getManager();
00364     return dbManager;
00365   }
00366 
00367 }

Generated on Mon Dec 4 11:03:25 2006 for OpenMobileIS by  doxygen 1.5.1-p1