Android Basic SQLite Tutorial for beginers
If you are just a bigenner of Android app developer, after some basic apps you'll need to store your data in database like user name and password, high score of the game or much more. Android provides a simple and lite as name SQLite database to manage the Database.
What is SQLite
SQLite is a relational database (RDBMS). what makes SQLite unique is that it is considered as embedded solution. Most database management system such as Oracle, MySQL, and SQL Server are standalone server processes that run independently.
SQLite is actually a library that is linked into applications. All
database operations are handled within the application through calls and
functions contained in the SQLite library.
I am going to show you how to insert,update and delete data in SQLite.
create a new Project in your eclipse called UserDetail
Some Snapes of App
Home page screen shot
New Form Screen shot
// MainActivity.java
package com.example.userdetail;
import java.io.IOException;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;
public class MainActivity extends Activity {
Button newForm,toServer,editmode,delete_db;
DataBaseHelper dbHelper;
SQLiteDatabase mDB;
Cursor mcur;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = new DataBaseHelper(this);
newForm = (Button)findViewById(R.id.nform);
toServer = (Button)findViewById(R.id.sendtoserver);
editmode = (Button)findViewById(R.id.edit);
delete_db = (Button)findViewById(R.id.delete_db);
delete_db.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
try {
// Delete DataBase
mDB = dbHelper.getWritableDatabase();
mDB.execSQL("DROP TABLE "+DataBaseHelper.USER_TABLE);
dbHelper.onCreate(mDB);
} catch (Exception e) {
e.printStackTrace();
}
}
});
editmode.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent intent = new Intent(MainActivity.this,DataList.class);
startActivity(intent);
}
});
newForm.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
Intent form = new Intent(getApplicationContext(),NewForm.class);
form.putExtra("update", false);
startActivity(form);
}
});
toServer.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
dbHelper = new DataBaseHelper(MainActivity.this);
mDB = dbHelper.getReadableDatabase();
String getData = "SELECT "+DataBaseHelper.NAME+","+DataBaseHelper.GENDER+","+DataBaseHelper.AGE+
" From "+DataBaseHelper.USER_TABLE;
mcur = mDB.rawQuery(getData, null);
int totalData = mcur.getCount();
mcur.moveToFirst();
for(int i=0;i<totalData;i++){
String name = mcur.getString(0);
String gender = mcur.getString(1);
int age = mcur.getInt(2);
try {
// put your url here
URL url = new URL("http://www.exmple.com");
HttpURLConnection urlc = (HttpURLConnection) url.openConnection();
urlc.setRequestProperty("Connection", "close");
urlc.setConnectTimeout(1000 * 30); // mTimeout is in seconds
urlc.connect();
if (urlc.getResponseCode() == 200) {
Log.d("Response", "==200");
Toast.makeText(MainActivity.this, "Successfull", Toast.LENGTH_LONG).show();
// Delete DataBase
mDB = dbHelper.getWritableDatabase();
mDB.execSQL("DROP TABLE "+DataBaseHelper.USER_TABLE);
dbHelper.onCreate(mDB);
}
} catch (IOException e) {
Toast.makeText(MainActivity.this, "Faild", Toast.LENGTH_LONG).show();
Log.d("Response", "!=200");
e.printStackTrace();
}
Log.d("DATA", "Name : "+name+", Gender : "+gender+", Age : "+age);
mcur.moveToNext();
mDB.close();
}
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
// NewForm.java // class
package com.example.userdetail;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Toast;
public class NewForm extends Activity{
EditText name,age;
Button savetoDb;
RadioGroup radioSexGroup;
RadioButton radioSexButton;
DataBaseHelper dbHelper;
SQLiteDatabase mDB;
boolean isUpdate;
String id,uName,uAge,uGen;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.form);
name = (EditText)findViewById(R.id.ename);
age = (EditText)findViewById(R.id.eage);
radioSexGroup = (RadioGroup)findViewById(R.id.radiogender);
savetoDb = (Button)findViewById(R.id.savetoSqlite);
isUpdate = getIntent().getExtras().getBoolean("update");
if(isUpdate){
id = getIntent().getExtras().getString("id");
uName = getIntent().getExtras().getString("name");
uAge = getIntent().getExtras().getString("age");
// uGen = getIntent().getExtras().getString("gender");
name.setText(uName);
age.setText(uAge);
// radioSexButton.setText(uGen);
}
savetoDb.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
dbHelper = new DataBaseHelper(NewForm.this);
mDB = dbHelper.getWritableDatabase();
int selectedId = radioSexGroup.getCheckedRadioButtonId();
radioSexButton = (RadioButton)findViewById(selectedId);
String uName = name.getText().toString();
String uSex = radioSexButton.getText().toString();
String uAge = age.getText().toString();
int intAge = Integer.parseInt(uAge);
ContentValues cv = new ContentValues();
cv.put(DataBaseHelper.NAME, uName);
cv.put(DataBaseHelper.GENDER, uSex);
cv.put(DataBaseHelper.AGE,intAge );
if(isUpdate){
// update database with new data
mDB.update(DataBaseHelper.USER_TABLE, cv, DataBaseHelper.KEY_ID +"="+id, null);
}else{
// insert new data into database
mDB.insert(DataBaseHelper.USER_TABLE, null, cv);
}
Toast.makeText(getApplicationContext(),"Name : "+uName+",Age : "+intAge+", Sex :" +radioSexButton.getText(), Toast.LENGTH_LONG).show();
mDB.close();
finish();
}
});
}
}
// DataBaseHelper.java
package com.example.userdetail;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DataBaseHelper extends SQLiteOpenHelper{
public static final int DB_VERSION = 1;
public static final String DATABASE_NAME = "details.db";
public static final String USER_TABLE = "user_detail";
public static final String KEY_ID = "id";
public static final String NAME = "name";
public static final String GENDER = "gender";
public static final String AGE = "age";
public DataBaseHelper(Context ctx) {
super(ctx, DATABASE_NAME, null, DB_VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
String TABLE = "CREATE TABLE "+USER_TABLE+"("+KEY_ID+" INTEGER PRIMARY KEY, "+NAME+" TEXT,"
+GENDER+" INTEGER,"+AGE+" INTEGER"+")";
db.execSQL(TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+USER_TABLE);
onCreate(db);
}
}
// DataList.java
package com.example.userdetail;
import java.util.ArrayList;
import android.app.Activity;
import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Toast;
public class DataList extends Activity{
ListView listv;
DataBaseHelper dbHelper;
SQLiteDatabase mDB;
Cursor mCur;
View v;
private ArrayList<String> userId = new ArrayList<String>();
private ArrayList<String> user_fName = new ArrayList<String>();
private ArrayList<String> user_age = new ArrayList<String>();
private ArrayList<String> user_gender = new ArrayList<String>();
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.displaylist);
listv = (ListView)findViewById(R.id.list);
v = (View)findViewById(R.id.view);
v.setBackgroundColor(Color.BLUE);
// edit datalist
listv.setOnItemClickListener(new OnItemClickListener() {
public void onItemClick(AdapterView <?>args0,View args1,int args2,long args3){
Intent i = new Intent(getApplicationContext(),NewForm.class);
i.putExtra("id", userId.get(args2));
i.putExtra("name", user_fName.get(args2));
i.putExtra("age", user_age.get(args2));
i.putExtra("gender", user_gender.get(args2));
i.putExtra("update", true);
startActivity(i);
}
});
dbHelper = new DataBaseHelper(DataList.this);
displayData();
}
@SuppressWarnings("static-access")
private void displayData() {
dbHelper = new DataBaseHelper(DataList.this);
mDB = dbHelper.getWritableDatabase();
String query = "SELECT "+"* "+"FROM "+DataBaseHelper.USER_TABLE;
mCur = mDB.rawQuery(query, null);
// mCur = mDB.rawQuery("SELECT * FROM "+dbHelper.USER_TABLE,null );
userId.clear();
user_fName.clear();
user_age.clear();
user_gender.clear();
if(mCur.moveToFirst()){
do{
userId.add(mCur.getString(mCur.getColumnIndex(dbHelper.KEY_ID)));
user_fName.add(mCur.getString(mCur.getColumnIndex(dbHelper.NAME)));
user_age.add(mCur.getString(mCur.getColumnIndex(dbHelper.AGE)));
user_gender.add(mCur.getString(mCur.getColumnIndex(dbHelper.GENDER)));
}while(mCur.moveToNext());
}
DisplayAdapter disadpt = new DisplayAdapter(DataList.this,userId, user_fName, user_age,user_gender);
listv.setAdapter(disadpt);
mCur.close();
}
@Override
protected void onResume() {
super.onResume();
displayData();
}
}
// DisplayAdapter
package com.example.userdetail;
import java.util.ArrayList;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
public class DisplayAdapter extends BaseAdapter{
private Context ctx;
private ArrayList<String>id;
private ArrayList<String>name;
private ArrayList<String>age;
private ArrayList<String>gender;
public DisplayAdapter(Context c,ArrayList<String>id,ArrayList<String>name,ArrayList<String>age,ArrayList<String>gen){
this.ctx = c;
this.id = id;
this.name = name;
this.age = age;
this.gender = gen;
}
@Override
public int getCount() {
// TODO Auto-generated method stub
return id.size();
}
@Override
public Object getItem(int arg0) {
// TODO Auto-generated method stub
return null;
}
@Override
public long getItemId(int arg0) {
// TODO Auto-generated method stub
return 0;
}
@Override
public View getView(int pos, View child, ViewGroup parent) {
Holder mHolder;
LayoutInflater layoutinflater;
if(child == null){
layoutinflater = (LayoutInflater)ctx.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
child = layoutinflater.inflate(R.layout.listcell, null);
mHolder = new Holder();
mHolder.txt_id = (TextView)child.findViewById(R.id.txt_id);
mHolder.txt_name = (TextView)child.findViewById(R.id.txt_name);
mHolder.txt_age = (TextView)child.findViewById(R.id.txt_age);
mHolder.txt_gen = (TextView)child.findViewById(R.id.txt_gender);
child.setTag(mHolder);
}else{
mHolder = (Holder)child.getTag();
}
mHolder.txt_id.setText(id.get(pos));
mHolder.txt_name.setText(name.get(pos));
mHolder.txt_age.setText(age.get(pos));
mHolder.txt_gen.setText(gender.get(pos));
return child;
}
public class Holder{
TextView txt_id;
TextView txt_name;
TextView txt_age;
TextView txt_gen;
}
}
/Layout
// activity_main.xml
<LinearLayout 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:orientation="vertical"
android:padding="10dip"
android:gravity="center_vertical">
<Button
android:id="@+id/nform"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="New Form" />
<Button
android:id="@+id/edit"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dip"
android:text="Edit" />
<Button
android:id="@+id/sendtoserver"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dip"
android:text="Send to Server" />
<Button
android:id="@+id/delete_db"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dip"
android:text="Delete DATA" />
</LinearLayout>
// form.xml
<?xml version="1.0" encoding="UTF-8"?>
<LinearLayout 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:orientation="vertical">
<EditText
android:id="@+id/ename"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="50dip"
android:layout_marginLeft="20dip"
android:layout_marginRight="20dip"
android:layout_marginBottom="25dip"
android:hint="Enter Name"
android:inputType="text">
<requestFocus/>
</EditText>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dip"
android:layout_marginRight="20dip"
android:layout_marginBottom="5dip"
android:textSize="15dip"
android:text="Gender:"/>
<RadioGroup
android:id="@+id/radiogender"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="20dip"
android:layout_marginLeft="20dip">
<RadioButton
android:id="@+id/male"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Male"
android:checked="true"/>
<RadioButton
android:id="@+id/female"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="female"/>
</RadioGroup>
<EditText
android:id="@+id/eage"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint=" Age "
android:inputType="number"
android:layout_marginTop="20dip"
android:layout_marginLeft="20dip"
android:layout_marginRight="20dip">
</EditText>
<Button android:id="@+id/savetoSqlite"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="Save Data"
android:layout_marginTop="20dip"
android:layout_marginLeft="20dip"
android:layout_marginRight="20dip"/>
</LinearLayout>
// displaylist.xml
<?xml version="1.0" encoding="UTF-8"?>
<LinearLayout 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:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="#F3CAE5"
android:gravity="center_vertical"
android:orientation="horizontal"
android:padding="8dp" >
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="ID"
android:textColor="#000" />
<TextView
android:text="Name"
android:layout_width="55dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textColor="#000" />
<TextView
android:text="Gender"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textColor="#000" />
<TextView
android:text="Age"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textColor="#000" />
</LinearLayout>
<View android:id="@+id/view"
android:layout_width="fill_parent"
android:layout_height="2dip" />
<ListView
android:id="@+id/list"
android:layout_height="wrap_content"
android:layout_width="match_parent"
android:divider="#8DB3E1"
android:dividerHeight="1dp">
</ListView>
</LinearLayout>
//listcell.xml
<LinearLayout 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:background="#F3CAE5"
android:gravity="center_vertical"
android:orientation="horizontal"
android:padding="8dp" >
<TextView
android:id="@+id/txt_id"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="17dp"
android:textColor="#000" />
<TextView
android:id="@+id/txt_name"
android:layout_width="55dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="17dp"
android:textColor="#000" />
<TextView
android:id="@+id/txt_gender"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="17dp"
android:textColor="#000" />
<TextView
android:id="@+id/txt_age"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="17dp"
android:textColor="#000" />
</LinearLayout>
// UserDetailManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.userdetail"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="18" />
<uses-permission android:name="android.permission.INTERNET"/>
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/userTheme" >
<activity
android:name="com.example.userdetail.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<activity
android:name=".NewForm"/>
<activity
android:name=".DataList"/>
</application>
</manifest>
