Steps to Design JDBC Applications in Java

Back to: Java Tutorials For Beginners and Professionals

Steps to Design JDBC Applications in Java

In this article, I am going to discuss Steps to Design JDBC Applications in Java with Examples. Please read our previous article where we discussed JDBC Drivers in Java Applications. At the end of this article, you will understand the following pointers in detail.

1. Steps to Design JDBC Applications in Java
2. Establishing Connection
3. Creating Statement Object
4. Submitting SQL Statement
5. Database Connectivity with Oracle
6. Connection Interface in JDBC
7. Statement Interface in JDBC

Steps to Design JDBC Applications in Java:

Let us understand how to perform CRUD operations in Java Applications using JDBC step by step. The following diagram shows the different steps required to develop a JDBC application in Java.

Let us discuss each step in detail.

Establishing Connection:

In the JDBC programming model, the first step is to establish the connection between the java application and the database server. Establishing the database connection in a java program involves two steps

Step1. Loading JDBC driver from secondary memory into primary memory.

First, you need to load the driver or register it before using it in the program. Registration is to be done once in your program. In java.lang.Class class, there is a static method “forName”. This method is used to load the JDBC driver into the application process space.

Syntax: public static void forName(String className) throws ClassNotFoundException 

You can register the Driver by following two ways :

1. Class.forName(“driver class name”) : Here we load the driver’s class file into memory at the runtime. No need of using new or creation of objects.
2. DriverManager.registerDriver(): DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver class at compile time.

When the forName() method is successfully executed, three things happen in the background

1. The driver class is loaded into memory.
2. The driver class object is created.
3. The driver class is registered with DriverManager.

Step2.Requesting for database connection

In order to establish a communication with the database, you must first open a JDBC connection to the database. After loading the driver, establish a connection. In java.sql.DriverManager class there is a static method “getConnection”. This method is used by the java program to request the database connection. Following are three methods of DriverManager.getConnection() :

1. getConnection(String url)
2. getConnection(String url, Properties prop)
3. getConnection(String url, String user, String password)

Syntax :
public static Connection getConnection(String url,String name,String password) throws SQLException

Example:
Connection con = DriverManager.getConnection(“connection string”, “user name”, “password”);

Once the above statement is executed, the client connects to the database server. Object-oriented representation of JDBC client’s session with the database server is nothing but java.sql.Connection object.

Note: Internally getConnection() method call connect method of connection class.

Creating Statement Object:

Once a connection is established you can interact with the database. The createStatement() method of the Connection interface is used to create a statement. The object of the statement is responsible to execute queries with the database.

Syntax : public Statement createStatement()throws SQLException  
Example: Statement st = con.createStatement();

The Statement object is designed to submit SQL statements from the JDBC client to the DBMS (via JDBC driver).

Submitting the SQL Statement:

Now its time to process the result by executing the query. The java.sql.Statement object has two important methods to submit SQL statements to the DBMS

1. executeUpdate(String dml):– The executeUpdate(SQL query) method of Statement interface is used to execute queries of updating/inserting. This method is used to submit DML (INSERT, UPDATE, and DELETE) SQL statements.
2. executeQuery (String drl):– The executeQuery() method of the 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. This method is used to submit the SELECT statement.

Syntax : public ResultSet executeQuery(String sql) throws SQLException

Close the Connection

So finally we have sent the data to the specified location. By closing the connection object statement and ResultSet will be closed automatically. The close() method of the Connection interface is used to close the connection.

Syntax : public void close()throws SQLException 

Let us understand the above-discussed steps with an example. We are going to interact with Oracle Database.

Database Connectivity with Oracle

To connect the java application with the oracle database, we need to know the following information for the oracle database:

1. Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
2. Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localhost:1521:xe where JDBC is the API, Oracle is the database, thin is the driver, localhost is the server name on which oracle is running, we may also use the IP address, 1521 is the port number and XE is the Oracle service name. You may get all this information from the tnsnames.ora file.
3. Username: The default username for the oracle database is the system.
4. Password: It is the password given by the user at the time of installing the oracle database.

Create a Table

Before establishing a connection, first, we need to create a table in the oracle database. Please execute the below SQL query to create a table:

CREATE TABLE ACCOUNT (accnonumber(8) primary key, name varchar2(12), balance number(8,2));

Sample Program to connect with Oracle database

Code Explanation:

Line1: By importing the java.sql package, JDBC API is made available to the JDBC application.

Line2: A user-defined class “AccountStoringApplication” is defined here.

1. This JDBC application is a standalone application.
2. Every standalone application should have a main method in java.
3. To encapsulate the main method, the class “AccountStoringApplication ” is defined here.

Line 4 & 5: the main method is defined as is standalone JDBC application.

Within the main method body during the forName() method call there is a chance of java.lang.ClassNotFoundException raising. During other JDBC API calls, there is a chance of java.sql.SQLException getting raised. These are checked exceptions. These checked exceptions must be handled. Non-handling of checked exceptions in a java application is a syntactical error.

To pass (temporarily handled) on the exception handling duty to the higher level and at the same to overcome the compilation error, a throws clause is written for the main method.

Line6:

The main method is calling forName() method on the Class(name of the class) class by specifying oracle corporation developed TYPE- 4 driver class as an argument. The forName() method loads the oracle driver class from secondary memory into primary memory.

In OracleDriver class (in fact in every driver class) there is a static block. In that block, two things are performed

1. Driver class object creation.
2. Registering it with DriverManager

Static
{
       OracleDriver d = new OracleDriver();
       DriverManager.registerDriver(d);
}

If the specified driver class is not found at the specified location, the forName() method causes ClassNotFoundException. In the case of OracleDriver, we need to place any one of the following jar files into the classpath.

1. classes12.jar
2. ojdbc14.jar
3. jdbc5.jar
4. ojdbc.jar

For example: set CLASSPATH = .;\ojdbc14.jar

Here dot (.) represent the current directory.

Line7:

The main method is calling the getConnection() method on DriverManager class by supplying the connection string as the first argument, username as the second, and password as the third argument. (Username and password are the database user name and password).

The connection string is “jdbc:oracle:thin:@localhost:1521:xe”

Here,

1. jdbc = main protocol
2. oracle = sub protocol
3. thin = type of driver (here type 4)
4. localhost = IP address of the current machine
5. 1521 = port number
6. xe = database service name.

Note: port number and database service name we have to check in the “tnsnames.ora” file

Line 8 & 9:

Creating a Statement object and executing the SQL statement against the Oracle database.

Line 13 & 14:

Closing the statement and connection object.

Note: For the insert, update, and delete operations, the DBMS returns an integer number. In the case of INSERT if the record is successfully inserted into the database then DBMS returns 1. In the case of UPDATE if the record is successfully updated then it returns the no i.e. updated number of records. And in the case of DELETE, it returns 1 if the successful record is deleted else it returns 0.

Connection Interface in JDBC

The java.sql.Connection interface represents a session between a java application and a database. All SQL statements are executed and results are returned within the context of a Connection Object. The connection interface is mainly used to create java.sql.statement, java.sql.PreparedStatement and java.sql.CallableStatement objects.

For example :
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“dbc:odbc:DSN”);

Methods of Connection Interface

1. public Statement CreateStatement(): creates a statement object that can be used to execute SQL queries.
2. public Statement createStatement(int resultSetType, int resultSetConcurrency): creates a statement object that will generate ResultSet objects with the given type and concurrency.
3. public void setAutoCommit(boolean status): is used to set the commit status. By default it is true.
4. public void comit(): saves the changes made since the previous commit/rollback permanent.
5. public void rollback(): Drops all changes made since the previous commit/rollback.
6. public void close(): closes the connection and releases JDBC resources immediately.

Statement Interface in JDBC

Once a connection is obtained we can interact with the database. The JDBC Statement, CallableStatement, and PreperedStatement interface define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database. They also define methods that help bridge data type differences between Java and SQL data types used in a database.

Methods of Statement Interface

1. public ResultSet executeQuery(String sql): is used to execute the SELECT query. It returns the object of ResultSet.
2. public int executeUpdate(String sql): is used to execute the specified query, it may be created, drop, insert, update, delete, etc.
3. public Boolean execute(String sql): is used to execute queries that may return multiple results.
4. public int[] executeBatch() : is used to execute batch of commands.

In the next article, I am going to discuss CRUD Operation in Java using JDBC and Oracle databases. Here, in this article, I try to explain the Steps to Design JDBC Applications in Java with Examples. I hope you enjoy this Steps to Design JDBC Applications in Java article.