JDBC - Insert record example

Posted on January 29, 2017


Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

In this section, we will show you how to insert records into a table using the JDBC API. There are four steps for inserting a record into a table via JDBC.

  • Open connection to the database.
  • Create a statement object to perform an insert query.
  • Execute the executeUpdate()  method of statement object to submit a SQL query to database.
  • Close connection to the database.

Insert record + JDBC Statement example

Here is an example of using the JDBC Statement to insert a record into a table.

InsertRecordExample.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class InsertRecordExample {

  public static void main(String[] args) {
    String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
    String username = "root";
    String password = "admin";

    Connection conn = null;
    Statement stmt = null;

    try {
      //Open connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      
      //Create statement
      stmt = conn.createStatement();

      //Insert first reocod
      String record1="INSERT INTO users (UID, NAME, DOB, EMAIL) "
          + "VALUES (1,'Joseph','1988-12-25','[email protected]')";
      stmt.executeUpdate(record1);
      
    //Insert second reocod
      String record2="INSERT INTO users (UID, NAME, DOB, EMAIL) "
          + "VALUES (2,'Andrew','1975-05-20','[email protected]')";
      stmt.executeUpdate(record2);

      System.out.println("Records inserted");

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        // Close connection
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

Output

Records inserted

MySQL 5.7 Command Line Client test

mysql> select * from users;
+-----+--------+------------+--------------------+
| UID | NAME   | DOB        | EMAIL              |
+-----+--------+------------+--------------------+
|   1 | Joseph | 1988-12-25 | [email protected]    |
|   2 | Andrew | 1975-05-20 | [email protected] |
+-----+--------+------------+--------------------+

 

Insert record + JDBC PreparedStatement example

Here is an example of using the JDBC PreparedStatement to insert a record into a table.

InsertRecordExample2.java

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;


/**
 * @author imssbora
 */
public class InsertRecordExample2 {

  private static final String INSERT_SQL="INSERT INTO employee "
      + "(EMP_ID, NAME, DOB, EMAIL, DEPT) VALUES (?,?,?,?,?)";
  
  public static void main(String[] args) {
    String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
    String username = "root";
    String password = "admin";
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    Connection conn = null;
    PreparedStatement stmt = null;

    try {
      // Open connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);

      // Create statement
      stmt = conn.prepareStatement(INSERT_SQL);

      // Insert first record
      stmt.setInt(1, 1);
      stmt.setString(2, "Michael");
      stmt.setDate(3, new Date(dateFormat.parse("1995-07-01").getTime()));
      stmt.setString(4, "[email protected]");
      stmt.setString(5, "IT Department");
      stmt.executeUpdate();

      // Insert second record
      stmt.setInt(1, 2);
      stmt.setString(2, "Henry");
      stmt.setDate(3, new Date(dateFormat.parse("1990-02-21").getTime()));
      stmt.setString(4, "[email protected]");
      stmt.setString(5, "HR Department");
      stmt.executeUpdate();

      System.out.println("Records inserted");

    } catch (SQLException | ParseException e) {
      e.printStackTrace();
    } finally {
      try {
        // Close connection
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

Output

Records inserted

MySQL 5.7 Command Line Client test

mysql> select * from employee;
+--------+---------+------------+---------------------+---------------+
| EMP_ID | NAME    | DOB        | EMAIL               | DEPT          |
+--------+---------+------------+---------------------+---------------+
|      1 | Michael | 1995-07-01 | [email protected] | IT Department |
|      2 | Henry   | 1990-02-21 | [email protected]   | HR Department |
+--------+---------+------------+---------------------+---------------+