Using a PreparedStatement Object to Execute an INSERT Statement - Java JDBC

Java examples for JDBC:SQL Statement

  1. HOME
  2. Java
  3. JDBC
  4. SQL Statement

Description

Using a PreparedStatement Object to Execute an INSERT Statement

Demo Code

import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class Main {
  public static void main(String[] args) {
    Connection conn = null;//from  ww  w  . ja  va  2  s. c  o m
    PreparedStatement pstmt = null;
    try {
      conn = JDBCUtil.getConnection();
      pstmt = getInsertSQL(conn);

      Date dob = Date.valueOf("2017-01-01");

      insertPerson(pstmt, 401, "S", "A", "F", dob, 0.0);
      insertPerson(pstmt, 501, "S", "B", "F", null, 10000.0);

      JDBCUtil.commit(conn);

      System.out.println("Updated person records successfully.");
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      JDBCUtil.rollback(conn);
    } finally {
      JDBCUtil.closeStatement(pstmt);
      JDBCUtil.closeConnection(conn);
    }
  }

  public static void insertPerson(PreparedStatement pstmt, int personId,
      String firstName, String lastName, String gender, Date dob, double income)
      throws SQLException {

    pstmt.setInt(1, personId);
    pstmt.setString(2, firstName);
    pstmt.setString(3, lastName);
    pstmt.setString(4, gender);

    if (dob == null) {
      pstmt.setNull(5, Types.DATE);
    } else {
      pstmt.setDate(5, dob);
    }

    pstmt.setDouble(6, income);

    pstmt.executeUpdate();
  }

  public static PreparedStatement getInsertSQL(Connection conn)
      throws SQLException {
    String SQL = "insert into person "
        + "(person_id, first_name, last_name, gender, dob, income) "
        + "values " + "(?, ?, ?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(SQL);
    return pstmt;
  }
}

class JDBCUtil {
  public static Connection getConnection() throws SQLException {
    Driver derbyEmbeddedDriver = null;// new
                                      // org.apache.derby.jdbc.EmbeddedDriver();
    DriverManager.registerDriver(derbyEmbeddedDriver);

    // Construct the connection URL
    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";

    Connection conn = DriverManager.getConnection(dbURL, userId, password);

    // Set the auto-commit off
    conn.setAutoCommit(false);

    return conn;
  }

  public static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeStatement(Statement stmt) {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeResultSet(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void commit(Connection conn) {
    try {
      if (conn != null) {
        conn.commit();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void rollback(Connection conn) {
    try {
      if (conn != null) {
        conn.rollback();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("Connetced to the database.");
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      closeConnection(conn);
    }
  }
}

Related Tutorials