黎活明8天快速掌握android视频教程--17_创建数据库与完成数据添删改查

一年好景君须记，最是橙黄橘绿时。这篇文章主要讲述黎活明8天快速掌握android视频教程--17_创建数据库与完成数据添删改查相关的知识，希望能为你提供帮助。
1、我们首先来看下整个项目
项目也是采用mvc的框架

文章图片

package dB; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.widget.Toast; public class DbOpenHelper extends SQLiteOpenHelper {public DbOpenHelper(Context context) {super(context, "wy.db", null, 1); }@Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE person(personid integer primary key autoincrement, name varchar(20), phone VARCHAR(12) NULL)"); }@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {} }

操作表的接口类：

package service; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; import java.util.ArrayList; import java.util.List; import dB.DbOpenHelper; import domain.Person; public class PersonService { private DbOpenHelper dbOpenHelper; public PersonService(Context context) { this.dbOpenHelper = new DbOpenHelper(context); } public void save(Person person){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("insert into person(name, phone) values(?,?)", new Object[]{person.getName(), person.getPhone()}); } /** * 删除记录 * @param name 记录ID */ public void delete(String name,String phone){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("delete from person where name=? and phone=?", new Object[]{name,phone}); } /** * 更新记录 * @param person */ public void update(Person person,String name,String phone){ SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); db.execSQL("update person set name=?,phone=? where name=? and phone=?", new Object[]{person.getName(), person.getPhone(),name,phone}); } /** * 查询记录 * @param name 记录ID * @return */ public Person find(String name,String phone){ SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from person where name=? and phone = ?", new String[]{name,phone}); if(cursor.moveToNext()){ int personid = cursor.getInt(cursor.getColumnIndex("personid")); String name1 = cursor.getString(cursor.getColumnIndex("name")); String phone1 = cursor.getString(cursor.getColumnIndex("phone")); return new Person( name1, phone1); } cursor.close(); return null; } /** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */ public List< Person> getScrollData(int offset, int maxResult){ List< Person> persons = new ArrayList< Person> (); SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset), String.valueOf(maxResult)}); while(cursor.moveToNext()){ int personid = cursor.getInt(cursor.getColumnIndex("personid")); /*这里也可以写成 *String name = cursor.getString(1); String phone = cursor.getString(2); 默认的表自带的id字段为0 ，name为第一个字段所有为1 ，phone为第二个字段为2*/ String name = cursor.getString(cursor.getColumnIndex("name")); String phone = cursor.getString(cursor.getColumnIndex("phone")); persons.add(new Person( name, phone)); } cursor.close(); return persons; }/** * 获取记录总数 * @return */ public long getCount(){ SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery("select count(*) from person", null); cursor.moveToFirst(); long result = cursor.getLong(0); //统计之后只有一个默认的字段，所以为0 cursor.close(); return result; } }

Person类对象是：

/** * 文件名：Person.java * 版权:版权所有 (C) 中国电科30所三部 * 描述: * 修改人: wei.yuan * 修改时间:2015/1/9 * 修改内容:新增 */ package domain; /** * 项目名称：SQLLite1 * 类描述： * 创建人：wei.yuan * 创建时间：2015/1/9 11:07 * 修改人：wei.yuan * 修改时间：2015/1/9 11:07 * 修改备注： * 版权:版权所有 (C) 中国电科30所三部 */ public class Person {private String name; private String phone; @Override public String toString() { return "Person{" + "name=\'" + name + \'\\\'\' + ", phone=\'" + phone + \'\\\'\' + \'}\'; }public Person(String name, String phone) { this.name = name; this.phone = phone; }public String getName() { return name; }public void setName(String name) { this.name = name; }public String getPhone() { return phone; }public void setPhone(String phone) { this.phone = phone; } }

我们可以编写一个测试工具类来对上面的功能进行测试：
Android的测试类必须继承AndroidTestCase，该类的方法必须以test开头

package testSQLLite; import android.test.AndroidTestCase; import android.util.Log; import android.widget.Toast; import java.util.List; import dB.DbOpenHelper; import domain.Person; import service.PersonService; public class TestSQLLite extends AndroidTestCase { finalstatic StringTAG ="weiyuan"; publicvoid testCreateDb() { DbOpenHelper dbOpenHelper = new DbOpenHelper(getContext()); dbOpenHelper.getWritableDatabase(); Log.i(TAG,"数据库创建成功"); } public void testSave() throws Exception{ PersonService service = new PersonService(this.getContext()); for(int i = 0; i< 20; i++) { service.save(new Person("weiyuan"+i,"12345"+i)); } Log.i(TAG,"数据保存成功"); }/*主要查找的是姓名和电话一起查找，只要满足了姓名和电话，才正确*/ public void testFind() throws Exception{ PersonService service = new PersonService(this.getContext()); Person person = service.find("chendong","456789"); Log.i(TAG, person.toString()); Log.i(TAG,"数据查找成功"); }/*删除某个记录*/ public void testdelete() throws Exception{ PersonService service = new PersonService(this.getContext()); service.delete("weiyuan1","123451"); Log.i(TAG,"数据删除成功"); } /*给新某个记录*/ public void testupdate() throws Exception{ PersonService service = new PersonService(this.getContext()); service.update(new Person("chendong","456789"),"weiyuan2","123452"); Log.i(TAG, "数据修改成功"); } /*获得分页的数据*/ public void testScrollData() throws Exception{ PersonService service = new PersonService(this.getContext()); List< Person> persons = service.getScrollData(0, 5); for(Person person : persons){ Log.i(TAG, person.toString()); }}public void testCount() throws Exception{ PersonService service = new PersonService(this.getContext()); long result = service.getCount(); Log.i(TAG, result+""); }}

【黎活明8天快速掌握android视频教程--17_创建数据库与完成数据添删改查】