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 to provide users with the information they request for, collect information from users, delete the information as required by the user, and update data to the database on a daily basis. We are going to look into one such approach by using Java as our programming language, JDBC as a 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 them 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 the oracle 11g express edition from the below link.

Click Here

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

Source: From my desktop

• Please double click the installer; the 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 on 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 the same for the Oracle DB, i. e. system. The password will be the one that 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 a 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 the 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 the JDBC operation.

1. Store the data from an entity to a database, i.e. they create an 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 that 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 the finally block.

Illustration

As we are using the Oracle database in our illustration, we need to have the required driver that Oracle should provide. The driver class is usually in the form of a jar file with the 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 class path of the project. Please check the below image.

Source: From my desktop

Let us create an artist table with the 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 the 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 a DB URL that should have your computer name and DB port number ( ideally 1521 ),  preceded by JDBC:oracle:thin:@. We have then created a username and password 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 the name Artist3 and updated the database or pushed this to the database using the execute the statement.
5. If we would like to assign values to the attributes, we will use the object that we have created for the artist class and note that it is not this class that will insert the value to Database; 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 the ” 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 a DB URL that should have your computer name and DB port number ( ideally 1521 ), preceded by JDBC:oracle:thin:@. We have then created a username and password 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 and interfaces and classes used 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