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.log.*;
00033 import org.openmobileis.common.util.exception.ServiceException;
00034 import java.sql.Connection;
00035 import java.sql.Statement;
00036
00057 public abstract class AbstractQueryManager {
00058
00059
00060
00061
00062
00063 static char Separator = '%';
00064
00065 private ManagerDB dbManager;
00066
00067 public AbstractQueryManager() {
00068 }
00069
00070 public void registerManagerDB(ManagerDB manager) {
00071 dbManager = manager;
00072 }
00073
00074
00081
00082
00083
00084
00085
00092 public void close() {
00093 this.getDbManager().disconnect();
00094 }
00095
00105 protected ResultSet executeQuery(String queryPattern, String parameters[]) {
00106
00107
00108 String query = "";
00109 try {
00110 ResultSet result = null;
00111 query = this.buildQuery(queryPattern, parameters);
00112
00113
00114
00115
00116 Connection connection = this.getDbManager().getConnection();
00117 if (connection != null) {
00118 Statement stat = connection.createStatement();
00119 result = stat.executeQuery(query);
00120 }
00121
00122 return result;
00123 }
00124 catch (java.sql.SQLException e) {
00125 LogManager.traceError(LogServices.SERVERSERVICE, new ServiceException("System Exception in query in query manger for:" + queryPattern + " with query : " + query, e));
00126 }
00127 return null;
00128 }
00129
00140 protected ResultSet executeDynamicQuery(String queryPattern, java.util.Hashtable variables) {
00141 String query = "";
00142 try {
00143 ResultSet result = null;
00144 query = this.buildDynamicQuery(queryPattern, variables);
00145 Connection connection = this.getDbManager().getConnection();
00146 if (connection != null) {
00147 Statement stat = connection.createStatement();
00148 result = stat.executeQuery(query);
00149 }
00150 return result;
00151 }
00152 catch (java.sql.SQLException e) {
00153 LogManager.traceError(LogServices.SERVERSERVICE, new ServiceException("System Exception in query in query manger for:" + queryPattern + " with query : " + query, e));
00154 }
00155 return null;
00156 }
00157
00167 protected void executeUpdate(String queryPattern, String parameters[]) throws ServiceException {
00168 String query = "";
00169 try {
00170 query = this.buildQuery(queryPattern, parameters);
00171
00172 Connection connection = this.getDbManager().getConnection();
00173 if (connection != null) {
00174 try {
00175 Statement stat = connection.createStatement();
00176 stat.executeUpdate(query);
00177 stat.close();
00178
00179 } finally {
00180 this.getDbManager().garbageOpenedConnection();;
00181 }
00182 } else {
00183 LogManager.traceError(LogServices.SERVERSERVICE, "Impossible to get a DB connection from the managerDB");
00184 }
00185 } catch (java.sql.SQLException e) {
00186 throw new ServiceException("System Exception in query in query manger for:" + queryPattern + " with query : " + query, e);
00187 }
00188 }
00189
00190
00200 protected void executeDynamicUpdate(String queryPattern, java.util.Hashtable variables) throws ServiceException {
00201 String query = "";
00202 try {
00203 query = this.buildDynamicQuery(queryPattern, variables);
00204 Connection connection = this.getDbManager().getConnection();
00205 try {
00206 if (connection != null) {
00207 Statement stat = connection.createStatement();
00208 stat.executeUpdate(query);
00209 stat.close();
00210 } else {
00211 LogManager.traceError(LogServices.SERVERSERVICE, "Impossible to get a DB connection from the managerDB");
00212 }
00213 } finally {
00214 this.getDbManager().garbageOpenedConnection();;
00215 }
00216 }
00217 catch (java.sql.SQLException e) {
00218 throw new ServiceException("System Exception in query in query manger for:" + queryPattern + " with query : " + query, e);
00219 }
00220 }
00221
00222
00223
00224
00225
00226
00227
00228
00229 protected String buildDynamicQuery(String pattern, java.util.Hashtable variables) {
00230 StringBuffer result = new StringBuffer();
00231 int beginIndex = 0;
00232 int endIndex = 0;
00233 while (endIndex != -1) {
00234 endIndex = pattern.indexOf("${", beginIndex);
00235 if (endIndex == -1)
00236 result.append(pattern.substring(beginIndex, pattern.length()));
00237 else {
00238 result.append(pattern.substring(beginIndex, endIndex));
00239 beginIndex = endIndex;
00240 endIndex = pattern.indexOf("}", beginIndex);
00241 if (endIndex != -1) {
00242 String varName = pattern.substring(beginIndex+2, endIndex);
00243 if (variables.containsKey(varName))
00244 result.append((String)variables.get(varName));
00245 beginIndex = endIndex + 1;
00246
00247 }
00248 }
00249 }
00250 return result.toString();
00251 }
00252
00253
00254
00255
00265 protected String buildQuery(String queryPattern, String parameters[]) {
00266 try {
00267
00268 if (parameters == null) {return queryPattern;}
00269
00270 StringBuffer newQuery = new StringBuffer();
00271 int currentIndex = 0;
00272 int startIndex = 0;
00273 int lastIndex = 0;
00274 String variable;
00275 String value;
00276
00277 for (int i = 0; i < parameters.length; i++) {
00278 currentIndex = queryPattern.indexOf(Separator, startIndex);
00279
00280 if (queryPattern.charAt(currentIndex+1) == Separator) {
00281 currentIndex +=1;
00282 }
00283 lastIndex = queryPattern.indexOf(Separator, currentIndex+1);
00284 variable = queryPattern.substring(currentIndex+1, lastIndex);
00285
00286 if (variable.equals(String.valueOf(i))) {
00287
00288 newQuery.append(queryPattern.substring(startIndex, currentIndex));
00289 value = parameters[i];
00290 if ((value == null)) {
00291 newQuery.append("NULL");
00292 } else {
00293
00294 newQuery.append(parameters[i]);
00295
00296 }
00297 startIndex = lastIndex + 1;
00298
00299 if ((startIndex < queryPattern.length()) && (queryPattern.charAt(startIndex) == Separator)) {
00300 startIndex +=1;
00301 newQuery.append(Separator);
00302 }
00303 } else {
00304 throw new ServiceException ("Parsing error in query : " + queryPattern);
00305 }
00306 }
00307
00308 newQuery.append(queryPattern.substring(startIndex));
00309 return newQuery.toString();
00310 }
00311 catch (Exception e) {
00312 LogManager.traceError(LogServices.SERVERSERVICE, e);
00313 }
00314 return null;
00315 }
00316
00317
00318
00319
00320 protected String makeSQLList (String[] items) {
00321 if (items == null)
00322 return "";
00323 StringBuffer buffer = new StringBuffer("(");
00324 for (int i = 0; i < items.length; i++) {
00325
00326 buffer.append((String)items[i]);
00327
00328 if (i < items.length - 1)
00329 buffer.append(",");
00330 }
00331 buffer.append(")");
00332 return buffer.toString();
00333 }
00334
00339 public ManagerDB getDbManager() {
00340 if(dbManager==null) dbManager = ManagerDB.getManager();
00341 return dbManager;
00342 }
00343
00344 }
00345