00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
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
00056
00057
00058
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
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
00143
00144 String query = "";
00145 try {
00146 ResultSet result = null;
00147 query = this.buildQuery(queryPattern, parameters);
00148
00149
00150
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
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
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 {
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;
00279
00280 }
00281 }
00282 }
00283 return result.toString();
00284 }
00285
00296 protected String buildQuery(String queryPattern, String parameters[]) throws DatabaseException {
00297
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
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
00320 newQuery.append(queryPattern.substring(startIndex, currentIndex));
00321 value = parameters[i];
00322 if ((value == null)) {
00323 newQuery.append("NULL");
00324 } else {
00325
00326 newQuery.append(parameters[i]);
00327
00328 }
00329 startIndex = lastIndex + 1;
00330
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 }
00339
00340 newQuery.append(queryPattern.substring(startIndex));
00341 return newQuery.toString();
00342 }
00343
00344
00345
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
00353 buffer.append((String) items[i]);
00354
00355 if (i < items.length - 1)
00356 buffer.append(",");
00357 }
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 }