Categories
Java SQL

Real life useful example of an Inner Join with Java JDBC and PostgreSQL

In this tutorial we will run a full demo of a real life useful Inner Join, with Java JDBC and PostgreSQL. Let’s consider a recurring business that has Customers, Products to sell to customers and Subscriptions that give the list of products each customer has subscribed to. What we want to do is store all this data in a database and given a client name, find the list of product names he has subscribed to.

1) The SQL tables

We need 3 tables: customer, product and subscription. The SQL code to create these 3 tables is:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE client (ID UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name VARCHAR(50) NOT NULL);

CREATE TABLE product (ID UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name VARCHAR(50) NOT NULL);

CREATE TABLE subscription (ID UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
                           client_id UUID NOT NULL,
                           product_id UUID NOT NULL,
                           name VARCHAR(50) NOT NULL);

2) PostgreSQL join function

Now that we have our tables and schema well defined, let’s write the SQL code used for the join:

CREATE OR REPLACE FUNCTION listProducts(clientName text)
returns TABLE (product_name varchar) as $$
declare clientId uuid;
BEGIN
  select id into clientId from client where name=clientName;
  RETURN QUERY SELECT product.name FROM subscription INNER JOIN product ON (subscription.client_id=clientId and subscription.product_id=product.id);
END;
$$ LANGUAGE plpgsql;

We create a PostgreSQL function listProducts, which takes a single argument called clientName:

  • First it does a select operation to find the id of the client whose name is clientName, from the table client. It stores the id in the variable clientId.
  • Then it does the actual join on tables subscription and product, using the following join clause: (subscription.client_id=clientId and subscription.product_id=product.id).
  • Finally, from the join result, we only select the the column product.name

3) Run the PostgreSQL database and create the tables

To run the PostgreSQL database, we use Java Testcontainers. Find a simple full tutorial on this framework here. The code is:

String username = "user";
String password = "password";
PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:11.1").withUsername(username).withPassword(password);
postgreSQLContainer.start();
Class.forName("org.postgresql.Driver");

Connection c = DriverManager.getConnection(postgreSQLContainer.getJdbcUrl(), username, password);

createTables(c);

where the function createTables is:

static void createTables(Connection c) throws SQLException, IOException {
    String sql = resource("tables.sql");
    Statement stmt = c.createStatement();
    stmt.executeUpdate(sql);
}
    
static String resource(String path) throws IOException {
    InputStream is = SQLUtil.class.getClassLoader().getResourceAsStream(path);
    return new String(is.readAllBytes(), StandardCharsets.UTF_8);
}

(“tables.sql” is the path to the SQL code from step 1))

4) Java representation of SQL tables

To run the above code in Java using JDBC, we first need to map the SQL tables as Java POJOs:

@NoArgsConstructor
@AllArgsConstructor
@Data
public static class Client {
    UUID id;
    String name;
}

@NoArgsConstructor
@AllArgsConstructor
@Data
public static class Product {
    UUID id;
    String name;
}

@NoArgsConstructor
@AllArgsConstructor
@Data
public static class Subscription {
    UUID id;
    String name;
    UUID clientId;
    UUID productId;
}

@NoArgsConstructor, @AllArgsConstructor and @Data are annotations from the Lombok project. They add constructors, getters and setters to the Java POJOs, 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.

5) Fill the SQL tables with test data

Let’s fill the tables with dummy data:

for (int i = 0; i < 10; i++) {
    String name = "c" + i;
    Client client = new Client(null, name);
    insert(client, c);
}

for (int j = 0; j < 10; j++) {
    String productName = "p" + j;
    Product p = new Product(null, productName);
    insert(p, c);
}

List<Client> clients = get(Client.class, c);
List<Product> products = get(Product.class, c);

for (int i = 0; i < clients.size(); i++) {
    Client client = clients.get(i);
    for (int j = 0; j <= i; j++) {
        String name = "s" + i + "x" + j;

        Product product = products.get(j);
        Subscription s = new Subscription(null, name, client.id, product.id);
        insert(s, c);
    }
}

where the function get is:

static <T> List<T> get(Class<T> clazz, Connection c) throws SQLException {
    ResultSetHandler<List<T>> resultSetHandler = new BeanListHandler<>(clazz, new BasicRowProcessor(new GenerousBeanProcessor()));
    String tableName = clazz.getSimpleName();
    List<T> result = new QueryRunner().query(c, "SELECT * FROM " + tableName, resultSetHandler);
    return result;
}

(this code uses Apache DB Utils library. See a simple tutorial on how to insert a Java object into a SQL table here), and where the function insert is:

static void insert(Object pojo, Connection c) throws SQLException {
    Map<Object, Object> map = new BeanMap(pojo);
    List<String> keys = map.keySet().stream().map(Object::toString).filter(k -> !k.equalsIgnoreCase("class") && !k.equalsIgnoreCase("id")).collect(Collectors.toList());
    List<String> keysSnakeCase = keys.stream().map(InnerJoinBlog::camelToSnakeCase).collect(Collectors.toList());
    
    String keysJoined = String.join(",", keysSnakeCase);
    String placeholders = String.join(",", Collections.nCopies(keysSnakeCase.size(), "?"));
    String sql = "INSERT INTO " + pojo.getClass().getSimpleName() + " (" + keysJoined + ") values (" + placeholders + ")";
    Object[] params = keys.stream().map(map::get).toArray(Object[]::new);

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

static String camelToSnakeCase(String camel) {
    Matcher m = Pattern.compile("(?<=[a-z])[A-Z]").matcher(camel);
    return m.replaceAll(match -> "_" + match.group().toLowerCase());
}

6) Inner Join with Java JDBC

To run the join from step 2), we use the function productNames():

static List<ProductName> productNames(String clientName, Connection c) throws SQLException, IOException {
    Statement stmt = c.createStatement();
    String sql = resource("join.sql");
    stmt.executeUpdate(sql);

    String select = "select * from listProducts('" + clientName + "')";
    return query(ProductName.class, select, c);
}

where “join.sql” is the SQL code from step 2), where the ProductName class is:

@NoArgsConstructor
@AllArgsConstructor
@Data
public static class ProductName {
    String productName;
}

and the query function is:

static <T> List<T> query(Class<T> clazz, String sql, Connection c) throws SQLException {
     ResultSetHandler<List<T>> resultSetHandler = new BeanListHandler<>(clazz, new BasicRowProcessor(new GenerousBeanProcessor()));
     List<T> result = new QueryRunner().query(c, sql, resultSetHandler);

     return result;
}

7) Result

If you run the function productNamesPostgres() from previous step with clientName = c3, you will get:

[ProductName(productName=p0), ProductName(productName=p1), ProductName(productName=p2), ProductName(productName=p3)]

which is the expected result.

8) Full working Java code

That’s it for this tutorial ! If you have any question please leave reply below, we answer within 24 hours !

Leave a Reply

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