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>