Set - 7

Question 1 :

How do I check what table-like database objects (table, view, temporary table, alias) are present in a particular database?

Answer :

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();
}


Question 2 :

What does ResultSet actually contain? Is it the actual data of the result or some links to databases? If it is the actual data then why can't we access it after connection is closed?

Answer :

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.


Question 3 :

How do I extract a BLOB from a database?

Answer :

A BLOB (Binary Large OBject) is essentially an array of bytes (byte[]), stored in the database. You extract the data in two steps:

1. Call the getBlob method of the Statement class to retrieve a java.sql.Blob object
2. Call either getBinaryStream or getBytes in the extracted Blob object to retrieve the java byte[] which is the Blob object.

Note that a Blob is essentially a pointer to a byte array (called LOCATOR in database-talk), so the java.sql.Blob object essentially wraps a byte pointer. Thus, you must extract all data from the database blob before calling commit or

private void runGetBLOB()
{
try
{ // Prepare a Statement:
PreparedStatement stmnt = conn.prepareStatement("select aBlob from BlobTable");

// Execute
ResultSet rs = stmnt.executeQuery();

while(rs.next())
{
try
{
// Get as a BLOB
Blob aBlob = rs.getBlob(1);
byte[] allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
}
catch(Exception ex)
{
// The driver could not handle this as a BLOB...
// Fallback to default (and slower) byte[] handling
byte[] bytes = rs.getBytes(1);
}
}

// Close resources
rs.close();
stmnt.close();

}
catch(Exception ex)
{
this.log("Error when trying to read BLOB: " + ex);
}
}


Question 4 :

How do I extract the SQL statements required to move all tables and views from an existing database to another database?

Answer :

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.


Question 5 :

How do I check what table types exist in a database?

Answer :

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();
}


Question 6 :

What is the advantage of using a PreparedStatement?

Answer :

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.


Question 7 :

How do I find all database stored procedures in a database?

Answer :

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();
}


Question 8 :

How can I investigate the physical structure of a database?

Answer :

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();
}


Question 9 :

How does the Java Database Connectivity (JDBC) work?

Answer :

The JDBC is used whenever a Java application should communicate with a relational database for which a JDBC driver exists. JDBC is part of the Java platform standard; all visible classes used in the Java/database communication are placed in package java.sql.

Main JDBC classes:
* DriverManager. Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under jdbc (such as odbc or dbAnywhere/dbaw) will be used to establish a database Connection.
* Driver. The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly.
* Connection. Interface with all methods for contacting a database
* Statement. Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
* ResultSet. The answer/result from a statement. A ResultSet is a fancy 2D list which encapsulates all outgoing results from a given SQL query.


Question 10 :

What is Metadata and why should I use it?

Answer :

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.


Question 11 :

How do I create a database connection?

Answer :

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");