qadevOOo/runner/convwatch/DBHelper.java

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