/*
* This program is free software; you can redistribute it and/or modify it under the
* terms of the GNU Lesser General Public License, version 2.1 as published by the Free Software
* Foundation.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* program; if not, you can obtain a copy at http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html
* or from the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*
* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* Copyright 2005 - 2009 Pentaho Corporation. All rights reserved.
*
*
* Created Aug 30, 2005
* @author wseyler
*/
package org.pentaho.platform.plugin.services.connections.sql;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import org.pentaho.commons.connection.ILimitableConnection;
import org.pentaho.commons.connection.IPentahoConnection;
import org.pentaho.commons.connection.IPentahoResultSet;
import org.pentaho.platform.api.data.IDatasourceService;
import org.pentaho.platform.api.engine.ILogger;
import org.pentaho.platform.api.engine.ObjectFactoryException;
import org.pentaho.platform.api.engine.PentahoSystemException;
import org.pentaho.platform.engine.core.system.IPentahoLoggingConnection;
import org.pentaho.platform.engine.core.system.PentahoSystem;
import org.pentaho.platform.plugin.services.messages.Messages;
/**
* @author wseyler
*
* TODO To change the template for this generated type comment go to Window - Preferences - Java - Code Style - Code Templates
*/
public class SQLConnection implements IPentahoLoggingConnection, ILimitableConnection {
protected Connection nativeConnection;
/*
* private static int connectionCtr = 0;
*/
// private int myCtr;
/** keep track of any created statements for closing at the end */
ArrayList<Statement> stmts = new ArrayList<Statement>();
/** keep track of any created result sets for closing at the end */
ArrayList<IPentahoResultSet> resultSets = new ArrayList<IPentahoResultSet>();
IPentahoResultSet sqlResultSet = null;
ILogger logger = null;
private int timeOut = -1; // in seconds
private int maxRows = -1;
private int fetchSize = -1;
private boolean readOnly;
private boolean forcedForwardOnly = false;
private boolean fallBackToNonscrollableOnError = true;
public static final int RESULTSET_SCROLLABLE = ResultSet.TYPE_SCROLL_INSENSITIVE;
public static final int RESULTSET_FORWARDONLY = ResultSet.TYPE_FORWARD_ONLY;
public static final int CONCUR_READONLY = ResultSet.CONCUR_READ_ONLY;
public static final int CONCUR_UPDATABLE = ResultSet.CONCUR_UPDATABLE;
/*
* private synchronized void bump() { connectionCtr++; }
*/
String lastQuery = null;
public SQLConnection() {
super();
}
public void setLogger(final ILogger logger) {
this.logger = logger;
}
public void setProperties(Properties props) {
// TODO: consolidate this into connect()
String jndiName = props.getProperty(IPentahoConnection.JNDI_NAME_KEY);
if (jndiName != null) {
initWithJNDI(jndiName);
} else {
connect(props);
}
}
//Added by Arijit Chatterjee.Sets the value of timeout
/**
* Sets the valid of the timeout (in seconds)
*/
public void setQueryTimeout(final int timeInSec) {
timeOut = timeInSec;
}
/**
* Sets the connection object to readonly.
* @param value
*/
public void setReadOnly(final boolean value) {
this.readOnly = value;
}
//Added by Arijit Chatterjee. gets the value of timeout
/**
* Returns the query timeout value (in seconds)
*/
public int getQueryTimeout() {
return this.timeOut;
}
public SQLConnection(final String driverName, final String location, final String userName, final String password,
final ILogger logger) {
super();
this.logger = logger;
init(driverName, location, userName, password);
}
protected void init(final String driverName, final String location, final String userName, final String password) {
// bump();
try {
/*
* TODO This is where we use the java.sql package to provide a SQL connection object back to the caller
*/
Driver driver = null;
try {
driver = DriverManager.getDriver(location);
} catch (Exception e) {
// if we don't find this connection, it isn't registered, so we'll try to find it on the classpath
}
if (driver == null) {
Class driverClass = Class.forName(driverName);
driver = (Driver) driverClass.newInstance();
DriverManager.registerDriver(driver);
}
Properties info = new Properties();
info.put("user", userName); //$NON-NLS-1$
info.put("password", password); //$NON-NLS-1$
nativeConnection = captureConnection(driver.connect(location, info));
if (nativeConnection == null) {
logger.error(Messages.getInstance().getErrorString("ConnectFactory.ERROR_0001_INVALID_CONNECTION2", driverName, location)); //$NON-NLS-1$
} else {
enhanceConnection(nativeConnection);
}
} catch (Throwable t) {
logger.error(Messages.getInstance().getErrorString("ConnectFactory.ERROR_0001_INVALID_CONNECTION2", driverName, location), t); //$NON-NLS-1$
close(); // do not allow connection to be used as it might not be enhanced
}
}
public boolean initialized() {
return nativeConnection != null;
}
/**
* return datasource type SQL
* @return datasource type
*/
public String getDatasourceType() {
return IPentahoConnection.SQL_DATASOURCE;
}
protected void initWithJNDI(final String jndiName) {
// bump();
// myCtr = connectionCtr;
try {
IDatasourceService datasourceService = PentahoSystem.getObjectFactory().get(IDatasourceService.class ,null);
DataSource dataSource = datasourceService.getDataSource(jndiName);
if (dataSource != null) {
nativeConnection = captureConnection(dataSource.getConnection());
if (nativeConnection == null) {
logger.error(Messages.getInstance().getErrorString("ConnectFactory.ERROR_0001_INVALID_CONNECTION", jndiName)); //$NON-NLS-1$
// clear datasource cache
datasourceService.clearDataSource(jndiName);
} else {
enhanceConnection(nativeConnection);
}
} else {
logger.error(Messages.getInstance().getErrorString("ConnectFactory.ERROR_0001_INVALID_CONNECTION", jndiName)); //$NON-NLS-1$
// clear datasource cache
datasourceService.clearDataSource(jndiName);
}
} catch (Exception e) {
logger.error(Messages.getInstance().getErrorString("ConnectFactory.ERROR_0001_INVALID_CONNECTION", jndiName), e); //$NON-NLS-1$
close(); // do not allow connection to be used as it might not be enhanced
// clear datasource cache
try {
IDatasourceService datasourceService = PentahoSystem.getObjectFactory().get(IDatasourceService.class ,null);
datasourceService.clearDataSource(jndiName);
} catch(ObjectFactoryException objface) {
logger.error(Messages.getInstance().getErrorString("ConnectFactory.ERROR_0002_UNABLE_TO_FACTORY_OBJECT=Unable to factory object", jndiName), e); //$NON-NLS-1$
}
}
}
/**
* Allows the native SQL Connection to be enhanced in a subclass. Best
* used when a connection needs to be enhanced with an "effective user"
* @param connection
*/
protected void enhanceConnection(Connection connection) throws SQLException {
}
/**
* Allows enhancements to the native SQL Connection to be removed in a subclass. Best
* used when a connection needs to be enhanced with an "effective user"
* @param connection
*/
protected void unEnhanceConnection(Connection connection) throws SQLException {
}
/**
* Allow wrapping/proxying of the native SQL connection by a subclass. Best
* used when a connection needs to be be enhanced or proxied for Single Signon
* or possibly tenanting.
* @param connection
* @return
*/
protected Connection captureConnection(Connection connection) throws SQLException {
return connection;
}
/**
* Allows the native SQL Statement to be enhanced by a subclass.
* Examples may be to allow additional information like a user to
* be bound to the statement.
* @param statement
*/
protected void enhanceStatement(Statement statement) throws SQLException {
}
/**
* iterate over and close all statements. Remove each statement from the list.
*/
private void closeStatements() {
Iterator iter = stmts.iterator();
while (iter.hasNext()) {
Statement stmt = (Statement) iter.next();
if (stmt != null) {
try {
stmt.close();
} catch (Exception ignored) {
}
}
iter.remove();
}
}
/**
* iterate over and close all resultsets. Remove each result set from the list.
*/
private void closeResultSets() {
Iterator iter = resultSets.iterator();
while (iter.hasNext()) {
IPentahoResultSet rset = (IPentahoResultSet) iter.next();
if (rset != null) {
try {
rset.close();
} catch (Exception ignored) {
}
}
iter.remove();
}
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoConnection#close()
*/
public void close() {
closeResultSets();
closeStatements();
if (nativeConnection != null) {
try {
unEnhanceConnection(nativeConnection);
if (getReadOnly()) {
try {
// Reset the readonly on the native connection before closing
nativeConnection.setReadOnly(false);
} catch (SQLException ignored) {}
}
nativeConnection.close();
} catch (SQLException e) {
logger.error(null, e);
}
}
nativeConnection = null;
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoConnection#getLastQuery()
*/
public String getLastQuery() {
return lastQuery;
}
/**
* Executes the specified query.
* @param query the query to execute
* @return the resultset from the query
* @throws SQLException indicates an error running the query
* @throws InterruptedException indicates that the query took longer than the allowed timeout value
* @throws PentahoSystemException
*/
public IPentahoResultSet executeQuery(final String query) throws SQLException, InterruptedException, PentahoSystemException {
return executeQuery(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
/**
* Executes the specified query with the defined parameters
* @param query the query to be executed
* @param scrollType
* @param concur
* @return the result set of data for the query
* @throws SQLException indicates an error running the query
* @throws InterruptedException indicates the query took longer than allowable by the query timeout
* @throws PentahoSystemException
*/
public IPentahoResultSet executeQuery(final String query, final int scrollType, final int concur)
throws SQLException, InterruptedException, PentahoSystemException {
if (this.getReadOnly()) {
try {
nativeConnection.setReadOnly(true);
} catch (Exception ignored) {}
}
// Create a statement for a scrollable resultset.
Statement stmt = null;
ResultSet resultSet = null;
try {
stmt = nativeConnection.createStatement(scrollType, concur);
stmts.add(stmt);
enhanceStatement(stmt);
setStatementLimitations(stmt);
resultSet = stmt.executeQuery(query);
} catch (Exception e) {
// We're going to assume that the problem MIGHT be that a scrolling resultset isn't supported
// on this connection, then try to fix it up...
if ((scrollType == ResultSet.TYPE_SCROLL_INSENSITIVE)&&(isFallBackToNonscrollableOnError())){
// FORCE forward only
stmt = nativeConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, concur);
stmts.add(stmt);
enhanceStatement(stmt);
setStatementLimitations(stmt);
resultSet = stmt.executeQuery(query);
setForcedForwardOnly(true);
}
}
sqlResultSet = new SQLResultSet(resultSet, this);
// add to list of resultsets for cleanup later.
resultSets.add(sqlResultSet);
lastQuery = query;
return sqlResultSet;
}
public IPentahoResultSet prepareAndExecuteQuery(final String query, final List parameters) throws SQLException {
return prepareAndExecuteQuery(query, parameters, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
/**
* The purpose of this method is to set limitations such as fetchSize
* and maxrows on the provided statement. If the JDBC driver does not
* support the setting and throws an Exception, we will re-throw iff
* the limit was explicitly set.
* @param stmt Either a Statement or PreparedStatement
* @throws SQLException, UnsupportedOperationException
*/
protected void setStatementLimitations(Statement stmt) throws SQLException {
if (this.getFetchSize() >= 0) {
try {
stmt.setFetchSize(this.getFetchSize());
} catch (Exception ex) {
if (ex instanceof SQLException) {
throw (SQLException)ex;
} else {
// exception here means either the number was out of bounds or
// the driver doesn't support this setter.
throw new UnsupportedOperationException(Messages.getInstance().getErrorString("SQLConnection.ERROR_0003_FETCHSIZE_NOT_SET", Integer.toString(this.getFetchSize())), ex); //$NON-NLS-1$
}
}
}
if (this.getMaxRows() >= 0 ) {
try {
stmt.setMaxRows(this.getMaxRows());
} catch (Exception ex) {
if (ex instanceof SQLException) {
throw (SQLException)ex;
} else {
// exception here means either the number was out of bounds or
// the driver doesn't support this setter.
throw new UnsupportedOperationException(Messages.getInstance().getErrorString("SQLConnection.ERROR_0002_ROWLIMIT_NOT_SET", Integer.toString(this.getMaxRows())), ex); //$NON-NLS-1$
}
}
}
if (this.getQueryTimeout() >= 0) {
try {
stmt.setQueryTimeout(this.getQueryTimeout());
} catch (Exception e) {
if (e instanceof SQLException) {
throw (SQLException)e;
} else {
throw new UnsupportedOperationException(Messages.getInstance().getErrorString("SQLConnection.ERROR_0001_TIMEOUT_NOT_SET", Integer.toString(this.getQueryTimeout())), e); //$NON-NLS-1$
}
}
}
}
public IPentahoResultSet prepareAndExecuteQuery(final String query, final List parameters, final int scrollType,
final int concur) throws SQLException {
if (this.getReadOnly()) {
try {
nativeConnection.setReadOnly(true);
} catch (Exception ignored) {}
}
// Create a prepared statement
PreparedStatement pStmt = null;
ResultSet resultSet = null;
try {
pStmt = nativeConnection.prepareStatement(query, scrollType, concur);
// add to stmts list for closing when connection closes
stmts.add(pStmt);
enhanceStatement(pStmt);
setStatementLimitations(pStmt);
for (int i = 0; i < parameters.size(); i++) {
pStmt.setObject(i + 1, parameters.get(i));
}
resultSet = pStmt.executeQuery();
} catch (Exception e) {
// attempt to remove the offending statement...
stmts.remove(pStmt);
if ((scrollType == ResultSet.TYPE_SCROLL_INSENSITIVE)&&(isFallBackToNonscrollableOnError())){
// FORCE forward only
pStmt = nativeConnection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, concur);
// add to stmts list for closing when connection closes
stmts.add(pStmt);
enhanceStatement(pStmt);
setStatementLimitations(pStmt);
for (int i = 0; i < parameters.size(); i++) {
pStmt.setObject(i + 1, parameters.get(i));
}
resultSet = pStmt.executeQuery();
setForcedForwardOnly(true);
}
}
sqlResultSet = new SQLResultSet(resultSet, this);
// add to list of resultsets for cleanup later.
resultSets.add(sqlResultSet);
lastQuery = query;
return sqlResultSet;
}
public boolean preparedQueriesSupported() {
return true;
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoConnection#isClosed()
*/
public boolean isClosed() {
try {
return nativeConnection.isClosed();
} catch (SQLException e) {
logger.error(null, e);
}
return true; // assume since we couldn't get here if it
// was open then we must be closed.
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoConnection#isReadOnly()
*
* Right now this archetecture only support selects (read only)
*/
public boolean isReadOnly() {
return true;
}
public void clearWarnings() {
try {
nativeConnection.clearWarnings();
} catch (SQLException e) {
logger.error(null, e);
}
}
public IPentahoResultSet getResultSet() {
return sqlResultSet;
}
public boolean connect(final Properties props) {
close();
String jndiName = props.getProperty(IPentahoConnection.JNDI_NAME_KEY);
if ((jndiName != null) && (jndiName.length() > 0)) {
initWithJNDI(jndiName);
} else {
String driver = props.getProperty(IPentahoConnection.DRIVER_KEY);
String provider = props.getProperty(IPentahoConnection.LOCATION_KEY);
String userName = props.getProperty(IPentahoConnection.USERNAME_KEY);
String password = props.getProperty(IPentahoConnection.PASSWORD_KEY);
init(driver, provider, userName, password);
String query = props.getProperty(IPentahoConnection.QUERY_KEY);
if ((query != null) && (query.length() > 0)) {
try {
executeQuery(query);
} catch (Exception e) {
logger.error(null, e);
}
}
}
return ((nativeConnection != null) && !isClosed());
}
public int execute(final String query) throws SQLException {
return execute(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
public int execute(final String query, final int scrollType, final int concur) throws SQLException {
// Create a statement for a scrollable resultset.
Statement stmt = nativeConnection.createStatement(scrollType, concur);
// add to stmts list for closing when connection closes
enhanceStatement(stmt);
stmts.add(stmt);
setStatementLimitations(stmt);
int result = stmt.executeUpdate(query);
lastQuery = query;
return result;
}
/**
* @return Returns the nativeConnection.
*/
public Connection getNativeConnection() {
return nativeConnection;
}
/**
* @return Returns the fetchSize.
*/
public int getFetchSize() {
return fetchSize;
}
/**
* @param fetchSize
* The fetchSize to set.
*/
public void setFetchSize(final int fetchSize) {
this.fetchSize = fetchSize;
}
/**
* @return Returns the maxRows.
*/
public int getMaxRows() {
return maxRows;
}
/**
* @param maxRows
* The maxRows to set.
*/
public void setMaxRows(final int maxRows) {
this.maxRows = maxRows;
}
/**
* Returns the state of the readonly flag
* @return true if the connection is set to readonly
*/
public boolean getReadOnly() {
return this.readOnly;
}
public void setFallBackToNonscrollableOnError(boolean fallBackToNonscrollableOnError) {
this.fallBackToNonscrollableOnError = fallBackToNonscrollableOnError;
}
public boolean isFallBackToNonscrollableOnError() {
return fallBackToNonscrollableOnError;
}
public boolean isForcedForwardOnly() {
return forcedForwardOnly;
}
public void setForcedForwardOnly(boolean forcedForwardOnly) {
this.forcedForwardOnly = forcedForwardOnly;
}
}