Attachment #67871 for bug #143837

This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

View | Details | Raw Unified | Return to bug 143837
Collapse All | Expand All




    <!-- ACTUAL CHANGES BEGIN HERE: -->

    <changes>
        <change>
            <api name="database_explorer_api"/>
            <summary>Add ability to test a connection for validity</summary>
            <version major="1" minor="29"/>
            <date day="15" month="8" year="2008"/>
            <author login="davidvc"/>
            <compatibility addition="yes"/>
            <description>
                This change allows you to make sure a DatabaseConnection is valid
                before you use it to issue commands to the database.
            </description>
            <class package="org.netbeans.api.db.explorer" name="DatabaseConnection"/>
            <issue number="143837"/>
        </change>
        <change>
            <api name="database_explorer_api"/>
            <summary>Add support for always quoting and for unquoting SQL identifiers.</summary>




     method.
   </p>
  </usecase>
 <usecase id="test-connection" name="Test a database connection for validity">
   <p>
    You may want to test to make sure a connection is open and valid before you
    get the underlying physical JDBC connection.  This can be achieved using the
    <a href="@TOP@org/netbeans/api/db/explorer/DatabaseConnection.html#getJDBCConnection(boolean)">
        DatabaseConnection.getJDBCConnection(boolean test)</a>
    method, which validates the underlying connection before returning it.  If the
    connection is invalid, it marks the DatabaseConnection as invalid and returns null.
   </p>
  </usecase>
  <usecase id="connections-combo-box" name="Displaying the database connections in the UI">
   <p>
    A component which provides database functionality (such as the SQL Editor




     *         ConnectionManager.
     */
    public Connection getJDBCConnection() {
        return getJDBCConnection(false);
    }
    
    /**
     * Get a JDBCConnection, with an option to test the connection before returning it
     * 
     * @param test If this is set to true, the connection is tested for validity.  If
     * it is invalid, the DatabaseConnection is marked as no longer connected and 
     * null is returned.  If this is set to false, then no test is performed
     * 
     * @return the physical connection or null if not connected (or invalid if test is true)
     *
     * @throws IllegalStateException if this connection is not added to the
     *         ConnectionManager.
     * 
     * @since 1.29
     */
    public Connection getJDBCConnection(boolean test) {
        if (!ConnectionList.getDefault().contains(delegate)) {
            throw new IllegalStateException("This connection is not added to the ConnectionManager."); // NOI18N
        }
        
        return delegate.getJDBCConnection(test);        
    }

    /**

Lines 35-37 Link Here

(-)a/db/src/org/netbeans/modules/db/explorer/Bundle.properties (+1 lines)
35	#	35	#
36	# Portions Copyrighted 2008 Sun Microsystems, Inc.	36	# Portions Copyrighted 2008 Sun Microsystems, Inc.
37	ERR_CONN_ALREADY_EXISTS = The connection with the name {0} already exists	37	ERR_CONN_ALREADY_EXISTS = The connection with the name {0} already exists
		38	MSG_TestFailed = The connection {0} does not appear to have a valid connection to the database: {1}




            }
        }
        return useDriver;
    }

    public Connection getJDBCConnection(boolean test) {
        if (test) {
            if (! test()) {
                try {
                    this.disconnect();
                } catch (DatabaseException e) {
                    LOGGER.log(Level.FINE, null, e);
                }

                return null;
            }
        }

        return getJDBCConnection();
    }

    private boolean test() {
        try {
            Connection conn = getJDBCConnection();
            if (conn == null || conn.isClosed()) {
                return false;
            }

            // Send a command to the server, if it fails we know the connection is invalid.
            conn.getMetaData().getTables(null, null, " ", new String[] { "TABLE" }).close();
        } catch (SQLException e) {
            LOGGER.log(Level.INFO, NbBundle.getMessage(DatabaseConnection.class,
                    "MSG_TestFailed", this.getName(), e.getMessage()));
            LOGGER.log(Level.FINE, null, e);
            return false;
        }
        return true;

    }

     private Collection getOpenConnections() {





package org.netbeans.api.db.explorer;

import java.sql.Connection;
import org.netbeans.modules.db.test.Util;
import org.netbeans.modules.db.util.DBTestBase;


        
        ConnectionManager.getDefault().removeConnection(dbconn);
        assertEquals(0, ConnectionManager.getDefault().getConnections().length);
    }

    public void testGetJDBCConnectionWithTest() throws Exception {
        DatabaseConnection dbconn = getDatabaseConnection();
        ConnectionManager.getDefault().disconnect(dbconn);
        assertNull(dbconn.getJDBCConnection(true));
        ConnectionManager.getDefault().connect(dbconn);
        assertNotNull(dbconn.getJDBCConnection(true));
        assertNotNull(dbconn.getJDBCConnection(false));

        dbconn.getJDBCConnection(true).close();
        assertNotNull(dbconn.getJDBCConnection(false));
        assertNull(dbconn.getJDBCConnection(true));
        assertNull(dbconn.getJDBCConnection(false));
    }
}





import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import org.netbeans.modules.db.util.DBTestBase;

/**
 *
 * @author Andrei Badea
 */
public class DatabaseConnectionTest extends DBTestBase {

    public DatabaseConnectionTest(String testName) {
        super(testName);




    private static int    unquotedCaseRule = RULE_UNDEFINED;
    private static int    quotedCaseRule = RULE_UNDEFINED;

    private JDBCDriver jdbcDriver;
    private DatabaseConnection dbConnection;
    
    protected Connection conn;


        super(name);
    }

    protected JDBCDriver getJDBCDriver() throws Exception{
        if (jdbcDriver == null) {
            jdbcDriver = JDBCDriver.create("derbydriver", "derbydriver", driverClass, new URL[] {driverJarUrl});
            assertNotNull(jdbcDriver);

     * Get the DatabaseConnection for the configured Java DB database.  This
     * method will create and register the connection the first time it is called
     */
    protected DatabaseConnection getDatabaseConnection() throws Exception {
        if (dbConnection == null) {
            JDBCDriver driver = getJDBCDriver();


Return to bug 143837

Lines 105-110 Link Here

(-)a/db/apichanges.xml (+14 lines)
105	<!-- ACTUAL CHANGES BEGIN HERE: -->	105	<!-- ACTUAL CHANGES BEGIN HERE: -->
106		106
107	<changes>	107	<changes>
		108	<change>
		109	<api name="database_explorer_api"/>
		110	<summary>Add ability to test a connection for validity</summary>
		111	<version major="1" minor="29"/>
		112	<date day="15" month="8" year="2008"/>
		113	<author login="davidvc"/>
		114	<compatibility addition="yes"/>
		115	<description>
		116	This change allows you to make sure a DatabaseConnection is valid
		117	before you use it to issue commands to the database.
		118	</description>
		119	<class package="org.netbeans.api.db.explorer" name="DatabaseConnection"/>
		120	<issue number="143837"/>
		121	</change>
108	<change>	122	<change>
109	<api name="database_explorer_api"/>	123	<api name="database_explorer_api"/>
110	<summary>Add support for always quoting and for unquoting SQL identifiers.</summary>	124	<summary>Add support for always quoting and for unquoting SQL identifiers.</summary>

Lines 339-344 Link Here

(-)a/db/arch.xml (+10 lines)
339	method.	339	method.
340	</p>	340	</p>
341	</usecase>	341	</usecase>
		342	<usecase id="test-connection" name="Test a database connection for validity">
		343	<p>
		344	You may want to test to make sure a connection is open and valid before you
		345	get the underlying physical JDBC connection. This can be achieved using the
		346	<a href="@TOP@org/netbeans/api/db/explorer/DatabaseConnection.html#getJDBCConnection(boolean)">
		347	DatabaseConnection.getJDBCConnection(boolean test)</a>
		348	method, which validates the underlying connection before returning it. If the
		349	connection is invalid, it marks the DatabaseConnection as invalid and returns null.
		350	</p>
		351	</usecase>
342	<usecase id="connections-combo-box" name="Displaying the database connections in the UI">	352	<usecase id="connections-combo-box" name="Displaying the database connections in the UI">
343	<p>	353	<p>
344	A component which provides database functionality (such as the SQL Editor	354	A component which provides database functionality (such as the SQL Editor

Lines 219-228 Link Here

(-)a/db/src/org/netbeans/api/db/explorer/DatabaseConnection.java (-1 / +20 lines)
219	* ConnectionManager.	219	* ConnectionManager.
220	*/	220	*/
221	public Connection getJDBCConnection() {	221	public Connection getJDBCConnection() {
		222	return getJDBCConnection(false);
		223	}
		224
		225	/**
		226	* Get a JDBCConnection, with an option to test the connection before returning it
		227	*
		228	* @param test If this is set to true, the connection is tested for validity. If
		229	* it is invalid, the DatabaseConnection is marked as no longer connected and
		230	* null is returned. If this is set to false, then no test is performed
		231	*
		232	* @return the physical connection or null if not connected (or invalid if test is true)
		233	*
		234	* @throws IllegalStateException if this connection is not added to the
		235	* ConnectionManager.
		236	*
		237	* @since 1.29
		238	*/
		239	public Connection getJDBCConnection(boolean test) {
222	if (!ConnectionList.getDefault().contains(delegate)) {	240	if (!ConnectionList.getDefault().contains(delegate)) {
223	throw new IllegalStateException("This connection is not added to the ConnectionManager."); // NOI18N	241	throw new IllegalStateException("This connection is not added to the ConnectionManager."); // NOI18N
224	}	242	}
225	return delegate.getJDBCConnection();	243
		244	return delegate.getJDBCConnection(test);
226	}	245	}
227		246
228	/**	247	/**

Lines 212-217 Link Here

(-)a/db/src/org/netbeans/modules/db/explorer/DatabaseConnection.java (+35 lines)
212	}	212	}
213	}	213	}
214	return useDriver;	214	return useDriver;
		215	}
		216
		217	public Connection getJDBCConnection(boolean test) {
		218	if (test) {
		219	if (! test()) {
		220	try {
		221	this.disconnect();
		222	} catch (DatabaseException e) {
		223	LOGGER.log(Level.FINE, null, e);
		224	}
		225
		226	return null;
		227	}
		228	}
		229
		230	return getJDBCConnection();
		231	}
		232
		233	private boolean test() {
		234	try {
		235	Connection conn = getJDBCConnection();
		236	if (conn == null \|\| conn.isClosed()) {
		237	return false;
		238	}
		239
		240	// Send a command to the server, if it fails we know the connection is invalid.
		241	conn.getMetaData().getTables(null, null, " ", new String[] { "TABLE" }).close();
		242	} catch (SQLException e) {
		243	LOGGER.log(Level.INFO, NbBundle.getMessage(DatabaseConnection.class,
		244	"MSG_TestFailed", this.getName(), e.getMessage()));
		245	LOGGER.log(Level.FINE, null, e);
		246	return false;
		247	}
		248	return true;
		249
215	}	250	}
216		251
217	private Collection getOpenConnections() {	252	private Collection getOpenConnections() {

Lines 41-47 Link Here

(-)a/db/test/unit/src/org/netbeans/api/db/explorer/DatabaseConnectionTest.java (-2 / +16 lines)
41		41
42	package org.netbeans.api.db.explorer;	42	package org.netbeans.api.db.explorer;
43		43
44	import org.netbeans.modules.db.explorer.ConnectionList;	44	import java.sql.Connection;
45	import org.netbeans.modules.db.test.Util;	45	import org.netbeans.modules.db.test.Util;
46	import org.netbeans.modules.db.util.DBTestBase;	46	import org.netbeans.modules.db.util.DBTestBase;
47		47
Lines 117-121 Link Here
117		117
118	ConnectionManager.getDefault().removeConnection(dbconn);	118	ConnectionManager.getDefault().removeConnection(dbconn);
119	assertEquals(0, ConnectionManager.getDefault().getConnections().length);	119	assertEquals(0, ConnectionManager.getDefault().getConnections().length);
120	}	120	}
		121
		122	public void testGetJDBCConnectionWithTest() throws Exception {
		123	DatabaseConnection dbconn = getDatabaseConnection();
		124	ConnectionManager.getDefault().disconnect(dbconn);
		125	assertNull(dbconn.getJDBCConnection(true));
		126	ConnectionManager.getDefault().connect(dbconn);
		127	assertNotNull(dbconn.getJDBCConnection(true));
		128	assertNotNull(dbconn.getJDBCConnection(false));
		129
		130	dbconn.getJDBCConnection(true).close();
		131	assertNotNull(dbconn.getJDBCConnection(false));
		132	assertNull(dbconn.getJDBCConnection(true));
		133	assertNull(dbconn.getJDBCConnection(false));
		134	}
121	}	135	}

Lines 43-55 Link Here

(-)a/db/test/unit/src/org/netbeans/modules/db/explorer/DatabaseConnectionTest.java (-2 / +2 lines)
43		43
44	import java.beans.PropertyChangeEvent;	44	import java.beans.PropertyChangeEvent;
45	import java.beans.PropertyChangeListener;	45	import java.beans.PropertyChangeListener;
46	import org.netbeans.modules.db.test.TestBase;	46	import org.netbeans.modules.db.util.DBTestBase;
47		47
48	/**	48	/**
49	*	49	*
50	* @author Andrei Badea	50	* @author Andrei Badea
51	*/	51	*/
52	public class DatabaseConnectionTest extends TestBase {	52	public class DatabaseConnectionTest extends DBTestBase {
53		53
54	public DatabaseConnectionTest(String testName) {	54	public DatabaseConnectionTest(String testName) {
55	super(testName);	55	super(testName);

Lines 89-96 Link Here

(-)a/db/test/unit/src/org/netbeans/modules/db/util/DBTestBase.java (-4 / +4 lines)
89	private static int unquotedCaseRule = RULE_UNDEFINED;	89	private static int unquotedCaseRule = RULE_UNDEFINED;
90	private static int quotedCaseRule = RULE_UNDEFINED;	90	private static int quotedCaseRule = RULE_UNDEFINED;
91		91
92	private static JDBCDriver jdbcDriver;	92	private JDBCDriver jdbcDriver;
93	private static DatabaseConnection dbConnection;	93	private DatabaseConnection dbConnection;
94		94
95	protected Connection conn;	95	protected Connection conn;
96		96
Lines 98-104 Link Here
98	super(name);	98	super(name);
99	}	99	}
100		100
101	protected static JDBCDriver getJDBCDriver() throws Exception{	101	protected JDBCDriver getJDBCDriver() throws Exception{
102	if (jdbcDriver == null) {	102	if (jdbcDriver == null) {
103	jdbcDriver = JDBCDriver.create("derbydriver", "derbydriver", driverClass, new URL[] {driverJarUrl});	103	jdbcDriver = JDBCDriver.create("derbydriver", "derbydriver", driverClass, new URL[] {driverJarUrl});
104	assertNotNull(jdbcDriver);	104	assertNotNull(jdbcDriver);
Lines 113-119 Link Here
113	* Get the DatabaseConnection for the configured Java DB database. This	113	* Get the DatabaseConnection for the configured Java DB database. This
114	* method will create and register the connection the first time it is called	114	* method will create and register the connection the first time it is called
115	*/	115	*/
116	protected static DatabaseConnection getDatabaseConnection() throws Exception {	116	protected DatabaseConnection getDatabaseConnection() throws Exception {
117	if (dbConnection == null) {	117	if (dbConnection == null) {
118	JDBCDriver driver = getJDBCDriver();	118	JDBCDriver driver = getJDBCDriver();
119		119