001: /*
002: * Copyright 2006 Pentaho Corporation. All rights reserved.
003: * This software was developed by Pentaho Corporation and is provided under the terms
004: * of the Mozilla Public License, Version 1.1, or any later version. You may not use
005: * this file except in compliance with the license. If you need a copy of the license,
006: * please go to http://www.mozilla.org/MPL/MPL-1.1.txt. The Original Code is the Pentaho
007: * BI Platform. The Initial Developer is Pentaho Corporation.
008: *
009: * Software distributed under the Mozilla Public License is distributed on an "AS IS"
010: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
011: * the license for the specific language governing your rights and limitations.
012: *
013: * Created Aug 30, 2005
014: * @author wseyler
015: */
016: package org.pentaho.data.connection.sql;
017:
018: import java.sql.Connection;
019: import java.sql.Driver;
020: import java.sql.DriverManager;
021: import java.sql.PreparedStatement;
022: import java.sql.ResultSet;
023: import java.sql.SQLException;
024: import java.sql.Statement;
025: import java.util.ArrayList;
026: import java.util.Iterator;
027: import java.util.List;
028: import java.util.Properties;
029: import javax.sql.DataSource;
030: import org.pentaho.commons.connection.IPentahoConnection;
031: import org.pentaho.commons.connection.IPentahoResultSet;
032: import org.pentaho.core.util.DatasourceHelper;
033: import org.pentaho.messages.Messages;
034: import org.pentaho.util.logging.ILogger;
035:
036: /**
037: * @author wseyler
038: *
039: * TODO To change the template for this generated type comment go to Window - Preferences - Java - Code Style - Code Templates
040: */
041: public class SQLConnection implements IPentahoConnection {
042: Connection nativeConnection;
043:
044: /*
045: * private static int connectionCtr = 0;
046: */
047: // private int myCtr;
048: /** keep track of any created statements for closing at the end */
049: ArrayList stmts = new ArrayList();
050:
051: /** keep track of any created result sets for closing at the end */
052: ArrayList resultSets = new ArrayList();
053:
054: IPentahoResultSet sqlResultSet = null;
055:
056: ILogger logger = null;
057:
058: int maxRows = -1;
059:
060: int fetchSize = -1;
061:
062: public static final int RESULTSET_SCROLLABLE = ResultSet.TYPE_SCROLL_INSENSITIVE;
063: public static final int RESULTSET_FORWARDONLY = ResultSet.TYPE_FORWARD_ONLY;
064: public static final int CONCUR_READONLY = ResultSet.CONCUR_READ_ONLY;
065: public static final int CONCUR_UPDATABLE = ResultSet.CONCUR_UPDATABLE;
066:
067: /*
068: * private synchronized void bump() { connectionCtr++; }
069: */
070: String lastQuery = null;
071:
072: /**
073: * @throws SQLException
074: *
075: */
076: public SQLConnection(Properties props, ILogger logger) {
077: super ();
078: this .logger = logger;
079: connect(props);
080: }
081:
082: public SQLConnection(String jndiName, ILogger logger) {
083: super ();
084: this .logger = logger;
085: initWithJNDI(jndiName);
086: }
087:
088: public SQLConnection(String driverName, String location,
089: String userName, String password, ILogger logger) {
090: super ();
091: this .logger = logger;
092: init(driverName, location, userName, password);
093: }
094:
095: private void init(String driverName, String location,
096: String userName, String password) {
097: // bump();
098: try {
099: /*
100: * TODO This is where we use the java.sql package to provide a SQL connection object back to the caller
101: */
102: Driver driver = null;
103: try {
104: driver = DriverManager.getDriver(location);
105: } catch (Exception e) {
106: // if we don't find this connection, it isn't registered, so we'll try to find it on the classpath
107: }
108: if (driver == null) {
109: Class driverClass = Class.forName(driverName);
110: driver = (Driver) driverClass.newInstance();
111: DriverManager.registerDriver(driver);
112: }
113: Properties info = new Properties();
114: info.put("user", userName); //$NON-NLS-1$
115: info.put("password", password); //$NON-NLS-1$
116: nativeConnection = driver.connect(location, info);
117: if (nativeConnection == null) {
118: logger
119: .error(Messages
120: .getErrorString(
121: "ConnectFactory.ERROR_0001_INVALID_CONNECTION2", driverName, location)); //$NON-NLS-1$
122: }
123: } catch (Throwable t) {
124: logger
125: .error(
126: Messages
127: .getErrorString(
128: "ConnectFactory.ERROR_0001_INVALID_CONNECTION2", driverName, location), t); //$NON-NLS-1$
129: }
130: }
131:
132: public boolean initialized() {
133: return nativeConnection != null;
134: }
135:
136: /**
137: * return datasource type SQL
138: * @return datasource type
139: */
140: public int getDatasourceType() {
141: return SQL_DATASOURCE;
142: }
143:
144: private void initWithJNDI(String jndiName) {
145: // bump();
146: // myCtr = connectionCtr;
147: try {
148: DataSource dataSource = DatasourceHelper
149: .getDataSourceFromJndi(jndiName);
150: if (dataSource != null) {
151: nativeConnection = dataSource.getConnection();
152: if (nativeConnection == null) {
153: logger
154: .error(Messages
155: .getErrorString(
156: "ConnectFactory.ERROR_0001_INVALID_CONNECTION", jndiName)); //$NON-NLS-1$
157: // clear datasource cache
158: DatasourceHelper.clearDataSource(jndiName);
159: }
160: } else {
161: logger
162: .error(Messages
163: .getErrorString(
164: "ConnectFactory.ERROR_0001_INVALID_CONNECTION", jndiName)); //$NON-NLS-1$
165: // clear datasource cache
166: DatasourceHelper.clearDataSource(jndiName);
167: }
168: } catch (Exception e) {
169: logger
170: .error(
171: Messages
172: .getErrorString(
173: "ConnectFactory.ERROR_0001_INVALID_CONNECTION", jndiName), e); //$NON-NLS-1$
174: // clear datasource cache
175: DatasourceHelper.clearDataSource(jndiName);
176: }
177: }
178:
179: /**
180: * iterate over and close all statements. Remove each statement from the list.
181: */
182: private void closeStatements() {
183: Iterator iter = stmts.iterator();
184: while (iter.hasNext()) {
185: Statement stmt = (Statement) iter.next();
186: if (stmt != null) {
187: try {
188: stmt.close();
189: } catch (Exception ignored) {
190: }
191: }
192: iter.remove();
193: }
194: }
195:
196: /**
197: * iterate over and close all resultsets. Remove each result set from the list.
198: */
199: private void closeResultSets() {
200: Iterator iter = resultSets.iterator();
201: while (iter.hasNext()) {
202: IPentahoResultSet rset = (IPentahoResultSet) iter.next();
203: if (rset != null) {
204: try {
205: rset.close();
206: } catch (Exception ignored) {
207: }
208: }
209: iter.remove();
210: }
211: }
212:
213: /*
214: * (non-Javadoc)
215: *
216: * @see org.pentaho.connection.IPentahoConnection#close()
217: */
218: public void close() {
219: closeResultSets();
220: closeStatements();
221: if (nativeConnection != null) {
222: try {
223: nativeConnection.close();
224: } catch (SQLException e) {
225: // TODO Auto-generated catch block
226: e.printStackTrace();
227: }
228: }
229: nativeConnection = null;
230: }
231:
232: /*
233: * (non-Javadoc)
234: *
235: * @see org.pentaho.connection.IPentahoConnection#getLastQuery()
236: */
237: public String getLastQuery() {
238: return lastQuery;
239: }
240:
241: public IPentahoResultSet executeQuery(String query)
242: throws SQLException {
243: return executeQuery(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
244: ResultSet.CONCUR_READ_ONLY);
245: }
246:
247: /*
248: * (non-Javadoc)
249: *
250: * @see org.pentaho.connection.IPentahoConnection#executeQuery(java.lang.String)
251: */
252: public IPentahoResultSet executeQuery(String query, int scrollType,
253: int concur) throws SQLException {
254: // Create a statement for a scrollable resultset.
255: Statement stmt = nativeConnection.createStatement(scrollType,
256: concur);
257: stmts.add(stmt);
258: if (fetchSize > 0) {
259: stmt.setFetchSize(fetchSize);
260: }
261: if (maxRows != -1) {
262: stmt.setMaxRows(maxRows);
263: }
264: ResultSet resultSet = stmt.executeQuery(query);
265: sqlResultSet = new SQLResultSet(resultSet, this );
266: // add to list of resultsets for cleanup later.
267: resultSets.add(sqlResultSet);
268: lastQuery = query;
269: return sqlResultSet;
270: }
271:
272: public IPentahoResultSet prepareAndExecuteQuery(String query,
273: List parameters) throws SQLException {
274: return prepareAndExecuteQuery(query, parameters,
275: ResultSet.TYPE_SCROLL_INSENSITIVE,
276: ResultSet.CONCUR_READ_ONLY);
277: }
278:
279: public IPentahoResultSet prepareAndExecuteQuery(String query,
280: List parameters, int scrollType, int concur)
281: throws SQLException {
282: // Create a prepared statement
283: PreparedStatement pStmt = nativeConnection.prepareStatement(
284: query, scrollType, concur);
285: // add to stmts list for closing when connection closes
286: stmts.add(pStmt);
287: if (fetchSize > 0) {
288: pStmt.setFetchSize(fetchSize);
289: }
290: if (maxRows != -1) {
291: pStmt.setMaxRows(maxRows);
292: }
293:
294: for (int i = 0; i < parameters.size(); i++) {
295: pStmt.setObject(i + 1, parameters.get(i));
296: }
297: ResultSet resultSet = pStmt.executeQuery();
298:
299: sqlResultSet = new SQLResultSet(resultSet, this );
300: // add to list of resultsets for cleanup later.
301: resultSets.add(sqlResultSet);
302: lastQuery = query;
303: return sqlResultSet;
304: }
305:
306: public boolean preparedQueriesSupported() {
307: return true;
308: }
309:
310: /*
311: * (non-Javadoc)
312: *
313: * @see org.pentaho.connection.IPentahoConnection#isClosed()
314: */
315: public boolean isClosed() {
316: try {
317: return nativeConnection.isClosed();
318: } catch (SQLException e) {
319: // TODO Auto-generated catch block
320: e.printStackTrace();
321: }
322: return true; // assume since we couldn't get here if it
323: // was open then we must be closed.
324: }
325:
326: /*
327: * (non-Javadoc)
328: *
329: * @see org.pentaho.connection.IPentahoConnection#isReadOnly()
330: *
331: * Right now this archetecture only support selects (read only)
332: */
333: public boolean isReadOnly() {
334: return true;
335: }
336:
337: public void clearWarnings() {
338: try {
339: nativeConnection.clearWarnings();
340: } catch (SQLException e) {
341: // TODO Auto-generated catch block
342: e.printStackTrace();
343: }
344: }
345:
346: public IPentahoResultSet getResultSet() {
347: return sqlResultSet;
348: }
349:
350: public boolean connect(Properties props) {
351: close();
352: String jndiName = props
353: .getProperty(IPentahoConnection.JNDI_NAME_KEY);
354: if (jndiName != null && jndiName.length() > 0) {
355: initWithJNDI(jndiName);
356: } else {
357: String driver = props
358: .getProperty(IPentahoConnection.DRIVER_KEY);
359: String provider = props
360: .getProperty(IPentahoConnection.LOCATION_KEY);
361: String userName = props
362: .getProperty(IPentahoConnection.USERNAME_KEY);
363: String password = props
364: .getProperty(IPentahoConnection.PASSWORD_KEY);
365: init(driver, provider, userName, password);
366: String query = props
367: .getProperty(IPentahoConnection.QUERY_KEY);
368: if (query != null && query.length() > 0) {
369: try {
370: executeQuery(query);
371: } catch (SQLException e) {
372: // TODO Auto-generated catch block
373: e.printStackTrace();
374: }
375: }
376: }
377: return (nativeConnection != null && !isClosed());
378: }
379:
380: public int execute(String query) throws SQLException {
381: return execute(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
382: ResultSet.CONCUR_READ_ONLY);
383: }
384:
385: public int execute(String query, int scrollType, int concur)
386: throws SQLException {
387: // Create a statement for a scrollable resultset.
388: Statement stmt = nativeConnection.createStatement(scrollType,
389: concur);
390: // add to stmts list for closing when connection closes
391: stmts.add(stmt);
392: int result = stmt.executeUpdate(query);
393: lastQuery = query;
394: return result;
395: }
396:
397: /**
398: * @return Returns the nativeConnection.
399: */
400: public Connection getNativeConnection() {
401: return nativeConnection;
402: }
403:
404: /**
405: * @return Returns the fetchSize.
406: */
407: public int getFetchSize() {
408: return fetchSize;
409: }
410:
411: /**
412: * @param fetchSize
413: * The fetchSize to set.
414: */
415: public void setFetchSize(int fetchSize) {
416: this .fetchSize = fetchSize;
417: }
418:
419: /**
420: * @return Returns the maxRows.
421: */
422: public int getMaxRows() {
423: return maxRows;
424: }
425:
426: /**
427: * @param maxRows
428: * The maxRows to set.
429: */
430: public void setMaxRows(int maxRows) {
431: this.maxRows = maxRows;
432: }
433: }
|