Java tutorial
package net.sourceforge.squirrel_sql.fw.sql; /* * Copyright (C) 2001-2004 Colin Bell * colbell@users.sourceforge.net * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library 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. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ import java.beans.PropertyChangeListener; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; import java.util.Calendar; import java.util.Date; import java.util.concurrent.*; import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory; import net.sourceforge.squirrel_sql.fw.dialects.DialectType; import net.sourceforge.squirrel_sql.fw.util.PropertyChangeReporter; import net.sourceforge.squirrel_sql.fw.util.StringManager; import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory; import net.sourceforge.squirrel_sql.fw.util.log.ILogger; import net.sourceforge.squirrel_sql.fw.util.log.LoggerController; import org.apache.commons.lang.StringUtils; /** * This represents a connection to an SQL server. it is basically a wrapper around * <TT>java.sql.Connection</TT>. * * @author <A HREF="mailto:colbell@users.sourceforge.net">Colin Bell</A> */ public class SQLConnection implements ISQLConnection { private ISQLDriver _sqlDriver; /** Internationalized strings for this class. */ private static final StringManager s_stringMgr = StringManagerFactory.getStringManager(SQLConnection.class); /** Logger for this class. */ private final static ILogger s_log = LoggerController.createLogger(SQLConnection.class); /** The <TT>java.sql.Connection</TT> this object is wrapped around. */ private Connection _conn; /** Connectiopn properties specified when connection was opened. */ private final SQLDriverPropertyCollection _connProps; private boolean _autoCommitOnClose = false; private Date _timeOpened; private Date _timeClosed; /** Object to handle property change events. */ private transient PropertyChangeReporter _propChgReporter; private SQLDatabaseMetaData metaData = null; public SQLConnection(Connection conn, SQLDriverPropertyCollection connProps, ISQLDriver sqlDriver) { super(); _sqlDriver = sqlDriver; if (conn == null) { throw new IllegalArgumentException("SQLConnection == null"); } _conn = conn; _connProps = connProps; _timeOpened = Calendar.getInstance().getTime(); metaData = new SQLDatabaseMetaData(this); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#close() */ public void close() throws SQLException { ExecutorService executorService = Executors.newSingleThreadExecutor(); int timeoutSeconds = 2; try { Runnable task = new Runnable() { @Override public void run() { _closeIntern(); } }; Future<?> future = executorService.submit(task); future.get(timeoutSeconds, TimeUnit.SECONDS); } catch (TimeoutException e) { String message = "The database connection took longer than " + timeoutSeconds + " seconds to close. " + "Maybe closing will succeed later but SQuirreL stops waiting to stay responsive for user interaction."; s_log.error(message, e); } catch (Throwable e) { s_log.error("Error while closing connection", e); } finally { _conn = null; } } private void _closeIntern() { try { SQLException savedEx = null; if (_conn != null) { s_log.debug("Closing connection"); try { if (!_conn.getAutoCommit()) { if (_autoCommitOnClose) { _conn.commit(); } else { _conn.rollback(); } } } catch (SQLException ex) { savedEx = ex; } _conn.close(); _conn = null; _timeClosed = Calendar.getInstance().getTime(); if (savedEx != null) { s_log.debug("Connection close failed", savedEx); throw savedEx; } s_log.debug("Connection closed successfully"); } } catch (SQLException e) { throw new RuntimeException(e); } } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#commit() */ public void commit() throws SQLException { validateConnection(); _conn.commit(); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#rollback() */ public void rollback() throws SQLException { validateConnection(); _conn.rollback(); } /** * Retrieve the properties specified when connection was opened. This can be <TT>null</TT>. * * @return Connection properties. */ public SQLDriverPropertyCollection getConnectionProperties() { return _connProps; } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getAutoCommit() */ public boolean getAutoCommit() throws SQLException { validateConnection(); return _conn.getAutoCommit(); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#setAutoCommit(boolean) */ public void setAutoCommit(boolean value) throws SQLException { validateConnection(); final Connection conn = getConnection(); final boolean oldValue = conn.getAutoCommit(); if (oldValue != value) { _conn.setAutoCommit(value); getPropertyChangeReporter().firePropertyChange(IPropertyNames.AUTO_COMMIT, oldValue, value); } } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getCommitOnClose() */ public boolean getCommitOnClose() { return _autoCommitOnClose; } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getTransactionIsolation() */ public int getTransactionIsolation() throws SQLException { validateConnection(); return _conn.getTransactionIsolation(); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#setTransactionIsolation(int) */ public void setTransactionIsolation(int value) throws SQLException { validateConnection(); _conn.setTransactionIsolation(value); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#setCommitOnClose(boolean) */ public void setCommitOnClose(boolean value) { _autoCommitOnClose = value; } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#createStatement() */ public Statement createStatement() throws SQLException { validateConnection(); return _conn.createStatement(); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#prepareStatement(java.lang.String) */ public PreparedStatement prepareStatement(String sql) throws SQLException { validateConnection(); return _conn.prepareStatement(sql); } /** * Retrieve the time that this connection was opened. Note that this time is the time that this * <TT>SQLConnection</TT> was created, not the time that the <TT>java.sql.Connection</TT> object that it is * wrapped around was opened. * * @return Time connection opened. */ public Date getTimeOpened() { return _timeOpened; } /** * Retrieve the time that this connection was closed. If this connection is still opened then <TT>null</TT> * will be returned.. * * @return Time connection closed. */ public Date getTimeClosed() { return _timeClosed; } /** * Retrieve the metadata for this connection. * * @return The <TT>SQLMetaData</TT> object. */ public SQLDatabaseMetaData getSQLMetaData() { return metaData; } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getConnection() */ public Connection getConnection() { /* This is extremely useful when trying to track down Swing UI freezing. * However, it currently fills the log which obscures other debug * messages even though UI performance is acceptable, so it is commented * out until it is needed later. if (s_log.isDebugEnabled()) { try { if (SwingUtilities.isEventDispatchThread() ) { throw new Exception(); } } catch (Exception e) { s_log.debug("GUI thread doing database work", e); } } */ return _conn; } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getCatalog() */ public String getCatalog() throws SQLException { validateConnection(); return getConnection().getCatalog(); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#setCatalog(java.lang.String) */ public void setCatalog(String catalogName) throws SQLException { validateConnection(); final Connection conn = getConnection(); final String oldValue = conn.getCatalog(); final DialectType dialectType = DialectFactory.getDialectType(metaData); if (!StringUtils.equals(oldValue, catalogName)) { setDbSpecificCatalog(dialectType, catalogName); getPropertyChangeReporter().firePropertyChange(IPropertyNames.CATALOG, oldValue, catalogName); } } /** * Decides which setCatalog method to call. Different databases have special requirements for this method * so this just determines the database type and redirects to the appropriate db-specific or generic * method. * * @param dialectType * the type of database * @param catalogName * the catalog name to use * @throws SQLException * if an error occurs */ private void setDbSpecificCatalog(DialectType dialectType, String catalogName) throws SQLException { switch (dialectType) { case MSSQL: setMSSQLServerCatalog(catalogName); break; case INFORMIX: setInformixCatalog(catalogName); break; default: setGenericDbCatalog(catalogName); break; } ; } /** * @param catalogName * @throws SQLException */ private void setGenericDbCatalog(String catalogName) throws SQLException { final Connection conn = getConnection(); conn.setCatalog(catalogName); } /** * MS SQL Server throws an exception if the catalog name contains a period without it being quoted. * * @param catalogName * the catalog name to use * @throws SQLException * if an error occurs */ private void setMSSQLServerCatalog(final String catalogName) throws SQLException { final Connection conn = getConnection(); // Bug #1995728 // MS-SQL is inconsistent with regard to setting the current catalog. If you have a database with // periods or spaces, then in some cases you must surround the catalog with quotes. For example, // if you have a catalog named 'db with spaces' you must execute the following SQL: // // use "db with spaces" // // However, the same is not always true for the JDBC API method Connection.setCatalog. For some old // versions of Microsoft drivers, you must quote the catalog as well. But for newer versions of the // driver, you must not quote the catalog. So here, we attempt to use the unquoted version first, then // if that fails, we will try quoting it. try { conn.setCatalog(catalogName); return; } catch (SQLException e) { s_log.error("Connection.setCatalog yielded an exception for catalog (" + catalogName + ") :" + e.getMessage() + " - will try quoting the catalog next.", e); } conn.setCatalog(quote(catalogName)); } /** * Work-around for Informix catalog switching bugs. * * @param catalogName * the catalog name to use * @throws SQLException * if an error occurs */ private void setInformixCatalog(String catalogName) throws SQLException { final Connection conn = getConnection(); Statement stmt = null; String sql = "DATABASE " + catalogName; try { stmt = conn.createStatement(); stmt.execute(sql); } catch (SQLException e) { s_log.error("setInformixCatalog: failed to change database with the database SQL directive: " + sql); } finally { SQLUtilities.closeStatement(stmt); } // finally, try to set the catalog, which appears to be a NO-OP in the Informix driver. conn.setCatalog(catalogName); } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getWarnings() */ public SQLWarning getWarnings() throws SQLException { validateConnection(); return _conn.getWarnings(); } /** * Add a listener for property change events. * * @param lis * The new listener. */ public void addPropertyChangeListener(PropertyChangeListener listener) { if (listener != null) { getPropertyChangeReporter().addPropertyChangeListener(listener); } else { s_log.debug("Attempted to add a null PropertyChangeListener"); } } /** * Remove a property change listener. * * @param lis * The listener to be removed. */ public void removePropertyChangeListener(PropertyChangeListener listener) { if (listener != null) { getPropertyChangeReporter().removePropertyChangeListener(listener); } else { s_log.debug("Attempted to remove a null PropertyChangeListener"); } } protected void validateConnection() throws SQLException { if (_conn == null) { throw new SQLException(s_stringMgr.getString("SQLConnection.noConn")); } } /** * Retrieve the object that reports on property change events. If it doesn't exist then create it. * * @return PropertyChangeReporter object. */ private synchronized PropertyChangeReporter getPropertyChangeReporter() { if (_propChgReporter == null) { _propChgReporter = new PropertyChangeReporter(this); } return _propChgReporter; } private String quote(String str) { // Bug #1995728 - Don't add quotes to an already quoted identifier if (str.startsWith("\"")) { return str; } String identifierQuoteString = ""; try { identifierQuoteString = getSQLMetaData().getIdentifierQuoteString(); } catch (SQLException ex) { s_log.debug("DBMS doesn't supportDatabasemetaData.getIdentifierQuoteString", ex); } if (identifierQuoteString != null && !identifierQuoteString.equals(" ")) { return identifierQuoteString + str + identifierQuoteString; } return str; } /** * @see net.sourceforge.squirrel_sql.fw.sql.ISQLConnection#getSQLDriver() */ public ISQLDriver getSQLDriver() { return _sqlDriver; } }