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