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      */
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      */
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 occured.
81      */
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 occured.
108      */
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 occured.
130      */
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 occured.
144      */
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" occurences 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      */
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      */
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