How to use format string with SQL query by resources

Question

I have some string const inside code. Can I put all this into resources strings.xml? I assume that the possibility of separate use of column names will remain. It is necessary to search for the index of posts in future requests to the database. Not to use the indexes the numbers is confusing, uncomfortable.

private static final String NAME_DB = "cars.db";
private static final String TABLE_NAME = "cars";

private static final String COL_BRAND = "brand";
private static final String COL_MODEL = "model";
private static final String COL_COLOR = "color";
private static final String COL_MAX_SPEED = "max_speed";
private static final String COL_ENGINE_POWER = "engine_power";

private static final String CREATE_TABLE =
        "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                COL_BRAND + " TEXT NOT NULL, " +
                COL_MODEL + " TEXT NOT NULL, " +
                COL_COLOR + " TEXT, " +
                COL_MAX_SPEED + " INTEGER," +
                COL_ENGINE_POWER + " INTEGER)";


private static final String INSERT_DEFAULT_VALUES =
        "INSERT INTO " + TABLE_NAME + "(brand, model, color, max_speed, engine_power) VALUES" +
                "('Audi', 'Q7 II', 'White', 233, 252)," +
                "('Acura', 'ZDX', 'Black', 241, 300)," +
                "('BMW', 'X1 I', 'Blue', 205, 204)," +
                "('Hyundai', 'Solaris', 'Red', 100, 100)," +
                "('Hyundai', 'Tucson', 'Gray', 150, 177)," +
                "('BMW', '8 G14', 'Black', 250, 320)";

private static final String SELECT_ALL = "SELECT * FROM " + TABLE_NAME;
private static final String SELECT_TOP_1 = "SELECT * FROM " + TABLE_NAME + " LIMIT 1";

Example code

DatabaseAdapter(Context context) {
    db = context.openOrCreateDatabase(NAME_DB, MODE_PRIVATE, null);
    db.execSQL(CREATE_TABLE);
    Cursor cursor = db.rawQuery(SELECT_TOP_1, null);

    if (!cursor.moveToFirst()) {
        db.execSQL(INSERT_DEFAULT_VALUES);
    }
}

String[] selectAll() {
    Cursor cursor = db.rawQuery(SELECT_ALL, null);
    if (!cursor.moveToFirst()) {
        return null;
    }

    ArrayList<String> list = new ArrayList<>();
    while (cursor.moveToNext()) {
        list.add(
                cursor.getString(cursor.getColumnIndex(COL_BRAND)) + " " +
                        cursor.getString(cursor.getColumnIndex(COL_MODEL)) + " " +
                        cursor.getString(cursor.getColumnIndex(COL_COLOR)) + " " +
                        cursor.getString(cursor.getColumnIndex(COL_MAX_SPEED)) + " " +
                        cursor.getString(cursor.getColumnIndex(COL_ENGINE_POWER))
        );
    }

    return list.toArray(new String[0]);
}

score 1 · Accepted Answer · answered Oct 18 '19 at 13:55

There are some solutions with SQL in XML. Look if you can use one of those.

However IMHO it is better to centralize the code and keep text there, without constants even.

Separate concerns: database query and resulting object. This can be done by providing a Stream of a low-level result set object, that using classes can map to their own classes. Decouples classes; keeps the layers separate. (Maybe less interesting on Android.) The using code becomes nicely compact, the queries being elsewhere.

My arguments:

Less back and forth jumping to different sources
Better readability
Column names do not change
Capitals of constants are less readable
Keeping the code together prevents similar queries to be spread around unattended

However it still is better than SQL in the higher level business code, to isolate SQL in XML to have smaller source, queries elsewhere. So at least do that.

Java - Storing SQL statements in an external file

How to use format string with SQL query by resources

1 Answers1