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
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
00055
00056
00057
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
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
00140
00141 String query = "";
00142 try {
00143 ResultSet result = null;
00144 query = this.buildQuery(queryPattern, parameters);
00145
00146
00147
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
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
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
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 {
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;
00273
00274 }
00275 }
00276 }
00277 return result.toString();
00278 }
00279
00290 protected String buildQuery(String queryPattern, String parameters[]) throws DatabaseException {
00291
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
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
00314 newQuery.append(queryPattern.substring(startIndex, currentIndex));
00315 value = parameters[i];
00316 if ((value == null)) {
00317 newQuery.append("NULL");
00318 } else {
00319
00320 newQuery.append(parameters[i]);
00321
00322 }
00323 startIndex = lastIndex + 1;
00324
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 }
00333
00334 newQuery.append(queryPattern.substring(startIndex));
00335 return newQuery.toString();
00336 }
00337
00338
00339
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
00347 buffer.append((String) items[i]);
00348
00349 if (i < items.length - 1)
00350 buffer.append(",");
00351 }
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 }