Java Database connectivity

In this blog, i will explain how to write a Java program which can interact with database and perform the following operations

• Record Insertion 
• Record Updation
• Record Deletion
• Viewing of the Records

In this example, we will use MySQL database. hence we need the following mysql-connector.jar file before we continue

First we will create a table called students in our mysql database TestDB.
Execute the following queries to create Database and Table .


mysql> create database TestDB;


mysql> use TestDB;


mysql> create table students(id int primary key,firstName varchar(25) not null,lastName varchar(25) not null,email varchar(75) not null unique);



JDBCConnection.java


package com.test.jdbc;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class JDBCConnection
{


    // This method is used to make the connection with mysql database
    public Connection getConnection()
    {
        Connection con = null;


        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/TestDB", "root", "sa1@#$");
        }
        catch(Exception e)
        {
            System.out.println("Connection Failure");
            e.printStackTrace();
        }


        return con;
    }


    // This method is used to insert a row in students table
    public void insertRow(int Id,String firstName,String lastName,String email)
    {
        Connection connection = new JDBCConnection().getConnection();
        Statement stmt = null;


        try
        {
            stmt = connection.createStatement();
            stmt.executeUpdate("insert into STUDENTS values("+ Id +",'"+ firstName +"','"+ lastName +"','"+ email +"')");
            System.out.println("Student inserted successfully");
        }
        catch(Exception e)
        {
            System.out.println("Student insertion failure");
            e.printStackTrace();
        }


    }


     // This method is used to update an existing row in students table
    public void updateRow(int Id,String firstName,String lastName,String email)
    {
        Connection connection = new JDBCConnection().getConnection();
        Statement stmt = null;


        try
        {
            stmt = connection.createStatement();
            int no_of_rows_updated = stmt.executeUpdate("update STUDENTS set firstName='"+ firstName +"',lastName='"+ lastName +"',email='"+ email +"' where id="+Id);
            if(no_of_rows_updated > 0)
            {
                System.out.println("Student updated successfully");
            }
            else
            {
                System.out.println("Student does not exist, provide proper Id");
            }
        }
        catch(Exception e)
        {
            System.out.println("Student updation failure");
            e.printStackTrace();
        }


    }




    // This method is used to delete a row in students table
    public void deleteRow(int Id)
    {
        Connection connection = new JDBCConnection().getConnection();
        Statement stmt = null;


        try
        {
            stmt = connection.createStatement();
            int no_of_rows_deleted = stmt.executeUpdate("delete from STUDENTS where id="+Id);
            if(no_of_rows_deleted > 0)
            {
                System.out.println("Student removed successfully");
            }
            else
            {
                System.out.println("Student does not exist, provide proper Id");
            }
        }
        catch(Exception e)
        {
            System.out.println("Student deletion failure");
            e.printStackTrace();
        }


    }


    // This method is used to display all the rows of students table
    public void displayRows()
    {
        Connection connection = new JDBCConnection().getConnection();
        Statement stmt = null;
        ResultSet rs = null;


        try
        {
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from STUDENTS");


            while(rs.next())
            {
                System.out.println("Id:" + rs.getString("id")+"\t FirstName:"+ rs.getString("firstName")+"\t LastName:"+ rs.getString("lastName"));
            }


        }
        catch(Exception e)
        {
            e.printStackTrace();
        }


    }


    public static void main(String args[])
    {


        // To insert values uncomment the below lines
            //new JDBCConnection().insertRow(1, "John", "McKenzie", "john@abc.com");
            //new JDBCConnection().insertRow(2, "Brian", "Zaranyika", "brian@abc.com");


        // To update values uncomment the below line
            //new JDBCConnection().updateRow(1, "Johny", "Bravo", "john@abc.com");


        // To delete values uncomment the below line
            //new JDBCConnection().deleteRow(2);


        // This line displays all the rows in the table
        new JDBCConnection().displayRows();


      
    }




}