上一篇文章介绍了sqlite数据库的创建,以及数据的访问,本文将主要介绍数据库的增删改查.
下面直接看代码:
MyDBHelper.java(创建数据库,添加一列phone)
package com.amos.android_database;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;/** * Created by amosli on 14-6-10. */public class MyDBHelper extends SQLiteOpenHelper{ /** * 创建数据库的构造方法 * @param context 应用程序上下文 * name 数据库的名字 * factory 查询数据库的游标工厂一般情况下用sdk默认的 * version 数据库的版本一般大于0 */ public MyDBHelper(Context context) { super(context, "test.db", null, 4); } private String tag = "MyDBHelper.class"; /** * 在数据库第一次创建时会执行 * @param db */ @Override public void onCreate(SQLiteDatabase db) { Log.d(tag, "onCreate....."); //创建一个数据库 db.execSQL("create table person (personid integer primary key autoincrement ,name varchar(30) )"); } /** * 更新数据的时候调用的方法 * @param db * @param oldVersion * @param newVersion */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.d(tag,"onUpgrade*******"); //增加一列 db.execSQL("alter table person add phone varchar(13) null"); }}
PersonDao.java(增删查改都在这了)
package com.amos.android_database.dao;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import com.amos.android_database.MyDBHelper;import com.amos.android_database.domain.Person;import java.util.ArrayList;import java.util.List;/** * Created by amosli on 14-6-11. */public class PersonDao { private MyDBHelper myDBHelper; public String tag = "PersonDao.class"; //在new出来的时候就实现myDBHelper初始化 public PersonDao(Context context) { myDBHelper = new MyDBHelper(context); } //增加 public void addPerson(String name, String phone) { SQLiteDatabase database = myDBHelper.getWritableDatabase(); //先判断数据库是否可用 if (database.isOpen()) { //执行插入操作 //database.execSQL("insert into person (name,phone) values('"+name+"','"+phone+"')"); //推荐如下写法 database.execSQL("insert into person (name,phone) values(?,?)", new Object[]{name, phone}); database.close(); } } //查找 public boolean findPerson(String phone) { boolean result = false; SQLiteDatabase database = myDBHelper.getReadableDatabase(); if (database.isOpen()) { //database.execSQL("select * from phone='"+phone+"'"); Cursor cursor = database.rawQuery("select * from person where phone=?", new String[]{phone}); if (cursor.moveToFirst()) { //游标是否移动到下一行,如果是,那说明有数据返回 Log.d(tag, "count:" + cursor.getColumnCount()); int nameIndex = cursor.getColumnIndex("name"); Log.d(tag, "name:" + cursor.getString(nameIndex)); cursor.close(); result = true; } else { result = false; } database.close(); } return result; } //删除一条数据 public void deletePerson(String phone) { SQLiteDatabase database = myDBHelper.getWritableDatabase(); if (database.isOpen()) { database.execSQL("delete from person where phone=?", new Object[]{phone}); } database.close(); } //更新一条数据 public void updatePerson(String phone, String newName, String newPhone) { SQLiteDatabase database = myDBHelper.getWritableDatabase(); if (database.isOpen()) { database.execSQL("update person set name=?,phone=? where phone=?", new Object[]{newName, newPhone, phone}); } database.close(); } //查找所有person public List findAllPerson(){ List personList = new ArrayList (); SQLiteDatabase database = myDBHelper.getReadableDatabase(); if(database.isOpen()){ Cursor cursor = database.rawQuery("select * from person ", null); while(cursor.moveToNext()){ int nameIndex = cursor.getColumnIndex("name"); int phoneIndex = cursor.getColumnIndex("phone"); String name = cursor.getString(nameIndex); String phone = cursor.getString(phoneIndex); Person person = new Person(name,phone); Log.d(tag,person.toString()); personList.add(person); } } database.close(); return personList; }} 注:
１.由上面的方法可以看到,查找数据主要调用的是rawQuery方法,而增删改都是通过execSQL执行数据的
２.一定要注意,使用database前一定要判断是否可用,使用database.isOpen();
3.使用完database一定要注意关闭数据库,database.close();不然会报异常!
AndroidManifest.xml(配置测试环境,看加粗部分)
测试类PersonDaoTest.java
package com.amos.android_database.test;import android.test.AndroidTestCase;import android.util.Log;import com.amos.android_database.dao.PersonDao;import com.amos.android_database.domain.Person;/** * Created by amosli on 14-6-11. */public class PersonDaoTest extends AndroidTestCase{ private String tag = "PersonDaoTest.class"; public void testAdd() throws Exception{ PersonDao personDao = new PersonDao(getContext()); for(int i=0;i<100;i++){ personDao.addPerson("amsoli"+i,"131888870"+i); } Log.d(tag,"添加一些新用户"); } public void testFind(){ PersonDao personDao = new PersonDao(getContext()); boolean result = personDao.findPerson("13188887776"); assertEquals(true,result); } public void testDelete(){ PersonDao personDao = new PersonDao(getContext()); personDao.deletePerson("13188887778"); } public void testUpdate(){ PersonDao personDao = new PersonDao(getContext()); personDao.updatePerson("13188887776","hi_amos","13188887775"); } public void testFindAllPerson(){ PersonDao personDao = new PersonDao(getContext()); for (Person person : personDao.findAllPerson()) { System.out.println(person.toString()); } }}
由于要反复测试,所以如果将数据库反复导出会非常麻烦,下面可以使用命令进行查看数据库中的数据:
1)进行命令行模式,注意是在platform-tools目录下,而非tools目录
sdk/platform-tools$ ./adb shell
2)使用cd命令切换到test.db的目录
#cd /data/data/com.amos.android_database/databases/# lstest.db
3)使用sqlite3打开test.db
# sqlite3 test.db SQLite version 3.5.9Enter ".help" for instructions sqlite> .databaseseq name file --- --------------- ----------------------------------------------------------0 main /data/data/com.amos.android_database/databases/test.db sqlite> .tableandroid_metadata person
查看其中的数据:
sqlite> select * from person;1|amsoli0|13188887002|amsoli1|13188887013|amsoli2|13188887024|amsoli3|13188887035|amsoli4|13188887046|amsoli5|13188887057|amsoli6|13188887068|amsoli7|13188887079|amsoli8|1318888708.........
注:
i:关于Sqlite的教程可以参考:
ii:关于本方源代码可以查看: