• Main Menu
  • JDBC (Java Data Base Connectivity)


    JDBC, Java Data Base Connectivity, is the Java-based access solution provided in the Java Standard Edition platform that is produced by the Oracle Corporation. JDBC is more accurately described as an API for the Java programming language that helps define how a client is able to access an established database. The API provides Java methods that are able to update and query data located in a database. Although not a requirement, the majority of the API is geared towards operations on relational databases. Additionally, there is a JDBC to ODBC bridge that allows the API to establish a connection to any ODBC accessible database from within a JVM-hosted execution environment.

    History of the JDBC API

    JDBC was first released by Sun Microsystems on February 19th, 1997 as part of JDK 1.1. Since that time, the API has been included as part of the JSE (Java Standard Edition) releases. The JDBC classes are included in the javax.sql and java.sql packages. Beginning with the 3.1 version, JDBC shifted to being developed under the JCR (Java Community Process). The JSR 54 release specifies the details of JDBC 3.0 (was included in the J2SE 1.4 release). JDBC 4.0 that was included in the Java SE 6 release includes the addition of JDBC 4.0. At the time of this writing, JDBC 4.1 was specified in JSR 221 which is considered to be a maintenance release of the API and is bundled with Java SE 7.

    What Does JDBC Do?

    JDBC provides a call-level interface for Java programmers for SQL database update commands and database manipulation. The API allows for the integration of SQL calls into the Java programming environment through providing library methods and classes that are capable of interfacing with one to many databases. Since JDBC is considered a mature API, there are a number of classes and methods that are able to interface with underlying database infrastructure in a simple but intuitive manner. JDBC becomes the conduit for sending SQL code to the database and returning the desired responses from queries. When the database operation is completed, the database connection is then closed. JDBC’s approach is fundamentally different than the pre-compilation option taken with Embedded SQL. Since Embedded SQL must be converted to the host language (C or C++), there are a number of issues that can arise with the API when compared to JDBC.

    JDBC Drivers

    A JDBC driver is a client-side adapter that is installed on client computers and not the web server. The driver is responsible for converting Java program requests to a protocol that the DBMS is able to understand. There are five (only four are recognized by much of industry) types of JDBC drivers that can be used for the majority of relational database servers:

    JDBC Driver Type 1: The Type 1 driver is responsible for invoking native code of the ODBC driver that is held locally.

    JDBC Driver Type 2: The Type 2 driver is responsible for invoking database vendor native library code on the client-side. The code is then able to communicate with databases located remotely on the network.

    JDBC Driver Type 3: The Type 3 driver is responsible for communicating with the server-side middleware which in turn communicates with the database. This is considered to be a “pure-Java” dirver.

    JDBC Driver Type 4: The Type 4 driver is created from “pure Java” and makes use of database native protocols.

    JDBC Driver Type 5: A JDBC Type 5 driver is similar to a Type 4 in that it offers a 100% client-side Java driver; however, it addresses many of the limitations previously found in Type 4 drivers. Some of the features include a greater data throughput capacity, a greater capacity to add features and functionality, and a reduction of the amount of run-time CPU memory and resource required for driver operation.

    JDBC Driver Providers

    There are a number of companies that produce JDBC drivers for use throughout industry. The following is a representative listing of associated JDBC drivers and vendors.

    DatDirect Technologies: DataDirect produces a comprehensive suite of Type 4/5 drivers for all major databases.

    IDS Software: IDS Software produces a Type 3 driver that allows concurrent access to all major databases. Some of the major features include: SSL encryption, resultset caching, dbShield, and custom data sourcing.

    OpenLink Software: Open Link provides JDBC drivers for a number of database types. These include a number of bridges that are able to provide a large range of functionality for their clients (ODBC and JDBC).

    JDBaccess: JDBaccess is a MySQL and Oracle persistence library that helps provide major database access in an API build on top of JDBC.

    JNetDirect: JNetDirect produces a full suite of JEE certified high-performance JDBC drivers for use in the commercial market.

    SchemaCrawler: SchemaCrawler is produced under an open source license and helps make the metadata in databases accessible as a POJO (Plain Old Java Object).

    Simba Technologies: The company publishes an SDK that is designed for building custom JDBC drivers for any relational database.

    RSSBus: RSSBux publishes Type 4 JDBC drivers that are designed to support web services, databases, and applications.

    How to Establish a Connection in JDBC?

    Before getting started with working with JDBC, a developer has to ensure that Java, JDBC, and the desired database management system (DBMS) is installed on the development computer. Once the proper development environment setup, the first task in JDBC programming is to establish a connection between the client application and the database (server).

    Step 1 – Load the vendor specific driver for the DBMS being used in the application. Since every DMBS can have varied behavior based on the operation(s) to be conducted, the JDBC driver manager has to be told which DBMS to use. The following code snippet demonstrates how to load an Oracle database driver:

    Class.forName(“oracle.jdbc.driver.OracleDriver”)

    Step 2 – Make the connection to the database in the code. After the appropriate driver is loaded, an instance of a Java Connection object can be created by using code similar to:

    Connection connectionObject = DriverManager.getConnection(

    “jdbc:oracle:thin:@techfaq:1521:SHR1_PRD”, username, passwd);

    In this statement the string passed to the database connection object translates to the following:

    Jdbc = the protocol for the database, oracle = the vendor of the DBMS, thin = the driver, tecfaq = the server, 1521 = the port, and SHR1+PRD = the server instance. The username and passwd fields are the appropriate username and passwords that permit SQLPLUS access to the DBMS account.

    Step 3 – Conduct operations on the open connection to the database using the connectionObject instance of the Connection class. The specific values passed for the handle to the DBMS will vary based on the type of DBMS used and various options configured in each developer’s specific case.

    How to Create a JDBC Statement Object?

    In JDBC, a Statement object is used to transmit or send SQL statements to the targeted DBMS. It is not the same as a SQL statement since a Statement object is associated with an open DBMS connection and not any one single SQL statement. Conceptually, a JDBC Statement object is similar to a channel that rides on an existing connection and passes one or many SQL statements to the DBMS. Before a Statement object can be created within Java code, an open connection must first be established.

    Step 1 – Use the following code snippet to create an instance of a Statement class object:

    Statement techFaqStatement = connectionObject.createStatement();

    Step 2 – A statement object now exists; however, there is not a SQL statement loaded that will be passed to the DBMS at this point. In JDBC, all data definition language (DDL) statements are executed using the executeUpdate method on the Statement class object. For example:

    techFaqStatement.executeUpdate(“CREATE TABLE Buy “ + “(stock VARCHAR2(40), bonds VARCHAR2(40), price REAL)”);

    techFaqStatement.executeUpdate(“INSERT INTO Sells “ + “VALUES”(‘Tech FAQ’,’Memebridge’,4.00)”);

    Step 3 – When conducting a query on a DBMS using a SQL SELECT statement, a set of tuples will be returned as the result of the operation. The state of the database will not be changed. JDBC provides a corresponding method called executeQuery that returns the result of the operation as a ResultSet object. For example:

    String stock, company;

    float price;

    ResultSet myRs = stmt.executeQuery(“SELECT * FROM Sells”);

    While (myRs.next()) {

    stock = myRs.getString(“buy”);

    company = myRs.getString(“bonds”);

    price = myRs.getFloat(“price”);

    }

    The tuples returned in the above example are contained in the myRs variable that is an instance of the ResultSet class.

    How to use the JDBC Prepared Statement Object

    In many cases, it is easier to use a PreparedStatement object to send SQL statements to a DBMS. The distinguishing feature that helps separate it from the Statement superclass is that it can be passed a SQL statement upon instantiation. The statement is then passed directly to the DBMS when compiled and the PreparedStatement is then associated as a channel with both a connection and compiled SQL statement. The following example demonstrates how to create a Prepared Statement object that takes three input parameters:

    PreparedStatement techfaqUpdatePrice= connectionObject.prepareStatement(

    “UPDATE Sells SET price = ? WHERE stock =? AND bonds =?”);

    Before a PreparedStatement instance can be executed; however, there should be values supplied for the parameters of the database. This can be accomplished through using the various set methods defined in the class to include: setInt, setFloat, setString, setDouble, etc.

    JDBC Transactions

    In the JDBC API, SQL statements are able to be grouped into one transaction. As a result, the proven database properties of atomicity, consistency, isolation, and durability can be ensured using the JDBC transactional features through the use of the Connection object. Once the object and connection is created, it will be in the auto-commit mode by default. As a result, each SQL statement will be treated as a transaction and will be committed as soon as execution concludes.

    Step 1 – The auto commit mode can be togged through a simple method call on the Connection object similar to the following example:

    //sets the Auto Commit feature to the “off” state.

    connectionObject.setAutoCommit(false);

    //sets the Auto Commit feature to the “on” state.

    connectionObject.setAutoCommit(true);

    After the Auto Commit feature is set to off, JDBC will not commit any SQL statements until the commit() method has been invoked on the connection object similar to:

    connectionObject.commit();

    Step 2 – Prior to the commit operation being conducted on the database, the rollback() method can be invoked in order to restore database values to the previous commit point. As an example:

    connectionObject.setAutoCommit(false);

    Statement myStatment = connectionObject.createStatement();

    myStatement.executeUpdate(“INSERT INTO Sells VALUES(Tech Faq, ‘Bank’, 4.00)” );

    connectionObject.rollback();

    stmt.executeUpdate(“INSERT INTO Sells VALUES(‘Memebridge’, ‘New York’, 7.00)” );

    connectionObject.commit();

    connectionObject.setAutoCommit(true);

    JDBC Exception Handling

    Java exception handling allows developers to handle exception conditions in a controlled fashion in JDBC applications. When a program encounters an exception condition, the current program execution will cease and control will pass to the nearest catch clause in the development code. If there is not an applicable catch clause, then the program execution will terminate. JDBC exception handling is very similar to Java IO exception handling except for the fact of the most common exception being thrown being the java.sql.SQLException.

    In JDBC, there are also SQL Warnings that are subclasses of the SQLException class. Unlike full blown exceptions; however, they will not terminate execution of the application if not handled appropriately. Instead, a SQL Warning represents a nonfatal error or an unexpected condition in the program execution.

    Got Something To Say:

    Your email address will not be published. Required fields are marked *

    Databases
    } 170 queries in 0.355 seconds.