Home > Tutorials > SQLite on Android – Part III (The Final)

SQLite on Android – Part III (The Final)


Ok! Now you probably get the idea of using SQLite on Android. Let’s move on to complete our application as usual.

 

G – Sketching the Layout

As usual, we create two layouts: one for list item, one for main display.

1. List Item Layout: (layout_list_item.xml)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
  xmlns:android="http://schemas.android.com/apk/res/android"
  android:orientation="vertical"
  android:layout_width="fill_parent"
  android:layout_height="fill_parent">

  <LinearLayout
  	android:orientation="horizontal"
  	android:layout_width="fill_parent"
  	android:layout_height="wrap_content"
  >
  	<TextView
  		android:id="@+id/txtName"
  		android:layout_width="wrap_content"
  		android:layout_height="wrap_content"
  		android:layout_weight="1"
  		android:textStyle="bold"
  		android:textColor="@android:color/white"
  	/>
  	<TextView
  		android:id="@+id/txtAge"
  		android:layout_width="wrap_content"
  		android:layout_height="wrap_content"
  		android:layout_weight="1"
  		android:layout_marginRight="5dip"
  		android:paddingRight="10dip"
  		android:gravity="right"
  		android:textStyle="italic"
  	/>
  </LinearLayout>

  <TextView
  	android:id="@+id/txtCity"
  	android:layout_width="fill_parent"
  	android:layout_height="wrap_content"
  	android:singleLine="true"
  	android:gravity="left"
  	android:textStyle="italic"
  />

</LinearLayout>

2. Main Layout: (layout_main.xml)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >

	<ListView
		android:id="@+id/listUsers"
		android:layout_width="fill_parent"
		android:layout_height="wrap_content"
	/>

</LinearLayout>

H – Class Diagram: On the Idea

Class Diagram - On the Idea

Class Diagram - On the Idea

Hang on! What are the things called “UserInfoCreator” and “ViewHolder“?

Eh oh, “UserInfoCreator” is the class designed for creating records for database and wrapping around UserDbAdapter. I’m just trying to minimize work on MainActivity.

About “ViewHolder”, it’s just a pattern to cache memory when inflating layout. You might need to refer to my other post on Design Pattern – ViewHolder.

 

I – From Design to Coding (w/ Passion)

1. UserInfo: (UserInfo.java)

package pete.android.study;

public class UserInfo {
	// fields
	private int mId;
	private String mName;
	private int mAge;
	private String mCity;

	/*
	 * constructor
	 */
	public UserInfo(int id, String name, int age, String city) {
		mId = id;
		mName = name;
		mAge = age;
		mCity = city;
	}

	/*
	 * set user id
	 */
	public void setID(int id) {
		mId = id;
	}

	/*
	 * get user id
	 */
	public int getID() {
		return mId;
	}

	/*
	 * set user name
	 */
	public void setName(String name) {
		mName = name;
	}

	/*
	 * get user name
	 */
	public String getName() {
		return mName;
	}

	/*
	 * set user age
	 */
	public void setAge(int age) {
		mAge = age;
	}

	/*
	 * get user age
	 */
	public int getAge() {
		return mAge;
	}

	/*
	 * set user city
	 */
	public void setCity(String city) {
		mCity = city;
	}

	/*
	 * get user city
	 */
	public String getCity() {
		return mCity;
	}
}

2. UserInfoAdapter: (UserInfoAdapter.java)

package pete.android.study;

import java.util.List;

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 UserInfoAdapter extends BaseAdapter {

	// private objects
	private List<UserInfo> mListUserInfo;
	private LayoutInflater mInflater;

	/*
	 * constructor
	 */
	public UserInfoAdapter(Context c, List<UserInfo> list) {
		mListUserInfo = list;
		// create layout inflater
		mInflater = LayoutInflater.from(c);
	}

	/*
	 * (non-Javadoc)
	 * @see android.widget.Adapter#getCount()
	 */
	@Override
	public int getCount() {
		return mListUserInfo.size();
	}

	/*
	 * (non-Javadoc)
	 * @see android.widget.Adapter#getItem(int)
	 */
	@Override
	public Object getItem(int position) {
		return mListUserInfo.get(position);
	}

	/*
	 * (non-Javadoc)
	 * @see android.widget.Adapter#getItemId(int)
	 */
	@Override
	public long getItemId(int position) {
		return position;
	}

	/*
	 * (non-Javadoc)
	 * @see android.widget.Adapter#getView(int, android.view.View, android.view.ViewGroup)
	 */
	@Override
	public View getView(int position, View convertView, ViewGroup parent) {
		// get view reference
		View view = convertView;
		// if null
		if(view == null) {
			// inflate new layout
			view = mInflater.inflate(R.layout.layout_list_item, null);
			// create a holder
			ViewHolder holder = new ViewHolder();
			// find controls
			holder.txtName = (TextView)view.findViewById(R.id.txtName);
			holder.txtAge = (TextView)view.findViewById(R.id.txtAge);
			holder.txtCity = (TextView)view.findViewById(R.id.txtCity);
			// set data structure to view
			view.setTag(holder);
		}

		// get selected user info
		UserInfo userInfo = mListUserInfo.get(position);
		// if not null
		if(userInfo != null) {
			// query data structure
			ViewHolder holder = (ViewHolder)view.getTag();
			// set data to display
			holder.txtName.setText(userInfo.getName());
			holder.txtAge.setText("(" + String.valueOf(userInfo.getAge()) + ")");
			holder.txtCity.setText(userInfo.getCity());
		}

		// return view
		return view;
	}

	/*
	 * @class ViewHolder
	 * to hold data structure on view with user info
	 */
	static class ViewHolder {
		private TextView txtName;
		private TextView txtAge;
		private TextView txtCity;
	}
}

3. UserInfoCreator: (UserInfoCreator.java)

package pete.android.study;

import java.util.List;

import android.content.Context;

public class UserInfoCreator {
	// db adapter
	private UserDbAdapter mDbAdapter;

	/*
	 * constructor
	 */
	public UserInfoCreator(Context c) {
		mDbAdapter = new UserDbAdapter(c);
	}

	/*
	 * open DBAdapter connection
	 */
	public void open() {
		mDbAdapter.open();
	}

	/*
	 * insert random data
	 */
	public void insert() {
    	mDbAdapter.insertUser("Pete Houston", 29, "Seoul, South Korea");
    	mDbAdapter.insertUser("Kelly Hooker", 22, "London, England");
    	mDbAdapter.insertUser("Jung Ha Lee", 25, "Pusan, South Korea");
    	mDbAdapter.insertUser("Elly Nguyen", 20, "Hanoi, Vietnam");
    	mDbAdapter.insertUser("Hana Kaemi", 26, "Kyoto, Japan");
    	mDbAdapter.insertUser("Jenya Kavroski", 19, "Leningrad, Russia");
	}

	/*
	 * query all user info from db
	 */
	public List<UserInfo> queryAll() {
		return mDbAdapter.fetchAllUsers();
	}

	/*
	 * close connection
	 */
	public void close() {
		mDbAdapter.close();
	}
}

4. MainActivity: (MainActivity.java)

package pete.android.study;

import android.app.Activity;
import android.os.Bundle;
import android.widget.ListView;

public class MainActivity extends Activity {
	// TAG for LogCat
    public static final String TAG = "SQLiteDebug";
    // list view control
	private ListView mListUserInfo;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.layout_main);

        // load list view control
        mListUserInfo = (ListView)findViewById(R.id.listUsers);

        // create data creator
        UserInfoCreator creator = new UserInfoCreator(this);
        // open connection
        creator.open();
        // insert data
        creator.insert();
        // add data to list view through adapter
        mListUserInfo.setAdapter(new UserInfoAdapter(this, creator.queryAll()));
        // close connection
        creator.close();

    }

}

J – Note

– It’s simple, yet complicated!

 

K – Get the Project by Pete

From now on, I’ll use GoogleCode for hosting my projects. So please access it through my given link!

-> Browse: SQLiteStudy by Pete on GoogleCode

 

L – Final Words

– Have you learned something good from here?

– Try to practice until you master SQLite on Android

– I’ll always be here to support any of you, I’ll try my best 🙂

 

Hope you enjoy this SQLite series!

 

Cheers,

Pete Houston

Categories: Tutorials Tags: , , ,
  1. June 15, 2016 at 10:53 pm

    Pete, I really appreciated your consistent use of “Adapters”. I have been struggling with understanding exactly what an Adapter was (i know…. pretty basic concept). Every time I thought I really understood it… I would wake up (the next day ) without a clue.

    This series of articles really helped.

    Thanks!

  2. Santosh B R
    February 18, 2016 at 9:28 pm

    Great tutorial, neatly explained. Thanks a lot..

  3. December 22, 2014 at 11:08 am

    I don’t undestand how use result of fetchSingleUser. I mean return “objOut”.

  4. Josh
    September 27, 2011 at 4:38 am

    I am using a droidx with the most recent 605 build but when I run this application it force quits. this is true for about any sql example I try to run. Any ideas?

  5. Miguel
    September 18, 2011 at 12:46 pm

    Thank You Pete,

    Very easy to follow and right to the point, as the rest of tutorials in your blog.
    I do have a question: How did you get the Class Diagram? It is a very useful tool.

    …and you are doing a GREAT JOB!
    please keep the support!
    Cheers

    • September 18, 2011 at 7:01 pm

      Oh, I’m using Enterprise Architect, a UML tool for software designer; since I’m a Software Designer, so I have to use it everyday as mandatory.
      You’re welcome to my blog! It’s nice to share and learn from others ^^!

  6. Math
    August 11, 2011 at 9:11 am

    Awesome job with your tutorial !

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: