qadevOOo/runner/convwatch/DBHelper.java

   1 /*
   2  * This file is part of the LibreOffice project.
   3  *
   4  * This Source Code Form is subject to the terms of the Mozilla Public
   5  * License, v. 2.0. If a copy of the MPL was not distributed with this
   6  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   7  *
   8  * This file incorporates work covered by the following license notice:
   9  *
  10  *   Licensed to the Apache Software Foundation (ASF) under one or more
  11  *   contributor license agreements. See the NOTICE file distributed
  12  *   with this work for additional information regarding copyright
  13  *   ownership. The ASF licenses this file to you under the Apache
  14  *   License, Version 2.0 (the "License"); you may not use this file
  15  *   except in compliance with the License. You may obtain a copy of
  16  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  17  */
  18
  19 package convwatch;
  20
  21 import java.sql.Connection;
  22 import java.sql.DriverManager;
  23 import java.sql.Statement;
  24 import java.sql.ResultSet;
  25 import java.sql.SQLException;
  26
  27 import java.lang.Thread;
  28 import java.util.StringTokenizer;
  29
  30 class ShareConnection
  31 {
  32     private Connection m_aConnection = null;
  33     public ShareConnection()
  34         {}
  35
  36     public Connection getConnection()
  37         {
  38             if (m_aConnection == null)
  39             {
  40                 try
  41                 {
  42                     m_aConnection = DBHelper.getMySQLConnection();
  43                 }
  44                 catch(java.sql.SQLException e)
  45                 {
  46                     GlobalLogWriter.get().println("DB: ERROR: can't connect to DB.");
  47                     m_aConnection = null;
  48                 }
  49             }
  50             return m_aConnection;
  51         }
  52 }
  53
  54     class MySQLThread extends Thread
  55     {
  56         Connection m_aCon = null;
  57         String m_sSQL;
  58         public MySQLThread(Connection _aCon, String _sSQL)
  59             {
  60                 m_aCon = _aCon;
  61                 m_sSQL = _sSQL;
  62             }
  63
  64         public void run()
  65             {
  66                 Statement oStmt = null;
  67                 if (m_aCon == null)
  68                 {
  69                     GlobalLogWriter.get().println("DB: ERROR: in ExecSQL, connection not established.");
  70                     return;
  71                 }
  72
  73                 // Connection oCon = null;
  74                 try
  75                 {
  76                     // oCon = getMySQLConnection();
  77                     oStmt = m_aCon.createStatement();
  78
  79                     GlobalLogWriter.get().println("DB: " + m_sSQL);
  80                     /* ResultSet oResult = */
  81                     oStmt.executeUpdate(m_sSQL);
  82                 }
  83                 catch(Exception e)
  84                 {
  85                     GlobalLogWriter.get().println("DB: Couldn't execute sql string '" + m_sSQL + "'");
  86                     GlobalLogWriter.get().println("DB: Reason: " + e.getMessage());
  87                 }
  88             }
  89     }
  90
  91 public class DBHelper
  92 {
  93     /**
  94      * This method inserts given values into<br>
  95      * the table 'states'
  96      * @param values a set of comma separated values to be inserted
  97      */
  98
  99     public void SQLinsertValues(Connection _aCon, String _sTableName, String value_names, String values)
 100         {
 101             if (_aCon == null)
 102             {
 103                 GlobalLogWriter.get().println("DB: ERROR: in SQLinsertValues, connection not established.");
 104                 return;
 105             }
 106
 107             // String aInsertStr = "";
 108             //
 109             // aInsertStr = "INSERT INTO " + _sTableName + " (" + value_names + " ) VALUES (" + values + ")";
 110             // ExecSQL(_aCon, aInsertStr);
 111             StringBuffer aInsertStr = new StringBuffer();
 112
 113             aInsertStr.append( "INSERT INTO " ) . append( _sTableName );
 114             aInsertStr.append( " (").append( value_names ).append ( ")" );
 115             aInsertStr.append(" VALUES (" ).append( values ).append( ")" );
 116             ExecSQL(_aCon, aInsertStr.toString() );
 117         }
 118
 119     public void SQLupdateValue(Connection _aCon, String _sTableName, String _sSet, String _sWhere)
 120         {
 121             if (_aCon == null)
 122             {
 123                 GlobalLogWriter.get().println("DB: ERROR: in SQLinsertValues, connection not established.");
 124                 return;
 125             }
 126
 127             // String aUpdateStr = "";
 128             //
 129             // aUpdateStr = "UPDATE " + _sTableName + " SET " + _sSet + " WHERE " + _sWhere;
 130             // ExecSQL( _aCon, aUpdateStr );
 131             StringBuffer aUpdateStr = new StringBuffer();
 132
 133             aUpdateStr.append( "UPDATE " ).append( _sTableName )
 134                 .append( " SET " ).append( _sSet )
 135                 .append( " WHERE " ).append( _sWhere );
 136             ExecSQL( _aCon, aUpdateStr.toString() );
 137         }
 138
 139     private static String m_sDBServerName;
 140     private static String m_sDBName;
 141     private static String m_sDBUser;
 142     private static String m_sDBPasswd;
 143
 144     protected synchronized void fillDBConnection(String _sInfo)
 145         {
 146             StringTokenizer aTokenizer = new StringTokenizer(_sInfo,",",false);
 147             while (aTokenizer.hasMoreTokens())
 148             {
 149                 String sPart = aTokenizer.nextToken();
 150                 if (sPart.startsWith("db:"))
 151                 {
 152                     m_sDBName = sPart.substring(3);
 153                     // GlobalLogWriter.get().println("DB: source version: " + m_sSourceVersion);
 154                 }
 155                 else if (sPart.startsWith("user:"))
 156                 {
 157                     m_sDBUser = sPart.substring(5);
 158                 }
 159                 else if (sPart.startsWith("passwd:"))
 160                 {
 161                     m_sDBPasswd = sPart.substring(7);
 162                 }
 163                 else if (sPart.startsWith("server:"))
 164                 {
 165                     m_sDBServerName = sPart.substring(7);
 166                 }
 167             }
 168         }
 169
 170     /**
 171      * This method establishes a Connection<br>
 172      * with the database 'module_unit' on jakobus
 173      */
 174
 175     public static Connection getMySQLConnection() throws SQLException
 176         {
 177             try
 178             {
 179                 Class.forName("org.gjt.mm.mysql.Driver");
 180                 String sConnection = "jdbc:mysql://" + m_sDBServerName + ":3306/" + m_sDBName;
 181                 // Connection mysql = DriverManager.getConnection(
 182                 //    "jdbc:mysql://jakobus:3306/jobs_convwatch","admin","admin");
 183                 Connection mysql = DriverManager.getConnection(sConnection, m_sDBUser, m_sDBPasswd);
 184                 return mysql;
 185             }
 186             catch (ClassNotFoundException e)
 187             {
 188                 GlobalLogWriter.get().println("DB: Class not found exception caught: " + e.getMessage());
 189                 GlobalLogWriter.get().println("DB: Maybe mysql.jar is not added to the classpath.");
 190             }
 191             return null;
 192         }
 193
 194     protected synchronized void ExecSQL(Connection _aCon, String _sSQL)
 195             {
 196                 MySQLThread aSQLThread = new MySQLThread(_aCon, _sSQL);
 197                 aSQLThread.start();
 198             }
 199
 200     public int QueryIntFromSQL(Connection _aCon, String _sSQL, String _sColumnName)
 201         throws ValueNotFoundException
 202         {
 203             Statement oStmt = null;
 204             int nValue = 0;
 205             try
 206             {
 207                 // oCon = getMySQLConnection();
 208                 oStmt = _aCon.createStatement();
 209
 210                 ResultSet oResult = oStmt.executeQuery(_sSQL);
 211                 oResult.next();
 212
 213                 try
 214                 {
 215                     if (_sColumnName.length() == 0)
 216                     {
 217                         // take the first row value (started with 1)
 218                         nValue = oResult.getInt(1);
 219                     }
 220                     else
 221                     {
 222                         nValue = oResult.getInt(_sColumnName);
 223                     }
 224                     // System.out.println("value: " + String.valueOf(nValue));
 225                 }
 226                 catch (SQLException e)
 227                 {
 228                     String sError = e.getMessage();
 229                     GlobalLogWriter.get().println("DB: Original SQL error: " + sError);
 230                     throw new ValueNotFoundException("Cant execute SQL: " + _sSQL);
 231                 }
 232             }
 233             catch(SQLException e)
 234             {
 235                 String sError = e.getMessage();
 236                 GlobalLogWriter.get().println("DB: Couldn't execute sql string " + _sSQL + "\n" + sError);
 237             }
 238             return nValue;
 239         }
 240
 241     public String Quote(String _sToQuote)
 242         {
 243             String ts = "'";
 244             String ds = "\"";
 245             int nQuote = _sToQuote.indexOf(ts);
 246             if (nQuote >= 0)
 247             {
 248                 return ds + _sToQuote + ds;
 249             }
 250             return ts + _sToQuote + ts;
 251         }
 252
 253 /* default date format in the MySQL DB yyyy-MM-dd */
 254     public static String today()
 255         {
 256             return DateHelper.getDateString("yyyy-MM-dd");
 257         }
 258
 259     public static final String sComma = ",";
 260     public static final String sEqual = "=";
 261     public static final String sAND = " AND ";
 262
 263 }
 264