JAVA JDBC – Insert, Update, Select and Delete Data in MySQL Table

In this tutorial you will learn:

1. How to Insert, Update, Select and Delete Data from MySQL in JDBC?
2. Programming Example

In this tutorial you are going to learn insert, update, delete or select rows using JDBC. While developing Java Application it is almost the very important part. Most of the application lies on Database and they use Table to save and retrieve records. It is very important to have sound knowledge over data manipulation, if you want to be a perfect java developer. Here, I am attaching a simple program that will easily demonstrate insert, update, delete and search record in table using JDBC.

Programming Example

package TableDemo;
import java.sql.*;

public class InsertRecord
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('190','MousePad')";
	    String query1="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('2870','Stationary')";
	    String query2="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('765','Books')";
	    String query3="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('3887','HardDisk')";
	    String query4="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('953','Ram')";
	    String query5="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('8746','Printer')";
	    String query6="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('646','Keyboard')";
	    String query7="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('947','Mouse')";
	    String query8="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('850','JoyStick')";
	    String query9="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('3252','Camera')";
	    
	    
	    //Step 4 : Run Query
	    
	    stmt.executeUpdate(query);
	    stmt.executeUpdate(query1);
	    stmt.executeUpdate(query2);
	    stmt.executeUpdate(query3);
	    stmt.executeUpdate(query4);
	    stmt.executeUpdate(query5);
	    stmt.executeUpdate(query6);
	    stmt.executeUpdate(query7);
	    stmt.executeUpdate(query8);
	    stmt.executeUpdate(query9);
	    
	    System.out.println("Record Inserted Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Record Inserted Successfully
Closing the connection.
_

Retrieve Records

Once you inserted record you can retrieve record using ResultSet Object of JDBC. Here is the complete programming example of retrieving data from MySQL using JDBC.

package TableDemo;
import java.sql.*;

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

public class SelectRecord
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(query);
	    while(rset.next())
	    {
	    	System.out.print("ID: " + rset.getInt(1));
	    	System.out.print(" Product : "+rset.getString(2));
	    	System.out.println(" Price : "+rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

ID: 1 Product : ToothPaste Price : 200
ID: 2 Product : MousePad Price : 190
ID: 3 Product : Stationary Price : 2870
ID: 4 Product : Books Price : 765
ID: 5 Product : HardDisk Price : 3887
ID: 6 Product : Ram Price : 953
ID: 7 Product : Printer Price : 8746
ID: 8 Product : Keyboard Price : 646
ID: 9 Product : Mouse Price : 947
ID: 10 Product : JoyStick Price : 850
ID: 11 Product : Camera Price : 3252
Closing the connection.
_

Update and Delete Records

Updating and Deleting Records in MySQL using JDBC is so simple. You have to follow same procedure and execute update or delete command. Here is the complete program.

Programming Example

package TableDemo;
import java.sql.*;

public class UpdateRecords
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String updatequery="UPDATE ITEM SET PRODUCT='UsbCable' Where ID=1";
	    String deletequery="DELETE FROM ITEM WHERE ID=9";
	    
	    
	    //Step 4 : Run Query
	    
	    stmt.executeUpdate(updatequery);
	    System.out.println("Table Updated Successfully");
	    stmt.executeUpdate(deletequery);
	    System.out.println("Row Deleted Successfully");
	    
	    System.out.println("******** Records Are *********");
        String selectquery="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(selectquery);
	    while(rset.next())
	    {
	    	System.out.print("ID: " + rset.getInt(1));
	    	System.out.print(" Product : "+rset.getString(2));
	    	System.out.println(" Price : "+rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Table Updated Successfully
Row Deleted Successfully
******** Records Are *********
ID: 1 Product : UsbCable Price : 200
ID: 2 Product : MousePad Price : 190
ID: 3 Product : Stationary Price : 2870
ID: 4 Product : Books Price : 765
ID: 5 Product : HardDisk Price : 3887
ID: 6 Product : Ram Price : 953
ID: 7 Product : Printer Price : 8746
ID: 8 Product : Keyboard Price : 646
ID: 10 Product : JoyStick Price : 850
ID: 11 Product : Camera Price : 3252
Closing the connection.
_

Summary

In this tutorial you learn to manipulate data like insert, update, select or delete records in MySQL Table using JDBC. Once you have completed this chapter you are now able to run your own query and get your desired result. In the next chapter you will learn to storing and retrieving image using JDBC.


Leave a Reply

Your email address will not be published. Required fields are marked *