Right now we are vendor locked into a database schema that doesn't fit our need. They have xml references in the database to flat files that we then retrieve to build POJOs (so the user table will have a GUID and we use that GUID reference to get an xml file to pull out user information). That only works for half of it, the other half is done through their webservice utilities. It's a mess, a huge mess.
We moving to a new vendor that is only a short term solution, they have similar issues with their structure not matching ours, but it is better (at least we can do everything using sql connections).
The problem I have is trying to figure out what would be a small, simple framework that we could use to get away from this mess, push us into a new mess, but make the process of "getting data" abstract enough so that we can add in a 3rd party when the need arises.
I like hibernate, but I think it is out in this case because it gives us nothing with the current system. There's going to be a pretty fun time where both systems are going to be using the same code while we transition clients from the old to the new. Luckily our UI is using pure Ajax so the real meat of the issue is maintaining a system that lets us switch between two entirely different formats of data on the fly.
Effectivley we'd do something like this, create an interface, IUserDAO and have two DAO objects OldSystemUserDAO and NewSystemUserDAO, both of those would inherit the same methods (CRUD) but they would follow entirely different paths to get that data (one hitting flat xml files, webservices, and a database while the other either uses webservices or a database).
Where should I start? Should I roll my own? Would I gain anything by implementing SEAM/Spring at this point? It's gotta be something simple, something that really just focuses on this data access layer point, but I haaaate rolling my own stuff.
Any help would be appreciated, hopefully I explained this with enough detail, I could provide some class examples if necessary.
The fundamental question you are asking is actually very hard: what is a good OO to RDBMS mapping layer. I've been writing the mapping layer, and using libraries that claim to do it well for nearly 30 years. The reality is that all have strengths and weaknesses.
That said, I would strongly urge you to look at Hibernate. It does most of what folks want, and its not too heavy. And
Hibernate Made Easy is a good book, written by Cameron Wallace McKenzie (who hangs around here).
I recommend that you aim to solve 95 percent of your problems directly with whatever solution you pick, the last 5 percent you can always figure out something. Looking for perfection here is futile.
Also, a nit, but an Architect is a person who designs things. They do not "architect" things, they are not busy "architecting" anything. The proper verb is design.