Discussions

News: JDO Community Process: JDO 2 Queries - Part 2

  1. JDO Community Process: JDO 2 Queries - Part 2 (34 messages)

    JDO 2 has overhauled the query support. JDO-QL has been greatly enhanced (projections, aggregates, single string format, and more), and native SQL support is available. This is the second article in the series, and covers projection, aggregation and the grouping ad de-duplication (making distinct) of query results.

    Read JDO Community Process: JDO 2 Queries - Part 2

    Threaded Messages (34)

  2. Obligatory plug: Kodo JDO (which powers TSS) supports all the JDO 2 features Robin presents here, as well as just about all the features from part 1 of his series, and from the upcoming parts 3 and 4.
  3. Versant Open Access JDO[ Go to top ]

    Obligatory plug 2: Versant Open Access JDO (was JDO Genie) supports most JDO 2 JDOQL features.

    We also have a Versant Open Access .NET product.

    Cheers
    David
  4. I hope Part IV will be most interesting.
    Power of coupling between my code and the database schema must be a good thing.
  5. Hi Juozas

    JDO provides persistence for Java objects. As such the first version of JDOQL facilitated queries which returned Java objects. For the most part this is an appropriate level of abstraction.

    However the Java community has been asking for aggregation (aggregate functions) and projection (retrieval of field-level data instead of just objects). In this regard I think many people will find Part II very interesting.

    My personal favourite feature of JDOQL in JDO 2.0 is the single-string form of queries, which is showcased in Part III.

    I used to think that native SQL queries would be critical in specific circumstances, such as with sub-selects, but Kodo JDO now supports subselects in JDOQL.

    With sub-selects and projection accessible through JDOQL the incidence of queries which _necessitate_ direct SQL access should be reduced, but we provide native SQL support to give users confidence that they can go directly to the database if they find they have to.

    Add to the mix our support for named queries, which can be written in JDOQL and later translated into SQL if required (and vice versa) with no application impact, and JDO's query capabilities have matured to be very comprehensive.

    Kind regards, Robin.
  6. Yes, I see it, JDOQL becomes more usefull and it can become one of SQL dialects if you will try to make it more usable. But it must be more consistent to use plain SQL for O/R mapping, I do not think it is possible to avoid it anyway (and there is no technical reason to avoid it).
  7. Yes, I see it, JDOQL becomes more usefull and it can become one of SQL dialects if you will try to make it more usable. But it must be more consistent to use plain SQL for O/R mapping, I do not think it is possible to avoid it anyway (and there is no technical reason to avoid it).

    The disadvantage of using plain SQL is portability. SQL can very very widely between dialects: The functions that are available and the syntax for various features can be quite different. JDOQL hides this, yet good JDO implementations will generate high-performance SQL for the underlying database.
  8. The disadvantage of using plain SQL is portability. SQL can very very widely between dialects: The functions that are available and the syntax for various features can be quite different.

    I hope JDO vendors do not extend JDOQL and do not pollute standard like RDBMS vendors do.
    BTW some databases can be configured to reject non standard queries.
  9. Juozas,
    and there is no technical reason to avoid [plain SQL for O/R mapping]

    Applications which exploit developer-provided native SQL access over JDOQL suffer from three specific limitations:

    1. The application is dependent upon the specific schema elements (tables, columns etc) which are referenced in the SQL.

    2. Depending on the SQL dialect which is used, the application may be dependent upon a particular relational database product / version.

    3. The application is no longer portable to non-relational datastores.

    Additionally, in many cases JDOQL queries are simpler to write than corresponding SQL queries, so developer productivity may be affected.

    Kind regards, Robin.
  10. Just parse SQL in the same way as you do with JDOQL, how it is different ? Paths look better than joins in query, but mapping needs this noise anyway and this mapping is static (it needs all possible joins in model and in mapping). Client side
    functions do not make stuff better too, but it is possible to add some escapes for client side query preprocessing like ODBC does ( {call ? = myFunction(?) } ). You can define some of standard SQL subset as "portable" query language (JDBC does it too).
    The same is about "non-relational persistent stores", SQL can query any database (I hope exotic databases support SQL for compatibility with standard tools anyway ).

    It is nice to have many standard query languages, but this suff looks very experimental and probably it is better to reinvent new standard query language at home to abstract JAVA persistence technologies than to use low level JDOQL.
    I think SQL is the best candidate for this kind of language.
  11. it is better to reinvent new standard query language at home to abstract JAVA persistence technologies than to use low level JDOQL.I think SQL is the best candidate for this kind of language.

    The problem is... there is no such thing as SQL! There is SQL/Server SQL, PostgreSQL SQL, MySQL SQL, Oracle SQL and so on and they all have differences. JDOQL is not low level - it operates at about the same level as these SQL versions. JDOQL is the abstraction of the persistence that you are wanting.
  12. The problem is... there is no such thing as SQL!
    There is SQL/Server SQL, PostgreSQL SQL, MySQL SQL, Oracle SQL and so on and they all have differences.
     JDOQL is not low level - it operates at about the same level as these SQL versions. JDOQL is the abstraction of the persistence that you are wanting.

    JDO vendors do the same with JDOQL and I see nothing wrong if vendor tries to improve technology and to contribute ideas for standard.
    As more you improve JDOQL as more it looks like SQL ("item having max(bidAmount) > 10.00")
    The end of this experiment is very clear, you will reinvent new SQL dialect too.
  13. The problem is... there is no such thing as SQL! There is SQL/Server SQL, PostgreSQL SQL, MySQL SQL, Oracle SQL and so on and they all have differences.  JDOQL is not low level - it operates at about the same level as these SQL versions. JDOQL is the abstraction of the persistence that you are wanting.
    JDO vendors do the same with JDOQL

    No, they don't. Every JDO vendor understands the same core JDOQL syntax and methods. Some vendors might introduce new JDOQL methods, but you can choose not to use them. By contrast, relational DB vendors each have their own syntactic idiosyncrasies that are usually impossible to avoid. Your assertions that it's possible to write completely portable SQL simply aren't true. Oracle 8 doesn't understand sql92 joins. SQLServer and DB2 both have their own unique locking syntax. Access, SQLServer, DB2, Firebird, Interbase, JDatastore, Oracle, and Pointbase all have various different ways of taking a substring. SQLServer, DB2, HSQL, Interbase, Oracle, JDatastore, and Postgres have various different ways of finding the index of one string within another. DB2, SQLServer, Oracle, Postgres, MySQL, and HSQL all have different ways of getting a certain range of results. Do I need to go on? And that's just SQL portability; it doesn't even go into JDBC driver capabilities like batching that vary from driver to driver.
    As more you improve JDOQL as more it looks like SQL ("item having max(bidAmount) > 10.00")The end of this experiment is very clear, you will reinvent new SQL dialect too.

    JDOQL uses tokens like "select" and "having" because they are familiar and they make sense. Aside from that, JDOQL uses Java syntax, not SQL syntax. It is no more a dialect of SQL than Java boolean expressions are. Also, JDOQL strings are often less than 1/5th the size of the resulting SQL, are much easier to grok, and don't require you to know every query in advance so you can design good DB views. No, making JDOQL like SQL would be a huge step backwards.
  14. Yes, SQL standard has a few levels and versions, any technology starts from experiments before to mature, the same is about JDO. Do not ignore and reinvent standards please, all "experts" like to invent alternatyve standards, but it is as silly as to reinvent UNIX.
  15. but it is as silly as to reinvent UNIX.

    I'll be sure to let Linus Torvalds know.
    hehehe. Just kidding!

    -geoff
  16. Yes, SQL standard has a few levels and versions, any technology starts from experiments before to mature, the same is about JDO. Do not ignore and reinvent standards please, all "experts" like to invent alternatyve standards, but it is as silly as to reinvent UNIX.

    First of all, the problem is not that SQL has different versions of standards, but that no one enforces the standards at all, so most DBs don't care about them. I already demonstrated that in my previous post; I can list more examples of core incompatibilities between databases if you want them. The situation with JDOQL is completely different, because the standard is strictly enforced. A JDOQL query works across all JDO implementations, and a JDO 1.x query will continue to work unaltered in JDO 2.

    Second, ORM query languages are not "reinventing" SQL, because SQL is not an object query language. It wasn't designed to query for objects, and it is not good at it. You continue to ignore this fact. You could complain that JDOQL (and HQL, and EJBQL) reinvent OQL, since that was a standard object query language, but to say it reinvents SQL demonstrates a lack of understanding of the purpose of SQL vs. the purpose of ORM query languages.

    I have respect for the view that ORM in general is not needed. I don't agree with that view, but one can make a coherent argument for it. However, once you accept ORM, you must recognize that SQL is a terrible language for object queries. Jouzas, I have yet to see you make a single technical argument as to why SQL is better than JDOQL (or HQL, or EJBQL) for querying objects. I see you post the same "just use SQL" assertions in every thread about any ORM technology; I'd be interested in hearing your technical arguments behind this assertion. Please address the points that have already been made in this thread:
    1. JDOQL (and other object query languages) is much more concise than the equivalent SQL for complex models... sometimes 5 times as concise or more.
    2. JDOQL only requires knowledge of the object model, while using SQL for object queries requires detailed knowledge of both the schema and the object model and the ability to mentally move between them.
    3. JDOQL continues to work when you change the schema, SQL does not.
    4. JDOQL is portable across databases and JDO vendors, SQL is not portable in any sense.

    Thanks.
  17. I think SQL is a great query language "for objects", and HQL proves the point (its SQL with different identifiers for types). It would help if you could just define "object" in your postings, Abe. Are you talking about variables, values, or types?
  18. I think SQL is a great query language "for objects", and HQL proves the point (its SQL with different identifiers for types).

    HQL takes its structure and functions from SQL, but it works at the object level. You can traverse Java relations in HQL, rather than making joins between tables. You query on fields in HQL, not columns. When I access field Cat.name in an HQL query, I don't have to worry that "name" is a property of the base class Animal, which is in a different table than the properties of the subclass Cat. (At least I think that's the case; I'm not an HQL expert, so please correct me if I'm wrong.) HQL has the same 4 advantages over SQL that I listed for JDOQL in my post above.

    I don't object to SQL as an object query language because it isn't modeled after Java as JDOQL is; I object to it because it doesn't work at the object level. Christian, I hope that answers your question. I think we agree in general (otherwise Hibernate would just use SQL and would have no need for HQL).
  19. HQL has automatic inner joins with a dot notation, on the mapped foreign key constraint - a very easy optimization we could make, so we did. I think SQL should have the same (optional ON clause). However, I'd like to see a real data type system first, that's still missing after 20 years.

    You use field names (well, variable identifiers of a Java type, like I said) instead of column names in HQL. Both have the same semantics, in two different systems. I can go into more detail here if needed.

    What is left is polymorphism, and this is surely something that has to be added to some definition of SQL to make it an "object" query language (some implicit polymorphic behavior mixed with casting operators).

    If, after adding the "4 advantages" you also have found (did I have four here or should I look for another trivial thing? Binary and ternary logic converter?) to SQL, we can make it an "object" query language, I'd prefer it any day over a new invention (someone just published the first article about "a single string format data query language that supports projection and aggregation"). Even if you are close to get the same (and even make it better with some "advantages" on top), why not admit that your stuff is based on SQL, plus some.

    This is frankly a request to just let us use what we have, before we go and invent new things. Please improve SQL and everything underneath. Thank you.
  20. HQL has automatic inner joins with a dot notation, on the mapped foreign key constraint ... You use field names (well, variable identifiers of a Java type, like I said) instead of column names in HQL ... What is left is polymorphism, and this is surely something that has to be added to some definition of SQL to make it an "object" query language

    So you're basically saying "if we add object-level concepts to SQL, then it becomes an object query language (like HQL)". That's self-evident. But my point is that SQL doesn't have those features right now.

    Whether an object query language should use SQL-like operators or Java-like operators is an entirely different debate. Surely for a solution like JDO that is not tied to a relational store, Java-like operators make more sense. For ORM solutions in general, I think it's just a matter of opinion; each approach has merits.
  21. So you're basically saying "if we add object-level concepts to SQL, then it becomes an object query language (like HQL)". That's self-evident. But my point is that SQL doesn't have those features right now.

    Well, yes, but my point is that it is a very trivial extension that does probably not need reinvention. You make it sound so difficult by using the words "object-level concepts" and "complex". I still have problems figuring out your definition for both (the first means "polymorphism" to me, the second word I usually don't use) and I think that some syntatical sugar and a handful of new functions is all we need in a SQL dialect for ORM.
  22. I think that some syntatical sugar and a handful of new functions is all we need in a SQL dialect for ORM.
    +1
  23. BTW "complex" join stuff looks like this in ODBC

    SELECT * FROM {T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C3}
           
    ( probably it is bit outdated problem )

    JDBC compiliant driver must support ODBC escapes, I see no problems to use this stuff for ORM too, certified driver must parse query string anway.
  24. 1. JDOQL (and other object query languages) is much more concise than the equivalent SQL for complex models... sometimes 5 times as concise or more.2. JDOQL only requires knowledge of the object model, while using SQL for object queries requires detailed knowledge of both the schema and the object model and the ability to mentally move between them.3. JDOQL continues to work when you change the schema, SQL does not.4. JDOQL is portable across databases and JDO vendors, SQL is not portable in any sense.Thanks.

    1. Views are used for complex models.

    2. JDQL needs both too, I need to map objects anyway. implementation can support property and class names in SQL too, It just missing JDO feature, not SQL flaw.

    3. RDBMS maintains dependences for views itself, SQL does not work if you do not make it to work, I see no problems to parse
    SQL on client. You parse JDOQL on client, do not you ?

    4. If you know bugs in drivers report it for vendors or implement adapter, you need it for JDO implementation anyway.

    BTW I do not want to talk about ORM technology in general then it is very long story, but I know some implementations with good SQL support, some JDO implementations have good SQL support too.
  25. Hi Abe,
    Agree SQL is not an object query language, and OQL's have their advantages in querying objects.
    One place where SQL scores is in generating reports. I am not talking complex reports. Trying to create even moderately complex reports using OQL can be a nightmare. I talk from experience and not hearsay. Our Chief architect insisted that all reports had use the same object(jdo) model for our reports. It required a whole lot of workarounds and 'patches' to the object model to get this done. In the end we ended up simply rewriting a whole lot of SQL functions (aggregates especially) and reducing the object model to a mess.
    As you say SQL has different versions but then how many EJB containers are really interoperable ? They may be j2ee compliant , but they all have different class loading mechanisms and a whole list of incompatibilities. Websphere didn't support jdk 1.4 for about 2+ years after jdk 1.4 was released! Any OQL implementation would suffer the same fate. WHile the vendors will implement the 'standard', they will add value by giving their extensions.
  26. One place where SQL scores is in generating reports.

    I agree. Though JDO 2 has introduced aggregate and grouping support to JDOQL, so SQL isn't quite as necessary as it once was. And many relational JDO implementations (like Kodo) have had aggregate support for a long time.

    I won't argue that JDOQL can do everything SQL can do; it can't (just as standard SQL can't do everything database-specific SQL can do). But what JDOQL can do, it does well IMO.
  27. Juozas,
    and there is no technical reason to avoid [plain SQL for O/R mapping]
    Applications which exploit developer-provided native SQL access over JDOQL suffer from three specific limitations:1. The application is dependent upon the specific schema elements (tables, columns etc) which are referenced in the SQL.2. Depending on the SQL dialect which is used, the application may be dependent upon a particular relational database product / version.3. The application is no longer portable to non-relational datastores. Additionally, in many cases JDOQL queries are simpler to write than corresponding SQL queries, so developer productivity may be affected.Kind regards, Robin.

    1) This does not matter if you use views

    2) This does not matter if you don't need multi-database support. Besides, if you use 100% pure standard SQL, it *will* be portable between databases. On top of that, using pure SQL, the SQL will be portable between different application environments (Java, C#, etc) so to that end, SQL is more portable.

    3) This does not matter if you don't plan on using non-relational databases.

    4) SQL is known by many and is well understood. This java-centric JDO thing is nothing but a small nitche standard understood by few. I would argue that to the average developer, SQL+JDBC is simpler.

    I don't understand why the Java croud is so fanatical about re-inventing the database on the middle tier (hibernate, toplink, jdo, etc.).

    I just don't get it.
  28. I just don't get it.

    I'm porting a site off of Cold Fusion. The exercise of moving it to Wicket and JDO has been pure pleasure. Every time I look at my code I am just blown away by how few lines of code I had to write.

    As a developer, those ostensibly altruistic soles that are intent on “saving” the rest of us always amuse me. To quote Bill Shakespeare, "the lady doth protest too much".

    It's amazing how for every JDO post, there seems to be some "character" intent on monopolizing the thread with talk of how JDO is not needed. In fact, developers do need JDO, and once they use it, they rarely want to go back to JDBC/SQL, let alone Entity Beans

    With the number of genuinely cumbersome Java technologies out there (Entity Beans and JSP to name a few), I'd sure love to see the critics turn their attention on those, rather than on JDO. JDO has been bootstrapped by the grassroots enthusiasm of a group of very persistent folks (no pun intended) who have brought a fantastic technology into the mainstream of Java without major corporate backing.

    -geoff
  29. It's amazing how for every JDO post, there seems to be some "character" intent on monopolizing the thread with talk of how JDO is not needed.

    I need JDO, It must be clear from my posts, but I need good JDO not experimental query language and SQL standard ignorance.
  30. BTW if it was attempt to show how SQL is ugly then you can write SQL queries this way:

    SELECT DISTINCT USERNAME
    FROM MY_USERS_VIEW

    It is more readable than JDOQL, is not it ?
  31. More readable than which specific JDOQL?
  32. "distinct winningBid.bidder.user.userName"

    It is not cleat that it returns without reading generated SQL, but it must look better using single string query.

    "select distinct winningBid.bidder.user.userName from AuctionItem"
    Is it something like this using single query string ?
  33. "distinct winningBid.bidder.user.userName"It is not cleat that it returns without reading generated SQL, but it must look better using single string query."select distinct winningBid.bidder.user.userName from AuctionItem"Is it something like this using single query string ?

    Yes, that is pretty much exactly what the single-string form would look like.
  34. BTW if it was attempt to show how SQL is ugly then you can write SQL queries this way:SELECT DISTINCT USERNAME FROM MY_USERS_VIEW It is more readable than JDOQL, is not it ?

    I don't find this significantly more readable than the JDOQL. Also, it is a very limited case: selecting a projection of a few values rather than objects, and on a pre-made view. Try comparing JDOQL that selects objects with a complex inheritance structure to the equivalent SQL without a pre-made view (as in a dynamic query), and JDOQL will be the clear winner every time. SQL is great at selecting relational data as such (though it has serious portability issues); it's pretty bad at selecting objects.
  35. Try comparing JDOQL that selects objects with a complex inheritance structure to the equivalent SQL without a pre-made view (as in a dynamic query), and JDOQL will be the clear winner every time. SQL is great at selecting relational data as such (though it has serious portability issues); it's pretty bad at selecting objects.

    We can try to compare it too, you can use XML files for mappings, I will use view :)

    I want to find motivation for JDOQL myself, but I can not find it. Looks like this experiment inherited by JDO from ODMG is just useless.