Hi Friends in this tutorial we will learn about
sqlite database, In this tutorial we learn basic concept of Sqlite Database and how to use of tham.so pleaase read all my steps for learning Sqlite Database.
1. What is Sqlite Database?
SQLite is a open source relational database,it's used to perform database operation on device and its features like SQL syntax, transactions and prepared statements
In android Sqlite database There are several SQLite commands such as these are REATE,SELECT,INSERT.UPDATE and DELETE.
Note - In android Sqlite database is requires limited memory at runtime (approx. 250 KByte)
2. Supporting Data Type in Sqlite database?
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database.SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.
3. What is use of SQLite in Android
SQLite is embedded into every Android device. Using an SQLite database in Android does not require a setup procedure or administration of the database.
You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.
Access to an SQLite database involves accessing the file system. This can be slow. Therefore it is recommended to perform database operations asynchronously.
If your application creates a database, this database is by default saved in the directory DATA/data/APP_NAME/databases/FILENAME.
4.How to use SQLite Database.
here i am providing all basic coding for use of Sqlite database please create all classes (java files)
in your project and use of them.
i am providing you source code blow.
5. Source code
6. Screen Shot for this code
7. Follow step bay step to create app with SQlite Dtabase
1.create modal class for store student detail.
Here is a Modal class in your project
Student.java.
package com.example.model;
import java.io.Serializable;
import java.util.ArrayList;
public class Student implements Serializable
{
private int roll;
private String name,branch,phone;
private ArrayList<StudentMarks> marks;
public Student(int roll, String name, String branch, String phone) {
super();
this.roll = roll;
this.name = name;
this.branch = branch;
this.phone = phone;
}
public Student(int roll, String name, String branch, String phone,
ArrayList<StudentMarks> marks) {
super();
this.roll = roll;
this.name = name;
this.branch = branch;
this.phone = phone;
this.marks = marks;
}
public int getRoll() {
return roll;
}
public void setRoll(int roll) {
this.roll = roll;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBranch() {
return branch;
}
public void setBranch(String branch) {
this.branch = branch;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public ArrayList<StudentMarks> getMarks() {
return marks;
}
public void setMarks(ArrayList<StudentMarks> marks) {
this.marks = marks;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return name;
}
}
2. Defining database helper class
At this point we have to write our own class to create and update database using the SQLiteOpenHelper:
MyDBHelper.java
Example :
package com.example.helper;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class MyDBHelper extends SQLiteOpenHelper
{
public static final String DBNAME = "StudentDB";
public static final int DBVER = 1;
public MyDBHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL("create table student (roll integer primary key,name text,branch text,phone text)");
db.execSQL("create table studmarks (mid integer primary key autoincrement,roll integer,date text,marks real)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
if(oldVersion==1 && newVersion==2)
{}
if(oldVersion==1 && newVersion==3)
{}
if(oldVersion==2 && newVersion==3)
{}
}
}
4.Create database manager class
At this point we have to write our own class to handle database operations such as creation, upgrading, reading and writing. Database operations are defined using the
MyDBHelpe class:
package com.example.manager;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.helper.MyDBHelper;
import com.example.model.Student;
public class StudentManager
{
private MyDBHelper helper;
private SQLiteDatabase db;
private Context ctx;
public StudentManager(Context ctx)
{
this.ctx = ctx;
helper = new MyDBHelper(ctx, MyDBHelper.DBNAME,null, MyDBHelper.DBVER);
}
public boolean addStudent(Student stud)
{
db = helper.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("roll",stud.getRoll());
cv.put("name",stud.getName());
cv.put("branch",stud.getBranch());
cv.put("phone",stud.getPhone());
long x = db.insert("student", null, cv);
db.close();
if(x>0)
return true;
return false;
}
public ArrayList<Student> getStudents()
{
ArrayList<Student> list = new ArrayList<Student>();
db = helper.getReadableDatabase();
Cursor cr = db.rawQuery("select * from student",null);
while(cr.moveToNext())
{
int roll = cr.getInt(0);
String name = cr.getString(1);
String branch = cr.getString(2);
String phone = cr.getString(3);
Student s = new Student(roll, name, branch, phone);
list.add(s);
}
cr.close();
db.close();
return list;
}
public void deleteStudent(int roll)
{
db = helper.getWritableDatabase();
db.delete("student","roll=?",new String[]{roll+""});
db.close();
}
public void updateStudent(Student s)
{
db = helper.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("name", s.getName());
cv.put("branch", s.getBranch());
cv.put("phone", s.getPhone());
db.update("student", cv, "roll=?",new String[]{s.getRoll()+""});
db.close();
}
public ArrayList<Student> getStudentByRoll(int roll)
{
ArrayList<Student> s = new ArrayList<Student>();
db = helper.getReadableDatabase();
Cursor c = db.rawQuery("select * from student where roll="+roll, null);
if(c.moveToNext())
{
int r = c.getInt(0);
String n = c.getString(1) ;
String b = c.getString(2) ;
String p = c.getString(3) ;
s.add(new Student(r, n, b, p));
}
db.close();
return s;
}
}
5.create main_Activity
Here is a main Activity for showing all students list with the help of ListActivity and also use Option Menu like show on Screen
MainActivity.java
package com.example.view;
import java.util.ArrayList;
import android.app.AlertDialog;
import android.app.ListActivity;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.ListView;
import com.example.facultyapp.R;
import com.example.manager.StudentManager;
import com.example.model.Student;
import com.example.model.StudentAdapter;
public class MainActivity extends ListActivity
{
ListView lv;
ArrayList<Student> studList;
StudentAdapter sadp;
StudentManager sm;
public int updatepos;
public void deleteStud(Student s)
{
studList.remove(s);
sadp.notifyDataSetChanged();
}
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
sm = new StudentManager(this);
lv = getListView();
studList = sm.getStudents();
sadp = new StudentAdapter(studList, this);
lv.setAdapter(sadp);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.main, menu);
return super.onCreateOptionsMenu(menu);
}
@Override
public boolean onOptionsItemSelected(MenuItem item)
{
int id = item.getItemId();
switch(id)
{
case R.id.addStud:
Intent in = new Intent(MainActivity.this,AddStudentActivity.class);
startActivityForResult(in, 11);
break;
case R.id.searchStud:
openDialog();
break;
case R.id.allStud:
studList.clear();
sadp.clear();
studList = sm.getStudents();
sadp = new StudentAdapter(studList, MainActivity.this);
lv.setAdapter(sadp);
break;
}
return super.onOptionsItemSelected(item);
}
private void openDialog()
{
AlertDialog.Builder bd = new AlertDialog.Builder(MainActivity.this);
bd.setTitle("Search Student");
final EditText et = new EditText(MainActivity.this);
bd.setView(et);
bd.setPositiveButton("Roll", new DialogInterface.OnClickListener()
{
@Override
public void onClick(DialogInterface dialog, int which) {
studList.clear();
sadp.clear();
int roll = Integer.parseInt(et.getText().toString());
studList = sm.getStudentByRoll(roll);
sadp = new StudentAdapter(studList, MainActivity.this);
lv.setAdapter(sadp);
}
});
bd.setNegativeButton("Branch", new DialogInterface.OnClickListener()
{
@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
}
});
AlertDialog dlg = bd.create();
dlg.show();
}
@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data)
{
if(requestCode==11 && resultCode==RESULT_OK)
{
Student s = (Student) data.getSerializableExtra("stud");
studList.add(s);
sadp.notifyDataSetChanged();
}
if(requestCode==12 && resultCode==RESULT_OK)
{
Student s = (Student) data.getSerializableExtra("s");
studList.set(updatepos, s);
sadp.notifyDataSetChanged();
}
super.onActivityResult(requestCode, resultCode, data);
}
}
23.Create Layout for adaptor view (Row for list of students)
this is XML coding for show item on the list view studview.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:orientation="horizontal" >
<TextView
android:id="@+id/nameTv"
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="Large Text"
android:textAppearance="?android:attr/textAppearanceLarge" />
<Button
android:id="@+id/viewBT"
style="?android:attr/buttonStyleSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="View" />
<Button
android:id="@+id/updateBT"
style="?android:attr/buttonStyleSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Update" />
<Button
android:id="@+id/delBT"
style="?android:attr/buttonStyleSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete" />
</LinearLayout>
45. Create Adaptor
At this point we are creating Adaptor using BaseAdaptor StudentAdapter.java
package com.example.model;
import java.util.ArrayList;
import android.app.AlertDialog;
import android.content.Context;
import android.content.DialogInterface;
import android.content.Intent;
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.TextView;
import com.example.facultyapp.R;
import com.example.manager.StudentManager;
import com.example.view.MainActivity;
import com.example.view.UpdateActivity;
public class StudentAdapter extends BaseAdapter
{
private ArrayList<Student> list;
private Context ctx;
public StudentAdapter(ArrayList<Student> list, Context ctx) {
super();
this.list = list;
this.ctx = ctx;
}
@Override
public int getCount() {
// TODO Auto-generated method stub
return list.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(final int position, View convertView, ViewGroup parent) {
//
LayoutInflater inf = (LayoutInflater) ctx.getSystemService(ctx.LAYOUT_INFLATER_SERVICE);
View vw = inf.inflate(R.layout.studview, null);
TextView tv = (TextView) vw.findViewById(R.id.nameTv);
Button viewBT = (Button) vw.findViewById(R.id.viewBT);
Button delBT = (Button) vw.findViewById(R.id.delBT);
Button upBT = (Button) vw.findViewById(R.id.updateBT);
final Student s = list.get(position);
upBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v)
{
Intent in = new Intent(ctx, UpdateActivity.class);
in.putExtra("stud",s);
MainActivity act = (MainActivity) ctx;
act.updatepos=position;
act.startActivityForResult(in, 12);
}
});
delBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v)
{
int roll = s.getRoll();
StudentManager sm = new StudentManager(ctx);
sm.deleteStudent(roll);
MainActivity act = (MainActivity) ctx;
act.deleteStud(s);
}
});
viewBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v)
{
AlertDialog.Builder bd = new AlertDialog.Builder(ctx);
bd.setTitle("Student..");
TextView tv = new TextView(ctx);
String data = "Roll - " + s.getRoll() + "\n";
data += "NAme - " + s.getName() + "\n";
data += "Phone - " + s.getPhone() + "\n";
data += "Branch - " + s.getBranch() + "\n";
tv.setText(data);
bd.setView(tv);
bd.setPositiveButton("Close",new DialogInterface.OnClickListener()
{
@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
}
});
AlertDialog dlg = bd.create();
dlg.show();
}
});
tv.setText(s.getName());
return vw;
}
public void clear() {
// TODO Auto-generated method stub
list.clear();
}
}
6.Create Layout for add student detail
Here an Example of XML file for creating layout of add new students. addstud.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:orientation="vertical" >
<EditText
android:id="@+id/rollET"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="Roll Number"
android:layout_marginTop="10dp"
>
<requestFocus />
</EditText>
<EditText
android:id="@+id/nameET"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="Student Name"
android:layout_marginTop="10dp"
/>
<Spinner
android:id="@+id/branchSP"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"/>
<EditText
android:id="@+id/phoneET"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:layout_marginTop="10dp"
android:hint="Phone Number" />
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp">
<Button
android:id="@+id/saveBT"
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="Save" />
<Button
android:id="@+id/canBT"
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="Cancel" />
</LinearLayout>
</LinearLayout>
6. insert New DATA
here we are creating new Activity AddStudentActivity.java for inser data on Sqlite database.
package com.example.view;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;
import com.example.facultyapp.R;
import com.example.manager.StudentManager;
import com.example.model.Student;
public class AddStudentActivity extends Activity
{
EditText rollET,nameET,phoneET;
Spinner branchSP;
Button saveBT,canBT;
String [] branch =
{
"CS","EC","IT","ME","CE","EE"
};
ArrayAdapter<String> branchadp;
StudentManager sm;
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.addstud);
rollET = (EditText) findViewById(R.id.rollET);
nameET = (EditText) findViewById(R.id.nameET);
phoneET = (EditText) findViewById(R.id.phoneET);
branchSP = (Spinner) findViewById(R.id.branchSP);
saveBT = (Button) findViewById(R.id.saveBT);
canBT = (Button) findViewById(R.id.canBT);
sm = new StudentManager(this);
branchadp = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item,branch);
branchSP.setAdapter(branchadp);
saveBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v)
{
int roll = Integer.parseInt(rollET.getText().toString());
String name = nameET.getText().toString();
String branch = (String) branchSP.getSelectedItem();
String phone = phoneET.getText().toString();
Student stud = new Student(roll, name, branch, phone);
boolean check = sm.addStudent(stud);
if(check)
{
Toast.makeText(AddStudentActivity.this, "Record Insert !",2).show();
Intent in = new Intent();
in.putExtra("stud", stud);
setResult(RESULT_OK, in);
}
else
Toast.makeText(AddStudentActivity.this, "Record Insert Failed !",2).show();
finish();
}
});
canBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
finish();
}
});
}
}
7.Create Layout for Update Student Detail
here is XML Coding for updates students data by using EditText
updatestud.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:orientation="vertical" >
<EditText
android:id="@+id/rollET"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="Roll Number"
android:layout_marginTop="10dp"
android:editable="false"
>
<requestFocus />
</EditText>
<EditText
android:id="@+id/nameET"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="Student Name"
android:layout_marginTop="10dp"
/>
<Spinner
android:id="@+id/branchSP"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"/>
<EditText
android:id="@+id/phoneET"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:ems="10"
android:layout_marginTop="10dp"
android:hint="Phone Number" />
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp">
<Button
android:id="@+id/updateBT"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="Update" />
<Button
android:id="@+id/canBT"
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="Cancel" />
</LinearLayout>
</LinearLayout>
7. For Update screen
At this screen we can update database using
UpdateActivity class
package com.example.view;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import com.example.facultyapp.R;
import com.example.manager.StudentManager;
import com.example.model.Student;
public class UpdateActivity extends Activity {
EditText rollET,nameET,phoneET;
Spinner branchSP;
Button upBT,canBT;
String [] branch =
{
"CS","EC","IT","ME","CE","EE"
};
ArrayAdapter<String> branchadp;
StudentManager sm;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.updatestud);
rollET = (EditText) findViewById(R.id.rollET);
nameET = (EditText) findViewById(R.id.nameET);
phoneET = (EditText) findViewById(R.id.phoneET);
branchSP = (Spinner) findViewById(R.id.branchSP);
upBT = (Button) findViewById(R.id.updateBT);
canBT = (Button) findViewById(R.id.canBT);
sm = new StudentManager(this);
branchadp = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item,branch);
branchSP.setAdapter(branchadp);
//creating a reference of Intent for getting data from previous Activity
Intent in = getIntent();
Student s = (Student) in.getSerializableExtra("stud");
rollET.setText(s.getRoll()+"");
nameET.setText(s.getName());
phoneET.setText(s.getPhone());
String b = s.getBranch();
for(int i=0;i<branch.length;i++)
{
if(b.equals(branch[i]))
{
branchSP.setSelection(i);
break;
}
}
canBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
finish();
}
});
//click on update button
upBT.setOnClickListener(new OnClickListener()
{
@Override
public void onClick(View v)
{
int roll = Integer.parseInt(rollET.getText().toString());
String name = nameET.getText().toString();
String branch = (String) branchSP.getSelectedItem();
String phone = phoneET.getText().toString();
Student s = new Student(roll, name, branch, phone);
//call a method for update database
sm.updateStudent(s);
Intent in = new Intent();
in.putExtra("s",s);
setResult(RESULT_OK, in);
finish();
}
});
}
}
ScreenShot :-
Source code
if you if any an issue regarding this post please give me comment on comment box?
Thank You for reading my blog?
