Table join in DAO and delegating ResultSet reading

Question

I'm wondering if (and how) this idea could come to bite me in the future.

I was thinking that I should take away the responsability from any given DAO to know how to load a different object from a joined resultset, besides from not needing to know the names used in the database itself.

(Here is a very simplified - working - )

Example

Let's say I have Cards and Clients. Not really important but, each client will only be able to use one card at a time.

The join example is in CardDAO -> getCardList() method.

Tables

Card Table Data

+---------+----------------+
| card_id | card_client_id |
+---------+----------------+
|       1 | 1              | // Card 1 used by client 1
|       2 | NULL           | // Card 2 available
+---------+----------------+

Client Table Data

+-----------+-------------+
| client_id | client_name |
+-----------+-------------+
|         1 | Client X    |
|         2 | Client Y   |
+-----------+-------------+

Codes

Simplified and reduced (Exceptions, Connections, closes(), etc). Also, Card and Client classes are just POJOs, like so:

public class Client {
    private int id;
    private String name;
    // Getters & setters
}

public class Card {
    private int id;
    private Client client;
    // Getters & setters
}

ClientDAO

public class ClientDAO{
    public static final String table = "tb_client";
    // Column names as defined in the table
    public static final String id = "client_id";
    public static final String name = "client_name";

    // Other methods    

    public static Client loadClient(ResultSet rs){
        Client client = new Client();
        client.setId(rs.getInt(ClientDAO.id));
        client.setName(rs.getString(ClientDAO.name));
        return client;
    }
}

CardDAO

public class CardDAO{
    public static final String table = "tb_card";
    // Column names as defined in the table
    public static final String id = "card_id";
    public static final String client = "card_client_id";

    // Other methods    

    public List<Card> getCardList(){
        List<Card> cards = new ArrayList<Card>();
        // Sql could be just concat too
        String sql = String.format("SELECT ca.*, cl.* FROM %s AS ca INNER JOIN %s AS cl ON ca.%s = cl.%s WHERE ca.%s is not null",
                CardDAO.table,
                ClientDAO.table,
                CardDAO.id,
                ClientDAO.id,
                CardDAO.client);

        PreparedStatement pstn = this.con.prepareStatement(sql);
        ResultSet rs = pstn.executeQuery();
        while (rs.next()) {
            cards.add(CardDAO.loadCard(rs));
        }
        return cards;
    }

    public static Card loadCard(ResultSet rs){
        Card card = new Card();
        card.setId(rs.getInt(CardDAO.id)); // Don't know the column name, it doesn't matter here
        // CardDAO doesn't need to know how to load Client, just 'SELECT' everything and delegate a loader
        card.setClient( ClientDAO.loadClient(rs) );
        return card;
    }
}

On a side note, this would be the generated query:

SELECT ca.*, cl.* FROM tb_card AS ca INNER JOIN tb_client AS cl ON ca.card_id = cl.client_id WHERE ca.card_client_id is not null

---

Some things I already thought:

• Increased complexity when writing queries;
• Need to always load all the columns from a given table to make it possible for the loadXClass method to be called (can't select specific fields, only everything);
• What if there are more levels to it? Example: the client has an address that is linked to an Address table, so I'd have to include it in the query and join every time so the code doesn't fail, even when not using it.

What are your thoughts?

Answer 1

I was thinking that I should take away the responsability from any given DAO to know how to load a different object from a joined resultset

Well, I would have made a view which does the selection and the dao would then do the mapping, or rather the disassembly and re-assembly of the resultSet (get all Cards, extract Clients, set the Client to the corresponding Card). So at least it's only one select.

Or you could introduce a new type in a layer 'above' both dao's, which does the mapping.

To your thoughts:

Increased complexity when writing queries

Yeaaah, that's why people tend to use existing ormappers ;) - you might want to introduce some static methods in your base dao, e.g. all() and join(String table, String condition), so the writing is more fluent and it's better to read... you might want to check the Criterion API/impl of hibernate, it's quite nice to write and read, but that's quite the work, if you really want to implement your own ormapper with a certain amount of sql features.

And: Most important: It's your job to make a class or a method readable. In test cases, I even write a static tomorrow() method, which creates a new Date and adds a day. Of course, you have to write a lot in java, but that must not be the excuse, to make your code not readable.

Need to always load all the columns from a given table to make it possible for the loadXClass method to be called (can't select specific fields, only everything);

I really think the 'only select data you need' paradigma is quite out dated. The management of the 'choose what columns' thingy, the 'be careful because its not null in the backend but someone might not have loaded that column' and the inconvenient increase of execution plans on your backend - which has to be managed, too - is in my opinion much more expensive compared to just select all (= upgrade the switch or add another cpu to the backend server)

What if there are more levels to it? Example: the client has an address that is linked to an Address table, so I'd have to include it in the query and join every time so the code doesn't fail, even when not using it.

... I think a view with a dao responsible for that case is the way to go ;-)

To be honest, in ormappers, I do not like the represent the complete erd in java objects approach. Maybe I just didn't get it, but sometimes it gets quite horrible. I mean, you have a Customer, the Customer has a List of Addresses, a List of Orders, a List of PhoneNumbers, a List of Items in the Shopping Card, a List of open bills and and and. And everything is present in the type of hell. And it gets worse, because all those dependencies have other dependencies, too. I found, that an app, at least above a certain size and complexity, is much more maintainable, if "those" problems are taken care of with a certain degree of isolation, even though that means, there is some redundancy, maybe a ClientAddress type and a ClientOrders type, instead of a "one size fits all" approach.

Hope that helps,...

Answer 2

Seems that your main concern is with the sql queries and you are worried about how much code you would have to change whenever a database table changes. etc, etc

I recommend you try Jooq.

Jooq lets you define your database in sql files, then it will generate classes that represent your tables. It also provides Utility classes for doing any kind of complex query you can imagine. Selecting from multiple tables? Jooq has got you. Not to mention it also gives you type safety due to being built with Java.

For example, lets redefine what you have done using the Jooq way.

SetupDatabase.sql

CREATE TABLE card_table (
    card_id             int PRIMARY KEY,
    card_client_id      int
    FOREIGN KEY(card_client_id) REFERENCES client_table(client_id)
);

CREATE TABLE client_table (
    client_id           int PRIMARY KEY,
    client_name         varchar(100) NOT NULL
);

Client.java

public class Client {
    public final int id;
    public final String name;

    public Client(int id, String name) {
        this.id = id;
        this.name = name;
    }
}

Card.java

public class Card {
    public final int id;
    public final Client client;
    
    public Card(int id, Client client) {
        this.id = id;
        this.client = client;
    }
}

ClientDAO.java

public class ClientDAO {
    public static final generated.tables.ClientTable clientTable = generated.tables.ClientTable.CLIENT_TABLE;
   
    // Other methods    
    public static Client loadClient(ClientTableRecord clientRecord){
        Client client = new Client(clientRecord.getId(), clientRecord.getName());
        return client;
    }
}

CardDAO

public class CardDAO {
    public static final generated.tables.CardTable cardTable = generated.tables.CardTable.CARD_TABLE;
    public static final generated.tables.ClientTable clientTable = generated.tables.ClientTable.CLIENT_TABLE;

    // Other methods    

    public List<Card> getCardList() {
        return org.jooq.DSLContext.transactionResult(config -> {
            org.jooq.DSLContext getter = org.jooq.impl.DSL.using(config);

            return getter.select(cardTable.ID, cardTable.CLIENT_ID, clientTable.CLIENT_NAME)
                .from(cardTable.join(clientTable)
                    .on(cardTable.CARD_CLIENT_ID.eq(clientTable.CLIENT_ID)))
                .where() // Place conditions here
                .fetchStream()
                .map(CardDAO::loadCard)
                .collect(Collectors.toList());
        });
    }

    public static Card loadCard(org.jooq.Record3<Integer, Integer, String> record) {
        Card card = new Card(record.value1(), ClientDAO.loadClient(new ClientTableRecord(record.value2(), record.value3())));
        return card;
    }
}

With this setup, all your major concerns have been resolved.

• No need to write queries, Jooq generates these for you. You can also get the query generated, as a string, by using the getSQL() method on any Query object.
• No need to select all columns at once, you can specify which ones you want.
• More tables can be joined to each other using the join, on, etc (there is a looott more). No need to fear the addition of new tables.

In addition, you gain the following:

1. Type safety

   The types of each table is known and each table record has getters and setters for each column, with well defined types

2. Whenever a database table changes, you can generate the corresponding java classes with jooq and jooq will automatically convert the column data types to a suitable one in Java. Now you no longer have to guess what type a varchar might be, or what a bigint, or datetime; Jooq will generate them and you just fix the parts of your code that use that field and not have to worry about the rest.

3. Separation of concerns.

   You are no longer writing sql in strings in java and sending those to prepared statements. Jooq handles all that for you

4. Java 8 support

   You may have noticed the lambda functions I used. Jooq supports java 8 out of the box. You should do the same if you aren't already. Java 9 was just out with even more helpful features.

I may sound like a JOOQ fanboy at this point, but don't let that deter you. Just checkout the download page and try it for yourself. Initial setup may require some technical knowledge of your choosen build tool (maven, gradle, etc), but the end product is an sql generating beast which offers type safety, so I say it's worth it.