Android SQLite Example

Android SQLite Example

Sqlite helper class helps us to manage database creation and version management.
SQLiteOpenHelper takes care of all database management activities. To use it,

1.Override onCreate(), onUpgrade() methods of SQLiteOpenHelper. Optionally override onOpen() method.

2.Use this subclass to create either a readable or writable database and use the SQLiteDatabase's four API methods insert(), execSQL(), update(), delete() to create, read, update and delete rows of your table.

Example to create a MyEmployees table and to select and insert records:

public class MyDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "DBName";

    private static final int DATABASE_VERSION = 2;

    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table MyEmployees
                                 ( _id integer primary key,name text not null);";

    public MyDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Method is called during creation of the database
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    // Method is called during an upgrade of the database,
    @Override
    public void onUpgrade(SQLiteDatabase database,int oldVersion,int newVersion){
        Log.w(MyDatabaseHelper.class.getName(),
                         "Upgrading database from version " + oldVersion + " to "
                         + newVersion + ", which will destroy all old data");
        database.execSQL("DROP TABLE IF EXISTS MyEmployees");
        onCreate(database);
    }
}

Now you can use this class as below,

public class MyDB{  

    private MyDatabaseHelper dbHelper;  

    private SQLiteDatabase database;  

    public final static String EMP_TABLE="MyEmployees"; // name of table 

    public final static String EMP_ID="_id"; // id value for employee
    public final static String EMP_NAME="name";  // name of employee

    /** 
     * 
     * @param context 
     */  
    public MyDB(Context context){  
        dbHelper = new MyDatabaseHelper(context);  
        database = dbHelper.getWritableDatabase();  
    }


    public long createRecords(String id, String name){  
        ContentValues values = new ContentValues();  
        values.put(EMP_ID, id);  
        values.put(EMP_NAME, name);  
        return database.insert(EMP_TABLE, null, values);  
    }    

    public Cursor selectRecords() {
        String[] cols = new String[] {EMP_ID, EMP_NAME};  
        Cursor mCursor = database.query(true, EMP_TABLE,cols,null  
            , null, null, null, null, null);  
        if (mCursor != null) {  
            mCursor.moveToFirst();  
        }  
        return mCursor; // iterate to get each value.
    }
}

Now you can use MyDB class in you activity to have all the database operations. The create records will help you to insert the values similarly you can have your own functions for update and delete.

How do I use SQLite in android?

SQLite is a opensource SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation.

Please check below links

1. Android SQLite Database Tutorial

2. SQLite Database Tutorial

3. SQLite and Android

Structure

 public class MySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_COMMENTS = "comments";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_COMMENT = "comment";

  private static final String DATABASE_NAME = "commments.db";
  private static final int DATABASE_VERSION = 1;

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
      + TABLE_COMMENTS + "(" + COLUMN_ID
      + " integer primary key autoincrement, " + COLUMN_COMMENT
      + " text not null);";

  public MySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
    onCreate(db);
  }

} 

Retrieve data from SQLite database and update it

Your table creation is wrong

 String CREATE_USER_CONTACTS_TABLE = "CREATE TABLE " + TABLE_USER + "("
        + USER_ID + " INTEGER PRIMARY KEY   AUTOINCREMENT," + USER_NAME 
   + " TEXT,"
        + USER_PH_NO + " TEXT" + USER_DISPLAY_NAME + " TEXT" +  
        USER_DISPLAY_PICTURE + " TEXT" + ")";

You are missing some commas. Try this

 String CREATE_USER_CONTACTS_TABLE = "CREATE TABLE " + TABLE_USER + " (" +
        USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + USER_NAME +
        " TEXT, " + USER_PH_NO + " TEXT, " + USER_DISPLAY_NAME + " TEXT, " +  
        USER_DISPLAY_PICTURE + " TEXT)";

You will need to uninstall ans reinstall your app, in order for the table to be re-created

A simple Cordova android example including Sqlite read/write and search

So after 3 days of trying, I finally got it done and I said it's better to share it with people who are interested in using Sqlite database in Cordova and PhoneGap. So here is the answer:

These scripts goes on <head> tag:

 <!-- cordova script (this will be a 404 during development) -->
    <script src="cordova.js"></script>
    <script type="text/javascript" charset="utf-8">

        // Wait for Cordova to load
        //
        document.addEventListener("deviceready", onDeviceReady, false);

        var currentRow;
        // Populate the database
        //
        function populateDB(tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id INTEGER PRIMARY KEY AUTOINCREMENT, name,number)');
        }

        // Query the database
        //
        function queryDB(tx) {
            tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);
        }

        function searchQueryDB(tx) {
            tx.executeSql("SELECT * FROM DEMO where name like ('%"+ document.getElementById("txtName").value + "%')",
                    [], querySuccess, errorCB);
        }
        // Query the success callback
        //
        function querySuccess(tx, results) {
            var tblText='<table id="t01"><tr><th>ID</th> <th>Name</th> <th>Number</th></tr>';
            var len = results.rows.length;
            for (var i = 0; i < len; i++) {
                var tmpArgs=results.rows.item(i).id + ",'" + results.rows.item(i).name
                        + "','" + results.rows.item(i).number+"'";
                tblText +='<tr onclick="goPopup('+ tmpArgs + ');"><td>' + results.rows.item(i).id +'</td><td>'
                        + results.rows.item(i).name +'</td><td>' + results.rows.item(i).number +'</td></tr>';
            }
            tblText +="</table>";
            document.getElementById("tblDiv").innerHTML =tblText;
        }

        //Delete query
        function deleteRow(tx) {
          tx.executeSql('DELETE FROM DEMO WHERE id = ' + currentRow, [], queryDB, errorCB);
        }

        // Transaction error callback
        //
        function errorCB(err) {
            alert("Error processing SQL: "+err.code);
        }

        // Transaction success callback
        //
        function successCB() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(queryDB, errorCB);
        }

         // Cordova is ready
        //
        function onDeviceReady() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(populateDB, errorCB, successCB);
        }

        //Insert query
        //
        function insertDB(tx) {
            tx.executeSql('INSERT INTO DEMO (name,number) VALUES ("' +document.getElementById("txtName").value
                    +'","'+document.getElementById("txtNumber").value+'")');
        }

        function goInsert() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(insertDB, errorCB, successCB);
        }

        function goSearch() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(searchQueryDB, errorCB);
        }

        function goDelete() {
             var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
             db.transaction(deleteRow, errorCB);
             document.getElementById('qrpopup').style.display='none';
        }

        //Show the popup after tapping a row in table
        //
        function goPopup(row,rowname,rownum) {
            currentRow=row;
            document.getElementById("qrpopup").style.display="block";
            document.getElementById("editNameBox").value = rowname;
            document.getElementById("editNumberBox").value = rownum;
        }

        function editRow(tx) {
            tx.executeSql('UPDATE DEMO SET name ="'+document.getElementById("editNameBox").value+
                    '", number= "'+document.getElementById("editNumberBox").value+ '" WHERE id = '
                    + currentRow, [], queryDB, errorCB);
        }
        function goEdit() {
            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
            db.transaction(editRow, errorCB);
            document.getElementById('qrpopup').style.display='none';
        }

    </script>

That's it! Now you can add, edit, delete and search through your data in Sqlite database using Cordova.

Hope it be helpful for the ones who are interested.

SQLite SELECT query in Android external database - table error

I think by the external database, you meant a database which you want to be loaded from your asset directory. Here you need to copy the database in your application internal storage first to make this accessible from your code. So I would like to suggest you do the following when you run the application for the first time.

public static final String DB_PATH = "/data/data/" + "com.your.package.name" + "/databases/";

private void copyFromAssetsAndCreateDatabase() {
    InputStream yourDatabaseFromAsset;

    try {
        yourDatabaseFromAsset = getApplicationContext().getAssets().open("MyExternalDatabase1");

        File dir = new File(DataHelper.DB_PATH);
        if (!dir.exists()) dir.mkdir();
        File f = new File(DataHelper.DB_PATH + "MyExternalDatabase1" + ".sqlite");
        if (!f.exists()) {
            f.createNewFile();
        }

        OutputStream mOutput = new FileOutputStream(f);
        byte[] mBuffer = new byte[1024];
        int mLength;

        while ((mLength = yourDatabaseFromAsset.read(mBuffer)) > 0)
            mOutput.write(mBuffer, 0, mLength);

        mOutput.flush();
        mOutput.close();
        mInputEnglish.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

Now when the database is copied from your external folder to your internal storage where the databases are located usually, it should find the database without any error. I do not know about the first database though. I think it was created in your application using the CREATE TABLE statement.

Hope that helps.

---

Related Topics