Advance Java

Learn New things from Rixosys

How To Connect To A Database in Java? 

How To Connect To A Database in Java? 

Java, being one in all the foremost prominent programming languages, provides extensive support to databases. It helps us to connect to various databases through JDBC (Java database Connectivity). during this article, i'll tell you the way to connect to a database and execute queries using JDBC.

Below topics are covered in this article:

  • Introduction to JDBC
  • Common JDBC components
  • Steps to create JDBC Application
  • JDBC Connections

Introduction to JDBC

JDBC is one of the standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. This API lets you encode the access request statements, in Structured query language (SQL).  This mainly involves opening a connection, creating a SQL database, executing SQL queries and then arriving at the output.

JDBC API can be used to access tabular data stored in any relational database. With this, you can update, save, fetch and delete the data from the databases. it is similar to the Open info connectivity (ODBC) provided by Microsoft.

For a better understanding of working of JDBC, let’s dive deeper into the topic and understand the architecture that lies behind Java database connectivity.

Common JDBC Components

The JDBC API provides the following interfaces and classes −

  • DriverManager: This is mainly used to manage a list of database drivers. The driver that recognizes a certain sub-protocol will be used to establish a database Connection.
  • The Driver is an interface that handles the communications with the database server. It also abstracts the details that are associated while working with Driver objects.
  • A Connection is an interface that consists of all the methods required to connect to a database. The connection object deals with the communication functions of the database  context.

Steps to create JDBC Application

In order to create a JDBC Application, you need to follow a few steps. Let’s see what are they.

Description: Steps to create JDBC Application - Advanced Java tutorial - Edureka

  1. Import the packages: You need to include all the packages that contain the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
  2. Register the JDBC driver: Here you have to initialize a driver so that you can open a communication channel with the database.
  3. Open a connection: Here, you can use the getConnection() method to create a Connection object, which represents a physical connection with the database.
  4. Execute a query: This actually requires to use an object of type Statement for building and submitting an SQL statement to the database.
  5. Extract data from the result set: It is suggested that you use the appropriate getXXX() method to retrieve the data from the result set.
  6. Clean up the environment: Here, it is essential to explicitly close all database resources versus relying on the JVM’s garbage collection.

Now as you have seen various steps involved to create a JDBC Application, let’s see an example code to create a database and establish a connection.

package Rixosys;
import java.sql.*;
import java.sql.DriverManager;
public class Example {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,"root","");
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
//STEP 6: Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close()
}catch(SQLException se2){
}// nothing can be done
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
} // end Example

Above code creates a table in your localhost database. To insert the values in the created database, you can refer to the below code.

package Rixosys;
import java.sql.*;
import java.sql.DriverManager;
public class Example {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,"root","");
//STEP 4: Execute a query
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
String sql = "CREATE TABLE EMPLOYEES " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
System.out.println("Inserting records into the table...");
stmt =conn.createStatement();
String sql ="INSERT INTO Employees VALUES (100, 'Kriss', 'Kurian', 18)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Employees VALUES (101, 'Enrique', 'John', 25)";
stmt.executeUpdate(sql);
sql= "INSERT INTO Employees (102, 'Taylor', 'Swift', 30)";
stmt.executeUpdate(sql);
sql= "INSERT INTO Employees VALUES(103, 'Linkin', 'Park', 28)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
//STEP 6: Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close()
}catch(SQLException se2){
}// nothing can be done
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
} // end Example

So this is how you can establish a connection to the database and insert values in the tables. Now let’s move further and understand various JDBC Driver Types

JDBC Driver Types

JDBC drivers are used to implement the defined interfaces in the JDBC API, for interacting with the database server. Essentially, a JDBC driver does three things and they are as follows:
1. Establishes a connection with a data source.
2. It will send queries and update statements to the data source.
3. Finally, it processes the results.

For example, JDBC drivers help you to open a database connection to interact with it by sending SQL or database commands. If you wish to know more about types of JDBC Driver, you can refer to this article on JDBC Drivers.

Now let’s move further and understand JDBC Connections.

JDBC Connections

  •   Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code.
  •   Register JDBC Driver: In this step, JVM to loads the desired driver implementation into memory so that it can fulfill the JDBC requests. There are 2 approaches to register a driver.

o   The most suitable approach to register a driver is to use Java’s forName() method to dynamically load the driver’s class file into memory, which automatically registers it. This method is suitable as it allows you to make the driver registration configurable and portable. Take a look at the below code:

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) 
System.out.println("Error: unable to load driver class!");
System.exit(1);
}

o    The second approach you can use to register a driver is to use the static registerDriver()method.

try {
Driver myDriver = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex)
{
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
  • You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft. Here each form requires a database URL.
  • Database URL Formulation: URL Formulation is necessary to create a properly formatted address that points to the database to which you want to connect. Once you loaded the driver, you can establish a connection using the DriverManager.getConnection() method. DriverManager.getConnection() methods are−
    • getConnection(String url)
    • getConnection(String url, Properties prop)
    • getConnection(String url, String user, String password)
  • Create a connection object

You can create a connection using the database URL, username, and password and also using properties object.

  • Close

Finally, to end the database session, you need to close all the database connections. However, if you forget, Java’s garbage collector will close the connection when it cleans up stale objects.

Example:- conn.close(); // Used to close the connection