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

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