JDBC Statement – Insert Records Example – Developers Corner

Java

A JDBC statement object is used to send SQL command(s) to your relational database management system (RDBMS). It is associated with an open connection to the database and may not be created without one. In this example, we will insert several records into the employee table using separate executeUpdate() calls.

Common Methods used with Statements

Method Name	Description
addBatch(String sql)	Adds the given SQL to the current list of SQL commmands for this Statement object
execute(String sql)	Executes the given SQL statement, which may return multiple results
executeBatch()	Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts
executeQuery(String sql)	Executes the given SQL statement, which returns a single ResultSet object
executeUpdate(String sql)	Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement

Creating a Statement Object

statement = connection.createStatement();
statement.executeUpdate(sql);

Full Program Listing

package com.avaldes.tutorials;

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

public class JDBCStatementInsertRecordsExample {

  public static void main(String[] args) throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username       = "webuser";
    String password       = "webuser123";
    Connection connection = null;
    Statement statement   = null;
    ResultSet result      = null;
    String sql            = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    System.out.println("Trying to get create a table using statement...");
    try {
      if (connection != null) {
        statement = connection.createStatement();
        sql = "INSERT INTO dbo.employee"
            + "(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) "
            + "VALUES(10, 'John', 'Baker', 'S', 109800, '2/12/2011', null);";
        statement.executeUpdate(sql);
        System.out.println(sql);
        
        sql = "INSERT INTO dbo.employee"
            + "(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) "
            + "VALUES(11, 'Mary', 'Anderson', 'S', 104100, '05/24/2009', null);";
        statement.executeUpdate(sql);
        System.out.println(sql);

        sql = "INSERT INTO dbo.employee"
            + "(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) "
            + "VALUES(12, 'Tina', 'Mayer', 'S', 143700, '04/08/2012', null);";
        statement.executeUpdate(sql);
        System.out.println(sql);
        
        System.out.println("Inserted records into employee table...");
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      return;
    } finally {
      System.out.println("Closing connection...");
        if (connection != null) connection.close();
    }
  }
}

Check Table to verify records where properly inserted

statement_insert_records — Table 1: Employee Table

Output

Trying to get create a table using statement...
INSERT INTO dbo.employee(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) VALUES(10, 'John', 'Baker', 'S', 109800, '2/12/2011', null);
INSERT INTO dbo.employee(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) VALUES(11, 'Mary', 'Anderson', 'S', 104100, '05/24/2009', null);
INSERT INTO dbo.employee(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) VALUES(12, 'Tina', 'Mayer', 'S', 143700, '04/08/2012', null);
Inserted records into employee table...
Closing connection...