Java SE JDBC: Insert with PreparedStatement Example

In this tutorial, we will take a look at how to insert records into a database table using java.sql package PreparedStatement,

Database Table:

mysql> desc my_uat.students;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| student_id      | int         | NO   | PRI | NULL    | auto_increment |
| student_name    | varchar(45) | NO   |     | NULL    |                |
| student_dob     | datetime    | NO   | PRI | NULL    |                |
| student_address | varchar(45) | NO   |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

Table create Statement:

CREATE TABLE `students` (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `student_name` varchar(45) NOT NULL,
  `student_dob` datetime NOT NULL,
  `student_address` varchar(45) NOT NULL,
  PRIMARY KEY (`student_id`,`student_dob`)

Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;

public class JDBCInsertExample {
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        String url ="jdbc:mysql://localhost:3306/my_uat";
        String userName="root";
        String password ="root123";
        String insertQuery ="insert into students values(?,?,?,?)";

        Connection connection = DriverManager.getConnection(url,userName,password);
        PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);

        preparedStatement.setString(1, null);
        preparedStatement.setString(2, "Andrew");
        preparedStatement.setObject(3, LocalDate.of(1999, 07, 21));
        preparedStatement.setString(4, "Ohio");
        int result = preparedStatement.executeUpdate();

        if(result == 1) {
            System.out.println("Record Insterted Successfully!");
        } else {
            System.out.println("Error occurred while inserting a record to database!");
        }
        
    }
}

Output:

Record Inserted Successfully!

If the query is not well-formed, you will get java.sql.SQLSyntaxErrorException,

Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int students values(null,'Andrew','1999-07-21','Ohio')' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at JDBCInsertExample.main(JDBCInsertExample.java:23)

Have Questions? Post them here!