Monday, October 22, 2012

Store Date formatted data on sqlite3 with Android

When your android app needs to store data with sqlite database, usually date formatted data needs to be stored for most of the cases. However, from the official document of Sqlite.org about the supported datatypes, we know it's better to have the well formatted text for the to be stored date.

So I made a little dig on this problem.
1st, the text of date stored in sqlite database is formatted as: "YYYY-MM-DD HH:MM:SS.SSS"

2nd, to make sure everything follows that format, you need to create a formatter first.

DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ");
Here, DateFormat is from class: java.text.DateFormat
Do Not use the class from Android. Since DateFormat class give you the chance to convert string to date and vice verse.

3rd, convert a database stored string date to a Date object.

Date date = formatter.parse(string);

4th, Convert a Date object to one database date format required string.


String string = formatter.format(date);

Saturday, October 6, 2012

How to move your focus through EditText on Android

Recently, I found many apps make the focus move through IME, like the Example I will make here, if you have 3 EditText, you need the user to fill out the username, password and the confirmed password.


Username
Password
Confirm

In the LinearLayout, we have four horizontal LinearLayout, each are pretty similar, while if you take a close look at it, you will notice for each EditText, if you need your focus goes through each, you need to make the EditText with single line and imeOptions as actionNext. For the last EditText, I personally set it as Done Button, so when you click it, you will close the IME, that thing appears on the bottom of the image. You could customize it with other functional button, like Go, Send, etc., please check the Document.

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

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:orientation="horizontal" >

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="right"
            android:text="Username:"
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <EditText
            android:id="@+id/dialog_create_device_edittext_name"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="3"
            android:imeOptions="actionNext"
            android:inputType="text"
            android:singleLine="true" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:orientation="horizontal" >

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="right"
            android:text="Password:"
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <EditText
            android:id="@+id/dialog_create_device_edittext_serial"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="3"
            android:imeOptions="actionNext"
            android:inputType="text|textPassword"
            android:singleLine="true" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:orientation="horizontal" >

        <TextView
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="right"
            android:text="Confirm Password:"
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <EditText
            android:id="@+id/dialog_create_device_edittext_address"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="3"
            android:imeOptions="actionDone"
            android:inputType="text|textPassword"
            android:singleLine="true" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:orientation="horizontal" >

        <Button
            android:id="@+id/dialog_create_device_button_confirm"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="@string/newdevice_dialog_Button_confirm" />

        <Button
            android:id="@+id/dialog_create_device_button_cancel"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="@string/newdevice_dialog_Button_cancel" />
    </LinearLayout>

</LinearLayout>

Tuesday, October 2, 2012

Database with Multiple Table on Android

This tutorial aims to show you how to have multiple database tables work on the android, and how these database corporate with each.

1st, Design the Table.

We have "Contacts" as the main table which has ID, PhoneNumber and Name, while we also have an accessory table called "Profiles", has ID, BelongTo(which is the id of the contact) and Name. The BelongTo property is used to link these two tables, or we could not figure out the relationship with each in the table.
One contact could have multiple profiles.

2nd, Build SQL Interface on Android.

Here we don't use split file to manage database and table interfaces. We have all of them in one file, if you need to split it into two or more files, which is what I recommended, plz ref: Vogella's Blog.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
package com.antonio081014.multidatabasetable;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "contactsManagerWithProfilePhoto";

    // Contacts table name
    private static final String TABLE_CONTACTS = "contacts";
    private static final String TABLE_PROFILES = "profiles";

    // Contacts Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_PH_NO = "phone_number";
    private static final String KEY_BELONGTO = "belongto";

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

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
 String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
  + KEY_ID + " INTEGER PRIMARY KEY autoincrement," + KEY_NAME
  + " TEXT not null," + KEY_PH_NO + " TEXT not null" + ")";
 db.execSQL(CREATE_CONTACTS_TABLE);

 String CREATE_PROFILEPHOTO_TABLE = "CREATE TABLE " + TABLE_PROFILES
  + "(" + KEY_ID + " INTEGER PRIMARY KEY autoincrement,"
  + KEY_BELONGTO + " INTEGER," + KEY_NAME + " TEXT not null"
  + ")";
 db.execSQL(CREATE_PROFILEPHOTO_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 // Drop older table if existed
 db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
 db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROFILES);
 // Create tables again
 onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact
    void addContact(Contact contact) {
 SQLiteDatabase db = this.getWritableDatabase();

 ContentValues values = new ContentValues();
 values.put(KEY_NAME, contact.getName()); // Contact Name
 values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone

 // Inserting Row
 db.insert(TABLE_CONTACTS, null, values);
 db.close(); // Closing database connection
    }

    void addProfilePhoto(ProfilePhoto profile) {
 SQLiteDatabase db = this.getWritableDatabase();

 ContentValues values = new ContentValues();
 values.put(KEY_BELONGTO, Integer.toString(profile.get_belongTo()));
 values.put(KEY_NAME, profile.getName());
 // Inserting Row
 db.insert(TABLE_PROFILES, null, values);
 db.close(); // Closing database connection
    }

    // Getting single contact
    Contact getContact(int id) {
 SQLiteDatabase db = this.getReadableDatabase();

 Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
  KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
  new String[] { String.valueOf(id) }, null, null, null, null);
 if (cursor != null)
     cursor.moveToFirst();

 Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
  cursor.getString(1), cursor.getString(2));
 // return contact
 return contact;
    }

    // Getting All Contacts
    public List<Contact> getAllContacts() {
 List<Contact> contactList = new ArrayList<Contact>();
 // Select All Query
 String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

 SQLiteDatabase db = this.getWritableDatabase();
 Cursor cursor = db.rawQuery(selectQuery, null);

 // looping through all rows and adding to list
 if (cursor.moveToFirst()) {
     do {
  Contact contact = new Contact(Integer.parseInt(cursor
   .getString(0)), cursor.getString(1),
   cursor.getString(2));
  // Adding contact to list
  contactList.add(contact);
     } while (cursor.moveToNext());
 }
 return contactList;
    }

    // Updating single contact
    public int updateContact(Contact contact) {
 SQLiteDatabase db = this.getWritableDatabase();

 ContentValues values = new ContentValues();
 values.put(KEY_NAME, contact.getName());
 values.put(KEY_PH_NO, contact.getPhoneNumber());

 // updating row
 return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
  new String[] { String.valueOf(contact.getID()) });
    }

    // Deleting single contact
    public void deleteContact(Contact contact) {
 SQLiteDatabase db = this.getWritableDatabase();
 db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
  new String[] { String.valueOf(contact.getID()) });
 db.close();
    }

    // Getting contacts Count
    public int getContactsCount() {
 String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
 SQLiteDatabase db = this.getReadableDatabase();
 Cursor cursor = db.rawQuery(countQuery, null);
 cursor.close();

 // return count
 return cursor.getCount();
    }

    public List<ProfilePhoto> getAllProfilesWithContact(int contactID) {
 List<ProfilePhoto> profileList = new ArrayList<ProfilePhoto>();
 String selectQ = "SELECT  * FROM " + TABLE_PROFILES + " WHERE "
  + KEY_BELONGTO + "=" + String.valueOf(contactID) + ";";

 SQLiteDatabase db = this.getWritableDatabase();
 Cursor cursor = db.rawQuery(selectQ, null);

 Cursor cursor2 = db.query(TABLE_PROFILES, new String[] { KEY_ID,
  KEY_BELONGTO, KEY_NAME }, KEY_BELONGTO + "=?",
  new String[] { String.valueOf(contactID) }, null, null, null,
  null);

 if (cursor.moveToFirst()) {
     do {
  ProfilePhoto profile = new ProfilePhoto(Integer.parseInt(cursor
   .getString(1)), cursor.getString(2));
  profileList.add(profile);
     } while (cursor.moveToNext());
 }
 return profileList;
    }

    public List<ProfilePhoto> getAllProfiles() {
 List<ProfilePhoto> profileList = new ArrayList<ProfilePhoto>();
 String selectQ = "SELECT  * FROM " + TABLE_PROFILES;

 SQLiteDatabase db = this.getWritableDatabase();
 Cursor cursor = db.rawQuery(selectQ, null);

 if (cursor.moveToFirst()) {
     do {
  ProfilePhoto profile = new ProfilePhoto(Integer.parseInt(cursor
   .getString(cursor.getColumnIndex(KEY_ID))),
   Integer.parseInt(cursor.getString(cursor
    .getColumnIndex(KEY_BELONGTO))),
   cursor.getString(cursor.getColumnIndex(KEY_NAME)));
  profileList.add(profile);
     } while (cursor.moveToNext());
 }
 return profileList;
    }
}

From Line 157, function public List<ProfilePhoto> getAllProfilesWithContact(int contactID) shows how to use SELECT SQL statement in android, also it shows how to use where clause statement here. Both cursor and cursor2 works. The first one use the raw SQL statement to execute, while the second one use the API from Official Android. Usually, both of them works fine, but I am not sure which could be faster.

3rd, Test with ListView.

Test with WHERE clause statement.

Code:

package com.antonio081014.multidatabasetable;

import java.util.List;

import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;

public class MainActivity extends Activity {

    private List<ProfilePhoto> listOfProfilePhoto;
    private ListView list;
    private DatabaseHandler db;

    BaseAdapter myAdapter = new BaseAdapter() {

 @Override
 public View getView(int position, View convertView, ViewGroup parent) {
     if (convertView != null)
  return convertView;
     LayoutInflater inflater = (LayoutInflater) getSystemService(Context.LAYOUT_INFLATER_SERVICE);
     View rowView = inflater.inflate(R.layout.row_layout, parent, false);
     TextView conTV = (TextView) rowView.findViewById(R.id.tv_contact);
     TextView proTV = (TextView) rowView.findViewById(R.id.tv_profile);

     conTV.setText(db.getContact(
      listOfProfilePhoto.get(position).get_belongTo()).getName());
     proTV.setText(listOfProfilePhoto.get(position).getName());

     return rowView;
 }

 @Override
 public long getItemId(int position) {
     return 0;
 }

 @Override
 public Object getItem(int position) {
     return null;
 }

 @Override
 public int getCount() {
     return listOfProfilePhoto.size();
 }
    };

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

 db = new DatabaseHandler(this);
 List<Contact> contacts = db.getAllContacts();
 if (contacts.size() == 0) {
     db.addContact(new Contact("antonio081014", "9100000000"));
     db.addContact(new Contact("antonio", "9199999999"));
     contacts = db.getAllContacts();
 }
 Log.i("Main contacts", contacts.toString());
 listOfProfilePhoto = db.getAllProfiles();
 if (listOfProfilePhoto.size() == 0) {
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty1"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty2"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty3"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty4"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty5"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty6"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty7"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty8"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty9"));

     listOfProfilePhoto = db.getAllProfilesWithContact(contacts.get(0)
      .getID());
     // listOfProfilePhoto = db.getAllProfiles();
 } else {
     listOfProfilePhoto = db.getAllProfilesWithContact(contacts.get(0)
      .getID());
 }
 Log.i("Main Profile", listOfProfilePhoto.toString());

 list = (ListView) findViewById(R.id.listView1);
 list.setAdapter(myAdapter);
    }
}

Test with SELECT * FROM TABLE;


package com.antonio081014.multidatabasetable;

import java.util.List;

import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;

public class MainActivity extends Activity {

    private List<ProfilePhoto> listOfProfilePhoto;
    private ListView list;
    private DatabaseHandler db;

    BaseAdapter myAdapter = new BaseAdapter() {

 @Override
 public View getView(int position, View convertView, ViewGroup parent) {
     if (convertView != null)
  return convertView;
     LayoutInflater inflater = (LayoutInflater) getSystemService(Context.LAYOUT_INFLATER_SERVICE);
     View rowView = inflater.inflate(R.layout.row_layout, parent, false);
     TextView conTV = (TextView) rowView.findViewById(R.id.tv_contact);
     TextView proTV = (TextView) rowView.findViewById(R.id.tv_profile);

     conTV.setText(db.getContact(
      listOfProfilePhoto.get(position).get_belongTo()).getName());
     proTV.setText(listOfProfilePhoto.get(position).getName());

     return rowView;
 }

 @Override
 public long getItemId(int position) {
     return 0;
 }

 @Override
 public Object getItem(int position) {
     return null;
 }

 @Override
 public int getCount() {
     return listOfProfilePhoto.size();
 }
    };

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

 db = new DatabaseHandler(this);
 List<Contact> contacts = db.getAllContacts();
 if (contacts.size() == 0) {
     db.addContact(new Contact("antonio081014", "9100000000"));
     db.addContact(new Contact("antonio", "9199999999"));
     contacts = db.getAllContacts();
 }
 Log.i("Main contacts", contacts.toString());
 listOfProfilePhoto = db.getAllProfiles();
 if (listOfProfilePhoto.size() == 0) {
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty1"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty2"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty3"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty4"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty5"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty6"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty7"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(1).getID(),
      "Beauty8"));
     db.addProfilePhoto(new ProfilePhoto(contacts.get(0).getID(),
      "Beauty9"));

     listOfProfilePhoto = db.getAllProfilesWithContact(contacts.get(0)
      .getID());
     // listOfProfilePhoto = db.getAllProfiles();
 } else {
     // listOfProfilePhoto = db.getAllProfilesWithContact(contacts.get(0)
     // .getID());
 }
 Log.i("Main Profile", listOfProfilePhoto.toString());

 list = (ListView) findViewById(R.id.listView1);
 list.setAdapter(myAdapter);
    }
}


PS: The code for the layout:
main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>

row_layout.xml

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

    <TextView
        android:id="@+id/tv_contact"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_weight="1"
        android:text="TextView" />

    <TextView
        android:id="@+id/tv_profile"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_weight="1"
        android:text="TextView" />

</LinearLayout>

The source code is posted on Github.