This deals with an sql
string, but it's not about the sql mechanics, its really about building the string. It just so happens it's an sql string. But if you think it should go to the db area anyway, feel free to send it over. Thanks!!!
==
I've been working on an application that has the potential to be replicated many many times.
With this in mind, I have built an application, which contains all the necessary core logic in a core jar.
Next I built an "application instance" to manage the client-specific aspects of the application, etc. Works great.
With this architecture in mind, I keep the core sql strings in the core application and the client-specific sql strings in the application instance.
What I noticed is that 95% of each sql call is going to be the same for each application. The only reason I have to separate the sql is because someone might want to track something unique to their instance (like a birthday, which isn't stored in my core).
I don�t like to have 30 instances of mostly the same sql calls out there. It�s an upgrade/maintenance nightmare with that many installs.
The overall goal is to make updating and modifying the 95% common sql easy. If I make a change, I don't want to have to update 30 mostly equal sql strings. Obviously, if I make a change to the app, I'm going to have to deploy a new jar each time, the real goal is to update one area vs. thirty.
Does this make sense?
I'm storing my sql statements in a .properties file under my
jdbc package (not in my classes directory). Anyway...
I thought about storing my sql strings in a way that I separate my sql statement. The select portion of the string in one variable, the from in another, a where in another, and so on...
Example:
stringMemberSelect=SELECT firstName,lastName
stringMemberFrom=From member
stringMemberWhere=WHERE active=1 AND memberID={0}
then in the application instance, I could have
stringMemberSelect=,birthday
stringMemberFrom=
stringMemberWhere=
Seems cumbersome, but I may not have many options�
I've seen an app that actually builds part of the sql string, then passes it to a custom class which then looks to see if that particular app instance has any additional things to add to the statement. If it does, it sends it the partially built string, tacks on to it the unique columns and then passes it back to the "core" area to finish off the string. The it executes it. Doesn't feel right though...
Thoughts?
Thanks!!!