Apps SQLite query statements in separate file

[PHarkinian]

I'm developing an Android application and I would like to keep my SQLite query statements outside of my Java classes.

I considered using a .properties file, in which to store all of my SQL statements. Sounds fine, each property in a .properties file holds a string - I can even store prepared statements and give them the needed parameters, for example:

get.student.with.first.name=SELECT * FROM Students WHERE FirstName = ?;
​

However, I have also implemented Persistence Contracts for my database tables like so:

public final class StudentPersistenceContract {
private StudentPersistenceContract() {}

public static abstract class StudentEntry implements BaseColumns {
public static final String TABLE_NAME = "Student";

public static final String COLUMN_FIRST_NAME = "FirstName";
public static final String COLUMN_LAST_NAME = "LastName";
}
}
​

I wouldn't want to hardcode the table and column names into the entries of the .properties file, I would like to access them dynamically like so: StudentPersistenceContract.StudentEntry.TABLE_NAME, etc.

One thing I thought of was creating a class which can't be instantiated in which to "construct" the queries I need. Something along the lines of...

public final class SqlQueryConstructor {

private SqlQueryConstructor() {}

public static final String GET_STUDENT_WITH_FIRST_NAME = "SELECT * FROM " + StudentPersistenceContract.StudentEntry.TABLE_NAME + " WHERE " + StudentPersistenceContract.StudentEntry.COLUMN_FIRST_NAME + " = ?;";
}
​

This way I can get the desired SQL query by accessing: SqlQueryConstructor.GET_STUDENT_WITH_FIRST_NAME

This is still a Java class, but it'll be good to know that all of my SQL is there and not scattered around all over the place.

Is this a good idea? Are there any other alternatives? Thanks in advance, guys!

 

[Deleted User]

I normally use a database helper class, in which all my queries are placed, so at least all the DB related stuff is in one place, not scattered around the codebase. This is the recommended pattern to use.
As for externalising your DB query statements out of the Java code, I'm afraid that's not practically possible. It would be nice if SQLite supported stored procedures, but it doesn't.
There's nothing inherently wrong with putting your database statements in your code, after all that's how traditional JDBC works.
Your idea of using public static final constants in your SqlQueryConstructor is good. My DB helper class is usually peppered with constants, defining column names etc.

 

[PHarkinian]

I normally use a database helper class, in which all my queries are placed, so at least all the DB related stuff is in one place, not scattered around the codebase. This is the recommended pattern to use.
As for externalising your DB query statements out of the Java code, I'm afraid that's not practically possible. It would be nice if SQLite supported stored procedures, but it doesn't.
There's nothing inherently wrong with putting your database statements in your code, after all that's how traditional JDBC works.
Your idea of using public static final constants in your SqlQueryConstructor is good. My DB helper class is usually peppered with constants, defining column names etc.

Thanks a bunch for the input, LV426.

I did think about using stored procedures, but yeah, SQLite doesn't support them.

I've been digging around lately, searching for what developers normally do considering accessing a database in Android applications. I want my code to be as clean as possible. I considered using an ORM, but quickly gave up on that idea (apparently Android ORMs are a lot slower than using native SQL statements). Also, according to my experience with ORMs - at one point they may not be able to give you all the control you want and you'll have to use native SQL queries anyway..... plus, they sometimes create a mess of a DB schema.

I think I'll go with the SqlQueryConstructor for now (so that my DB Helper class won't be that verbose and packed up with constants).

Thanks again!

 

[Deleted User]

Yes although ORMs are a very cool idea, and no doubt they are extremely clever, at some point you need to do some customising. And I don't know about you, but I like to be in full control, in terms of understanding exactly what my DB queries are doing, so I have the flexibility of optimising if necessary.