JDBC- Java Database Connectivity

In this Article we will be discussing about what is JDBC and how to setup and run our first program.

Contents:

1. What is JDBC?
2. Need of JDBC?
3. Datatypes in JDBC?
4. JDBC Architecture?
5. How to use JDBC and write our first Program?

What is JDBC?

Java Database Connectivity (JDBC) is an application program interface (API) packaged with the Java that makes it possible to simplify the process of connecting Java applications to external, relational database management systems (RDBMS).

Need for JDBC:

1. Establish database connectivity
2. To Execute SQL queries and DDL/DML commands
3. View and modify data records

Datatypes in JDBC:

JDBC Architecture:

Using Java code , we will Establish the connection using Driver Manager based on the SQL.

Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_employees","root","Koushick@07");

How to use JDBC and write our first Program:

In This example, we will be writing the basic JDBC program for the below statements,

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

Also, we will be using the below tables to use in our code. I have already created the table and DB in my local.

if you want to use the same data as well in your local copy the below file and execute all the commands at once

create database if not exists demo_employees;

use demo_employees;

drop table if exists employees;

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(64) DEFAULT NULL,
  `first_name` varchar(64) DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `department` varchar(64) DEFAULT NULL,
  `salary` DECIMAL(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);

Let’s code now..

To start writing the code in Java first we need the JDBC connection and then following to it we need a statement to execute

Connection:

To Establish a connection we need the Driver to execute, the Driver varies based on the SQL for MySQL we need to add the below Driver,

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

 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_employees","root","Koushick@07");
 connection.setAutoCommit(false);

Statement:

//using the connection reference variable we are creating the statement to execute
Statement statement = connection.createStatement();
//GET
 statement.executeQuery(sql);
//UPDATE
statement.executeUpdate(updateSql);
//INSERT
statement.executeUpdate(sql);
//DELETE
statement.executeUpdate(deleteSql);

ResultSet:

Using ResultSet we will be able to fetch the records from the table by string or Index.

ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                String salary = resultSet.getString("salary");
                System.out.println(firstName + " | " + lastName + " | " + salary);
            }

Let’s see the full code now..

import org.junit.jupiter.api.*;

import java.sql.*;

@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class SQLBasicQueries {

    Connection connection = null;

    @Test
    @Order(1)
    public void selectQuery(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_employees","root","Koushick@07");
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            String sql = "select * from employees";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                String salary = resultSet.getString("salary");
                System.out.println(firstName + " | " + lastName + " | " + salary);
            }
            System.out.println("Select Query Executed..");
            statement.close();
            connection.commit();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


    }

    @Test
    @Order(2)
    public void InsertQuery(){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_employees","root","Koushick@07");
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            String sql = "INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (14,'Kumar','Dinesh','dinesh.kumar@foo.com', 'HR', 70000.00)";
            int rowsaffected = statement.executeUpdate(sql);
            System.out.println("The Number of rows affected : " + rowsaffected);
            System.out.println("Insert Query Updated..");
            System.out.println("Checking the table after the update...");
            String getnewrecord = "select * from employees where last_name='Kumar'";
            ResultSet resultSet = statement.executeQuery(getnewrecord);
            while (resultSet.next()){
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                String salary = resultSet.getString("salary");
                System.out.println(firstName + " | " + lastName + " | " + salary);
            }
            System.out.println("Select Query Executed...");
            statement.close();
            connection.commit();
            connection.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    @Order(3)
    public void UpdateQuery(){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_employees","root","Koushick@07");
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            System.out.println("Calling Select Query Before Updating...");
            String getallrecords = "select * from employees";
            ResultSet resultSetget = statement.executeQuery(getallrecords);
            while (resultSetget.next()){
                String firstName = resultSetget.getString("first_name");
                String lastName = resultSetget.getString("last_name");
                String salary = resultSetget.getString("salary");
                System.out.println(firstName + " | " + lastName + " | " + salary);
            }
            System.out.println("Select Query Executed...");
            System.out.println("Updating The Record...");
            String updateSql = "update employees set salary=85000 where first_name='Dinesh' and last_name='Kumar'";
            int rowsaffected = statement.executeUpdate(updateSql);
            System.out.println("The Number of rows affected : " + rowsaffected);
            System.out.println("Update Query Executed...");
            System.out.println("Checking the table after the update..");
            String sql = "select * from employees where first_name='Dinesh' and last_name='Kumar'";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                String salary = resultSet.getString("salary");
                System.out.println(firstName + " | " + lastName + " | " + salary);
            }
            System.out.println("Select Query Executed...");
            statement.close();
            connection.commit();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    @Order(4)
    public void DeleteQueries(){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_employees","root","Koushick@07");
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            String deleteSql = "delete from employees where first_name='Dinesh' and last_name='Kumar'";
            int rowsupdated = statement.executeUpdate(deleteSql);
            System.out.println("The Number of rows Affected : " + rowsupdated);
            System.out.println("Delete Query Executed..");
            System.out.println("Checking the table after the update..");
            String getdeletedata = "select * from employees where first_name='Dinesh' and last_name='Kumar'";
            ResultSet resultSet = statement.executeQuery(getdeletedata);
            if(!resultSet.next()){
                System.out.println("Employee Record is not Found. please change your query..");
            }
            statement.close();
            connection.commit();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Now let’s see the code little briefly..

selectQuery() — select * from employees

The above select method in Java code is trying to fetch all the data from employees table

InsertQuery() — INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (14,’Kumar’,’Dinesh’,’dinesh.kumar@foo.com’, ‘HR’, 70000.00)

The above Insert method is used to add entry in the table of name Dinesh Kumar.

UpdateQuery() — update employees set salary=85000 where first_name=’Dinesh’ and last_name=’Kumar’

The above update method is used to update the salary for the employee named Dinesh Kumar

DeleteQueries() — delete from employees where first_name=’Dinesh’ and last_name=’Kumar’

The above delete method is used to delete the employee entry named Dinesh Kumar

Hope it helps!

Happy Learning :)

Thank you