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