How to Create a Database Connection?

Java Database Connectivity is a standard API or we can say an application interface present between the Java programming language and the various databases like Oracle, SQL, PostgreSQL, MongoDB, etc. It basically connects the front end(for interacting with the users) with the backend for storing data entered by the users in the table details. JDBC or Java Database Connection creates a database by following the following steps:

• Import the database
• Load and register drivers
• Create a connection
• Create a statement
• Execute the query
• Process the results
• Close the connection

Step 1: Import the database

Java consists of many packages that ease the need to hardcode every logic. It has an inbuilt package of SQL that is needed for JDBC connection. 

Syntax:

import java.sql* ;

Step 2: Load and register drivers

This is done by JVM(Java Virtual Machine) that loads certain driver files into secondary memory that are essential to the working of JDBC.

Syntax:

forName(com.mysql.jdbc.xyz);

class.forname() method is the most common approach to register drivers. It dynamically loads the driver file into the memory. 

Example:

try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e) {
   System.out.println("cant load driver class!");
   System.Exit(1);
}

Here, the oracle database is used. Now let us considered a random example of hotel database management systems. Now applying SQL commands over it naming this database as ‘sample’. Now suppose the user starts inserting tables inside it be named as “sample1” and “sample2“.

// Connections class
 
// Importing all SQL classes
import java.sql.*;
 
public class connection{

// Object of Connection class
// initially assigned NULL
Connection con = null;

public static Connection connectDB()
{
    try
    {
        
        // Step 2: involve among 7 in Connection
        // class i.e Load and register drivers

        // 2(a) Loading drivers using forName() method
        // Here, the name of the database is mysql
        Class.forName("com.mysql.jdbc.Driver");

        // 2(b) Registering drivers using DriverManager
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/database",
            "root", "1234");
            
        // Root is the username, and
        // 1234 is the password

        // Here, the object of Connection class is return
        // which further used in main class 
        return con;
    }

    // Here, the exceptions is handle by Catch block
    catch (SQLException | ClassNotFoundException e)
    {
        
        // Print the exceptions
        System.out.println(e);

        return null;
    }
}
}

Step 3: Create a connection

Creating a connection is accomplished by the getconnection() method of DriverManager class, it contains the database URL, username, and password as a parameter. 

Syntax:

public static Connection getConnection(String URL, String Username, String password)  
throws SQLException  

Example:

String URL = "jdbc:oracle:thin:@amrood:1241:EMP";
String USERNAME = "geekygirl";
String PASSWORD = "geekss"
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

Here, 

The database URL looks like- jdbc:oracle:thin:@amrood:1221:EMP
The username be-"geekygirl"
The passwords be-"geekss"

Step 4: Create a statement

Query statement is created to interact with the database by following the proper syntax. Before writing the query, we must connect the database using connect() method. For example:

conn = connection.connectDB();
String sql = "select * from customer";

This statement basically shows the contents of the customers' table. We can also create a statement using the createStatement() method of the Connection interface. Here, the object of the statement is mainly responsible to execute queries.

Syntax:

public Statement createStatement()throws SQLException 

Example:

Statement s = conn.createStatement();

Step 5: Execute the query

For executing the query (written above), we need to convert the query in JDBC readable format, for that we use the preparedstatement() function and for executing the converted query, we use the executequery() function of the Statement interface. It returns the object of "rs" which is used to find all the table records.

Syntax:

public rs executeQuery(String sql)throws SQLException  

Example:

p = conn.prepareStatement(sql);
rs = p.executeQuery();

Step 6: Process the results

Now we check if rs.next() method is not null, then we display the details of that particular customer present in the “customer” table.next() function basically checks if there's any record that satisfies the query, if no record satisfies the condition, then it returns null. Below is the sample code: 

while (rs.next())
{  
    int id = rs.getInt("cusid");
    String name = rs.getString("cusname");
    String email = rs.getString("email");
    System.out.println(id + "\t\t" + name + 
                            "\t\t" + email);
}

Step 7: Close the connection

After all the operations are performed it's necessary to close the JDBC connection after the database session is no longer needed. If not explicitly done, then the java garbage collector does the job for us. However being a good programmer, let us learn how to close the connection of JDBC. So to close the JDBC connection close() method is used, this method close all the JDBC connection. 

Syntax:

public void close()throws SQLException  

Example:

conn.close();

Sample code is illustrated above:

// Importing SQL libraries to create database
import java.sql.*;
  
class GFG{
  
// Step 1: Main driver method
public static void main(String[] args)
{
    
    // Step 2: Creating connection using
    // Connection type and inbuilt function
    Connection con = null;
    PreparedStatement p = null;
    ResultSet rs = null;

    con = connection.connectDB();

    // Here, try block is used to catch exceptions
    try
    {
        
        // Here, the SQL command is used to store 
        // String datatype
        String sql = "select * from customer";
        p = con.prepareStatement(sql);
        rs = p.executeQuery();

        // Here, print the ID, name, email 
        // of the customers
        System.out.println("id\t\tname\t\temail");

        // Check condition
        while (rs.next()) 
        {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            System.out.println(id + "\t\t" + name + 
                                    "\t\t" + email);
        }
    }

    // Catch block is used for exception
    catch (SQLException e) 
    {
        
        // Print exception pop-up on the screen
        System.out.println(e);
    }
}
}

Output: