Images

JDBC - PART II


JDBC
JDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBCis oriented towards relational databases.
The Java Database Connectivity (JDBC) is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases –SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC provides a call-level API forSQL-based database access.
JDBC technology allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data. With a JDBC technology-enabled driver, you can connect all corporate data even in a heterogeneous environment.


4. QUERYING AND MODIFYING DATA
Introduction
PreparedStatement: This is an interface of java.sql package which extends Statement interface. If you want to execute Statement object many times then we should use PreparedStatement object as it reduces the execution time. PreparedStatement object is faster than the Statement object as it is precompiled. In PreparedStatement we use IN parameter whose values are not known when the Sql statement is created. So we use "?" as a IN parameter and we also know it by the name of parameter marker. In this interface the modification has been done on the methods execute, executeQuery and executeUpdate. These methods are modified in such a way so that they take no argument.
An element in a batch consists of a parameterized command and an associated set of parameters when a PreparedStatement is used. The batch update facility is used with a PreparedStatement to associate multiple sets of input parameter values with a single PreparedStatement object. The sets of parameter values together with their associated parameterized update command can then be sent to the underlying DBMS engine for execution as a single unit.
The example below inserts two new employee records into a database as a single batch. The PreparedStatement.setXXX() methods are used to create each parameter set (one for each employee), while the PreparedStatement.addBatch() method adds a set of parameters to the current batch.
// turn off autocommit
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)");
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();
// submit the batch for execution
int[] updateCounts = stmt.executeBatch();
Finally, PreparedStatement.executeBatch() is called to submit the updates to the DBMS. Calling PreparedStatement.executeBatch() clears the statement's associated list of batch elements. The array returned by PreparedStatement.executeBatch() contains an element for each set of parameters in the batch, similar to the case for Statement. Each element either contains an update count or the generic `success' indicator .
Error handling in the case of PreparedStatement objects is the same as error handling in the case of Statement objects. Some drivers may stop processing as soon as an error occurs, while others may continue processing the rest of the batch. As for Statement, the number of elements in the array returned by BatchUpdateException.getUpdateCounts() indicates whether or not the driver continues processing after a failure. The same three array element values are possible, as for Statement. The order of the entries in the array is the same order as the order in which elements were added to the batch.
Description of program:
In this program we are going to insert data in the database by using the setObject method of PreparedStatement interface. Before going into the details of the program we should firstly need to establish the connection with MySQL database by the help of JDBC driver. After establishing the connection now we will insert the data in setObject method. If the data gets added in the database table then it will display a message "Record is added in the table." otherwise it will show "SQL statement is not executed!".
Description of code:
prepareStatement(String sql):
This method returns the PreparedStatement object for sending the parameterized SQL statement to the database that contains the pre-compiled SQL statement. Here the pre- compiled means once the statement has been compiled then it will not compile the same thing again. It takes the string type arguments which contains one or more '?' parameter placeholders.
setObject(int par_index, object obj):
It is used for setting the values of parameterized index by using the given object in this method. It takes two arguments to given below:
par_index: It specifies the parameter like: the first is 1, second is 2, ......
object obj: It contains the parameter values to given by the users.
Here is the code of program:
import java.sql.*;
public class PreparedStatementSetObject{
public static void main(String[] args) {
System.out.println("Prepared Statement Set Array Example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
PreparedStatement prest = con.prepareStatement("insert emp_sal values(?,?)");
prest.setObject(1,"Sushil");
prest.setObject(2,15000);
int n = prest.executeUpdate();
System.out.println(n + " Record is added in the table.");
con.close();
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
The PreparedStatement interface inherits from Statement and differs from it in two ways:
1. Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared."
2. The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead, the statement has a question mark ("?") as a placeholder for each IN parameter. The "?" is also known as a parameter marker. An application must set a value for each question mark in a prepared statement before executing the prepared statement.
Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.
Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a set of methods that are needed for setting the values to be sent to the database in place of the placeholders for IN parameters. Also, the three methods execute, executeQuery, and executeUpdate are modified so that they take no argument. The Statement forms of these methods (the forms that take an SQL statementparameter) should never be used with a PreparedStatement object.

4. QUERYING AND MODIFYING DATA
Methods of the PreparedStatement Interface
The PreparedStatement interface creates an object that represents a precompiled SQL statement.
A SQL statement is pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. Note: The setter methods for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.
java.sql package

public interface PreparedStatement

extends Statement
PreparedStatement interface methods
Method return value typeMethod
voidclearParameters()
Clears the current parameter values immediately.
booleanexecute()
Executes any kind of SQL statement.
ResultSetexecuteQuery() 
Executes the SQL query in this PreparedStatement object and returns the result set generated by the query.
intexecuteUpdate() 
Executes the SQL INSERT, UPDATE or DELETE statement in this PreparedStatement object.
voidsetBigDecimal (int parameterIndex, BigDecimal x) 
Sets the designated parameter to a java.lang.BigDecimal value.
voidsetBinaryStream(int parameterIndex, InputStream x, int length)
voidsetBoolean (int parameterIndex, boolean x) 
Sets the designated parameter to a Java boolean value. JDBC driver converts this to an SQL SMALLINT value when it sends it to the database.
voidsetBlob(int i, Blob x) JDBC 2.0 
Sets a BLOB parameter.
voidsetDate(int parameterIndex, Date x) 
Sets the designated parameter to a java.sql.Date value.
voidsetDouble(int parameterIndex, double x) 
Sets the designated parameter to a Java double value. JDBC driver converts this to an SQL DECIMAL value when it sends it to the database.
voidsetFloat(int parameterIndex, float x) 
Sets the designated parameter to a Java float value. When a BigDecimal is converted to float, if the BigDecimal is too large to represent as a float, it will be converted to FLOAT.NEGATIVE_INFINITY or FLOAT.POSITIVE_INFINITY as appropriate.
voidsetInt (int parameterIndex, int x) 
Sets the designated parameter to a Java int value.
voidsetLong(int parameterIndex, long x) 
Sets the designated parameter to a Java long value.
voidsetNull (int parameterIndex, int sqlType) 
Sets the designated parameter to SQL NULL.
voidsetObject(int parameterIndex, Object x, int targetSqlType) 
Sets the value of the designated parameter with the given object.
voidsetShort (int parameterIndex, short x) 
Sets the designated parameter to a Java short value.
voidsetString (int parameterIndex, String x) 
Sets the designated parameter to a Java String value.
voidsetTime (int parameterIndex, Time x) 
Sets the designated parameter to a java.sql.Time value.
voidsetTimestamp (int parameterIndex, Timestamp x) 
Sets the designated parameter to a java.sql.Timestamp value
voidsetTimestamp (int parameterIndex, Timestamp x) 
Sets the designated parameter to a java.sql.Timestamp value

4. QUERYING AND MODIFYING DATA
Retrieving Rows
SQL Select statement:
The SELECT statement is used to select data from a table.
Syntax: Select column_names FROM table_name;
The result from a SQL query is stored in a resultset. The SELECT statement has mainly three clauses.
1). Select
2.) From
3). Where
The Select specifies the table columns that are retrieved. The From clause tells from where the tables has been accessed. The Where clause specifies which tables are used. The Where clause is optional, if not used then all the table rows will be selected.
We can see that we have used semicolon at the end of the select statement. It is used to separate each SQL statement in database systems which helps us to execute more than one SQL statement in the same call to the server.
Example
import java.io.*;
import java.sql.*;
public class InsertRecords{
public static void main(String[] args) {
System.out.println("Insert records example using prepared statement!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql:
//localhost:3306/jdbctutorial","root","root");
try{
String sql = "INSERT movies VALUES(?,?)";
PreparedStatement prest = con.prepareStatement(sql);
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter movie name:");
String mov = bf.readLine();
prest.setString(1, mov);
System.out.println("Enter releases year of movie:");
int year = Integer.parseInt(bf.readLine());
prest.setInt(2, year);
int count = prest.executeUpdate();
System.out.println(count + "row(s) affected");
con.close();
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

4. QUERYING AND MODIFYING DATA
INSERTING Rows:
This statement allows you to insert a single or multiple records into the database. We can specify the name of the column in which we want to insert the data.
Syntax: Insert into table_name values (value1, value2..);
The Insert statement has mainly three clauses.
1). Insert: It specifies which table column has to be inserted in the table.
2). Into : It tells in which the data will be stored.
3). Values: In this we insert the values we have to insert.
We can also specify the columns for which we want to insert data.

QUERYING AND MODIFYING DATA
Deleting Records using the Prepared Statement
This section helps us for deleting the records from the database table by using thePreparedStatement interface of the java.sql package. Sometimes, some records we have entered becomes useless after sometime so we need to delete those records. We can do it very easily by using the simple Sql query. The given example provides the facility for deleting the records from the database table. Brief description below: Please go through this example very carefully.
Description of program:
In this program we are going to delete the records from the 'movies' table that released in the year 1985. For this, first of all we will establish the connection with MySQL database by using the JDBC driver. After establishing the connection we will pass the SQL statement in the prepareStatemet method which returns the PreparedStatement object and we will set the releases year of movies in the setInt method by using the PreparedStatement object. If it records gets delete then the executeUpdate method will return the number of deleted records. This method will execute the SQL statement that may be INSERT, UPDATE or DELETE statement. If any problems arises with SQL statement then it will display a message "SQL statement is not executed!".
Here is the code of program:
import java.sql.*;

public class DeleteRecords{
public static void main(String[] args) {
System.out.println("Delete records example using prepared statement!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
String sql = "DELETE FROM movies where year_made = ?";
PreparedStatement prest = con.prepareStatement(sql);
prest.setInt(1,1985);
int del = prest.executeUpdate();
System.out.println("Number of deleted records: " + del);
con.close();
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

MANAGING DATABASE TRANSACTIONS
INTRODUCTION
Understanding transactions
Application programmers benefit from developing their applications on platforms such asJava 2 Enterprise Edition (J2EE) that support transactions. A transaction-based system simplifies application development because it frees the developer from the complex issues of failure recovery and multi-user programming. Transactions are not limited to single databases or single sites. Distributed transactions can simultaneously update multiple databases across multiple sites.
A programmer typically divides the total work of an application into a series of units. Each unit of work is a separate transaction. As the application progresses, the underlying system ensures that each unit of work, each transaction, fully completes without interference from other processes. If not, it rolls back the transaction and completely undoes whatever work the transaction had performed.
Characteristics of transactions
Typically, transactions refer to operations that access a shared resource like a database.All access to a database is performed in the context of a transaction. All transactions share the following characteristics:
• Atomicity
• Consistency
• Isolation
• Durability
These characteristics are denoted by the acronym ACID.
A transaction often consists of more than a single operation. Atomicity requires that either all or none of the operations of a transaction are performed for the transaction to be considered complete. If any of a transaction's operations cannot be performed, then none of them can be performed.
Consistency refers to resource consistency. A transaction must transition the database from one consistent state to another. The transaction must preserve the database's semantic and physical integrity.
Isolation requires that each transaction appear to be the only transaction currently manipulating the database. Other transactions can run concurrently. However, a transaction must not see the intermediate data manipulations of other transactions until and unless they successfully complete and commit their work. Because of interdependencies among updates, a transaction can get an inconsistent view of the database were it to see just a subset of another transaction's updates. Isolation protects a transaction from this sort of data inconsistency.
Transaction isolation is qualified by varying levels of concurrency permitted by the database. The higher the isolation level, the more limited the concurrency extent. The highest level of isolation occurs when all transactions can be serialized. That is, the database contents look as if each transaction ran by itself to completion before the next transaction started. However, some applications can tolerate a reduced level of isolation for a higher degree of concurrency. Typically, these applications run a greater number of concurrent transactions even if transactions are reading data that may be partially updated and perhaps inconsistent.
Lastly, durability means that updates made by committed transactions persist in the database regardless of failure conditions. Durability guarantees that committed updates remain in the database despite failures that occur after the commit operation and that databases can be recovered after a system or media failure.
Transaction attributes
EJBs that use bean-managed transaction have transaction attributes associated with each method of the bean. The attribute value tells the container how it must manage the transactions that involve this bean. There are six different transaction attributes that can be associated with each method of a bean. This association is done at deployment time by the Application Assembler or Deployer.
These attributes are:
• Required—This attribute guarantees that the work performed by the associated method is within a global transaction context. If the caller already has a transaction context, then the container uses the same context. If not, the container begins a new transaction automatically. This attribute permits easy composition of multiple beans and co-ordination of the work of all the beans using the same global transaction.
• RequiresNew—This attribute is used when the method does not want to be associated with an existing transaction. It ensures that the container begins a new transaction.
• Supports—This attribute permits the method to avoid using a global transaction. This must only be used when a bean's method only accesses one transaction resource—or no transaction resources—and does not invoke another enterprise bean. It is used solely for optimization, because it avoids the cost associated with global transactions. When this attribute is set and there is already a global transaction, the EJB Container invokes the method and have it join the existing global transaction. However, if this attribute is set, but there is no existing global transaction, the Container starts a local transaction for the method, and that local transaction completes at the end of the method.
• NotSupported—This attribute also permits the bean to avoid using a global transaction. When this attribute is set, the method must not be in a global transaction. Instead, the EJB Container suspends any existing global transaction and starts a local transaction for the method, and the local transaction completes at the conclusion of the method.
• Mandatory—It is recommended that this attribute not be used. Its behavior is similar to Requires, but the caller must already have an associated transaction. If not, the container throws a javax.transaction.TransactionRequiredException. This attribute makes the bean less flexible for composition because it makes assumptions about the caller's transaction.
• Never—It is recommended that this attribute not be used. However, if used, the EJB Container starts a local transaction for the method. The local transaction completes at the conclusion of the method.

MANAGING DATABASE TRANSACTIONS
Committing a Transaction
When you set the auto-commit mode to false, the operation perfomed by SQL statementsare not reflected permanently in a database. You need to explicitly call the commit()method of the Connection interface to reflect the changes made by the transactions in the database. All the SQL statements that appear between two commit() methods are treated as a single transaction and are executed as asingle unit.
Example:
import java.sql.*;

public class createTrans
{
public static void main(String args[])
{
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con=DriverManager.getConnection(“jdbc:odbc:MyDataSource”, ” ”, “ ”);
PreparedStatement ps=con.preparedStatement(“INSERT into product(p_id,p_desc) VALUES (?,?)”);
Ps.setInt(1,1008);
Ps.setString(2,”Hard Disk”);
Int firstctr=ps.executeUpdate();
System.out.println(“First row inserted but not commited”);
ps=con.preparedStatement(“INSERT into product(p_id,p_desc) VALUES (?,?)”);
Ps.setInt(1,1009);
Ps.setString(2,”Hard Disk2”);
Int firstctr=ps.executeUpdate();
System.out.println(“Second row inserted but not commited”);
Con.commit();
System.out.println(“Transaction Committed”);
Ps.close();
Con.close();
}
catch(Exception e)
{
System.out.println(“Error :” +e);
}
}
}
Overridden JDBC methods
Java.sql.Connection.commit()
As defined in the JDBC API, this method commits all work that was performed on a JDBC connection since the previous commit() or rollback(), and releases all database locks.
If a global transaction is associated with the current thread of execution do not use this method. If the global transaction is not a container-managed transaction, that is the application manages its own transactions, and a commit is required use the JTA API to perform the commit rather than invoking commit() directly on the JDBC connection.
Java.sql.Connection.rollback()
As defined in the JDBC API, this method rolls back all work that was performed on a JDBCconnection since the previous commit() or rollback(), and releases all database locks.
If a global transaction is associated with the current thread of execution do not use this method. If the global transaction is not a container-managed transaction, that is the application manages its own transactions, and a rollback is required use the JTA API to perform the rollback rather than invoking rollback() directly on the JDBC connection.
Java.sql.Connection.close()
As defined in the JDBC API, this method closes the database connection and all JDBC resources associated with the connection.
If the thread is associated with a transaction this call simply notifies the JDBC pool that work on the connection is complete. The JDBC pool releases the connection back to the connection pool once the transaction has completed. JDBC connections opened by the JDBC pool cannot be closed explicitly by an application.
Java.sql.Connection.setAutoCommit(boolean)
As defined in the JDBC API, this method is used to set the auto commit mode of a transaction. The setAutoCommit() method allows Java applications to either:
• Execute and commit all SQL statements as individual transactions (when set to true). This is the default mode, or
• Explicitly invoke commit() or rollback() on the connection (when set to false).
If the thread is associated with a transaction, the JDBC pool turns off the auto-commit mode for all connections factoried in the scope of a partition's transaction service transaction. This is because the transaction service must control transaction completion. If an application is involved with a transaction, and it attempts to set the auto commit mode to true, the java.sql.SQLException() will be raised.

PERFORMING BATCH UPDATES
Introduction
The batch update facility allows multiple update operations to be submitted to a data source for processing at once. Submitting multiple updates together, instead of individually, can greatly improve performance. Statement, PreparedStatement, and CallableStatement objects can be used to submit batch update

PERFORMING BATCH UPDATES
Implementing Batch updates in JDBC
With batch updates, instead of updating rows of a DB table one at a time, you can directJDBC to execute a group of updates at the same time. Statements that can be included in the same batch of updates are known as batchable statements.
If a statement has input parameters or host expressions, you can include that statement only in a batch that has other instances of the same statement. This type of batch is known as a homogeneous batch. If a statement has no input parameters, you can include that statement in a batch only if the other statements in the batch have no input parameters or host expressions. This type of batch is known as a heterogeneous batch. Two statements that can be included in the same batch are known as batch compatible.
Use the following Statement methods for creating, executing, and removing a batch of SQL updates:
• addBatch
• executeBatch
• clearBatch
Use the following PreparedStatement and CallableStatement method for creating a batch of parameters so that a single statement can be executed multiple times in a batch, with a different set of parameters for each execution.
• addBatch
Restrictions on executing statements in a batch:
• If you try to execute a SELECT statement in a batch, a BatchUpdateException is thrown.
• A CallableStatement object that you execute in a batch can contain output parameters. However, you cannot retrieve the values of the output parameters. If you try to do so, a BatchUpdateException is thrown.
• You cannot retrieve ResultSet objects from a CallableStatement object that you execute in a batch. A BatchUpdateException is not thrown, but the getResultSet method invocation returns a null value.
To make batch updates using several statements with no input parameters, follow these basic steps:
1. For each SQL statement that you want to execute in the batch, invoke the addBatch method.
2. Invoke the executeBatch method to execute the batch of statements.
3. Check for errors. If no errors occurred:
a. Get the number of rows that were affect by each SQL statement from the array that the executeBatch invocation returns. This number does not include rows that were affected by triggers or by referential integrity enforcement.
b. If AutoCommit is disabled for the Connection object, invoke the commit method to commit the changes.
If AutoCommit is enabled for the Connection object, the IBM DB2 Driver for JDBC and SQLJadds a commit method at the end of the batch.
To make batch updates using a single statement with several sets of input parameters, follow these basic steps:
1. Invoke the createStatement method to create a Statement object.
2. For each set of input parameter values:
a. Execute setXXX methods to assign values to the input parameters.
b. Invoke the addBatch method to add the set of input parameters to the batch.
3. Invoke the executeBatch method to execute the statements with all sets of parameters.
4. Check for errors. If no errors occurred:
a. Get the number of rows that were updated by each execution of the SQL statementfrom the array that the executeBatch invocation returns.
b. If AutoCommit is disabled for the Connection object, invoke the commit method to commit the changes.
If AutoCommit is enabled for the Connection object, the IBM DB2 Driver for JDBC and SQLJadds a commit method at the end of the batch.
In the following code fragment, two sets of parameters are batched. An UPDATE statementthat takes two input parameters is then executed twice, once with each set of parameters. The numbers to the right of selected statements correspond to the previously-described steps.
try {

…
PreparedStatement prepStmt = con.prepareStatement(
"UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();

prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch();
for (int i=0; i < numUpdates.length; i++) {
if (numUpdates[i] == SUCCESS_NO_INFO)
System.out.println("Execution " + i +
": unknown number of rows updated");
else
System.out.println("Execution " + i +
"successful: " numUpdates[i] + " rows updated");
}
con.commit();
} catch(BatchUpdateException b) {
// process BatchUpdateException
}
Multi-row INSERT: When you execute multiple INSERT statements in a batch, and the database server supports multi-row INSERT, the IBM DB2 Driver for JDBC and SQLJ uses multi-row INSERT to insert the rows. Multi-row INSERT can provide better performance than individual INSERT statements.
You cannot execute a multi-row insert operation by including a multi-row INSERT statement in your JDBC application.

PERFORMING BATCH UPDATES
Description of batch updates
The batch update facility allows a Statement object to submit a set of heterogeneous update commands together as a single unit, or batch, to the underlying DBMS. In the example below all of the update operations required to insert a new employee into a fictitious company database are submitted as a single batch.
// turn off autocommit
con.setAutoCommit(false);

Statement stmt = con.createStatement();

stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");

// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
In the example, autocommit mode is disabled to prevent the driver from committing the transaction when Statement.executeBatch() is called. Disabling autocommit allows an application to decide whether or not to commit the transaction in the event that an error occurs and some of the commands in a batch cannot be processed successfully. For this reason, autocommit should always be turned off when batch updates are done. The commit behavior of executeBatch is always implementation defined when an error occurs and autocommit is true.
To keep our discussion of batch updates general, we define the term element to refer to an individual member of a batch. As we have seen, an element in a batch is just a simple command when a Statement object is being used. Although we are focusing on usingStatement objects to do batch updates in this section, the discussion that follows applies toPreparedStatment and CallableStatement objects, as well.
In the new JDBC API, a Statement object has the ability to keep track of a list of commands -or batch-that can be submitted together for execution. When a Statement object is created, its associated batch is empty-the batch contains no elements. TheStatement.addBatch() method adds an element to the calling statement's batch. The method Statement.clearBatch() (not shown above) can be called to reset a batch if the application decides not to submit a batch of commands that has been constructed for a statement.
Successful execution
The Statement.executeBatch() method submits a statement's batch to the underlying data source for execution. Batch elements are executed serially (at least logically) in the order in which they were added to the batch. When all of the elements in a batch execute successfully, executeBatch() returns an integer array containing one entry for each element in the batch. The entries in the array are ordered according to the order in which the elements were processed (which, again, is the same as the order in which the elements were originally added to the batch). An entry in the array may have the following values:
1. If the value of an array entry is greater than or equal to zero, then the batch element was processed successfully and the value is an update count indicating the number of rows in the database that were effected by the element's execution.
2. A value of -2 indicates that a element was processed successfully, but that the number of effected rows is unknown.
Calling executeBatch() closes the calling Statement object's current result set if one is open. The statement's internal list of batch elements is reset to empty once executeBatch() returns. The behavior of the executeQuery, executeUpdate, or execute methods is implementation defined when a statement's batch is non-empty.
ExecuteBatch() throws a BatchUpdateException if any of the elements in the batch fail to execute properly, or if an element attempts to return a result set. Only DDL and DML commands that return a simple update count may be executed as part of a batch. When a BatchUpdateException is thrown, the BatchUpdateException.getUpdateCounts() method can be called to obtain an integer array of update counts that describes the outcome of the batch execution.

PERFORMING BATCH UPDATES
Exception-Handling in Batch Updates
A JDBC driver may or may not continue processing the remaining elements in a batch once execution of an element in a batch fails. However, a JDBC driver must always provide the same behavior when used with a particular DBMS. For example, a driver cannot continue processing after a failure for one batch, and not continue processing for another batch.
If a driver stops processing after the first failure, the array returned byBatchUpdateException.getUpdateCounts() will always contain fewer entries than there were elements in the batch. Since elements are executed in the order that they are added to the batch, if the array contains N elements, this means that the first N elements in the batch were processed successfully when executeBatch() was called.
When a driver continues processing in the presence of failures, the number of elements, N, in the array returned by BatchUpdateException.getUpdateCounts()is always equal to the number of elements in the batch. The following additional array value is returned when aBatchUpdateException is thrown and the driver continues processing after a failure:
A value indicates that the command or element failed to execute successfully. This value is also returned for elements that could not be processed for some reason-such elements fail implicitly.
JDBC drivers that do not continue processing after a failure never return -3 in an update count array. Drivers of this type simply return a status array containing an entry for each command that was processed successfully.
A JDBC technology based application can distinguish a JDBC driver that continues processing after a failure from one that does not by examining the size of the array returned by BatchUpdateException.getUpdateCounts(). A JDBC driver that continues processing always returns an array containing one entry for each element in the batch. AJDBC driver that does not continue processing after a failure will always return an array whose number of entries is less than the number of elements in the batch.

CREATING AND CALLING STORED PROCEDURES IN JDBC
Introduction
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, we will show you a simple example of what a stored procedure looks like and how it is invoked from JDBC.

CREATING AND CALLING STORED PROCEDURES IN JDBC
Creating a Stored Procedure
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code.Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Note: Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities.
This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for eachDBMS. For example, some use begin . . . end , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statementcreates a stored procedure:
create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME
The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:
String createProcedure = "create procedure SHOW_SUPPLIERS " +
"as " +
"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"order by SUP_NAME";
The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:
Statement stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.

CREATING AND CALLING STORED PROCEDURES IN JDBC
Calling a Stored Procedure from JDBC
JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedureSHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
The ResultSet rs will be similar to the following:
SUP_NAME
COF_NAME
Acme, Inc.
xyz
Acme, Inc.
asdf
Superior Coffee
wert
Superior Coffee
defrgt
The High Ground
xyz
Note that the method used to execute cs is executeQuery because cs calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method executeUpdate would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute theCallableStatement .
The class CallableStatement is a subclass of PreparedStatement, so a CallableStatementobject can take input parameters just as a PreparedStatement object can. In addition, aCallableStatement object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method execute are used rarely.

USING METADATA IN JDBC
Introduction
Developing performance-oriented JDBC applications is not easy. JDBC drivers do not throw exceptions to tell you when your code is running too slow.
This series of performance tips presents some general guidelines for improving JDBC application performance that have been compiled by examining the JDBC implementations of numerous shipping JDBC applications. These guidelines include:
• Using Database MetaData methods appropriately

• Retrieve only required data

• Select functions that optimize performance

• Manage connections and updates

USING METADATA IN JDBC
Using Database Metadata Interface
Because database metadata methods that generate ResultSet objects are slow compared to other JDBC methods, their frequent use can impair system performance. The guidelines in this section will help you optimize system performance when selecting and usingdatabase metadata.
Minimizing the Use of Database Metadata Methods
Compared to other JDBC methods, database metadata methods that generate ResultSetobjects are relatively slow. Applications should cache information returned from result sets that generate database metadata methods so that multiple executions are not needed.
Although almost no JDBC application can be written without database metadata methods, you can improve system performance by minimizing their use. To return all result column information mandated by the JDBC specification, a JDBC driver may have to perform complex queries or multiple queries to return the necessary result set for a single call to a database metadata method. These particular elements of the SQL language are performance expensive.
Applications should cache information from database metadata methods. For example, call getTypeInfo once in the application and cache away the elements of the result set that your application depends on. It is unlikely that any application uses all elements of the result set generated by a database metadata method, so the cache of information should not be difficult to maintain.
Avoiding Search Patterns
Using null arguments or search patterns in database metadata methods results in generating time-consuming queries. In addition, network traffic potentially increases due to unwanted results. Always supply as many non-null arguments to result sets that generate database metadata methods as possible.
Because database metadata methods are slow, applications should invoke them as efficiently as possible. Many applications pass the fewest non-null arguments necessary for the function to return success.
for example:
ResultSet WSrs = WSc.getTables (null, null, "WSTable", null);

should be:

ResultSet WSrs = WSc.getTables ("cat1", "johng", "WSTable",
"TABLE");
In the first getTables() call, the application probably wants to know if the table WSTableexists. Of course, a JDBC driver takes the call literally and interprets the request differently. A JDBC driver interprets the request as: return all tables, views, system tables, synonyms, temporary tables, or aliases that exist in any database schema inside any database catalog that are named ‘WSTable’.
The second call to getTables() more accurately reflects what the application wants to know. A JDBC driver interprets this request as: return all tables that exist in the ‘johng’ schema in the current catalog where the name is ‘WSTable’.
Clearly, a JDBC driver can process the second request much more efficiently than it can process the first request.
Sometimes, little information is known about the object for which you are requesting information. Any information that the application can send the driver when calling database metadata methods can result in improved performance and reliability.
Using a Dummy Query to Determine Table Characteristics
Avoid using getColumns() to determine characteristics about a table. Instead, use a dummy query with getMetadata().
Consider an application that allows the user to choose the columns that will be selected. Should the application use getColumns() to return information about the columns to the user or instead prepare a dummy query and call getMetadata()?
Case 1: GetColumns Method
ResultSet WSrc = WSc.getColumns (... "UnknownTable" ...);
// This call to getColumns() will generate a query to
// the system catalogs... possibly a join
// which must be prepared, executed, and produce
// a result set
. . .
WSrc.next();
string Cname = getString(4);
. . .
// user must retrieve N rows from the server
// N = # result columns of UnknownTable
// result column information has now been obtained
Case 2: GetMetadata Method
// prepare dummy query
PreparedStatement WSps = WSc.prepareStatement
("SELECT * from UnknownTable WHERE 1 = 0");
// query is never executed on the server - only prepared
ResultSetMetaData WSsmd=WSps.getMetaData();
int numcols = WSrsmd.getColumnCount();
...
int ctype = WSrsmd.getColumnType(n)
...
// result column information has now been obtained
In both cases, a query is sent to the server. But in Case 1, the query must be prepared and executed, the result description information must be formulated, and a result set of rows must be sent to the client. In Case 2, a simple query must be prepared and only result description information must be formulated. Clearly, Case 2 is the better performing model.
To somewhat complicate this discussion, let us consider a DBMS server that does not natively support preparing a SQL statement. The performance of Case 1 does not change, but the performance of Case 2 increases slightly because the dummy query must be evaluated instead of only prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and should execute without accessing table data. For this situation, method 2 still outperforms method 1.
In summary, always use result set metadata to retrieve table column information such as column names, column data types, and column precision and scale. Only use getColumns()when the requested information cannot be obtained from result set metadata (i.e. table column default values).

USING METADATA IN JDBC
Using the JDBC MetaData Interface
• ResultSet: ResultSetMetaData getMetaData()
• ResultSetMetaData provides information about the types and properties of the DDL properties of a ResultSet object
• ResultSetMetaData provides various methods for finding out information about the structure of a ResultSet:
• getColumnClassName(int col): gets fully-qualified Java class name to which a column value will be mapped; eg. Java.lang.Integer, etc.
• getColumnCount(): gets the number of columns in the ResultSet
• getColumnDisplaySize(int col): gets the normal maximum width in characters for column
• getColumnName(int col): gets the name of column
• int getColumnType(int col): gets the JDBC type (java.sql.Types) for the value stored in col; eg. Value 12 = JDBC VARCHAR, etc.
• getPrecision(int col): for numbers, gets the mantissa length, for others, gets the number of bytes for column

JDBC API
JDBC API Interface
The API interface is made up of 4 main interfaces:
• java.sql DriverManager
• java. sql .Connection
• java. sql. Statement
• java.sql.Resultset
In addition to these, the following support interfaces are also available to the developer:
• java.sql.Callablestatement
• java. sql. DatabaseMetaData
• java.sql.Driver
• java. sql. PreparedStatement
• java. sql .ResultSetMetaData
• java. sql. DriverPropertymfo
• java.sql.Date
• java.sql.Time
• java. sql. Timestamp
• java.sql.Types
• java. sql. Numeric
DriverManager
This is a very important class. Its main purpose is to provide a means of managing the different types of JDBC database driver On running an application, it is the DriverManager's responsibility to load all the drivers found in the system property j dbc . drivers. For example, this is where the driver for the Oracle database may be defined. This is not to say that a new driver cannot be explicitly stated in a program at runtime which is not included in jdbc.drivers. When opening a connection to a database it is the DriverManager' s role to choose the most appropriate driver from the previously loaded drivers.
Connection
When a connection is opened, this represents a single instance of a particular database session. As long as the connection remains open, SQL queries may be executed and results obtained. More detail on SQL can be found in later chapters and examples found inAppendix A. This interface can be used to retneve information regarding the table descriptions, and any other information about the database to which you are connected. By using Connection a commit is automatic after the execution of a successful SQL statement,unless auto commit has been explicitly disabled. In this case a commit command must follow each SQL statement, or changes will not be saved. An unnatural disconnection from the database during an SQL statement will automatically result in the rollback of that query, and everything else back to the last successful commit.
Statement
The objective of the Statement interface is to pass to the database the SQL string for execution and to retrieve any results from the database in the form of a ResultSet. Only one ResultSet can be open per statement at any one time. For example, two ResultSets cannot be compared to each other if both ResultSets stemmed from the same SQL statement. If an SQL statement is re-issued for any reason, the old Resultset is automatically closed.
ResultSet
A ResultSet is the retrieved data from a currently executed SQL statement. The data from the query is delivered in the form of a table. The rows of the table are returned to the program in sequence. Within any one row, the multiple columns may be accessed in any order
A pointer known as a cursor holds the current retrieved record. When a ResUltSet is returned, the cursor is positioned before the first record and the next command (equivalent to the embedded SQL FETCH command) pulls back the first row. A ResultSet cannot go backwards. In order to re-read a previously retrieved row, the program must close the ResultSet and re-issue the SQL statement. Once the last row has been retrieved the statement is considered closed, and this causes the Resu1tSet to be automatically closed.
CallableStatement
This interface is used to execute previously stored SQL procedures in a way which allows standard statement issues over many relational DBMSs. Consider the SQL example:
SELECT cname FROM tnaine WHERE cname = var;
If this statement were to be stored, the program would need a way to pass the parameter var into the callable procedure. Parameters passed into the call are referred to sequentially, by number. When defining a variable type in JDBC the program must ensure that the type corresponds with the database field type for IN and OUT parameters.
DatabaseMetaData
This interface supplies information about the database as a whole. MetaData refers to information held about data. The information returned is in the form of ResultSet5. Normal ResultSet methods, as explained previously, may be used in this instance. If metadata is not available for the particular request then an SQLException will occur
Driver
For each database driver a class that implements the Driver interface must be provided. When such a class is loaded it should register itself with the DriverManager, which will then allow it to be accessed by a program.
PreparedStatement
A PreparedStatement object is an SQL statement which is pre-compiled and stored. This object can then be executed multiple times much more efficiently than preparing and issuing the same statement each time it is needed. When defining a variable type in JDBC,the program must ensure that the type corresponds with the database field type for IN and OUT parameters.
ResultSetMetaData
This interface allows a program to determine types and properties in any columns in aResultSet. It may be used to find out a data type for a particular field before assigning its variable type.
DriverPropertyinfo
This class is only of interest to advanced programmers. Its purpose is to interact with a particular driver to determine any properties needed for connections.
Date
The purpose of the Date class is to supply a wrapper to the standard Java Date class which extends to allow JDBC to recognise an SQL DATE.
Time
The purpose of the Time class is to supply a wrapper to the standard Java Time class which extends to allow JDBC to recognise an SQL TIME.
Timestamp
The purpose of the Times tamp class is to supply a wrapper to the standard Java Date class which extends to allow JDBC to recognise an SQL TIMESTAMP
Types
The Types class determines any constants that are used to identify SQL types.
Numeric
The object of the Numeric class is to provide high precision in numeric computations that require fixed point resolution. Examples include monetary or encryption key applications. These equate to database SQL NUMERIC or DECIMAL types.
Driver Interface
The driver side of the JDBC layer is the part that interfaces to the actual database, and therefore is generally written by database vendors. Most developers only need to know how to install and use drivers. The JDBC Driver API defines a set of interfaces which have to be implemented by a vendor
JDBC is based on Microsoft's Open Database Connectivity (ODBC) interface which many of the mainstream databases have adopted. Therefore, a JDBCODBC bridge is supplied as part of JDBC, which allows most databases to be accessed before the Java driver is released. Although efficient and fast, it is recommended that the actual database JDBC driver is used rather than going through another level of abstraction with ODBC.
Developers have the power to develop and test applications that use the JDBC-ODBCbridge. If and when a proper driver becomes available they will be able to slot in the new driver and have the applications utilise it instantly, without the need for rewriting. However, do not assume the JDBC-ODBC bridge is a bad alternative. It is a small and very efficient way of accessing databases.
Application Areas
JDBC has been designed and implemented for use in connecting to databases. Fortunately, JDBC has made no restrictions, over and above the standard Java security mechanisms, for complete systems. To this end, a number of overall system configurations are feasible for accessing databases.
1. Java application which accesses local database
2. Java applet accesses server-based database
3. Database access from an applet via a stepping stone
Facebook
Google
Twitter
0

0 comments:

Subscribe to: Post Comments (Atom)