Dao complex logic sql

Question

i'm developping a small buissness application and i need to know how to pass complex logic to indicate what Clients to fetch from the database

Here is my ClientDAO class :

public class ClientDAO {

    public void save(Client clt) {

    }

    public Client find(int id) {

        return null;
    }

    public void update(Client clt) {

    }

    public void delete(Client clt) {

    }
}

This is a normal CRUD class, but if i need to fetch all client FROM date xx to yy, i need to add another find method overloaded?? and if i want to find all Client That have an age bettwen xx and yy i will another find function?? that don't seem a good design

i know what i'm doing is wrong, i want to know the right way to do it.

PS : i'm going to use JDBC without any ORM

Answer 1

When not using an ORM, creating multiple methods for querying the data is the right way to go. The purpose of the DAO is to totally isolate the rest of the application from the database access logic, meaning the DAO is the only class that knows the table and column names.

Now, for the advanced topic: If the application will need to query the table using a variety of criterias, creating a new method for each combination would be cumbersome, and having too many parameters on a single method wouldn't be right either.

For this kind of problem, the builder pattern is a good solution. Your DAO could implement a filter() method that returns a builder object, with good criteria methods and a final execute() method:

public class ClientDAO {

    public static final class Filter {
        Filter() {
            // code not shown for brevity
        }
        public Filter withNameLike(String name) {
            // code not shown for brevity
        }
        public Filter createdAfter(Date fromDate) {
            // code not shown for brevity
        }
        public Filter createdBefore(Date fromDate) {
            // code not shown for brevity
        }
        public List<Client> query() {
            // code not shown for brevity
        }
    }

    public static Filter filter() {
        return new Filter();
    }

}

It can then be used like:

List<Client> clients = ClientDAO.filter()
                                .withNameLike("%John%")
                                .createdAfter(fromDate)
                                .query();