MySQL : Java Database Connectivity

Java Database Connectivity (JDBC) refers to a Java API (Application Program Interface) that facilitates communication between Java and a database. JDBC is a general term for a variety of databases. In this tutorial, we'll learn how to establish connectivity between Java and MySQL, so that we can perform the CRUD operations on our database using a java program. 

Prerequisites :

MySQL must be installed. If not, you can get it here. (Remember the password you provided for root user.)

Download mysql jar file from here. Unzip the contents and copy mysql-connector-java-8.0.21 file and paste in a folder(C:\mysqlJar on my machine) and remember to include it in classpath while running our java program.

Getting started with MySQL :

Open command prompt , in that type 

mysql -uroot -p{Enter the password for root user}

This command will open the MySQL prompt. Now let's see the basic commands.

To create database :

create database jdbcDB; 

To create user : 

create user 'jdbcuser'@'localhost' identified by 'jdbcpassword';

Note : You can give any other username in place of jdbcuser and any other password instead of jdbcpassword.

To grant privileges to jdbcuser :

grant all privileges on jdbcDB.* to 'jdbcuser'@'localhost' with grant option;

To quit from mysql prompt :

quit;

Now login again, but this time from jdbcuser :

mysql -ujdbcuser -pjdbcpassword

To select database :

use jdbcDB;

To create table  : 

create table employee (id int primary key auto_increment, name varchar(50) not null, age int 

not null);

To insert record in table : 

insert into employee(name,age) values('Tom',29);

For multiple records :

insert into employee(name,age) values('Dick',26),('Harry',34);

To delete records :

delete from employee where name='Tom';

To update record : 

update employee set age=30 where name='Harry';

To delete table : 

drop table employee;

Now that we have the basic knowledge of MySQL queries, we'll see about the JDBC implementation.

Steps to follow : 

1. Load Driver class.

2. Establish connection with the MySQL server by providing port number,database name(jdbcDB),username(jdbcuser) and password(jdbcpassword).

3. Get the instance of Statement class and execute our mysql statement(create, insert etc) by calling the required method of Statement class. 

4. Close all the database resources (statement, connection etc.).

Note : We are using finally block to close all the resources because whether or not any exception is raised, finally block will be executed. So, all the database resources will be definitely released.

Creating A Table

(EmployeeTable.java)

import java.sql.*;
class EmployeeTable 
{
public static void main(String args[])
{
Connection c=null;
Statement s=null;
try
{
System.out.println("Loading Driver class");
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Establishing connection.");
c=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcDB","jdbcuser","jdbcpassword");
System.out.println("Creating MySQL statement.");
s=c.createStatement();
String str="create table employee(" 
	+ " id int primary key auto_increment, " 
	+ " name varchar(50) not null, " 
	+ " age int not null) ";
System.out.println("Executing create statement.");
s.executeUpdate(str);
System.out.println("Table created.");
}
catch(Exception e)
{
e.printStackTrace();
System.exit(0);
}
finally
{
try
{
if(s!=null) s.close();
if(c!=null) c.close();
}catch(SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(0);
}
}
}
}

To compile, type in command prompt :

javac EmployeeTable.java

To run : 

java -classpath c:\mysqljar\*;. EmployeeTable

This was my output :

Note : Check in the mysql prompt whether the table is created in the database you specified.

Insert Into Table 

(InsertExample.java)

import java.sql.*;
class InsertExample 
{
public static void main(String args[])
{
Connection c=null;
Statement s=null;
try
{
System.out.println("Loading Driver class");
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Establishing connection.");
c=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcDB","jdbcuser","jdbcpassword");
System.out.println("Creating MySQL statement.");
s=c.createStatement();
String str="insert into employee(name,age) values('Tom',45), ('Dick',50),('Harry',25)";
System.out.println("Executing insert statement.");
s.executeUpdate(str);
System.out.println("Records inserted.");
}
catch(Exception e)
{
e.printStackTrace();
System.exit(0);
}
finally
{
try
{
if(s!=null) s.close();
if(c!=null) c.close();
}catch(SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(0);
}
}
}
}

Compile and run as before. This was my output.

Note : Check in the mysql prompt to check whether the records are inserted in the employee table. 

Delete From Table

(DeleteExample.java)

import java.sql.*;
class DeleteExample 
{
public static void main(String args[])
{
Connection c=null;
Statement s=null;
try
{
System.out.println("Loading Driver class");
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Establishing connection.");
c=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcDB","jdbcuser","jdbcpassword");
System.out.println("Creating MySQL statement.");
s=c.createStatement();
String str="delete from employee where id = 3 ";
System.out.println("Executing delete statement.");
s.executeUpdate(str);
System.out.println("Record deleted.");
}
catch(Exception e)
{
e.printStackTrace();
System.exit(0);
}
finally
{
try
{
if(s!=null) s.close();
if(c!=null) c.close();
}catch(SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(0);
}
}
}
}

Compile and run like before. This was my output :

Note : Remember to check in the mysql prompt to whether the record is deleted from the employee table.

Update Contents Of Table 

In this example, we'll learn a new way for writing JDBC code : using class PreparedStatement.

PreparedStatement class has simplified the task of writing complex mysql statements in the form of strings and has facilitated a more dynamic approach to introduce values in those strings. We just have to write a question mark wherever we want data to be inserted dynamically. And data is placed by calling setString/setInt methods of this class. 

(UpdateExample.java)

import java.sql.*;
class UpdateExample 
{
public static void main(String args[])
{
Connection c=null;
PreparedStatement ps=null;
try
{
System.out.println("Loading Driver class");
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Establishing connection.");
c=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcDB","jdbcuser","jdbcpassword");
System.out.println("Creating MySQL statement.");
String str="update employee set name= ?, age = ? where id = ? ";
ps=c.prepareStatement(str);
ps.setString(1, "Rohan");
ps.setInt(2,37);
ps.setInt(3, 4);
System.out.println("Executing update statement.");
ps.executeUpdate();
System.out.println("Record updated.");
}
catch(Exception e)
{
e.printStackTrace();
System.exit(0);
}
finally
{
try
{
if(ps!=null) ps.close();
if(c!=null) c.close();
}catch(SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(0);
}
}
}
}

Compile and run like before. This was my output :

Fetch Data From Table

For learning about this, let us first understand ResultSet interface in java.

ResultSet 

ResutSet : An object of type ResultSet stores the result of a query. It maintains a cursor that points to its current row of data. 

next - The next method moves the cursor to the next row. It returns true if there is a next row and false otherwise.

getInt- There are two overloaded forms of getInt. One that takes the index of column as an argument and another that takes the name of column as an argument. It returns the value of the specific column of current row. 

getString - Try to interpret this method yourself.

(SelectExample.java)

import java.sql.*;
class SelectExample 
{
public static void main(String args[])
{
Connection c=null;
PreparedStatement ps=null;
ResultSet rs=null;
try
{
System.out.println("Loading Driver class");
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Establishing connection.");
c=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcDB","jdbcuser","jdbcpassword");
System.out.println("Creating MySQL statement.");
String str="select * from employee where age > ?" ;
ps=c.prepareStatement(str);
ps.setInt(1,30);
System.out.println("Executing update statement.");
rs=ps.executeQuery();
System.out.println("Fetching records...");
String name;
int age;
System.out.println("Name\t"+"Age");
while(rs.next())
{
name=rs.getString("name");
age=rs.getInt("age");
System.out.println(name+"\t"+age);
}
}
catch(Exception e)
{
e.printStackTrace();
System.exit(0);
}
finally
{
try
{
if(rs!=null) rs.close();
if(ps!=null) ps.close();
if(c!=null) c.close();
}catch(SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(0);
}
}
}
}

Compile and run like before. This was my output :

Assignment : Try and implement delete using PreparedStatement and select using Statement class.

Conclusion 

In this article, we learned the basic examples of JDBC and how to perform CRUD operations through  a java program. Execute these examples yourself and ask any doubts in the comments.