Patterns for use w/ Database

[Geoff Tate]

I am looking for ideas for a standard way to build objects from databases. For example, I have an employee class that has properties that would get populated from a database. Should I have a seperate class that manages/creates the employee instances or should the employee class have hooks to the database within itself. Also, when it comes time to update any changes. Should there be an employee.update() or should a managing class do the database logic. Curious to see what everyone thinks.

[Thomas Paul]

I wouldn't put the hooks to the database directly in each class that represents data because if you haave to change databases you will have too many changes. I would have one class repsonsible for DB pooling and have the employee class ask that class for a Connection. But the Employee class should know the basics of how to store itself in the DB.

[Junilu Lacar]

Take a look at Craig Larman's book "Applying UML and Patterns." In chapter 38, he presents a framework for doing what you describe. He uses a number of patterns which he has presented in earlier chapters, which he collective calls GRASP (General Responsibility Assignment Software Patterns.
J. Lacar

Originally posted by Geoff Tate:
I am looking for ideas for a standard way to build objects from databases.

[Kathy Shkarlet]

There're also mapping tools which will create persistent classes from the database schema. Those classes already know how to make themselves persistent.

[Ajith Kallambella]

I personally don't like directly mapping classes to tables. Instead classes should have handle( object attribute ) to corresponding database record(s) via flat objects. This enables us to effectively represent both persistent and non-persistent("new") records as classes.
Each class would be associated with a ClassManager that will act as a link to PersistenceManager. The most important part of the Class manager would be a Cache. Optimistic concurrency control or a pessimistic concurrency control using time-stamps would be implemented everytime objects in the cache are persisted. Everytime a record is retrieved from the database it is first added to the cache and then a reference is returned to the requestor.
A set of ClassManagers would share a common PersistenceService. This service handles writing/reading data( our "flat" classes that are table representations) from the tables using JDBC/SQL. You can implement value added features like connection pooling, transactions across multiple databases etc in the PersistenceService layer.
Last but not the least, you have to have a way of refreshing the objects in the cache so that they reflect the most current state of the record in the database. This is very important in a multi-client scenario. You could use a event-broadcast mechanism with the help of custom TableWatcher that constantly poll for changes in the DB data. DB-based triggers can write to one common table information about "what" changed and this information will get "cooked" into notifications. Clients can then use a publish-subscribe pattern to listen to object-change events that interest them and refresh the objects in their cache.
Phew!! I hope this is will not cause information-indigestion to you. If you have any questions I'll be glad to help you out
BTW : I have written such a framework using a non-Java( but OO-based ) language and it works great!!
------------------
Ajith Kallambella M.
Sun Certified Programmer for the Java�2 Platform.
IBM Certified Developer - XML and Related Technologies, V1.
[This message has been edited by Ajith Kallambella (edited March 01, 2001).]

[Thomas Paul]

I think this adds a huge level of unneeded complexity. It also would make the app more difficult to port to EJB.
[This message has been edited by Thomas Paul (edited March 01, 2001).]

[Geoff Tate]

I have created classes that take a connection object in to do the work but I'd rather have it detached from the table/db specific code. I wasn't really concerned about the table to class mapping like an EJB would do. I have seen GRASP before - I need to take another look at that.

[Michael Chang]

I would prefer to separate data classes from database connections. What I've done is to have a controller class for each data class. Each controller class has a query method and an update method to perform database operation for its corresponding data class. And each controller knows how to generate the necessary SQL statements for the data class and how to map the database results to the data class. This way, your data classes are completely separated from database operations and become more reusable. When some object needs to database operation, it will go through the controller. If necessary, you can also have a generic database controller to control properties like data source, name, user ID, and password, etc.

[Avijeet Dash]

I wud create an EmployeeDetailDO data object for all teh attributes and get/set methods.
an EmployeeEntityBean for all the database opeartions.
an EmployeeSessionBean to fill the DO.

[Thomas Paul]

This way, your data classes are completely separated from database operations and become more reusable.

I don't see how this could be true. A class that represents data from a database would have to be so tightly coupled to the controller class that it couldn't exist without the controller class. I'm not saying that a controller class is bad... I'm just saying that it doesn't improve reusability. It may make maintenance easier.

[John Wetherbie]

It depends on how you plan things to be reused. In my experience reusing a single class in a stand-alone fashion is very rare. I aim at component or package reuse.
The ultmitate test of reuse is actually reusing something.
John

[Thomas Paul]

The point is that the two classes are so tightly coupled that you get no advantage for reuse by making them separate classes. As far as reuse goes, the two classes will always be used together anyway so you haven't bought anything.

[Michael Chang]

It is true that usually you will need to go through the controller class to fill the data object. But your data object can also be set up by other objects or saved into session or passed around, a pure data object is a much cleaner and more efficient way than having the database stuff in it. Think of the data object being used by a jsp file or as a session information. Session data needs to be serializable in most of the environments. So you don't want to keep a lot of database information in it.

[zulfiqar raza]

Is there any place where I can get a source code example for one of these suggestions. That is it shows a simple table, an associated table class and/or dataobject classes which get and update data from a table???
thanks you in advance.
zulfiqar

[Thomas Paul]

If you are referring to a shared controller class used by all data objects than I would agree with you. We do this so that only one class actually knows how to get to a database.

[Jim Baiter]

There are a bunch of them at Ward Cunningham's Wiki web:
http://c2.com/cgi/wiki?search=Database

[Geoff Tate]

I have been using controller classes to build the objects in a factory-like fashion. This allows to build one employee instance or a collection of employee instances. I guess it is very difficult to reuse objects built from a database to be used with other databases because they are so tightly coupled. There is something that doesn't seem right about that.

[Geoff Tate]

can anyone see fault in doing the following: creating your reusable classes in one package and the database/application specific classes in another? ex:
com.geofftate.reusable.Employee
com.geofftate.application.EmployeeManager.
Now I can freely get and set my Employee classes free from the specifics of my app. I can use an application specific EmployeeManager to load and update employee data using logic specific to the application.