Is it worth creating a data access layer in my case?

Question

The scenario:

Suppose I had to build a website to interact with a legacy database. The vendor is not going to be replaced anytime soon. Performance is needed and for most of tables we will only work with a subset of columns which might vary from query to query, so we will mostly not work with entire "entities".

The question:

is it worth creating a dedicated data access layer apart from the services and presentation one? Or would be more correct to interact directly with JDBC from the service layer to avoid multiple mappings (2 in this case)?

Summary:

DAL <---> BL <---> Presentation VS BL/DAL hybrid <---> Presentation

Answer 1

Yes.

In all the time it is a good idea to hide JDBC into a dedicated data access layer. You can use this as an ACL too (see Domain Driven Design - Anticorruption Layer) which works as a mapper to represent the legacy data in the way you would like to manipulate.

Except of very seldom trivial CRUD databases, usually the database model (aka storage model) is not 1:1 representation of the domain model (the way you're representing data in your application in objects). Hence the "i need only a few columns" problem is totally valid in any project.

15 years ago the ORM mappers (e.g. Hibernate, Linq) started offering a way to hide this "complexity" from the developers by giving a false impression that you don't need to think about your storage model, and the storage model can be inferred from the domain model. This solution however has an extreme serious performance hit - easily understood by DB architects, and hard to explain to most of the Java programmers :)

5 years ago, the Domain Driven Design approach started to take this less strainght, explicitly pointing out that the "same" data can be modeled differently in different subsystems (e.g. in the database), and it is all OK since we can convert the data to our needs even if the data comes from XML or JSON or has weird column names, or other evilness. This approach is called Anticorruption layer (ACL) - especially a dedicated place in code when you "translate" between different systems representing the same data.

In your case, instead of using this data access layer as a brainless DAO which just 1:1 reads all table columns into a class, you can do a little more:

• create a repository (so a separate class which handles data manipulation)
• the respository accepts and retrieves data in your format (only the right columns, etc.)
• use select statements to pick the data you need, joining to other legacy tables and using case/decode statements to get the data in the format you need, (so convert the storage data model to your domain data model)
• when updating or inserting something, in this ACL code you are free to insert/update multiple tables depending on your storage data model (so convert your domain data model to storage data model)