Web Development Toolkit for Java Developers: Build dynamic, secure, and scalable web applications with Java (English Edition)

Chapter 1

Database Connectivity From Java

Introduction

Handling data is very important for any programming language or application. Therefore, understanding how to access the database from the language is very significant. There are different approaches to access the database from a Java application. This chapter covers the major components and their structures, so one can understand and use them for accessing database from the application. Based on the requirements of accessing the database, one can select a different driver to handle the database. The chapter shows the merits and demerits of using different types of drivers so that one can select the right driver while programming the application for database usage.

Structure

In this chapter, we will cover the following topics:

Introduction to JDBC API

Core components of JDBC API

JDBC drivers

Types of JDBC drivers

Summary

Objectives

The chapter covers the concept of drivers, types of drivers and core components of JDBC. The understanding of these components will help one select the right type of driver for establishing the database connectivity based on one’s requirement.

Introduction to JDBC

Java application programming interface is a list of all classes that are part of the Java Development Kit (JDK). These prewritten classes provide a tremendous amount of functionality to a programmer. To connect any database and access data from Java environment, the support of Java Database Connectivity (JDBC) API is provided to the programmers. Using the JDBC API, you can access virtually any data source, from relational databases to different files. The JDBC API comprises two packages:

java.sql

javax.sql

You automatically get both packages when you download the Java Platform Standard Edition (Java SE) 8. To use the JDBC API with a particular database management system, you need a JDBC technology-based driver to mediate between JDBC technology and the database.

Java JDBC is a Java library to connect and execute query with the database.

JDBC API uses JDBC drivers to connect with the database; refer to Figure 1.1:

Figure 1.1: Use of JDBC API in Java application to connect with a database

The JDBC library includes APIs for performing the basic operations like the following:

Query the database: Read the data from the database.

Query the database and result set meta data: The database metadata contains information about the database itself, for instance, information about the tables defined the columns in each table, the data types, etc.

Update the database: Write data to the database, which includes adding new records or modifying the existing records.

Perform transactions: A transaction groups multiple updates and possible queries, into a single action. Either all the actions are executed or none of them are.

Core components of JDBC API

The JDBC API provides the interfaces and classes that allow you to perform the operations required to connect and communicate with a database from a Java application. The components include the following:

DriverManager: This class manages a list of database drivers.

Driver: This interface handles communications with the database server. A JDBC driver is a collection of Java classes that enables you to connect to a certain database. A JDBC driver implements many of the JDBC interfaces, which hide the complexity of connecting to a database.

Connection: After the JDBC driver is loaded and initialized, we need to connect to the database; this interface has all methods for contacting a database. All communication with the database is done with a proper connection. An application can have more than one connection open to a database at a time.

Statement: Objects created from this interface are used to submit the SQL statements to the database. A Statement is what you use to execute queries and updates against the database. There are a few different types of statements that we can use as per our requirements, but each statement corresponds to a single query or update at a time.

ResultSet: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. The result obtained after executing a query is held and allowed for navigation using the ResultSet.

SQLException: This class handles any errors that occur in a database application. The following are the core components of JDBC API.

The below figure shows the major components used in establishing the database connection.

Figure 1.2: Core components of JDBC API

The Java application communicates with the core components to load the Driver, and then it establishes a connection, creates a statement, executes it, retrieves the ResultSet, traverses and processes the result. If anything goes wrong during the database communication after loading the driver, SQLException are thrown based on the type of error.

JDBC drivers

JDBC drivers implement the defined interfaces in the JDBC API for interacting with your database server. JDBC driver implementations vary because of the wide variety of operating systems and hardware platforms on which Java operates. Sun has divided the implementation types into four categories:

Type 1: JDBC-ODBC (Open Database Connectivity) Bridge

Type 2: Native-API driver

Type 3: Network-Protocol driver (Middleware driver)

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

Types of JDBC drivers

There are different types of drivers available for establishing the database connection; let’s look at them.

Type 1: JDBC-ODBC Bridge

It requires you to configure a Data Source Name (DSN) on your system. A type 1 JDBC driver consists of a Java part that translates the JDBC interface calls to ODBC calls. An ODBC bridge then calls the ODBC driver of the given database. Type 1 drivers were mostly used when there were no type 4 drivers (all Java drivers).

Advantages

Easy to use

Can be easily connected to any database

Disadvantages

Performance degraded because JDBC method call is converted into the ODBC function calls.

The ODBC driver needs to be installed on the client machine.

Following is the flowchart of Type 1 Driver:

Figure 1.3: Type 1 Driver

Type 2: Native-API driver

A type 2 JDBC driver is like a type 1 driver, except the ODBC part is replaced with a native code part instead. The native code part is targeted at a specific database product, and JDBC API calls are converted into native C/C++ API calls.

Advantage

Performance upgraded than JDBC-ODBC bridge driver

Disadvantage

The Native driver needs to be installed on each client machine.

The Vendor client library needs to be installed on the client machine.

Following is the flow chart of Type 2 Driver:

Figure 1.4: Type 2 Driver

Type 3: Network-Protocol

JDBC clients use standard network sockets. A type 3 JDBC driver is an all-Java driver that sends JDBC interface calls to an intermediate server. The intermediate server then connects to the database on behalf of the JDBC driver.

Advantage

No client-side library is required because of the application server.

Disadvantages

Network support is required on the client machine.

It requires database-specific coding in the middle tier.

Maintenance of NetworkProtocol driver becomes costly.

Following is the flow chart of Type 3 Driver:

Figure 1.5: Type 3 Driver

Type 4: DatabaseProtocol driver

It communicates directly with the vendor's database through socket connection. A type 4 JDBC driver is an all-Java driver that connects directly to the database. It is implemented for a specific database product.

Advantages

Better performance than all other drivers

No software required at client or server side

Disadvantages

Drivers depend on the database.

Following is the flow chart of Type 4 Driver:

Figure 1.6: Type 4 Driver

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 for your database yet.

The type 1 driver is not considered a deployment-level driver and is typically used for development and testing purposes only.

Apart from the four drivers listed by the sun recently, there is a type 5 driver.

Type 5 JDBC Drivers

Type 5 JDBC drivers offer the same client-side, single tier, 100% Java architecture of type 4 JDBC drivers but address the limitations of many of the type 4 JDBC drivers available today. Progress DataDirect is the only vendor of Type 5 JDBC drivers. It offers superior application performance and scalability, codeless enhancement and all-in-one deployment.

Conclusion

The chapter covered the use of JDBC, explained the major components of JDBC, and listed and explained the types of drivers available to communicate with the database. The chapter is the foundation required to start with database connectivity from the Java language.

The next chapter will cover the details of how to access the database from the Java language.

Points to remember

Java Database Connectivity (JDBC) is used to connect databases from the Java language; the type of driver can be selected based on the requirement of communication with the database.

Java drivers allow you to interact with the database and access it.

Multiple choice questions

What does JDBC stand for?

Java Database Connectivity

Java Driver for Basic Connection

Joint Database Connectivity

Joint Driver for Basic Connection

Which of the following type of JDBC driver should be used if your Java application is accessing multiple types of databases at the same time?

Type 1

Type 2

Type 3

Type 4

Answers

1. a

2. c

Questions

What is JDBC?

What is a JDBC driver?

What are the core components of JDBC API?

Explain the types of JDBC driver.

Key terms

JDBC: It refers to the Java API to connect and execute query with the database.

JDBC drivers: JDBC drivers implement the defined interfaces in the JDBC API for interacting with your database server.

Join our book's Discord space

Join the book's Discord Workspace for Latest updates, Offers, Tech happenings around the world, New Release and Sessions with the Authors:

https://discord.bpbonline.com

Chapter 2

Performing Insert, Update, Delete and Select Operations

Introduction

This chapter covers writing the necessary queries to perform the basic database creation and manipulation tasks from the Java platform. The concept of database remains the same, but writing the query from Java language is little different than writing query from the database itself This chapters deals with the syntax basic queries that we can write from the Java language.

Structure

In this chapter, we will discuss the following topic:

Steps to connect and access the database from a Java application

Metadata

Creating application to access the Oracle database using IDE Netbeans

Create a table in Oracle from Java

Alter table in Oracle from Java

Insert records in table from Java

Update records in table from Java

Delete records in table from Java

Objectives

After studying this chapter, you should be able to access the database and perform basic operations like creating a table, inserting records in the table, altering it, deleting the records, updating the records, and performing the basic database tasks from the Java program.

Steps to access the database from Java application

These are the steps involved in building a JDBC application:

Import the packages

Register the JDBC driver

Open a connection

Create a statement

Execute a query

Extract data from result set

Clean up the environment

Import the packages

To use the classes and interfaces provided by API, we need to import the package in which our desired functionality falls. By default, java.lang is imported in the application, so all the other packages have to be imported explicitly. To use the JDBC API, we need to import java.sql or javax.sql, as they provide the primary and necessary classes for establishing the connection and communicating with the database. The import statement is as follows:

import java.sql. *;

It is a bad practice to write an ‘*’ after the package name; instead, write the name of classes required to use as part of application programming.

Register the JDBC driver

We need to register the driver to initialize communication with the database. This step causes the JVM to load the desired driver implementation into memory so that it can fulfill your JDBC requests.

You must register the driver in your program before you use it. Registering the driver is the process by which the Oracle driver's class file is loaded into the memory so that it can be utilized as an implementation of the JDBC interfaces.

Class.forName()

It dynamically loads the driver's class file into memory, which automatically registers it.

This method is preferable because it allows you to make the driver registration configurable and portable.

The following figure shows the code snippet for registering the Oracle driver using the forName method:

Figure 2.1: Register Oracle Driver using forName method

DriverManager.registerDriver()

The static DriverManager.registerDriver() method can be used to register the driver.

Use it if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

The following figure shows the code snippet for registering the Oracle driver using the registerDriver method:

Figure 2.2: Register Oracle Driver using registerDriver method

Open a connection

After loading the driver, you can establish a connection using the DriverManager.getConnection() method, as follows:

getConnection(String url)

getConnection(String url, Properties prop)

getConnection(String url, String user, String password)

A database URL is an address that points to your database; based on the database, the url can be formed as shown in the following table:

Table 2.1: Database and URL format

Based on the different forms of getConnection, the following approaches can be used. The following snippet provides the details of url, username and password to set up the connection:

String URL = jdbc:oracle:thin:@localhost:1521:EMP;

String USER = username;

String PASS = password

Connection conn = DriverManager.getConnection(URL, USER, PASS);

The following snippet shows the use of the property file to read the username and password to set up the connection:

Figure 2.3: Set up connection with property files

Creating a Statement

It is used for building and submitting an SQL statement to the database. Once a connection is established, we can interact with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces 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. Below table provides the type of Statements available:

Table 2.2: Types of Statements

Creating a Statement object

Before we use a Statement object to execute a statement, we need to create a statement using the Connection object’s createStatement() method, as shown here:

Figure 2.4: Create statement object

Creating the PreparedStatement objects

The PreparedStatement interface extends the Statement interface, which gives you added functionality with a couple of advantages over a generic Statement object. This statement gives you the flexibility of supplying arguments dynamically. We need to create a prepared statement using the prepareStatement() method, as shown in the below code. The following code snippet shows the use of query parameter:

Figure 2.5: Create preparedstatement object

Creating the CallableStatement objects

Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object, which would be used to execute a call to a database stored procedure. We need to create a callable statement using the prepareCall() method, as shown in the next section.

Creating CallableStatement object

Suppose you need to execute the following Oracle stored procedure:

CREATE OR REPLACE PROCEDURE getStudentName

(STUDENT_ID IN NUMBER, STUDENT_NAME OUT VARCHAR) AS

BEGIN

SELECT name INTO STUDENT_NAME

FROM Student

WHERE ID = STUDENT_ID;

END;

CallableStatement cstmt = null;

try {

String SQL = {call getStudentName (? ?)};

cstmt = con.prepareCall(SQL);

. . .

}

catch (SQLException e) {

. . .

}

The following code shows the creation of procedure and the use of callable statement to invoke the procedure:

Figure 2.6: Create statement object

The statement can be closed using the Statement object’s close() method.

Executing a query

Once we have created a Statement object, we can use it to execute an SQL statement with one of its three execute methods:

boolean execute (String SQL): It returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL Data Definition Language (DDL) statements or when you need to use truly dynamic SQL.

int executeUpdate (String SQL): It returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you the total number of affected rows are returned. , this method is used with e, an INSERT, UPDATE, or DELETE statement.

ResultSet executeQuery (String SQL): It returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

Extracting data from result set

The SQL statements that read data from a database query return the data in a result set. The java.sql.ResultSet interface represents the result set of a database query. A ResultSet object maintains a cursor that points to the current row in the result set. The term result set refers to the row and column data contained in a ResultSet object. The methods of the ResultSet interface can be divided into three categories:

Navigational methods

Get methods

Update methods

The cursor is movable based on the properties of the ResultSet. These properties are designated when the corresponding Statement that generates the ResultSet is created. JDBC provides the following connection methods to create statements with the desired ResultSet:

createStatement(int RSType, int RSConcurrency)

prepareStatement(String SQL, int RSType, int RSConcurrency)

prepareCall(String sql, int RSType, int RSConcurrency)

In the first method , the first argument indicates the type of a ResultSet object, and the second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable. The possible RSType are given as follows. If you do not specify any ResultSet type, you will automatically get one that is TYPE_FORWARD_ONLY..

Table 2.3: Types of ResultSet

Concurrency of ResultSet: The possible RSConcurrency are given as follows. If you do not specify any concurrency type, you will automatically get one that is CONCUR_READ_ONLY.

Table 2.4: Types of Concurrency Support in ResultSet

Navigation methods provided by the ResultSet: These methods involve moving the cursor to the derived ResultSet. The most common navigation methods are listed in Table 2.5:

Table 2.5: Navigation methods provided by ResultSet

Viewing a ResultSet: The ResultSet interface contains dozens of methods for getting the data of the current row. There is a get method for each of the possible data types, and each get method has two versions:

One that takes in a column name

One that takes in a column index

For example, if the column you are interested in viewing contains an int, you need to use one of the getInt() methods of ResultSet.

Table 2.6: Retrieval methods provided by ResultSet

For each primitive data type; for types like Object, String, and URL; and for SQL data types, there are get methods.

Updating a ResultSet: The ResultSet interface contains a collection of update methods for updating the data of a result set.

Table 2.7: Update methods provided by ResultSet

Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database. To update your changes to the row in the database, you need to invoke one of the following methods:

Table 2.8: Update methods for changing row value provided by ResultSet

Clean up the environment

To clean up the environment, we need to close the open connections, statements, etc. as we explicitly open the connection. It is a good practice to close the connection once the operations on the database are complete.

MetaData

Metadata refers to data about data. To get details about the results received after executing query, we use the ResultSetMetaData; likewise, to receive more information about the database being used, we can use DatabaseMetaData. These are provided as interfaces, with several methods that we can explore and use for our purpose.

ResultSetMetaData

MetaData means to get the more detailed data . In terms of the result that we obtain after executing a query, we may need to know column name, column type, total number of columns, etc. We can obtain this data from the methods provided by the ResultSetMetaData interface. Commonly used methods are listed in Table 2.9:

Table 2.9: Commonly used methods of ResultSetMetaData interface

DatabaseMetaData

In terms of database, we may need to know database name, version, driver name, etc. This data can be obtained from the methods provided by