Generic JDBC Writes using Consumers

One might argue that this is the exact scenario that Hibernate was made for: a series of interconnected and related tables. Each subsequent table needs the primary key from the prior table. This might be the case when you have tables that decorate the one another.

In the following example, we will create a user object, which will be decorated by a web user object. The user object holds general information about the user (e.g. their name), while the web user object contains their login credentials.

We will start by spinning up a Postgres Docker container.

$ docker run --name oms -e POSTGRES_PASSWORD=D3faulTp455w0rD -d -p 5432:5432 postgres

Once the container is up, we can create the tables that we will use. We will use the Postgres command line tool to connect to the Postgres server embedded within the Docker container and create the tables.

$ docker run -it --rm --link oms:postgres postgres psql -h postgres -U postgres

Password for user postgres:

psql (11.1 (Debian 11.1-1.pgdg90+1))

Type "help" for help.

First, we shall create the users table.

postgres=# CREATE TABLE IF NOT EXISTS users (id serial NOT NULL, first_name VARCHAR(255), 

last_name VARCHAR(255));

Next, we will shall create the web_users table, which is the table that has the extended attributes.

postgres=# CREATE TABLE IF NOT EXISTS web_users (id serial NOT NULL, user_id INT, 

user_name VARCHAR(255), pass_word VARCHAR(255));

postgres=# \q

Now it is time to move onto the Java code. This first thing that we need is a Connection.

Connection getConnection() {
    Connection conn = null;
    String url = "jdbc:postgresql://localhost:5432/postgres?user=postgres" + 

                 "&password=D3faulTp455w0rD";

    try {
        conn = DriverManager.getConnection(url);
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return conn;
}

Next, we will define two Functional Interfaces. They will behave like Consumers, but as they are JDBC based, they will throw SQLExceptions.

@FunctionalInterface
interface PreparedStatementArgumentSetter {
    void setArgs(PreparedStatement ps) throws SQLException;
}

@FunctionalInterface
interface ResultSetExtractor {
    void extract(ResultSet rs) throws SQLException;
}

We will need Domain objects to use in the JDBC operations. We are going with minimalism and brevity in this example, rather than entirely production ready code.

class User {
    public int userId;
    public String firstName;
    public String lastName;

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                '}';
    }
}

class WebUser {
    public int webUserId;
    public int userId;
    public String userName;
    public String passWord;

    @Override
    public String toString() {
        return "WebUser{" +
                "webUserId=" + webUserId +
                ", userId=" + userId +
                ", userName='" + userName + '\'' +
                ", passWord='" + passWord + '\'' +
                '}';
    }
}

We will now combine these two concepts into a method that will consume the Functional Interfaces to perform the JDBC operations.

void writeEntry(String sql, PreparedStatementArgumentSetter setter, 

                            ResultSetExtractor extractor) {
    PreparedStatement stmt = null;
    try {
        stmt = getConnection().prepareStatement(sql);
        setter.setArgs(stmt);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            extractor.extract(rs);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

We will define two methods that use this method in order to create the User object and the connected WebUser object.

User createUser(final User user) {
    writeEntry("INSERT INTO users (first_name, last_name) VALUES (?,?) RETURNING id",
            ps -> {
                ps.setString(1, user.firstName);
                ps.setString(2, user.lastName);
            }, rs -> {
                user.userId = rs.getInt(1);
            });
    return user;
} 

 WebUser createWebUser(final User user, final String userName, final String passWord) {

    final WebUser webUser = new WebUser();

    writeEntry("INSERT INTO web_users (user_id, user_name, pass_word) VALUES (?,?,?)" +

               " RETURNING id",

            ps -> {
                ps.setInt(1, user.userId);
                ps.setString(2, userName);
                ps.setString(3, passWord);
            }, rs -> {
                webUser.webUserId = rs.getInt(1);
                webUser.userId = user.userId;
                webUser.userName = userName;
                webUser.passWord = passWord;
            });

    return webUser;
}

All that is left is to run this code.

void run() {
    var u = new User();
    u.firstName = "Nathan";
    u.lastName = "Crocker";

    createUser(u);
    System.out.println("created user: " + u);

    var w = createWebUser(u, "ncrocker", "D3faulTp455w0rD__again");
    System.out.println("created webUser: " + w);
}

created user: User{userId=1, firstName='Nathan', lastName='Crocker'}

created webUser: WebUser{webUserId=1, userId=1, userName='ncrocker', 

passWord='D3faulTp455w0rD__again'}

And now we clean things up:

$ docker ps

CONTAINER ID        IMAGE               COMMAND                  

e9dd1ef16476        postgres            "docker-entrypoint.s…" 

$ docker stop e9

$ docker rm $(docker ps -a -q)

e9dd1ef16476

$ docker rmi $(docker images -q)

Further details about the Postgres Docker image can be found here: https://hub.docker.com/_/postgres/