×

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

Java Math acos() Method

The acos() method of Math class computes the trigonometric Arc Cosine (inverse of cosine ) of an angle. The value returned is between 0.0 to pi. Syntax: public static double acos(double a) Parameters: The...

1 minute read.

Minimum Difference Between Groups of Size Two in Java

There is given an array with various integers in it. The goal is to divide the elements into distinct groups, each of which has just two, so that the difference...

3 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.

Bin Packing Problem Java

Assigning some items with known weights to bins with consistent capacities is necessary for the bin packing problem. The goal is to use the smallest possible boxes while ensuring everything is put...

6 minutes read.

Java String substring() method

Java String substring() method returns a part of the String. Syntax: public String substring(int startIndex) public String substring(int startIndex, int endIndex) Parameters: startIndex : starting index endIndex : ending index Returns: It returns a specified String. Throws: StringIndexOutOfBoundsException if start...

2 minutes read.

Display Unique Rows in a Binary Matrix in Java

To solve this problem, we must first locate and display the distinct rows of a supplied binary matrix afterward. We will go through how to show distinct rows inside a...

12 minutes read.

Java Double Keyword

In java primitive data types, we have two different types of data types which are Boolean and floating-point data types.In floating data type again we have four types which are...

3 minutes read.

Java Class Methods

Methods called on a class rather than a specific object instance are known as class methods. The static modifier guarantees uniform implementation across all instances of the class. Syntax public class NameOfClass...

3 minutes read.

Java AWT

Java AWT Java programming is used to develop different types of applications like window-based applications, web applications, Enterprise applications, or mobile applications. For creating standalone applications, Java AWT API is used....

11 minutes read.

Zigzag Traversal of Binary Tree in Java

In this article, you will be acknowledged about the zigzag traversal of binary tree in java and the approaches or ways in which the zigzag traversal can be done. Zigzag Traversal A...

10 minutes read.

Fall through in Java

In this article, you will be acknowledged about the fall through condition in Java along with an example program. Fall through It is a situation where there isn't a break statement in...

3 minutes read.

Java Command Line Argument

Command-line arguments are passed to the main() method when we want to pass information into a program during runtime. It is the information that directly follows the program’s name on the...

1 minute read.

Java Math subtractExact() Method

The subtractExact() method of Java Math class returns mathematical difference of the specified two arguments, throwing an exception if the result overflows int or long. Syntax: public static int subtractExact (int x,...

1 minute read.

IdentityHashMap in Java

The IdentityHashMap class is comparable to the HashMap class and is an AbstractMap implementation. However, when comparing the key, it uses reference equality rather than object equality (or values). Identity HashMap...

6 minutes read.

Delete a Cycle from a Linked List in Java

Assume a method detectAndRemoveLoop(), which examines if a given Linked List includes a loop and, if so, eliminates this Loop and returns true. This returns false if the list does...

7 minutes read.

Palindrome Partitioning problem in Java

In this article, you will be acknowledged about partitioning of the palindrome. It can be done in many ways. This article makes sure every method is discussed. Palindrome If a string remains...

6 minutes read.

Three Partition Problems in Java

In talks with leading IT organizations like Google, Amazon, TCS, Accenture, etc., this extremely intriguing subject is constantly brought up. The goal of the problem-solving exercise is to evaluate the...

8 minutes read.

Uses of Java

Java is used in many real-world Java applications, including technologies and tools. This Java programming language has become the backbone for developing many applications. In areas like embedded systems and...

3 minutes read.

Arithmetic exception in Java

Exception Handling is one of the most potent ways of handling runtime faults and preserving the application's normal flow. In Java, an exception is an out-of-the-ordinary state, and exceptions are...

3 minutes read.

Buffer reader to read string in Java

The Buffered Reader class of Java is used to read the stream of characters from the input stream. Program to read string using Buffer reader import java.io.*; class  Demo {   public static void main(String...

3 minutes read.