what is the best place to place SQL queries in an application?
The queries might be big and requires formatting.
Appending the query using StringBuilder looks very cluttered.
Storing them in files and reading them every time when a request is made - looks like a bad idea.(but i think reading from the file can be put in a static block)
Keep the SQL query in a resource file that you read to a constant at class load time:
private static final String PERSON_QUERY;
static{
InputStream str = null;
try{
str = ThisClass.class.getResourceAsStream("/path/to/query.sql");
PERSON_QUERY = IOUtils.toString(str);
}catch(IOException e){
throw new IllegalStateException("Failed to read SQL query", e);
}finally{
IOUtils.closeQuitely(str);
}
}
That way you can use your favorite editor to edit the SQL, but you still get the query in a constant in java.
If you do this a lot, extract the code to a helper method:
public static String loadResourceToString(final String path){
final InputStream stream =
Thread
.currentThread()
.getContextClassLoader()
.getResourceAsStream(path);
try{
return IOUtils.toString(stream);
} catch(final IOException e){
throw new IllegalStateException(e);
} finally{
IOUtils.closeQuietly(stream);
}
}
and use that in your static blocks:
private static final String PERSON_QUERY;
private static final String ADDRESS_QUERY;
private static final String AGE_QUERY;
static{
PERSON_QUERY = Helper.loadResourceToString("queries/personQuery.sql");
ADDRESS_QUERY = Helper.loadResourceToString("queries/addressQuery.sql");
AGE_QUERY = Helper.loadResourceToString("queries/ageQuery.sql");
}
In my opinion, different languages should always be separated. It's an awful practice to assemble SQL, HTML, XML, JavaScript etc. from Java code. Use plain templates or template engines like Velocity whenever possible. That gives you many benefits, one of them being that you can change the template without recompiling the java class.
PS: I am using Apache Commons / IO in the above code, but it's not necessary, just easier.
Read about PreparedStatement
In this you need not store all the variable parts of the query
like, insert into table_x values (?,?,?);
and using statement.setString(1,"hello"); , statement.setInt(2,1); , statement.setDouble (3,4.555);
and finally statement.execute(); you can insert the values..
PS: Storing the prepared statement strings in a properties file is recommended.
I'd personally lean towards placing those queries in an XML file; properties file are a nightmare for complex queries (not to forget the \ after each line of query). And while you are at it, why not just use a simple DAO framework like iBatis (now MyBatis) which is a pleasure to use for both simple and complex projects. :-)
Yeah, its good to go. And a property file wouldn't be a bad idea. But sometimes we need to build queries on the fly, for that StringBuiler approach is fine.
You can put them in a .properties file. Using Apache Commons for configuration, you can avoid reading files every time.
If you choose to go with this route, you can aid readibility by breaking up one query into more rows using backslashes:
myLongQuery: select col1, col2, col3, col4 from \
table1 where \
col1 = 'something'
Static queries -- the ones which are depend only on binding parameters -- are perfectly fit in *DAO classes, which abstract DB access away -- you only deal with DAO API like loadUser(int userId) or saveUser(User user). This way how queries are stored in the DAO isn't a big question, do as you like.
I don't use dynamic queries usually, so I can't give good advice about them.
In my scenario, I have a Specific DAO where all my SQL queries are "registered" in a static final block.
Example:
public class MySQLUserDAO extends UserDAO {
private static final String SQL_COUNT = "SELECT COUNT(1) AS TOTAL FROM USER";
// private static final String SQL_CREATE = "INSERT INTO USER(FIRST_NAME, MIDDLE_NAME, LAST_NAME, EMAIL_ADDRESS, DOB) VALUES (?, ?, ?, ?, ?)";
private static final String SQL_DELETE = "DELETE FROM USER WHERE USER_ID = ?";
private static final String SQL_RETRIEVE = "SELECT * FROM USER WHERE USER_ID = ?";
private static final String SQL_UPDATE = "UPDATE USER SET FIRST_NAME = ?, MIDDLE_NAME = ?, LAST_NAME = ?, GENDER = ?, EMAIL_ADDRESS = ?, DOB = ? WHERE USER_ID = ?";
private static final String SQL_FIND_EMAIL = "SELECT * FROM USER WHERE EMAIL_ADDRESS = ?";
private static final String SQL_FIND_FIRST_NAME = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(FIRST_NAME))) = LOWER(?)";
private static final String SQL_FIND_FIRST_NAME_LIKE = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(FIRST_NAME))) LIKE ?";
private static final String SQL_FIND_LAST_NAME = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(LAST_NAME))) = LOWER(?)";
private static final String SQL_FIND_LAST_NAME_LIKE = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(LAST_NAME))) LIKE ?";
private static final String SQL_FIND_BY_NAME = "SELECT * FROM USER WHERE LTRIM(RTRIM(LOWER(CONCAT_WS(' ', FIRST_NAME, LAST_NAME)))) LIKE ?";
But for queries that requires dynamic statement creation, I place it in the method it is used for.
Example:
/* (non-Javadoc)
* @see net.imatri.dao.JdbcDAO#create(java.lang.Object)
*/
@Override
public boolean create(UserEntity user) throws DAOException {
// TODO Auto-generated method stub
PreparedStatement ps = null;
ResultSet generatedKeyResultSet = null;
boolean created = false;
String SQL_CREATE = "INSERT INTO USER(FIRST_NAME, MIDDLE_NAME, LAST_NAME, EMAIL_ADDRESS";
String sqlValues = "(?, ?, ?, ?";
if (user.getGender() != null) {
SQL_CREATE += ", GENDER";
sqlValues += ", ?";
}
if (user.getBirthDate() != null) {
SQL_CREATE += ", DOB";
sqlValues += ", ?";
}
SQL_CREATE += ") VALUES " + sqlValues + ")";
try {
ps = getConnection().prepareStatement(SQL_CREATE, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getMiddleName());
ps.setString(3, user.getLastName());
int pos = 4;
if (user.getGender() != null) {
ps.setString(pos++, user.getGender().toString());
}
ps.setString(pos++, user.getEmailAddress());
if (user.getBirthDate() != null)
ps.setDate(pos++, new Date(user.getBirthDate().getTime()));
ps.executeUpdate();
generatedKeyResultSet = ps.getGeneratedKeys();
if (generatedKeyResultSet != null && generatedKeyResultSet.next()) {
user.setId(generatedKeyResultSet.getLong(1));
}
created = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new DAOException(e);
} finally {
try {
close(generatedKeyResultSet, ps);
} catch (SQLException e) {
// TODO Auto-generated catch block
logger.error("Error closing statement or resultset.", e);
}
}
return created;
}
Your approach isn't bad. We've just used to having DAO's containing SQL in a static final block.
If your SQL can grow many lines, you can use StringBuilder (with no synchronization) or StringBuffer (with synchronization) for String manipulation.
I used to put them into special properties file that is packed into my jar. Then I extracted it using Properties.load(getClass().getResourceAsStream("queries.properties")) and used prepared statement.
But years passed since I used this technique last time and now I think that it is highly not recommended unless you have a serious reason to do this.
I think that usage of JPA is a "right" solution for big projects. If you are developing smaller project use mapping tool like iBatis that allows you to write queries as annotations.
One thing you might want to look into is Stored Procedures or views. I am not sure what what type of database you are using, but in MS SQL and MySQL both of these are an option. They offer not only a place to store your long queries, but since you pass in variables rather than just executing a query, this also protects again the dreaded dun dun dunnnnnnn SQL injection. Now, I also don't know how complex your application is, but in general I tend to use a solution where my queries are stored on the database end, rather than in an application somewhere.
A bit of reading: (wiki articles yes, but there are good references at the bottom.) http://en.wikipedia.org/wiki/Stored_procedure http://en.wikipedia.org/wiki/View_(database)
