SQLite增刪改查

標籤：sqlite

第一步: 建立一個類繼承SQLiteOpenHelper.

        onCreate方法在第一次串連資料庫是執行

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6E/82/wKioL1V-rRmghKFcAARQMK5iU-g385.jpg" title="1.png" alt="wKioL1V-rRmghKFcAARQMK5iU-g385.jpg" />

第二步: 建立實體類Person.

    

package com.jasper.SQLite.entities;

public class Person {

private int id;

private String name;

private int age;

public Person() {

super();

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public Person(int id, String name, int age) {

super();

this.id = id;

this.name = name;

this.age = age;

}

@Override

public String toString() {

return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";

}

}

第三步: 建立資料庫操作類PersonDao

    

package com.jasper.SQLite.dao;

import java.util.ArrayList;

import java.util.List;

import com.jasper.SQLite.db.PersonSQLiteOpenHelper;

import com.jasper.SQLite.entities.Person;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

public class PersonDao {

private PersonSQLiteOpenHelper mOpenHelper;

public PersonDao(Context context) {

mOpenHelper = new PersonSQLiteOpenHelper(context);

}

// 向persons表插入資料

public void insert(Person p) {

SQLiteDatabase db = mOpenHelper.getWritableDatabase();

if (db.isOpen()) {

// 插入操作

db.execSQL("insert into persons (name,age) values(?,?);",

new Object[] { p.getName(), p.getAge() });

// 關閉資料庫

db.close();

}

}

public void delete(int id) {

SQLiteDatabase db = mOpenHelper.getWritableDatabase();

if (db.isOpen()) {

// 刪除操作

db.execSQL("delete from persons where id = ?;",

new Integer[] { id });

// 關閉資料庫

db.close();

}

}

public void update(String name, int id) {

SQLiteDatabase db = mOpenHelper.getWritableDatabase();

if (db.isOpen()) {

// 更新操作

db.execSQL("update persons set name = ? where id = ?;",

new Object[] { name, id });

// 關閉資料庫

db.close();

}

db.close();

}

// 查詢整張表

public List<Person> queryAll() {

SQLiteDatabase db = mOpenHelper.getWritableDatabase();

if (db.isOpen()) {

// 查詢操作

Cursor cursor = db.rawQuery("select id,name,age from persons;",

null);

// 結果集不為空白且條目大於0

if (cursor != null && cursor.getCount() > 0) {

List<Person> personList = new ArrayList<Person>();

int id;

String name;

int age;

while (cursor.moveToNext()) {

id = cursor.getInt(0);

name = cursor.getString(1);

age = cursor.getInt(2);

personList.add(new Person(id, name, age));

}

// 關閉資料庫

db.close();

return personList;

}

db.close();

}

return null;

}

public Person query(int id)

{

SQLiteDatabase db = mOpenHelper.getWritableDatabase();

if (db.isOpen()) {

Cursor cursor = db.rawQuery("select id,name,age from persons where id = ?;", new String[]{id + ""});

if(cursor != null && cursor.moveToFirst())

{

int _id = cursor.getInt(0);

String name = cursor.getString(1);

int age = cursor.getInt(2);

db.close();

return new Person(_id, name, age);

}

db.close();

}

return null;

}

}

第四步: 建立測試類別TestCase

    

package com.jasper.SQLite.test;

import java.util.List;

import com.jasper.SQLite.dao.PersonDao;

import com.jasper.SQLite.db.PersonSQLiteOpenHelper;

import com.jasper.SQLite.entities.Person;

import android.test.AndroidTestCase;

import android.util.Log;

public class TestCase extends AndroidTestCase {

private static final String TAG = "TestCase";

public void test()

{

PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());

openHelper.getWritableDatabase();

}

public void testInsert()

{

PersonDao per = new PersonDao(getContext());

per.insert(new Person(0, "阿飛", 20));

}

public void testDelete()

{

PersonDao per = new PersonDao(getContext());

per.delete(2);

}

public void testUpdate()

{

PersonDao per = new PersonDao(getContext());

per.update("lily", 1);

}

public void testQueryAll()

{

PersonDao per = new PersonDao(getContext());

List<Person> personList = per.queryAll();

for (Person person : personList) {

Log.i(TAG, person.toString());

}

}

public void testQuery()

{

PersonDao per = new PersonDao(getContext());

Person person = per.query(1);

Log.i(TAG, person.toString());

}

}

SQLite增刪改查