Java Database Connectivity with MySQL

In this tutorial, we will learn how to connect Database with MySQL in Java.

5 Steps to Connect to the Database in Java

• Load the driver (or) Register the driver class
• Establish a connection (or) Create a connection
• Creating statement
• Executing queries & obtain the result
• Closing the connection

Register the Driver Class:

Software that contains classes and interfaces created using the JDBC API is referred to as a database driver.

Given the variety of drivers on the market, we need first to specify the driver that will be used in a Java programme to communicate with the database server.

The driver class is registered using the forName() function of the Class. The driver class is dynamically loaded using this technique.            

Syntax:

public static void forName(String className)throws ClassNotFoundException

Example:

Class.forName("com.mysql.jdbc.Driver");

In order to connect to the database, a programmer must identify the database driver he will be using. There are four ways to register a driver.

• The driver can be registered by creating an object that belongs to the driver class of the driver programme. for instance

com.mysql.jdbc.Driver obj = new com.mysql.jdbc.Driver ();

• The driver class object should be sent to the second method of registering a driver. The DriverManager class's registerDriver() function. As an illustration,

DriverManager.registerDriver ( new com.mysql.jdbc.Driver () );

• The third way to register the driver is to send the driver class name directly to forName() method,as:

Class.forName(“com.mysql.jdbc.Driver”);

Create the Connection Object

To connect to the database, utilise the getConnection() function of the DriverManager class.

Syntax:

1) public static Connection getConnection(String URL)throws SQLException




2) public static Connection getConnection(String URL, String name, String password)
throws SQLException

Connection:

c=DriverManager.getConnection("jdbc:mysql:thin:@localhost:1521:xe","system","password"); 

Three items are necessary to connect to a database:

• URL: The database's URL is a protocol to connect to the database is represented by the URL (Uniform Resource Locator). In a nutshell, it finds the database on the network.
• Username: Each user will be granted a username to connect to a database, which is typically assigned by the database administrator.
• Password: This is the password that the database administrator assigned to the user to log in to the database.

Create the Statement Object:

Statements are created using the Connection interface's createStatement() function. Executing database queries is the responsibility of the object of the statement.

Syntax:

public Statement createStatement() throws SQLException

Once created, the connection is used to transmit SQL commands to the database.

For transmitting SQL statements to databases, the java.sql package provides three interfaces:

Specifically, the Statement interface and its two sub-interfaces, PreparedStatement and CallableStatement.

The objects of these three sentences are returned by three methods of the Connection object.

When sending a SQL statement to the database without any arguments, a Statement object is utilised. By utilising the Connection Object's createStatement(String query) method, its instances are returned.

Statement object Example:

Statement stmt=con.createStatement();

PreparedStatement object:

Precompiled statements can be sent to databases with or without IN arguments using a PreparedStatement object. The insert SQL command is typically used to add rows of data to databases.

An insert SQL statement is created and submitted to the database for each insertion. The identical expression is constructed n times if there are n rows that need to be added. It takes up a lot of time. Because of this, we employ PreparedStatement which has already been precompiled. Only the values that need to be added in this situation are sent repeatedly to the database.

Syntax:

PreparedStatement ps=con.prepareStatement(String query);

CallableStatement Object:

A CallableStatement object is used to invoke functions and stored procedures running on a database server and deliver the results to the client.

A stored procedure is a collection of statements that are saved, executed, and provided to the client by a database server.

Syntax:

CallableStatement cs=con.prepareCall(String query);

Execute the Query

The Statement interface's executeQuery() function is used to run database queries.

The object of ResultSet that can be used to get all of a table's records is returned by this method. The results (rows) of running a SQL command on a database are contained in an object called a "ResultSet."

Syntax:

public ResultSet executeQuery(String sql)throws SQLException

Example:

ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
{
    System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

Close the Connection Object:

The Connection interface's close() function is used to terminate the connection. Statement and ResultSet will be closed automatically by closing the connection object. 

Syntax:

public void close()throws SQLException

Example:

con.close();

Program:

import java.io.*;
import java.sql.*;
public class DBC {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/emp";
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) 
{
Connection c = null;
Statement s = null;
try{
//STEP 2: Register JDBC driver 
Class.forName("com.mysql.cj.jdbc.Driver");
//STEP 3: Establish a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,"root","");
//STEP 4: Creating a Statement
System.out.println("Creating statement...");
s = c.createStatement();
String sql;
sql = "SELECT id,name, age FROM Students";
ResultSet rs = s.executeQuery(sql);
//STEP 5: Executing Queries
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("Name");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", Name: " + name);
}
//STEP 6: Closing the Connection
rs.close();
s.close();
c.close();
}catch(SQLException se){
//Exception Handling for JDBC
se.printStackTrace();
}catch(Exception e){
//Exception Handling for Class.forName
e.printStackTrace();
}finally{
System.out.println("THE END");
}//finally block
}//end main
}