JDBC Driver
JDBC Driver Manager
The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.
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 jdbc. 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.
The JDBC API defines the Java interfaces and classes that programmers use to connect to databases
and send queries. A JDBC driver implements these interfaces and classes for a particular DBMS vendor.
A Java program that uses the JDBC API loads the specified driver for a particular DBMS before it actually connects to a database. The JDBC DriverManager class then sends all JDBC API calls to the loaded driver. JDBC Driver
This topic defines the Java(TM) Database Connectivity (JDBC) driver types. Driver types are used to
categorize the technology used to connect to the database. A JDBC driver vendor uses these types to
describe how their product operates. Some JDBC driver types are better suited for some applications
than others.
Types of JDBC drivers
This topic defines the Java(TM) Database Connectivity (JDBC) driver types. Driver types are used to
categorize the technology used to connect to the database. A JDBC driver vendor uses these types to
describe how their product operates. Some JDBC driver types are better suited for some applications
than others.
There are four types of JDBC drivers known as: - JDBC-ODBC bridge plus ODBC driver, also called Type 1.
- Native-API, partly Java driver, also called Type 2.
- JDBC-Net, pure Java driver, also called Type 3.
- Native-protocol, pure Java driver, also called Type 4.
Type 1 Driver- the JDBC-ODBC bridge
The JDBC type 1 driver, also known as the JDBC-ODBC bridge is a database driver implementation
that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into
ODBC function calls. The bridge is usually used when there is no pure-Java driver available for a particular database.
The driver is implemented in the sun.jdbc.odbc.JdbcOdbcDriver class and comes with the Java 2 SDK,
Standard Edition. The driver is platform-dependent as it makes use of ODBC which in turn depends
on native libraries of the operating system. Also, using this driver has got other dependencies such as
ODBC must be installed on the computer having the driver and the database which is being connected
to must support an ODBC driver. Hence the use of this driver is discouraged if the alternative of a
pure-Java driver is available.
Type 1 is the simplest of all but platform specific i.e only to Microsoft platform.
A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some
ODBC native code and in many cases native database client code must be loaded on each client machine
that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic
installation and downloading of a Java technology application is not important. For information on the
JDBC-ODBC bridge driver provided by Sun, see JDBC-ODBC Bridge Driver.
Type 1 drivers are "bridge" drivers. They use another technology such as Open Database Connectivity
(ODBC) to communicate with a database. This is an advantage because ODBC drivers exist for many
Relational Database Management System (RDBMS) platforms. The Java Native Interface (JNI) is
used to call ODBC functions from the JDBC driver.
A Type 1 driver needs to have the bridge driver installed and configured before JDBC can be used with it.
This can be a serious drawback for a production application. Type 1 drivers cannot be used in an applet
since applets cannot load native code.
Functions: - Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the
- ODBC driver.
- Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. This driver is native
- code and not Java, and is closed
source.
- Client -> JDBC Driver -> ODBC Driver -> Database
- There is some overhead associated with the translation work to go from JDBC to ODBC.
Advantages:
Almost any database for which ODBC driver is installed, can be accessed.
Disadvantages: - Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC
- driver, then to the native database connectivity interface.
- The ODBC driver needs to be installed on the client machine.
- Considering the client-side software needed, this might not be suitable for applets.
Type 2 Driver - the Native-API Driver
The JDBC type 2 driver, also known as the Native-API driver is a database driver implementation that
uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.
The type 2 driver is not written entirely in Java as it interfaces with non-Java code that makes the final
database calls.
The driver is compiled for use with the particular operating system. For platform interoperability, the
Type 4 driver, being
a full-Java implementation, is preferred over this driver.
A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for
Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver
requires that some binary code be loaded on each client machine.
However the type 2 driver provides more functionality and performance than the type 1 driver as it does
not have the overhead of the additional ODBC function calls.
Type 2 drivers use a native API to communicate with a database system. Java native methods are used to
invoke the API functions that perform database operations. Type 2 drivers are generally faster than
Type 1 drivers.
Type 2 drivers need native binary code installed and configured to work. A Type 2 driver also uses the
JNI. You cannot use a Type 2 driver in an applet since applets cannot load native code. A Type 2 JDBC
driver may require some Database Management System (DBMS) networking software to be installed.
The Developer Kit for Java JDBC driver is a Type 2 JDBC driver.
Functions: - This type of driver converts JDBC calls into calls to the client API for that database.
- Client -> JDBC Driver -> Vendor Client DB Library -> Database
Advantage
Better performance than Type 1 since no jdbc to odbc translation is needed.
Disadvantages - The vendor client library needs to be installed on the client machine.
- Cannot be used in internet due the client side software needed.
- Not all databases give the client side library.
Type 3 driver - the Network-Protocol Driver
The JDBC type 3 driver, also known as the network-protocol driver is a database driver implementation
which makes use of a middle-tier between the calling program and the database. The middle-tier
(application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol. This differs from the type 4 driver in that the protocol conversion logic resides not at the client, but in the
middle-tier. However, like type 4 drivers, the type 3 driver is written entirely in Java. The same driver can be used for multiple databases. It depends on the number of databases the middleware
has been configured to support. The type 3 driver is platform-independent as the platform-related
differences are taken care by the middleware. Also, making use of the middleware provides additional
advantages of security and firewall access. A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent
net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able
to connect all of its Java technology-based clients to many different databases. The specific protocol used
depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors
of this solution will provide products suitable for Intranet use. In order for these products to also support
Internet access they must handle the additional requirements for security, access through firewalls, etc.,
that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing
database middleware products. These drivers use a networking protocol and middleware to communicate with a server. The server then
translates the protocol to DBMS function calls specific to DBMS. Type 3 JDBC drivers are the most flexible JDBC solution because they do not require any native binary
code on the client. A Type 3 driver does not need any client installation.
Functions: - Follows a three tier communication approach.
- Can interface to multiple databases - Not vendor specific.
- The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database.
- Thus the client driver to middleware communication is database independent.
- Client -> JDBC Driver -> Middleware-Net Server -> Any Database
Advantages - Since the communication between client and the middleware server is database independent, there
- is no need for the vendor db library on the client machine. Also the client to middleware need'nt be
- changed for a new database.
- The Middleware Server (Can be a full fledged J2EE Application server) can provide typical
- middleware services like caching (connections, query results, and so on), load balancing, logging,
- auditing etc..
- eg. for the above include jdbc driver features in Weblogic.
- Can be used in internet since there is no client side software needed.
- At client side a single driver can handle any database.(It works provided the middlware supports
- that database!!)
Disadvantages - Requires database-specific coding to be done in the middle tier.
- An extra layer added may result in a time-bottleneck. But typically this is overcome by providing
- efficient middleware
services described above.
Type 4 - the Native-Protocol Driver
The JDBC type 4 driver, also known as the native-protocol driver is a database driver implementation
that converts JDBC calls directly into the vendor-specific database protocol.
The type 4 driver is written completely in Java and is hence platform independent. It is installed inside the
Java Virtual Machine of the client. It provides better performance over the type 1 and 2 drivers as it does
not have the overhead of conversion of calls into ODBC or database API calls. Unlike the type 1 and 2
drivers, it does not need associated software to work.
A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network
protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server
and is a practical solution for Intranet access. Since many of these protocols are proprietary the database
vendors themselves will be the primary source for this style of driver. Several database vendors have these
in progress.
As the database protocol is vendor-specific, separate drivers, usually vendor-supplied, need to be used to
connect to the database.
A Type 4 driver uses Java to implement a DBMS vendor networking protocol. Since the protocols are
usually proprietary, DBMS vendors are generally the only companies providing a Type 4 JDBC driver.
Type 4 drivers are all Java drivers. This means that there is no client installation or configuration. However,
a Type 4 driver may not be suitable for some applications if the underlying protocol does not handle issues
such as security and network connectivity well.
The IBM Toolbox for Java JDBC driver is a Type 4 JDBC driver, indicating that the API is a pure Java
networking protocol driver.
Functions - Type 4 drivers are entirely written in Java that communicate directly with a vendor's database through
- socket connections. No translation or middleware layers, are required, improving performance.
- The driver converts JDBC calls into the vendor-specific database protocol so that client applications
- can communicate directly with the database server.
- Completely implemented in Java to achieve platform independence.
- e.g include the widely used Oracle thin driver - oracle.jdbc.driver. OracleDriver which connect to jdbc:oracle:thin URL format.
- Client Machine -> Native protocol JDBC Driver -> Database server
Advantages
These drivers don't translate the requests into db request to ODBC or pass it to client api for the db, nor
do they need a middleware layer for request indirection. Thus the performance is considerably improved.
Disadvantage
At client side, a separate driver is needed for each database.
JDBC Versions
1). The JDBC 1.0 API. 2). The JDBC 1.2 API. 3). The JDBC 2.0 Optional Package API. 4). The JDBC 2.1 core API. 5) The JDBC 3.0 API. 6) The JDBC 4.0 API. Features of JDBC 1.0 API The JDBC 1.0 API was the first officially JDBC API launched consists of the following java classes and
interfaces that you can open connections to particular databases. This version includes a completely redesigned administration console with an enhanced graphical interface
to manage and monitor distributed virtual databases. Features of JDBC 1.2 API 1). It supports Updatabale ResultSets. 2). The DatabaseMetaData code has been refactored to provide more transparency with regard to the
underlying database engine. 3) New pass through schedulers for increased performance.
Features of The JDBC 2.0 Optional Pacakage API
1). The use of DataSource interface for making a connection. 2). Use of JNDI to specify and obtain database connections. 3). It allows us to use Pooled connections, that is we can reuse the connections. 4). In this version the distrbuted transactions is possible. 5). It provides a way of handling and passing data using Rowset technology. Features of the JDBC 2.1 core API. 1). Scroll forward and backward in a result set or has the ability to move to a specific row. 2). Instead of using SQL commands, we can make updates to a database tables using methods in the Java programming language 3). We can use multiple SQL statements in a a database as a unit, or batch. 4). It uses the SQL3 datatypes as column values. SQL3 types are Blob, Clob, Array, Structured type, Ref. 5). Increased support for storing persistent objects in the java programming language. 6). Supports for time zones in Date, Time, and Timestamp values. 7). Full precision for java.math.BigDecimal values. Features of JDBC 3.0 API 1). Reusabilty of prepared statements by connection pools. 2). In this version there is number of properties defined for the ConnectionPoolDataSource. These properties can be used to describe how the PooledConnection objects created by DataSource objects should be pooled. 3) A new concept has been added to this API is of savepoints. 4). Retrieval of parameter metadata. 5). It has added a means of retrieving values from columns containing automatically generated values. 6). Added a new data type i.e. java.sql.BOOLEAN. 7). Passing parameters to CallableStatement. 8). The data in the Blob and Clob can be altered. 9). DatabaseMetaData API has been added. Features of JDBC 4.0 : 1). Auto- loading of JDBC driver class. 2). Connection management enhancements. 3.) Support for RowId SAL type. 4). SQL exception handling enhancements. 5). DataSet implementation of SQl using Annotations. 6). SQL XML support
JDBC 3.0 API
- Reusabilty of prepared statements by connection pools.
- In this version there is number of properties defined for theConnectionPoolDataSource. These
- properties can be used to describe how thePooledConnection objects created byDataSource
- objects should be pooled.
- A new concept has been added to this API is of savepoints: One of the useful new features is
- transactional savepoints. With JDBC 3.0, the transactional model is now more flexible. Now you can
- start a transaction , insert several rows in it and then create a savepoint. This savepoint serves as a
- bookmark. The application can rollback to the savepoint and then commit the group of inserts as if
- the updates have never been attempted. For eg:
Statement st=connection.createStatement();
int rowcount=st.executeUpdate("insert into employee values("tim"));
int rowcoutn=st.executeUpdate("insert into salary values(20000.0);
Savepoint sv=connection.setSavePoint("savepoint"); //create save point for inserts
int rowcount=st.executeUpdate("delete from employee");
connection.rollback(sv); //discard the delete statement but keeps
- the inserts
connection.commit(); //inserts are now permanent
- Retrieval of parameter metadata.
- It has added a means of retrieving values from columns containing automatically generatedvalues.
- Added a new data type i.e. java.sql.BOOLEAN.
- Passing parameters to CallableStatement.
- The data in the Blob and Clob can be altered: JDBC 3.0 introduces a standard mechanism for updating BLOB and CLOB data.
- DatabaseMetaData API has been added.
- It allows stored procedure parameters to be called by name.
JDBC 4.0
JDBC 4 is a major new release with a strong focus ease-of-use and programmer productivity. JDBC 4's
key priority is to make it easier for developers to work directly with the JDBC API. The new features
availble in J2SE 1.5 added ease-of-development features in JDBC 4 In addition to ease-of-use, JDBC 4 introduces several SQL 203 features, like Java specific mapping for
the new XML SQL type. Now in JDBC 4 every PreparedStatement is poolable by default. - Auto- loading of JDBC driver class: In JDBC 4 invoking the getConnection() onDriverManager will automatically load a driver. Upon loading the driver, an instance of the driver is created and the registerDriver() method is invoked to make that driver available to clients.
- Connection management enhancements: In jdbc it may happen that a Connection is lying idle or not closed in a pool, then it became stale over time. This will led to the connection pool run out of resources due to stale connection. We know that the stale connection doesn't mean that a connection is closed. Now in JDBC 4 Connection class we have provided one methodisValid(), which allows a client to query the database driver if a connection is still valid. As database is a shared resource, so many application can access the same data store. To maintain all the records of the client is a difficult task, now in JDBC 4 an application can associate metadata with a database connection via the setClientInfo() method, which takes a name/value pair, or a Properties object.
- Support for RowId data type: JDBC introduces support for ROWID, a data type that had been in use in database products even before it became part of the SQL.
- SQL exception handling enhancements: JDBC 4 addresses the error handling beautifully. As databases are often remotely accessible resources, problems such as network failures is common and it can cause exceptions when executing a database operation. SQL statements can also cause exceptions. Prior to JDBC 4, most JDBC operations generated a simple SQLException.
- SQL XML support:
- DataSet implementation of SQL using Annotations: The JDBC 4.0 specification leverages annotations to allow developers to associate a SQL query with a Java class without a need to write a lot of code to achieve this association.
New Features in JDBC 4.0
Java database connectivity (JDBC) is the Java Soft specification of the API. It allows Application
Programs to interact with the Database to access the Relational Data. Typically the JDBC API consists
of a set of interfaces and classes written in the Java programming language. The applications can be written
to connect to databases using these standard interfaces and classes and also to process the results by
sending queries written in SQL. Furthermore the back-end Database session can be connected by JDBC
API which can execute the Queries to get the Results.
Some of the new set of features which come along with Mustang is JDBC 4.0 are: No need for Class.forName("DriverName") Changes in Connection and Statement Interface Better Pools Using ResultSet Becomes More Flexible More APIs Become Available
The three most important features of Driver and Connection Management in JDBC are described below with some new features and enhancements. 1. Getting Connected Becomes Easier This is one of the most important feature in JDBC that has relieved the developers from loading the driver
explicitly. Exactly, you got it right, no need to explicitly load the driver by callingClass.forName anymore.
So from now on don't bother to load the driver because theDriverManager will automatically load the
driver found in the application CLASSPATH while your application tries to connect the database for the
first time. However it is still preferred to create an appropriate DataSource object to retrieve a connection.
Since the properties of data source instance can be modified which allows portability and transparency,
there is no need to modify any application code to connect to a different database instance. Hence to
retrieve a connection there is no need to change any application code to connect to a different database
instance that requires loading a different driver. For example: Earlier the programmers used to manually load the drivers before making any Database
Calls. The code which was used by the programmers is:
Class.forName("FullyQualifiedNameOfTheDriverClass"); The disadvantage is that the code will be changed if the Database Driver vendor wants to change the class
name. Now this problem doesn't persist anymore. With the help of Service Provider Mechanism available
from Java 5, the Driver will be Loaded Automatically by the JVM provided that the Jar files corresponding
to the Driver Class are in the appropriate class path. To understand this mechanism consider two entities, one is the service which contains a set of interfaces
and the other entity is the provider who gives a well-defined Implementation for the Service. Now to maintain a directory structure, this service is packaged as a Jar by the provider. The structure of a directory is: \\META-INF\\services\\FullNameOfTheService
After maintaining the directory structure, if the JDBC Driver Provider wants to make his implementation as a Service then he has to maintain the file name along with the directory structure as, \\META-INF\\services\\java.sql.Driver Rememeber the file should only contain one entry that is the Name of the Provider Class. The function
of this entry is to implement the Driver. Also the name of the file is java.sql.Driver in the above code.
For instance the content of the file in My SQL would be, File - Java.sql.Drivercom.mysql.jdbc.Driver # Class name of the Driver
Hence any Java program will automatically load the JDBC driver by the procedure. Moreover if the
application class-path contains multiple drivers then the preference will be given to the first matching
driver only ignoring the rest of the drivers. 2. Using ResultSet Becomes More Flexible Several important features of ResultSet interfaces hierarchy are described below: The ResultSet provided by the RowSet sub-interface is scrollable, updateable, and offline-editable .
The JDBC programming has become much more easier and flexible due to the current version's
support for the SQLXML data type along with these features. Another feature of the ResultSet is the WebRowSet sub-interface. This interface provides the ability
to read data from database tables. Moreover it is used to serialize the data to an XML document,
and deserialize from XML back to result set.
Accessing Database using Java and JDBC
Database plays an important role in storing large amount of data in a pattern. Here we are going
develop and example to access the database using Java and JDBC. For this, firstly we need to
establish a connection between database and java file with the help of various types of APIs,
interfaces and methods. We are using MySQL database. Connection: This interface specifies connection with specific databases like: MySQL, Ms-Access,
Oracle etc and java files. The SQL statements are executed within the context of this interface. Class.forName(String driver): It loads the driver. DriverManager: This class controls a set of JDBC drivers. Each driver has to be register with this
class. getConnection(String url, String userName, String password): This method establishes a
connection to specified database url. It is having three arguments:
url: - Database url where stored or created your database
username: - User name of MySQL
password: -Password of MySQL getMetaData(): This is a method of Connection interface. It retrieves the metadata of the database. DataBaseMetaData: This interface gives information about the database like number of tables in the database, columns of the table etc. getTables(null, null, "%", null): This method provides the description of the tables available in
the given catalog. As we have set other parameters null, so it will provide only table names. Here is the code:
import java.sql.*;
public class AccessDatabases {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root", "root");
Statement st = con.createStatement();
DatabaseMetaData meta = con.getMetaData();
ResultSet rs = meta.getTables(null, null, "%", null);
String tableNames = "";
while (rs.next()) {
tableNames = rs.getString(3);
System.out.println(tableNames);
}
} catch (Exception e) {
}
}
} |
In this section we studies how to connect to database and then list all the tables in the database.
We have used MySQL database server.
SQL Exception Handling
A lot of improvement has been done regarding Exception handling in the following fields: - Iterable SQL Exception
- Concreate Sub Classes for specific SQL Exception
Iterable SQL Exception A new interface which is implemented by the java.sql.SQLException is Iterable<Throwable>
interface. To get the Next Set of Exceptions, this interface (Iterable<Throwable> interface) can
be iterated further. Not only this it can also be used in Enhanced For-Loops (available from Java 5.0). For example: try{
// Some Database Access Code
}catch(SQLException exception){
for(Throwable throwable: exception){
System.out.println(throwable.getMessage());
}
} However earlier to get the next Exception the JDBC 4.0 applications used to rely onSQLException.getNextException(). Unlike Iterable SQL Exception, the SQLException.getNextException() used to loop over until it gets the null as a returned exception. try{
// Some Database Access Code
}catch(SQLException exception){
System.out.println(exception.getMessage());
SQLException nextException = exception.getNextException();
while (nextException != null){
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
} Concreate Sub Class for specific SQL Exception Another feature which is added to Java 6.0 is Sub Class for specific SQL Exception. Few
SQL Exception related Sub-Classes have been added to the API. These Exceptions which
encapsulate the State of the SQL Error are divided into three groups namely Transient,
Recoverable and Non-Transient which are described below. Transient Exception: It is a kind of exception in which the program recovers from the exception
without altering the application. For example, during the program run-time any type of SQL Exception occurs and if in another attempt the program recovers from the same exception then this kind of
exception is known as Transient Exception. The java.sql.TransientException classrepresents this exception. In JDBC 4.0 API following are the identified Transient Exceptions. - SQLTransientConnectionException
- SQLTimeoutException
- SQLTransactionRollbackException
Recoverable Exception: This exception is represented byjava.sql.SQLRecoverableException.
It is a bit different from the Transient Exception in the sense that this exception can be recovered
by applying some Application Specific Recovery. Non-Transient Exception: This exception is represented by java.sql.TransientException.
This exception signifies that no matter how many times the application Program tries to recover the exception, it can never be recovered. The exceptions which belong to this group are: - SQLFeatureNotSupportedException
- SQLNonTransientConnectionException
- SQLDataException
- SQLInvalidAuthorizationSpecException
- SQLSyntaxErrorException
- SQLIntegrityConstraintViolationException
6. Support for SQL RowId A Row Id is an unique Identifier which is supported by databases such as Oracle and DB2. It is
used to identify a Row. For instance, there are multiple records without having a unique identifier
column then to store the query output in a Collection (such Hashtable) that doesn't allow duplicates ............................................................ The following piece of code shows how to retrieve the value of the Row Id for a particular Row. String selectQuery = " select rowid from Employees where id ='123' ";
ResultSet resultSet = statement.executeQuery("selectQuery");
java.sql.Rowid rowId = resultSet.getRowId(); The RowID interface was added to JDBC 4.0 to support the ROWID data type which is supported
by databases such as Oracle and DB2. RowId is useful in cases where there are multiple records
that don't have a unique identifier column and you need to store the query output in a Collection
(such Hashtable) that doesn't allow duplicates. We can use ResultSet's getRowId() method to get
a RowId and PreparedStatement's setRowId() method to use the RowId in a query.
An important thing to remember about the RowId object is that its value is not portable between
data sources and should be considered as specific to the data source when using the set or update
methods in PreparedStatement and ResultSet respectively. So, it shouldn't be shared between
different Connection and ResultSet objects Most of the popular Databases support the concept of a Row Id. A Row Id is an unique Identifier
which is used to identify a Row. Support for Retrieving and setting the Row Id for a row is made
available from JDBC 4.0 version. For example, consider the following piece of code, which will
retrieve the value of the Row Id for a particular Row. String selectQuery = " select rowid from Employees where id ='123' ";
ResultSet resultSet = statement.executeQuery("selectQuery");
java.sql.Rowid rowId = resultSet.getRowId(); The Row Id is represented by java.sql.RowId class. Before doing any kind of manipulation on the
Row Id, it is wise to check whether the under-lying Database implementation provides Support for
Row Id(s), as well as the Lifetime of the Row Id objects. The life-time of the Row Id determines
how long the Row Id is valid, whether the Row Id exists only for a particular Session or for a Set of Transactions within a Session or Outside the Database Session etc. This availability can be known
with the help of DatabaseMetaData like the following, RowIdLifeTime rowIdLifeTime = DatabaseMetaData.getRowIdLifetime();
if (rowIdLifeTime != ROWID_UNSUPPORTED){
// Row Id support is there for this Data source.
}
The returned RowIdLifeTime is an Enum which tells about the Life-Time of the Row Id object.
Possible values are ROWID_UNSUPPORTED, ROWID_VALID_FOREVER, ROWID_VALID_OTHER, ROWID_VALID_SESSION, ROWID_VALID_TRANSACTION
and ROWID_VALID_FOREVER.
Wrapper
This new wrapper interface provides a mechanism for accessing an instance of a resource. This is u
sed by many JDBC driver implementations. Earlier the wrapper used to be data source specific
however now the developers can access these resources that are wrapped as proxy class instances.
The interface which the developers can use are as follows: This interface describes a standard mechanism to access these wrapped resources represented by
their proxy, to permit direct access to the resource delegates. isWrapperFor(Class<?> iface): Returns true if this either implements the interface argument or is directly or indirectly a wrapper for
an object that does. Returns false otherwise. If this implements the interface then return true, else if
this is a wrapper then return the result of recursively calling isWrapperFor on the wrapped object.
If this does not implement the interface and is not a wrapper, return false. This method should be implemented as a low-cost operation compared to unwrap so that callers can use this method to
avoid expensive unwrap calls that may fail. If this method returns true then calling unwrap with the
same argument should succeed. unwrap(Class<T> iface): Returns an object that implements the given interface to allow access to non-standard methods, or
standard methods not exposed by the proxy. If the receiver implements the interface then the result is
the receiver or a proxy for the receiver. If the receiver is a wrapper and the wrapped object
implements the interface then the result is the wrapped object or a proxy for the wrapped object.
Otherwise return the the result of calling unwrap recursively on the wrapped object or a proxy for
that result. If the receiver is not a wrapper and does not implement the interface, then an
SQLException is thrown Conclusion We have described some of the new features of Java 6.0 in this section. The enhancement in the
features like Automatic Driver Loading, SQL Exception Handling, Changes in Connection and
Statement Interface are of great help. All the features have been explained with the help of examples
to grasp the things more easily and quickly.
Difference between JDBC 3.0 & JDBC 4.0
Features of JDBC 3.0 - Reusabilty of prepared statements by connection pools.
- In this version there is number of properties defined for theConnectionPoolDataSource. These properties can be used to describe how thePooledConnection objects created byDataSource objects should be pooled.
- A new concept has been added to this API is of savepoints: One of the useful new features is transactional savepoints. With JDBC 3.0, the transactional model is now more flexible. Now you can start a transaction , insert several rows in it and then create a savepoint. This savepoint serves as a bookmark. The application can rollback to the savepoint and then commit the group of inserts as if the updates have never been attempted. For eg:
Statement st=connection.createStatement();
int rowcount=st.executeUpdate("insert into employee values("tim"));
int rowcoutn=st.executeUpdate("insert into salary values(20000.0);
Savepoint sv=connection.setSavePoint("savepoint"); //create save point for inserts
int rowcount=st.executeUpdate("delete from employee");
connection.rollback(sv); //discard the delete statement but keeps the inserts
connection.commit(); //inserts are now permanent
- Retrieval of parameter metadata.
- It has added a means of retrieving values from columns containing automatically generatedvalues.
- Added a new data type i.e. java.sql.BOOLEAN.
- Passing parameters to CallableStatement.
- The data in the Blob and Clob can be altered: JDBC 3.0 introduces a standard mechanism for updating BLOB and CLOB data.
- DatabaseMetaData API has been added.
- It allows stored procedure parameters to be called by name.
Features of JDBC 4 JDBC 4 is a major new release with a strong focus ease-of-use and programmer productivity. JDBC 4's key priority is to make it easier for developers to work directly with the JDBC API. The new features availble in J2SE 1.5 added ease-of-development features in JDBC 4 In addition to ease-of-use, JDBC 4 introduces several SQL 203 features, like Java specific mapping for the new XML SQL type. Now in JDBC 4 every PreparedStatement is poolable by default. - Auto- loading of JDBC driver class: In JDBC 4 invoking the getConnection() onDriverManager will automatically load a driver. Upon loading the driver, an instance of the driver is created and the registerDriver() method is invoked to make that driver available to clients.
- Connection management enhancements: In jdbc it may happen that a Connection is lying idle or not closed in a pool, then it became stale over time. This will led to the connection pool run out of resources due to stale connection. We know that the stale connection doesn't mean that a connection is closed. Now in JDBC 4 Connection class we have provided one methodisValid(), which allows a client to query the database driver if a connection is still valid. As database is a shared resource, so many application can access the same data store. To maintain all the records of the client is a difficult task, now in JDBC 4 an application can associate metadata with a database connection via the setClientInfo() method, which takes a name/value pair, or a Properties object.
- Support for RowId data type: JDBC introduces support for ROWID, a data type that had been in use in database products even before it became part of the SQL.
- SQL exception handling enhancements: JDBC 4 addresses the error handling beautifully. As databases are often remotely accessible resources, problems such as network failures is common and it can cause exceptions when executing a database operation. SQL statements can also cause exceptions. Prior to JDBC 4, most JDBC operations generated a simple SQLException.
- SQL XML support:
- DataSet implementation of SQL using Annotations: The JDBC 4.0 specification leverages annotations to allow developers to associate a SQL query with a Java class without a need to write a lot of code to achieve this association.
Relational Database
An important part of every business is to keep records. We need to keep records of our customers, the employees of our company, the emails etc. To keep all the data indivually is quite difficult and hectic job, because whenever if we need the record of a particular customer or an employee we need to search manually. It takes lot of time and still not reliable. Here comes the concept of databases. What is database? A database is an organized collection of information. A simple example of a database are like your telephone directory, recipe book etc.
A Relational model is the basis for any relational database management system (RDBMS). A relational model has mainly three components: A collection of objects or relations,. Operators that act on the objects or relations. Data integrity methods.
To design a database we need three things: Table Rows Columns
A table is one of the most important ingredient to design the database. It is also known as arelation,
is a two dimensional structure used to hold related information. A database consists of one or more
tables.
A table contains rows : Rows is a collection of instance of one thing, such as the information of one employee.
A table contains the columns: Columns contains all the information of a single type. Each column in
a table is a category of information referred to as a field. One item of data, such as single phone number of a person is called as a Data Value. ACID Properties: ACID properties are one of the important concept for databases. ACID stands for Atomicity,
Consistency, Isolation, and Durability. These properties of a DBMS allow safe sharing of data.
Without these properties the inaccuracy in the data will be huge. With the help of the ACID
properties the accuracy can be maintained. Normalization: Normalization is a design technique which helps the to design the relational databases. Normalization
is essentially a two step process that puts data into tabular form by removing redundant data from the relational tables. A basic goal of normalization is to create a set of relational tables that are free of
redundant data and the data should be consistent. Normalization has been divided into following forms. - First Normal Form: A relational table, by definition are in first normal form. All values of the columns are atomic. It means that it contains no repeating values.
- A relationl table is in second normal form if it is in 1NF and every non- key column is fully dependent upon the primary key.
- A relational table is in third normal form (3NF) if it is already in 2NF and every non- key column is non transitively dependent upon its primary key. The advantage of having table in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies.
Common SQL statements
The commonly used SQL statements are: 1): Select
2): Insert
3): Update
4): Delete 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. SQL INSERT Statement: 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. The UPDATE Statement: The Update statement is used to modify the data in the table. Whenever we want to update or delete a row then we use the Update statement. The syntax is : UPDATE table_name Set colunm_name = new_value WHERE column_name = some_name; The Update statement has mainly three clauses. 1). UPDATE: It specifies which table column has to be updated.
2). Set: It sets the column in which the data has to be updated.
3). Where: It tells which tables are used. SQL DELETE Statement: This delete statement is used to delete rows in a table. Systax: DELETE FROM table_name WHERE column_name = some_name; The Delete statement has following clauses. 1). Delete: It specifies which table column has to be deleted.
2). From: It tells from where the Table has been accessed.
3). Where: It tells which tables are used.
JDBC Concepts
Transactions: Whenever a connection is created by using the JDBC, then by default it is in auto- commit mode. This means that SQL statement will be automatically committed immediately after it is executed and it is treated as a transaction. But imagine a situation where you want to execute a batch of statements, either they should commit at on go or they should get failed together. For this we need to disable the auto- commit mode by using the method: con.setAutoCommit(false). After setting the auto- commit as false, no SQL statement will be committed until we call the
con.commit() method. If there arises any problem while committing then the set of statements will be rollback, without committing. Logging: on the server--->logging--->JDBC. By this we can enable JDBC logging and specify a log file name for the JDBC log. Attributes of Logging: 1) Enable JDBC Logging: It determines whether the server has a JDBC log file. 2) JDBC Log File Name: It is the name of the log file. Isolation: The isolation is needed when there are concurrent transactions. Concurrent transactions
are transactions are transactions that occurs at the same time. In isolation one transaction does not
interfere with another. For setting the isolation level for a JDBC transaction, use the Connection.setTransaction(int level) method By using the snapshot isolation level we can only see the snapshot of the data locked by other
transactions when running from inside the transaction with snapshot isolation level. Some of the transaction level are given below: 1). TRANSACTION_NONE 2). TRANSACTION_READ_UNCOMMITED 3. TRANSACTION_READ_COMMITTED 4. TRANSACTION_REPEATABLE_READ 5. TRANSACTION_SERIALIZABLE By setting the isolation levels you are having an impact on the performance of the transaction.
You can get the existing isolation level with: getTransactionIsolation() method. Concurrency: Database concurrency controls ensure that the transactions occur in an ordered fashion. Concurrency control deals with the issue involved with allowing multiple people simultaneous access to shared entities.
java.sql package
This package provides the APIs for accessing and processing data which is stored in the database
especially relational database by using the java programming language. It includes a framework
where we different drivers can be installed dynamically to access different databases especially
relational databases. This java.sql package contains API for the following : 1 Making a connection with a database with the help of DriverManager class
a) DriverManager class: It helps to make a connection with the driver.
b) SQLPermission class: It provides a permission when the code is running within a Security
Manager, such as an applet. It attempts to set up a logging stream through the DriverManager class.
c) Driver interface : This interface is mainly used by the DriverManager class for registering and
connecting drivers based on JDBC technology.
d). DriverPropertyInfo class : This class is generally not used by the general user. 2). Sending SQL Parameters to a database :
a). Statement interface: It is used to send basic SQL statements.
b). PreparedStatement interface: It is used to send prepared statements or derived SQL statements
from the Statement object.
c). CallableStatement interface : This interface is used to call database stored procedures.
d). Connection interface : It provides methods for creating statements and managing their connections
and properties.
e). Savepoint : It helps to make the savepoints in a transaction.
3). Updating and retrieving the results of a query:
a). ResultSet interface: This object maintains a cursor pointing to its current row of data. The
cursor is initially positioned before the first row. The next method of the resultset interface moves the
cursor to the next row and it will return false if there are no more rows in the ResultSet object. By
default ResultSet object is not updatable and has a cursor that moves forward only. 4.) Providing Standard mappings for SQL types to classes and interfaces in Java Programming language.
a). Array interface: It provides the mapping for SQL Array.
b). Blob interface : It provides the mapping for SQL Blob.
c). Clob interface: It provides the mapping for SQL Clob.
d). Date class: It provides the mapping for SQL Date.
e). Ref interface: It provides the mapping for SQL Ref.
f). Struct interface: It provides the mapping for SQL Struct.
g). Time class: It provides the mapping for SQL Time.
h). Timestamp: It provides the mapping for SQL Timestamp.
i). Types: It provides the mapping for SQL types. 5). Metadata a). DatabaseMetaData interface: It keeps the data about the data. It provides information about the database.
b). ResultSetMetaData: It gives the information about the columns of a ResultSet object.
c). ParameterMetaData: It gives the information about the parameters to the PreparedStatement
commands. 6). Exceptions a). SQLException: It is thrown by the mehods whenever there is a problem while accessing the data
or any other things.
b). SQLWarning: This exception is thrown to indicate the warning.
c). BatchUpdateException: This exception is thrown to indicate that all commands in a batch update
are not executed successfully.
d). DataTruncation: It is thrown to indicate that the data may have been truncated. 7). Custom mapping an SQL user- defined type (UDT) to a class in the java programming language. a). SQLData interface: It gives the mapping of a UDT to an intance of this class.
b). SQLInput interface: It gives the methods for reading UDT attributes from a stream.
c). SQLOutput: It gives the methods for writing UDT attributes back to a stream.
Driver Manager Class
The JDBC Driver Manager is a very important class that defines objects which connect Java
applications to a JDBC driver. Usually Driver Manager is the backbone of the JDBC architecture.
It's very simple and small that is used to provide a means of managing the different types of JDBC
database driver running on an application. The main responsibility of JDBC database driver is to load
all the drivers found in the system properly as well as to select the most appropriate driver from
opening a connection to a database. The Driver Manager also helps to select the most appropriate
driver from the previously loaded drivers when a new open database is connected. The DriverManager class works between the user and the drivers. The task of the DriverManager
class is to keep track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. It even keeps track of the driver login time limits and printing
of log and tracing messages. This class is mainly useful for the simple application, the most frequently
used method of this class is DriverManager.getConnetion(). We can know by the name of the method
that this method establishes a connection to a database. The DriverManager class maintains the list of the Driver classes. Each driver has to be get registered in the DriverManager class by calling the method DriverManager.registerDriver(). By calling the Class.forName() method the driver class get automatically loaded. The driver is loaded
by calling the Class.forName() method. JDBC drivers are designed to tell the DriverManager about themselves automatically when their driver implementation class get loads. This class has many methods. Some of the commonly used methods are given below: 1. deregisterDriver(Driver driver) : It drops the driver from the list of drivers registered in the DriverManager class.
2. registerDriver(Driver driver) : It registers the driver with the DriverManager class.
3. getConnection(String url) : It tries to establish the connection to a given database URL.
4. getConnection(String url, Sting user, String password) : It tries to establish the connection to a given database URL.
5. getConnection(String url, Properties info) : It tries to establish the connection to a given database URL.
6. getDriver(String url) : It attempts to locate the driver by the given string.
7. getDrivers() : It retrieves the enumeration of the drivers which has been registered with the DriverManager class.
Data Source
The JDBC API provides the DataSourceinterface as an alternative to the DriverManager for establishing the connection. A DataSource object is the representation of database or the data source in the Java programming language. DataSouce object is mostly preferred over the DriverManager for establishing a connection to the database.
DataSource object can be thought as a factory for making connections to the particular database
that the DataSource instance represents. DataSource has a set of properties that identify and describe the real world data source that it
represents. The properties include information about the location of the database server, the network protocol use to communicate with the server the name of the database and so on.
DataSource object works with JNDI (Java Naming and Directory interface) naming service so
application can use the JNDI API to access the DataSource object. In short we can say that the DataSource interface is implemented to provide three kinds of
connections: 1). Basic DataSource class This class is provided by the driver vendor. It is used for portability and easy maintence. 2). To provide connection pooling. It is provided by the application server vendor or driver vendor. It works with
ConnectionPoolDataSource class provided by a driver vendor. Its advantage is portability, easy maintenence and increased performance. 3). To provide distributed transactions This class works with an XADataSource class, which is provided by the driver vendor. Its
advantages are easy maintenence, portability and ability to participate in distributed transactions.
Connection Object
A Connection object represents a connection with a database. When we connect to a database
by using connection method, we create a Connection Object, which represents the connection to the database. An application may have one or more than one connections with a single database or many connections with the different databases also. We can use the Connection object for the following things: 1). It creates the Statement, PreparedStatement and CallableStatement objects for executing
the SQL statements. 2). It helps us to Commit or roll back a jdbc transactionn. 3). If you want to know about the database or data source to which you are connected then theConnection object gathers information about the database or data source by the use ofDatabaseMetaData. 4). It helps us to close the data source. The Connection.isClosed() method returns true only if the Connection.close() has been called. This method is used to close all the connection. Firstly we need to to establish the connection with the database. This is done by using the methodDriverManager.getConnection(). This method takes a string containing a URL. TheDriverManager class, attempts to locate a driver that can connect to the database represented
by the string URL. Whenever the getConnection() method is called the DriverManager class
checks the list of all registered Driver classes that can connect to the database specified in the URL. Syntax:
String url = "jdbc: odbc: makeConnection";
Connection con = DriverManager.getConnection(url, "userID", "password");
Basic steps in writing a JDBC Application
This section gives you brief description of JDBC Steps for making connection with the database,
executing the query and showing the data to the user. In this application we have connected to the
MySQL database and retrieved the employee names from the database. Here are the JDBC Steps
to be followed while writing JDBC program: - Loading Driver
- Establishing Connection
- Executing Statements
- Getting Results
- Closing Database Connection
Before explaining you the JDBC Steps for making connection to the database and retrieving the
employee from the tables, we will provide you the structure of the database and sample data. Here is the sql script to create table and populate the table with data:
-- Table structure for table `employee` CREATE TABLE `employee` ( `employee_name` varchar(50) NOT NULL, PRIMARY KEY (`employee_name`) ); INSERT INTO `employee` (`employee_name`) VALUES ('Prasobh.k'), ('Rajeesh'), ('Arun.G.S'), ('Lalbee'); |
Here is the code of java program that retrieves all the employee data from database and displays
on the console:
/*
Import JDBC core packages.
Following statement imports the java.sql package, which contains the JDBC core API.
*/ import java.sql.*;
public class RetriveAllEmployees{
public static void main(String[] args) {
System.out.println("Getting All Rows from employee table!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbc";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
Statement st = con.createStatement();
ResultSet res = st.executeQuery("SELECT * FROM employee");
System.out.println("Employee Name: " );
while (res.next()) {
String employeeName = res.getString("employee_name");
System.out.println(employeeName );
}
con.close();
}
catch (ClassNotFoundException e){
System.err.println("Could not load JDBC driver");
System.out.println("Exception: " + e);
e.printStackTrace();
}
catch(SQLException ex){
System.err.println("SQLException information");
while(ex!=null) {
System.err.println ("Error msg: " + ex.getMessage());
System.err.println ("SQLSTATE: " + ex.getSQLState());
System.err.println ("Error code: " + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException(); // For drivers that support chained exceptions
}
}
}
} |
Explanation of JDBC Steps: - Loading Driver
Loading Database driver is very first step towards making JDBC connectivity with the database. It is necessary to load the JDBC drivers before attempting to connect to the database. The JDBC drivers automatically register themselves with the JDBC system when
- loaded. Here is the code for loading the JDBC driver:
Class.forName(driver).newInstance();
- Establishing Connection
In the above step we have loaded the database driver to be used. Now its time to make the
- connection with the database server. In the Establishing Connection step we will logon to the
- database with user name and password. Following code we have used to make the connection
- with the database:
con = DriverManager.getConnection(url+db, user, pass);
- Executing Statements
In the previous step we established the connection with the database, now its time to
- execute query against database. You can run any type of query against database to
- perform database operations. In this example we will select all the rows from employee
- table. Here is the code that actually execute the statements against
- database:ResultSet res = st.executeQuery( "SELECT * FROM employee" );
- Getting Results
In this step we receives the result of execute statement. In this case we will fetch the employees records from the recordset object and show on the console. Here is the code: while (res.next()) {
String employeeName = res.getInt( " employee_name " );
System.out.println( employeeName );
}
- Closing Database Connection
Finally it is necessary to disconnect from the database and release resources being
- used. If you don’t close the connection then in the production environment your
- application will fail due to hanging database connections. Here is the code for
- disconnecting the application from database: con.close();
|
|
|
|
|
No comments:
Post a Comment