The Database module provides the DatabaseExplorerAPI which allows access to the database connections and drivers defined in the Database Explorer. It allows a client to retrieve the connection list and their properties, to create new connections and remove existing ones. The Database Explorer also manages a list of JDBC drivers used to connect to databases. The API provides access to these drivers and allows to create new and remove existing drivers.
The DatabaseExplorerLayerAPI allows for the declarative registration of database connections and JDBC drivers in the module layer. Database runtimes (which are representations of an instance of a database server) can also be registered in the layer.
.dbschema files with own actions by registering them in the
Loaders/text/x-dbschema/Actions folder. Note that this folder is actually provided by the
.sql files with own actions by registering them in the
Loaders/text/x-sql/Actions folder. Note that this folder is actually provided by the
This module also provides a SQLSupportAPI which provides utilities for working with SQL such as quoting identifiers.
SQLIdentifiers now returns a fallback
Quoter if there is no
The fallback quoter supports unquoting most common identifier
quoting formats, uses SQL-99 quotes for quoting and quotes all
identifiers, that don't start with an ascii character or
contain ascii non-characters or non-numbers.
Users can enable or disable using of scrollable cursors, which can make queries faster, but also can cause problems for some drivers.
Users can specify connection properties in the UI now. The properties can be retrieved using new method DatabaseConnection.getConnectionProperties(). There is also a new factory method DatabaseConnection.create() that accepts an object with additional connection properties.
Allow to specify the display name of the connection as it shows under the Databases node. Added new factory method to create database connection with given display name.
Currently there is no easy way to get the JDBCDriver instance that a
DatabaseConnection will use / used to connect to a database. A
DatabaseConnection.getJDBCDriver() method should be added.
An external module can register JDBC drivers. A typical example is a module which provides integration with a database server. In this case the module contains the JDBC driver for that database server and uses the Database Explorer API to add it do the Database Explorer.
Another client of this API could be a module providing integration with a J2EE application server. Sometimes a J2EE application server bundles a database server for improving the out-of-the-box experience. When the server is registered in the IDE the JDBC drivers for the bundled database server are added to the Database Explorer.
The drivers are registered by making calls on JDBCDriverManager or by registering an XML file which describes the driver in the module layer. The XML file is described by the JDBC Driver DTD. An example of a registration file describing the JDBC driver for PostgreSQL follows:
<?xml version='1.0'?> <!DOCTYPE driver PUBLIC '-//NetBeans//DTD JDBC Driver 1.0//EN' 'http://www.netbeans.org/dtds/jdbc-driver-1_0.dtd'> <driver> <name value='postgresql-7'/> <display-name value='PostgreSQL (v7.0 and later)'/> <class value='org.postgresql.Driver'/> <urls> <url value='file:/folder1/folder2/drivers/pg74.1jdbc3.jar'/> </urls> </driver>
This file should be registered in the
Databases/JDBCDrivers folder of the module layer.
To addres a bundled JAR inside the IDE the nbinst protocol can be used in the URLs:
You can use the JDBCDriver.getDriver() method to obtain a reference to the underlying JDBC Driver instance. This is useful if you want to use the registered drivers but create your own JDBC connections independent of the Database Explorer.
When creating a new connection the JDBC driver which it should use can be specified. A list of all the registered JDBC drivers can be retrieved using JDBCDriverManager.getDrivers().
An external module can register new database runtimes. A database runtime
is an abstraction of a database server instance
(usually bundled with the IDE, an integration module or with a J2EE server). It allows a database
server instance to be started and stopped when a connection to this
instance is made in the IDE. Database runtimes are represented by the
SPI interface and are registered in the
Databases/Runtimes of the module layer.
A module can create new database connections (for example to a bundled database). New connections can be added by calling DatabaseConnection.create() to create a new DatabaseConnection instance and then ConnectionManager.addConnection() to add the connection to the Database Explorer.
New connections can also be added by registering them in the module layer. The format of the registration file is described by the Database Connection DTD. An example of a registration file describing a connection to a PostgreSQL database follows:
<?xml version='1.0'?> <!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'> <connection> <driver-class value='org.postgresql.Driver'/> <driver-name value='postgres-7'/> <database-url value='jdbc:postgresql:test'/> <schema value='public'/> <user value='test'/> <password value='cGFzc3dvcmQ='/> </connection>
This file should be registered in the
of the module layer.
The password element is optional, but if it is included, its value must be the Base64 encoding of the UTF-8 representation of the password. Note that the UTF-8 representation of passwords composed entirely of ASCII characters is the same as their ASCII representation, so for such passwords all that needs to be done is to convert them to Base64.
Base64 encoding serves as a simple scrambling to prevent accidental revelation of the password. It is not indended to offer any real security. You can protect the password by assigning appropriate file protections to the connection XML file.
Sometimes the list of connections needs to be displayed somewhere else in the IDE than the Runtime tab. A typical example is the SQL Editor, which allows the user to select the database connection which the SQL statement will be executed against in a combo box in the editor toolbar. The list of connections can be obtained by calling ConnectionManager.getConnections(), which returns an array of DatabaseConnection instances.
The client usually needs to show the display name of the connection. The display name can be retrieved using the DatabaseConnection.getDisplayName() method.
Sometimes a client needs to retrieve the connection properties, such as the driver class.
An example could be a module for a J2EE server creating a connection pool. The properties can
be retrieved using the
methods of the
Usually when displaying a list of connections (usually in a combo box), the last item is "New Connection", which displays the standard New Database Connection dialog of the Database Explorer. This can be achieved by calling one of the ConnectionManager.showAddConnectionDialog() methods.
A user of this API may want to remove a connection from the list of connections registered by the Database Explorer. This is done using ConnectionManager.removeConnection()
A component which provides database functionality (such as the SQL Editor)
will need to connect to a database. This can be achieved using the
method and the
java.sql.Connection instance can be retrieved using the
If you want to connect to the database without showing a dialog or any kind of UI, you can use the DatabaseConnection.connect() method.
You may want to test to make sure the underlying physical JDBC connection obtained from a DatabaseConnection is either valid or null. This is done using the DatabaseConnection.getJDBCConnection(boolean test) method, which validates the underlying connection before returning it. If the connection is invalid, it marks the DatabaseConnection as disconnected and returns null.
A component which provides database functionality (such as the SQL Editor
or a module providing support for data sources) will need to let the user
select the a database connection, usually through a combo box.
This can be achieved using the
JComboBox passed to the method will be filled with the list of connections as returned by
ConnectionManager.getConnections(), followed by a separator
and a New Database Connection item which will display the dialog for adding a new database connection when selected.
A component might need to allow database tables from the Database Explorer to
be dragged to a visual editor. An API is provided in
DataFlavors for database objects and nested classes
encapsulating those database objects during a drag and drop transfer.
A component might need support for working with SQL identifiers. In particular, it's important to know when to quote a SQL identifier. The SQLIdentifiers.Quoter class is provided for this.
Read more about the implementation in the answers to architecture questions.