Re: Decouple SQL queries from class in OOP design

Responding to Hongyu...

To my knowledge, in OOP design it's not good to mix SQL queries with
application codes in classes, because it limits the usability of the
classes and bound them to the local database schema. But I am not sure
what the best way is to decouple them. The method that I am using right
now is to create a hash to store all the SQL statements and prepare
them in the main program, then I will pass this hash as a variable to
all the classes that are going to use it. I am not sure whether there
is any better design. Thanks in advance!

You are correct that one wants to decouple the problem solution logic from the database. (CRUD/USER processing is an exception.) Typically that is done by encapsulating the DB access in a subsystem that has a generic data transfer interface based on the problem solution's data access needs.

Within that subsystem one provides SQL command construction. To do that one has to have a mapping of data identity between the subsystem interface messages and the RDB schemas. So if one has an interface message like:

saveAccount (accountID, value1, value2, ... valueN)

The subsystem will have to have a mapping to know that saveAccount maps to the "Account" table, accountID maps to the "Account Name" field, value1 maps to the "current balance" field, and so on. (Essentially one identifies the individual tuple fields positionally in the message.)

There are various mechanisms based on table lookups to do this, of which your hash scheme is one (though the granularity is usually at the field level). This allows a SQL string to be incrementally built up for the saveAccount interface method by plugging in text snippets to a standard format.

Such lookup tables can be hard-wired in the subsystem. Alternatively, one can initialize them from external configuration files. That's more infrastructure work but it allows the entire RDB access subsystem to be reused as-is across applications by simply substituting the configuration file.


*************
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@xxxxxxxxxxxxxxxxx
Pathfinder Solutions  -- Put MDA to Work
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
(888)OOA-PATH



.

Relevant Pages

  • Re: Restricting functionality on objects: "remote access proxy" (pattern)
    ... about the current user's access rights in a chat. ... If the level of privilege can change during the chat, then whoever understands that change context can simply reset the privilege attribute value. ... The simplest implementation is for the subsystem interface to re-dispatch those messages to objects that have the responsibilities needed to to service the user's request. ...
    (comp.object)
  • Re: What doesnt lend itself to OO?
    ... The whole idea that a subsystem is just ... > If the clock service has identity then the client looks like... ... The first line exists in the server. ... external interface is the traditional input interface whose ...
    (comp.object)
  • Re: Help on choosing a valid pattern: composite or not?
    ... But that class would only exist in the UI subsystem in my ... In the GUI subsystem where GfxNode might live I am expressing an entirely different set of paradigms for display and I might have a handy MVC infrastructure to make the code simpler, which is where GfxNode comes into the picture. ... I can go directly to the View from the subsystem interface. ... Let's say I have defined a Facade pattern class for the interface called, for lack of anything better, GUIInterface. ...
    (comp.object)
  • Re: Definition of FACTORY
    ... The subsystem lives to convert data back and forth from ... DBAccess subsystem through an interface. ... The reverse, saving a Customer, would be the same sort of thing. ...
    (comp.object)
  • Re: What doesnt lend itself to OO?
    ... The whole idea that a subsystem is just ... > The first line exists in the server. ... objects between client and server i.e. as far as the client code is ... > external interface is the traditional input interface whose ...
    (comp.object)