Java Database Connectivity, commonly referred to as JDBC, is a fundamental technology for Java developers when it comes to interacting with relational databases. It provides a standardized way to connect, query, and manipulate databases, making it an essential part of many Java applications, from web applications to enterprise software. In this article, we will explore JDBC, its architecture, and how to use it effectively in your Java projects.
Understanding JDBC
JDBC is a Java-based API (Application Programming Interface) that allows Java applications to interact with relational databases. It provides a set of classes and methods for database operations, including connecting to databases, executing SQL queries, and processing result sets. With JDBC, you can work with various relational database management systems (RDBMS), such as MySQL, Oracle, PostgreSQL, and SQL Server, without having to learn the intricacies of each database system.
Key Components of JDBC
- JDBC Drivers:
→ JDBC drivers are platform-specific implementations that enable Java applications to communicate with a particular database system. There are four types of JDBC drivers:
- Type-1: JDBC-ODBC bridge driver (legacy).
- Type-2: Native-API driver (partially Java-based).
- Type-3: Network Protocol driver (middleware-based).
- Type-4: Thin driver (fully Java-based, recommended for most scenarios).
2. JDBC API:
- The JDBC API consists of classes and interfaces that provide the core functionality for database connectivity. Key classes include
Connection,Statement,PreparedStatement,CallableStatement, andResultSet.
3. JDBC URL:
- A JDBC URL (Uniform Resource Locator) is a string that specifies how to connect to a database. It includes information like the database's location, name, and authentication details.
4. DriverManager:
- The
DriverManagerclass is responsible for managing a list of database drivers. It helps in establishing a connection to the database using the appropriate driver.
Whenever we want to connect our java application with database we have to use 7 steps:
- import the required package :
import java.util.*;2. Load and register the JDBC drivers :
Driver for mysql is com.mysql.jdbc.driver load this and register. To register we need to use the forName() method
Syntax of forName() method
public static void forName(String className)throws ClassNotFoundException
Class.forName("com.mysql.jdbc.Driver"); // This class.forName is responsible to load the driver
// Here you need to throw the ClassNotFoundException3. Establish the connection:
In order to establish the connection we need to instantiate the interface called as connection. The getConnection() method of DriverManager class is used to establish connection with the database.
Syntax of getConnection() method
public static Connection getConnection(String url,
String user, String password) throws SQLException
Connection is an interface and we cannot create the object of interface directly so that we need class which implements the connection or method which will give you the instance of the connection. getConnection() is the method in java which gives you the instance of the connection. Method of the class called DriverManager. Also, get connection method is the static method which returns the instance of connection.
Example
Connection con=DriverManager.getConnection(url,userName,password);4. Create Statement:
We have Three types of statements:
a) Statement : We can execute the query with the help of statement object
b) PreparedStatement : When we have a query which is inbuilt or predefined queries but have different values then at that time we can use PreparedStatement
c) CallableStatement: When we want to execute the Procedure Language in sql then we can use CallableStatement.
The createStatement() method of Connection interface is used to create statement
Syntax of createStatement() method
public Statement createStatement()throws SQLException
Example
Statement st=con.createStatement(); 5. Execute the query:
The executeQuery() method of Statement interface is used to execute queries to the database.
Syntax of executeQuery() method
public ResultSet executeQuery(String sql)throws SQLException
example
ResultSet rs=st.executeQuery(query);6. Process the results
You retrieve and process the result using rs.getString("name");
String name=rs.getString("name");
System.out.println(name);7. Close the connections:
Here, simply get the object of Statement and close it and get the object of connection and close it.
st.close();
con.close();Lets us discuss the above steps using the real world example:
Suppose, you want to call your friend to ask whether the results are out or not.
- Before calling you need a phone which signifies our step 1.
- You need a network and a SIM card to call which is equivalent to the loading and registering the JDBC drivers (step 2).
- You have to dial numer and call your friend to establish the connection (step 3)
- You have to think what you have to say (step 4)
- You have to speak the statement (step 5)
- You have to get response whether it is out or not (step 6)
- Once everything done (step 7)
package jdbc;
import java.sql.*; //1st step
public class JdbcMainFile1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url="jdbc:mysql://localhost:3306/student?useSSL=false";
String userName="root";
String password="BiZEn/?**f6f8j00";
String query="select name from nepal where id=101";
//we are fetching the name of the user with id 101
//2.step 2
Class.forName("com.mysql.jdbc.Driver");
//step 3. Establish the connection object
Connection con=DriverManager.getConnection(url,userName,password);
//Step 4 & 5: now create the statement and Execute the SQL queries
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(query); //execute the query
//Stored in the ResultSet
rs.next(); // take pointer to the next record. Responsible to take
// to the next step
//Step 6
//Process the Results
// You retrieve and process the
//result using rs.getString("name");
String name=rs.getString("name"); //we want data with column name 'name'
System.out.println(name);
//Step 7
st.close();
con.close();
//execute the query
}
}In MySQL Workbech :
create table nepal(
id int auto_increment,
name varchar(45),
salary double ,
primary key (id)
);
insert into nepal (id,name,salary) values ('101', 'bijen', '100000');
select *from nepal;
In Console
Here, we are fetching only one value.
Now, to get the first row and with name, id and salary :-
String name=rs.getInt(1)+" : "+rs.getString(2)+" : "+rs.getInt(3); //we want data with column name 'name'
package jdbc;
import java.sql.*; //1st step
public class JdbcMainFile2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url="jdbc:mysql://localhost:3306/student?useSSL=false";
String userName="root";
String password="BiZEn/?**f6f8j00";
String query="select * from nepal";
//2.step 2
Class.forName("com.mysql.jdbc.Driver");
//step 3. Establish the connection object
Connection con=DriverManager.getConnection(url,userName,password);
//Step 4 & 5: now create the statement and Execute the SQL queries
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(query);
//inside rs we have 3 columns and 4 rows
rs.next();
//Step 6
//Process the Results
// You retrieve and process the
//result using rs.getString("name");
String name=rs.getInt(1)+" : "+rs.getString(2)+" : "+rs.getInt(3); //we want data with column name 'name'
System.out.println(name);
//Step 7
st.close();
con.close();
//execute the query
}
}
Here, We will be able to fetch the id , name and salary and of first row only.
To get other values we just need to use while loop.
String name="";
while (rs.next()) {
name=rs.getInt(1)+" : "+rs.getString(2)+" : "+rs.getInt(3); //we want data with column name 'name'
System.out.println(name);
}For example
package jdbc;
import java.sql.*; //1st step
public class JdbcMainFile2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url="jdbc:mysql://localhost:3306/student?useSSL=false";
String userName="root";
String password="BiZEn/?**f6f8j00";
String query="select * from nepal";
//2.step 2
Class.forName("com.mysql.jdbc.Driver");
//step 3. Establish the connection object
Connection con=DriverManager.getConnection(url,userName,password);
//Step 4 & 5: now create the statement and Execute the SQL queries
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(query);
//inside rs we have 3 columns and 4 rows
String name="";
while (rs.next()) {
name=rs.getInt(1)+" : "+rs.getString(2)+" : "+rs.getInt(3); //we want data with column name 'name'
System.out.println(name);
}
//Step 7
st.close();
con.close();
//execute the query
}
}
This is how we can fetch data from the data base.
Now, we can insert values with the help of java instead of MySQL.
we can insert data with the help of insert query
String query="insert into nepal values (105,'Nikil',78777)";Example
package jdbc;
import java.sql.*; //1st step
public class JdbcMainFile3 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url="jdbc:mysql://localhost:3306/student?useSSL=false";
String userName="root";
String password="BiZEn/?**f6f8j00";
String query="insert into nepal values (105,'Nikil',78777)";
//2.step 2
Class.forName("com.mysql.jdbc.Driver");
//step 3. Establish the connection object
Connection con=DriverManager.getConnection(url,userName,password);
Statement st=con.createStatement();
int count =st.executeUpdate(query);
System.out.println(count+ "row/s affected");
st.close();
con.close();
}
}Here, st.executeQuery(query); is changed to st.executeUpdate(query); for inserting we have to use executeUpdate(query)
Now, When you want to insert values when values are coming dynamically, we can use PreparedStatement instead of statement. (When queries are fixed and values are keeps changing).
package jdbc;
import java.sql.*;
import java.util.Scanner;
public class JdbcMainFile4 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url = "jdbc:mysql://localhost:3306/student?useSSL=false";
String userName = "root";
String password="BiZEn/?**f6f8j00";
String query = "INSERT INTO nepal VALUES (?, ?, ?)";
Scanner sc = new Scanner(System.in);
System.out.println("Enter Employee id");
int uid = sc.nextInt();
// Consume the newline character
sc.nextLine();
System.out.println("Enter the employee_name");
String uname = sc.nextLine();
System.out.println("Enter the Salary");
double usalary = sc.nextDouble();
// Step 2: Load the JDBC Driver
Class.forName("com.mysql.jdbc.Driver");
// Step 3: Establish the connection
Connection con = DriverManager.getConnection(url, userName, password);
con.setAutoCommit(false); // Disable Autocommit
PreparedStatement pst = con.prepareStatement(query);
pst.setInt(1, uid);
pst.setString(2, uname);
pst.setDouble(3, usalary);
// Step 4: Execute the insert query
int k = pst.executeUpdate();
if (k != 0) {
System.out.println("Data is added successfully");
con.commit(); // Commit the transaction
} else {
System.out.println("Something is wrong");
}
// Re-enable autocommit
con.setAutoCommit(true);
pst.close();
con.close();
}
}In console
In MySQL
That's how we can use the insertion.