How to Connect Database in Java?

How to Connect Database in Java?

While writing an IT application using any programming language, the flow of Data from and to the application serves the core functionality. If the data flow is somehow affected, it can adversely affect the application functionality and may cause a big loss to the business.

There are different methods available today for connecting your program to a database in order to provide users with the information they request for, collect information from users, delete the information as required by the user and also to update data to the database on daily basis.

We are going to look into one such approach by using Java as our programming language, JDBC as database connectivity method and following the object-oriented approach.

What is JDBC?

JDBC stands for Java Database Connectivity and it helps a Java program to perform different kinds of operations over the database such as create, read, update and delete. Also, JDBC is a Java API.

By using JDBC, a programmer should be able to:

• Establish a connection with Database
• Run SQL commands by sending it to Database
• Interprets the results that come from Database

Creating a Database to Connect Database in Java

Before working with JDBC, it is required to have a database in order to connect to it. We will be making use of the Oracle Database for the sake of our illustration. Please download oracle 11g express edition from below link.

https://www.oracle.com/database/technologies/appdev/xe.html

I already have Oracle 10g installer ready with me as you can see below:

Source: From my desktop

• Please double click the installer, below screen will pop up. Click next.

Source: From my desktop

• Please accept the license agreement and click on next.

Source: From my desktop

• Please keep the default settings as they and click on next.

Source: From my desktop

• It will ask for a password that will be required when you log in to Oracle 11g instance. Please provide the password and click on next.

Source: From my desktop

• After you provide the password, the installation process will begin. Once the installation is finished, a shortcut icon shall get created in your desktop screen. If you are not able to find it, please go to windows start and search for Oracle, you should get it. Click on it and it will open a thin client for you via a browser. Log in to the instance. Please note down that the username will be same for Oracle DB i.e. system. The password will be the one which you had provided while configuring the database.

Source: From my desktop

• Now once you log in, the very first screen that you will see will look like below:

Source: From my desktop

• Now let’s create a table. Please click on the SQL module from the above screen and run the below query.

Source: From my desktop

• As you can see, we have successfully created a table with the name Educba.

Connect Database in Java

There are few interfaces and classes that are used in order to connect to a database and perform operations using JDBC API.

We will explain one by one, but let me first present to you the below program:

Source: From my desktop

Below are the interfaces which we will be using to connect to Database and perform operations over it:

1. Driver Manager
2. Connection
3. Statement
4. Result set
5. Prepared statement

Let us look at the operations that we can perform as a part of JDBC operation.

1. Store the data from an entity to a database i.e. the create operation
2. Retrieve the data to the entity or a bean from a database
3. Update the data for the entity or the bean in the database
4. Delete or remove the data on the entity or a bean from the database.

No matter what operation do we perform, there are some basic steps which would remain the same:

1. Load the driver.

Class.forName("oracle.jdbc.driver.OracleDriver");

2. Create a URL string

String url="jdbc:oracle:thin:@172:.26.132.40:1521:orclilp";

3. Use the driver manager to create a connection

con=DriverManager.getConnection(URL, USERNAME, PASSWORD);

4. Use Connection reference to create Statement

stmt = conn.createStatement();

5. Use a statement to execute the query

6. Process the result set (optional, depends)

7. Release the resources in finally block.

 Illustration

As we are using Oracle database in our illustration, we need to have the required driver that should be provided by Oracle. The driver class is usually in the form of jar file with name ojdbc14.jar. This driver should be imported to your java program as a part of “Referenced Libraries” if it is not there.

If you have installed Oracle 11g in your C folder, it can be ideally found in the below path: (If it is not there, it can be easily downloaded from here)

C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib

Source: From my desktop

A lot of jar files should be available however ojdbc14.jar is the latest one. The same can be used for our purpose. This jar should be added to the classpath of the project. Please check the below image.

Source: From my desktop

Let us create an artist table with following attributes: (You can directly run the SQL command line which comes as a separate module of Oracle Express edition. But first you need to connect and you can execute the “connect” command in order to connect to the database.

Source: From my desktop

As a part of this illustration, we would be creating three java classes i.e. Artist.java, CreateTable.java and ArtistManagementDao.java to achieve this.

Also in the java layer, we need to create a class called Artist bean. This class should have attributes of the Artist with the above context. It will look like below:

Artist.java

Let us create 2 other java classes with name ArtistManagementDao and CreateTable.java

ArtistManagementDao.java

package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class ArtistManagementDao {
private final static String DRIVERNAME="oracle.jdbc.driver.OracleDriver";
private final static String URL="jdbc:oracle:thin:@LENOVO-PC:1521:XE";
private final static String USERNAME="System";
private final static String PASSWORD="Your DB password";
private Connection con =null;
public void addArtist(Artist a)
{
try {
Class.forName(DRIVERNAME);
String sql="insert into Artist1 values (?,?)";
con=DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement pst=con.prepareStatement(sql);
pst.setString(1, a.getArtistID());
pst.setString(2, a.getArtistName());
pst.executeUpdate();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println(e);
} catch (SQLException e) {
System.out.println(e);
}
}
}

CreateTable.java

package com;
import java.sql.*;
public class CreateTable{
static final String DB_URL = "jdbc:oracle:thin:@LENOVO-PC:1521:XE";
static final String USER = "System";
static final String PASS = "Your DB Password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
ArtistManagementDao am=new ArtistManagementDao();
Artist a=new Artist();
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected to database successfully");
System.out.println("Creating table");
stmt = conn.createStatement();
//ARTIST TABLE
String sql = "CREATE TABLE Artist3 " +
"(ArtistID varchar2(5) primary key not NULL, " +
" ArtistName varchar2(25))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
a.setArtistId("abc");
a.setArtistName("ankit");
am.addArtist(a);
System.out.println("\nArtistID="+a.getArtistID()+"\nArtistName="+a.getArtistName());
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
}
}

CreateTable.java Explanation:

1. We have first created DB URL which should have your computer name, as well as DB port number (ideally 1521), preceded by JDBC:oracle: thin:@. Then we have created username and password in order to pass these attributes during the time of Database connection. It is highly recommended to encrypt these values in the production environment.

2. In the CreateTable.java class, we have created an object “am” and “a” of ArtistManagementDao.java and Artist.java class respectively.

3. Then we tried to establish our connection by following the steps that we have learned earlier using a conn statement.

4. Then we have created a table with name Artist3 and updated the database or pushed this to the database using the execute the statement.

5. Now if we would like to assign values to the attributes, we will make use of the object that we have created for artist class and make a note, it is not this class which will insert the value to Database, rather it will be ArtistmanagementDao class. We already have stored values in attributes as of now and we will see in day class how to push this to the database.

6. Finally, we have used “am” object from ArtistManagementDao to pass this object “a” to method addArtist() of ArtistManagementDao java class.

7. We have also used to try and catch block for catching any exceptions and throwing out the same.

ArtistManagementDao Explanation:

1. The Dao class helps us to isolate the application/business layer from the persistence layer. This is usually the relational database.

2. We have first created DB URL which should have your computer name, as well as DB port number (ideally 1521), preceded by JDBC:oracle: thin:@. Then we have created username and password in order to pass these attributes during the time of Database connection. It is highly recommended to encrypt these values in the production environment.

3. Inside the addArtist method, we tried to establish our connection by following the steps that we have learned earlier using a conn statement.

4. Now, we have used the insert method to push values from attributes to the database.

5. In the next statement, in order to test, we have the get statement to retrieve values from the database.

6. We have also used to try and catch block for catching any exceptions and throwing out the same.

Recommended Articles

This has been a guide on how to connect database in java. Here we have discuss how to connect the database in java along with interfaces and classes used in order to connect a database. You may also look at the following articles to learn more –

1. What is SQL Server?
2. Is Big Data a Database?
3. How JavaScript Works
4. Multithreading Interview Questions in Java