File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Performance and the fly likes Maintaining SQL Queries in a separate text file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Maintaining SQL Queries in a separate text file" Watch "Maintaining SQL Queries in a separate text file" New topic
Author

Maintaining SQL Queries in a separate text file

Narayanan Madaswamy
Greenhorn

Joined: Apr 17, 2013
Posts: 4
Hi,

I am working on web based project using Spring MVC framework and I am maintaining all SQL queries in a separate text file so that I can change them any time when needed. I just want to know am I doing right, it this a optmistict way of coding??
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7779
    
  21

Narayanan Madaswamy wrote:I am working on web based project using Spring MVC framework and I am maintaining all SQL queries in a separate text file so that I can change them any time when needed. I just want to know am I doing right, it this a optmistict way of coding??

It's one possibility, but if they can be grouped into more generic queries, you might also want to think about PreparedStatement's.

Winston

Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Seems to me that if you were storing your SQL queries externally, you would pretty much have to be using PreparedStatement. I can't see how parameters would work otherwise -- unless you produced your own design for identifying and inserting parameters.
Narayanan Madaswamy
Greenhorn

Joined: Apr 17, 2013
Posts: 4
Yes you are right Paul Clapham, I am using my own function to prepare query statements. please check the following and please let me know if I am doing something silly :-)

Query in text file :

select label,control,controlid,valuetypescript,validationscript,style,cssclass,field_id from dynamic_controls where page_id=val0 and portfolio_id=val1

Call to QueryBuilder :

String [] QueryVals={"10",session.getAttribute("portfolioid").toString()};
QueryBuilder(QueryFromFile,QueryVals);

QueryBuilder defination :

public String QueryBuilder(String Query,String[] Val)
{
for(int i=Val.length-1; i>=0;i--)
{
Query=Query.replace("val"+i, Val[i]);
}
return Query;
}


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

This indeed isn't an ideal solution. Your code lacks all of the benefits of using PreparedStatements: it is prone to SQL injection attacks, it may suffer problems with formatting dates, numbers and strings correctly and in most databases it will perform worse (perhaps much worse) than necessary.

Please have a look at our PreparedStatement page.

Edit: storing the SQL strings in a separate file is a workable solution, assuming you do use prepared statements. Some of the advantages are that you could easily create database specific versions of your statements, if necessary (some statements could be reworked to perform better in a different database), and you could also easily let an experienced database developer review your statements even if he isn't fluent with Java.
Narayanan Madaswamy
Greenhorn

Joined: Apr 17, 2013
Posts: 4
Thanks Martin Vajsar, can you put an example code here..??
Marshall Blythe
Ranch Hand

Joined: Feb 26, 2013
Posts: 32
Keeping your SQL in text files instead of "baking" it into Java classes is a good goal, but have have you considered using the MyBatis SQL Mapper framework instead of rolling you own solution? MyBatis lets you externalize your SQL in XML files. It has powerful mapping & dynamic SQL capabilities, and it uses PreparedStatements for optimum efficiency. It integrates nicely with Spring, but like all frameworks there is a learning curve to overcome in order to use it effectively.
jake lopez
Greenhorn

Joined: Jul 03, 2013
Posts: 13
sounds like MyBatis
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Maintaining SQL Queries in a separate text file