Understanding the Basics of JDBC (Java Database Connectivity)

Java, as a powerful and versatile programming language, enables the development of a wide range of applications. With Core Java, we can develop standalone applications—programs that run locally on a single machine. Examples include simple tools like Calculators or software like MS Word.

However, when our objective shifts towards building web applications—those that run over the internet—we step into Advanced Java. Technologies in Advanced Java include:

• JDBC (Java Database Connectivity)

• Servlets

• JSP (JavaServer Pages)

Each of these components plays a specific role:

• JSP is responsible for presentation logic—what the end user sees.

• Servlets handle business logic—processing done on the server.

• JDBC allows any Java-based application (a standalone program or a servlet) to communicate with a database.

Java Editions and Where JDBC Fits

Java is available in three primary editions:

1. JSE (Java Standard Edition) – Core Java, includes JDBC.

2. JEE (Java Enterprise Edition) – Includes Servlets and JSP.

3. JME (Java Micro Edition) – Used for embedded or mobile systems.

JDBC is part of Java SE, which makes it usable in both standalone and enterprise-level applications.

The Need for JDBC: Storage Considerations

In software development, storing data is essential—for example, customer info, billing details, or transaction logs. Storage can be categorized into:

1. Temporary Storage Areas

• Reside in memory (e.g., Heap, Stack, Method Area).

• Lost after the program or JVM terminates.

2. Permanent Storage Areas

• Retain data even after the application is closed.

• Examples: File systems, Databases, Data warehouses, Big Data technologies.

Limitations of File Systems:

• Cannot handle large volumes of data.

• Lack of a query language.

• Poor security and no enforcement of constraints.

• Risk of data duplication and inconsistency.

Why Databases?

Databases resolve the above issues:

• Can store large data volumes efficiently.

• Support SQL (Structured Query Language) for operations.

• Provide data security via authentication.

• Enforce constraints (Primary Key, Unique Key) to maintain data integrity.

Still, databases have their limitations:

• Struggle with very large-scale data (e.g., terabytes).

• Only support structured data (tables), not semi-structured (XML) or unstructured data (audio, video, images).

For massive or complex data needs, one would explore Data Warehouses or Big Data tools.

What is JDBC?

JDBC (Java Database Connectivity) is an API that enables Java applications to interact with databases.

• Part of Java SE (Standard Edition).

• A specification defined by Sun Microsystems (now Oracle).

• Actual implementation provided by database vendors through JDBC Drivers.

Features of JDBC

1. Standardized API – Code written with JDBC can be used with any database by just changing the driver.

2. Platform Independent – Since drivers are in Java, JDBC works across different platforms.

3. Easy to Perform CRUD Operations – Create, Read, Update, Delete (also referred to as CURD/SCUD by learners in training centers).

4. Support for Complex SQL Operations – Inner Joins, Outer Joins, Stored Procedures, etc.

5. Vendor Support – Widely adopted with a variety of driver implementations.

JDBC Architecture

The architecture of JDBC follows a layered approach where each component plays a critical role in enabling smooth communication between a Java application and a database.

User Interface (Java Application / Servlet) : This is the topmost layer where the user interacts with the application. It could be a:

• Standalone Java application

• Web application using Servlets/JSP

JDBC API : A standard Java API that provides classes and interfaces to connect and interact with databases. It allows developers to:

• Connect to databases

• Send SQL queries

• Retrieve results

• Handle transactions

JDBC Driver Manager : It manages the list of database drivers. Based on the connection string, it loads the appropriate driver required to interact with the desired database.

JDBC Drivers : These are database-specific implementations provided by vendors. The driver translates JDBC calls into database-specific calls. There are four types of JDBC drivers:

• Type 1: JDBC-ODBC Bridge

• Type 2: Native-API Driver

• Type 3: Network Protocol Driver

• Type 4: Thin Driver (Pure Java Driver)

(Refer to the earlier article on Types of JDBC Drivers)

Database (SQL Server, Oracle, Other Sources) : The final destination where the actual data resides. JDBC allows access to multiple types of databases (relational and even some non-relational, if drivers exist).

Steps to Create a JDBC Application

1. Load and Register Driver

2. Establish Connection between Java Application and Database

3. Create Statement Object

4. Send and Execute SQL Query

5. Process Results from ResultSet

6. Close Connection

Step 1: Load and Register Driver Class

JDBC API is a Set of Interfaces defined by Java Vendor. Database Vendor is responsible to provide Implementation. This Group of Implementation Classes is nothing but "Driver Software". We have to make this Driver Software available to our Java Program. For this we have to place corresponding Jar File in the Class Path.

We can load any Java Class by using Class.forName() Method. Hence by using the same Method we can load Driver Class.

Class.forName("com.mysql.cj.jdbc.Driver");

Note: From JDBC 4.0 V (Java 1.6 V) onwards Driver Class will be loaded automatically from Class Path and we are not required to perform this step explicitly.

Step-2: Establish Connection between Java Application and Database

Once we loaded and registered Driver, by using that we can establish Connection to the Database. For this DriverManager Class contains getConnection() Method.

Connection con= DriverManager.getConnection(jdbcurl,username,pwd);

"Jdbcurl" represents URL of the Database. "username" and "pwd" are Credentials to connect to the Database.

Eg: Connection con= DriverManager.getConnection(" jdbc:mysql://localhost:3306/first ", "uname", "pass");

Note: DriverManager will use Driver Class internally to connect with Database. DriverManager Class getConnection() Method internally calls Driver Class connect() Method.

Step-3: Creation of Statement Object

Once we established Connection between Java Application and Database, we have to prepare SQL Query and we have to send that Query to the Database. Database Engine will execute that Query and send Result to Java Application. To send SQL Query to the Database and to bring Results from Database to Java Application some Vehicle must be required, which is nothing but Statement Object.

We can create Statement Object by using createStatement() Method of Connection Interface.

Statement st = con.createStatement();

Step-4: Prepare, Send and Execute SQL Query

Once we create Statement Object, we can call the following Methods on that Object to execute our Queries.

1. executeQuery() 2. executeUpdate() 3. execute()

executeQuery() Method: We can use this Method for Select Operations. Because of this Method Execution, we will get a Group of Records, which are represented by ResultSet Object. Hence the Return Type of this Method is ResultSet.

eg: ResultSet rs = st.executeQuery("select * from movies");

executeUpdate() Method: We can use this Method for Non-Select Operations (Insert|Delete|Update) Because of this Method Execution, we won't get a Group of Records and we will get a Numeric Value represents the Number of Rows effected. Hence Return Type of this Method is int

Eg: int rowCount = st.executeUpdate("delete from employees where esal>100000"); System.out.println("The number of employees deleted:"+rowCount);

execute() method: We can use this Method for both Select and Non-Select Operations. If we don't know the Type of Query at the beginning and it is available dynamically at runtime then we should use this execute() Method.

Step-5: Process Result from ResultSet

After executing Select Query, Database Engine will send Result back to Java Application. This Result is available in the form of ResultSet. i.e. ResultSet holds Result of executeQuery() Method, which contains a Group of Records. By using ResultSet we can get Results.

ResultSet is a Cursor always locating Before First Record (BFR). To check whether the next Record is available OR not, we have to use rs.next() Method. This Method Returns True if the next Record is available, otherwise returns False.

Note: In JDBC, Index is always one based but not Zero based i.e. Index of First Column is 1 but not 0.

Step 6: Close the Connection

After completing Database Operations it is highly recommended to close the Resources whatever we opened in reverse order of opening.

1. rs.close(); It closes the ResultSet and won't allow further processing of ResultSet

2. st.close(); It closes the Statement and won't allow sending further Queries to the Database.

3. con.close(); It closes the Connection and won't allow for further Communication with the Database.

These are the Steps to Develop the JDBC application...

whille Preparing the Query we should have some basic knowledge on any database like oracle, mysql etc... For this article we are going using Mysql.

According to Database Specification, all SQL Commands are divided into following Types...

DDL (Data Definition Language) Commands: Create Table, Alter Table, Drop Table Etc

DML (Data Manipulation Language) Commands: Insert, Delete, Update

DQL (Data Query Language) Commands: Select

DCL (Data Control Language) Commands: Alter Password, Grant Access Etc..

Data Administration Commands: Start Audit, Stop Audit

Transactional Control Commands: Commit, Rollback, Savepoint Etc

According to Java Developer Point of View, all SQL Operations are divided into 2 Types...

• Select Operations (DQL)

• Non-Select Operations (DML, DDL Etc)

Basic SQL Commands

1. To Create a Table: Create table movies (no number, name varchar2(20),hero varchar2(20),heroine varchar2(20));

2. To Drop/Delete Table: drop table movies;

3. To Insert Data: insert into movies values(1,'bahubali2','prabhas','anushka');

4. To Delete Data: delete from movies where no=3;

5. To Update Data: update movies set heroine='Tamannah' where no=1;

Select Operations

Whenever we are performing Select Operation then we will get a Group of Records as Result.

Eg: select * from movies;

Non-Select Operations

Whenever we are performing Non-Select Operation then we will get Numeric Value that represents the Number of Rows affected.

Eg: update movies set heroine='Tamannah' where no=1;

We required to Set Class Path of MySql Driver Jar File while executing the code

Variable Name: CLASSPATH

Variable Value: D:\mysql-connector-java-bin.jar;.;

We have Executed about 24 JDBC codes to understand the following

1. How to Create a Table

2. How To Delete A Table

3. How to Insert a Record into Table

4. How to Insert Multiple Records into Table

5. How to Update a Record in the Table

6. How to Update Multiple Records in the Table

7. How to Delete a Record from the Table

8. How to Delete multiple Records from the Table

9. How to Select all Rows from the Table

10. How to Select all Rows from the Table based on sorting Order of the Salaries

11. How to Select particular Columns from the Table

12. How to Select Range of Records based on Address

13. How to Select Range of Records based on Salaries

14. How to Select Range of Records based on Initial Characters of the Employee Name

15. To Display Number of Rows by SQL Aggregate Function count(*)

16. How to Select highest salaried Employee Information by using SQL Aggregate Function Max

17. How to Select Nth Highest Salaried Employee Information

18. How to Display retrieved Data from the Database through HTML

19. How to execute Select and Non-Select Queries by using execute() Method

20. Execute Methods LoopHoles-1: executeQuery() Vs Non-select

21. Execute Methods LoopHoles-2: executeUpdate() Vs Select

22. Execute Methods LoopHoles-3:executeUpdate() Vs DDL

23. To Demonstrate JDBC Coding Standards

24. To Demonstrate JDBC Coding Standards with Code Reusability

All the codes for the above are solved and pushed into the Github

https://github.com/Satya7816/JAVA_JDBC_CODES/

Note: All the above codes using Mysql database

This is the Brief about the Understanding the Basics of JDBC

Thank you.