Java JDBC Insert Example

By | September 29, 2021
Java JDBC Insert Example

Introduction

Web applications collect data from the user through the User Interface (UI) and insert the information into the database. This process happens automatically without us running any database scripts or monitoring data. This magic is done by JDBC (Java DataBase Connectivity), which is a Java API to connect Java code to any database.

What is JDBC (Prepared)Statement and JDBC insert

Through JDBC, we can perform all the standard SQL operations like insert, update, delete, etc. First, we need to establish the database connection, then create the SQL statements, execute them, and then verify the results.

Connecting to the database

To connect to the database, we have to establish JDBC connection using the getConnection() method by specifying the DriverManager or the DataSource:

Connection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", "jack@1880");
connectionProps.put("password", "kb@ert5k");
conn = DriverManager.getConnection(

        "jdbc:" + "mysql" + "://" +
        "localhost" +
        ":" + "8080" + "/",
        connectionProps);

Note that the servername and port number has to be as per your project. Here we have used localhost and 8080. Same way, here the dbms is mysql, but it depends on your dbms. The same applies for the username and password properties too.

Handling exceptions

The above code is correct except one thing – we have not handled SQLException. If there is any problem with establishing the connection, the program will throw SQLException, so we must add a try/catch block to handle the same:

Vamware
    try {
conn = DriverManager.getConnection(
        "jdbc:" + "mysql" + "://" +
        "localhost" +
        ":" + "8080" + "/",
        connectionProps);
} catch (SQLException e) {
System.out.println("Couldn't connect!");
}

Creating a series of inserts

We can insert rows using java sql Statement class or PreparedStatement class. The latter is preferred because it is secure, but to understand it we need to also learn about the former.

Using Statement

Create a statement using the connection established:

Vamware
Statement stmt = conn.createStatement();

Now, create the query and execute it.

String sql = "insert into employee values(1200, 'Jack Cha', '29-10-2013', 45000, 'Java')";
stmt.executeUpdate(sql);

To insert more queries at once, we can add them to a batch, and then execute the batch:

stmt.addBatch("insert into employee values(1201, 'Sam S', '29-10-2019', 25000, 'Java'");
stmt.addBatch("insert into employee values(1202, 'Micheal', '19-10-2010', 75000, 'Java'");
stmt.addBatch("insert into employee values(1203, 'Maria', '29-03-2014', 35000, 'Java'");
stmt.addBatch("insert into employee values(1204, 'Joe', '09-10-2015', 30000, 'Java'");

stmt.executeBatch();
stmt.close();

Using PreparedStatement

The process is the same, but we can prepare the query using setter methods. Suppose we are getting all the values from an object emp of the class Employee.

sql = "insert into employee values(?, ?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, emp.getId());
pstmt.setString(2, emp.getName());
pstmt.setDate(3, emp.getDate());
pstmt.setFloat(4, emp.getSalary());
pstmt.setString(5, emp.getSkill());

pstmt.executeUpdate();

We can perform batch operations using PreparedStatement as well.

pstmt.setInt(1, emp1.getId());
pstmt.setString(2, emp1.getName());
pstmt.setDate(3, emp1.getDate());
pstmt.setFloat(4, emp1.getSalary());
pstmt.setString(5, emp1.getSkill());
pstmt.addBatch();
pstmt.setInt(1, emp2.getId());
pstmt.setString(2, emp2.getName());
pstmt.setDate(3, emp2.getDate());
pstmt.setFloat(4, emp2.getSalary());
pstmt.setString(5, emp2.getSkill());
pstmt.addBatch();
pstmt.executeBatch();
pstmt.close();

Summary

We have seen that there are different ways to perform a JDBC insert, using Statement and PreparedStatement. To follow this article, you should know the basics of SQL. It is also important to handle SQLException so that the program terminates gracefully in case of any failure.

People are also reading: 

Leave a Reply

Your email address will not be published. Required fields are marked *