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.io.PrintWriter;
  20 import java.sql.Connection;
  21 import java.sql.DriverManager;
  22 import java.sql.ResultSet;
  23 import java.sql.SQLException;
  24 import java.sql.Statement;
  25
  26 import org.apache.http.MethodNotSupportedException;
  27 import org.apache.log4j.Logger;
  28
  29 import eu.etaxonomy.cdm.database.ICdmDataSource;
  30 import eu.etaxonomy.cdm.database.update.DatabaseTypeNotSupportedException;
  31
  32
  33 /**
  34  *
  35  *  Creates Cursors from extern relational DB.
  36  *  Used only for developpers convienence hence undocumented.
  37  *  You may create input cursors in any other way you want
  38  *  @author Andreas M&uuml;ller
  39  */
  40 public class Source {
  41         static Logger logger = Logger.getLogger(Source.class);
  42
  43 /* ************ Constants **************************************/
  44     //Mode
  45         private final static boolean DEBUG_MODE = false;
  46     private final static boolean DEBUG_LOG_WRITER = false;
  47
  48     //DB info
  49 //      public final static String SQL_SERVER_2000 = "SQLServer2000";
  50         public final static String SQL_SERVER_2005 = "SQLServer2005";
  51         public final static String SQL_SERVER_2008 = "SQLServer2008";
  52         public final static String ACCESS = "Access";
  53         public final static String EXCEL = "Excel";
  54         public final static String ODDBC = "ODBC";
  55         public final static String ORACLE = "Oracle";
  56         public final static String DB2 = "DB2";
  57
  58         //coursor mode
  59         public final static String SELECT_DIRECT = "direct";
  60         public final static String SELECT_CURSOR = "cursor";
  61
  62     //driver class
  63 //    private static String clsSQLServer2000 = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  64     private static String clsSQLServer2005 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  65     private static String clsSQLServer2008 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  66     private static String clsODBC = "sun.jdbc.odbc.JdbcOdbcDriver";
  67     private static String clsOracle = "oracle.jdbc.driver.OracleDriver";
  68     private static String clsDB2 = "COM.ibm.db2.jdbc.net.DB2Driver";
  69     private static String clsSQLServerDdtek = "com.ddtek.jdbc.sqlserver.SQLServerDriver";
  70     private static String clsDefault = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  71
  72     //url
  73 //    private static String urlSQLServer = "jdbc:microsoft:sqlserver://";
  74     private static String urlSQLServer2005 = "jdbc:sqlserver://";
  75     private static String urlSQLServer2008 = "jdbc:sqlserver://";
  76     private static String urlDB2 = "jdbc:db2://";
  77     private static String urlOracle = "jdbc:oracle:thin:@:1243:";
  78     private static String urlDataDirectSQLServer = "jdbc:datadirect:sqlserver://";
  79     private static String urlODBC = "jdbc:odbc:";
  80     // FIXME this default is not acceptable
  81     private static String urlDefault = "jdbc:microsoft:sqlserver://LAPI:1433;DatabaseName=studienarbeit;SelectMethod=direct";
  82
  83
  84 /* *************** VARIABLES *******************************/
  85     private Connection mConn;
  86     private Statement mStmt;
  87     private String mQuery;
  88     private String mUrl = null;
  89     private String mDb = null;
  90     private int mPort = 1433; //default port TODO 2 currently only for SQLServer, needs to be implemented also for othe DBMS
  91     private String mDbms = null;
  92     private String mServer = null;
  93     private boolean isCursor;
  94     private boolean connExist = false; //does a Connection exist?
  95     private String mUserName;
  96     private String mPwd;
  97
  98         private boolean doLog = false;
  99
 100
 101         private static String userNameDefault = "sa"; //default user
 102     private static String pwdDefault = "sa"; //default PWD
 103
 104
 105 /* ******************** CONSTRUCTORS **********************************/
 106
 107
 108     /**
 109      * Creates a source and sets the according variables
 110      * @param dbms name of database management system (e.g. "SQLServer", "Access", "Excel", "Oracle"
 111          * @param server name of server, if dbms is ODBC, this must be the ODBC name
 112          * @param db name of database
 113         */
 114     public Source(String dbms, String server, String db){
 115         mDbms = dbms;
 116         mServer = server;
 117         mDb = db;
 118         this.mPwd = pwdDefault;
 119         this.mUserName = userNameDefault;
 120     }
 121
 122     /**
 123      * Creates a source and sets the query string
 124      * @param dbms name of database management system (e.g. "SQLServer", "Access", "Excel", "Oracle"
 125          * @param server name of server, if dbms is ODBC, this must be the ODBC name
 126          * @param db name of database
 127      * @param query the query
 128      */
 129     public Source(String dbms, String server, String db, String query){
 130         this(dbms, server, db);
 131         this.setQuery(query);
 132     }
 133
 134     /**
 135     * Creates a source and sets the parameter.
 136      * @param cursor access mode, if true 'cursor', if false 'direct'
 137     */
 138     public Source(String dbms, String server, String db, boolean cursor)   {
 139         this(dbms, server, db);
 140         isCursor = cursor;
 141     }
 142
 143
 144     /**
 145     * Creates a source and sets the parameter. If port is -1 the databases default port is used.
 146     * @param dbms name of database management system (e.g. "SQLServer", "Access", "Excel", "Oracle"
 147     * @param server name of server, if dbms is ODBC, this must be the ODBC name
 148     * @param db name of database
 149     * @param port the databases port (-1: use default port)
 150     */
 151     public Source(String dbms, String server, String db, int port) {
 152         this(dbms, server, db);
 153         if (port != -1){
 154                 this.setPort(port);
 155         };
 156     }
 157
 158     /**
 159      * Creates a source with parameters of a ICdmDataSource instance
 160      *
 161      * @param cdmDataSource
 162      */
 163     public Source(ICdmDataSource cdmDataSource){
 164         mDbms = cdmDataSource.getDatabaseType().getName();
 165         mServer = cdmDataSource.getServer();
 166         mDb = cdmDataSource.getDatabase();
 167         mPwd = cdmDataSource.getPassword();
 168         mUserName = cdmDataSource.getUsername();
 169         this.setPort(cdmDataSource.getPort());
 170     }
 171
 172 //********************* METHODS *****************************************************/
 173
 174     //******* RESULTSETS *******************//
 175     /**
 176      * Returns the Resultset for query 'query'.
 177      * Does not change the Sources query-string!!
 178      * @return Resultset for the query.
 179      */
 180     public ResultSet getResultSet (String query){
 181         ResultSet rs;
 182         try {
 183             this.getConnection(); //establish connection
 184                 if (query == null){
 185                         return null;
 186                 }
 187             mStmt = mConn.createStatement();
 188             rs = mStmt.executeQuery(query);
 189             return rs;
 190         }catch(Exception e){
 191             logger.error("Problems when creating Resultset for query \n  " + query + " \n" + "Exception: " + e);
 192             throw new RuntimeException(e);
 193         }
 194     }
 195
 196     //******* INSERT, UPDATE, DELETE *******************//
 197     /**
 198      * Executes an insert, update or delete statement.
 199      * Returns the number of rows changed or -1 if updatedStatement was 0 or and error occurred.
 200      * Does not change the Sources query-string!!
 201      * @return Resultset for the query.
 202      */
 203     public int update (String updateStatement){
 204         int result;
 205         try {
 206             this.getConnection(); //establish connection
 207                 if (updateStatement == null){
 208                         return -1;
 209                 }
 210             mStmt = mConn.createStatement();
 211             result = mStmt.executeUpdate(updateStatement);
 212             return result;
 213         }catch(SQLException e){
 214             logger.error("Problems when creating Resultset for query \n  " + updateStatement + " \n" + "Exception: " + e);
 215             return -1;
 216         }
 217     }
 218
 219
 220     /**
 221      * Returns the Resultset for Sources query.
 222      * @return Resultset for the Sources query
 223      */
 224     public ResultSet getResultSet (){
 225         return getResultSet(mQuery);
 226     }
 227
 228
 229     // ***************CONNECTION *************************************//
 230     /**
 231      * Returns the connection.
 232      * @return the Sources connection
 233      */
 234     public Connection getConnection(){
 235         try {
 236                         if (this.connExist == false){
 237                                 if (mConn != null){
 238                                         mConn.close();
 239                                 }
 240                                 this.makeConnection() ;
 241                 }
 242                         return this.mConn;
 243                 } catch (SourceConnectionException e) {
 244                         //TODO error weitergabe des Fehlers nach oben?
 245                         logger.error("Error when establishing Connection");
 246                         return null;
 247                 } catch (SQLException e) {
 248                         logger.error("SQLException in getConnection.");
 249                         return null;
 250                 }
 251     }
 252
 253
 254     /**
 255      * Makes the connection.
 256      * @return true if connection created
 257      * @throws SourceConnectionException
 258      */
 259     private boolean makeConnection()throws SourceConnectionException {
 260         if (doLog ){
 261                 DriverManager.setLogWriter(new PrintWriter(System.out));
 262         }
 263         try {
 264                         if (makeConnectionString() == false){
 265                                 throw new SourceConnectionException ("Error in Connection String");
 266                         }
 267                         if (mDbms.equalsIgnoreCase(ODDBC) ){
 268                                 //not necessarily limited to ODBC
 269                                 java.util.Properties prop = new java.util.Properties();
 270 //                          prop.put("charSet", "Big5");
 271                             prop.put("user", mUserName);
 272                             prop.put("password", mPwd);
 273 //                          DriverManager.setLogWriter(new PrintWriter(System.out));
 274                             mConn = DriverManager.getConnection(mUrl, prop);
 275                         }else{
 276                                 mConn = DriverManager.getConnection(mUrl, mUserName, mPwd);
 277                         }
 278
 279
 280
 281                         mConn.setCatalog(mDb);  //
 282                         logger.info("Connected to " + mConn.getCatalog());
 283                         mStmt = mConn.createStatement();
 284                         this.connExist = true;
 285                         return true;
 286                 }catch (SQLException e){
 287             logger.error("Problems when trying to open the database !!!\n" +
 288                     "URL: " + mUrl  + "\n" +
 289                     "Exception: " + e);
 290             throw new SourceConnectionException ();
 291         }
 292     }
 293
 294     /**
 295      * Makes the connection string
 296      * @return false if ClassNotFoundException, else true
 297      */
 298     private boolean makeConnectionString(){
 299         String selectMethod;
 300         String server;
 301
 302         if (isCursor)
 303                 selectMethod = SELECT_CURSOR;
 304             else
 305                 selectMethod = SELECT_DIRECT;
 306             try{
 307                 if (DEBUG_LOG_WRITER) DriverManager.setLogWriter((new PrintWriter(System.out)));
 308 //              if (mDbms.equalsIgnoreCase(SQL_SERVER_2000)) {
 309 //                  Class.forName(clsSQLServer2000);
 310 //                  server = mServer + ":" + mPort;
 311 //                  mUrl = urlSQLServer + server + ";DataBase=" + mDb + ";SelectMethod="+ selectMethod;
 312 //              }
 313                 else if (mDbms.equalsIgnoreCase(SQL_SERVER_2005)) {
 314                     Class.forName(clsSQLServer2005);
 315                     server = mServer + ":" + mPort;
 316                     mUrl = urlSQLServer2005 + server + ";databaseName=" + mDb +";SelectMethod="+ selectMethod;
 317                 }
 318                 else if (mDbms.equalsIgnoreCase(SQL_SERVER_2008)) {
 319                     Class.forName(clsSQLServer2008);
 320                     server = mServer + ":" + mPort;
 321                     mUrl = urlSQLServer2008 + server + ";databaseName=" + mDb +";SelectMethod="+ selectMethod;
 322                 }
 323                 else if (mDbms.equalsIgnoreCase(ACCESS)) {
 324                         Class.forName(clsODBC);
 325
 326                         //mDb must be the file path
 327                         mUrl = urlODBC + "Driver={Microsoft Access Driver (*.mdb)};DBQ=";
 328                         mUrl += mDb.trim() + ";DriverID=22;READONLY=false}";
 329                 }
 330                 else if (mDbms.equalsIgnoreCase(EXCEL)) {
 331                     Class.forName(clsODBC);
 332                     mUrl = urlODBC + "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=";
 333                     mUrl += mDb.trim() + ";DriverID=22;READONLY=false";
 334                 }
 335                 else if (mDbms.equalsIgnoreCase(ODDBC)) {
 336                     //mDb must be the System DNS name
 337                         Class.forName(clsODBC);
 338                     mUrl = urlODBC + mDb ;
 339                 }
 340                 else if (mDbms.equalsIgnoreCase(ORACLE)) {
 341                     Class.forName(clsOracle);
 342                     mUrl = urlOracle + mDb ;
 343                 }
 344                 else if (mDbms.equalsIgnoreCase(DB2)) {
 345                     Class.forName(clsDB2);
 346                     mUrl = urlDB2 + mDb;
 347                 }
 348                 else if (mDbms.equalsIgnoreCase("SQLServerDdtek")) {
 349                      Class.forName(clsSQLServerDdtek);
 350                      mUrl = urlDataDirectSQLServer + mServer;
 351                  }
 352                 else {
 353                     Class.forName(clsSQLServer2005);
 354                     mUrl = urlDefault;
 355                 }
 356                 logger.debug("Connection String: " + mUrl);
 357                 return true;
 358             }catch (ClassNotFoundException e){
 359                 logger.error("Datenbank-Treiber-Klasse konnte nicht geladen werden\n" + "Exception: " + e.toString());
 360                 return false;
 361             }
 362     }
 363
 364
 365 /* ******************** SETTER *************************************/
 366
 367     /**
 368      * @param query
 369      */
 370     public Source setQuery (String query){
 371         mQuery = query;
 372         return this;
 373     }
 374
 375     /**
 376      * Sets the username.
 377      * @param userName
 378      */
 379     public Source setUsername (String userName){
 380         if (userName == null){
 381                 this.mUserName = userNameDefault;
 382         }else{
 383                 this.mUserName = userName;
 384         }
 385         this.connExist = false;
 386                 return this;
 387     }
 388
 389     /**
 390      * Sets the password.
 391      * @param pwd
 392      */
 393     public Source setPassword (String pwd){
 394         if (pwd == null){
 395                 this.mPwd = pwdDefault;
 396         }else{
 397                 this.mPwd = pwd;
 398         }
 399         this.connExist = false;
 400                 return this;
 401         }
 402
 403     /**
 404      * Sets the username and password.
 405      * @param userName
 406      * @param pwd
 407      */
 408     public Source setUserAndPwd (String userName, String pwd){
 409         setUsername(userName);
 410         setPassword(pwd);
 411         return this;
 412         }
 413
 414     /**
 415      * Sets the port.
 416      * @param userName
 417      * @param pwd
 418      */
 419     public Source setPort (int port){
 420         this.mPort = port;
 421                 this.connExist = false;
 422                 return this;
 423         }
 424
 425     public String getDatabase(){
 426         return mDb;
 427     }
 428
 429     public String getServer(){
 430         return mServer;
 431     }
 432
 433
 434     public boolean isDoLog() {
 435                 return doLog;
 436         }
 437
 438         public void setDoLog(boolean doLog) {
 439                 this.doLog = doLog;
 440         }
 441
 442     /**
 443      * Checks if an attribute exists in the database schema. At the moment only supported
 444      * for SQL Server.
 445      * TODO implement for others.
 446      * @param tableName
 447      * @param dbAttribute
 448      * @return
 449      * @throws MethodNotSupportedException
 450      */
 451     public boolean checkColumnExists(String tableName, String dbAttribute) throws DatabaseTypeNotSupportedException{
 452         if (mDbms.equalsIgnoreCase(SQL_SERVER_2005)|| mDbms.equalsIgnoreCase(SQL_SERVER_2008) ){
 453                 String strQuery = "SELECT  Count(t.id) as n " +
 454                                 " FROM sysobjects AS t " +
 455                                 " INNER JOIN syscolumns AS c ON t.id = c.id " +
 456                                 " WHERE (t.xtype = 'U') AND " +
 457                                 " (t.name = '" + tableName + "') AND " +
 458                                 " (c.name = '" + dbAttribute + "')";
 459                         ResultSet rs = getResultSet(strQuery) ;
 460                         int n;
 461                         try {
 462                                 rs.next();
 463                                 n = rs.getInt("n");
 464                                 return n>0;
 465                         } catch (SQLException e) {
 466                                 e.printStackTrace();
 467                                 return false;
 468                         }
 469         }else{
 470                 throw new DatabaseTypeNotSupportedException("Check column exist is not supported by the database system");
 471         }
 472     }
 473
 474     @Override
 475     public String toString(){
 476         if (mDb != null){
 477                 return mDb;
 478         }else if (mUrl == null){
 479                 return super.toString();
 480         }else{
 481                 return mUrl;
 482         }
 483     }
 484
 485
 486 }