Android Sqlite Tutorial

Android Sqlite Tutorial



Source Code:
https://www.dropbox.com/s/63vufkq32zp5g07/Sqlite.zip?dl=0

Step 1:
We need to create database

package com.example.slide;

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 {

//Database Version
private static final int DATABASE_VERSION = 1;
//Database Name
private static final String DATABASE_NAME = "Test";
//Table Name
private static final String TABLE_TEST = "TestTable";
//Column Name
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_AGE = "age";

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

//Create Table
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_TEST + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_AGE + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}

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

//Insert Value
public void adddata(Context context,String movieId,String songId) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, movieId);
values.put(KEY_AGE, songId);
db.insert(TABLE_TEST, null, values);
db.close();
}

//Get Row Count
public int getCount() {
String countQuery = "SELECT  * FROM " + TABLE_TEST;
int count = 0;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
if(cursor != null && !cursor.isClosed()){
count = cursor.getCount();
cursor.close();
}
return count;
}

//Delete Query
public void removeFav(int id) {
String countQuery = "DELETE FROM " + TABLE_TEST + " where " + KEY_ID + "= " + id ;
SQLiteDatabase db = this.getReadableDatabase();
db.execSQL(countQuery);
}

//Get FavList
public List<FavoriteList> getFavList(){
String selectQuery = "SELECT  * FROM " + TABLE_TEST;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
List<FavoriteList> FavList = new ArrayList<FavoriteList>();
if (cursor.moveToFirst()) {
do {
FavoriteList list = new FavoriteList();
list.setId(Integer.parseInt(cursor.getString(0)));
list.setName(cursor.getString(1));
list.setAge(cursor.getString(2));
FavList.add(list);
} while (cursor.moveToNext());
}
return FavList;
}

}

Step 2:
We need to create Activity with listview using Base Adapter
package com.example.slide;

import java.util.List;

import android.app.Activity;
import android.app.Dialog;
import android.content.Context;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity {
FavoriteList favList = new FavoriteList();
Button add,checkcount,list;
Context context = this;
DatabaseHandler db;
ListView listView;
List<FavoriteList> favoriteList;
LinearLayout layout;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

add = (Button)findViewById(R.id.add);
add.setOnClickListener(addOnClick);
checkcount = (Button)findViewById(R.id.checkcount);
checkcount.setOnClickListener(checkcountOnClick);
list = (Button)findViewById(R.id.list);
list.setOnClickListener(listOnClick);
layout = (LinearLayout)findViewById(R.id.layout);
listView = (ListView)findViewById(R.id.listView);
db = new DatabaseHandler(this);

//db.removeFav();

/*favList = db.getFavList();
Toast.makeText(getApplicationContext(), ""+favList.getSongname(), Toast.LENGTH_LONG).show();*/
}

View.OnClickListener addOnClick = new OnClickListener() {
@Override
public void onClick(View v) {
final Dialog dialog = new Dialog(context);
dialog.setContentView(R.layout.row);
dialog.setTitle("Add Data to Database");
final EditText name = (EditText) dialog.findViewById(R.id.name);
final EditText age = (EditText) dialog.findViewById(R.id.age);
Button Add = (Button) dialog.findViewById(R.id.Add);
Add.setText("Add");
Add.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
if(name.getText().toString() != null && name.getText().toString().length() >0 ){
if(age.getText().toString() != null && age.getText().toString().length() >0 ){
db.adddata(context, name.getText().toString(), age.getText().toString());
favoriteList = db.getFavList();
listView.setAdapter(new ViewAdapter());
dialog.dismiss();
}else{
Toast.makeText(getApplicationContext(), "Please Enter the Age", Toast.LENGTH_LONG).show();
}
}else{
Toast.makeText(getApplicationContext(), "Please Enter the Name", Toast.LENGTH_LONG).show();
}
}
});
dialog.show();
}
};

View.OnClickListener checkcountOnClick = new OnClickListener() {
@Override
public void onClick(View v) {
int uyu = db.getCount();
Toast.makeText(getApplicationContext(), ""+uyu, Toast.LENGTH_LONG).show();
}
};

View.OnClickListener listOnClick = new OnClickListener() {
@Override
public void onClick(View v) {
favoriteList = db.getFavList();
listView.setAdapter(new ViewAdapter());
}
};

public class ViewAdapter extends BaseAdapter {

LayoutInflater mInflater;

public ViewAdapter() {
mInflater = LayoutInflater.from(context);
}

@Override
public int getCount() {
return favoriteList.size();
}

@Override
public Object getItem(int position) {
return null;
}

@Override
public long getItemId(int position) {
return position;
}

@Override
public View getView(final int position, View convertView, ViewGroup parent) {

if (convertView == null) {
convertView = mInflater.inflate(R.layout.listitem,null);
}

final TextView nameText = (TextView) convertView.findViewById(R.id.nameText);
nameText.setText("Name : "+favoriteList.get(position).getName());
final TextView ageText = (TextView) convertView.findViewById(R.id.ageText);
ageText.setText("Age : "+favoriteList.get(position).getAge());

final Button delete = (Button) convertView.findViewById(R.id.delete);
delete.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
db.removeFav(favoriteList.get(position).getId());
notifyDataSetChanged();
favoriteList = db.getFavList();
listView.setAdapter(new ViewAdapter());
}
});
return convertView;
}
}
}


XML files

We need to create activity_main

<?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="#DCDCDC"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical" >

        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:gravity="center_horizontal"
            android:text="Add Data to Database"
            android:textColor="#228B22"
            android:textSize="16dp"
            android:textStyle="bold" />

        <Button
            android:id="@+id/add"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="2dp"
            android:background="@drawable/buttoncolor"
            android:text="Add"
            android:textColor="#FFFFFF" />

        <Button
            android:id="@+id/list"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="2dp"
            android:background="@drawable/buttoncolor"
            android:text="Show List of Data"
            android:textColor="#FFFFFF" />

        <Button
            android:id="@+id/checkcount"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="2dp"
            android:background="@drawable/buttoncolor"
            android:text="Check value Count"
            android:textColor="#FFFFFF" />
    </LinearLayout>

    <LinearLayout
        android:id="@+id/layout"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_margin="5dp"
        android:layout_weight="1" >

        <ListView
            android:id="@+id/listView"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" >
        </ListView>
    </LinearLayout>
</LinearLayout>


BaseAdapter listview XML is listitem.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#FFFFFF"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" >

        <LinearLayout
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:orientation="vertical" >

            <TextView
                android:id="@+id/nameText"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textColor="#228B22" />

            <TextView
                android:id="@+id/ageText"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textColor="#228B22" />
        </LinearLayout>

        <Button
            android:id="@+id/edit"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="2dp"
            android:background="@drawable/buttoncolor"
            android:paddingLeft="5dp"
            android:paddingRight="5dp"
            android:text="Edit"
            android:textColor="#FFFFFF" />

        <Button
            android:id="@+id/delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="2dp"
            android:background="@drawable/buttoncolor"
            android:paddingLeft="5dp"
            android:paddingRight="5dp"
            android:text="Delete"
            android:textColor="#FFFFFF" />
    </LinearLayout>


</LinearLayout>

We need to create Model for storing the data
FavoriteList .java

package com.example.slide;

public class FavoriteList {

//private variables
int id;
String name;
String age;

// Empty constructor
public FavoriteList(){

}
// constructor
public FavoriteList(int id, String name, String age){
this.id = id;
this.name = name;
this.age = age;
}

// getting id
public int getId(){
return this.id;
}

// setting id
public void setId(int id){
this.id = id;
}

// getting name
public String getName(){
return this.name;
}

// setting name
public void setName(String name){
this.name = name;
}

// getting Moviename
public String getAge(){
return this.age;
}

// setting Moviename
public void setAge(String age){
this.age = age;
}

}

I have used buttoncolor.xml

<?xml version="1.0" encoding="UTF-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android"
    android:shape="rectangle" >
 
    <padding android:top="7dp"
        android:bottom="7dp"/>
 
    <corners android:radius="7dp"/>

    <solid android:color="#228B22"/>
 

</shape>

Another one is shape.xml

<?xml version="1.0" encoding="UTF-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android"
    android:shape="rectangle" >
 
    <padding android:top="7dp"
        android:bottom="7dp"/>
 
    <stroke android:color="#228B22" android:width="2dp"/>
 
    <corners android:radius="7dp"/>

    <solid android:color="#FFFFFF"/>
 

</shape>