Using the Transaction Script pattern - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 323

scripts. This can make the code difficult to understand and maintain, especially if
the business logic is complex. This is made worse by the fact that transaction
script-based business must explicitly load and save data, whereas in a domain
model-based design, many objects are automatically loaded by navigation and
changes are automatically written back to the database.
Cost of maintaining handwritten SQL
Another problem with using the Transaction Script pattern is that you have to write
all of the SQL yourself. While this gives you a lot of control and makes your SQL
available for inspection by the DBA s, it can be difficult and tedious to maintain
large amounts of SQL . It is quite common for one small change to a table definition
to cause you to update multiple SQL statements and DAO s. For example, if you add
a column to the RESTAURANT table, then in addition to changing the Restaurant-
DAO you might need to change the PendingOrderDAO because it executes a SQL state-
ment involving the RESTAURANT table. There are ways of designing the DAO
classes that reduces this problem, but they do not prevent it altogether.
Lack of portability of SQL
The problem with developing and maintaining SQL is made even worse by the dif-
ferences between the SQL dialects supported by the various databases. For exam-
ple, some databases (such as Oracle) have sequences to generate unique ID s,
whereas other databases (such as HSQLDB ) have identity columns. Consequently,
developing a JDBC application that supports multiple databases is extremely chal-
lenging. This can be a problem even if your application is only deployed on a sin-
gle database because you might want to write tests that use an in-memory database
such as HSQLDB .
You could use a persistence framework to avoid these problems. JDO and
Hibernate insulate the application from the differences between the various data-
bases and will even generate the DDL that defines the schema from the O/R map-
ping. In this kind of design, the persistent classes would mirror the database
schema rather than implementing a domain model and would not contain any
business logic. Of course, this option would only work if an application accessed
the database in ways that are supported by the persistence framework, which is
often not the case if you are using the Transaction Script pattern. Moreover, if the
application can use a persistence framework, then it is not clear why you would
not want to go further and implement a complete domain model.