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                         throw new RuntimeException("Error when establishing Connection.", e);
 245                 } catch (SQLException e) {
 246                         throw new RuntimeException("SQLException in getConnection.", e);
 247                 }
 248     }
 249
 250
 251     /**
 252      * Makes the connection.
 253      * @return true if connection created
 254      * @throws SourceConnectionException
 255      */
 256     private boolean makeConnection()throws SourceConnectionException {
 257         if (doLog ){
 258                 DriverManager.setLogWriter(new PrintWriter(System.out));
 259         }
 260         try {
 261                         if (makeConnectionString() == false){
 262                                 throw new SourceConnectionException ("Error in Connection String");
 263                         }
 264                         if (mDbms.equalsIgnoreCase(ODDBC) ){
 265                                 //not necessarily limited to ODBC
 266                                 java.util.Properties prop = new java.util.Properties();
 267 //                          prop.put("charSet", "Big5");
 268                             prop.put("user", mUserName);
 269                             prop.put("password", mPwd);
 270 //                          DriverManager.setLogWriter(new PrintWriter(System.out));
 271                             mConn = DriverManager.getConnection(mUrl, prop);
 272                         }else{
 273                                 mConn = DriverManager.getConnection(mUrl, mUserName, mPwd);
 274                         }
 275
 276
 277
 278                         mConn.setCatalog(mDb);  //
 279                         logger.info("Connected to " + mConn.getCatalog());
 280                         mStmt = mConn.createStatement();
 281                         this.connExist = true;
 282                         return true;
 283                 }catch (SQLException e){
 284             logger.error("Problems when trying to open the database !!!\n" +
 285                     "URL: " + mUrl  + "\n" +
 286                     "Exception: " + e);
 287             throw new SourceConnectionException ();
 288         }
 289     }
 290
 291     /**
 292      * Makes the connection string
 293      * @return false if ClassNotFoundException, else true
 294      */
 295     private boolean makeConnectionString(){
 296         String selectMethod;
 297         String server;
 298
 299         if (isCursor)
 300                 selectMethod = SELECT_CURSOR;
 301             else
 302                 selectMethod = SELECT_DIRECT;
 303             try{
 304                 if (DEBUG_LOG_WRITER) DriverManager.setLogWriter((new PrintWriter(System.out)));
 305 //              if (mDbms.equalsIgnoreCase(SQL_SERVER_2000)) {
 306 //                  Class.forName(clsSQLServer2000);
 307 //                  server = mServer + ":" + mPort;
 308 //                  mUrl = urlSQLServer + server + ";DataBase=" + mDb + ";SelectMethod="+ selectMethod;
 309 //              }
 310                 else if (mDbms.equalsIgnoreCase(SQL_SERVER_2005)) {
 311                     Class.forName(clsSQLServer2005);
 312                     server = mServer + ":" + mPort;
 313                     mUrl = urlSQLServer2005 + server + ";databaseName=" + mDb +";SelectMethod="+ selectMethod;
 314                 }
 315                 else if (mDbms.equalsIgnoreCase(SQL_SERVER_2008)) {
 316                     Class.forName(clsSQLServer2008);
 317                     server = mServer + ":" + mPort;
 318                     mUrl = urlSQLServer2008 + server + ";databaseName=" + mDb +";SelectMethod="+ selectMethod;
 319                 }
 320                 else if (mDbms.equalsIgnoreCase(ACCESS)) {
 321                         Class.forName(clsODBC);
 322
 323                         //mDb must be the file path
 324                         mUrl = urlODBC + "Driver={Microsoft Access Driver (*.mdb)};DBQ=";
 325                         mUrl += mDb.trim() + ";DriverID=22;READONLY=false}";
 326                 }
 327                 else if (mDbms.equalsIgnoreCase(EXCEL)) {
 328                     Class.forName(clsODBC);
 329                     mUrl = urlODBC + "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=";
 330                     mUrl += mDb.trim() + ";DriverID=22;READONLY=false";
 331                 }
 332                 else if (mDbms.equalsIgnoreCase(ODDBC)) {
 333                     //mDb must be the System DNS name
 334                         Class.forName(clsODBC);
 335                     mUrl = urlODBC + mDb ;
 336                 }
 337                 else if (mDbms.equalsIgnoreCase(ORACLE)) {
 338                     Class.forName(clsOracle);
 339                     mUrl = urlOracle + mDb ;
 340                 }
 341                 else if (mDbms.equalsIgnoreCase(DB2)) {
 342                     Class.forName(clsDB2);
 343                     mUrl = urlDB2 + mDb;
 344                 }
 345                 else if (mDbms.equalsIgnoreCase("SQLServerDdtek")) {
 346                      Class.forName(clsSQLServerDdtek);
 347                      mUrl = urlDataDirectSQLServer + mServer;
 348                  }
 349                 else {
 350                     Class.forName(clsSQLServer2005);
 351                     mUrl = urlDefault;
 352                 }
 353                 logger.debug("Connection String: " + mUrl);
 354                 return true;
 355             }catch (ClassNotFoundException e){
 356                 logger.error("Datenbank-Treiber-Klasse konnte nicht geladen werden\n" + "Exception: " + e.toString());
 357                 return false;
 358             }
 359     }
 360
 361
 362 /* ******************** SETTER *************************************/
 363
 364     /**
 365      * @param query
 366      */
 367     public Source setQuery (String query){
 368         mQuery = query;
 369         return this;
 370     }
 371
 372     /**
 373      * Sets the username.
 374      * @param userName
 375      */
 376     public Source setUsername (String userName){
 377         if (userName == null){
 378                 this.mUserName = userNameDefault;
 379         }else{
 380                 this.mUserName = userName;
 381         }
 382         this.connExist = false;
 383                 return this;
 384     }
 385
 386     /**
 387      * Sets the password.
 388      * @param pwd
 389      */
 390     public Source setPassword (String pwd){
 391         if (pwd == null){
 392                 this.mPwd = pwdDefault;
 393         }else{
 394                 this.mPwd = pwd;
 395         }
 396         this.connExist = false;
 397                 return this;
 398         }
 399
 400     /**
 401      * Sets the username and password.
 402      * @param userName
 403      * @param pwd
 404      */
 405     public Source setUserAndPwd (String userName, String pwd){
 406         setUsername(userName);
 407         setPassword(pwd);
 408         return this;
 409         }
 410
 411     /**
 412      * Sets the port.
 413      * @param userName
 414      * @param pwd
 415      */
 416     public Source setPort (int port){
 417         this.mPort = port;
 418                 this.connExist = false;
 419                 return this;
 420         }
 421
 422     public String getDatabase(){
 423         return mDb;
 424     }
 425
 426     public String getServer(){
 427         return mServer;
 428     }
 429
 430
 431     public boolean isDoLog() {
 432                 return doLog;
 433         }
 434
 435         public void setDoLog(boolean doLog) {
 436                 this.doLog = doLog;
 437         }
 438
 439     /**
 440      * Checks if an attribute exists in the database schema. At the moment only supported
 441      * for SQL Server.
 442      * TODO implement for others.
 443      * @param tableName
 444      * @param dbAttribute
 445      * @return
 446      * @throws MethodNotSupportedException
 447      */
 448     public boolean checkColumnExists(String tableName, String dbAttribute) throws DatabaseTypeNotSupportedException{
 449         if (mDbms.equalsIgnoreCase(SQL_SERVER_2005)|| mDbms.equalsIgnoreCase(SQL_SERVER_2008) ){
 450                 String strQuery = "SELECT  Count(t.id) as n " +
 451                                 " FROM sysobjects AS t " +
 452                                 " INNER JOIN syscolumns AS c ON t.id = c.id " +
 453                                 " WHERE (t.xtype = 'U') AND " +
 454                                 " (t.name = '" + tableName + "') AND " +
 455                                 " (c.name = '" + dbAttribute + "')";
 456                         ResultSet rs = getResultSet(strQuery) ;
 457                         int n;
 458                         try {
 459                                 rs.next();
 460                                 n = rs.getInt("n");
 461                                 return n>0;
 462                         } catch (SQLException e) {
 463                                 e.printStackTrace();
 464                                 return false;
 465                         }
 466         }else{
 467                 throw new DatabaseTypeNotSupportedException("Check column exist is not supported by the database system");
 468         }
 469     }
 470
 471     @Override
 472     public String toString(){
 473         if (mDb != null){
 474                 return mDb;
 475         }else if (mUrl == null){
 476                 return super.toString();
 477         }else{
 478                 return mUrl;
 479         }
 480     }
 481
 482
 483 }