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.