Recommended data structure for a Data Access layer

Question

I am building a DataAccess layer to a DB, what data structure is recommended to use to pass and return a collection?

Answer 1

I use a list of data access objects mapped to the db tables.

Answer 2

I'm not sure what language you're using, but in general, there are tradeoffs of simplicity vs extensibility.

If you return the DataSet directly, you have now coupled yourself to database specific classes. This leaves little room for extension - what if you allow access to files or to other types of data sources? But, it is also very simple. This is the recordset pattern and C#/VB provide a lot of built-in support for this. The GUI layer can access the recordset and easily manipulate the data. This works well for simple applications.

On the other hand, you can wrap the datasets in a custom object, and provide gateway methods (see the Gateway pattern http://martinfowler.com/eaaCatalog/gateway.html). This method is more complex, but provides a lot more extensibility. In a larger application when you need to separate the the business logic, data logic, and GUI logic, this is a more robust way to go.

For larger enterprise applications, you can look into using Object Relational Mapping tools (ORM). They help to automatically map java objects to database tables. They hide a lot of the painful SQL details. Frameworks such as Spring provide excellent support for ORMs.

Answer 3

I tend to use arrays of objects, so that I can disconnect the DAO from the business logic.

You can store the data in the DAO as a dataset, for example, and give them an easy way to add to the database before doing an update, so they can pass in information to do modification operations, and then when they want to commit the changes they can do it in one shot.

I prefer that the user can't add/modify the structure themselves, as it makes it harder to determine what must be changed in the database.

By initially returning an array they can then display what is in the database.

Then, as the presentation layer makes changes, the DAO can be updated by the controller. By having a loose coupling the entire system becomes more flexible, as you can change the DAO from a dataset to something else, and the rest of the application doesn't care.

Answer 4

There are two choices that are the most generic.

The first way to look at a ResultSet is as a List of Maps, where each Map represents a row in the ResultSet. The keys are the columns listed in the FROM clause; the values are the database values.

The second way to look at a ResultSet is as a Map of Lists, where each List represents a column in the ResultSet. The Map keys are the columns listed in the FROM clause; the values are the List of database values.

If you don't want to do full-blown ORM, these can carry you a long way.