I have a project that contains many DAOs, and therefore many SQL statements. I want to extract the SQL from related DAO's and put them in a common file. However...
the project is composed of many jars (they represent modules of the main application). For example, one of the jars I create is called AudioUtils.jar. Within AudioUtils.jar are a variety of classes (including DAOs) that are needed within the main application. The application is a webapp running under Tomcat, and all of the jars live in WEB-INF/lib .
I tried to extract the SQL into a separate properties file, include this file in the jar, and then use the ResourceBundle to get to the SQL statements. I (think) I've learned that resources inside the jar file can't be seen by the classes inside the jar file. The properties file would be seen if it was in WEB-INF/classes, but of course it isn't.
So, I'm asking for suggestions. I would like a clean way for the properties file (or whatever type of file it needs to be) to be included in a jar, but still made available to the classes IN THAT JAR. I'm not interested in having to expand the jar, or having to configure the main application, or custom class loaders - I just want to drop the jar into WEB-INF/lib like I do now and have it work.
This isn't specific to webapps or Tomcat, as I'd like to (eventually) re-use these jars in other (non-Tomcat) applications.
Any suggestions are appreciated!
Derek Eichele wrote:I (think) I've learned that resources inside the jar file can't be seen by the classes inside the jar file.
Well, that surprises me. Since the jar file is in the web application's classpath, it should be possible for anything in the web application to access resources inside the jar via something like
Provided of course that the "sql.properties" file is in the root folder of the jar. Are you saying that's not the case?
Thanks, Paul, for the reply.
In my defense .... :-)
I was fixated on ResourceBundles, and I couldn't get those to find the properties file. I had tried other alternatives like PropertiesConfiguration, but gave up too quickly as it seems I merely missed the leading "/" in the file name. Sigh.
Now that I know that I can locate the file, how should I best access it? PropertiesConfiguration has some limitations with regard to whitespace, line feeds etc.
Is there a common practice to do this sort of thing?
Yes, I agree, the fact the the file is in a jar doesn't matter any more.
What I'm looking for now is ideas on how others might implement the actual file and the retrieval of the sql statements.
I tried PropertiesConfiguration but that doesn't do what I want. I was hoping to do something similar, though, as in :
nameOfMyQuery="select some values from some table ...... "
and I was hoping to find out if there is a norm for this sort of thing that I should follow.
Although I generally understand the point of having all SQLs in one place, it seems to me that having the text in the source code has its benefits too. Especially if I revisit the code, eg. to check indexes of bind variables. Also editing lengthy SQL statements in a properties file would seem a bit tedious to me, as my display is only so wide. And in a few cases I stuff specific non-constant constructs into the SQL, which would probably require to parcel out single SQL query into several items in the properties file.
Rob Prime wrote:All you need to do is make sure that the columns you use in code / other configuration are still going to be there.
This is an important point. Do you use or know any tool to accomplish this for SQLs stored separately?
To make sure that I don't access non-existent schema objects I have a String constant defined for every table, column, sequence and view I access with the DAO classes and I rigorously use these constants in my SQL statements, never typing the object names directly into SQL statements. These constants are generated automatically from schema definitions. That way if the object gets renamed or deleted, I get compile-time error.
I strongly prefer to check existence of schema objects before runtime, though there are certainly more ways to do so and certainly this can be done for SQL statements stored separately from DAO classes. Indeed, having the SQL statements separately might even allow for better syntax check, maybe using Oracle's explain plan or equivalents for other DBs. The parcelled-out statements would still pose a problem, though.
I wanted to achieve a couple of simple things. First, the SQL inside my DAO's is broken over multiple lines for "readability" and some of the statements are quite long, leading to a lot of " string " + " string " code split over multiple lines. This makes it very difficult to cut and paste the statement into a SQL tool to edit/run the query. I wanted to find away to put the SQL statement as a single string to make this easier. Properties (PropertiesConfiguration is Apache Commons) will allow Strings to span multiple lines of the properties file, but requires a continuation character ('\') which I was hoping to avoid.
Also, the way the application was written, some of the SQL is re-used, and if a statement needs to be edited, I want to do it only in one place.
The file containing the statements are to be contained in the same jar/hierarchy as the DAO's that use them, so being able to determine which SQL statements are used with which DAO's will be fairly obvious.