In this article, we will perform Create, Read, Update and Delete operations on a MySQL database using JDBC.
 
 
We have created Employee table in the database, with the following structure :
 

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)

 
First, we need to setup connectivity from Java code to the database using JDBC.

Refer the following article for the same :

Connecting to Database using JDBC

 
For performing JDBC operations, we will create a Statement object from the Connection object as follows :
 

Statement stmt = conn.createStatement();

 
 
For Insert, Update and Delete operations, we can call Statement.executeUpdate() with required sql string as follows :
 

int rows = stmt.executeUpdate("insert into employee(age,name) values(23,'James')");
rows= stmt.executeUpdate("Update employee set age=31 where name='James'");
rows = stmt.executeUpdate("delete from employee where name = 'James'");

 
 
For Select operation, we will use executeQuery() method of Statement object.
 
This will provide us a ResultSet of records and we need to loop through them to find individual data.
 

ResultSet rs = stmt.executeQuery("Select * from employee");
    while(rs.next()){
      System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));
    }

 
 
Here is the complete program :
 

package com.topjavatutorial.jdbc;

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

public class ExampleCRUD_JDBCOperations {
  
  public static void main(String[] args) throws SQLException {

    String url ="jdbc:mysql://localhost:3306/TestDatabase"; //update connection string
    
    String user = "user";//add your db user id here
    String password = "password";//add your db password here
    
    Connection conn = DriverManager.getConnection(url, user, password);
    System.out.println("Successfully connected");
    
    //insert employee record into database
    Statement stmt = conn.createStatement();
    int rows = stmt.executeUpdate("insert into employee(age,name) values(23,'James')");
    System.out.println("Rows inserted = "+ rows);
    
    //update employee record
    rows= stmt.executeUpdate("Update employee set age=31 where name='James'");
    System.out.println("Rows updated = "+ rows);
    
    //read employee records
    ResultSet rs = stmt.executeQuery("Select * from employee");
    while(rs.next()){
      System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));
    }
    
    //delete employee record
    rows = stmt.executeUpdate("delete from employee where name = 'James'");
    System.out.println("Rows deleted = "+ rows);
  }
  

}

 
 

Output

 
Successfully connected
Rows inserted = 1
Rows updated = 1
Emp Id : 8, Name : John Doe, Age : 21
Emp Id : 10, Name : James, Age : 31
Rows deleted = 1

 
 

© 2016 – 2018, www.topjavatutorial.com. All rights reserved. On republishing this post, you must provide link to original post