This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Maintaining SQL Queries in a separate text file

 
Narayanan Madaswamy
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10417
63
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin Vajsar, can you put an example code here..??
 
Marshall Blythe
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sounds like MyBatis
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic