What is JDBC ?
JDBC is a Java database connectivity technology (Java Standard
Edition platform) from Oracle Corporation. This technology 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.
Short for Java Database Connectivity, a Java
API that enables Java programs to execute SQL statements. This allows Java
programs to interact with any SQL-compliant database. Since nearly all
relational database management systems (DBMSs) support SQL, and because Java
itself runs on most platforms, JDBC makes it possible to write a single
database application that can run on different platforms and interact with
different DBMSs.
JDBC is similar to ODBC, but is designed
specifically for Java programs, whereas ODBC is language-independent. JDBC was
developed by JavaSoft, a subsidiary of Sun Microsystems.
What is JDBC Driver?
JDBC drivers implement the defined interfaces in
the JDBC API, for interacting with your database server.
For example, using JDBC drivers enable you to
open database connections and to interact with it by sending SQL or database
commands then receiving results with Java.
The Java.sql package that ships
with JDK, contains various classes with their behaviours defined and their
actual implementaions are done in third-party drivers. Third party vendors
implements the java.sql.Driver interface in their database
driver.
JDBC
Drivers Types :
JDBC driver implementations vary because of the
wide variety of operating systems and hardware platforms in which Java
operates. Sun has divided the implementation types into four categories, Types
1, 2, 3, and 4, which is explained below –
Type
1: JDBC-ODBC Bridge Driver
In a Type 1 driver, a JDBC bridge is used to
access ODBC drivers installed on each client machine. Using ODBC, requires
configuring on your system a Data Source Name (DSN) that represents the target
database.
When Java first came out, this was a useful
driver because most databases only supported ODBC access but now this type of
driver is recommended only for experimental use or when no other alternative is
available.
The JDBC-ODBC Bridge that comes with JDK 1.2 is
a good example of this kind of driver.
Type
2: JDBC-Native API
In a Type 2 driver, JDBC API calls are converted
into native C/C++ API calls, which are unique to the database. These drivers
are typically provided by the database vendors and used in the same manner as
the JDBC-ODBC Bridge. The vendor-specific driver must be installed on each
client machine.
If we change the Database, we have to change the
native API, as it is specific to a database and they are mostly obsolete now,
but you may realize some speed increase with a Type 2 driver, because it
eliminates ODBC's overhead.
The Oracle Call Interface (OCI) driver is an
example of a Type 2 driver.
Type
3: JDBC-Net pure Java
In a Type 3 driver, a three-tier approach is
used to access databases. The JDBC clients use standard network sockets to
communicate with a middleware application server. The socket information is
then translated by the middleware application server into the call format
required by the DBMS, and forwarded to the database server.
This kind of driver is extremely flexible, since
it requires no code installed on the client and a single driver can actually
provide access to multiple databases.
You can think of the application server as a
JDBC "proxy," meaning that it makes calls for the client application.
As a result, you need some knowledge of the application server's configuration
in order to effectively use this driver type.
Your application server might use a Type 1, 2,
or 4 driver to communicate with the database, understanding the nuances will
prove helpful.
Type
4: 100% Pure Java
In a Type 4 driver, a pure Java-based driver
communicates directly with the vendor's database through socket connection.
This is the highest performance driver available for the database and is
usually provided by the vendor itself.
This kind of driver is extremely flexible, you
don't need to install special software on the client or server. Further, these
drivers can be downloaded dynamically.
MySQL's Connector/J driver is a Type 4 driver.
Because of the proprietary nature of their network protocols, database vendors
usually supply type 4 drivers.
Which
Driver should be Used?
If you are accessing one type of database, such
as Oracle, Sybase, or IBM, the preferred driver type is 4.
If your Java application is accessing multiple
types of databases at the same time, type 3 is the preferred driver.
Type 2 drivers are useful in situations, where a
type 3 or type 4 driver is not available yet for your database.
The type 1 driver is not considered a
deployment-level driver, and is typically used for development and testing
purposes only.
5
Steps to connect to the database in java:
There
are 5 steps to connect any java application with the database in java using
JDBC. They are as follows:
- Register
the driver class
- Creating
connection
- Creating
statement
- Executing
queries
- Closing
connection
1) Register the driver class
|
The forName() method of Class class is used to register the
driver class. This method is used to dynamically load the driver class.
|
Syntax of forName() method
public static void forName(String className)throws ClassNotFoundException
Example to register the OracleDriver class
Class.forName("oracle.jdbc.driver.OracleDriver");
2) Create the connection object
|
The getConnection() method of DriverManager class is used to
establish connection with the database.
|
Syntax of getConnection() method
1.
1) public static Connection getConnection(String url)throws SQLException
2.
2) public static Connection getConnection(String url,String name,String password)
3.
throws SQLException
Example to establish connection with the Oracle database
1.
Connection con=DriverManager.getConnection(
2.
"jdbc:oracle:thin:@localhost:1521:xe","system","password");
3)
Create the Statement object
|
The
createStatement() method of Connection interface is used to create statement.
The object of statement is responsible to execute queries with the database.
|
Syntax of createStatement() method
public Statement createStatement()throws SQLException
Example to create the statement object
Statement stmt=con.createStatement();
4)
Execute the query
|
The executeQuery() method of Statement interface is used to
execute queries to the database. This method returns the object of ResultSet
that can be used to get all the records of a table.
|
Syntax of executeQuery() method
public ResultSet executeQuery(String sql)throws SQLException
Example
to execute query
1.
ResultSet rs=stmt.executeQuery("select * from emp");
2.
while(rs.next()){
3.
System.out.println(rs.getInt(1)+" "+rs.getString(2));
4.
}
5) Close the connection object
|
By
closing connection object statement and ResultSet will be closed
automatically. The close() method of Connection interface is used to close
the connection.
|
Syntax of close() method
public void close()throws SQLException
Example to close connection
con.close();
JDBC
Connection Pooling:
JDBC
connection pooling is conceptually similar to any other form of object pooling.
Database connections are often expensive to create because of the overhead of
establishing a network connection and initializing a database connection
session in the back end database. In turn, connection session initialization
often requires time consuming processing to perform user authentication,
establish transactional contexts and establish other aspects of the session
that are required for subsequent database usage.
Additionally,
the database's ongoing management of all of its connection sessions can impose
a major limiting factor on the scalability of your application. Valuable
database resources such as locks, memory, cursors, transaction logs, statement
handles and temporary tables all tend to increase based on the number of
concurrent connection sessions.All in all, JDBC database connections are both
expensive to initially create and then maintain over time. Therefore, as we
shall see, they are an ideal resource to pool.
If
your application runs within a J2EE environment and acquires JDBC connections
from an appserver defined datasource then your application is probably already
using connection pooling. This fact also illustrates an important
characteristic of a best practices pooling implementation -- your application
is not even aware it's using it! Your J2EE application simply acquires JDBC
connections from the datasource, does some work on the connection then closes
the connection. Your application's use of connection pooling is transparent.
The characteristics of the connection pool can be tweaked and tuned by your appserver's
administrator without the application ever needing to know.
