Basic Android Sqlite database example/tutorial

Author: | Posted in Android, Quick Tips No comments

There are many ways to store user/application data in android like saving data in Preferences, saving data in Files, saving data data in SQLite database, etc.
Today we will learn how to create SQLite database in Android to save and use user data.
for creating database in android we need to create a java class in our default package(eg.: DatabaseClass.java)(this class extends SQLiteOpenHelper) and paste below code in that class.

package com.example.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseClass extends SQLiteOpenHelper {
    final static String db_name = "databasetestmd";
    final static int db_version = 1;
    public DatabaseClass(Context context) {
        super(context, db_name, null, db_version);
        // TODO Auto-generated constructor stub
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String createFlameUser = "CREATE TABLE storeUser (id integer primary key autoincrement, userName);";
        db.execSQL(createFlameUser);
        // if you want to store some default value for database you can
        // insert them after creating table
        ContentValues cv = new ContentValues();
        cv.put("userName", "");
        db.insert("storeUser", null, cv);
    }
    //this method is used to store user name in sqlite database
    public void setUser(final String name) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("userName", name);
        // updating user name in database
        db.update("storeUser", values, "id" + "=?",
                new String[] { String.valueOf(1) });
    }
    //this method is used to get user name from sqlite database
    public String getUser() {
        String name = "";
        // Select query used for getting data from sqlite database
        String selectQuery = "SELECT * FROM storeUser WHERE id=1";
        SQLiteDatabase database = this.getReadableDatabase();
        Cursor c = database.rawQuery(selectQuery, null);
        if (c == null) {
            return name;
        } else {
            c.moveToFirst();
            name = c.getString(c.getColumnIndex("userName"));
        }
        c.close();
        return name;
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
    }
}

change your main layout file(eg.: activity_main.xml)

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >
    <TextView
        android:id="@+id/hello_user"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        android:textSize="18sp" />
    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
        <EditText
            android:id="@+id/user_name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_toLeftOf="@+id/submit" />
        <Button
            android:id="@+id/submit"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentRight="true"
            android:text="Submit" />
    </RelativeLayout>
</LinearLayout>

now use database class in our main activity class(eg.: MainActivity.java) to show user from database and to change user name in database

package com.example.database;
import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity {
    TextView tvHelloUser = null;
    EditText edtUserName = null;
    Button btnSubmit = null;
    Context context = null;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        context = this;
        setContentView(R.layout.activity_main);
        tvHelloUser = (TextView) findViewById(R.id.hello_user);
        edtUserName = (EditText) findViewById(R.id.user_name);
        btnSubmit = (Button) findViewById(R.id.submit);
        btnSubmit.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View arg0) {
                // TODO Auto-generated method stub
                String userName = edtUserName.getText().toString().trim();
                if (userName.length() > 1) {
                    updateUser(userName);
                } else {
                    Toast.makeText(context, "Please enter proper user name!",
                            Toast.LENGTH_SHORT).show();
                }
            }
        });
        showUserName();
    }
    protected void updateUser(String userName) {
        // TODO Auto-generated method stub
        try {
            DatabaseClass dbc = new DatabaseClass(context);
            dbc.setUser(userName);
            dbc.close();
            tvHelloUser.setText("Hello! " + userName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private void showUserName() {
        // TODO Auto-generated method stub
        try {
            DatabaseClass dbc = new DatabaseClass(context);
            String userName = dbc.getUser();
            dbc.close();
            tvHelloUser.setText("Hello! " + userName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

when you run your project on first run there is a blank entry in database so no name will appear. but when you enter a name and submit it to database, every time app run you will get that name on top.
this is very basic tutorial for how to use SQLite Database in android.
you can create more tables in database and use as per your requirement.