A messaging system in which clients address messages to a specific node in a content hierarchy, called a topic. Publishers and subscribers are generally anonymous and can dynamically publish or subscribe to the content hierarchy. The system takes care of distributing the messages arriving from a node’s multiple publishers to its multiple subscribers.
The database connection is created in 3 steps:
1. Find a proper database URL (see FAQ on JDBC URL)
2. Load the database driver
3. Ask the Java DriverManager class to open a connection to your database
In java code, the steps are realized in code as follows:
1. Create a properly formatted JDBR URL for your database. (See FAQ on JDBC URL for more information). A JDBC URL has the form jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
2.
try {
Class.forName(“my.database.driver”);
}
catch(Exception ex)
{
System.err.println(“Could not load database driver: ” + ex);
}
3. Connection conn = DriverManager.getConnection(“a.JDBC.URL”, “databaseLogin”, “databasePassword”);
Metadata (‘data about data’) is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns.
The JDBC view of a database internal structure can be seen in the image below.
* Several database objects (tables, views, procedures etc.) are contained within a Schema.
* Several schema (user namespaces) are contained within a catalog.
* Several catalogs (database partitions; databases) are contained within a DB server (such as Oracle, MS SQL
The DatabaseMetaData interface has methods for discovering all the Catalogs, Schemas, Tables and Stored Procedures in the database server. The methods are pretty intuitive, returning a ResultSet with a single String column; use them as indicated in the code below:
public static void main(String[] args) throws Exception
{
// Load the database driver – in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
// Open a connection to the database
Connection conn = DriverManager.getConnection(“[jdbcURL]“,
“[login]“, “[passwd]“);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all Catalogs
System.out.println(“\nCatalogs are called ‘” + dbmd.getCatalogTerm()
+ “‘ in this RDBMS.”);
processResultSet(dbmd.getCatalogTerm(), dbmd.getCatalogs());
// Get all Schemas
System.out.println(“\nSchemas are called ‘” + dbmd.getSchemaTerm()
+ “‘ in this RDBMS.”);
processResultSet(dbmd.getSchemaTerm(), dbmd.getSchemas());
// Get all Table-like types
System.out.println(“\nAll table types supported in this RDBMS:”);
processResultSet(“Table type”, dbmd.getTableTypes());
// Close the Connection
conn.close();
}
public static void processResultSet(String preamble, ResultSet rs)
throws SQLException
{
// Printout table data
while(rs.next())
{
// Printout
System.out.println(preamble + “: ” + rs.getString(1));
}
// Close database resources
rs.close();
}
Use the getProcedures method of interface java.sql.DatabaseMetaData to probe the database for stored procedures. The exact usage is described in the code below.
public static void main(String[] args) throws Exception
{
// Load the database driver – in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
// Open a connection to the database
Connection conn = DriverManager.getConnection(“[jdbcURL]“,
“[login]“, “[passwd]“);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all procedures.
System.out.println(“Procedures are called ‘”
+ dbmd.getProcedureTerm() +”‘ in the DBMS.”);
ResultSet rs = dbmd.getProcedures(null, null, “%”);
// Printout table data
while(rs.next())
{
// Get procedure metadata
String dbProcedureCatalog = rs.getString(1);
String dbProcedureSchema = rs.getString(2);
String dbProcedureName = rs.getString(3);
String dbProcedureRemarks = rs.getString(7);
short dbProcedureType = rs.getShort(8);
// Make result readable for humans
String procReturn = (dbProcedureType == DatabaseMetaData.procedureNoResult
? “No Result” : “Result”);
// Printout
System.out.println(“Procedure: ” + dbProcedureName
+ “, returns: ” + procReturn);
System.out.println(” [Catalog | Schema]: [" + dbProcedureCatalog
+ " | " + dbProcedureSchema + "]“);
System.out.println(” Comments: ” + dbProcedureRemarks);
}
// Close database resources
rs.close();
conn.close();
}
For SQL statements that are executed repeatedly, using a PreparedStatement object would almost always be faster than using a Statement object. This is because creating a PreparedStatement object by explicitly giving the SQL statement causes the statement to be precompiled within the database immediately. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan – it simply runs the statement.
Typically, PreparedStatement objects are used for SQL statements that take parameters. However, they can also be used with repeatedly executed SQL statements that do not accept parameters.
Use the getTableTypes method of interface java.sql.DatabaseMetaData to probe the database for table types. The exact usage is described in the code below.
public static void main(String[] args) throws Exception
{
// Load the database driver – in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
// Open a connection to the database
Connection conn = DriverManager.getConnection(“[jdbcURL]“,
“[login]“, “[passwd]“);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all table types.
ResultSet rs = dbmd.getTableTypes();
// Printout table data
while(rs.next())
{
// Printout
System.out.println(“Type: ” + rs.getString(1));
}
// Close database resources
rs.close();
conn.close();
}
The operation is performed in 9 steps:
1. Open a connection to the source database. Use the DriverManager class.
2. Find the entire physical layout of the current database. Use the DatabaseMetaData interface.
3. Create DDL SQL statements for re-creating the current database structure. Use the DatabaseMetaData interface.
4. Build a dependency tree, to determine the order in which tables must be setup. Use the DatabaseMetaData interface.
5. Open a connection to the target database. Use the DriverManager class.
6. Execute all DDL SQL statements from (3) in the order given by (4) in the target database to setup the table and view structure. Use the PreparedStatement interface.
7. If (6) threw exceptions, abort the entire process.
8. Loop over all tables in the physical structure to generate DML SQL statements for re-creating the data inside the table. Use the ResultSetMetaData interface.
9. Execute all DML SQL statements from (8) in the target database.
A ResultSet is an interface. Its implementation depends on the driver and hence ,what it “contains” depends partially on the driver and what the query returns.
For example with the Odbc bridge what the underlying implementation layer contains is an ODBC result set. A Type 4 driver executing a stored procedure that returns a cursor – on an oracle database it actually returns a cursor in the databse. The oracle cursor can however be processed like a ResultSet would be from the client. Closing a connection closes all interaction with the database and releases any locks that might have been obtained in the process.
Use java.sql.DatabaseMetaData to probe the database for metadata. Use the getTables method to retrieve information about all database objects (i.e. tables, views, system tables, temporary global or local tables or aliases). The exact usage is described in the code below.
NOTE! Certain JDBC drivers throw IllegalCursorStateExceptions when you try to access fields in the ResultSet in the wrong order (i.e. not consecutively). Thus, you should not change the order in which you retrieve the metadata from the ResultSet.
public static void main(String[] args) throws Exception
{
// Load the database driver – in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
// Open a connection to the database
Connection conn = DriverManager.getConnection(“[jdbcURL]“,
“[login]“, “[passwd]“);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all dbObjects. Replace the last argument in the getTables
// method with objectCategories below to obtain only database
// tables. (Sending in null retrievs all dbObjects).
String[] objectCategories = {“TABLE”};
ResultSet rs = dbmd.getTables(null, null, “%”, null);
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbObjectType = rs.getString(4);
// Printout
System.out.println(“” + dbObjectType + “: ” + dbObjectName);
System.out.println(” Catalog: ” + dbObjectCatalog);
System.out.println(” Schema: ” + dbObjectSchema);
}
// Close database resources
rs.close();
conn.close();
}