Home » Enterprise Java » spring » JDBC » Establishing JDBC Connection in Java

About Shaik Ashish

He completed his Bachelors Degree in Computer Applications. He is a freelancer, writer, Microsoft Certified in Python and Java. He enjoys Writing, Teaching, Coding in Java and Python, Learning New Technologies and Music.

Establishing JDBC Connection in Java

In this post, we will learn how to establish a JDBC connection, from a Java program to an oracle database. Once we establish a connection, we will insert records into it.

You can also check our Java Programming Database Tutorial in the following video:

1. Introduction

JDBC is an acronym for Java Database Connectivity. It’s an advancement for ODBC ( Open Database Connectivity ). JDBC is an standard API specification developed in order to move data from front-end to back-end.

This API consists of classes and interfaces written in Java. It basically acts as an interface (not the one we use in Java) or channel between your Java program and databases i.e it establishes a link between the two so that a programmer could send data from Java code and store it in the database and retrieve it for future use.

As previously told JDBC is an advancement for ODBC, ODBC being platform-dependent had a lot of drawbacks. ODBC API was written in C, C++, Python, Core Java and as we know above languages (except Java and some part of Python )are platform dependent. Therefore to remove dependence, JDBC was developed by database vendor which consisted of classes and interfaces written in Java.

2. Steps to Establish JDBC Connection

Establishing a JDBC Connection and executing SQL Queries from java program involves five steps :

2.1 Load the Driver

To begin with, you first need to load the driver or register it before using it in the program. Registration is to be done once in your program. You can register a driver in one of the two ways mentioned below :

JDBC Connection
  • Class.forName() : Here we load the driver’s class file into memory at the runtime. No need of using new or creation of object .The following example uses Class.forName() to load the Oracle driver – Class.forName(“oracle.jdbc.driver.OracleDriver”);
  •  DriverManager.registerDriver(): DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver class at compile time . The following example uses DriverManager.registerDriver()to register the Oracle driver – DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())

2.2 Creating a Connection

After loading the driver, establish connections using :

Connection con = DriverManager.getConnection(url,user,password) 

user – username from which your sql command prompt can be accessed.
password – password from which your sql command prompt can be accessed.

con: is a reference to Connection interface.
url : Uniform Resource Locator. It can be created as follows:

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

Where oracle is the database used, thin is the driver used , @localhost is the IP Address where database is stored, 1521 is the port number and xe is the service provider. All 3 parameters above are of String type and are to be declared by programmer before calling the function. Use of this can be referred from final code.

2.3 Create a statement

Once a connection is established you can interact with the database. The Statement, CallableStatement, and PreparedStatement interfaces define the methods that enable you to send SQL commands and receive data from your database. Use of Statement is as follows:

Statement st = con.createStatement();

Here, con is a reference to Connection interface used in previous step .

2.4 Execute the SQL Queries

Now comes the most important part i.e executing the query. Query here is an SQL Query . Now we know we can have multiple types of queries. Some of them are as follows:

  • Query for updating/inserting tables in a database.
  • Query for retrieving data.

The executeQuery(SQL query) method of Statement interface is used to execute queries of retrieving values from the database. This method returns the object of ResultSet that can be used to get all the records of a table.
The executeUpdate(SQL query) method of statement interface is used to execute queries of updating/inserting.

Example:

For Select SQL Query:

ResultSet rs= st.executeQuery("select * from studentinfo");

For Non-Select SQL Query:

int rowCount= st.executeUpdate("delete from studentinfo where sid=1111");

2.5 Closing the Connection

By closing connection, objects of Statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

con. close()

3. Implementation

Now that we understood all the steps involved, let us implement them programmatically. The below code inserts data into oracle database.

Note: Make sure oracle ojdbc6.jar file is in classpath.DatabaseExample.java

import java.sql.*; 
import java.util.*; 
class Test 
{ 
    public static void main(String a[]) throws SQLException
    { 
        //Creating the connection 
        String url = "jdbc:oracle:thin:@localhost:1521:xe"; 
        String user = "system"; 
        String pass = "12345"; 
  
        //Entering the data 
        Scanner k = new Scanner(System.in); 
        System.out.println("enter name"); 
        String name = k.next(); 
        System.out.println("enter roll no"); 
        int roll = k.nextInt(); 
        System.out.println("enter class"); 
        String cls =  k.next(); 
  
        //Inserting data using SQL query 
        String sql = "insert into student1 values('"+name+"',"+roll+",'"+cls+"')"; 
        Connection con=null; 
        try
        { 
            //loading the driver
           Class.forName("oracle.jdbc.OracleDriver");
  
            //Reference to connection interface 
            con = DriverManager.getConnection(url,user,pass); 
  
            Statement st = con.createStatement(); 
            int m = st.executeUpdate(sql); 
            if (m == 1) 
                System.out.println("Data inserted successfully"); 
            else
                System.out.println("insertion failed"); 
        } 
        catch(Exception ex) 
        { 
            System.out.println("insertion failed"); 
            System.out.println(ex); 
        }
        finally
        {
            con.close();  //closing the connection
        }
    } 
} 

Once we input the data, the data is stored in the database and the output is as follows:Output

Data inserted successfully

4. Download the Source Code

This is an example of Establishing JDBC connection in Java.

Download
You can download the full source code of this example here: Establishing JDBC Connection in Java

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments