How to Insert, Update and Delete data with MySQL database



In my previous article, I showed how to connect to 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 a MySQL database and a table in it. The following query will create a database called "test" and a table called "student". If you want to remember 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 let's move to the program. You can use your existing project folder or you can create a new project also. But don't forget to add the MYSQL JDBC Driver to your project. In the previous post, you got to experience how to add the library to 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 a very simple static Java program that can be used to insert, update and delete data from the MySQL database. 


    PreparedStatements 


    • In this program, we retrieve data at run time. So we need to use runtime parameters to perform the task.
    • In my first program in the last post, I have used Statement objects. We cannot use these objects to retrieve data at runtime. 
    • That is why we use PreparedStatements as runtime objects. This allows passing 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 the MySQL database. 
    • I have used separate try-catch blocks to easily identify any errors that can 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 the prepared statements and the connection.



    How to Insert, Update and Delete data with MySQL database How to Insert, Update and Delete data with MySQL database Reviewed by Ravi Yasas on 10:05 PM Rating: 5

    1 comment:

    1. You arre so awesome! I don't suppose I've truly
      read through anything like that before. So good to find ssomebody
      wikth some unique tthoughts on this issue.
      Really.. thanks for stzrting this up. This website is onee thing that is required on the web, someone with spme originality!

      ReplyDelete

    Powered by Blogger.