ตัวอย่างการใช้งาน SQLite บน Android ตอนที่ 2

1
<?xml version="1.0" encoding="utf-8"?>
2

3
<LinearLayout
4
    xmlns:android="http://schemas.android.com/apk/res/android"
5
    android:orientation="horizontal"
6
    android:layout_width="match_parent"
7
    android:layout_height="match_parent"
8
    android:gravity="center">
9

10
    <LinearLayout
11
        android:layout_width="match_parent"
12
        android:layout_height="wrap_content"
13
        android:orientation="vertical"
14
        android:layout_margin="20dp" >
15

16
        <LinearLayout
17
            android:layout_width="match_parent"
18
            android:layout_height="wrap_content"
19
            android:orientation="horizontal" >
20

21
            <TextView
22
                android:layout_width="wrap_content"
23
                android:layout_height="wrap_content"
24
                android:text="@string/friend_first_name"
25
                android:textSize="24sp" />
26

27
            <EditText
28
                android:id="@+id/add_first_name"
29
                android:layout_width="match_parent"
30
                android:layout_height="wrap_content"
31
                android:textSize="24sp"
32
                android:singleLine="true" />
33

34
        </LinearLayout>
35

36
        <LinearLayout
37
            android:layout_width="match_parent"
38
            android:layout_height="wrap_content"
39
            android:orientation="horizontal" >
40

41
            <TextView
42
                android:layout_width="wrap_content"
43
                android:layout_height="wrap_content"
44
                android:text="@string/friend_last_name"
45
                android:textSize="24sp" />
46

47
            <EditText
48
                android:id="@+id/add_last_name"
49
                android:layout_width="match_parent"
50
                android:layout_height="wrap_content"
51
                android:textSize="24sp"
52
                android:singleLine="true" />
53

54
        </LinearLayout>
55

56
        <LinearLayout
57
            android:layout_width="match_parent"
58
            android:layout_height="wrap_content"
59
            android:orientation="horizontal" >
60

61
            <TextView
62
                android:layout_width="wrap_content"
63
                android:layout_height="wrap_content"
64
                android:text="@string/friend_tel"
65
                android:textSize="24sp" />
66

67
            <EditText
68
                android:id="@+id/add_tel"
69
                android:layout_width="match_parent"
70
                android:layout_height="wrap_content"
71
                android:textSize="24sp"
72
                android:singleLine="true" />
73

74
        </LinearLayout>
75

76
        <LinearLayout
77
            android:layout_width="match_parent"
78
            android:layout_height="wrap_content"
79
            android:orientation="horizontal" >
80

81
            <TextView
82
                android:layout_width="wrap_content"
83
                android:layout_height="wrap_content"
84
                android:text="@string/friend_email"
85
                android:textSize="24sp" />
86

87
            <EditText
88
                android:id="@+id/add_email"
89
                android:layout_width="match_parent"
90
                android:layout_height="wrap_content"
91
                android:textSize="24sp"
92
                android:singleLine="true" />
93

94
        </LinearLayout>
95

96
        <LinearLayout
97
            android:layout_width="match_parent"
98
            android:layout_height="wrap_content"
99
            android:orientation="vertical" >
100

101
            <TextView
102
                android:layout_width="wrap_content"
103
                android:layout_height="wrap_content"
104
                android:text="@string/friend_description"
105
                android:textSize="24sp" />
106

107
            <EditText
108
                android:id="@+id/add_description"
109
                android:layout_width="match_parent"
110
                android:layout_height="wrap_content"
111
                android:textSize="24sp"
112
                android:singleLine="false" />
113

114
        </LinearLayout>
115

116
        <Button
117
            android:id="@+id/button_submit"
118
            android:layout_width="100dp"
119
            android:layout_height="wrap_content"
120
            android:layout_gravity="center"
121
            android:padding="15dp"
122

123
            android:layout_marginTop="20dp"
124
            android:text="@android:string/ok" />
125

126
    </LinearLayout>
127

128
</LinearLayout>

1
<?xml version="1.0" encoding="utf-8"?>
2
<resources>
3

4
    <string name="app_name">AhoySQLite</string>
5

6
    <string name="friend_first_name">FirstName</string>
7
    <string name="friend_last_name">LastName</string>
8
    <string name="friend_tel">Tel</string>
9
    <string name="friend_email">Email</string>
10
    <string name="friend_description">Description</string>
11

12
    <string name="action_delete">Delete</string>
13
    <string name="action_edit">Edit</string>
14
    <string name="action_add">Add</string>
15

16
    <string name="alert_title">Delete this friend?</string>
17
    <string name="alert_message">Are you sure to delete this friend?</string>
18

19
    <string name="add_data_title">Add this friend?</string>
20
    <string name="add_data_message">Are you sure to add this friend?</string>
21

22
</resources>

1
public void addFriend(Friend friend) {
2
    sqLiteDatabase = this.getWritableDatabase();
3

4
    ContentValues values = new ContentValues();
5
    //values.put(Friend.Column.ID, friend.getId());
6
    values.put(Friend.Column.FIRST_NAME, friend.getFirstName());
7
    values.put(Friend.Column.LAST_NAME, friend.getLastName());
8
    values.put(Friend.Column.TEL, friend.getTel());
9
    values.put(Friend.Column.EMAIL, friend.getEmail());
10
    values.put(Friend.Column.DESCRIPTION, friend.getDescription());
11

12
    sqLiteDatabase.insert(Friend.TABLE, null, values);
13

14
    sqLiteDatabase.close();
15
}

1
package com.devahoy.sample.ahoysqlite;
2

3
import android.app.Activity;
4
import android.app.AlertDialog;
5
import android.content.DialogInterface;
6
import android.os.Bundle;
7
import android.view.MenuItem;
8
import android.view.View;
9
import android.widget.Button;
10
import android.widget.EditText;
11

12
import com.devahoy.sample.ahoysqlite.model.Friend;
13
import com.devahoy.sample.ahoysqlite.utils.DBHelper;
14

15

16
public class AddFriendActivity extends Activity {
17

18
    private EditText mFirstName;
19
    private EditText mLastName;
20
    private EditText mTel;
21
    private EditText mEmail;
22
    private EditText mDescription;
23
    private Button mButtonOK;
24

25
    private DBHelper mHelper;
26

27
    private int ID = -1;
28

29
    @Override
30
    protected void onCreate(Bundle savedInstanceState) {
31
        super.onCreate(savedInstanceState);
32

33
        setContentView(R.layout.activity_addfriend);
34

35
        mFirstName = (EditText) findViewById(R.id.add_first_name);
36
        mLastName = (EditText) findViewById(R.id.add_last_name);
37
        mTel = (EditText) findViewById(R.id.add_tel);
38
        mEmail = (EditText) findViewById(R.id.add_email);
39
        mDescription = (EditText) findViewById(R.id.add_description);
40
        mButtonOK = (Button) findViewById(R.id.button_submit);
41

42
    }
43

44
}

1
mHelper = new DBHelper(this);
2

3
mButtonOK.setOnClickListener(new View.OnClickListener() {
4
    @Override
5
    public void onClick(View v) {
6

7
        AlertDialog.Builder builder =
8
                new AlertDialog.Builder(AddFriendActivity.this);
9
        builder.setTitle(getString(R.string.add_data_title));
10
        builder.setMessage(getString(R.string.add_data_message));
11

12
        builder.setPositiveButton(getString(android.R.string.ok),
13
                new DialogInterface.OnClickListener() {
14
                    @Override
15
                    public void onClick(DialogInterface dialog, int which) {
16
                        Friend friend = new Friend();
17
                        friend.setFirstName(mFirstName.getText().toString());
18
                        friend.setLastName(mLastName.getText().toString());
19
                        friend.setTel(mTel.getText().toString());
20
                        friend.setEmail(mEmail.getText().toString());
21
                        friend.setDescription(mDescription.getText().toString());
22

23
                        if (ID == -1) {
24
                            mHelper.addFriend(friend);
25
                        } else {
26
                            friend.setId(ID);
27
                            //mHelper.updateFriend(friend);
28
                        }
29
                        finish();
30
                    }
31
                });
32

33
        builder.setNegativeButton(getString(android.R.string.cancel),
34
                new DialogInterface.OnClickListener() {
35
                    @Override
36
                    public void onClick(DialogInterface dialog, int which) {
37
                        dialog.cancel();
38
                    }
39
                });
40

41

42
        builder.show();
43
    }
44
});

1
<menu xmlns:android="http://schemas.android.com/apk/res/android" >
2

3
    <item
4
        android:id="@+id/action_add"
5
        android:orderInCategory="100"
6
        android:showAsAction="always"
7
        android:icon="@android:drawable/ic_menu_add"/>
8
</menu>

1
@Override
2
public boolean onCreateOptionsMenu(Menu menu) {
3
    getMenuInflater().inflate(R.menu.main, menu);
4
    return true;
5
}
6

7
@Override
8
public boolean onOptionsItemSelected(MenuItem item) {
9
    int id = item.getItemId();
10
    if (id == R.id.action_add) {
11
        Intent addFriend = new Intent(this, AddFriendActivity.class);
12

13
        startActivity(addFriend);
14
        overridePendingTransition(android.R.anim.fade_in,
15
                android.R.anim.fade_out);
16
    }
17
    return super.onOptionsItemSelected(item);
18
}

1
getActionBar().setDisplayHomeAsUpEnabled(true);

1
@Override
2
public boolean onOptionsItemSelected(MenuItem item) {
3
    finish();
4
    overridePendingTransition(android.R.anim.fade_in,
5
            android.R.anim.fade_out);
6

7
    return super.onOptionsItemSelected(item);
8
}

1
<?xml version="1.0" encoding="utf-8"?>
2

3
<LinearLayout
4
    xmlns:android="http://schemas.android.com/apk/res/android"
5
    android:layout_width="match_parent"
6
    android:layout_height="match_parent"
7
    android:orientation="vertical"
8

9
    android:padding="20dp">
10

11

12
    <TextView
13
        android:id="@+id/detail_first_name"
14
        android:layout_width="wrap_content"
15
        android:layout_height="wrap_content"
16
        android:text="@string/friend_first_name"
17
        android:textSize="24sp"/>
18

19
    <TextView
20
        android:id="@+id/detail_last_name"
21
        android:layout_width="wrap_content"
22
        android:layout_height="wrap_content"
23
        android:layout_marginTop="20dp"
24
        android:text="@string/friend_last_name"
25
        android:textSize="24sp"/>
26

27
    <TextView
28
        android:id="@+id/detail_tel"
29
        android:layout_width="wrap_content"
30
        android:layout_height="wrap_content"
31
        android:layout_marginTop="20dp"
32
        android:text="@string/friend_tel"
33
        android:textSize="24sp"/>
34

35

36
    <TextView
37
        android:id="@+id/detail_email"
38
        android:layout_width="wrap_content"
39
        android:layout_height="wrap_content"
40
        android:layout_marginTop="20dp"
41
        android:text="@string/friend_email"
42
        android:textSize="24sp"/>
43

44

45
    <TextView
46
        android:id="@+id/detail_description"
47
        android:layout_width="wrap_content"
48
        android:layout_height="wrap_content"
49
        android:layout_marginTop="20dp"
50
        android:text="@string/friend_description"
51
        android:textSize="24sp"/>
52

53
    <LinearLayout
54
        android:layout_width="match_parent"
55
        android:layout_height="match_parent"
56
        android:gravity="center"
57
        android:orientation="horizontal"
58
        android:padding="20dp">
59

60
        <Button
61
            android:id="@+id/button_edit"
62
            android:layout_width="wrap_content"
63
            android:layout_height="wrap_content"
64
            android:text="@string/action_edit"
65
            />
66

67
        <Button
68
            android:id="@+id/button_delete"
69
            android:layout_width="wrap_content"
70
            android:layout_height="wrap_content"
71
            android:text="@string/action_delete"
72
            />
73

74
    </LinearLayout>
75

76

77
</LinearLayout>

1
public Friend getFriend(String id) {
2

3
    sqLiteDatabase = this.getReadableDatabase();
4

5
    Cursor cursor = sqLiteDatabase.query( Friend.TABLE,
6
            null,
7
            Friend.Column.ID + " = ? ",
8
            new String[] { id },
9
            null,
10
            null,
11
            null,
12
            null);
13

14

15
    if (cursor != null) {
16
        cursor.moveToFirst();
17
    }
18

19
    Friend friend = new Friend();
20

21
    friend.setId((int) cursor.getLong(0));
22
    friend.setFirstName(cursor.getString(1));
23
    friend.setLastName(cursor.getString(2));
24
    friend.setTel(cursor.getString(3));
25
    friend.setEmail(cursor.getString(4));
26
    friend.setDescription(cursor.getString(5));
27

28
    return friend;
29
}

1
sqLiteDatabase.query( Friend.TABLE,
2
            null,
3
            COL_ID + " = ? ",
4
            new String[] { id },
5
            null,
6
            null,
7
            null,
8
            null);

1
public class DetailActivity extends Activity {
2
    DBHelper mHelper;
3

4
    private TextView mFirstName;
5
    private TextView mLastName;
6
    private TextView mTel;
7
    private TextView mEmail;
8
    private TextView mDescription;
9
    private String id = "";
10

11
    private Button mButtonEdit;
12
    private Button mButtonDelete;
13

14
    private Friend mFriend;
15

16
    @Override
17
    protected void onCreate(Bundle savedInstanceState) {
18
        super.onCreate(savedInstanceState);
19

20
        getActionBar().setDisplayHomeAsUpEnabled(true);
21

22
        mHelper = new DBHelper(this);
23

24
        Bundle bundle  = getIntent().getExtras();
25

26
        if (bundle != null) {
27
            id = bundle.getString(Friend.Column.ID);
28
        }
29

30
        setContentView(R.layout.acitivity_detail);
31

32
        mFirstName = (TextView) findViewById(R.id.detail_first_name);
33
        mLastName = (TextView) findViewById(R.id.detail_last_name);
34
        mTel = (TextView) findViewById(R.id.detail_tel);
35
        mEmail = (TextView) findViewById(R.id.detail_email);
36
        mDescription = (TextView) findViewById(R.id.detail_description);
37
        mButtonDelete = (Button) findViewById(R.id.button_delete);
38
        mButtonEdit = (Button) findViewById(R.id.button_edit);
39
    }
40

41
}

1
mFriend = mHelper.getFriend(id);
2

3
mFirstName.setText(mFriend.getFirstName());
4
mLastName.setText(mFriend.getLastName());
5
mTel.setText(mFriend.getTel());
6
mEmail.setText(mFriend.getEmail());
7
mDescription.setText(mFriend.getDescription());

1
@Override
2
protected void onListItemClick(ListView l, View v, int position, long id) {
3
    super.onListItemClick(l, v, position, id);
4

5
    Intent detail = new Intent(this, DetailActivity.class);
6
    String listName = friends.get(position);
7
    int index = listName.indexOf(" ");
8
    String columnId = listName.substring(0, index);
9

10
    detail.putExtra(Friend.Column.ID, columnId);
11
    startActivity(detail);
12
    overridePendingTransition(android.R.anim.fade_in,
13
            android.R.anim.fade_out);
14

15
}

1
mButtonEdit.setOnClickListener(new View.OnClickListener() {
2
    @Override
3
    public void onClick(View v) {
4
        Intent updateIntent = new Intent(DetailActivity.this,
5
                AddFriendActivity.class);
6

7
        updateIntent.putExtra(Friend.Column.ID, mFriend.getId());
8
        updateIntent.putExtra(Friend.Column.FIRST_NAME, mFriend.getFirstName());
9
        updateIntent.putExtra(Friend.Column.LAST_NAME, mFriend.getLastName());
10
        updateIntent.putExtra(Friend.Column.TEL, mFriend.getTel());
11
        updateIntent.putExtra(Friend.Column.EMAIL, mFriend.getEmail());
12
        updateIntent.putExtra(Friend.Column.DESCRIPTION, mFriend.getDescription());
13

14
        startActivity(updateIntent);
15
        overridePendingTransition(android.R.anim.fade_in,
16
                android.R.anim.fade_out);
17
    }
18
});

1
Bundle bundle = getIntent().getExtras();
2

3
if (bundle != null) {
4
    ID = bundle.getInt(Friend.Column.ID);
5
    String firstName = bundle.getString(Friend.Column.FIRST_NAME);
6
    String lastName = bundle.getString(Friend.Column.LAST_NAME);
7
    String tel = bundle.getString(Friend.Column.TEL);
8
    String email = bundle.getString(Friend.Column.EMAIL);
9
    String description = bundle.getString(Friend.Column.DESCRIPTION);
10

11
    mFirstName.setText(firstName);
12
    mLastName.setText(lastName);
13
    mTel.setText(tel);
14
    mEmail.setText(email);
15
    mDescription.setText(description);
16
}

1
mHelper.updateFriend(friend);

1
public void updateFriend(Friend friend) {
2

3
    sqLiteDatabase  = this.getWritableDatabase();
4

5
    ContentValues values = new ContentValues();
6
    values.put(Friend.Column.ID, friend.getId());
7
    values.put(Friend.Column.FIRST_NAME, friend.getFirstName());
8
    values.put(Friend.Column.LAST_NAME, friend.getLastName());
9
    values.put(Friend.Column.EMAIL, friend.getEmail());
10
    values.put(Friend.Column.DESCRIPTION, friend.getDescription());
11

12
    int row = sqLiteDatabase.update(Friend.TABLE,
13
            values,
14
            Friend.Column.ID + " = ? ",
15
            new String[] { String.valueOf(friend.getId()) });
16

17
    sqLiteDatabase.close();
18
}

1
public void deleteFriend(String id) {
2

3
    sqLiteDatabase = this.getWritableDatabase();
4

5
    /*sqLiteDatabase.delete(Friend.TABLE, Friend.Column.ID + " = ? ",
6
            new String[] { String.valueOf(friend.getId()) });*/
7
    sqLiteDatabase.delete(Friend.TABLE, Friend.Column.ID + " = " + id, null);
8

9
    sqLiteDatabase.close();
10
}

1
mButtonDelete.setOnClickListener(new View.OnClickListener() {
2
    @Override
3
    public void onClick(View v) {
4

5
        AlertDialog.Builder builder =
6
                new AlertDialog.Builder(DetailActivity.this);
7
        builder.setTitle(getString(R.string.alert_title));
8
        builder.setMessage(getString(R.string.alert_message));
9

10
        builder.setPositiveButton(getString(android.R.string.ok),
11
                new DialogInterface.OnClickListener() {
12
            @Override
13
            public void onClick(DialogInterface dialog, int which) {
14
                mHelper.deleteFriend(id);
15

16
                Toast.makeText(getApplication(),
17
                        "Deleted", Toast.LENGTH_LONG).show();
18
                finish();
19
            }
20
        });
21

22
        builder.setNegativeButton(getString(android.R.string.cancel), null);
23

24
        builder.show();
25

26
    }
27
});