• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL statements in files in jars - best practice suggestions?

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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!



 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.

 
Everybody's invited. Even this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic