SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.
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.
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.
The parts of the above directory are constructed based on the following rules. DATA
is the path which the Environment.getDataDirectory()
method returns. APP_NAME
is your application name. FILENAME
is the name you specify in your application code for the database.
The android.database
package contains all necessary classes for working with databases. The android.database.sqlite
package contains the SQLite specific classes.
To create and upgrade a database in your Android application you create a subclass of the SQLiteOpenHelper
class. In the constructor of your subclass you call the super()
method of SQLiteOpenHelper
, specifying the database name and the current database version.
In this class you need to override the following methods to create and update your database.
onCreate()
– is called by the framework, if the database is accessed but not yet created.onUpgrade()
– called, if the database version is increased in your application code. This method allows you to update an existing database schema or to drop the existing database and recreate it via the onCreate()
method.Both methods receive an SQLiteDatabase
object as parameter which is the Java representation of the database.
The SQLiteOpenHelper
class provides the getReadableDatabase()
and getWriteableDatabase()
methods to get access to an SQLiteDatabase
object either in read or write mode.
The database tables should use the identifier _id
for the primary key of the table. Several Android functions rely on this standard.
SQLiteDatabase
is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.
More specifically SQLiteDatabase
provides the insert()
, update()
and delete()
methods.
In addition it provides the execSQL()
method, which allows to execute an SQL statement directly.
The object ContentValues
allows to define key/values. The key
represents the table column identifier and the value
represents the content for the table record in this column. ContentValues
can be used for inserts and updates of database entries.
Queries can be created via the rawQuery()
and query()
methods or via the SQLiteQueryBuilder
class .
rawQuery()
directly accepts an SQL select statement as input.
query()
provides a structured interface for specifying the SQL query.
SQLiteQueryBuilder
is a convenience class that helps to build SQL queries.
The following gives an example of a rawQuery()
call.
1 2 |
Cursor cursor = getReadableDatabase(). rawQuery("select * from table_name where _id = ?", new String[] { id }); |
The following gives an example of a query()
call.
1 2 3 |
return database.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY}, null, null, null, null, null); |
A query returns a Cursor
object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.
To get the number of elements of the resulting query use the getCount()
method.
To move between individual data rows, you can use the moveToFirst()
and moveToNext()
methods. The isAfterLast()
method allows to check if the end of the query result has been reached.
Cursor
provides typed get*()
methods, e.g. getLong(columnIndex)
, getString(columnIndex)
to access the column data for the current position of the result. The “columnIndex” is the number of the column you are accessing.
Cursor
also provides the getColumnIndexOrThrow(String)
method which allows to get the column index for a column name of the table.
A Cursor
needs to be closed with the close()
method call.
ListViews
are Views
which allow to display a list of elements.
ListActivities
are specialized activities which make the usage of ListViews
easier.
To work with databases and ListViews
you can use the SimpleCursorAdapter
. The SimpleCursorAdapter
allows to set a layout for each row of the ListViews
.
You also define an array which contains the column names and another array which contains the IDs of Views
which should be filled with the data.
The SimpleCursorAdapter
class will map the columns to the Views
based on the Cursor
passed to it.
To obtain the Cursor
you should use the Loader
class.
In this example i will be demonstrating how to work on SQLite Database i.e. local database for storing data and fetching data again.
Let’s get start by creating a project in Eclipse IDE.
1. Create a new project in Eclipse by navigating to File ⇒ New Android ⇒ Application Project and fill required details. (I kept my main activity name as MainActivity.java)
2. Open your your AndroidManifest.xml file and make your “MainActivity” as Launcher activity.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.sqllite_demo" android:versionCode="1" android:versionName="1.0" > <uses-sdk android:minSdkVersion="8" android:targetSdkVersion="22" /> <application android:allowBackup="true" android:icon="@drawable/ic_launcher" android:label="@string/app_name" android:theme="@style/AppTheme" > <activity android:name=".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> </application> </manifest> |
3. Create a new folder in res folder naming drawable by right clicking on res ⇒ New ⇒ Folder . In that folder create a new xml file for button selector naming button_selector.xml and set it as background to buttons. We can go beyond just displaying image, where Android allows us to differentiate different states of the selected button.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<?xml version="1.0" encoding="utf-8"?> <selector xmlns:android="http://schemas.android.com/apk/res/android"> <item android:state_pressed="true"><shape> <solid android:color="#0032C3" /> <stroke android:width="1dp" android:color="#0032C3" /> <corners android:radius="6dp" /> <padding android:bottom="3dp" android:left="3dp" android:right="3dp" android:top="3dp" /> </shape></item> <item><shape> <gradient android:angle="270" android:endColor="#1589FF" android:startColor="#1589FF" /> <stroke android:width="1dp" android:color="#1589FF" /> <corners android:radius="6dp" /> <padding android:bottom="3dp" android:left="3dp" android:right="3dp" android:top="3dp" /> </shape></item> </selector> |
4. Create a layout file for MainActivtiy.java under res ⇒ layout folder. I named the layout file as activity_main.xml. This layout contains some edittexts, buttons and a listview for displaying saved data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="fill_parent" android:layout_height="fill_parent" android:background="#ffffff" android:orientation="vertical" android:padding="5dp" > <EditText android:id="@+id/name" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:hint="@string/name" android:inputType="text" android:padding="10dp" android:textColor="#000000" android:textSize="18sp" /> <EditText android:id="@+id/email" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:hint="@string/email" android:inputType="textEmailAddress" android:padding="10dp" android:textColor="#000000" android:textSize="18sp" /> <EditText android:id="@+id/address" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:hint="@string/address" android:inputType="text" android:padding="10dp" android:textColor="#000000" android:textSize="18sp" > </EditText> <LinearLayout android:layout_width="fill_parent" android:layout_height="wrap_content" android:orientation="horizontal" > <Button android:id="@+id/save" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="3dp" android:layout_weight="1" android:background="@drawable/button_selector" android:text="@string/save" android:textColor="#ffffff" android:textSize="15sp" /> <Button android:id="@+id/show" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="3dp" android:layout_weight="1" android:background="@drawable/button_selector" android:text="@string/show" android:textColor="#ffffff" android:textSize="15sp" /> <Button android:id="@+id/delete" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="3dp" android:layout_weight="1" android:background="@drawable/button_selector" android:text="@string/delete" android:textColor="#ffffff" android:textSize="15sp" /> </LinearLayout> <ListView android:id="@+id/show_saved_data" android:layout_width="fill_parent" android:layout_height="wrap_content" android:layout_margin="3dp" android:divider="#ff00" android:dividerHeight="1dp" android:visibility="gone" > </ListView> </LinearLayout> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
package com.sqllite_demo; public class Data_Model { String name, email, address; // Setter for database public Data_Model(String name, String email, String address) { this.name = name; this.email = email; this.address = address; } // Getter public String getName() { return name; } public String getEmail() { return email; } public String getAddress() { return address; } } |
6. Now make a Database_Helper.java file for SQLite Database for storing the data and reading data back. For this class we extend the class with SQLiteOpenHelper.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
package com.sqllite_demo; 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 Database_Helper extends SQLiteOpenHelper { // Database Name private static final String Database_Name = "Androhub"; // Database Version private static final int Database_Version = 1; // Table Name private static final String Table_Name = "User_Data"; // Column Id Primary Key private static final String Column_Id = "id"; // Table fields private static final String Name = "name"; private static final String Email = "Email"; private static final String Address = "Address"; // Create table query private static final String Create_Table = "Create table " + Table_Name + " ( " + Column_Id + " integer primary key autoincrement, " + Name + " text not null, " + Email + " text not null, " + Address + " text not null );"; // Drop/delete table query private static final String Drop_Table = "Drop table if exists " + Table_Name; public Database_Helper(Context context) { super(context, Database_Name, null, Database_Version); // TODO Auto-generated constructor stub } @Override public void onCreate(SQLiteDatabase db) { // executing the created table query db.execSQL(Create_Table); } @Override public void onUpgrade(SQLiteDatabase db, int olderVersion, int newVersion) { // executing the drop table query if database version is changed db.execSQL(Drop_Table); onCreate(db); } // Insert data into database method public void insertData(Data_Model data) { // Accessing SQL database to write data SQLiteDatabase db = this.getWritableDatabase(); // Content values used for editing/writing data into database ContentValues values = new ContentValues(); // Putting datas into content values values.put(Name, data.getName()); values.put(Email, data.getEmail()); values.put(Address, data.getAddress()); // Now inserting content values data into table db.insert(Table_Name, null, values); // Closing database after using db.close(); } // Getting all saved data public List<Data_Model> getAllData() { // Data model list in which we have to return the data List<Data_Model> data = new ArrayList<Data_Model>(); // Accessing database for reading data SQLiteDatabase db = this.getReadableDatabase(); // Select query for selecting whole table data String select_query = "Select * from " + Table_Name; // Cursor for traversing whole data into database Cursor cursor = db.rawQuery(select_query, null); try { // check if cursor move to first if (cursor.moveToFirst()) { // looping through all data and adding to arraylist do { Data_Model data_model = new Data_Model(cursor.getString(1), cursor.getString(2), cursor.getString(3)); data.add(data_model); } while (cursor.moveToNext()); } } finally { // After using cursor we have to close it cursor.close(); } // Closing database db.close(); // returning list return data; } // Deleting table from database public void deleteTable() { SQLiteDatabase db = this.getWritableDatabase(); // Deleting table db.delete(Table_Name, null, null); // Closing database db.close(); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 |
package com.sqllite_demo; import java.util.ArrayList; import java.util.List; import android.os.Bundle; import android.support.v7.app.ActionBarActivity; import android.text.TextUtils; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; import android.widget.Toast; public class MainActivity extends ActionBarActivity implements OnClickListener { EditText name, email, address; Button save, show, delete; ListView show_data_list; Database_Helper database; // Variable for database table fields size int data_size = 0; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); // Calling init method init(); } void init() { name = (EditText) findViewById(R.id.name); email = (EditText) findViewById(R.id.email); address = (EditText) findViewById(R.id.address); save = (Button) findViewById(R.id.save); show = (Button) findViewById(R.id.show); delete = (Button) findViewById(R.id.delete); show_data_list = (ListView) findViewById(R.id.show_saved_data); // Initializing database database = new Database_Helper(MainActivity.this); // Implementing click listener to all buttons save.setOnClickListener(this); show.setOnClickListener(this); delete.setOnClickListener(this); } @Override public void onClick(View view) { switch (view.getId()) { case R.id.save: // Getting edittext fields texts into string String getName = name.getText().toString(); String getEmail = email.getText().toString(); String getAddress = address.getText().toString(); // Check if all edit text is filled or not if (TextUtils.isEmpty(getName) || TextUtils.isEmpty(getEmail) || TextUtils.isEmpty(getAddress)) { // Showing a toast if any of them fields empty Toast.makeText(MainActivity.this, "All fields are necessary.", Toast.LENGTH_SHORT).show(); } else { // Insert data into database database.insertData(new Data_Model(getName, getEmail, getAddress)); // Toast for successfully saved data Toast.makeText(MainActivity.this, "Data saved successfully.", Toast.LENGTH_SHORT).show(); } break; case R.id.show: // Getting stored data from database in list List<Data_Model> list = database.getAllData(); // Arraylist for storing data to show over listview ArrayList<Data_Model> new_list = new ArrayList<Data_Model>(); // getting list size and stored in data_size data_size = list.size(); // Checked if database contains data or not if (data_size == 0) { // If data is not contained means 0 then show a toast Toast.makeText(MainActivity.this, "There is no data in table.", Toast.LENGTH_SHORT).show(); // Check if listview is shown or not if (show_data_list.isShown()) { // Hide listview show_data_list.setVisibility(View.GONE); } } else { // For each for all data in database for (Data_Model data : list) { // Getting data from database String stored_name = data.getName(); String stored_email = data.getEmail(); String stored_address = data.getAddress(); // Adding data to array list new_list.add(new Data_Model(stored_name, stored_email, stored_address)); } // Custom adapter for setting array list Custom_Adapter adapter = new Custom_Adapter(MainActivity.this, new_list); // setting adapter over listview show_data_list.setAdapter(adapter); // Notifying adapter adapter.notifyDataSetChanged(); // Showing listview show_data_list.setVisibility(View.VISIBLE); } break; case R.id.delete: // Check if database contains data or not if (data_size == 0) { // If there is no data toast is shown Toast.makeText(MainActivity.this, "There is nothing to delete.", Toast.LENGTH_SHORT) .show(); } else { // else data is deleted and hide the listview Toast.makeText(MainActivity.this, "Data deleted Successfully.", Toast.LENGTH_SHORT).show(); database.deleteTable(); show_data_list.setVisibility(View.GONE); } break; } } } |
8. For displaying data in ListView i make a custom layout file under res ⇒ layout folder. I named the layout file as customview_for_listview.xml.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?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" > <TextView android:id="@+id/all_data" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="TextView" /> </LinearLayout> |
9. Finally make a Custom_Adapter.java for ListView and add the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
package com.sqllite_demo; 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 Custom_Adapter extends BaseAdapter { ArrayList<Data_Model> arrayList; LayoutInflater inflater; ViewHolder holder = null; public Custom_Adapter(Context context, ArrayList<Data_Model> arrayList) { this.arrayList = arrayList; this.inflater = (LayoutInflater) context .getSystemService(Context.LAYOUT_INFLATER_SERVICE); } @Override public int getCount() { // TODO Auto-generated method stub return arrayList.size(); } @Override public Object getItem(int pos) { // TODO Auto-generated method stub return arrayList.get(pos); } @Override public long getItemId(int pos) { // TODO Auto-generated method stub return pos; } @Override public View getView(int pos, View view, ViewGroup root) { if (view == null) { view = inflater.inflate(R.layout.customview_for_listview, root, false); holder = new ViewHolder(); holder.text = (TextView) view.findViewById(R.id.all_data); view.setTag(holder); } else { holder = (ViewHolder) view.getTag(); } // Getting all fields from array list and stored in strings String name = arrayList.get(pos).getName(); String email = arrayList.get(pos).getEmail(); String address = arrayList.get(pos).getAddress(); // Data is displayed over textView holder.text.setText("Name : " + name + "\n" + "Email : " + email + "\n" + "Address : " + address); return view; } // View holder for holding view public class ViewHolder { TextView text; } } |
10. Run the application and you will get the output as shown in video and you are done.
Thanks. 🙂
Subscribe to us and get the latest news.