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

1
package com.devahoy.sample.ahoysqlite.model;
2

3
public class Friend {
4

5
    private int id;
6
    private String firstName;
7
    private String lastName;
8
    private String tel;
9
    private String email;
10
    private String description;
11

12
    //Default Constructor
13
    public Friend() {
14

15
    }
16
    //Constructor
17
    public Friend(int id, String firstName, String lastName, String tel,
18
                  String email, String description) {
19

20
        this.id = id;
21
        this.firstName = firstName;
22
        this.lastName = lastName;
23
        this.tel = tel;
24
        this.email = email;
25
        this.description = description;
26

27
    }
28

29
    //Getter, Setter
30

31
}

1
package com.devahoy.sample.ahoysqlite.utils;
2

3
import android.content.Context;
4
import android.database.sqlite.SQLiteDatabase;
5
import android.database.sqlite.SQLiteOpenHelper;
6

7
public class DBHelper extends SQLiteOpenHelper {
8

9
    public DBHelper(Context context) {
10
        super(context, "devahoy_friends.db", null, 1);
11
    }
12
    @Override
13
    public void onCreate(SQLiteDatabase db) {
14

15
    }
16

17
    @Override
18
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
19

20
    }
21
}

1
@Override
2
public void onCreate(SQLiteDatabase db) {
3

4
    String CREATE_FRIEND_TABLE = "CREATE TABLE friend ( id INTEGER PRIMARY KEY AUTOINCREMENT, " +
5
                "first_name TEXT, last_name TEXT, tel TEXT, email TEXT, description TEXT)";
6

7
    db.execSQL(CREATE_FRIEND_TABLE);
8
}
9

10
@Override
11
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
12

13
    String DROP_FRIEND_TABLE = "DROP TABLE IF EXISTS friend";
14

15
    db.execSQL(DROP_FRIEND_TABLE);
16

17
    onCreate(db);
18
}

1
public class Friend {
2
    //Database
3
    public static final String DATABASE_NAME = "devahoy_friends.db";
4
    public static final int DATABASE_VERSION = 1;
5
    public static final String TABLE = "friend";
6

7
    public class Column {
8
        public static final String ID = BaseColumns._ID;
9
        public static final String FIRST_NAME = "first_name";
10
        public static final String LAST_NAME = "last_name";
11
        public static final String TEL = "tel";
12
        public static final String EMAIL = "email";
13
        public static final String DESCRIPTION = "description";
14
    }
15
    ...
16
}

1
String CREATE_FRIEND_TABLE = String.format("CREATE TABLE %s " +
2
        "(%s INTEGER PRIMARY KEY  AUTOINCREMENT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
3
        Friend.TABLE,
4
        Friend.Column.ID,
5
        Friend.Column.FIRST_NAME,
6
        Friend.Column.LAST_NAME,
7
        Friend.Column.TEL,
8
        Friend.Column.EMAIL,
9
        Friend.Column.DESCRIPTION);

1
package com.devahoy.sample.ahoysqlite.utils;
2

3
import android.content.Context;
4
import android.database.sqlite.SQLiteDatabase;
5
import android.database.sqlite.SQLiteOpenHelper;
6
import android.util.Log;
7

8

9
public class DBHelper extends SQLiteOpenHelper {
10

11
    private final String TAG = getClass().getSimpleName();
12

13
    private SQLiteDatabase sqLiteDatabase;
14

15
    public DBHelper(Context context) {
16
        super(context, Friend.DATABASE_NAME, null, Friend.DATABASE_VERSION);
17
    }
18

19
    @Override
20
    public void onCreate(SQLiteDatabase db) {
21

22
        String CREATE_FRIEND_TABLE = String.format("CREATE TABLE %s " +
23
                "(%s INTEGER PRIMARY KEY  AUTOINCREMENT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
24
                Friend.TABLE,
25
                Friend.Column.ID,
26
                Friend.Column.FIRST_NAME,
27
                Friend.Column.LAST_NAME,
28
                Friend.Column.TEL,
29
                Friend.Column.EMAIL,
30
                Friend.Column.DESCRIPTION);
31

32
        Log.i(TAG, CREATE_FRIEND_TABLE);
33

34
        // create friend table
35
        db.execSQL(CREATE_FRIEND_TABLE);
36
    }
37

38
    @Override
39
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
40

41
        String DROP_FRIEND_TABLE = "DROP TABLE IF EXISTS " + Friend.TABLE;
42

43
        db.execSQL(DROP_FRIEND_TABLE);
44

45
        Log.i(TAG, "Upgrade Database from " + oldVersion + " to " + newVersion);
46

47
        onCreate(db);
48
    }

1
package com.devahoy.sample.ahoysqlite;
2

3
import android.app.ListActivity;
4
import android.content.Intent;
5
import android.os.Bundle;
6
import android.view.Menu;
7
import android.view.MenuItem;
8
import android.view.View;
9
import android.widget.ArrayAdapter;
10
import android.widget.ListView;
11

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

15
import java.util.List;
16

17

18
public class MainActivity extends ListActivity {
19

20
    DBHelper mHelper;
21
    List<String> friends;
22

23
    @Override
24
    protected void onCreate(Bundle savedInstanceState) {
25
        super.onCreate(savedInstanceState);
26

27
        String[] lists = {""};
28

29
        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
30
                android.R.layout.simple_list_item_1, lists);
31
        setListAdapter(adapter);
32
    }
33
}

1
public List<String> getFriendList() {
2
    List<String> friends = new ArrayList<String>();
3

4
    sqLiteDatabase = this.getWritableDatabase();
5

6
    Cursor cursor = sqLiteDatabase.query
7
            (Friend.TABLE, null, null, null, null, null, null);
8

9
    if (cursor != null) {
10
        cursor.moveToFirst();
11
    }
12

13
    while(!cursor.isAfterLast()) {
14

15
        friends.add(cursor.getLong(0) + " " +
16
                cursor.getString(1) + " " +
17
                cursor.getString(2));
18

19
        cursor.moveToNext();
20
    }
21

22
    sqLiteDatabase.close();
23

24
    return friends;
25
}

1
public class MainActivity extends ListActivity {
2

3
    DBHelper mHelper;
4
    List<String> friends;
5

6
    @Override
7
    protected void onCreate(Bundle savedInstanceState) {
8
        super.onCreate(savedInstanceState);
9

10
        mHelper = new DBHelper(this);
11
        friends = mHelper.getFriendList();
12

13
        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
14
                android.R.layout.simple_list_item_1, friends);
15

16
        setListAdapter(adapter);
17
    }
18
}