Good Post on Clean Code


Using SQL Statements as a String in Class file while working on Android or Java application can be such a pain.

I was checking in the net to find out whether there are any ways in which we can store the SQL Statments separately so that the code can be more readable.

I found some interesting stuff. Please find below the links for the same.

1. Clean Code

2. Java Practices : Keep SQL out of code.

3. Android : Store SQL Queries in an XML File (Stackoverflow answer)

I am working on a way to store all my SQL Statements in an XML File while working with Android.

Found this post on placing XML File with Data on Android in StackOverflow.

res

  • No subdirectorys are allowed under the specific resource-folders.
  • The R-class indexes all resources and provides simple access.
  • There are some simple methods which help reading files stored in the res-directory

assets

  • Subdirectorys are allowed (as much as you like).
  • No indexing by the R-class
  • Reading resources stored in assets is done using the AssetManager.

Also found a cool post on Working with resources in Android : Link.

 

SQL Statements in the case of Android can be stored in the strings.xml under the resources–> values  folder.

In case you using a Database class which is a pure java class the this link will be able to help you on how to do the same.

In my case, I created a separate DAO Class called the DBClass which deals with the connecting and disconnecting with the database. I set the context of the DAO class so that the strings can be used here.

Advertisements

Android: Filling a table from resource file using xml (SQLite)


Good link.

Cleanest way to build an SQL string in Java


Properties file can be used:

update_query=UPDATE user_table SET name=? WHERE id=?

Then with the help of a simple utility class:

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
    	InputStream is = 
    		Queries.class.getResourceAsStream("/" + propFileName);
    	if (is == null){
    		throw new SQLException("Unable to load property file: " + propFileName);
    	}
    	//singleton
    	if(props == null){
    		props = new Properties();
    		try {
    			props.load(is);
    		} catch (IOException e) {
    			throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
    		}			
    	}
    	return props;
    }

    public static String getQuery(String query) throws SQLException{
    	return getQueries().getProperty(query);
    }

}

you might use your queries as follows:

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

All credits to the guy who gave the answer.

Source : StackOverflow