Experimenting with more dynamic select queries on DAO objects

[Bill O'Neil]

Hi,

I have been messing around with some of the codegen on a side project and was hoping I could get some feedback.  Its still very early stage but I would like the best way to show an example.

Assume I have the following mysql schema.

CREATE TABLE `person` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `email` varchar(255) NOT NULL,

  `first_name` varchar(255) DEFAULT NULL,

  `last_name` varchar(255) DEFAULT NULL,

  `age` int NOT NULL,

  `ts_created` datetime NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `email_idx` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

My code currently adds something like this.

Person person = PersonDao.select()

    .firstName("bill")

    .lastName("O'Neil)

    .fetchOne()

This allows you to build queries using multiple columns directly from the DAO instead of just using fetchByFirstName(), fetchByLastName().  It also exposes methods which give you direct access to the table fields so you can do the following.

List<Person> people = PersonDao.select()

    .firstName(firstName -> firstName.startsWith("bi"))

    .lastName(lastName -> lastName.startsWith("O'Ne"))

    .age(age -> age.gt(25))

    .fetchAll()

Two questions.

1. Does anything like this already exist?

2. What would be the best way to show a full example for some feedback if it does not exist.

I built an example object that works with the above queries and I am almost finished with the code generation part which will auto generate the sql builder for each DaoObject.

Thanks, Bill

[Lukas Eder]

Hi Bill,

Thanks for your enquiry. This is the jooq-developer list, which was (long ago) used occasionally by jOOQ developers to decide on internal stuff, before we moved pretty much all discussion about internals over to GitHub, closer to the actual code. I'm moving this discussion over to the jooq-user list, as a lot more people are reading that one:

https://groups.google.com/d/forum/jooq-user

Your generated API looks indeed very useful, although, I personally wouldn't call the initiating method "select()", as that would probably confuse users into believing that only the FIRST_NAME and LAST_NAME columns would be selected - which is a perfectly sound use-case per se. How about calling it "fetchBy()" to be consistent with other generated DAO methods? Another option would be "where()"...

As far as the lambda expressions are concerned, that looks interesting, but in the end, why not just allow for:

PersonDao.where(

    PERSON.FIRST_NAME.startsWith("bi").and(

    PERSON.LAST_NAME.startsWith("O'Ne")).and(

    PERSON.AGE.gt(25))

).fetchAll();

If you're planning on exposing org.jooq.Field<?> API to DAO consumers anyway, why not also expose org.jooq.Condition API?

In any case, very interesting work. I'm sure we'll be able to factor out 1-2 feature requests from it!

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ Developer Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-develope...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.