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 .

You first have to create a _table_ to then have _rows_ to initialize — K-ballo, Jan 01 '13 at 05:45
i did create the table and all i'm just having trouble with the initialization .. — user1936104, Jan 01 '13 at 05:54
@Arash i did surprisingly their isn't much on initialization — user1936104, Jan 01 '13 at 05:55
@user1936104 Please see my answer, it will solve your problem. — Dipak Keshariya, Jan 01 '13 at 06:59

score 7 · Answer 1 · edited May 23 '17 at 12:01

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:

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.

score 4 · Answer 2 · edited May 07 '15 at 17:16

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();
    }  
}

In onCreate you need to execute DATABASE_CREATE_PLAYER instead of DATABASE_TABLE_NAME — findusl, May 23 '15 at 18:22

score 0 · Answer 3 · answered Jan 01 '13 at 05:56

if you want to populate it in code, this tutorial will help. It's a series of 14 video tutorials.

http://thenewboston.org/watch.php?cat=6&number=111

If you want to access a preloaded database, check out this link.

http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

But neither one addresses the use of a content provider that you will need for tablets. But both will get you started and once you learn them you can learn the providers.

Your first link seems to be broken now. – Suragch Sep 09 '14 at 03:45 — Suragch, Sep 09 '14 at 03:45

score 0 · Answer 4 · answered Jan 01 '13 at 06:03

0

Write below line before onCreate() method and after private static final String TAG = "DBAdapter"; this line, it will solve your problem.

private static final String DATABASE_CREATE = "create table mTable1 
                   (_id integer primary key autoincrement, 
                   name text not null, 8u text not null, 9u text not null, 
                   10u text not null, 11u text not null);";

answered Jan 01 '13 at 06:03

Dipak Keshariya

22,193
18
76
128

hello @Dipak thank u for answering but this code only creates the the table but does not give it initial values what i need to do is to give multiple rows initial values only once – user1936104 Jan 01 '13 at 07:23

score 0 · Answer 5 · answered Jan 01 '13 at 06:06

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "contactsManager";

// Contacts table name
private static final String TABLE_CONTACTS = "contacts";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";

public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

    // Create tables again
    onCreate(db);
}

You can also visit these site for complete reference and better understanding ::

androidhive.info/Sqlite Tutorial -

initialize a sqlite database android

5 Answers5

Linked