qadevOOo/runner/convwatch/DBHelper.java

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