Android--SQLite（增，删，改，查）操作
需要5个类：
1.实体类：Person.java
2.抽象类:SQLOperate.java(封装了对数据库的操作)
3.助手类：DBOpenHelper.java(继承SQLiteOpenHelper)
4.实现类:SQLOperateImpl.java(实现抽象类SQLOperate.java)
5.测试类：Test.java(继承AndroidTestCase)
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
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());
}
}
