Establishing JDBC Connection in Java
Connecting the front end and the back end is a vital part of Java programming. The concept of a database plays a major role in achieving this. Before knowing about establishing a JDBC connection in Java, let us first recap the basics of it.
What is a JDBC?
JDBC stands for Java Database Connectivity. It is nothing but the advanced version of the Open Database Connectivity (ODBC). JDBC is an API specification, that helps in moving the data from the frontend to the backend. Therefore, it establishes a link between the two which makes the programmer send the data from Java code and store it in the database for later use.
Why did JDBC come into existence?
The ODBC model that existed before had a lot of drawbacks. This is because it was platform-dependent. The languages used to build the ODBC API are C, C++, and Core Java. To remove this drawback of platform dependency we incorporate Java to create a platform-independent database API. This is the JDBC that we will be learning about in this article.
Steps for Connectivity between Java Program and Database:
1. Importing the Packages
2. Loading the drivers using the forName() method
3. Registering the drivers using the DriverManager
4. Establishing a connection using the Connection class object
5. Creating a statement
6. Executing the query
7. Closing the connections
1. Importing the packages:
Import the required packages to incorporate JDBC.
Syntax to import a package:
import packageName.ClassName;
Example:
package myPackage; import java.util.Scanner;
2. Drivers Loading:
Loading the drivers is the first step once you import the packages. You need to load or register it before applying it to the program. Registration can be done in either of the two ways mentioned below:
a. Class.forName():
In this method, we first load the driver’s class file into the memory during the runtime. Creating objects using a new keyword is unnecessary. Here is an example that uses Class.forName() to load the Oracle driver:
Class.forName(“oracle.jdbc.driver.OracleDriver”);
And the second method is using the DriverManager.registerDriver() which is explained below.
3. Drivers Registration using the DriverManager
DriverManager.registerDriver():
DriverManager is a Java inbuilt class with a static member register. Here, the constructor of the driver class will be called during the compile time. The given example uses DriverManager.registerDriver() to register the Oracle Driver:
DriveManager.registerDriver(new oracle.jdbc.driver.OracleDriver())
4. Connection Establishment using the Connection class object
Now, we can make a connection with the help of the Connection class object.
Connection con = DriverManager.getConnection(url, user, password)
- user: This denotes the username from where the SQL command prompt can be accessed.
- password: This denotes the password from which the SQL prompt can be accessed.
- con: This acts as a reference to the Connection interface.
- url: We can create a Uniform Resource Locator in the given format:
String url = “jdbc:oracle:thin:@localhost:1521:xe”
5. Statement Creationg:
After establishing a connection, we can easily interact with the database. The interfaces like JDBCStatement, CallableStatement, and PreparedStatement define the methods which enable us to send the SQL commands and receive data from the database.
Example to create a JDBC statement:
Statement st = con.createStatement(); // con is the reference to COnenction interface from that is present in the previous step.
6. Query Execution:
Now, it is time to execute the SQL query that we have created. The query can be of any type
- It can be a query for updating/inserting a table in a database.
- It can be a query for retrieving data.
- To execute queries of retrieving values, from the database, we can use the executeQuery() method from the Statement
- interface. It returns the object of ResultSet that is used to obtain the other records present in the table.
- To execute the queries of updating or inserting, we can use the executeUpdate SQL query methods of the Statement interface.
Pseudo Code:
int m = st.executeUpdate(sql);
if(m==1)
System.out.println(“Inserted Successfully: ” +sql);
else
System.out.println(“Insertion Failed”);
7. Closing the Connections:
Once the data is sent to the required location, the connection must be closed. Once this takes place, all the objects of the Statement and ResultSet will be closed automatically. We can use the close() method of the Connection interface to achieve this. con.close();
Sample program to establish connection in JDBC:
import java.sql.*; // Importing database
import java.util.*;
// Importing required classes
class Main {
public static void main(String a[])
{
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "system";
String pass = "12345"; // Username and password to access DB
Scanner k = new Scanner(System.in);
System.out.println("Enter name: ");
String name = k.next();
System.out.println("Enter Regno: ");
int regno = k.nextInt();
System.out.println("Enter Class");
String cls = k.next();
String sql = "insert into student1 values('" + name + "'," + regno + ",'" + cls + "')";
// Insert query
Connection con = null;
try {
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver());
con = DriverManager.getConnection(url, user,pass);
Statement st = con.createStatement();
int m = st.executeUpdate(sql);
if (m == 1)
System.out.println("Inserted Successfully : " + sql);
else
System.out.println("Insertion Failed");
con.close(); // Close the connections
}
catch (Exception ex) {
System.err.println(ex);
}
}
}
Output:
Enter Name:
John
Enter Regno:
23
Enter Class:
5C
Inserted Successfully : insert into student1 values(‘John’, 23, ‘5C’)
Conclusion:
These are the steps to establish a JDBC connection in Java. You can try the sample program to have a clear understanding of its working. Also, refer to the “Database Operations in Java” article at FirstCode to obtain further information on JDBC connectivity.