initialize a sqlite database android

Question

Hi every I am pretty new to developing in android and i wanted to add a database to my app.

The problem is that i don't know how to initialize the whole table only once.

I did a lot of reading and i found that u can do it in the overriding of the onCreate(SQLiteDatabase db) method in the helper class .

These are my data fields and my onCreate(SQLiteDatabase db) method .

public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_EIGHTU = "8u";
public static final String KEY_NINEU = "9u";
public static final String KEY_TENU = "10u";
public static final String KEY_ELEVENU = "11u";
private static final String TAG = "DBAdapter"; 

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

but am not sure how.. any ideas ?

The main problem is that I have multiple rows that I want to initialize .

Answer 1

The other answers did not seem to answer how to actually populate the database with initial values so I will share how I did it here.

Basically you save your database columns as string arrays in an xml file. I will just show one array but you could do more for other columns. (You would need to be careful, though, with multiple columns not to mess up the array order, and thus messing up your database rows.)

Make a /res/values/arrays.xml file and add the array to it.

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string-array name="my_array">

            <item>string 1</item>
            <item>string 2</item>
            <item>string 3</item>
            <item>string 4</item>
            <item>string 5</item>

    </string-array>

</resources>

Then you can get that array when you create the database in your helper class. Here is what I did:

private static class DatabaseHelper extends SQLiteOpenHelper {

    private final Context fContext;

    // constructor
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        fContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + " ("
                + FIRST_COLUMN + " INTEGER PRIMARY KEY,"
                + SECOND_COLUMN + " TEXT NOT NULL,"
                + THIRD_COLUMN + " INTEGER,"
                + FOURTH_COLUMN + " TEXT NOT NULL"
                + ");");

        // Initialize the db with values from my_array.xml
        final int DEFAULT_THIRD_COLUMN = 1;
        final String DEFAULT_FOURTH_COLUMN = "";
        ContentValues values = new ContentValues();
        Resources res = fContext.getResources();
        String[] myArray = res.getStringArray(R.array.my_array);
        for (String item : myArray){
            values.put(SECOND_COLUMN, item);
            values.put(THIRD_COLUMN, DEFAULT_THIRD_COLUMN);
            values.put(FOURTH_COLUMN, DEFAULT_FOURTH_COLUMN);
            db.insert(TABLE_NAME, null, values);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);

    }
}

See these links for more help:

• String Resources - String Array docs
• Android: Filling a table from resource file using string array (SQLite)

Another thing I have done is make the databases ahead of time and then just copy it to the database folder when the app is installed. That may work better if you have a large amount of data and you don't want to download the database from the Internet. If you want to copy your database then see this answer.

Answer 2

Try out this way:

public class MyDBHelper  
{  
    // Database properties   
    private static final String DATABASE_NAME = "Test.sqlite";   
    private static final String DATABASE_TABLE_NAME = "Table 1";   
    private static final int DATABASE_VERSION = 4;

    //  Table1 properties   
    public static final String KEY_ROWID = "_id";   
    public static final String KEY_NAME = "name";   
    public static final String KEY_EIGHTU = "8u";   
    public static final String KEY_NINEU = "9u";      
    public static final String KEY_TENU = "10u";   
    public static final String KEY_ELEVENU = "11u";  
    private static final String TAG = "DBAdapter";

    // Create Script   
    private static final String DATABASE_CREATE_PLAYER = "CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE_NAME + "( " + KEY_ROWID + " INTEGER, " + KEY_NAME + " TEXT, " + KEY_EIGHTU + " TEXT, " + KEY_NINEU + " TEXT, " + KEY_TENU + " TEXT, " + KEY_ELEVENU + " TEXT);";        
    private final Context m_context;  
    private DatabaseHelper m_dbHelper;   
    public static SQLiteDatabase m_db;   
    public MyDBHelper(Context ctx)  
    {   
        this.m_context = ctx;  
        m_dbHelper = new DatabaseHelper(m_context);  
    }   

    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

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

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_NAME );
            onCreate(db);
        }
    }

    //---opens the database---
    public MyDBHelper open() throws SQLException
    {
        m_db = m_dbHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close()
    {
        if (m_db != null)
            m_db.close();
        if (m_dbHelper != null)
            m_dbHelper.close();
    }  
}