Android数据库基本操作 SQLite

1.Person.java

package com.mrzhu.sqltite; 
 
public class Person { 
     
    private int _id; 
    private String name; 
     
    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; 
    } 
     
    @Override 
    public String toString() { 
        return "Person [id=" + _id + ", name=" + name + "]"; 
    } 
     
    public Person() { 
        super(); 
    } 
     
    public Person(int _id, String name) { 
        super(); 
        this._id = _id; 
        this.name = name; 
    } 
} 




2.SQLOperate.java

package com.mrzhu.sqltite; 
 
import java.util.List; 
 
/** 
* 增删改查 
* @author ZLQ 
* 
*/ 
public interface SQLOperate { 
    public void add(Person p); 
    public void delete(int id); 
    public void updata(Person p); 
    public List<Person> find(); 
    public Person findById(int id); 
} 




3.DBOpenHelper.java

[html] 
package com.mrzhu.sqltite; 
 
import android.content.Context; 
import android.database.sqlite.SQLiteDatabase; 
import android.database.sqlite.SQLiteOpenHelper; 
 
/** 
* 助手类 
* @author ZLQ 
* 
*/ 
public class DBOpneHelper extends SQLiteOpenHelper { 
 
    private static final int VERSION = 1;//版本 
    private static final String DB_NAME = "people.db";//数据库名 
    public static final String STUDENT_TABLE = "student";//表名 
    public static final String _ID = "_id";//表中的列名 
    public static final String NAME = "name";//表中的列名 
    //创建数据库语句，STUDENT_TABLE，_ID ，NAME的前后都要加空格 
    private static final String CREATE_TABLE = "create table " + STUDENT_TABLE + " ( " + _ID + " Integer primary key autoincrement," + NAME + " text)"; 
     
    public DBOpneHelper(Context context) { 
        super(context, DB_NAME, null, VERSION); 
    } 
 
    //数据库第一次被创建时调用  
    @Override 
    public void onCreate(SQLiteDatabase db) { 
        db.execSQL(CREATE_TABLE); 
    } 
 
    //版本升级时被调用  
    @Override 
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
 
    } 
 
} 



4.SQLOperateImpl.java

package com.mrzhu.sqltite; 
 
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; 
 
public class SQLOperateImpl implements SQLOperate{ 
 
    private DBOpneHelper dbOpenHelper; 
     
    public SQLOperateImpl(Context context) { 
        dbOpenHelper = new DBOpneHelper(context); 
    } 
 
    /** 
     * 增，用insert向数据库中插入数据 
     */ 
    public void add(Person p) { 
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 
        ContentValues values = new ContentValues(); 
        values.put(DBOpneHelper._ID, p.getId()); 
        values.put(DBOpneHelper.NAME, p.getName()); 
        db.insert(DBOpneHelper.STUDENT_TABLE, null, values); 
    } 
 
    /** 
     * 删，通过id删除数据 
     */ 
    public void delete(int id) { 
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 
        db.delete(DBOpneHelper.STUDENT_TABLE, DBOpneHelper._ID + "=?", new String[]{String.valueOf(id)}); 
    } 
 
    /** 
     * 改，修改指定id的数据 
     */ 
    public void updata(Person p) { 
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 
        ContentValues values = new ContentValues(); 
        values.put(DBOpneHelper._ID, p.getId()); 
        values.put(DBOpneHelper.NAME, p.getName()); 
        db.update(DBOpneHelper.STUDENT_TABLE, values, DBOpneHelper._ID + "=?", new String[]{String.valueOf(p.getId())}); 
    } 
 
    /** 
     * 查，查询表中所有的数据 
     */ 
    public List<Person> find() { 
        List<Person> persons = null; 
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); 
        Cursor cursor = db.query(DBOpneHelper.STUDENT_TABLE, null, null, null, null, null, null); 
        if(cursor != null){ 
            persons = new ArrayList<Person>(); 
            while(cursor.moveToNext()){ 
                Person person = new Person(); 
                int _id = cursor.getInt(cursor.getColumnIndex(DBOpneHelper._ID)); 
                String name = cursor.getString(cursor.getColumnIndex(DBOpneHelper.NAME)); 
                person.setId(_id); 
                person.setName(name); 
                persons.add(person); 
            } 
        } 
        return persons; 
    } 
 
    /** 
     * 查询指定id的数据 
     */ 
    public Person findById(int id) { 
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); 
        Cursor cursor = db.query(DBOpneHelper.STUDENT_TABLE, null, DBOpneHelper._ID + "=?", new String[]{String.valueOf(id)}, null, null, null); 
        Person person = null; 
        if(cursor != null && cursor.moveToFirst()){ 
            person = new Person(); 
            int _id = cursor.getInt(cursor.getColumnIndex(DBOpneHelper._ID)); 
            String name = cursor.getString(cursor.getColumnIndex(DBOpneHelper.NAME)); 
            person.setId(_id); 
            person.setName(name); 
        } 
        return person; 
    } 
} 




5.Test.java

在AndroidManifest.xml中的<application></application>外添加
（targetPackage是当前工程的包名）

<instrumentation
android:targetPackage="com.mrzhu.sqltite"
android:name="android.test.InstrumentationTestRunner">
</instrumentation>

在<application></application>中添加<uses-library android:name="android.test.runner"/>


package com.mrzhu.sqltite; 
 
import java.util.List; 
 
import android.test.AndroidTestCase; 
import android.util.Log; 
 
public class Test extends AndroidTestCase { 
    public void testAdd() throws Exception{ 
        SQLOperateImpl test = new SQLOperateImpl(getContext()); 
        Person person = new Person(2, "Peter"); 
        test.add(person); 
    } 
     
    public void testDelete() throws Exception{ 
        SQLOperateImpl test = new SQLOperateImpl(getContext()); 
        test.delete(1); 
    } 
     
    public void testUpdata() throws Exception{ 
        SQLOperateImpl test = new SQLOperateImpl(getContext()); 
        Person person = new Person(1, "Tom"); 
        test.updata(person); 
    }   www.2cto.com
     
    public void testFind() throws Exception{ 
        SQLOperateImpl test = new SQLOperateImpl(getContext()); 
        List<Person> persons = test.find(); 
        for (Person person : persons) { 
            Log.i("System.out", person.toString()); 
        } 
    } 
     
    public void testFindById() throws Exception{ 
        SQLOperateImpl test = new SQLOperateImpl(getContext()); 
        Person person = test.findById(2); 
        Log.i("System.out", person.toString()); 
    } 
}