Showing posts with label JDBC. Show all posts
Showing posts with label JDBC. Show all posts

Friday, 31 March 2017

JDBC - The Steps

JDBC : The Steps

In this article we are highlighting the steps to connect and process data in JDBC. For this article we are using Oracle Database. For other databases the drivers will vary but the steps will remain the same.
The steps involved in the process of connecting to a database and executing a query are as follows:
·         Load and register the JDBC driver.
·         Open a connection to the database.
·         Create a statement object to perform a query.
·         Execute the statement object and return a query resultset.
·         Process the resultset.
·         Close the resultset and statement objects.
·         Close the connection.

Load and Register the JDBC Driver
The first step is to establish a communication between the JDBC program and the database. This is done by using the static registerDriver() method of the DriverManager class of the JDBC API.
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Alternatively, we can use the forName() method of the java.lang.Class class can be used to load and register the JDBC driver:

Connecting to a Database
Once the JDBC driver has been loaded and registered, a database connection needs be established. This is done by using the getConnection() method of the DriverManager class. A call to this method creates an object instance of the java.sql.Connection class. The getConnection() requires three input parameters, namely, a connect string, a username, and a password.
Connection conn = DriverManager.getConnection(URL, username, passwd);

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@training:1521:Oracle",
 "oratest", "oratest");

Querying the Database
Querying the database involves two steps:
·         Creating a statement object to perform a query
·         Executing the query and returning a resultset.

Creating a Statement Object
The next step is to instantiate objects that run the query against the database connected. This is done by the createStatement() method of the Connection object created above. A call to this method creates an object instance of the Statement
Statement stmt = conn.createStatement();

Executing the Query and Returning a ResultSet
Once a Statement object has been constructed, the next step is to execute the query. This is done by using the executeQuery() method of the Statement object. A call to this method takes as parameter a SQL SELECT statement and returns a JDBC ResultSet object.
ResultSet rset = stmt.executeQuery
      ("SELECT empno, ename, sal, deptno FROM emp ");

Processing the Results of a Database Query That Returns Multiple Rows
Once the query has been executed, there are two steps to be carried out:
After the query execution we need to perform 2 things
·         Process the output resultset to fetch the rows
·         Retrievethe column values of the current row
The first step is using the next() method of the ResultSet object. A call to next() is executed in a loop to fetch the rows one row at a time, with each call to next() advancing the control to the next available row.
The second step is to fetch the values in the columnsby using the getXXX() methods of the JDBC rset object. Here getXXX() corresponds to the getInt(), getString() etc with XXX being replaced by a Java datatype.
String str;
while (
 str = rset.getInt(1)+ " "+ rset.getString(2)+ "
         "+rset.getFloat(3)+ " "rset.getInt(4)+ "\n";

Here 1, 2, 3, and 4 in rset.getInt(), rset.getString(), getFloat(), and getInt() respectively denote the position of the columns in the SELECT statement, that is, the first column empno, second column ename, third column sal, and fourth column deptno of the SELECT statement respectively.
The parameters for the getXXX() methods can be specified by position of the corresponding columns as numbers 1, 2, and so on, or by directly specifying the column names enclosed in double quotes, as getString("ename") and so on, or a combination of both.
Closing the ResultSet and Statement
Once the ResultSet and Statement objects have been used, they must be closed.

Closing the Connection
The last step is to close the database connection which is done by a call to the close() method.

Here is a complete example of JDBC Select using Type 4 driver and Oracle Database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC01 {
public static void main(String[] args) {
                try {
                } catch (ClassNotFoundException e) {
                try {
Connection con =DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
                                Statement s=con.createStatement();
                                String query01="select * from emp";
                                ResultSet rs=s.executeQuery(query01);
                System.out.println(rs.getInt("empno")+"    "+rs.getString("ename")+"   "+rs.getDouble("sal"));          

                } catch (SQLException e) {

Wednesday, 29 March 2017

JDBC Drivers

JDBC Drivers

A JDBC driver is a software component which enables a Java application to interact with a database. To connect with individual databases, JDBC requires drivers for each database. The JDBC driver felicitates connection to the database and implements the mechanism for transferring the query and result between client and database.

There are 4 (four) types of JDBC drivers.

1.    JDBC-ODBC bridge 
2.    Native-API driver 
3.    Network-Protocol driver (Middleware driver) 
4.    Database-Protocol driver (Pure Java driver) or thin driver.

Type 1 driver – JDBC-ODBC bridge

The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that uses the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.

Type 1 driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the underlying operating system the JVM is running on. ODBC must be installed on the computer having the driver and the database must support an ODBC driver. The use of this driver is discouraged. Any application using a type 1 driver is non-portable.
Sun (now Oracle) provided a JDBC-ODBC Bridge driver: sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source.

·         Any database for which an ODBC driver is installed can be accessed, and data can be retrieved.
·         Performance overhead since the calls have to go through the JDBC bridge to the ODBC driver, then to the native database connectivity interface. 
·         The ODBC driver needs to be installed on the client machine.
·         Not suitable for applets, because the ODBC driver needs to be installed on the client.
·         Specific ODBC drivers are not always available on all platforms; hence, portability of this driver is limited.
·         No support from JDK 1.8 (Java 8) onwards.

Type 2 driver – Native-API drive

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.
·         As there is no implementation of JDBC-ODBC bridge, it may be considerably faster than a Type 1 driver.
·         The vendor client library needs to be installed on the client machine.
·         Not all databases have a client-side library.
·         This driver is platform dependent.
·         This driver supports all Java applications except applets.

Type 3 driver – Network-Protocol driver (middleware driver)

The JDBC type 3 driver, also known as the Pure Java driver for database middleware, 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 a vendor-specific database protocol. The type 3 driver is written entirely in Java. The same client-side JDBC driver may 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 of by the middleware.  Also, making use of the middleware provides additional advantages of security and firewall access.
·  Since the communication between client and the middleware server is database independent,  there is no need for the database vendor library on the client. The client need not be changed for a new database.
·  The middleware server can provide typical middleware services like caching (of connections, query results, etc.),  load balancing, logging, and auditing.
·         A single driver can handle any database, provided the middleware supports it.

·         Requires database-specific coding to be done in the middle tier.
·        The middleware layer added may result in additional latency, but is typically overcome by using better middleware services.

Type 4 driver – Database-Protocol driver (Pure Java driver)

The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol. Type 4 drivers are platform independent as they are written in Java. They install inside the Java Virtual Machine of the client. This provides better performance as it does not have the overhead of conversion of calls into ODBC or database API calls.
As the database protocol is vendor specific, the JDBC client requires separate drivers, usually vendor supplied, to connect to different types of databases.
·         Completely implemented in Java to achieve platform independence.
·        These drivers don't translate the requests into an intermediary format.
·  The client application connects directly to the database server. No translation or middleware layers are used, improving performance.
·   The JVM can manage all aspects of the application-to-database connection; this can facilitate debugging.
·         Drivers are database specific, as different database vendors use widely different network protocols.

Monday, 27 March 2017

Java Database Connectivity

Java Database Connectivity

Java JDBC is a java API to connect and execute query with the database. JDBC API uses jdbc drivers to connect with the database.

Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which allows to connect and execute query with the database. JDBC is a part of the Java Standard Edition platform. JDBC provides methods to query and update data in a database, and is oriented towards relational databases.

The JDBC classes are contained in the Java package java.sql and javax.sql.

The latest version is JDBC 4.2, and is included in Java SE 8.

JDBC allows multiple implementations to exist and be used by the same application. The API provides a mechanism for dynamically loading the correct Java packages and registering them with the JDBC Driver Manager. The Driver Manager is used as a connection factory for creating JDBC connections.

JDBC connections support creating and executing statements. These are SQL's CREATE, INSERT, UPDATE and DELETE statements, or they may be SELECT statement to query the database.
Also we can execute the stored procedures available in the database through a JDBC connection.
In order to execute queries in databases JDBC uses statements using one of the following interfaces:

·         Statement – the statement is sent to the database server each and every time.
·         PreparedStatement – the statement is cached and then the execution path is pre-determined on the database server allowing it to be executed multiple times in an efficient manner.
·         CallableStatement – used for executing stored procedures on the database.

SELECT Query statements return a JDBC row result set. The row result set is used to iterate over the result set. Individual columns in a row are retrieved either by name or by column number. There may be any number of rows in the result set.

Update statements such as INSERT, UPDATE and DELETE return an update count that indicates how many rows were affected in the database.

Java Database Connectivity

Related Posts Plugin for WordPress, Blogger...