1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 23 package stats; 24 25 import java.sql.Connection; 26 import java.sql.DriverManager; 27 import java.sql.ResultSet; 28 import java.sql.ResultSetMetaData; 29 import java.sql.Statement; 30 import java.util.Enumeration; 31 import java.util.Hashtable; 32 import java.util.StringTokenizer; 33 import java.util.Vector; 34 35 /** 36 * 37 */ 38 public class SQLExecution { 39 40 protected Connection mConnection = null; 41 protected Statement mStatement = null; 42 protected String mJdbcClass = null; 43 protected String mDbURL = null; 44 protected String mUser = null; 45 protected String mPassword = null; 46 protected boolean m_bConnectionOpen = false; 47 protected boolean m_bDebug = false; 48 49 50 /** Creates a new instance of SQLExecution 51 * @param jdbcClass The jdbc class for the connection. 52 * @param dbUrl The url of the database. 53 * @param user The user for connecting the database. 54 * @param password The password of throws user. 55 */ SQLExecution(String jdbcClass, String dbUrl, String user, String password)56 public SQLExecution(String jdbcClass, String dbUrl, String user, String password) { 57 mJdbcClass = jdbcClass; 58 mUser = user; 59 mPassword = password; 60 mDbURL = dbUrl; 61 } 62 63 /** Creates a new instance of SQLExecution with additional debug output. 64 * @param jdbcClass The jdbc class for the connection. 65 * @param dbUrl The url of the database. 66 * @param user The user for connecting the database. 67 * @param password The password of throws user. 68 * @param debug Write debug information, if true. 69 */ SQLExecution(String jdbcClass, String dbUrl, String user, String password, boolean debug)70 public SQLExecution(String jdbcClass, String dbUrl, String user, String password, boolean debug) { 71 mJdbcClass = jdbcClass; 72 mUser = user; 73 mPassword = password; 74 mDbURL = dbUrl; 75 m_bDebug = debug; 76 } 77 78 /** 79 * Open a connection to the DataBase 80 * @return True, if no error occurred. 81 */ openConnection()82 public boolean openConnection() { 83 if(m_bConnectionOpen) return true; 84 try { 85 Class.forName(mJdbcClass); 86 } catch (ClassNotFoundException e) { 87 System.err.println("Couldn't find jdbc driver : " + e.getMessage()); 88 return false; 89 } 90 91 try { 92 // establish database connection 93 mConnection = DriverManager.getConnection( 94 mDbURL, mUser, mPassword); 95 mStatement = mConnection.createStatement(); 96 } 97 catch(java.sql.SQLException e) { 98 System.err.println("Couldn't establish a connection: " + e.getMessage()); 99 return false; 100 } 101 m_bConnectionOpen = true; 102 return true; 103 } 104 105 /** 106 * Close the connection to the DataBase 107 * @return True, if no error occurred. 108 */ closeConnection()109 public boolean closeConnection() { 110 if (!m_bConnectionOpen) return true; 111 try { 112 // close database connection 113 mStatement.close(); 114 mConnection.close(); 115 } 116 catch(java.sql.SQLException e) { 117 System.err.println("Couldn't close the connection: " + e.getMessage()); 118 return false; 119 } 120 m_bConnectionOpen = false; 121 return true; 122 } 123 124 /** 125 * Execute an sql command. 126 * @param command The command to execute. 127 * @param sqlInput Input values for the command. 128 * @param sqlOutput The results of the command are put in this Hashtable. 129 * @return True, if no error occurred. 130 */ executeSQLCommand(String command, Hashtable sqlInput, Hashtable sqlOutput)131 public boolean executeSQLCommand(String command, Hashtable sqlInput, Hashtable sqlOutput) 132 throws IllegalArgumentException { 133 return executeSQLCommand(command, sqlInput, sqlOutput, false); 134 } 135 136 /** 137 * Execute an sql command. 138 * @param command The command to execute. 139 * @param sqlInput Input values for the command. 140 * @param sqlOutput The results of the command are put in this Hashtable. 141 * @param mergeOutputIntoInput The output of the result is put into the 142 * sqlInput Hashtable. 143 * @return True, if no error occurred. 144 */ executeSQLCommand(String command, Hashtable sqlInput, Hashtable sqlOutput, boolean mergeOutputIntoInput)145 public boolean executeSQLCommand(String command, Hashtable sqlInput, Hashtable sqlOutput, boolean mergeOutputIntoInput) 146 throws IllegalArgumentException { 147 if (sqlOutput == null) { 148 sqlOutput = new Hashtable(); 149 // this has to be true, so the user of this method gets a return 150 mergeOutputIntoInput = true; 151 if (sqlInput == null) { 152 System.out.println("sqlInput and sqlOutput are null: cannot return the results of the sql command."); 153 return false; 154 } 155 } 156 Vector sqlCommand = new Vector(); 157 sqlCommand.add(""); 158 boolean update = false; 159 // synchronize all "$varname" occurrences in the command string with 160 // values from input 161 StringTokenizer token = new StringTokenizer(command, " "); 162 while (token.hasMoreTokens()) { 163 String originalKey = token.nextToken(); 164 // search for keys, beginning with "$" 165 int index = originalKey.indexOf('$'); 166 if (index != -1) { 167 // found key 168 String pre = ""; 169 pre = originalKey.substring(0,index); 170 // generate key: remove "$" 171 String key = originalKey.substring(index+1); 172 String post = ""; 173 // remove any endings the key might have 174 while (key.endsWith(",") || key.endsWith("\'") || 175 key.endsWith(";") || key.endsWith(")") || 176 key.endsWith("\"")) { 177 post = key.substring(key.length()-1) + post; 178 key = key.substring(0, key.length()-1); 179 } 180 // look for key in the Hashtable 181 if (sqlInput.containsKey(key)) { 182 // is there a value for the key? 183 Object in = sqlInput.get(key); 184 if (in instanceof String[]) { 185 // value is a String[] 186 String[]vals = (String[])in; 187 if (vals.length != sqlCommand.size() && sqlCommand.size() > 1) { 188 // size of this array and previous array(s) does not match 189 throw new IllegalArgumentException("The key '" + key + "' in command \n'" 190 + command + "'\n has not the same value count as the keys before."); 191 } 192 // build up the commands 193 boolean addNewVals = (sqlCommand.size() == 1); 194 for (int i=0; i<vals.length; i++) { 195 String value = checkForQuotationMarks(vals[i]); 196 // add the values 197 if (addNewVals && i!=0) { 198 // all values until now were of type String, not String[], so now new values have to be added. 199 sqlCommand.add(i, (String)sqlCommand.get(0) + " " + pre + value + post); 200 } 201 else { 202 // we already have vals.length commands (or are at the first command), so just add. 203 sqlCommand.set(i, (String)sqlCommand.get(i) + " " + pre + value + post); 204 } 205 } 206 } 207 else { 208 // value is a String: no other possibility 209 String value = checkForQuotationMarks((String)sqlInput.get(key)); 210 for (int i=0; i<sqlCommand.size(); i++) { 211 sqlCommand.set(i, (String)sqlCommand.get(i) + " " + pre + value + post); 212 } 213 } 214 } 215 else { 216 // no input value found 217 throw new IllegalArgumentException ("The key '" + key + "' in command \n'" 218 + command + "'\n does not exist in the input values."); 219 } 220 } 221 else { 222 // token is not a key, just add it 223 for (int i=0; i<sqlCommand.size(); i++) 224 sqlCommand.set(i, (String)sqlCommand.get(i) + " " + originalKey); 225 if (originalKey.equalsIgnoreCase("update") || 226 originalKey.equalsIgnoreCase("delete") || 227 originalKey.equalsIgnoreCase("insert")) { 228 update = true; 229 } 230 231 } 232 } 233 for (int i=0;i<sqlCommand.size(); i++) { 234 execute((String)sqlCommand.get(i), sqlOutput, update); 235 // merge output with input 236 if (!update && mergeOutputIntoInput) { 237 Enumeration keys = sqlOutput.keys(); 238 while(keys.hasMoreElements()) { 239 String key = (String)keys.nextElement(); 240 String[]val = (String[])sqlOutput.get(key); 241 if (val != null && val.length != 0) { 242 if (val.length == 1) 243 sqlInput.put(key, val[0]); 244 else 245 sqlInput.put(key, val); 246 } 247 } 248 } 249 } 250 if (!update && sqlOutput == null) 251 return false; 252 return true; 253 } 254 255 /** 256 * Execute any SQL command. 257 * @param command The command. 258 * @param update If true, it is a update/alter command instead of an select 259 * command 260 * @return A Hashtable with the result. 261 */ execute(String command, Hashtable output, boolean update)262 private void execute(String command, Hashtable output, boolean update) { 263 if (m_bDebug) 264 System.out.println("Debug - SQLExecution - execute Command: " + command); 265 try { 266 if (update) { 267 // make an update 268 mStatement.executeUpdate(command); 269 } 270 else { 271 // make a select: collect the result 272 ResultSet sqlResult = mStatement.executeQuery(command); 273 ResultSetMetaData sqlRSMeta = sqlResult.getMetaData(); 274 int columnCount = sqlRSMeta.getColumnCount(); 275 String[] columnNames = new String[columnCount]; 276 int countRows = 0; 277 boolean goThroughRowsTheFirstTime = true; 278 for(int i=1; i<=columnCount; i++) { 279 columnNames[i-1] = sqlRSMeta.getColumnName(i); 280 // initialize output 281 Vector v = new Vector(); 282 283 sqlResult.beforeFirst(); 284 while (sqlResult.next()) { 285 String value = sqlResult.getString(i); 286 v.add(value); 287 // the first time: count rows 288 if (goThroughRowsTheFirstTime) 289 countRows++; 290 } 291 // rows are counted 292 if (goThroughRowsTheFirstTime) 293 goThroughRowsTheFirstTime = false; 294 295 // put result in output Hashtable 296 String[]s = new String[countRows]; 297 s = (String[])v.toArray(s); 298 output.put(columnNames[i-1], s); 299 if (m_bDebug) { 300 if (i == 1) { 301 System.out.print("Debug - SQLExecution - Command returns: "); 302 System.out.print("row: " + columnNames[i-1] + " vals: "); 303 } 304 for (int j=0; j<s.length; j++) 305 System.out.print(s[j] + " "); 306 if (i == columnCount - 1) 307 System.out.println(); 308 } 309 } 310 } 311 } 312 catch (java.sql.SQLException e) { 313 e.printStackTrace(); 314 } 315 } 316 317 /** 318 * Replace <"> with <''> in the value Strings, or the command will fail. 319 * @param checkString The String that is checked: a part of the command 320 * @return The String, cleared of all quotation marks. 321 */ checkForQuotationMarks(String checkString)322 private String checkForQuotationMarks(String checkString) { 323 String returnString = checkString; 324 int quotIndex = 0; 325 while ((quotIndex = returnString.indexOf('\"')) != -1) { 326 String firstHalf = returnString.substring(0, quotIndex); 327 String secondHalf = returnString.substring(quotIndex+1); 328 returnString = firstHalf + "\'\'" + secondHalf; 329 } 330 return returnString; 331 } 332 333 } 334