Friday, April 11, 2014

How to Insert, Update and Delete data with MySQL database



In my previous post I showed how to connect with MySQL database and retrieve using Java. In this post I am going to illustrate how to insert data into your MySQL database, how to update data on it and how to delete data from it. To do this we use "Prepared Statements" in Java. 

First of all I wanted to create MySQL database and a table in it. Following query will create a database called "test" and a table called "student". If you want to remind about that, try this (http://easyjavase.blogspot.com/2014/03/familiar-with-mysql.html#.UzGXZvmSxio)


create database test;

use test;

create table student(

 id int primary key,
 name varchar(30),
 age int,
 city varchar(20)
);

insert into student values
 (01,'John',23,'Colombo'),
 (02,'Ann',22,'Kandy'),
 (03,'Stella',25,'Galle'),
 (04,'Don',22,'Trinco');

select * from student


Now you have successfully created a "student" table in a database called "test".

Then lets move to the program. You can use your existing project folder or you can create new project also. But don't forget to add the MYSQL JDBC Driver to your project. In previous post you got experience how to add the library into your project. you can find more on, http://easyjavase.blogspot.com/2014/03/how-to-connect-to-mysql-database-using.html#.U0LPgPmSxio

Here is the simple code of my project. 

package j2ee;

import java.sql.*;

public class DbUpdate {
    public static void main(String[] args) throws SQLException {
        
        Connection dbConnection = null;
        PreparedStatement PSInsert = null;
        PreparedStatement PSUpdate = null;
        PreparedStatement PSDelete = null;
        
        String insertTable = "INSERT INTO student" + "(id,name,age,city)VALUES" + "(? , ? , ? , ?)";
        String updateTable = "UPDATE student SET name = ?" + "WHERE id = ?";
        String deleteTable = "DELETE FROM student " + "WHERE id = ?";
        
        try{
            
            dbConnection  = getDBConnection();
            dbConnection.setAutoCommit(false);
            
            //for insert
            PSInsert = dbConnection.prepareStatement(insertTable);
            PSInsert.setString(1, "05");
            PSInsert.setString(2, "Sean");
            PSInsert.setString(3, "26");
            PSInsert.setString(4, "Colombo");
            PSInsert.executeUpdate();
            dbConnection.commit();
            
            //for update
            PSUpdate = dbConnection.prepareStatement(updateTable);
            PSUpdate.setString(1, "Ann-updated");
            PSUpdate.setString(2, "02");
            PSUpdate.executeUpdate();
            dbConnection.commit();
            
            //for delete
            PSDelete = dbConnection.prepareStatement(deleteTable);
            PSDelete.setString(1, "04");
            PSDelete.executeUpdate();
            dbConnection.commit();
            
            System.out.println("Done..!!!");         
            
        }catch(SQLException e){
            System.out.println("Error occured " + e.toString());
            dbConnection.rollback();
        }
        finally{
            if(PSInsert !=null){
                PSInsert.close();
            }
            if(PSUpdate != null){
                PSUpdate.close();
            }
            if(dbConnection != null){
                dbConnection.close();
            }
        }
     }
    
    private static Connection getDBConnection(){
        Connection con = null;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException e){
            System.out.println("Error 1 : " + e.getMessage());
        }
        try{
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","1234");
        }catch(SQLException e){
            System.out.println("Error 2 : " + e.getMessage());
        }
        return con;
    }
}


This is very simple static Java program that can be used to insert, update and delete data from MySQL database. 


    PreparedStatements 


    • In this program we retrieve data in run time. So we need to use runtime parameters to perform the task.
    • In my first program in last post I have used Statement objects. We cannot use these objects to retrieve data in runtime. 
    • That is why we use PreparedStatements as runtime objects. This allows to pass runtime parameters to SQL queries.
    • In this program I have created three types of PreparedStatement objects. 


    getDBConnection() method


    • This is not a pre defined method. I have created this method to Connect MySQL database. 
    • I have used separate try-catch blocks to easily identify any errors that can be happen while connecting.

    setAutoCommit() or commit() 

    • This commit keyword belongs to SQL transactions. 
    • commit is used to save the changes in the SQL. 
    • In JavaSE 7 API, it says, void commit() Makes all changes made since the previous commit / rollback permanent and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled.
    • You need to commit your code after execute() method or you can set setAutoCommit(true).

     setInt(), setString(), setDate() ...etc


    • In my program I have used setInt() and setString() methods.
    • Each method consist of Parameter index and value.
      • setInt( int ParameterIndex, int value )
    • This method sets the value according to the given parameter and then the driver converts this into SQL Integer value.

    Think about following code that I have used,

    String insertTable = "INSERT INTO student" + "(id,name,age,city)VALUES" + "(? , ? , ? , ?)";
    
                PSInsert.setInt(1, 8);               //Index is 1, value is 8
                PSInsert.setString(2, "Sean");       //Index is 2, value is 'Sean'
                PSInsert.setInt(3, 26);              //Index is 3, value is 26
                PSInsert.setString(4, "Colombo");    //Index is 4, value is 'Colombo'
    
    

    rollback()

    • This is another special method which is used to roll back all the changes that we have created in the database, if there are any error while transaction.
    • This method is used in the catch() block.
    • This method should be used only when auto-commit mode has been disabled( setAutoCommit(false) ).


    Finally I have closed prepared statements and the connection.