Generic Dao Iciql implementation

[decebals]

Hi

I use in my application a generic (CRUD) dao. The interface is simple/generic enough:

public interface GenericDao<T extends Identifiable<ID>, ID extends Serializable> {

    /**
     * Returns the {@link Identifiable} entity with the specified identifier.
     *
     * @param id
     * @return
     */
    public T findById(ID id);

    /**
     * Returns all the {@link Identifiable} entities.
     *
     * @return
     */
    public List<T> findAll();

    /**
     * Stores the {@link Identifiable} entity.
     *
     * @param entity
     * @return
     */
    public T save(T entity);

    /**
     * Removes the {@link Identifiable} entity.
     *
     * @param id
     */
    public boolean deleteById(ID id);

    /**
     * Returns the total number of entities.
     * @return
     */
    public long count();

}

public interface EntityDao<T extends Entity> extends GenericDao<T, Long> {
}

I am using with success two particular implementation InMemory and Csv for fast prototype and testing.

My intention is to add a Iciql implementation for the case when I use the application in prod.

I see from https://github.com/gitblit/iciql/blob/master/src/main/java/com/iciql/DaoProxy.java#L540 that in theory it is possible to achieve this.

Can you provide me some snippet about the best implementation for a IciqlGenericDao? Is it possible a signature like this for a custom Dao?

public interface CustomerDao extends EntityDao<Customer> {

    public Customer findByEmail(String email);

}

public class CsvCustomerDao extends IciqlEntityDao<Customer> implements CustomerDao {

    public Customer findByEmail(String email) {
        // annotation or an elegant/simple code
    }

}

[decebals]

I can implement the simple List<T> findAll() method.

I see this line in examples:

// select * from products
List<Product> allProducts = db.from(p).select();

but I don't know what is p and in my generic context (<T>) how can I create a such object.

[gitblit]

DAOs are (currently) interfaces with annotated methods.

public interface CustomerDao extends Dao {
    @SqlQuery("select * from customers where email = :email")
    public Customer findByEmail(String email);
}

Db db.open(url, username, password);
CustomerDao dao = db.open(DbCustomerDao.class);
Customer customer = dao.findByEmail("decebal@suiu.com");

On your other example...

Product productsTable = new Product();
List<Product> allProducts = db.from(productsTable).select();

Iciql is heavily reflection-driven. Reviewing the documentation @ http://iciql.com might be helpful.

[gitblit]

That example requires Java 8 with -parameters flag (sound familiar? ;) ). Java 7 alternative would be:

 @SqlQuery("select * from customers where email = :1")

OR

@SqlQuery("select * from customers where email = :email")
public Customer findByEmail(@BindBean("email") String email);

[gitblit]

The section on aliases might be helpful in understanding what p is in your question: http://iciql.com/usage.html#H16

[decebals]

• Is it ok this implementation?

public T findById(ID id) {
    T alias = getAlias();

    return (alias != null) ? db.from(alias).where(alias.getId()).is(id).selectFirst() : null;
}

because I received an error:

Exception in thread "main" com.iciql.IciqlException: unmapped field in statement!
SELECT id, name, fiscalCode FROM Company WHERE ? = ? 

• Other problem:

@Iciql.IQTable
public class Company extends Entity {

    @Iciql.IQColumn
    private String name;

    @Iciql.IQColumn
    private String fiscalCode; // CUI or CIF

    // getters and setters
}

public abstract class Entity implements Identifiable<Long> {

    @Iciql.IQColumn(primaryKey = true, autoIncrement = true)
    protected Long id;

    // getters and setters
}

with this code:

companyDao.save(new Company().setName("test").setFiscalCode("1"));
db.merge(entity);

give me an exception:

java.lang.IllegalStateException: No primary key columns defined for table class ro.fortsoft.matilda.domain.Company - no update possible

[gitblit]

public T findById(ID id) {
    return ... where(alias.getId()).is(id) ...
}

This would only work if you are using a custom TypeAdapter on the ID type and TBH it's not a use-case I've needed so I'm not sure if it will work. I always use ints, longs, or UUIDs for identifiers.

On your other problem you need to...

@Iciql.IQTable(inheritColumns = true)
public class Company extends Entity {

[gitblit]

Actually, the custom ID type should work just fine. It's basically the same as an UUID case which I currently handle with a custom TypeAdapter..

[decebals]

It's ok for now. I made some progress but now I have another problem.

I try to implement a findByExample. The implementation is ok in InMemory and Csv and I wish to preserve this method for Iciql.

Map<String, Method> getters = BeanUtils.findGetters(example);
Map<String, Object> exampleProperties = BeanUtils.getProperties(example, getters);

T alias = getAlias();
Query<T> query = getDb().from(alias);
for (Map.Entry<String, Object> property : exampleProperties.entrySet()) {
    String propertyName = property.getKey();
    Object propertyValue = property.getValue();
    // >> WHERE (companyId = 1 AND name = 'IBM')
//    query.where(propertyName, propertyValue); // << HERE
}
System.out.println("query = " + query.toSQL());

return query.select();

My problem is that I don't know how I create the where clause in Iciql. Can you help me?

[gitblit]

Best-match (I think) for your approach:

List<String> clauses = new ArrayList<>();
List<Object> parameters = new ArrayList<>();
for (Map.Entry<String, Object> property : exampleProperties.entrySet()) {
    String propertyName = property.getKey();
    clauses.add(propertyName + "=?");
    parameters.add(property.getValue());
}
String clause = Joiner.on(" AND ").join(clauses);
List<T> list = db.from(alias).where(clause, parameters).select();
return list;

[gitblit]

Actually that could be written more succinctly - but I think you get the general idea.

[gitblit]

Something like this is cleaner although the values() bit is still clunky but that's because Iciql does not accept a Collection.

Object [] parameters = exampleProperties.values().toArray();
String clause = exampleProperties.keySet().stream()
    .map(name -> name + "=?")
    .collect(Collectors.joining(" AND "));
return db.from(alias).where(clause, parameters).select();

[decebals]

Brilliant. I am almost ready :smile:

Maybe the last issue is the non generic dao methods (findByXYZ).

For example:

public interface CustomerDao extends EntityDao<Customer> {

    public Customer findByEmail(String email);

}

public class IciqlCustomerDao extends IciqlEntityDao<Customer> implements CustomerDao {

    public Customer findByEmail(String email) {
        // HERE annotation or an elegant/simple code
    }

}

My actual implementation is:

public class IciqlCustomerDao extends IciqlDao<Customer> implements CustomerDao {

    @Override
    public Customer findByEmail(String email) {
        Customer alias = getAlias();

        return getDb().from(alias).where(alias.getEmailAddress()).is(email).selectFirst();
   }

}

I don't know if I can use annotations (SqlStatement, ...) for a more simple, declarative approach. For now in Iciql a generic MyDao must extends Dao and for my purpose I want to extends EntityDao. I see in JDBI that is not a such constrain. If I want to use the default (Generic)Dao provided by iciql then fine, I will extends that interface but I'd rather not have this constraint. In this scenario my IciqlDao must be something like DaoProxy from iciql, maybe help me if actual DaoProxy is spited in two: BaseDaoProxy and CrudDaoProxy (or other better names) then my IciqlDao extends BaseDaoProxy.

I must recognize that the current workflow from iciql is straightforward and it's very simple to use this library in a web application developed with Pippo web framework but I wish to push the things ahead.

I implemented with success in my pippo based application the open session in view pattern using a generic dao and iciql.

The core class is:

public class DbUtils {

    private static final Logger log = LoggerFactory.getLogger(DbUtils.class);

//    private static final String url = "jdbc:h2:mem:matilda";
    private static final String url = "jdbc:h2:./data/matilda";
    private static final String username = "";
    private static final String password = "";

    private static DataSource dataSource;
    private static Db genericDb;
    private static Map<Request, Db> cache = new ConcurrentHashMap<>();

    static {
        dataSource = JdbcConnectionPool.create(url, username, password);
    }

    public static Db getDb() {
        Request request = getRequest();
        if (request == null) {
            if (genericDb == null) {
                log.debug("Create generic Db instance");
                genericDb = Db.open(dataSource);
            }

            return genericDb;
        }

        if (!cache.containsKey(request)) {
            log.debug("Create request Db instance");
            cache.put(request, Db.open(dataSource));
        }

        return cache.get(request);
    }

    public static void closeDb() {
        Request request = getRequest();
        if (request == null) {
            if (genericDb != null) {
                log.debug("Close generic Db instance");
                genericDb.close();
            }
        } else if (cache.containsKey(request)) {
            log.debug("Close request Db instance");
            cache.remove(request).close();
        }
    }

    private static Request getRequest() {
        // TODO fix this in pippo
//        return Request.get();
        RouteContext routeContext = RouteDispatcher.getRouteContext();

        return (routeContext != null) ? routeContext.getRequest() : null;
    }

}

and in my base iciql dao (this class is extended by all my dao classes):

public class IciqlDao<T extends Entity> extends IciqlEntityDao<T> {

    public IciqlDao() {
        this(DbUtils::getDb);
    }

    // I use this constructor in DaoTest class (use in memory h2)
    public IciqlDao(IciqlDbFactory dbFactory) {
        super(dbFactory);
    }

}

all I do in my PippoApplication class is to add an after filter that calls DbUtils.closeDb()

It is an experimental approach and the result (the generic-dao library with inmemory, csv, iciql implementations as modules, plus the real application) will be available as open source projects when I finished the work.

[gitblit]

For now in Iciql a generic MyDao must extends Dao and for my purpose I want to extends EntityDao.

The way the Iciql DAO feature is implemented you can not provide a Class you can only provide an Interface and your interface must extend Dao. Your class hierarchy is incompatible with the current DAO feature. I'm open to refactoring DaoProxy to be more useful but for now I think you are stuck with the DSL approach for findByEmail.

[decebals]

I pushed a first commit of my tiny generic dao project. The iciql implementation is here. Sure is not perfect but it's a start. I see that I have a problem with iciql repository. Maybe it's time to upload it on sonatype :smile:

[decebals]

Thanks for your support!

[gitblit]

Iciql will get to MavenCentral eventually. It's on my todo list, low on my list, but on the list nontheless.

I think the main problem you have is that you didn't specify the Iciql repo in the POM.