需要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);
- }
- 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());
- }
- }