wood burning stoves 2.0*
The moose likes JDBC and the fly likes How to not reinvent the wheel - technologies/frameworks everywhere Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to not reinvent the wheel - technologies/frameworks everywhere" Watch "How to not reinvent the wheel - technologies/frameworks everywhere" New topic
Author

How to not reinvent the wheel - technologies/frameworks everywhere

Stephan Mueller
Ranch Hand

Joined: May 05, 2010
Posts: 50
Hello out there!
That's a rather generic issue so I hope I've chosen the correct forum in the end.

I'm about to write a DB-Query application.
The access is READ-ONLY and against already-existing schemas.

Let me explain the odds & ends:
A Client (provides the GUI for the queries) connects to a server/retrieves
a server-instance (JNDI/RMI) that provides a list of datapools.
A dataPool is configured using a plugin mechanism, where a plugin consists
of a xml-configuration, describing the db-host, the plugin/dataPool starter-class
and things like what will be presented to the user in the gui.
The "what the user will see" part consists of:
  • a query editor to write and execute plain, old SQL
  • Available pre-defined queries, with coresponding parameters
  • A list of Entities and their attributes, from which the user can drag/drop a sample table to define how the result list will look like and what the query constraints would be (this comes down to a QBE implementation)
    You may have seen this QBE approach in Paradox, Star/Openoffice (yes, Access is similar)


  • The crucial part: The list of entities is either generated from the database metadata,
    JDBC provides nice functionality for this, or provided by classes, delivered with the plugin, to
    provide the user with functional names/relations rather then the techy-named tables/columns.
    (the functional-to-technical translation may be done via config files or read from an existing repository
    like most CRMs bring with them).

    The helper classes from the plugin will know how to generate the SQL for a given Entity and their related Entities - if the user chose such things.

    Now the "reinvent the wheel" part. There are a lot of frameworks out there, in particular Hibernate that allow for abstract query (QBE) generation. I have no experience with this but from what I've read across the web it would be hard to form a QBE for:
    the relations: a(id), b(id), ab(aID, bID) and to retrieve "the 'a's that are linked to every 'b'" which would resolve to a query like

    or
    or (fancy stuff with HAVING)
    or ...
    You get the point.

    Now we need a question:
    Do you know of any QBE implementations that allow complex query generation
    (I've read that hibernate has an implementation of QBE and E-QBE but never worked with them, tbh, I've little experience with hibernate) ?

    Can you suggest frameworks for what I want to achieve?
    Is my intention clear at all, if not, what other information do you need?
    Is there something I should get straight in my thoughts about the problem ?

    Looking forward to your comments!

    Regards.


    1. Make it run - 2. Make it run correctly - 3. Make it pretty OR fast/small - 4. ??? - 5. Profit
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61309
        
      66

    "srm wasHere", please check your private messages for an important administrative matter.


    [Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
    Campbell Ritchie
    Sheriff

    Joined: Oct 13, 2005
    Posts: 39048
        
      23
    Damn! You've beaten me to the warning. Is your middle name Rob by any chance?
    Stephan Mueller
    Ranch Hand

    Joined: May 05, 2010
    Posts: 50
    Wow.
    Not really the comment I'd wished for but first things first, right.
    Fixed and ready to go.
    Campbell Ritchie
    Sheriff

    Joined: Oct 13, 2005
    Posts: 39048
        
      23
    Moving to our databases forum, where you will be more likely to get a useful answer.
    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2501
        
        8

    I don't know any library that has these capabilities.

    Oracle has a product called Discoverer, that performs most of the capabilities you are describing (minus data pool / jndi)
    It is a business layer above database tables/views.
    The layer exists of objects with attributes.The links between these objects are defined in the layer.

    When creating a report(this is done by selecting objects and attributes, and setting conditions), Discoverer cunningly builds an sql statement against the underlying database tables / views.

    Not trivial! Difficult queries! Lots of grouping on lots of levels! Beware!


    OCUP UML fundamental and ITIL foundation
    youtube channel
    Stephan Mueller
    Ranch Hand

    Joined: May 05, 2010
    Posts: 50
    Jan, thank you for the pointer to Oracle's tool. I'll see if I can have a look at this one and see, how they tried to solve these problems.
    On the one hand, it's good to hear that there are no state of the art solutions to this, so I'm probably not reinventing the wheel (as a sidenote, this application will be part of my diploma thesis), on the other hand, it would have been great to look into other solutions to get a feeling on what is to be done.
    I guess I have to dig into hibernates current qbe implementation, also a look into openoffice might be worth a try (even if they c++).

    Discoverer cunningly builds an sql statement

    Do you have any internas on this?

    Not trivial! Difficult queries! Lots of grouping on lots of levels! Beware!

    Yes, that's what I'm looking forward to (or what I'm afraid of).
    Another area of concern is how to provide a user interface, that allows the user to express his ideas in a way, it get's correctly interpreted and translated. The old QBE interfaces look rather formal und archaic.
    Let's see with what I can come up with.

    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2501
        
        8

    Do you have any internas on this?
    No. But at one time in my career I was responsible for solving issues like: "My figure in my report is wrong and it worked last month and I got this report from Jim who has left the company". I would then ask Discoverer to show me the sql statement it generated for that report, and would track back from there to the source data.
    These queries could be rather complex if the report was using summaries, grouping, breaks, totals, ... at various levels.
    There are also some challenges with circular references (the tool does its very best to detect them and warn the user).
    Kudos to Oracle for getting it done.
    And kudos to you.
    Stephan Mueller
    Ranch Hand

    Joined: May 05, 2010
    Posts: 50
    Thank you for the discussion, Jan.
    A particular "thank you" for the fact, that I could recognize that
    I'm not developing a (full fledged) reporting tool - rather an application that allows
    to transform raw technical data into a functional view which can be used for further
    business intelligence (et al) operation, this transformation is done by letting the user define how
    the result should look like (as stated in the above posts).
    So I'm taking a lot of complexity out of
    These queries could be rather complex if the report was using summaries, grouping, breaks, totals, ... at various levels.

    Still, there's enough to do and think through. Let's see what I can accomplish, and if my employer is ok with it, the core parts of the application will be open-source
    and I'll post it here to discuss this with the community.

    Regards,
    Stephan
    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2501
        
        8

     
    GeeCON Prague 2014
     
    subject: How to not reinvent the wheel - technologies/frameworks everywhere