Android - SQLite

Hello


Motivation

---

Android default Database engine is Lite. SQLite is a lightweight transactional database engine that occupies a small amount of disk storage and memory, so it's a perfect choice for creating databases on many mobile operating systems such as Android, iOS.

Class SQLiteDatabase

---

Exposes methods to manage a SQLite database.

SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

Database names must be unique within an application, not across all applications.

In addition to SQLite's default BINARY collator, Android supplies two more, LOCALIZED, which changes with the system's current locale, and UNICODE, which is the Unicode Collation Algorithm and not tailored to the current locale.

Class SQLiteDatabase methods

---

openOrCreateDatabase : 

• open \ create data base

execSQL

• Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
• It has no means to return any data (such as the number of affected rows). Instead, you can  use insert(String, String, ContentValues), update(String, ContentValues, String, String[]), et al.

rawQuery

• Runs the provided SQL and returns a Cursor over the result set.

close

• Releases a reference to the object, closing the object if the last reference was released

Source sample

---

SQLite.zip

This sample illustrate :

• DataBase and Table creation
• Insert row into table
• Delete all rows from table
• Read all rows from data base

Data base : LocationDbName
Table_1 Schema :

• VARCHAR, UserId
• REAL - Longitude
• REAL - Latitude
• VARCHAR - Location
• INTEGER- DateTime (time in milisecond sience1, 1970, 00:00:00 GMT)

remark :

• this sample used method openOrCreateDatabase of ContextWrapper

SQLite.java

package com.example.sqllite;

import java.util.Date;


import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

public class SQLite extends Activity {

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        textViewMessage = (TextView) findViewById(R.id.textViewMessage);
     try {
   m_locationDB =  openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);
  } catch (Exception e) {
         textViewMessage.setText(e.getMessage());
        if(m_locationDB!= null)
          m_locationDB.close();
  }
       }
    
    
    private void mFillDbsTable(){
         Date dt = new Date();
         m_locationDB.execSQL("INSERT INTO " +
       TABLE_NAME +
       " Values ('Yosi',33, 44,'Hyarkon 15 Tel Aviv Israel',0);");
         
         m_locationDB.execSQL("INSERT INTO " +
           TABLE_NAME +
           String.format(" Values ('John',11, 15,'Times Square New York U.S.A',%d);",dt.getTime()));
    }

   
    
    public void insertClick(View view)
    {
        try {
         String strSqlStatement;
         strSqlStatement = String.format("CREATE TABLE IF NOT EXISTS %s (%s VARCHAR , %s REAL, %s REAL,%s VARCHAR , %s INTEGER);",
           TABLE_NAME,Const.strUserIdKey,Const.strLongitudeKey,
           Const.strLatitudeKey,Const.strLocationKey,Const.strDateTimeKey);
         m_locationDB.execSQL(strSqlStatement);
         
         mFillDbsTable();
         } catch (Exception se ) {
          textViewMessage.setText(se.getMessage());
        }
        
        }
    
    public void deleteClick(View view)
    {
      try {
       m_locationDB.execSQL(String.format("DELETE FROM  %s;",TABLE_NAME));
          }
      catch (Exception se ) {
          textViewMessage.setText(se.getMessage());
        }
    }
    
    public void readClick(View view)
    {
      try {
       Cursor c = m_locationDB.rawQuery(String.format("SELECT *  FROM %s;",TABLE_NAME),null);
            Date dt = new Date();
            String strRow="";
           textViewMessage.setText("");
           
          if (c != null ) {
           if  (c.moveToFirst()) {
            do {
             String strUserId = c.getString(c.getColumnIndex(Const.strUserIdKey));
                double fLongitude = c.getDouble(c.getColumnIndex(Const.strLongitudeKey));
                double fLatitiud = c.getDouble(c.getColumnIndex(Const.strLatitudeKey));
                String strLocation = c.getString(c.getColumnIndex(Const.strLocationKey));
              dt.setTime(c.getLong(c.getColumnIndex(Const.strDateTimeKey)));
              
             strRow += (String.format("%s,%f,%f,%s,%s\n",
                strUserId,fLongitude,fLatitiud,strLocation,dt.toString()));
              
            }while (c.moveToNext());
            
            textViewMessage.setText(strRow);
           } 
          }
         } catch (Exception se ) {
          textViewMessage.setText(se.getMessage());
        }
    }
    

    private final String DB_NAME = "LocationDbName";
 private final String TABLE_NAME = "Table_1";
    SQLiteDatabase m_locationDB = null;
    TextView textViewMessage;
}

Run the application to create

Click on Insert then click Read :

Click on Delete then click Read :





The following app was published using this post.

Nathan