Working with SQLite on Android
September 26, 2011 13 Comments
From last few month, I was not able to post any new topic. So today thought to post something, but what? Then I remembered how much I had broken my head to start with the SQLite in android.
I had found few difficulties while learning because of:
- All the database operation must has done from code only
- Reading the data from database using ADB sell was difficult
- If I am debugging from mobile, the data base is not accessible only
Later I manage to learn it and build a sample Application before I had to start my project. Here I am going to explain the same project which will insert record and display records in the same interface. It may be helpful for my readers.
Fig : Package Explorer of the example project
Here first step is to create a class that inherits from SQLiteOpenHelper class. This class provides three methods two methods to override and one constructor of super class :
- onCreate(SQLiteDatabase db): invoked when the database is created, this is where we can create tables and columns to them, create views or triggers.
- onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): invoked when we make a modification to the database such as altering, dropping , creating new tables.
- DatabaseHelper(Context context) : invokes to create database by providing the target context with database name and schema version.
(It is better to put the entire database, tables and fields name in static variable so that writing code will be easier)
package com.student; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DatabaseHelper extends SQLiteOpenHelper { static final String db_demo = "db_demo"; static final String tbl_student = "tbl_student"; static String name = "name"; static String t1 = "test1"; static String t2 = "test2"; static String t3 = "test3"; // method to create or connect to a database public DatabaseHelper(Context context) { super(context, db_demo, null, 2); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + tbl_student + "(id INTEGER PRIMARY KEY, " + "name TEXT, " + t1 + " INTEGER, " + t2 + " INTEGER, " + t3 + " INTEGER);"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { android.util.Log.v("Constatnts :", "Upgrading Data, Which Will Destroy All Old Data"); db.execSQL("DROP TABLE IF EXISTS " + tbl_student); onCreate(db); } }
So, here is the class which will take care of creating database, tables and upgrading the database.
Then we will create an Interface where we will write the code for insertion of the data into the table and selecting the data from same table.
The selected data I have displayed into the list view, as I have shown in one of my previous post.
package com.student; import java.util.ArrayList; import java.util.List; import android.app.Activity; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.util.Log; 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.ListView; import android.widget.TextView; import android.widget.Toast; public class Main extends Activity implements OnClickListener { EditText et_name, et_t1, et_t2, et_t3; Button btn_save; ListView listView; String name, t1, t2, t3; List<String> list_name = new ArrayList<String>(); List<Integer> list_t1 = new ArrayList<Integer>(); List<Integer> list_t2 = new ArrayList<Integer>(); List<Integer> list_t3 = new ArrayList<Integer>(); static String[] arr_name; static Integer[] arr_t1; static Integer[] arr_t2; static Integer[] arr_t3; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); et_name = (EditText) findViewById(R.id.et_name); et_t1 = (EditText) findViewById(R.id.et_t1); et_t2 = (EditText) findViewById(R.id.et_t2); et_t3 = (EditText) findViewById(R.id.et_t3); btn_save = (Button) findViewById(R.id.btn_save); btn_save.setOnClickListener(this); //deleteDatabase(); readDBdata(); listView = (ListView) findViewById(R.id.lv_Marks); listView.setAdapter(new EfficientAdapter(this)); } @Override public void onClick(View v) { if (v == btn_save) { if (et_name.length() <= 0 || et_t1.length() <= 0 || et_t2.length() <= 0 || et_t3.length() <= 0) { Toast.makeText(this, "Please fill each field", Toast.LENGTH_SHORT).show(); return; } name = et_name.getText().toString(); t1 = et_t1.getText().toString(); t2 = et_t2.getText().toString(); t3 = et_t3.getText().toString(); DatabaseHelper dbh = new DatabaseHelper(this); SQLiteDatabase db = dbh.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put(dbh.name, name); cv.put(dbh.t1, t1); cv.put(dbh.t2, t2); cv.put(dbh.t3, t3); try { // db.insert(dbh.tbl_student, null, cv); or db.insertOrThrow(dbh.tbl_student, null, cv); } catch (Exception ex) { Toast.makeText(this, "Sorry data can't be saved. error:" + ex.toString(), Toast.LENGTH_SHORT).show(); } finally { db.close(); } Toast.makeText(this, "Data has been saved.", Toast.LENGTH_SHORT) .show(); readDBdata(); listView.setAdapter(new EfficientAdapter(this)); } } public void readDBdata() { DatabaseHelper dbh = new DatabaseHelper(this); SQLiteDatabase db = dbh.getWritableDatabase(); list_name.clear(); list_t1.clear(); list_t2.clear(); list_t3.clear(); Cursor cursor = db.rawQuery("Select " + dbh.name + "," + dbh.t1 + "," + dbh.t2 + "," + dbh.t3 + " from tbl_student;", null); if (cursor.moveToFirst()) { do { list_name.add(cursor.getString(0)); list_t1.add(cursor.getInt(1)); list_t2.add(cursor.getInt(2)); list_t3.add(cursor.getInt(3)); } while (cursor.moveToNext()); } if (cursor != null && !cursor.isClosed()) { cursor.close(); } db.close(); arr_name = new String[list_name.size()]; arr_t1 = new Integer[list_t1.size()]; arr_t2 = new Integer[list_t2.size()]; arr_t3 = new Integer[list_t3.size()]; for (int i = 0; i < list_name.size(); i++) { arr_name[i] = list_name.get(i); arr_t1[i] = list_t1.get(i); arr_t2[i] = list_t2.get(i); arr_t3[i] = list_t3.get(i); } // Toast.makeText(this, list_t3.toString(), Toast.LENGTH_SHORT).show(); } private static class EfficientAdapter extends BaseAdapter { private LayoutInflater mInflater; public EfficientAdapter(Context context) { mInflater = LayoutInflater.from(context); } public int getCount() { return arr_name.length; } public Object getItem(int position) { return position; } public long getItemId(int position) { return position; } public View getView(int position, View convertView, ViewGroup parent) { ViewHolder holder; if (convertView == null) { convertView = mInflater.inflate(R.layout.three_col_row, null); holder = new ViewHolder(); holder.text1 = (TextView) convertView .findViewById(R.id.TextView01); holder.text2 = (TextView) convertView .findViewById(R.id.TextView02); holder.text3 = (TextView) convertView .findViewById(R.id.TextView03); convertView.setTag(holder); } else { holder = (ViewHolder) convertView.getTag(); } holder.text1.setText(arr_name[position]); holder.text2.setText(String.valueOf(arr_t1[position] + ", " + arr_t2[position] + ", " + arr_t3[position])); holder.text3.setText(String.valueOf(((arr_t1[position] + arr_t2[position] + arr_t3[position]) / 3))); return convertView; } static class ViewHolder { TextView text1; TextView text2; TextView text3; } } public void deleteDatabase() { DatabaseHelper dbh = new DatabaseHelper(this); SQLiteDatabase SQLDb = dbh.getWritableDatabase(); dbh.close(); SQLDb.close(); if (this.deleteDatabase(dbh.db_demo)) { Log.d("Droped", "deleteDatabase(): database deleted."); } else { Log.d("Cant Drop", "deleteDatabase(): database NOT deleted."); } } }
This is not the end of the project execution. After developing the SQlite project in android, running the application successfully is one another challenging job.
While running executing the application we should always check the log for the possible errors and exceptions.
To check the data in the database, we have to go through some few steps
Find the location of the database from Windows > Show View > Other > Android > File Explorer
You can find database in,
data>data>com.<project name>.databases>your database
Now we have to use access same location from the ADB shell
To access database data base, go to command prompt and change location to platform-tools inside android-sdk .
Then execute the “adb shell” command and give the location to your database
“sqlite3 /data/data/com.<project_name>/databases/<database_name>”
Now you are into your database, you can execute the SQlite query for the database.
(Note: we can view the database only if we are executing the application in emulator, while executing in mobile viewing the database permission will not be there.)
I’m not surprised that you had difficulty as you indicated in learning to use SQLite in Android. Most of the documentation on it is extremely poor, and unfortunately consists primarily of recipes for different specific applications rather than actual fundamentals. Even though I have used SQLite in both C#, and even in that most objectionable language Objective C with iPhone applications, I have had a devil of a time making sense of the vast majority of Android documentation on the use of SQLite.
you are awesome
Hiii,
i have a doudt on how to store and retrive images into database and show that in a listview with textviews….can u give me a breif description about that i have gone through many codes but i didnt get the exact output of what i want so please help me in doing that..
thanks in advance
Hello Abhinov,
I have not yet tried this. But you can try this URL, I hope this may work for you
http://www.helloandroid.com/tutorials/store-imagesfiles-database
main.xml and three_col_row code ?
main.xml uses three_col_row to display three column into the list.
is three_col_row, make sure you state or what?
Hi, I need your help. Why my data>data does not display the database? What should we do other than add in DatabaseHelper.java, Main.java and 2 xml file? Should I add SQLite on the project on Eclipse?
Hi, I need some help. How can I insert parse xml data from the internet into sqlite?? I’m having a very hard time in this one. Can you send me codes for this?thanks.
By the way, very nice tutorial you got there..
Sorry, I am quite busy these days to give you the source code.
but if you can read the xml file from android, you can put the insertion into the loop and put them into sqlite
Pingback: Populate data in EditText boxes : Android Community - For Application Development
Its awesome and so helpful. Can u help me to read the data in an activity which is inserted in another activity. I want to get data in my next activity in android which i insert in another activity. please help
I guess, static variables can be useful in this case. create a static variable in the next activity, and you can add data before you go to the activity.