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/