Java Database Connectivity

JDBC means access to the Java Database. It's a step forward for ODBC (Open Database Connectivity). JDBC is a standard API specification for moving data from the frontend to the backend. This API consists of classes and interfaces written in Java.

This simply serves as an interface between your Java system and databases (not the one we use in Java) or network, i.e. this provides a connection between the two so that a developer can send Java code information and store it in the database for future use.

The Java JDBC API allows Java applications to connect to relational databases such as MySQL, PostgreSQL, MS SQL Server, Oracle, H2 Database, etc. The JDBC API allows querying and updating relational databases, as well as calling stored procedures, and obtaining the database meta data.The Java JDBC API is part of the Java SE SDK core, making JDBC usable to all Java applications wishing to use it. Here is a diagram of a Java program connecting to a relational database using JDBC:

JDBC is independent from SQL

JDBC is not standardizing the SQL sent to the server. You, the JDBC API client, are writing the SQL. The SQL dialect used by the various databases varies slightly, so to be 100% independent of the database, you also need to be 100% independent of the database (i.e. use commands that are understood by all databases).

JDBC is not for databases that are not relational

The Java JDBC API is built to communicate with relational databases, meaning that you use standard SQL to connect with databases. The JDBC API is not intended for non-related servers like Mongo DB, Cassandra, Dynamo and so on. From a Java application you can use such databases, but you should see what drivers such databases provide for Java itself.

JDBC is independent of the type of database

The Java JDBC API standardizes how to connect to a database, how to execute queries against it, how to access a request output, how to execute database changes, how to call stored procedures, and how to get meta data from the server. Through "standardizing," I mean the repository's software looks the same across various products.Therefore, if your project needs this in the future, it will be much easier to switch to another database.

Steps for Java program and database connectivity mentioned below

Step 1. Load JDBC driver

First of all, you need to load or register the driver before you use it in the program. You must register once in your program. In one of the two ways listed below, you can register a driver:

Step 2. Class.forName()

Here at runtime we load the class file of the driver into memory. No need to use fresh or create object. The instance below uses Class.forName() to load the Oracle driver.–

Class.forName("org.h2.Driver");

Step 3. DriverManager.registerDriver():

DriverManager is an integrated Java class with a register of static members. Here we call the driver class constructor at the moment of compilation. DriverManager.registerDriver() is used in the following example/instance to register the Oracle driver -

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()) 

Step 4. Connections establishment

We used code below after loading the driver to create connections

Connection connection = DriverManager.getConnection(DBurl,username,password)

These are the following components used in creating connections

1. username : it is a username from which your sql command prompt can be accessed.
2. password : It is a password from which your sql command prompt can be accessed.
3. connection : It is an element of connection, i.e. it is a communication interface reference.
4. DBurl : It is a Uniform Resource Locator. It can be created as follows:

String url = “ jdbc:oracle:thin:@localhost:1521:xe” 

Where Oracle is the database used, the driver used is tiny, where the database is located, @localhost is the IP address, 1521 is the port number, and xe is the service provider. The 3 parameters above are String sort, which the programmer must declare before calling the function. You may refer to the use of this from the final code.

Step 5. Statement creation

Whether the server needs to be modified or queried, you will need to build a JDBC Statement or JDBC PreparedStatement through which the update or request will occur.

Statement statement = connection.createStatement(); 

Step 6. Query execution

Here comes the most important part, i.e. the query execution. Here's a query from the SQL. We here understand that we can have various kinds of questions. Some of them are the following:

1. Request to delete / modify / insert a table in a database.
2. Request to collect or retrieve information from the database.

The Statement interface's executeQuery) (method is used to perform queries to extract server values. This method returns the ResultSet item which can be used to obtain from the table all data / records.

The Statement interface executeUpdate(sql query) method is used to perform update / insert.

Example:

int result = stmt.executeUpdate(sql); 
if (result == 1) 
System.out.println("inserted successfully : "+sql); 
else 
System.out.println("insertion failed"); 

Here sql is sql query of the type String

Step 7. Close database connection

You have to open the connection again when you're finished with the JDBC server connection. In the request, but also within the database server, a JDBC connection could take up a large amount of sources. Therefore, after use it is important to close the connection to the database again. You close a JDBC relation through the method of closing). Here is an example of closing a JDBC connection:

connection.close(); 

Working example below :

importjava.sql.*;  
importjava.util.*;  

Java JDBC program below:

class MyFirstJDBCProgram 
{  
    public static void main(String a[])  
    {  
        String url = "jdbc:oracle:thin:@localhost:1521:xe";  
        String username = "India";  
        String password = "India";  
        Scanner k = new Scanner(System.in);  
System.out.println("enter class student name");  
        String Studentname = k.next();  
System.out.println("enter class student roll no");  
        int studentRollNumber = k.nextInt();  
System.out.println("enter student current class");  
        String Studentcls =  k.next();  
        String sql = "insert into student1 values('"+Studentname+"',"+studentRollNumber+",'"+Studentcls+"')";  
        Connection conn=null;  
        try 
        {  
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  
            con = DriverManager.getConnection(url,user,pass);  
            Statement stmt = conn.createStatement();  
            int count = stmt.executeUpdate(sql);  
            if (count == 1)  
System.out.println("inserted student record successfully into database : "+sql);  
            else 
System.out.println("insertion failed."); 
        }  
        catch(Exception ex)  
        {  
System.err.println(ex);  
        } finally { 
conn.close(); 
       }  
    }  
}