Creating example values in SQLite database using strings from strings.xml

Question

I got this class, which creates database at the 1st run of the app. I wanted to fill one table with some example values from strings.xml. Basically I'd do this like:

private static final String CATEGORIES_FILL = "INSERT INTO categories VALUES ('', " + getString(R.string.fuel) + "); " +
                                              "INSERT INTO categories VALUES ('', " + getString(R.string.food) + "); " +
                                              "INSERT INTO categories VALUES ('', " + getString(R.string.treatment) + "); " +
                                              "INSERT INTO categories VALUES ('', " + getString(R.string.salary) + "); ";

The problem is, the class is not an activity, and string must be static so I can use

db.execSQL(CATEGORIES_FILL);

Please correct my approach so I could use strings.xml to do that.

I assume I could do that in my activity class surrounding it with try block, but I don't like the idea of executing this every time I open the activity.

Fragment of database class

private static class DatabaseHelper extends SQLiteOpenHelper {


    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);


        final String CATEGORIES_FILL = "INSERT INTO categories VALUES ('', " + context.getString(R.string.fuel) + "); " +
        "INSERT INTO categories VALUES ('', " + context.getString(R.string.food) + "); " +
        "INSERT INTO categories VALUES ('', " + context.getString(R.string.treatment) + "); " +
        "INSERT INTO categories VALUES ('', " + context.getString(R.string.salary) + "); "; 
    }

    /* Tworzenie tabel
     * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        db.execSQL(CATEGORIES_FILL); //need the way to access this final here
        db.execSQL(EXPENSES_CREATE);
        db.execSQL(INCOMES_CREATE);
        db.execSQL(BUGS_CREATE);            
    }

Jens · Accepted Answer · 2011-12-22 14:02:01Z

3

Something vaguely like this.

Add to res/values/<yourchoiceoffilename>.xml & add your string references to a string-array.

<resources>
    <string-array name="categories">
        <item>@string/fuel</item>
        <item>@string/food</item>
        <item>@string/treatment</item>
        <item>@string/salary</item>
    </string-array>
</resources>

Create a SQLiteStatement on your database.

SQLiteDatabase db = ...;
SQLiteStatement insert = db.compileStatement("INSERT INTO categories (yourcolumnnamehere) VALUES (?)");

//process each string
for (String category : getResources().getStringArray(R.array.categories)) 
{
    insert.bindValue(1, category);
    long id = insert.executeInsert(); // In case you care about the row id.
}

Edit: And, consider doing something like following to your class (I've probably left a bug or two in there, but you should get the gist of it):

private static class DatabaseHelper extends SQLiteOpenHelper {
    ...
    // Replace 'yourcolumnnamehere' with whatever your column is actually named.
    private static final String INSERT_CATEGORY = "INSERT INTO categories (yourcolumnamehere) VALUES (?)"
    ...
    ...

    private final String[] mCategories;
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mCategories = context.getResources().getStringArray(R.array.categories);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);

        // Here you create the SQLiteStatement that will be used
        // to add categories. The values are stored in mCategories.
        SQLiteStatement statement = db.compileStatement(INSERT_CATEGORY);
        for (String category : mCategories) {
            statement.bindValue(1, category);
            statement.executeInsert();
        }
        statement.close();

        db.execSQL(EXPENSES_CREATE);
        db.execSQL(INCOMES_CREATE);
        db.execSQL(BUGS_CREATE);            
    }

edited Dec 22, 2011 at 14:02

answered Dec 20, 2011 at 15:33

Jens

17.1k4 gold badges59 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jacek Kwiecień Over a year ago

but I can't use getResources if class doesnt extend Activity right? And what's the input in the (?) place

Jens Over a year ago

Any Context will be able to get you Resources. ? is a placeholder used when binding values to a pre-compiled SQLite statement. Where are you doing this since you have no access to a Context.

Jens Over a year ago

So, for any sane implementation, a subclass of SQLiteOpenHelper I take it? The SQLiteOpenHelper constructor takes a Context, save a reference to that parameter of your own (since it doesn't define a helpful getContext) and use that.

Jens Over a year ago

Yup, you got a Context available. Call context.getResources() .getStringArray(R.array.categories) to retrieve an array of strings stored in your resources. While you "can" make complete SQL Strings that hold your values its better to use a SQLiteStatement since bound column values will be escaped properly and not break your SQL.

Jens Over a year ago

Store it as a field in your DatabaseHelper. I've edited the answer.

|

Venky · Accepted Answer · 2011-12-20 15:20:53Z

1

<string-array name="categories">
        <item>aaaaaa</item>
        <item>bbbbbbbb</item>
        <item>cccccccc</item>
        <item>ddddd</item>
</string-array>

Instead of adding String , add string array to string.xml Then in Java retrieve it using :

String[] categoriesAndDescriptions = getResources().getStringArray(R.array.categories);
    for(String cad : categoriesAndDescriptions) {
        String categoryAndDesc = cad;
        list.add(categoryAndDesc);
    }

list is ArrayList, after you get one value store it in Arraylist.

Now Populate this ArayList to Insert Query, you can get first value using list.get(0)

answered Dec 20, 2011 at 15:20

Venky

11.1k5 gold badges51 silver badges66 bronze badges

1 Comment

Jacek Kwiecień Over a year ago

getResources is also Context class based. i can't use id on non-activity class

Timweb · Accepted Answer · 2017-01-22 11:51:28Z

0

try this

private Resources mResources;

DatabaseHelper(Context context) {
   super(context, DATABASE_NAME, null, DATABASE_VERSION);

   mResources = context.getResources();

   final String CATEGORIES_FILL = "INSERT INTO categories VALUES ('', " + mResources.getString(R.string.fuel) + "); " +
                "INSERT INTO categories VALUES ('', " + mResources.getString(R.string.food) + "); " +
                "INSERT INTO categories VALUES ('', " + mResources.getString(R.string.treatment) + "); " +
                "INSERT INTO categories VALUES ('', " + mResources.getString(R.string.salary) + "); "; 
            }

answered Jan 22, 2017 at 11:51

Timweb

234 bronze badges

Collectives™ on Stack Overflow

Creating example values in SQLite database using strings from strings.xml

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related