package net.sf.soapjdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Map;
import java.util.Properties;
import net.sf.services.soapjdbcgate.Execute;
import net.sf.services.soapjdbcgate.ExecutePreparedQuery;
import net.sf.services.soapjdbcgate.ExecutePreparedUpdate;
import net.sf.services.soapjdbcgate.ExecuteQuery;
import net.sf.services.soapjdbcgate.ExecuteUpdate;
import net.sf.services.soapjdbcgate.GetDatabaseProductName;
import net.sf.services.soapjdbcgate.GetDatabaseProductVersion;
import net.sf.services.soapjdbcgate.LowerCaseTableNames;
import net.sf.services.soapjdbcgate.SoapJdbcGateStub;
import net.sf.services.soapjdbcgate.types.ArrayOfString;
import org.apache.axis2.transport.http.HTTPConstants;
import org.apache.axis2.transport.http.HttpTransportProperties.ProxyProperties;
import org.apache.commons.collections15.map.LRUMap;
import org.apache.log4j.Logger;
public class SoapJdbcConnection implements Connection {
public static final String PROXY_HOST = "http.proxyHost";
public static final String PROXY_PORT = "http.proxyPort";
public static final String PROXY_USERNAME = "http.proxyUser";
public static final String PROXY_PASSWORD = "http.proxyPassword";
private static Logger sLogger = Logger.getLogger( SoapJdbcConnection.class );
private boolean mClosed;
private boolean mReadOnly;
private Map<String, Class<?>> mTypeMap;
private SoapJdbcResultSet mLastResultSet;
private int mLastUpdateCount;
private ArrayList<BatchStatement> mBatch = new ArrayList<BatchStatement>();
private String mDatabaseName;
private LRUMap<String, SoapJdbcResultSetMetaData> mCachedMetadata;
private String mURL;
private SoapJdbcGateStub mPort;
private Integer mLastInsertID;
private String mUser;
private String mPassword;
public SoapJdbcConnection( String url, Properties info ) throws SQLException {
// URL-Syntax: jdbc:soap://<soap-service-url>#database
mURL = url.substring( 12 );
String database = "";
int index = mURL.indexOf( "#" );
if ( index > 0 && mURL.length() > index + 1 ) {
database = mURL.substring( index + 1 );
mURL = mURL.substring( 0, index );
} // if
try {
mPort = new SoapJdbcGateStub( mURL );
mUser = info.getProperty( "user", "" );
mPassword = info.getProperty( "password", "" );
mDatabaseName = database;
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Link-Error", SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
mCachedMetadata = new LRUMap<String, SoapJdbcResultSetMetaData>();
}
public Statement createStatement() throws SQLException {
checkClosed();
return new SoapJdbcStatement( this );
}
public PreparedStatement prepareStatement( String sql ) throws SQLException {
checkClosed();
return new SoapJdbcPreparedStatement( this, sql );
}
public CallableStatement prepareCall( String sql ) throws SQLException {
checkClosed();
return new SoapJdbcCallableStatement( this, sql );
}
public String nativeSQL( String sql ) throws SQLException {
checkClosed();
return sql;
}
public void setAutoCommit( boolean autoCommit ) throws SQLException {
checkClosed();
}
public boolean getAutoCommit() throws SQLException {
checkClosed();
return true;
}
public void commit() throws SQLException {
checkClosed();
}
public void rollback() throws SQLException {
checkClosed();
}
public void close() {
mClosed = true;
}
public boolean isClosed() {
return mClosed;
}
public DatabaseMetaData getMetaData() throws SQLException {
checkClosed();
return new SoapJdbcDatabaseMetaData( new net.sf.soapjdbc.mysql.DatabaseMetaData( new net.sf.soapjdbc.mysql.Connection( this ),
mDatabaseName ), this );
}
public void setReadOnly( boolean readOnly ) throws SQLException {
checkClosed();
mReadOnly = readOnly;
}
public boolean isReadOnly() throws SQLException {
checkClosed();
return mReadOnly;
}
public void setCatalog( String catalog ) throws SQLException {
checkClosed();
}
public String getCatalog() throws SQLException {
checkClosed();
return mDatabaseName;
}
public void setTransactionIsolation( int level ) throws SQLException {
checkClosed();
}
public int getTransactionIsolation() throws SQLException {
checkClosed();
return Connection.TRANSACTION_NONE;
}
public SQLWarning getWarnings() throws SQLException {
checkClosed();
return null;
}
public void clearWarnings() throws SQLException {
checkClosed();
}
public Statement createStatement( int resultSetType, int resultSetConcurrency ) throws SQLException {
checkClosed();
checkResultSetTypeAndConcurrency( resultSetType, resultSetConcurrency );
return new SoapJdbcStatement( this );
}
public PreparedStatement prepareStatement( String sql, int resultSetType, int resultSetConcurrency ) throws SQLException {
checkClosed();
checkResultSetTypeAndConcurrency( resultSetType, resultSetConcurrency );
return new SoapJdbcPreparedStatement( this, sql );
}
public CallableStatement prepareCall( String sql, int resultSetType, int resultSetConcurrency ) throws SQLException {
checkClosed();
checkResultSetTypeAndConcurrency( resultSetType, resultSetConcurrency );
return new SoapJdbcCallableStatement( this, sql );
}
public Map<String, Class<?>> getTypeMap() throws SQLException {
checkClosed();
return mTypeMap;
}
public void setTypeMap( Map<String, Class<?>> typeMap ) throws SQLException {
checkClosed();
mTypeMap = typeMap;
}
public void setHoldability( int holdability ) throws SQLException {
checkResultSetHoldability( holdability );
}
public int getHoldability() throws SQLException {
checkClosed();
return ResultSet.CLOSE_CURSORS_AT_COMMIT;
}
public Savepoint setSavepoint() throws SQLException {
throw new SoapJdbcSQLException( "savepoints aren't supported" );
}
public Savepoint setSavepoint( String name ) throws SQLException {
throw new SoapJdbcSQLException( "savepoints aren't supported" );
}
public void rollback( Savepoint savepoint ) throws SQLException {
throw new SoapJdbcSQLException( "rollback called while connection is in autocommit-mode" );
}
public void releaseSavepoint( Savepoint savepoint ) throws SQLException {
throw new SoapJdbcSQLException( "savepoints aren't supported" );
}
public Statement createStatement( int resultSetType, int resultSetConcurrency, int resultSetHoldability ) throws SQLException {
checkClosed();
checkResultSetTypeAndConcurrency( resultSetType, resultSetConcurrency );
checkResultSetHoldability( resultSetHoldability );
return new SoapJdbcStatement( this, resultSetType, resultSetConcurrency, resultSetHoldability );
}
public PreparedStatement prepareStatement( String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability )
throws SQLException {
checkClosed();
checkResultSetTypeAndConcurrency( resultSetType, resultSetConcurrency );
checkResultSetHoldability( resultSetHoldability );
return new SoapJdbcPreparedStatement( this, sql );
}
public CallableStatement prepareCall( String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability )
throws SQLException {
checkClosed();
checkResultSetTypeAndConcurrency( resultSetType, resultSetConcurrency );
checkResultSetHoldability( resultSetHoldability );
return new SoapJdbcCallableStatement( this, sql );
}
public PreparedStatement prepareStatement( String sql, int autoGeneratedKeys ) throws SQLException {
checkClosed();
if ( autoGeneratedKeys != Statement.RETURN_GENERATED_KEYS && autoGeneratedKeys != Statement.NO_GENERATED_KEYS ) {
throw new SoapJdbcSQLException( "Illegal autoGeneratedKeys-parameter", SoapJdbcSQLError.SQL_STATE_ILLEGAL_ARGUMENT );
} // if
return new SoapJdbcPreparedStatement( this, sql );
}
public PreparedStatement prepareStatement( String sql, int[] columnIndexes ) throws SQLException {
checkClosed();
return new SoapJdbcPreparedStatement( this, sql );
}
public PreparedStatement prepareStatement( String sql, String[] columnNames ) throws SQLException {
checkClosed();
return new SoapJdbcPreparedStatement( this, sql );
}
private void checkClosed() throws SQLException {
if ( mClosed ) {
throw new SoapJdbcSQLException( "No operations allowed after connection closed!",
SoapJdbcSQLError.SQL_STATE_CONNECTION_NOT_OPEN );
} // if
} // checkClosed
private void checkResultSetHoldability( int resultSetHoldability ) throws SQLException {
if ( resultSetHoldability != ResultSet.CLOSE_CURSORS_AT_COMMIT ) {
throw new SoapJdbcSQLException( "holdability isn't supported" );
} // if
}
private void checkResultSetTypeAndConcurrency( int resultSetType, int resultSetConcurrency ) throws SQLException {
if ( resultSetType != ResultSet.TYPE_FORWARD_ONLY ) {
throw new SoapJdbcSQLException( "ResultSet.TYPE_FORWARD_ONLY only supported for ResultSetType",
SoapJdbcSQLError.SQL_STATE_ILLEGAL_ARGUMENT );
} // if
if ( resultSetConcurrency != ResultSet.CONCUR_READ_ONLY ) {
throw new SoapJdbcSQLException( "ResultSet.CONCUR_READ_ONLY only supported for ResultSetConcurrency",
SoapJdbcSQLError.SQL_STATE_ILLEGAL_ARGUMENT );
} // if
}
ResultSet executeQuery( String sql ) throws SQLException {
try {
if ( sLogger.isDebugEnabled() ) {
sLogger.debug( "executeQuery: " + sql );
} // if
SoapJdbcResultSetMetaData metadata = mCachedMetadata.get( sql );
ExecuteQuery query = new ExecuteQuery();
query.setUsername( mUser );
query.setPassword( mPassword );
query.setDatabase( mDatabaseName );
query.setSql( sql );
query.setWithMetadata( metadata == null );
prepareHTTPClient();
processResultSet( sql, mPort.executeQuery( query ).get_return(), metadata );
return mLastResultSet;
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
}
private void processResultSet( String sql, net.sf.services.soapjdbcgate.types.SoapJdbcResultSet map, SoapJdbcResultSetMetaData metadata ) {
SoapJdbcResultSetRowData rowdata = null;
if ( map.getRowdata() != null ) {
ArrayOfString[] rows = map.getRowdata().getRows();
String[][] data = new String[rows.length][0];
if ( rows.length == 1 && rows[0] == null ) {
data = new String[0][0];
} else {
for ( int i = 0; i < rows.length; i++ ) {
data[i] = rows[i].getValue();
} // for
} // if
rowdata = new SoapJdbcResultSetRowData( data );
} // if
if ( map.getMetadata() != null ) {
SoapJdbcResultSetMetaDataField[] fields = new SoapJdbcResultSetMetaDataField[map.getMetadata().getFields().length];
for ( int i = 0; i < fields.length; i++ ) {
net.sf.services.soapjdbcgate.types.SoapJdbcResultSetMetaDataField field = map.getMetadata().getFields()[i];
fields[i] = new SoapJdbcResultSetMetaDataField();
fields[i].originalType = field.getOriginalType();
fields[i].autoIncrement = field.getAutoIncrement();
fields[i].nullable = field.getNullable();
fields[i].signed = field.getSigned();
fields[i].columnSize = field.getColumnSize();
fields[i].columnName = field.getColumnName();
fields[i].databaseName = mDatabaseName;
fields[i].tableName = field.getTableName();
} // for
metadata = new SoapJdbcResultSetMetaData( fields );
mCachedMetadata.put( sql, metadata );
} // if
if ( rowdata != null && metadata != null ) {
mLastResultSet = new SoapJdbcResultSet( metadata, rowdata );
} else {
if ( map.getLastInsertID() != -1 ) {
mLastInsertID = map.getLastInsertID();
} // if
if ( map.getUpdateCount() != -1 ) {
mLastUpdateCount = map.getUpdateCount();
} // if
} // if
}
ResultSet executePreparedQuery( String sql, Object[] params ) throws SQLException {
try {
if ( sLogger.isDebugEnabled() ) {
sLogger.debug( "executePreparedQuery: " + sql );
sLogger.debug( "executePreparedQuery-params: " + Arrays.asList( params ) );
} // if
SoapJdbcResultSetMetaData metadata = mCachedMetadata.get( sql );
ExecutePreparedQuery query = new ExecutePreparedQuery();
query.setUsername( mUser );
query.setPassword( mPassword );
query.setDatabase( mDatabaseName );
query.setSql( sql );
query.setParam( prepareParameter( params ) );
query.setWithMetadata( metadata == null );
prepareHTTPClient();
processResultSet( sql, mPort.executePreparedQuery( query ).get_return(), metadata );
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
return mLastResultSet;
}
private ArrayOfString prepareParameter( Object[] params ) {
ArrayOfString parameters = new ArrayOfString();
for ( Object object : params ) {
if ( object instanceof Boolean ) {
Boolean b = (Boolean) object;
parameters.addValue( b ? "1" : "0" );
} else {
parameters.addValue( object == null ? null : object.toString() );
} // if
} // for
return parameters;
}
int executePreparedUpdate( String sql, Object[] params ) throws SQLException {
try {
if ( sLogger.isDebugEnabled() ) {
sLogger.debug( "executePreparedUpdate: " + sql );
sLogger.debug( "executePreparedUpdate-params: " + Arrays.asList( params ) );
} // if
ExecutePreparedUpdate query = new ExecutePreparedUpdate();
query.setUsername( mUser );
query.setPassword( mPassword );
query.setDatabase( mDatabaseName );
query.setSql( sql );
query.setParam( prepareParameter( params ) );
prepareHTTPClient();
processResultSet( sql, mPort.executePreparedUpdate( query ).get_return(), null );
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
return mLastUpdateCount;
}
int executeUpdate( String sql ) throws SQLException {
try {
if ( sLogger.isDebugEnabled() ) {
sLogger.debug( "executeUpdate: " + sql );
} // if
ExecuteUpdate query = new ExecuteUpdate();
query.setUsername( mUser );
query.setPassword( mPassword );
query.setDatabase( mDatabaseName );
query.setSql( sql );
prepareHTTPClient();
processResultSet( sql, mPort.executeUpdate( query ).get_return(), null );
return mLastUpdateCount;
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
}
boolean execute( String sql ) throws SQLException {
try {
if ( sLogger.isDebugEnabled() ) {
sLogger.debug( "execute: " + sql );
} // if
SoapJdbcResultSetMetaData metadata = mCachedMetadata.get( sql );
Execute query = new Execute();
query.setUsername( mUser );
query.setPassword( mPassword );
query.setDatabase( mDatabaseName );
query.setSql( sql );
query.setWithMetadata( metadata == null );
prepareHTTPClient();
processResultSet( sql, mPort.execute( query ).get_return(), metadata );
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
return false;
}
SoapJdbcResultSet getResultSet() {
return mLastResultSet;
}
int getUpdateCount() {
return mLastUpdateCount;
}
void addBatch( String sql ) {
mBatch.add( new BatchStatement( sql ) );
}
void addBatch( SoapJdbcPreparedStatement stmt, Object[] params ) {
mBatch.add( new BatchStatement( stmt, params ) );
}
void clearBatch() {
mBatch.clear();
}
int[] executeBatch() {
return null;
}
String getDatabaseProductVersion() throws SQLException {
try {
GetDatabaseProductVersion name = new GetDatabaseProductVersion();
name.setUsername( mUser );
name.setPassword( mPassword );
name.setDatabase( mDatabaseName );
prepareHTTPClient();
return mPort.getDatabaseProductVersion( name ).get_return();
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
}
String getDatabaseProductName() throws SQLException {
try {
GetDatabaseProductName name = new GetDatabaseProductName();
name.setUsername( mUser );
name.setPassword( mPassword );
name.setDatabase( mDatabaseName );
prepareHTTPClient();
return mPort.getDatabaseProductName( name ).get_return();
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
}
Boolean lowerCaseTableNames() throws SQLException {
try {
LowerCaseTableNames name = new LowerCaseTableNames();
name.setUsername( mUser );
name.setPassword( mPassword );
name.setDatabase( mDatabaseName );
prepareHTTPClient();
return mPort.lowerCaseTableNames( name ).get_return();
} catch ( Exception e ) {
throw new SoapJdbcSQLException( "Remote Exception occured during database operation",
SoapJdbcSQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, e );
} // try
}
public String getURL() {
return mURL;
} // getURL
Integer getLastInsertID() {
return mLastInsertID;
} // getLastInsertID
private void prepareHTTPClient() {
String proxyHost = System.getProperty( PROXY_HOST );
if ( proxyHost != null ) {
int proxyPort = Integer.parseInt( System.getProperty( PROXY_PORT ) );
String proxyUsername = System.getProperty( PROXY_USERNAME );
String proxyPassword = System.getProperty( PROXY_PASSWORD );
ProxyProperties proxyProperties = new ProxyProperties();
proxyProperties.setProxyName( proxyHost );
proxyProperties.setProxyPort( proxyPort );
if ( proxyUsername != null ) {
proxyProperties.setUserName( proxyUsername );
proxyProperties.setPassWord( proxyPassword );
} // if
// in order to makesure that we use HTTP 1.0
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.HTTP_PROTOCOL_VERSION, HTTPConstants.HEADER_PROTOCOL_10 );
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.PROXY, proxyProperties );
} else {
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.HTTP_PROTOCOL_VERSION, HTTPConstants.HEADER_PROTOCOL_11 );
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.CHUNKED, Boolean.TRUE );
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.MC_GZIP_REQUEST, Boolean.TRUE );
} // if
// enable compression
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.MC_ACCEPT_GZIP, Boolean.TRUE );
mPort._getServiceClient().getOptions().setProperty( HTTPConstants.HEADER_CACHE_CONTROL_NOCACHE, Boolean.TRUE );
}
private static class BatchStatement {
private String mSqlToExecute;
private SoapJdbcPreparedStatement mStatementToExecute;
private Object[] mParams;
private BatchStatement( String sql ) {
mSqlToExecute = sql;
}
private BatchStatement( SoapJdbcPreparedStatement stmt, Object[] params ) {
mStatementToExecute = stmt;
mParams = params;
}
}
} // end of class SoapJdbcConnection