ORM Who can generate script dynamically

Refresh

April 2019

Views

226 time

1

So basically I was checking working of hibernate, as well evaluated ORMs JOOQ. JPA is main de-facto standard for all java based ORM implementations more or less.

I am not saying they are not perfect or good but my problem is like I have found many issues and hick-ups while using various options provided by these below are my issues:

  1. Hibernate:

    a. It now obsolated criteria API so now we have to follow JPA way, in this case I can't use multiselect() or Selection for few columns of one table where my bean pojo don't have specific constructor.

Like for eg. My Bean/POJO have almost 80 columns

at one place i need only 10 columns and another place I need 20 columns of the table to which my Bean/POJO is mapping. now in such case I have to create either 2 separate DTO containing selection fields or I must have to create two different overloaded constructors inside my main mapping Bean/POJO.

And this is as per JPA,there is no way by which using just getter settersof fields should be initialize of my Bean/POJO.

Idea behind this was to reduce no of classes, which we have to create unnecessarily.

Also if suppose I have such cases where from a table I have to fetch almost 25 different combination then In terms of JPA I have to create either 25 different constructors in one Bean/POJO or 25 diferent DTOs (WTH).

b. Hibernate fails when my constructor of Bean/Pojo is like below

MyBean(String name, String email)

while in selection if I will do

query.multiselect(new Selection[]{mappingBean.get("email"),mappingBean.get("name")});

It initializes in wrong order this is as per me type hell flaw. (Off course developer do have responsibility but still its wrong)

  1. The Search Criteria Pattern: Wherever we need the data we have to write method at immediate place or we have to write JPQL, HQL or whatever abstraction scripting just for sake of hiding SQL

now if I have on one table 25 different combinations of different scripts I have to write 25 different methods for those scripts, since the criteria APIs either Hibernate or JPS both have poor and complex script building system.

And In case JOOQ its all java is interpreted in script.(WTH)

Now There is no simple API or ORM which can satisfy below requirements I found If you can help it I will be great full to you.

  1. I don't want to use HQL JPQL of whatever abstraction language instead of SQL like just

I created few code it passed as an objects to a method MY API should be able to do this.

  1. If there are case of joins (I must Say not every data base is designed properly specially in case of unplanned changes sudden) in that case we have to have some flexibility for Joins Every time there is no possibility to maintain proper relations (because there are management kind of constraints who focuses on Delivery not on maintainable systems).

  2. If There are SQL I have to write I should be writing specific parts like part of where clause, or just selection parts

4.No one properly supports complex scripts like dynamic Unions intersections.

etc The list is all long but if you can help me with at least some suitable one can help me out...

Edit:

Now editing for JOOQ part

I have not explored internals of JOOQ it is also using AST ok but my issue is with the pattern it is following can check here

if I see what's wrong plain JDBC have done, if i have to get my data from data base in the form of Integer or String or individual data type as individual fields, this facility my JDBC is any way giving me I have to take care there in JDBC for type safety same I have to do it with JOOQ DSL. At least I expect a Bean/POJO in result like Hibernate Criteria API provides or JPA provides, its different point that they also have their lags. If JOOQ have I am not aware because as much as I have seen examples found SQL type codes in its tutorials.

Now let's come to another example I have (explored so far up to this you can help me to figure out few of my issues which you can get here with Hibernate I tried to raise with its community, check here and here (since you have thanked and have referenced theses API as inspirations),

For eg. a examples of JOOQ [![Example From JOOQ][1]][1]

Now if we observe, I have to write 1 sql or only one set of column selection and I end up using this, but imagine I have to use same table with different set of columns at N no of places then I have to write N no of scripts(here code is interchangeable JOOQ SCRIPT check Image what I mean, tyour SQL and code are looks imitating each other) of JOOQ DSL,

So I analyzed this in below points:

  1. If I have to write N no of different selections then either I will write a Class which will wrap all selections in N no of different methods and where ever I need I just have to call those methods at my business logic layer to fetch data(This is what i see flaw in HQL, JPQL, AND JOOQ DSL (As per Image)).

Here I will be creating chaos by creating N of methods because I have to figure out oh for 4 columns I have to use method X and for 5 columns I have to use method Y, this will furter worse when you are having team of 100 developers because every one may not be taking effort to find correct method instead he/she creating there self which will create redundant and duplicate codes.

As well I am sure you don't want to call script like DSL in your Business logic layer right because you just need what on basis of why and while "HOW & From WHERE" is task of ORM and its allies (here I am refering DAOs and Factory methods of Data fetching using DAO calling as allies or ORM).

  1. Now Another way is I will have separate set of criteria classes which are loosely coupled with my Bean/POJOs completely separated from ORM it will just follow some standard rules of ORM so it can utilize the search criteria for data look up at DB and return me a List of Beans/POJOs.

So here for any selection I just have to write one method for eg. we call it List findData(SearchCriteria sc) inside a factory Class (M calling it as factory because it will have method and object from DAOs and using DAOs it will provide List of Bean so it becomes a object generator class from Data base utilizing ORM).

now this method has responsibility on basis of standards to provide me desired output and in this case wherever in my business layer I need a specific set of data I can create search criteria and pass it to this method which will give me list of Beans/POJOs.

So by this I might be creating search criteria as per requirement at different part of business logic layer no matter N no of times to get data but at least I know for sure that responsibility to fetch data is only on one method of my factory class which is a kind of a separator (kind you take door for eg) between my business layer and my API of ORM, from factory matters will be handles by ORM not by user or I say developer itself.

Hope you got my point.

These things hibernate or JPA have solved a bit but not completely because their terminology is also same that wherever you need use criteria builder, criteria query inside your business layer which is not a good code structure.

Edit 2:

Lukas, I got your point I have below points

  1. Check below code of JOOQ:
public static ResultQuery<Record2<String, String>> actors(
    Function<Actor, Condition> where
) {
    return ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
              .from(ACTOR)
              .where(where.apply(ACTOR)));
}

Here ACTOR looks like actual table name and FIRST_NAME as it's column, so is there any standard under which we have to follow certain code practice or I have to design it my own

like A Bean/POJO/DTO for example UserBean.java can hold data fetched from DB as it is default mapping of Table USER and we can fetch data using using

ctx.select( *).from(UserBean.class)  //I m not sure how do * but will figgure out that separately

So in that case Ideally there has to be standard mapper for eg in above case UserDAO.java which will have fields in form of static final string containing exact column name of table & table Name.

this can be used by a factory to invoke at broader level, (Its just an idea of proper code structure),

so is there any practice is listed or pattern suggestion present by JOOQ? I just given a my interpretations.

  1. For point 2 I am not considering well designed data bases since mine is not among those well established relationship & constraints maintaining schema,

It has lot of tables which have common data containing columns which gives proper relation between two tables, but do not have any foreign key relation ship applied at db level.

Now in such cases in hibernate the Join becomes too difficult and this we have to deal with the help of a DTO containing cross table data mapping and HQL or plain SQL.

my idea is, if I have to join two entities each other which do not have ny proper relationship at db level is it possible, and does JOOQ have like below arrangements?

if There is UserBean and BankAccountBean where one user can have multiple bank account,

so for below script

Select A.USER_ID, A.USER_NAME, B.BANK_NAME, B.BANK_ACCT_TYPE, B.BANK_ACCT_NUMBER 
FROM USER A LEFT JOIN USER_BANK_ACCOUNT B On A.USER_ID = B.USER_ID

Now in this case ideally instead of creating a separate entire new Bean My idea is, that, there must be UserBankAcctJoinBean should be able to handle this data fetching (here dynamic selection must be considered)

Now if we talk about UserBankAcctJoinBean it should extend UserBean (since in my join the primary table is User) so by this by default it will inherit all the properties of Users and it should contain a reference variable of UserBankAccountBean so selection parts which belongs to USER_BANK_ACCOUNT should be initialized on this reference via reflection

Here it is different than Hibernate since it does allow to have list object of secondary tables which is not the basic idea because there hibernate contains proper established relations between table in schema.

Does JOOQ have this or similar way?

  1. Also if JOOQ follows JPA pattern how it internally works in case of dynamic selections because as much as i have explored JPA in case of dynamic selections its specification says to initialize the object directly using specific constructor and this is the biggest challenge because if I want to use 4-5 different ways of multiple different columns then I have to use 4-5 different overloaded constructors or have to create 4-5 different Bean/POJO/DTO (This point is already there in my previous edits)

Also in case of hibernate source code it first loads data from ResultSet to Object[] and then it initializes the Bean/POJO/DTO using setter methods of fields, so here if you see the dual lag because instead of directly initializing object by invoking field's getter setter it first stores data to Object array then from that array it loads to bean which ends up creating per fetch row iteration two times.

This is really waste of resource because for same task iteration twice over same set of data is not a efficient way,

So how does JOOQ works internally? does it directly initializes data to DTO using getter setters or it also works similar fashion of Hibernate?

1 answers

3

I'll try to answer your jOOQ part only

Pre Edit 2

if I see what's wrong plain JDBC have done, if i have to get my data from data base in the form of Integer or String or individual data type as individual fields, this facility my JDBC is any way giving me I have to take care there in JDBC for type safety same I have to do it with JOOQ DSL. At least I expect a Bean/POJO in result like Hibernate Criteria API provides or JPA provides, its different point that they also have their lags. If JOOQ have I am not aware because as much as I have seen examples found SQL type codes in its tutorials.

jOOQ can project your tuples into arbitrary "Beans/POJOs". Just use the DefaultRecordMapper. An example:

class DTO {
  int a;
  int b;
  int c;
}

And then

List<DTO> dtos =
ctx.select(T.A, T.B)
   .from(T)
   .fetchInto(DTO.class);

If I have to write N no of different selections then either I will write a Class which will wrap all selections in N no of different methods and where ever I need I just have to call those methods at my business logic layer to fetch data(This is what i see flaw in HQL, JPQL, AND JOOQ DSL (As per Image)).

You didn't post any image, but from how I understand your question, in my above example, I've reused a DTO for fetching two columns instead of 3. There's no need to create one DTO per query. Of course, you could just work with the untyped Record as follows:

Result<Record> result =
ctx.select(T.A, T.B)
   .from(T)
   .fetch();

And then

for (Record record : result)
    System.out.println(record.get(T.A) + ":" + record.get(T.B));

Here I will be creating chaos by creating N of methods because I have to figure out oh for 4 columns I have to use method X and for 5 columns I have to use method Y, this will furter worse when you are having team of 100 developers because every one may not be taking effort to find correct method instead he/she creating there self which will create redundant and duplicate codes.

Just because many jOOQ examples use a quasi static SQL syntax for simplicity doesn't mean you can't write dynamic SQL. In fact, every jOOQ statement is a dynamic SQL statement. For example:

List<Field<?>> select = new ArrayList<>();
if (something)
    select.add(T.A);
if (somethingElse)
    select.add(T.B);
Result<?> result = ctx.select(select).from(T).fetch();

Further reading here: https://blog.jooq.org/2017/01/16/a-functional-programming-approach-to-dynamic-sql-with-jooq

So here for any selection I just have to write one method for eg. we call it List findData(SearchCriteria sc) inside a factory Class (M calling it as factory because it will have method and object from DAOs and using DAOs it will provide List of Bean so it becomes a object generator class from Data base utilizing ORM).

Are you maybe looking for Spring Data?

Hope you got my point.

Not really, but I hope you get mine :)

Post Edit 2

So, you've added a ton of additional questions. Here are my answers:

Here ACTOR looks like actual table name and FIRST_NAME as it's column, so is there any standard under which we have to follow certain code practice or I have to design it my own

jOOQ lets you work with what it provides out of the box, or you can spend days customising jOOQ for your own style. If you don't know the answer here, just use jOOQ's defaults. They're well chosen.

//I m not sure how do * but will figgure out that separately

You can either leave the select() list empty, or call selectFrom(), or use DSL.asterisk()

so is there any practice is listed or pattern suggestion present by JOOQ?

Again, jOOQ doesn't judge you for your own stylistic taste, it lets you do what you want with it, without getting into the way. But jOOQ has "obvious" defaults

Now in this case ideally instead of creating a separate entire new Bean My idea is, that, there must be UserBankAcctJoinBean should be able to handle this data fetching (here dynamic selection must be considered)

If you think that's a really good and scalable idea, then do it. You can write your own beans. Or just work with jOOQ's records, which are not unlike tuples.

Also if JOOQ follows JPA pattern

I don't know what that means or why you think jOOQ does that.

because if I want to use 4-5 different ways of multiple different columns then I have to use 4-5 different overloaded constructors or have to create 4-5 different Bean/POJO/DTO (This point is already there in my previous edits)

If you worry about this, then don't use immutable POJOs, use mutable ones with JavaBeans style setters and getters, and jOOQ will only call the ones that had a matching column present in your result set.