2 * Copyright (C) 2009 EDIT
3 * European Distributed Institute of Taxonomy
4 * http://www.e-taxonomy.eu
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.
10 package eu
.etaxonomy
.cdm
.io
.common
;
13 * Created on 14.05.2005
14 * @author Andreas M�ller
22 import org
.apache
.log4j
.Logger
;
24 import eu
.etaxonomy
.cdm
.database
.ICdmDataSource
;
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üller
35 static Logger logger
= Logger
.getLogger(Source
.class);
37 /* ************ Constants **************************************/
39 private final static boolean DEBUG_MODE
= false;
40 private final static boolean DEBUG_LOG_WRITER
= false;
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";
53 public final static String SELECT_DIRECT
= "direct";
54 public final static String SELECT_CURSOR
= "cursor";
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";
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";
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
;
92 private static String userNameDefault
= "sa"; //default user
93 private static String pwdDefault
= "sa"; //default PWD
96 /* ******************** CONSTRUCTORS **********************************/
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
105 public Source(String dbms
, String server
, String db
){
109 this.mPwd
= pwdDefault
;
110 this.mUserName
= userNameDefault
;
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
120 public Source(String dbms
, String server
, String db
, String query
){
121 this(dbms
, server
, db
);
122 this.setQuery(query
);
126 * Creates a source and sets the parameter.
127 * @param cursor access mode, if true 'cursor', if false 'direct'
129 public Source(String dbms
, String server
, String db
, boolean cursor
) {
130 this(dbms
, server
, db
);
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)
142 public Source(String dbms
, String server
, String db
, int port
) {
143 this(dbms
, server
, db
);
150 * Creates a source with parameters of a ICdmDataSource instance
152 * @param cdmDataSource
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());
163 //********************* METHODS *****************************************************/
165 //******* RESULTSETS *******************//
167 * Returns the Resultset for query 'query'.
168 * Does not change the Sources query-string!!
169 * @return Resultset for the query.
171 public ResultSet
getResultSet (String query
){
174 this.getConnection(); //establish connection
178 mStmt
= mConn
.createStatement();
179 rs
= mStmt
.executeQuery(query
);
181 }catch(SQLException e
){
182 logger
.error("Problems when creating Resultset for query \n " + query
+ " \n" + "Exception: " + e
);
187 //******* INSERT, UPDATE, DELETE *******************//
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.
194 public int update (String updateStatement
){
197 this.getConnection(); //establish connection
198 if (updateStatement
== null){
201 mStmt
= mConn
.createStatement();
202 result
= mStmt
.executeUpdate(updateStatement
);
204 }catch(SQLException e
){
205 logger
.error("Problems when creating Resultset for query \n " + updateStatement
+ " \n" + "Exception: " + e
);
212 * Returns the Resultset for Sources query.
213 * @return Resultset for the Sources query
215 public ResultSet
getResultSet (){
216 return getResultSet(mQuery
);
220 // ***************CONNECTION *************************************//
222 * Returns the connection.
223 * @return the Sources connection
225 public Connection
getConnection(){
227 if (this.connExist
== false){
231 this.makeConnection() ;
234 } catch (SourceConnectionException e
) {
235 //TODO error weitergabe des Fehlers nach oben?
236 logger
.error("Error when establishing Connection");
238 } catch (SQLException e
) {
239 logger
.error("SQLException in getConnection.");
246 * Makes the connection.
247 * @return true if connection created
248 * @throws SourceConnectionException
250 private boolean makeConnection()throws SourceConnectionException
{
252 if (makeConnectionString() == false){
253 throw new SourceConnectionException ("Error in Connection String");
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;
261 }catch (SQLException e
){
262 logger
.error("Probleme beim �ffnen der Datenbank !!!\n" +
263 "URL: " + mUrl
+ "\n" +
265 throw new SourceConnectionException ();
270 * Makes the connection string
271 * @return false if ClassNotFoundException, else true
273 private boolean makeConnectionString(){
278 selectMethod
= SELECT_CURSOR
;
280 selectMethod
= SELECT_DIRECT
;
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;
288 else if (mDbms
.equalsIgnoreCase(SQL_SERVER_2005
)) {
289 Class
.forName(clsSQLServer2005
);
290 server
= mServer
+ ":" + mPort
;
291 mUrl
= urlSQLServer2005
+ server
+ ";databaseName=" + mDb
+";SelectMethod="+ selectMethod
;
293 else if (mDbms
.equalsIgnoreCase(SQL_SERVER_2008
)) {
294 Class
.forName(clsSQLServer2008
);
295 server
= mServer
+ ":" + mPort
;
296 mUrl
= urlSQLServer2008
+ server
+ ";databaseName=" + mDb
+";SelectMethod="+ selectMethod
;
298 else if (mDbms
.equalsIgnoreCase(ACCESS
)) {
299 Class
.forName(clsODBC
);
300 mUrl
= urlODBC
+ mDb
;
302 else if (mDbms
.equalsIgnoreCase(EXCEL
)) {
303 Class
.forName(clsODBC
);
304 mUrl
= urlODBC
+ mDb
;
306 else if (mDbms
.equalsIgnoreCase(ODDBC
)) {
307 Class
.forName(clsODBC
);
308 mUrl
= urlODBC
+ mServer
;
310 else if (mDbms
.equalsIgnoreCase(ORACLE
)) {
311 Class
.forName(clsOracle
);
312 mUrl
= urlOracle
+ mDb
;
314 else if (mDbms
.equalsIgnoreCase(DB2
)) {
315 Class
.forName(clsDB2
);
318 else if (mDbms
.equalsIgnoreCase("SQLServerDdtek")) {
319 Class
.forName(clsSQLServerDdtek
);
320 mUrl
= urlDataDirectSQLServer
+ mServer
;
323 Class
.forName(clsSQLServer2005
);
326 logger
.debug("Connection String: " + mUrl
);
328 }catch (ClassNotFoundException e
){
329 logger
.error("Datenbank-Treiber-Klasse konnte nicht geladen werden\n" + "Exception: " + e
.toString());
335 /* ******************** SETTER *************************************/
340 public Source
setQuery (String query
){
349 public Source
setUsername (String userName
){
350 if (userName
== null){
351 this.mUserName
= userNameDefault
;
353 this.mUserName
= userName
;
355 this.connExist
= false;
363 public Source
setPassword (String pwd
){
365 this.mPwd
= pwdDefault
;
369 this.connExist
= false;
374 * Sets the username and password.
378 public Source
setUserAndPwd (String userName
, String pwd
){
379 setUsername(userName
);
389 public Source
setPort (int port
){
391 this.connExist
= false;
395 public String
getDatabase(){
399 public String
getServer(){