Set - 7

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.