SQLite Database in Android

Overview

 

SQLite DataBase

 

SQLiteOpenHelper

1. getWritableDataBase()
2. getReadableDataBase()
3. onCreate()
4. onUpgrade()

Some query methods in SQLiteDataBase:

 

- execSQL()

- Insert()---------------ContentValues----------Put(String key,String value)

- Query()------Cursor------moveToFirst(),moveToNext(),getCount(),moveToPosition()

- update()

- delete()

 

Now there are some commands that need to be written in a Command Prompt to see the entries in the database.

 

The commands are as follows:

1. set path of the bin
2. set path of platform-tools
3. adb shell
4. cd data
5. cd package name of the application
6. cd databases
7. pwd(present working directory)
8. ls
9. SQLite3 (name of database -db)
10. table
11. select * from table name;

Here are some snapshots of this.

 

 

The procedure of coding:

1. Make an XML file activity_main.xml with two EditTexts and one Button.
2. Just look them up in the MainActivity.java file
3. Use another class named Mydatabase.java
4. After that follow the commands as given above to see the entries in the database.

Code

 

MainActivity.java

package com.example.sqlite;
import android.os.Bundle;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener
{
        EditText euser, epass;
        Button inser;
       Mydatabase o1=new Mydatabase(this,"emp.db",null, 1);
      @Override
      protected void onCreate(Bundle savedInstanceState)
      {

               super.onCreate(savedInstanceState);
              setContentView(R.layout.activity_main);
              euser=(EditText) findViewById(R.id.editText1);
              epass=(EditText) findViewById(R.id.editText2);
              inser=(Button) findViewById(R.id.button1);

            inser.setOnClickListener(new OnClickListener()
            {
                   @Override
            public void onClick(View v)
            {
                   // TODO Auto-generated method stub
                   add();
             }
       });
  }
     public void add()
     {
            SQLiteDatabase db=o1.getWritableDatabase();
            ContentValues cv=new ContentValues();
            cv.put(Mydatabase.user, euser.getText().toString());
            cv.put(Mydatabase.pass, epass.getText().toString());
            db.insert(Mydatabase.dtab, null, cv);
            Toast.makeText(getApplicationContext(), "hii", 1000).show();
     }
    @Override
     public void onClick(View v)
     {
      // TODO Auto-generated method stub
      }
}

Mydatabse.java

package com.example.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.*;

public class Mydatabase extends SQLiteOpenHelper
{
      //declare dbname
      public static final String dbname="emp.db";
      public static final String dtab="emp1";
      //declare clmn nm
      public static final String user="user";
      public static final String pass="pass";
      public static final int dver=1;

      public Mydatabase(Context database, String dbname, CursorFactory factory, int dver)
      {
               super(database, dbname, factory, dver);
               // TODO Auto-generated constructor stub
      }
     @Override
     public void onCreate(SQLiteDatabase db)
     {
           // TODO Auto-generated method stub
           String str="create table emp1(_id integer primary key autoincrement,user text,pass text not null);";  //first ; query termination and later ; is statement termination
           db.execSQL(str);
    }
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
   {
        // TODO Auto-generated method stub
   }
}

activity_main.xml

<RelativeLayout 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:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >

  <Button
  android:id="@+id/button1"
  android:hint="press"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_alignParentBottom="true"
  android:layout_centerHorizontal="true"
  android:layout_marginBottom="102dp"
  android:text="Button" />

  <EditText
  android:id="@+id/editText1"
  android:hint="please enter username"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_alignParentTop="true"
  android:layout_marginTop="44dp"
  android:ems="10"
  android:inputType="textPersonName" >
    <requestFocus />
  </EditText>

  <EditText
  android:id="@+id/editText2"
  android:hint="please enter password"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_alignRight="@+id/editText1"
  android:layout_below="@+id/editText1"
  android:layout_marginTop="57dp"
  android:ems="10"
  android:inputType="textPersonName" />
</RelativeLayout>