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 
24 package convwatch;
25 
26 import java.sql.Connection;
27 import java.sql.DriverManager;
28 import java.sql.Statement;
29 import java.sql.ResultSet;
30 import java.sql.SQLException;
31 
32 import java.lang.Thread;
33 import java.util.StringTokenizer;
34 
35 class ShareConnection
36 {
37     private Connection m_aConnection = null;
38     public ShareConnection()
39         {}
40 
41     public Connection getConnection()
42         {
43             if (m_aConnection == null)
44             {
45                 try
46                 {
47                     m_aConnection = DBHelper.getMySQLConnection();
48                 }
49                 catch(java.sql.SQLException e)
50                 {
51                     GlobalLogWriter.get().println("DB: ERROR: can't connect to DB.");
52                     m_aConnection = null;
53                 }
54             }
55             return m_aConnection;
56         }
57 }
58 
59     class MySQLThread extends Thread
60     {
61         Connection m_aCon = null;
62         String m_sSQL;
63         public MySQLThread(Connection _aCon, String _sSQL)
64             {
65                 m_aCon = _aCon;
66                 m_sSQL = _sSQL;
67             }
68 
69         public void run()
70             {
71                 Statement oStmt = null;
72                 if (m_aCon == null)
73                 {
74                     GlobalLogWriter.get().println("DB: ERROR: in ExecSQL, connection not established.");
75                     return;
76                 }
77 
78                 // Connection oCon = null;
79                 try
80                 {
81                     // oCon = getMySQLConnection();
82                     oStmt = m_aCon.createStatement();
83 
84                     GlobalLogWriter.get().println("DB: " + m_sSQL);
85                     /* ResultSet oResult = */
86                     oStmt.executeUpdate(m_sSQL);
87                 }
88                 catch(Exception e)
89                 {
90                     GlobalLogWriter.get().println("DB: Couldn't execute sql string '" + m_sSQL + "'");
91                     GlobalLogWriter.get().println("DB: Reason: " + e.getMessage());
92                 }
93             }
94     }
95 
96 public class DBHelper
97 {
98     /**
99      * This method inserts given values into<br>
100      * the table 'states'
101      * @param values a set of comma separated values to be inserted
102      */
103 
104     public void SQLinsertValues(Connection _aCon, String _sTableName, String value_names, String values)
105         {
106             if (_aCon == null)
107             {
108                 GlobalLogWriter.get().println("DB: ERROR: in SQLinsertValues, connection not established.");
109                 return;
110             }
111 
112             // String aInsertStr = "";
113             //
114             // aInsertStr = "INSERT INTO " + _sTableName + " (" + value_names + " ) VALUES (" + values + ")";
115             // ExecSQL(_aCon, aInsertStr);
116             StringBuffer aInsertStr = new StringBuffer();
117 
118             aInsertStr.append( "INSERT INTO " ) . append( _sTableName );
119             aInsertStr.append( " (").append( value_names ).append ( ")" );
120             aInsertStr.append(" VALUES (" ).append( values ).append( ")" );
121             ExecSQL(_aCon, aInsertStr.toString() );
122         }
123 
124     public void SQLupdateValue(Connection _aCon, String _sTableName, String _sSet, String _sWhere)
125         {
126             if (_aCon == null)
127             {
128                 GlobalLogWriter.get().println("DB: ERROR: in SQLinsertValues, connection not established.");
129                 return;
130             }
131 
132             // String aUpdateStr = "";
133             //
134             // aUpdateStr = "UPDATE " + _sTableName + " SET " + _sSet + " WHERE " + _sWhere;
135             // ExecSQL( _aCon, aUpdateStr );
136             StringBuffer aUpdateStr = new StringBuffer();
137 
138             aUpdateStr.append( "UPDATE " ).append( _sTableName )
139                 .append( " SET " ).append( _sSet )
140                 .append( " WHERE " ).append( _sWhere );
141             ExecSQL( _aCon, aUpdateStr.toString() );
142         }
143 
144     private static String m_sDBServerName;
145     private static String m_sDBName;
146     private static String m_sDBUser;
147     private static String m_sDBPasswd;
148 
149     protected synchronized void fillDBConnection(String _sInfo)
150         {
151             StringTokenizer aTokenizer = new StringTokenizer(_sInfo,",",false);
152             while (aTokenizer.hasMoreTokens())
153             {
154                 String sPart = aTokenizer.nextToken();
155                 if (sPart.startsWith("db:"))
156                 {
157                     m_sDBName = sPart.substring(3);
158                     // GlobalLogWriter.get().println("DB: source version: " + m_sSourceVersion);
159                 }
160                 else if (sPart.startsWith("user:"))
161                 {
162                     m_sDBUser = sPart.substring(5);
163                 }
164                 else if (sPart.startsWith("passwd:"))
165                 {
166                     m_sDBPasswd = sPart.substring(7);
167                 }
168                 else if (sPart.startsWith("server:"))
169                 {
170                     m_sDBServerName = sPart.substring(7);
171                 }
172             }
173         }
174 
175     /**
176      * This method establishes a Connection<br>
177      * with the database 'module_unit' on jakobus
178      */
179 
180     public static Connection getMySQLConnection() throws SQLException
181         {
182             try
183             {
184                 Class.forName("org.gjt.mm.mysql.Driver");
185                 String sConnection = "jdbc:mysql://" + m_sDBServerName + ":3306/" + m_sDBName;
186                 // Connection mysql = DriverManager.getConnection(
187                 //    "jdbc:mysql://jakobus:3306/jobs_convwatch","admin","admin");
188                 Connection mysql = DriverManager.getConnection(sConnection, m_sDBUser, m_sDBPasswd);
189                 return mysql;
190             }
191             catch (ClassNotFoundException e)
192             {
193                 GlobalLogWriter.get().println("DB: Class not found exception caught: " + e.getMessage());
194                 GlobalLogWriter.get().println("DB: Maybe mysql.jar is not added to the classpath.");
195             }
196             return null;
197         }
198 
199 
200     /**
201      * This method removes all entries of the given<br>
202      * module/platform combination
203      * @param mdl the name of the module, e.g. sal
204      * @param os the name of the platform, e.g. unxsols
205      */
206     // LLA: public static void SQLdeleteValues(Connection _aCon, String _sEnvironment, String _sUnitName, String _sMethodName, String _sCWS, String _sDate)
207     // LLA:     {
208     // LLA:         String sSQL =
209     // LLA:             "DELETE FROM states WHERE " +
210     // LLA:             "     unit=" + DatabaseEntry.Quote(_sUnitName) +
211     // LLA:             " AND pf="   + DatabaseEntry.Quote (_sEnvironment) +
212     // LLA:             " AND meth=" + DatabaseEntry.Quote (_sMethodName) +
213     // LLA:             " AND cws="  + DatabaseEntry.Quote(_sCWS) +
214     // LLA:             " AND dt="   + DatabaseEntry.Quote(_sDate);
215     // LLA:
216     // LLA:         // ExecSQL(_aCon, sSQL);
217     // LLA:     }
218 
219     protected synchronized void ExecSQL(Connection _aCon, String _sSQL)
220             {
221                 MySQLThread aSQLThread = new MySQLThread(_aCon, _sSQL);
222                 aSQLThread.start();
223             }
224 
225 
226 
227     // public static int QueryIntFromSQL(String _sSQL, String _sColumnName, String _sValue)
228     //     {
229     //         boolean bNeedSecondTry = false;
230     //         int nValue = 0;
231     //         do
232     //         {
233     //             try
234     //             {
235     //                 nValue = QueryIntFromSQL(_sSQL, _sColumnName, _sValue);
236     //             }
237     //             catch (ValueNotFoundException e)
238     //             {
239     //                 bNeedSecondTry = true;
240     //                 String sSQL = "INSERT INTO " + _sTable + "(" + _sColumnName + ") VALUES (" + _sValue + ")";
241     //                 ExecSQL(sSQL);
242     //             }
243     //         } while (bNeedSecondTry);
244     //         return nValue;
245     //     }
246 
247     public int QueryIntFromSQL(Connection _aCon, String _sSQL, String _sColumnName)
248         throws ValueNotFoundException
249         {
250             Statement oStmt = null;
251             Connection oCon = null;
252             int nValue = 0;
253             try
254             {
255                 // oCon = getMySQLConnection();
256                 oStmt = _aCon.createStatement();
257 
258                 ResultSet oResult = oStmt.executeQuery(_sSQL);
259                 oResult.next();
260 
261 				try
262                 {
263                     if (_sColumnName.length() == 0)
264                     {
265                         // take the first row value (started with 1)
266                         nValue = oResult.getInt(1);
267                     }
268                     else
269                     {
270                         nValue = oResult.getInt(_sColumnName);
271                     }
272     	            // System.out.println("value: " + String.valueOf(nValue));
273 				}
274 				catch (SQLException e)
275 				{
276 	                String sError = e.getMessage();
277     	            GlobalLogWriter.get().println("DB: Original SQL error: " + sError);
278                     throw new ValueNotFoundException("Cant execute SQL: " + _sSQL);
279 				}
280             }
281             catch(SQLException e)
282             {
283                 String sError = e.getMessage();
284                 GlobalLogWriter.get().println("DB: Couldn't execute sql string " + _sSQL + "\n" + sError);
285             }
286             return nValue;
287         }
288 
289 	public String Quote(String _sToQuote)
290 		{
291             String ts = "'";
292             String ds = "\"";
293             int nQuote = _sToQuote.indexOf(ts);
294             if (nQuote >= 0)
295             {
296                 return ds + _sToQuote + ds;
297             }
298 			return ts + _sToQuote + ts;
299 		}
300 
301 /* default date format in the MySQL DB yyyy-MM-dd */
302     public static String today()
303         {
304             return DateHelper.getDateString("yyyy-MM-dd");
305         }
306 
307     public static final String sComma = ",";
308     public static final String sEqual = "=";
309     public static final String sAND = " AND ";
310 
311 }
312 
313