Tutorial details

Android beginner tutorial: Part 66 - Creating SQLite databases | App Code for Sale | Preview

Android beginner tutorial: Part 66 - Creating SQLite databases | Android Tutorial

Creating SQLite databases in Android

Overview PAGE TOP

Today we'll learn about creating SQLite databases in Android.

Content providers are components that let you store and manage data to use in your applications. Android uses SQLite database engine and provides a number of functions for managing databases. If you are unfamiliar with SQL, you might want to read my SQL tutorials I've written on this blog.

When debugging applications with SQL databases, you need to be able to access and view the database somehow. There are a number of applications that let you open, view and manage SQLite databases. There is one made by me, you can download it for free at http://kircode.com/kirsqlite/.

Today we'll learn how to create an SQLite database in android using the SQLiteOpenHelper class.

Create a java class myDbHelper.java, make it extend SQLiteOpenHelper and implement BaseColumns class. The BaseColumn interface has an _ID string variable defined, which represents the name of the id column in the records of the database.

Declare 4 static variables, which contain name values:

public static final String DB_TEST = "contacts.db";
public static final String TABLE_NAME = "people";
public static final String NAME = "name";
public static final String PHONE = "phone";

Now create an onCreate() function, which receives an SQLiteDatabase object. Call execSQL() method of that object to create a new table, then use a ContentValues object to add a few values to the table:

@Override
public void onCreate(SQLiteDatabase db){
db.execSQL("CREATE TABLE " + TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + NAME + " TEXT, " + PHONE + " TEXT);");

ContentValues values = new ContentValues();

values.put(NAME, "Tom");
values.put(PHONE, "0000001");
db.insert(TABLE_NAME, NAME, values);

values.put(NAME, "John");
values.put(PHONE, "0000002");
db.insert(TABLE_NAME, NAME, values);

values.put(NAME, "Bob");
values.put(PHONE, "0000003");
db.insert(TABLE_NAME, NAME, values);

values.put(NAME, "Joe");
values.put(PHONE, "0000004");
db.insert(TABLE_NAME, NAME, values);
}

Besides the onCreate() function, objects extending SQLiteOpenHelper should also contain an onUpgrade() function. It is called whenever a new version of the application is installed. Since the old database remains, some changes might be necessary, because the new database might have a new structure, etc.

In this example, let's just rewrite the table by dropping it and then calling onCreate() aganin:

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

Full class code here:

package com.kircode.codeforfood_test;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public class myDbHelper extends SQLiteOpenHelper implements BaseColumns{

public static final String DB_TEST = "contacts.db";
public static final String TABLE_NAME = "people";
public static final String NAME = "name";
public static final String PHONE = "phone";

public myDbHelper(Context context){
super(context, DB_TEST, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db){
db.execSQL("CREATE TABLE " + TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + NAME + " TEXT, " + PHONE + " TEXT);");

ContentValues values = new ContentValues();

values.put(NAME, "Tom");
values.put(PHONE, "0000001");
db.insert(TABLE_NAME, NAME, values);

values.put(NAME, "John");
values.put(PHONE, "0000002");
db.insert(TABLE_NAME, NAME, values);

values.put(NAME, "Bob");
values.put(PHONE, "0000003");
db.insert(TABLE_NAME, NAME, values);

values.put(NAME, "Joe");
values.put(PHONE, "0000004");
db.insert(TABLE_NAME, NAME, values);
}

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

Now go to the layout file of the main Activity of the application, activity_main.xml, and add a button:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity" >

    <Button android:id="@+id/createButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Create database"
        />

</LinearLayout>

In MainActivity.java, we listen to the click on the button and load the SQLiteDatabase object of this application. If db doesn't equal null after we do that, Toast a message saying that the database is created. If it equals null, Toast an error message.

package com.kircode.codeforfood_test;

import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

public class MainActivity extends Activity{

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

final Button createbtn = (Button)findViewById(R.id.createButton);
createbtn.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
SQLiteDatabase db = new myDbHelper(getApplicationContext()).getWritableDatabase();

if(db != null){
Toast.makeText(MainActivity.this, "Database is created!", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(MainActivity.this, "Error creating database!", Toast.LENGTH_SHORT).show();
}
}
});
}

}

Now if you run this on an emulator or a device and press the button, a database will be created for your application.

To make sure that the database exists, open the emulator's File Explorer in DDMS, select the application process, then find the database file in data/data/com.kircode.codeforfood_test/databases. The path will vary depending on what your application is called.

Pull the database file from the emulator onto your harddrive using the button located under DDMS tab. Then you can open it using KirSQLite.

Here's what the database looks like in KirSQLite:

sqlite1.png

That's all for today.

Thanks for reading!

Reference PAGE TOP

http://kirill-poletaev.blogspot.it/2013/01/android-beginner-tutorial-part-66.html

0 Comments Leave a comment

Please login in order to leave a comment.

Newest first
!

Sign-in to your Chupamobile Account.

The Easiest way to Launch your next App or Game.

Join Chupamobile and get instant access to thousands of ready made App and Game Templates.

Creating an account means you’re okay with Chupamobile’s Terms of Service and Privacy Policy.