How to insert a record using JDBC PreparedStatement

After we followed how to connect to MySQL with JDBC driver and how to create a table using jdbc statement tutorial is time to insert some data into this table.

Example

package com.admfactory.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class JDBCInsertRecord {

    private static Connection connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/admfactory.com", "root", "");
        } catch (Exception e) {
            System.out.println("Failed to connect to DB.");
            e.printStackTrace();
            return null;
        }
    }

    private static void insertData(String name, String email, String password, String type, Date dob) throws Exception {
        PreparedStatement statement = null;
        try {
            String query = "INSERT INTO `user` (`id`, `name`, `email`, `password`, `type`, `dob`) VALUES(?, ?, ?, ?, ?, ?);";
            Connection connection = connect();
            statement = connection.prepareStatement(query);
            /** add null for auto increment column */
            statement.setNull(1, java.sql.Types.BIGINT);
            statement.setString(2, name);
            statement.setString(3, email);
            statement.setString(4, password);
            statement.setString(5, type);
            statement.setDate(6, dob);
            int r = statement.executeUpdate();
            /** Check if the user was successfully inserted. */
            if (r > 0) {
                System.out.println("New user [" + name + ", " + email + "] was successfully inserted.");
            } else {
                System.out.println("Failed to insert new record!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    public static void main(String[] args) {
        try {
            System.out.println("Insert new records.");
            /** Get the connection. */
            Connection connection = connect();

            if (connection != null) {
                /** Insert some data. */
                insertData("John Doe", "john@gmail.com", "pass", "REGULAR", new Date(new java.util.Date().getTime()));
                insertData("Admin Joe", "joe@gmail.com", "12345", "ADMIN", new Date(new java.util.Date().getTime()));
                insertData("Subsriber Jill", "jill@gmail.com", "qwert", "SUBSCRIBER",
                        new Date(new java.util.Date().getTime()));
                System.out.println("Records inserted successfully!");
                /** Close the connection. */
                connection.close();
            }
        } catch (Exception e) {
            System.out.println("Something went wrong.");
        }

    }
}

Note that the index in the statement object starts with 1. It is a very common mistake starts from 0.

Output

Insert new records.
New user [John Doe, john@gmail.com] was successfully inserted.
New user [Admin Joe, joe@gmail.com] was successfully inserted.
New user [Subsriber Jill, jill@gmail.com] was successfully inserted.
Records inserted successfully!

References

• How to connect to MySQL with JDBC driver
• How to create a table using JDBC statement
• PreparedStatement
• Statement