{"id":418,"date":"2015-04-07T12:14:03","date_gmt":"2015-04-07T09:14:03","guid":{"rendered":"http:\/\/www.roweb.ro\/blog\/?p=418"},"modified":"2026-04-23T15:31:26","modified_gmt":"2026-04-23T12:31:26","slug":"android-sqlite-database","status":"publish","type":"post","link":"https:\/\/www.roweb.ro\/blog\/android-sqlite-database\/","title":{"rendered":"Android SQLite Database \u2013 Developer Guide: Setup, Best Practices and Performance Tips"},"content":{"rendered":"

Android provides several ways to store user and app data. SQLite is one way of storing the application data, locally, on the device. SQLite supports all the relational database features common on the Oracle databases or Microsoft SQL Server. In order to access this database it must be used the API provided by Android which is available in the android.database.sqlite<\/a> package.<\/p>\n

\"android<\/a><\/p>\n

Image source: google.com<\/small><\/p>\n

DBAdapter<\/h2>\n

When implementing a database on Android, a common practice is to create a class which interacts with the SQLite database and also with the activities of the app. This class is called DBAdapter<\/code> and contains all the necessary code for creating the tables and the assigned fields. All of the operations regarding for inserting, updating, and deleting records are in the DBAdapter<\/code>.<\/p>\n

\"DBAdapter\"<\/a><\/p>\n

Image source: https:\/\/lecturesnippets.com\/<\/a><\/small><\/p>\n

SQLiteOpenHelper Class<\/h2>\n

This class is used for opening, creating and upgrading the database. The method onCreate()<\/code> is the one used when the database table is created. The method onUpgrade()<\/code> is used when the structure of a database table is changed, for example when a new field is added or the type of a field is changed.<\/p>\n

In order to be used, the class SQLiteOpenHelper<\/code> must be extended by a class from the app.<\/p>\n

 <\/p>\n

SQLiteDatabase Class<\/h2>\n

The SQLiteDatabase<\/code> class is used to create a SQLiteDatabase<\/code> object. This object uses the DatabaseHelper<\/code> object to open the writable database. The SQLiteDatabase<\/code> object can then use the standard insert()<\/code>, update()<\/code>, delete()<\/code>, and query()<\/code> operations to manipulate the records within the database table.<\/p>\n

 <\/p>\n

Cursor Class<\/h2>\n

To navigate on the queried database records Android provides a class called Cursor<\/code>. More details on that here<\/a>. The class contains the methods: moveToFirst()<\/code>, moveToNext()<\/code>, moveToPrevious()<\/code> and moveToPosition()<\/code>.<\/p>\n

\"android<\/a><\/p>\n

 <\/p>\n

In this tutorial we will implement an example of storing users <\/em>in the SQLite database. We will use a table named User<\/em><\/strong>. This table contains three fields: id (INT), name (TEXT), phone_number(TEXT).<\/strong><\/p>\n

User Table Structure<\/h2>\n

\"SQLite<\/a><\/p>\n

User.java<\/em><\/strong><\/p>\n

\npublic class User\n{\n    \/\/private variables\n    int _id;\n    String _name;\n    String _phone_number;\n \n    \/\/ Empty constructor\n    public User()\n    {\n    }\n \n    \/\/ constructor\n    public User(int id, String name, String _phone_number)\n    {\n       this._id = id;\n       this._name = name;\n       this._phone_number = _phone_number;\n    }\n \n    \/\/ constructor\n    public User(String name, String _phone_number)\n    {\n       this._name = name;\n       this._phone_number = _phone_number;\n    }\n \n     \/\/ getting ID\n     public int getID()\n     {\n        return this._id;\n     }\n \n     \/\/ setting id\n     public void setID(int id)\n     {\n        this._id = id;\n     }\n\n     \/\/ getting name\n     public String getName()\n     {\n        return this._name;\n     }\n \n     \/\/ setting name\n     public void setName(String name)\n     {\n        this._name = name;\n     } \n\n     \/\/ getting phone number\n     public String getPhoneNumber()\n     {\n        return this._phone_number;\n     }\n\n     \/\/ setting phone number\n     public void setPhoneNumber(String phone_number)\n     {\n        this._phone_number = phone_number;\n     }\n \n     \/\/ toString\n     @Override\n     public String toString()\n     {\n         return \"User [_id=\" + _id + \", _name=\" + _name + \",  _phone_number=\" + _phone_number + \"]\";\n     }\n}<\/code><\/pre>\n
 <\/em><\/strong><\/p>\n
 Declare static variables<\/h3>\n
\/\/ Database Version\nprivate static final int DATABASE_VERSION = 1;\n\n\/\/ Database Name\nprivate static final String DATABASE_NAME = \"DBUsers\";\n\n\/\/ Users table name\nprivate static final String TABLE_User = \"User\";\n\n\/\/ Users Table Columns names\n\/\/ Primary key - User ID\nprivate static final String KEY_ID = \"id\";\n\n\/\/ User Name\nprivate static final String KEY_NAME = \"name\";\n\n\/\/ User Phone Number\nprivate static final String KEY_PH_NO = \"phone_number\";\n<\/code><\/pre>\n
 <\/p>\n
Creating Table USER<\/h3>\n
@Override\npublic void onCreate(SQLiteDatabase db)\n{\n    String CREATE_User_TABLE = \"CREATE TABLE \" + TABLE_User+ \"(\"\n    + KEY_ID + \" INTEGER PRIMARY KEY AUTOINCREMENT,\" + KEY_NAME + \" TEXT,\"\n    + KEY_PH_NO + \" TEXT\" + \")\";\n    db.execSQL(CREATE_UserS_TABLE);\n}\n<\/code><\/pre>\n
 <\/p>\n
Upgrading database<\/h3>\n
Used when you want to change a column type or add new field. First of all you need to save
\nthe current database version to check if you must add some changes<\/p>\n
@Override\npublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)\n{\n    \/\/ Drop older table if existed\n    db.execSQL(\"DROP TABLE IF EXISTS \" + TABLE_UserS);\n\n    \/\/ Create tables again\n    onCreate(db);\n}\n<\/code><\/pre>\n
 <\/p>\n
All CRUD(Create, Read, Update, Delete) Operations<\/h3>\n
Adding new User<\/span><\/em><\/p>\n
void addUser(User User)\n{\n    SQLiteDatabase db = this.getWritableDatabase();\n    ContentValues values = new ContentValues();\n    values.put(KEY_NAME, User.getName()); \/\/ User Name\n    values.put(KEY_PH_NO, User.getPhoneNumber()); \/\/ User Phone\n\n    \/\/ Inserting Row\n    db.insert(TABLE_UserS, null, values); \n    db.close(); \/\/ Closing database connection\n\n}\n<\/code><\/pre>\n
 <\/p>\n
Getting single User by ID<\/span><\/em><\/p>\n
User getUser(int id)\n{\n     SQLiteDatabase db = this.getReadableDatabase(); \n     Cursor cursor = db.query(TABLE_User, new String[] { KEY_ID,\n     KEY_NAME, KEY_PH_NO }, KEY_ID + \"=?\",\n     new String[] { String.valueOf(id) }, null, null, null, null);\n     if (cursor != null)\n     cursor.moveToFirst();\n\n     User User = new User(Integer.parseInt(cursor.getString(0)),\n     cursor.getString(1), cursor.getString(2));\n\n     \/\/ return User\n     return User;\n}\n<\/code><\/pre>\n
 <\/p>\n
Getting All Users<\/span><\/em><\/p>\n
public List<User> getAllUsers()\n{\n    List<User> UserList = new ArrayList<User>();\n\n    \/\/ Select All Users Query\n    String selectQuery = \"SELECT * FROM \" + TABLE_UserS;\n\n    \/\/ Open writable Database\n    SQLiteDatabase db = this.getWritableDatabase();\n    Cursor cursor = db.rawQuery(selectQuery, null);\n\n    \/\/ Looping through all rows and adding to list using the Cursor \/\/ object\n    if (cursor.moveToFirst())\n    {\n      do\n      {\n           \/\/ Getting the current User\n           User User = new User();\n           User.setID(Integer.parseInt(cursor.getString(0)));\n           User.setName(cursor.getString(1));\n           User.setPhoneNumber(cursor.getString(2));\n\n           \/\/ Adding User to list\n           UserList.add(User);\n\n        } while (cursor.moveToNext());\n     }\n      return UserList;\n}\n<\/code><\/pre>\n
Updating single User<\/span><\/em><\/p>\n
public int updateUser(User User)\n{\n    SQLiteDatabase db = this.getWritableDatabase();\n    ContentValues values = new ContentValues();\n    values.put(KEY_NAME, User.getName());\n    values.put(KEY_PH_NO, User.getPhoneNumber());\n\n    \/\/ updating row using the User ID field\n    return db.update(TABLE_User, values, KEY_ID + \" = ?\",\n    new String[] { String.valueOf(User.getID()) });\n}\n<\/code><\/pre>\n
 Deleting single User<\/span><\/em><\/p>\n
public void deleteUser(User User)\n{\n    SQLiteDatabase db = this.getWritableDatabase();\n\n    \/\/deleting row using the User ID field\n    db.delete(TABLE_User, KEY_ID + \" = ?\",\n    new String[] { String.valueOf(User.getID()) });\n\n    db.close();\n}\n<\/code><\/pre>\n
 <\/p>\n
How to use?<\/h2>\n
\"puzzle\"<\/a><\/p>\n
\npublic class AndroidSQLiteActivity extends Activity\n{\n    \/** Called when the activity is first created. *\/\n\n    @Override\n\n    public void onCreate(Bundle savedInstanceState)\n    {\n          super.onCreate(savedInstanceState);\n          setContentView(R.layout.main);\n          DatabaseHandler db = new DatabaseHandler(this);\n\n          \/** CRUD Operations* *\/\n\n          \/\/ Inserting Users\n          Log.d(\"Insert: \", \"Add users ..\");\n          db.addUser(new User(\"User1\", \"9100000000\"));\n          db.addUser(new User(\"User2\", \"9199999999\"));\n          db.addUser(new User(\"User3\", \"9522222222\"));\n          db.addUser(new User(\"User4\", \"9533333333\"));\n\n          \/\/ Get all Users\n          Log.e(\"Reading: \", \"Reading all Users..\");\n          List<User> Users = db.getAllUsers();\n\n           for (User cn : Users)\n           {\n               Log.e(\"Name: \", cn.toString());\n           }\n        }\n}\n<\/code><\/pre>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Android provides several ways to store user and app data. SQLite is one way of storing the application data, locally, on the device. SQLite supports all the relational database features common on the Oracle databases or Microsoft SQL Server. In order to access this database it must be used the API provided by Android which […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[174],"tags":[33,34],"acf":[],"_links":{"self":[{"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/posts\/418"}],"collection":[{"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/comments?post=418"}],"version-history":[{"count":3,"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/posts\/418\/revisions"}],"predecessor-version":[{"id":9987,"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/posts\/418\/revisions\/9987"}],"wp:attachment":[{"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/media?parent=418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/categories?post=418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.roweb.ro\/blog\/wp-json\/wp\/v2\/tags?post=418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}