Insert a Java object into a SQL table, with automatic mapping

In this tutorial, we will see a simple method to insert a Java object as a row into a SQL table. Thanks to Apache DB Utils and Apache Bean Utils, our mapping between Java JDBC and SQL will be automatic.

1) The Java object

Let’s consider the following Java class Customer:

@AllArgsConstructor
@Data
class Customer {
    String name;
    String email;
}

(@AllArgsConstructor and @Data are annotations from the Lombok project. They add constructors, getters and setters to the POJO Customer, so that we don’t have to write all this boilerplate code. But if you prefer, you can write the getters and setters by hand.)

2) The SQL table

Now let’s consider the following SQL table customer, for which we want to insert Java Customer objects:

CREATE TABLE customer (name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL);

3) The mapping function from Java to SQL

Let’s import theApache DB Utils and Apache Bean Utils libraries into our pom.xml:

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency>

<dependency>
    <groupId>commons-beanutils</groupId>
    <artifactId>commons-beanutils</artifactId>
    <version>1.9.4</version>
</dependency>

Then let’s build the function with signature:

void insert(Object pojo, java.sql.Connection conn)

This function takes a Java object called pojo and a JDBC connection called conn. The function inserts the pojo into the SQL table that has the same name as the pojo‘s Java class. The code is the following:

import org.apache.commons.beanutils.BeanMap;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanListHandler;

void insert(Object pojo, java.sql.Connection conn) throws SQLException {
    Map<Object, Object> map = new BeanMap(pojo);

    String tableName = pojo.getClass().getSimpleName();

    List<String> keys = new ArrayList<>();
    List<Object> paramsList = new ArrayList<>();
    for (Object kObj : map.keySet()) {
        String k = kObj.toString();

        if (k.equalsIgnoreCase("class"))
            continue;

        keys.add(camelToSnakeCase(k));
        paramsList.add(map.get(k));
    }

    String keysJoined = String.join(",", keys);
    String placeholders = String.join(",", Collections.nCopies(keys.size(), "?"));
    String sql = "INSERT INTO " + tableName + " (" + keysJoined + ") values (" + placeholders + ")";
    Object[] params = paramsList.toArray(new Object[0]);

    new QueryRunner().update(conn, sql, params);
}

Basically, this code converts the pojo into a Map<Object, Object> that represents the POJO fields names and associated values. Then we do the SQL insert by passing the map to Apache DB Utils.

4) Full demo

In a Java main, we will start a PostgreSQL database, create the SQL table customer, create a Java Customer object, and finally insert it into the table by calling the above insert function.

package com;

import lombok.*;
import org.testcontainers.containers.PostgreSQLContainer;

import java.sql.*;

public class Main {
    public static void main(String[] args) throws Exception {
        String databaseName = "database";
        String username = "user";
        String password = "password";

        PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:11.1").withDatabaseName(databaseName).withUsername(username).withPassword(password);
        postgreSQLContainer.start();

        Class.forName("org.postgresql.Driver");
        String url = postgreSQLContainer.getJdbcUrl();
        Connection conn = DriverManager.getConnection(url, username, password);

        // create the SQL Table customer
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("CREATE TABLE customer (name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL);");
        stmt.close();

        // create a Java Customer object
        Customer customer = new Customer("foo", "foo@xy.com");

        // insert the customer into the SQL table customer, using the insert function from above
        insert(customer, conn);
    }

    @AllArgsConstructor
    @Data
    public static class Customer {
        String name;
        String email;
    }
}

That’s it for this tutorial ! If you encounter any problem, please leave a reply below, we answer within 24 hours.