cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/common/Source.java

   1 /**
   2 * Copyright (C) 2009 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9
  10 package eu.etaxonomy.cdm.io.common;
  11
  12 /*
  13  * Created on 14.05.2005
  14  * @author Andreas M�ller
  15  * Updated 20.08.2006
  16  */
  17
  18
  19 import java.sql.*;
  20 import java.io.*;
  21
  22 import org.apache.log4j.Logger;
  23
  24 import eu.etaxonomy.cdm.database.ICdmDataSource;
  25
  26
  27 /**
  28  *
  29  *  Creates Cursors from extern relational DB.
  30  *  Used only for developpers convienence hence undocumented.
  31  *  You may create input cursors in any other way you want
  32  *  @author Andreas M&uuml;ller
  33  */
  34 public class Source {
  35         static Logger logger = Logger.getLogger(Source.class);
  36
  37 /* ************ Constants **************************************/
  38     //Mode
  39         private final static boolean DEBUG_MODE = false;
  40     private final static boolean DEBUG_LOG_WRITER = false;
  41
  42     //DB info
  43 //      public final static String SQL_SERVER_2000 = "SQLServer2000";
  44         public final static String SQL_SERVER_2005 = "SQLServer2005";
  45         public final static String SQL_SERVER_2008 = "SQLServer2008";
  46         public final static String ACCESS = "Access";
  47         public final static String EXCEL = "Excel";
  48         public final static String ODDBC = "ODBC";
  49         public final static String ORACLE = "Oracle";
  50         public final static String DB2 = "DB2";
  51
  52         //coursor mode
  53         public final static String SELECT_DIRECT = "direct";
  54         public final static String SELECT_CURSOR = "cursor";
  55
  56     //driver class
  57 //    private static String clsSQLServer2000 = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  58     private static String clsSQLServer2005 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  59     private static String clsSQLServer2008 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  60     private static String clsODBC = "sun.jdbc.odbc.JdbcOdbcDriver";
  61     private static String clsOracle = "oracle.jdbc.driver.OracleDriver";
  62     private static String clsDB2 = "COM.ibm.db2.jdbc.net.DB2Driver";
  63     private static String clsSQLServerDdtek = "com.ddtek.jdbc.sqlserver.SQLServerDriver";
  64     private static String clsDefault = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  65
  66     //url
  67 //    private static String urlSQLServer = "jdbc:microsoft:sqlserver://";
  68     private static String urlSQLServer2005 = "jdbc:sqlserver://";
  69     private static String urlSQLServer2008 = "jdbc:sqlserver://";
  70     private static String urlDB2 = "jdbc:db2://";
  71     private static String urlOracle = "jdbc:oracle:thin:@:1243:";
  72     private static String urlDataDirectSQLServer = "jdbc:datadirect:sqlserver://";
  73     private static String urlODBC = "jdbc:odbc:";
  74     // FIXME this default is not acceptable
  75     private static String urlDefault = "jdbc:microsoft:sqlserver://LAPI:1433;DatabaseName=studienarbeit;SelectMethod=direct";
  76
  77
  78 /* *************** VARIABLES *******************************/
  79     private Connection mConn;
  80     private Statement mStmt;
  81     private String mQuery;
  82     private String mUrl = null;
  83     private String mDb = null;
  84     private int mPort = 1433; //default port TODO 2 derzeit nur f�r SQLServer, m�sste auch f�r andere Datenbanken umgesetzt werden
  85     private String mDbms = null;
  86     private String mServer = null;
  87     private boolean isCursor;
  88     private boolean connExist = false; //does a Connection exist?
  89     private String mUserName;
  90     private String mPwd;
  91
  92     private static String userNameDefault = "sa"; //default user
  93     private static String pwdDefault = "sa"; //default PWD
  94
  95
  96 /* ******************** CONSTRUCTORS **********************************/
  97
  98
  99     /**
 100      * Creates a source and sets the according variables
 101      * @param dbms name of database management system (e.g. "SQLServer", "Access", "Excel", "Oracle"
 102          * @param server name of server, if dbms is ODBC, this must be the ODBC name
 103          * @param db name of database
 104         */
 105     public Source(String dbms, String server, String db){
 106         mDbms = dbms;
 107         mServer = server;
 108         mDb = db;
 109         this.mPwd = pwdDefault;
 110         this.mUserName = userNameDefault;
 111     }
 112
 113     /**
 114      * Creates a source and sets the query string
 115      * @param dbms name of database management system (e.g. "SQLServer", "Access", "Excel", "Oracle"
 116          * @param server name of server, if dbms is ODBC, this must be the ODBC name
 117          * @param db name of database
 118      * @param query the query
 119      */
 120     public Source(String dbms, String server, String db, String query){
 121         this(dbms, server, db);
 122         this.setQuery(query);
 123     }
 124
 125     /**
 126     * Creates a source and sets the parameter.
 127      * @param cursor access mode, if true 'cursor', if false 'direct'
 128     */
 129     public Source(String dbms, String server, String db, boolean cursor)   {
 130         this(dbms, server, db);
 131         isCursor = cursor;
 132     }
 133
 134
 135     /**
 136     * Creates a source and sets the parameter. If port is -1 the databases default port is used.
 137     * @param dbms name of database management system (e.g. "SQLServer", "Access", "Excel", "Oracle"
 138     * @param server name of server, if dbms is ODBC, this must be the ODBC name
 139     * @param db name of database
 140     * @param port the databases port (-1: use default port)
 141     */
 142     public Source(String dbms, String server, String db, int port) {
 143         this(dbms, server, db);
 144         if (port != -1){
 145                 this.setPort(port);
 146         };
 147     }
 148
 149     /**
 150      * Creates a source with parameters of a ICdmDataSource instance
 151      *
 152      * @param cdmDataSource
 153      */
 154     public Source(ICdmDataSource cdmDataSource){
 155         mDbms = cdmDataSource.getDatabaseType().getName();
 156         mServer = cdmDataSource.getServer();
 157         mDb = cdmDataSource.getDatabase();
 158         mPwd = cdmDataSource.getPassword();
 159         mUserName = cdmDataSource.getUsername();
 160         this.setPort(cdmDataSource.getPort());
 161     }
 162
 163 //********************* METHODS *****************************************************/
 164
 165     //******* RESULTSETS *******************//
 166     /**
 167      * Returns the Resultset for query 'query'.
 168      * Does not change the Sources query-string!!
 169      * @return Resultset for the query.
 170      */
 171     public ResultSet getResultSet (String query){
 172         ResultSet rs;
 173         try {
 174             this.getConnection(); //establish connection
 175                 if (query == null){
 176                         return null;
 177                 }
 178             mStmt = mConn.createStatement();
 179             rs = mStmt.executeQuery(query);
 180             return rs;
 181         }catch(SQLException e){
 182             logger.error("Problems when creating Resultset for query \n  " + query + " \n" + "Exception: " + e);
 183             return null;
 184         }
 185     }
 186
 187     //******* INSERT, UPDATE, DELETE *******************//
 188     /**
 189      * Executes an insert, update or delete statement.
 190      * Returns the number of rows changed or -1 if updatedStatement was 0 or and error occurred.
 191      * Does not change the Sources query-string!!
 192      * @return Resultset for the query.
 193      */
 194     public int update (String updateStatement){
 195         int result;
 196         try {
 197             this.getConnection(); //establish connection
 198                 if (updateStatement == null){
 199                         return -1;
 200                 }
 201             mStmt = mConn.createStatement();
 202             result = mStmt.executeUpdate(updateStatement);
 203             return result;
 204         }catch(SQLException e){
 205             logger.error("Problems when creating Resultset for query \n  " + updateStatement + " \n" + "Exception: " + e);
 206             return -1;
 207         }
 208     }
 209
 210
 211     /**
 212      * Returns the Resultset for Sources query.
 213      * @return Resultset for the Sources query
 214      */
 215     public ResultSet getResultSet (){
 216         return getResultSet(mQuery);
 217     }
 218
 219
 220     // ***************CONNECTION *************************************//
 221     /**
 222      * Returns the connection.
 223      * @return the Sources connection
 224      */
 225     public Connection getConnection(){
 226         try {
 227                         if (this.connExist == false){
 228                                 if (mConn != null){
 229                                         mConn.close();
 230                                 }
 231                                 this.makeConnection() ;
 232                 }
 233                         return this.mConn;
 234                 } catch (SourceConnectionException e) {
 235                         //TODO error weitergabe des Fehlers nach oben?
 236                         logger.error("Error when establishing Connection");
 237                         return null;
 238                 } catch (SQLException e) {
 239                         logger.error("SQLException in getConnection.");
 240                         return null;
 241                 }
 242     }
 243
 244
 245     /**
 246      * Makes the connection.
 247      * @return true if connection created
 248      * @throws SourceConnectionException
 249      */
 250     private boolean makeConnection()throws SourceConnectionException {
 251         try {
 252                         if (makeConnectionString() == false){
 253                                 throw new SourceConnectionException ("Error in Connection String");
 254                         }
 255                         mConn = DriverManager.getConnection(mUrl, mUserName, mPwd);
 256                         mConn.setCatalog(mDb);  //
 257                         logger.info("Connected to " + mConn.getCatalog());
 258                         mStmt = mConn.createStatement();
 259                         this.connExist = true;
 260                         return true;
 261                 }catch (SQLException e){
 262             logger.error("Probleme beim �ffnen der Datenbank !!!\n" +
 263                     "URL: " + mUrl  + "\n" +
 264                     "Exception: " + e);
 265             throw new SourceConnectionException ();
 266         }
 267     }
 268
 269     /**
 270      * Makes the connection string
 271      * @return false if ClassNotFoundException, else true
 272      */
 273     private boolean makeConnectionString(){
 274         String selectMethod;
 275         String server;
 276
 277         if (isCursor)
 278                 selectMethod = SELECT_CURSOR;
 279             else
 280                 selectMethod = SELECT_DIRECT;
 281             try{
 282                 if (DEBUG_LOG_WRITER) DriverManager.setLogWriter((new PrintWriter(System.out)));
 283 //              if (mDbms.equalsIgnoreCase(SQL_SERVER_2000)) {
 284 //                  Class.forName(clsSQLServer2000);
 285 //                  server = mServer + ":" + mPort;
 286 //                  mUrl = urlSQLServer + server + ";DataBase=" + mDb + ";SelectMethod="+ selectMethod;
 287 //              }
 288                 else if (mDbms.equalsIgnoreCase(SQL_SERVER_2005)) {
 289                     Class.forName(clsSQLServer2005);
 290                     server = mServer + ":" + mPort;
 291                     mUrl = urlSQLServer2005 + server + ";databaseName=" + mDb +";SelectMethod="+ selectMethod;
 292                 }
 293                 else if (mDbms.equalsIgnoreCase(SQL_SERVER_2008)) {
 294                     Class.forName(clsSQLServer2008);
 295                     server = mServer + ":" + mPort;
 296                     mUrl = urlSQLServer2008 + server + ";databaseName=" + mDb +";SelectMethod="+ selectMethod;
 297                 }
 298                 else if (mDbms.equalsIgnoreCase(ACCESS)) {
 299                     Class.forName(clsODBC);
 300                     mUrl = urlODBC + mDb ;
 301                 }
 302                 else if (mDbms.equalsIgnoreCase(EXCEL)) {
 303                     Class.forName(clsODBC);
 304                     mUrl = urlODBC + mDb ;
 305                 }
 306                 else if (mDbms.equalsIgnoreCase(ODDBC)) {
 307                     Class.forName(clsODBC);
 308                     mUrl = urlODBC + mServer ;
 309                 }
 310                 else if (mDbms.equalsIgnoreCase(ORACLE)) {
 311                     Class.forName(clsOracle);
 312                     mUrl = urlOracle + mDb ;
 313                 }
 314                 else if (mDbms.equalsIgnoreCase(DB2)) {
 315                     Class.forName(clsDB2);
 316                     mUrl = urlDB2 + mDb;
 317                 }
 318                 else if (mDbms.equalsIgnoreCase("SQLServerDdtek")) {
 319                      Class.forName(clsSQLServerDdtek);
 320                      mUrl = urlDataDirectSQLServer + mServer;
 321                  }
 322                 else {
 323                     Class.forName(clsSQLServer2005);
 324                     mUrl = urlDefault;
 325                 }
 326                 logger.debug("Connection String: " + mUrl);
 327                 return true;
 328             }catch (ClassNotFoundException e){
 329                 logger.error("Datenbank-Treiber-Klasse konnte nicht geladen werden\n" + "Exception: " + e.toString());
 330                 return false;
 331             }
 332     }
 333
 334
 335 /* ******************** SETTER *************************************/
 336
 337     /**
 338      * @param query
 339      */
 340     public Source setQuery (String query){
 341         mQuery = query;
 342         return this;
 343     }
 344
 345     /**
 346      * Sets the username.
 347      * @param userName
 348      */
 349     public Source setUsername (String userName){
 350         if (userName == null){
 351                 this.mUserName = userNameDefault;
 352         }else{
 353                 this.mUserName = userName;
 354         }
 355         this.connExist = false;
 356                 return this;
 357     }
 358
 359     /**
 360      * Sets the password.
 361      * @param pwd
 362      */
 363     public Source setPassword (String pwd){
 364         if (pwd == null){
 365                 this.mPwd = pwdDefault;
 366         }else{
 367                 this.mPwd = pwd;
 368         }
 369         this.connExist = false;
 370                 return this;
 371         }
 372
 373     /**
 374      * Sets the username and password.
 375      * @param userName
 376      * @param pwd
 377      */
 378     public Source setUserAndPwd (String userName, String pwd){
 379         setUsername(userName);
 380         setPassword(pwd);
 381         return this;
 382         }
 383
 384     /**
 385      * Sets the port.
 386      * @param userName
 387      * @param pwd
 388      */
 389     public Source setPort (int port){
 390         this.mPort = port;
 391                 this.connExist = false;
 392                 return this;
 393         }
 394
 395     public String getDatabase(){
 396         return mDb;
 397     }
 398
 399     public String getServer(){
 400         return mServer;
 401     }
 402
 403
 404 }