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
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
;
26 import org
.apache
.http
.MethodNotSupportedException
;
27 import org
.apache
.log4j
.Logger
;
29 import eu
.etaxonomy
.cdm
.database
.ICdmDataSource
;
30 import eu
.etaxonomy
.cdm
.database
.update
.DatabaseTypeNotSupportedException
;
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üller
41 static Logger logger
= Logger
.getLogger(Source
.class);
43 /* ************ Constants **************************************/
45 private final static boolean DEBUG_MODE
= false;
46 private final static boolean DEBUG_LOG_WRITER
= false;
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";
59 public final static String SELECT_DIRECT
= "direct";
60 public final static String SELECT_CURSOR
= "cursor";
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";
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";
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
;
98 private boolean doLog
= false;
101 private static String userNameDefault
= "sa"; //default user
102 private static String pwdDefault
= "sa"; //default PWD
105 /* ******************** CONSTRUCTORS **********************************/
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
114 public Source(String dbms
, String server
, String db
){
118 this.mPwd
= pwdDefault
;
119 this.mUserName
= userNameDefault
;
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
129 public Source(String dbms
, String server
, String db
, String query
){
130 this(dbms
, server
, db
);
131 this.setQuery(query
);
135 * Creates a source and sets the parameter.
136 * @param cursor access mode, if true 'cursor', if false 'direct'
138 public Source(String dbms
, String server
, String db
, boolean cursor
) {
139 this(dbms
, server
, db
);
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)
151 public Source(String dbms
, String server
, String db
, int port
) {
152 this(dbms
, server
, db
);
159 * Creates a source with parameters of a ICdmDataSource instance
161 * @param cdmDataSource
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());
172 //********************* METHODS *****************************************************/
174 //******* RESULTSETS *******************//
176 * Returns the Resultset for query 'query'.
177 * Does not change the Sources query-string!!
178 * @return Resultset for the query.
180 public ResultSet
getResultSet (String query
){
183 this.getConnection(); //establish connection
187 mStmt
= mConn
.createStatement();
188 rs
= mStmt
.executeQuery(query
);
191 logger
.error("Problems when creating Resultset for query \n " + query
+ " \n" + "Exception: " + e
);
192 throw new RuntimeException(e
);
196 //******* INSERT, UPDATE, DELETE *******************//
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.
203 public int update (String updateStatement
){
206 this.getConnection(); //establish connection
207 if (updateStatement
== null){
210 mStmt
= mConn
.createStatement();
211 result
= mStmt
.executeUpdate(updateStatement
);
213 }catch(SQLException e
){
214 logger
.error("Problems when creating Resultset for query \n " + updateStatement
+ " \n" + "Exception: " + e
);
221 * Returns the Resultset for Sources query.
222 * @return Resultset for the Sources query
224 public ResultSet
getResultSet (){
225 return getResultSet(mQuery
);
229 // ***************CONNECTION *************************************//
231 * Returns the connection.
232 * @return the Sources connection
234 public Connection
getConnection(){
236 if (this.connExist
== false){
240 this.makeConnection() ;
243 } catch (SourceConnectionException e
) {
244 //TODO error weitergabe des Fehlers nach oben?
245 logger
.error("Error when establishing Connection");
247 } catch (SQLException e
) {
248 logger
.error("SQLException in getConnection.");
255 * Makes the connection.
256 * @return true if connection created
257 * @throws SourceConnectionException
259 private boolean makeConnection()throws SourceConnectionException
{
261 DriverManager
.setLogWriter(new PrintWriter(System
.out
));
264 if (makeConnectionString() == false){
265 throw new SourceConnectionException ("Error in Connection String");
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
);
276 mConn
= DriverManager
.getConnection(mUrl
, mUserName
, mPwd
);
281 mConn
.setCatalog(mDb
); //
282 logger
.info("Connected to " + mConn
.getCatalog());
283 mStmt
= mConn
.createStatement();
284 this.connExist
= true;
286 }catch (SQLException e
){
287 logger
.error("Problems when trying to open the database !!!\n" +
288 "URL: " + mUrl
+ "\n" +
290 throw new SourceConnectionException ();
295 * Makes the connection string
296 * @return false if ClassNotFoundException, else true
298 private boolean makeConnectionString(){
303 selectMethod
= SELECT_CURSOR
;
305 selectMethod
= SELECT_DIRECT
;
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;
313 else if (mDbms
.equalsIgnoreCase(SQL_SERVER_2005
)) {
314 Class
.forName(clsSQLServer2005
);
315 server
= mServer
+ ":" + mPort
;
316 mUrl
= urlSQLServer2005
+ server
+ ";databaseName=" + mDb
+";SelectMethod="+ selectMethod
;
318 else if (mDbms
.equalsIgnoreCase(SQL_SERVER_2008
)) {
319 Class
.forName(clsSQLServer2008
);
320 server
= mServer
+ ":" + mPort
;
321 mUrl
= urlSQLServer2008
+ server
+ ";databaseName=" + mDb
+";SelectMethod="+ selectMethod
;
323 else if (mDbms
.equalsIgnoreCase(ACCESS
)) {
324 Class
.forName(clsODBC
);
326 //mDb must be the file path
327 mUrl
= urlODBC
+ "Driver={Microsoft Access Driver (*.mdb)};DBQ=";
328 mUrl
+= mDb
.trim() + ";DriverID=22;READONLY=false}";
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";
335 else if (mDbms
.equalsIgnoreCase(ODDBC
)) {
336 //mDb must be the System DNS name
337 Class
.forName(clsODBC
);
338 mUrl
= urlODBC
+ mDb
;
340 else if (mDbms
.equalsIgnoreCase(ORACLE
)) {
341 Class
.forName(clsOracle
);
342 mUrl
= urlOracle
+ mDb
;
344 else if (mDbms
.equalsIgnoreCase(DB2
)) {
345 Class
.forName(clsDB2
);
348 else if (mDbms
.equalsIgnoreCase("SQLServerDdtek")) {
349 Class
.forName(clsSQLServerDdtek
);
350 mUrl
= urlDataDirectSQLServer
+ mServer
;
353 Class
.forName(clsSQLServer2005
);
356 logger
.debug("Connection String: " + mUrl
);
358 }catch (ClassNotFoundException e
){
359 logger
.error("Datenbank-Treiber-Klasse konnte nicht geladen werden\n" + "Exception: " + e
.toString());
365 /* ******************** SETTER *************************************/
370 public Source
setQuery (String query
){
379 public Source
setUsername (String userName
){
380 if (userName
== null){
381 this.mUserName
= userNameDefault
;
383 this.mUserName
= userName
;
385 this.connExist
= false;
393 public Source
setPassword (String pwd
){
395 this.mPwd
= pwdDefault
;
399 this.connExist
= false;
404 * Sets the username and password.
408 public Source
setUserAndPwd (String userName
, String pwd
){
409 setUsername(userName
);
419 public Source
setPort (int port
){
421 this.connExist
= false;
425 public String
getDatabase(){
429 public String
getServer(){
434 public boolean isDoLog() {
438 public void setDoLog(boolean doLog
) {
443 * Checks if an attribute exists in the database schema. At the moment only supported
445 * TODO implement for others.
449 * @throws MethodNotSupportedException
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
) ;
465 } catch (SQLException e
) {
470 throw new DatabaseTypeNotSupportedException("Check column exist is not supported by the database system");
475 public String
toString(){
478 }else if (mUrl
== null){
479 return super.toString();