wood burning stoves*
The moose likes Java in General and the fly likes SQL statements in files in jars - best practice suggestions? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "SQL statements in files in jars - best practice suggestions?" Watch "SQL statements in files in jars - best practice suggestions?" New topic
Author

SQL statements in files in jars - best practice suggestions?

Derek Eichele
Greenhorn

Joined: Aug 19, 2010
Posts: 10

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!



Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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?
Derek Eichele
Greenhorn

Joined: Aug 19, 2010
Posts: 10

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?


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Not sure I understand the question. You have an InputStream pointing at the resource, so you should access it via that InputStream. Or if that doesn't work for you, then the getResource() method will return a URL pointing at the resource. But questions about file formats and so on are irrelevant here, as it makes no difference whether the resource is in a jar or whether it's a file in the WEB-INF/classes folder.
Derek Eichele
Greenhorn

Joined: Aug 19, 2010
Posts: 10

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.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Just plain old Properties is what I would use. I haven't heard of PropertiesConfiguration and I can't find it in the standard Java API, so I can't comment on its pros and cons.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Just out of curiosity, what rationale is behind this requirement? Having SQL statements separated from source files makes the maintenance of the DAO class more difficult, in my opinion.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19656
    
  18

I disagree. It allows you to update your statements, improving them. All you need to do is make sure that the columns you use in code / other configuration are still going to be there.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Rob, I assume you react to my question.

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.
Derek Eichele
Greenhorn

Joined: Aug 19, 2010
Posts: 10

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.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL statements in files in jars - best practice suggestions?
 
Similar Threads
best place for .properties files in a Web App
maven: the war directory structure
servlets in jar
JARs needed by Tomcat
jars inside jar ? can it work ?