Database Design using SQLiteOpenHelper

Demo:

 

1)StudentDB:

package com.venky.standarddatabase;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class StudentDB extends SQLiteOpenHelper {

      SQLiteDatabase db;

      private static final String DATABASE_NAME = "student";

      private static final int DATABASE_VERSION = 1;

      private static final String TABLE_NAME = "sampletable";

      private static final String COL_ROWID = "rowid";

      private static final String COL_SNAME = "sname";

      private static final String COL_SLOCATION = "slocation";

      private static final String COL_SPHNUMBER = "sphnumber";

      String CREATE_TABLE = "create table sampletable(rowid integer primary key autoincrement,sname text not null,slocation text not null,sphnumber text not null)";

      public StudentDB(Context context) {

            super(context, DATABASE_NAME, null, DATABASE_VERSION);

            // TODO Auto-generated constructor stub

      }

      @Override

      public void onCreate(SQLiteDatabase db) {

            // TODO Auto-generated method stub

            db.execSQL(CREATE_TABLE);

      }

      @Override

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

            // TODO Auto-generated method stub

      }

      void openDatabase() {

            db = getWritableDatabase();

      }

      void closeDatabase() {

            db.close();

      }

      void insertValues(String sname, String slocation, String sphnumber) {

            ContentValues values = new ContentValues();

            values.put("sname", sname);

            values.put("slocation", slocation);

            values.put("sphnumber", sphnumber);

            db.insert(TABLE_NAME, null, values);

      }

      Cursor getAllValues() {

            return db.query(TABLE_NAME, null, null, null, null, null, null);

      }

      void deleteAllRecord() {

            db.delete(TABLE_NAME, null, null);

      }

}

2)InsertDataActivity:

 

package com.venky.standarddatabase;

import android.app.Activity;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.Toast;

public class InsertData extends Activity {

      EditText edtSname, edtSlocation, edtSphnumber;

      StudentDB db;

      @Override

      public void onCreate(Bundle savedInstanceState) {

            // TODO Auto-generated method stub

            super.onCreate(savedInstanceState);

            db = new StudentDB(getApplicationContext());

            setContentView(R.layout.insert);

            edtSname = (EditText) findViewById(R.id.edtSname);

            edtSlocation = (EditText) findViewById(R.id.edtSlocation);

            edtSphnumber = (EditText) findViewById(R.id.edtSphnmber);

            Button btnSave = (Button) findViewById(R.id.btnInsert);

            btnSave.setOnClickListener(new OnClickListener() {

                  @Override

                  public void onClick(View v) {

                        // TODO Auto-generated method stub

                        String name = edtSname.getText().toString().trim();

                        String location = edtSlocation.getText().toString().trim();

                        String phnumber = edtSphnumber.getText().toString().trim();

                        db.openDatabase();

                        db.insertValues(name, location, phnumber);

                        Toast.makeText(getApplicationContext(),

                                    "Successfully values inserted", 3000).show();

                        edtSname.setText("");

                        edtSlocation.setText("");

                        edtSphnumber.setText("");

                  }

            });

      }

}

3)ViewDataActivity:

package com.venky.standarddatabase;

import android.app.Activity;

import android.database.Cursor;

import android.os.Bundle;

import android.view.Menu;

import android.view.MenuItem;

import android.view.View;

import android.view.ViewGroup;

import android.widget.BaseAdapter;

import android.widget.ListView;

import android.widget.TextView;

import android.widget.Toast;

public class ViewData extends Activity {

      StudentDB db;

      Cursor c;

      ListView listView;

      MyAdapter adapter;

      @Override

      protected void onCreate(Bundle savedInstanceState) {

            // TODO Auto-generated method stub

            super.onCreate(savedInstanceState);

            setContentView(R.layout.view);

            db = new StudentDB(getApplicationContext());

            db.openDatabase();

            listView = (ListView) findViewById(R.id.listView);

            adapter = new MyAdapter();

            updateListView();

      }

      @Override

      public boolean onCreateOptionsMenu(Menu menu) {

            // TODO Auto-generated method stub

            menu.add("Delete All Records");

            return super.onCreateOptionsMenu(menu);

      }

      @Override

      public boolean onOptionsItemSelected(MenuItem item) {

            // TODO Auto-generated method stub

            db.deleteAllRecord();

            Toast.makeText(getApplicationContext(), "Deleted", 3000).show();

            updateListView();

            return super.onOptionsItemSelected(item);

      }

      void updateListView() {

            c = db.getAllValues();

            listView.setAdapter(adapter);

      }

      class MyAdapter extends BaseAdapter {

            @Override

            public int getCount() {

                  // TODO Auto-generated method stub

                  return c.getCount();

            }

            @Override

            public Object getItem(int position) {

                  // TODO Auto-generated method stub

                  return position;

            }

            @Override

            public long getItemId(int position) {

                  // TODO Auto-generated method stub

                  return position;

            }

            @Override

            public View getView(int position, View v, ViewGroup parent) {

                  // TODO Auto-generated method stub

                  v = getLayoutInflater().inflate(R.layout.style, null);

                  TextView tv1 = (TextView) v.findViewById(R.id.tvRowid);

                  TextView tv2 = (TextView) v.findViewById(R.id.tvSname);

                  TextView tv3 = (TextView) v.findViewById(R.id.tvSlocation);

                  TextView tv4 = (TextView) v.findViewById(R.id.tvSphnumber);

                  c.moveToPosition(position);

                  tv1.setText(c.getString(0));

                  tv2.setText(c.getString(1));

                  tv3.setText(c.getString(2));

                  tv4.setText(c.getString(3));

                  return v;

            }

      }

}

4)StandardDatabaseActivity:

package com.venky.standarddatabase;

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

public class StandardDabatabseActivity extends Activity {

      /** Called when the activity is first created. */

      @Override

      public void onCreate(Bundle savedInstanceState) {

            super.onCreate(savedInstanceState);

            setContentView(R.layout.main);

            Button btnInsert = (Button) findViewById(R.id.btnInsert);

            btnInsert.setOnClickListener(new OnClickListener() {

                  @Override

                  public void onClick(View v) {

                        // TODO Auto-generated method stub

                        startActivity(new Intent(getApplicationContext(),

                                    InsertData.class));

                  }

            });

            Button btnView = (Button) findViewById(R.id.btnView);

            btnView.setOnClickListener(new OnClickListener() {

                  @Override

                  public void onClick(View v) {

                        // TODO Auto-generated method stub

                        startActivity(new Intent(getApplicationContext(),

                                    ViewData.class));

                  }

            });

      }

}

 5)main.xml:

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

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:background="@color/color_purple"

    android:orientation="vertical" >

    <Button

        android:id="@+id/btnInsert"

        android:layout_width="100dp"

        android:layout_height="wrap_content"

        android:layout_gravity="center"

        android:layout_marginTop="100dp"

        android:text="Insert"

        android:textSize="20dp" />

    <Button

        android:id="@+id/btnView"

        android:layout_width="100dp"

        android:layout_height="wrap_content"

        android:layout_gravity="center"

        android:layout_marginTop="100dp"

        android:text="View"

        android:textSize="20dp" />

</LinearLayout>

6)insert.xml:

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

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:background="@color/color_purple"

    android:orientation="vertical" >

    <TextView

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Enter Student Name"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText

        android:id="@+id/edtSname"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

        <requestFocus />

    </EditText>

    <TextView

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Enter Slocation"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText

        android:id="@+id/edtSlocation"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" />

    <TextView

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Enter Sphnumber"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText

        android:id="@+id/edtSphnmber"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" />

    <Button

        android:id="@+id/btnInsert"

        android:layout_width="100dp"

        android:layout_height="wrap_content"

        android:layout_gravity="center"

        android:text="Save" />

</LinearLayout>

7)view.xml:

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

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:background="@color/color_purple"

    android:orientation="vertical" >

    <ListView

        android:id="@+id/listView"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

    </ListView>

</LinearLayout>

8)style.xml:

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

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:background="@color/color_purple"

    android:orientation="vertical" >

    <TextView

        android:id="@+id/tvRowid"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Rowid"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <TextView

        android:id="@+id/tvSname"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Sname"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <TextView

        android:id="@+id/tvSlocation"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Slocation"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <TextView

        android:id="@+id/tvSphnumber"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="Sphnumber"

        android:textAppearance="?android:attr/textAppearanceLarge" />

</LinearLayout>

9)Download this Project Click Here