Working with SQLite on Android

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.");
}
}
}

Fig: Output for the project

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.)

Advertisements

Login Application For Android

Last week my friend was stuck for an Android application. That application was to use the remote database (MS SQL Server) for login.

Later I saw the coding of Android, which was Java only. Its much like just another Java framework. So I thought to give a try to help him and even I could learn some Mobile Application programming.

As Android provide flat file data storage or SQlite for data storage, it was quite tough to access the remote database like MSSQL Server, Oracle or MYSql.

Later I found in newtondev that we can execute GET or POST request from the android page and get the output of the requesting page. This lead me to the solution.

I sends the form data from Android application to the Server-side page (JSP, Servlet,ASP or PHP), then those page will compile the required operation for login validation using any database , then it returns “1” if the login is valid else return “0”.

Here are the source codes that will give you Idea for the application.

Application -> res -> layout -> main.xml (interface for Android)

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="210dip"
    android:layout_marginTop="10dip"
    android:background="#DDDDDD">
    <TextView
        android:id="@+id/tv_un"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="10pt"
        android:textColor="#444444"
        android:layout_alignParentLeft="true"
        android:layout_marginRight="9dip"
        android:layout_marginTop="20dip"
        android:layout_marginLeft="10dip"
        android:text="User Name:"/>
    <EditText
        android:id="@+id/et_un"
        android:layout_width="150dip"
        android:layout_height="wrap_content"
        android:background="@android:drawable/editbox_background"
        android:layout_toRightOf="@id/tv_un"
        android:layout_alignTop="@id/tv_un"/>
     <TextView
        android:id="@+id/tv_pw"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="10pt"
        android:textColor="#444444"
        android:layout_alignParentLeft="true"
        android:layout_below="@id/tv_un"
        android:layout_marginRight="9dip"
        android:layout_marginTop="15dip"
        android:layout_marginLeft="10dip"
        android:text="Password:"/>
    <EditText
        android:id="@+id/et_pw"
        android:layout_width="150dip"
        android:layout_height="wrap_content"
        android:background="@android:drawable/editbox_background"
        android:layout_toRightOf="@id/tv_pw"
        android:layout_alignTop="@id/tv_pw"
        android:layout_below="@id/et_un"
        android:layout_marginLeft="17dip"
        android:password="true"        />
    <Button
        android:id="@+id/btn_login"
        android:layout_width="100dip"
        android:layout_height="wrap_content"
        android:layout_below="@id/et_pw"
        android:layout_alignParentLeft="true"
        android:layout_marginTop="15dip"
        android:layout_marginLeft="110dip"
        android:text="Login" />
     <TextView
        android:id="@+id/tv_error"
        android:layout_width="fill_parent"
        android:layout_height="40dip"
        android:textSize="7pt"
        android:layout_alignParentLeft="true"
        android:layout_below="@id/btn_login"
        android:layout_marginRight="9dip"
        android:layout_marginTop="15dip"
        android:layout_marginLeft="15dip"
        android:textColor="#AA0000"
        android:text=""/>
</RelativeLayout>

Application -> src -> com.example.login -> LoginLayout.java

package com.example.login;

import java.util.ArrayList;

import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class LoginLayout extends Activity {
    EditText un,pw;
	TextView error;
    Button ok;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        un=(EditText)findViewById(R.id.et_un);
        pw=(EditText)findViewById(R.id.et_pw);
        ok=(Button)findViewById(R.id.btn_login);
        error=(TextView)findViewById(R.id.tv_error);

        ok.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub

            	ArrayList<NameValuePair> postParameters = new ArrayList<NameValuePair>();
            	postParameters.add(new BasicNameValuePair("username", un.getText().toString()));
            	postParameters.add(new BasicNameValuePair("password", pw.getText().toString()));

            	String response = null;
            	try {
            	    response = CustomHttpClient.executeHttpPost("<target page url>", postParameters);
            	    String res=response.toString();
            	    res= res.replaceAll("\\s+","");
            	    if(res.equals("1"))
            	    	error.setText("Correct Username or Password");
            	    else
            	    	error.setText("Sorry!! Incorrect Username or Password");
            	} catch (Exception e) {
            		un.setText(e.toString());
            	}

            }
        });
    }
}

Application -> src -> com.example.login ->CustomHttpClient.java (from newtondev)

package com.example.login;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URI;
import java.util.ArrayList;

import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.conn.params.ConnManagerParams;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.params.HttpConnectionParams;
import org.apache.http.params.HttpParams;

public class CustomHttpClient {
	/** The time it takes for our client to timeout */
    public static final int HTTP_TIMEOUT = 30 * 1000; // milliseconds

    /** Single instance of our HttpClient */
    private static HttpClient mHttpClient;

    /**
     * Get our single instance of our HttpClient object.
     *
     * @return an HttpClient object with connection parameters set
     */
    private static HttpClient getHttpClient() {
        if (mHttpClient == null) {
            mHttpClient = new DefaultHttpClient();
            final HttpParams params = mHttpClient.getParams();
            HttpConnectionParams.setConnectionTimeout(params, HTTP_TIMEOUT);
            HttpConnectionParams.setSoTimeout(params, HTTP_TIMEOUT);
            ConnManagerParams.setTimeout(params, HTTP_TIMEOUT);
        }
        return mHttpClient;
    }

    /**
     * Performs an HTTP Post request to the specified url with the
     * specified parameters.
     *
     * @param url The web address to post the request to
     * @param postParameters The parameters to send via the request
     * @return The result of the request
     * @throws Exception
     */
    public static String executeHttpPost(String url, ArrayList<NameValuePair> postParameters) throws Exception {
        BufferedReader in = null;
        try {
            HttpClient client = getHttpClient();
            HttpPost request = new HttpPost(url);
            UrlEncodedFormEntity formEntity = new UrlEncodedFormEntity(postParameters);
            request.setEntity(formEntity);
            HttpResponse response = client.execute(request);
            in = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));

            StringBuffer sb = new StringBuffer("");
            String line = "";
            String NL = System.getProperty("line.separator");
            while ((line = in.readLine()) != null) {
                sb.append(line + NL);
            }
            in.close();

            String result = sb.toString();
            return result;
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * Performs an HTTP GET request to the specified url.
     *
     * @param url The web address to post the request to
     * @return The result of the request
     * @throws Exception
     */
    public static String executeHttpGet(String url) throws Exception {
        BufferedReader in = null;
        try {
            HttpClient client = getHttpClient();
            HttpGet request = new HttpGet();
            request.setURI(new URI(url));
            HttpResponse response = client.execute(request);
            in = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));

            StringBuffer sb = new StringBuffer("");
            String line = "";
            String NL = System.getProperty("line.separator");
            while ((line = in.readLine()) != null) {
                sb.append(line + NL);
            }
            in.close();

            String result = sb.toString();
            return result;
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Since the application will not be allowed to access the Internet (remote service). So we have to add permission using following line to the AndroidManifest.xml


<uses-permission android:name="android.permission.INTERNET" />

So that the AndroidManifest.xml look like this.


<?xml version="1.0" encoding="utf-8"?>
   <manifest xmlns:android="http://schemas.android.com/apk/res/android"
           package="com.example.layout"
           android:versionCode="1"
           android:versionName="1.0">
       <uses-permission android:name="android.permission.INTERNET" />
       <application android:icon="@drawable/icon" android:label="@string/app_name">
       .
       .
       .
       </application>

</manifest>

Then on the server-side we can check login by using password. But here in this example I have use a simple servlet, which check the static login.

package web.com;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Prashant
 */
@WebServlet(name="AndroidResponse", urlPatterns={"/androidres.do"})
public class AndroidResponse extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        PrintWriter out=response.getWriter();
        String un,pw;
        un=request.getParameter("username");
        pw=request.getParameter("password");
        if(un.equalsIgnoreCase("prashant") && pw.equals("sharma"))
            out.print(1);
        else
            out.print(0);
    }
}

Output :

<?xml version=”1.0″ encoding=”utf-8″?>

<RelativeLayout xmlns:android=”http://schemas.android.com/apk/res/android&#8221;

android:layout_width=”fill_parent”

android:layout_height=”210dip”

android:layout_marginTop=”10dip”

android:background=”#DDDDDD”>

<TextView

android:id=”@+id/tv_un”

android:layout_width=”wrap_content”

android:layout_height=”wrap_content”

android:textSize=”10pt”

android:textColor=”#444444″

android:layout_alignParentLeft=”true”

android:layout_marginRight=”9dip”

android:layout_marginTop=”20dip”

android:layout_marginLeft=”10dip”

android:text=”User Name:”/>

<EditText

android:id=”@+id/et_un”

android:layout_width=”150dip”

android:layout_height=”wrap_content”

android:background=”@android:drawable/editbox_background”

android:layout_toRightOf=”@id/tv_un”

android:layout_alignTop=”@id/tv_un”/>

<TextView

android:id=”@+id/tv_pw”

android:layout_width=”wrap_content”

android:layout_height=”wrap_content”

android:textSize=”10pt”

android:textColor=”#444444″

android:layout_alignParentLeft=”true”

android:layout_below=”@id/tv_un”

android:layout_marginRight=”9dip”

android:layout_marginTop=”15dip”

android:layout_marginLeft=”10dip”

android:text=”Password:”/>

<EditText

android:id=”@+id/et_pw”

android:layout_width=”150dip”

android:layout_height=”wrap_content”

android:background=”@android:drawable/editbox_background”

android:layout_toRightOf=”@id/tv_pw”

android:layout_alignTop=”@id/tv_pw”

android:layout_below=”@id/et_un”

android:layout_marginLeft=”17dip”

android:password=”true”        />

<Button

android:id=”@+id/btn_login”

android:layout_width=”100dip”

android:layout_height=”wrap_content”

android:layout_below=”@id/et_pw”

android:layout_alignParentLeft=”true”

android:layout_marginTop=”15dip”

android:layout_marginLeft=”110dip”

android:text=”Login” />

<TextView

android:id=”@+id/tv_error”

android:layout_width=”fill_parent”

android:layout_height=”40dip”

android:textSize=”7pt”

android:layout_alignParentLeft=”true”

android:layout_below=”@id/btn_login”

android:layout_marginRight=”9dip”

android:layout_marginTop=”15dip”

android:layout_marginLeft=”15dip”

android:textColor=”#AA0000″

android:text=””/>

</RelativeLayout>