×

JDBC Program in Java

JDBC Program in Java

JDBC is an API that defines how a client may access a database. It is a part of Java Standard Edition (Java SE). JDBC stands for Java Database Connectivity. It is used to connect a Java program to the database. Thus, using JDBC, we can retrieve, update, and delete data from the database. The JDBC program in Java shows how to connect to a database and execute DML (Data Manipulation Language) and DDL (Data Definition Language) statements regarding the database.

Connecting to a Database

There are 5 steps that are involved in the connection to a database.

1) Register Driver: The forName() method of the Class class is used for driver registration. For example, to register the driver of MySQL database, the following statement is used.

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

2) Create connection: After driver registration, a connection to the database is required. To achieve the same, the getConnection() method is used.

 Connection connection = DriverManager.getConnection(
jdbc:mysql://localhost:3306/database_name, userName, password);  

3) Create Statement: The third step is to create a statement object. The statement object is responsible for the query execution. The following statement creates a statement object.

Statement sttmnt = connection.createStatement(); 

4) Execute Queries: The penultimate step is to execute the query using the statement object created in the previous step. The executeQuery() method does the execution. This method takes the query statement in its parameter.

ResultSet resSet = sttmnt.executeQuery(Query statement); 

Query statement is of type string.

5) Close Connection: After completing the database related task it is required to close the database connection, which is done by invoking the close() method. The following statements shows how the connection can be closed.

 resSet.close();
sttmnt.close();
connection.close(); 

Before writing the code, it is important to know an essential prerequisite for connecting to a database.

To connect to a database, its concerned connector jar file is required. In the following code, the connection is getting established to a MySQL database. Therefore, it is required to have a connector jar file for MySQL. While executing the following code, use the command written below.

Java -cp .;mysqlconnector.jar MySqlConnExample

In the classpath, it is mandatory to give the location of the connector jar file; otherwise, exceptions are raised.

The following program uses the steps, defined above, to connect to MySQL and then demonstrates the execution of queries.

FileName: MySqlConnExample.java

 // Importing the sql package
import java.sql.*;
// Importing the sql package
import java.sql.*;
public class MySqlConnExample
{
// URL for the database
// 3306 is the TCP port number
// Database name is first
static final String DATABASE_URL = "jdbc:mysql://localhost:3306/first";
// driver class
static final String DRIVER = "com.mysql.jdbc.Driver";
// User name and password for accessing the database
static String usrName = "root";
static String pswd = "root";
// driver method
public static void main(String argvs[])
{
Connection connection = null; // for connection
String qry = null; // for storing queries
Statement sttmnt = null; // for query execution
ResultSet resSet = null; // for storing the result of query
try
{
// Registering the DRIVER
Class.forName(DRIVER); 
System.out.println("Connecting to the database");
// Creating a connection to the database
connection = DriverManager.getConnection(DATABASE_URL, usrName, pswd); 
// the query to be executed
// orderId, customerId, city are the fields/ column names
// orders is the name of a table present in the database first
qry = "select orderId, customerId, city from orders";
// for query execution
sttmnt = connection.createStatement(); 
// excuting the query and storing the result
resSet = sttmnt.executeQuery(qry); 
while(resSet.next() ) 
{
// Fetching the results using the column name
int oId  = resSet.getInt("orderId");
int cId = resSet.getInt("customerId");
String city = resSet.getString("city");
//printing the values
System.out.print("Order ID: " + oId);
System.out.print(", Customer ID: " + cId);
System.out.println(", City: " + city);
}
}
catch(SQLException sqlExpn)
{
               // For catering the errors generated from JDBC
               sqlExpn.printStackTrace();
}
catch(Exception expn)
{
               // For catering the errors generated from Class.forName
               expn.printStackTrace();
}
finally
{
               try
               {
                              // doing the cleaning up work
                              // closing the connection
                              resSet.close();
                              sttmnt.close();
                              connection.close();
                               System.out.println("Connection Closed");
               }
               catch(SQLException sqlExpn)
               {
                               sqlExpn.printStackTrace();
               }
}
}
} 

Output:

 Connecting to the database
Order ID: 9321, Customer ID: 10, City: Gorakhpur
Order ID: 10202, Customer ID: 5, City: Noida
Order ID: 10292, Customer ID: 5, City: Delhi
Order ID: 18292, Customer ID: 2, City: Patna
Order ID: 78421, Customer ID: 3, City: Pune
Order ID: 80370, Customer ID: 9, City: Raipur
Order ID: 84170, Customer ID: 6, City: Nagpur
Order ID: 86770, Customer ID: 8, City: Hapur
Connection Closed 

Explanation: First, we are establishing the connection with the database using the appropriate credentials. Then we are fetching the result from the table Orders of the database. Notice, the closing of the connection is done in the finally block. Because no matter what happens eventually, the established connection has to be terminated eventually, and execution of the finally block is not dependent on the generation of exceptions.

Note: Not only MySQL, we can also establish a connection to other databases like Oracle, Postgres, etc. To achieve that, the driver class for the databases is required. Also, the connector jar file of the concerned database must be known to the classpath of the program.


Related Topics

How to compare two dates in different format in Java?

We need to compare two dates frequently when coding. Real-world examples include sorting a list of persons by age or keeping track of students' attendance. To compare two dates, we...

7 minutes read.

Java Math floorDiv() Method

The floorDiv () method of Math class returns the largest integer value that is less than or equal to the algebraic quotient. It firstly divides the dividend and divisor and...

2 minutes read.

String Programs in Java

String Programs in Java: In Java, a String is an immutable object that represents a sequence of characters. For example, “Tutorial” is a string that consists of 8 characters: ‘T’,...

10 minutes read.

Java Thread Lifecycle: States and Stages

Multithreading Multithreading is one of the most important features in object-oriented programming, and this multithreading can be implemented by various object-oriented programming languages like Java, Python, and C++. A multithreaded process...

7 minutes read.

Alice and Bob Problem Java

Alice and Bob were two friends who liked to play games. Both together found a game, which has the description below. The game begins with an integer num, used to create...

2 minutes read.

Comparetoignorecase in Java

In Java, the function compareToIgnoreCase ( ) is part of the String class, which is part of the java.lang package. It is used to compare any two strings while disregarding...

4 minutes read.

Types of Events in Java

One of Java's key ideas is the principle of an event. Events in Java are activities that result in a change in the state or behavior of an object. The...

4 minutes read.

How to Import Packages in Java

To know about the importing the packages of Java, we need to understand about how to packages work. Packages The package in Java is a collection of Classes and Interfaces. The packages...

3 minutes read.

File Operation in Java

In Java, a file is an Abstract data type. These are used to store the data which is related. Files are named storage locations. With a file we can perform...

7 minutes read.

How to resolve Illegal state exceptions in Java

What is IllegalStateException? When a method is called at the incorrect time, a runtime exception called an IllegalStateException is raised in Java. This exception is utilized to show that a method...

3 minutes read.

Java Coding Software

Desktop and web apps are created using Java, an object-oriented programming language. Java code may be executed on any platform, making it platform-independent. A text editor, tool, or piece of...

9 minutes read.

DatabaseMetaData in Java

The Data about another data is called Meta data. The DatabaseMetaData interface has the meta data of the database present in the system. It consists of database product name, total...

2 minutes read.

Java File

Java file class implements the concept of file handling. It has several methods, such as deleting, creating, reading, and updating files. This class allows java users to perform various operations...

5 minutes read.

Int vs Integer in Java

In Java, numerical data can be stored using int or Integer. int and Integer both have different definitions but similar usage in Java. What is int in Java? int is a primitive...

4 minutes read.

Big Decimal class in Java

The fairly Big pretty Decimal class provides operations for arithmetic, rounding, comparison and format conversion in a sort of big way. It can handle generally large and very small floating-point...

6 minutes read.

Java Integer toUnsignedString() method

The toUnsignedString() method of Java Integer class returns a string representation of the argument as an unsigned decimal value. The second syntax returns a string representation of the given argument as...

2 minutes read.

Java Math toIntExact() Method

The toIntExact() method of Java Math class returns the int value of the given long argument, throwing an exception if the value overflows an int. Syntax: public static int toIntExact (long value) Parameters: The...

2 minutes read.

For Loop Program in Java

For Loop Program in Java The for-loop program in Java is written when we want a particular set of statements to be executed repeatedly until the given criteria/ condition is met....

8 minutes read.

Java Boolean logicalXor() Method

The logicalXor() method of Java Boolean class returns the result of implementing logical XOR operation on the specified Boolean operands. Syntax: public static boolean logicalXor (boolean a, boolean b) Parameters: The parameters ‘a’ and...

2 minutes read.

Graphics Program in Java

Graphics Program in Java The graphics program in Java is part of the Swing program in Java. In this section, we will learn about the implementation of custom graphics using some...

6 minutes read.