Lưu trữ dữ liệu với SQLite (Thực hành)

package com.howkteam.sqliteex;


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class HowkSQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_PEOPLE = "people";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_PERSON = "person";

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

  // Câu lệnh khởi tạo Database.
  private static final String DATABASE_CREATE = "create table "
          + TABLE_PEOPLE + "( " + COLUMN_ID
          + " integer primary key autoincrement, " + COLUMN_PERSON
          + " text not null);";

  public HowkSQLiteHelper(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(HowkSQLiteHelper.class.getName(),
            "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PEOPLE);
    onCreate(db);
  }
}

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

package com.howkteam.sqliteex;



public class Person {

  private long id;

  private String name;



  public long getId() {

    return id;

  }



  public void setId(long id) {

    this.id = id;

  }



  public String getName() {

    return name;

  }



  public void setName(String comment) {

    this.name = comment;

  }



  // Will be used by the ArrayAdapter in the ListView

  @Override

  public String toString() {

    return name;

  }

}

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

package com.howkteam.sqliteex;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

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

public class PeopleDataSource {
  // Các trường database.
  private SQLiteDatabase database;
  private HowkSQLiteHelper dbHelper;
  private String[] allColumns = {HowkSQLiteHelper.COLUMN_ID,
          HowkSQLiteHelper.COLUMN_PERSON};

  public PeopleDataSource(Context context) {
    dbHelper = new HowkSQLiteHelper(context);
  }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public Person createPerson(String pName) {
    ContentValues values = new ContentValues();
    values.put(HowkSQLiteHelper.COLUMN_PERSON, pName);
    long insertId = database.insert(HowkSQLiteHelper.TABLE_PEOPLE, null,
            values);
    Cursor cursor = database.query(HowkSQLiteHelper.TABLE_PEOPLE,
            allColumns, HowkSQLiteHelper.COLUMN_ID + " = " + insertId, null,
            null, null, null);
    cursor.moveToFirst();
    Person newPerson = cursorToPerson(cursor);
    cursor.close();
    return newPerson;
  }

  public void deletePerson(Person p) {
    long id = p.getId();
    Log.e("SQLite", "Person entry deleted with id: " + id);
    database.delete(HowkSQLiteHelper.TABLE_PEOPLE, HowkSQLiteHelper.COLUMN_ID
            + " = " + id, null);
  }

  public List<Person> getAllPeople() {
    List<Person> people = new ArrayList<Person>();

    Cursor cursor = database.query(HowkSQLiteHelper.TABLE_PEOPLE,
            allColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      Person person = cursorToPerson(cursor);
      people.add(person);
      cursor.moveToNext();
    }
    // Nhớ đóng con trỏ lại nhé.
    cursor.close();
    return people;
  }

  private Person cursorToPerson(Cursor cursor) {
    Person person = new Person();
    person.setId(cursor.getLong(0));
    person.setName(cursor.getString(1));
    return person;
  }
}

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

<?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:id="@+id/group"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content">

        <Button
            android:id="@+id/add"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="onClick"
            android:text="Add New"/>

        <Button
            android:id="@+id/delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="onClick"
            android:text="Delete First"/>

    </LinearLayout>

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

</LinearLayout>
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

package com.howkteam.sqliteex;


import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;

import java.util.List;
import java.util.Random;

public class MainActivity extends ListActivity {

  private PeopleDataSource datasource;

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

    datasource = new PeopleDataSource(this);
    datasource.open();

    List<Person> values = datasource.getAllPeople();

    // use the SimpleCursorAdapter to show the
    // elements in a ListView
    ArrayAdapter<Person> adapter = new ArrayAdapter<Person>(this,
            android.R.layout.simple_list_item_1, values);
    setListAdapter(adapter);
  }

  // Tạo sự kiện khi click vào các nút trong activity_main.xml
  public void onClick(View view) {
    @SuppressWarnings("unchecked")
    ArrayAdapter<Person> adapter = (ArrayAdapter<Person>) getListAdapter();
    Person person = null;
    switch (view.getId()) {
      // Thêm người vào danh sách.
      case R.id.add:
        String[] people = new String[]{"Alice", "Bob", "Mallory"};
        int nextInt = new Random().nextInt(3);
        person = datasource.createPerson(people[nextInt]);
        adapter.add(person);
        break;
      // Xóa người đầu tiên khỏi danh sách.
      case R.id.delete:
        if (getListAdapter().getCount() > 0) {
          person = (Person) getListAdapter().getItem(0);
          datasource.deletePerson(person);
          adapter.remove(person);
        }
        break;
    }
    adapter.notifyDataSetChanged();
  }

  @Override
  protected void onResume() {
    datasource.open();
    super.onResume();
  }

  @Override
  protected void onPause() {
    datasource.close();
    super.onPause();
  }
}

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