SQLite数据库的增删改查

1、数据库的CURD

  1 public class PersonDao {
  2     private static final String TAG = "PersonDao";
  3     private MyDBOpenHelper dbOpenHelper;
  4 
  5     // 在personDao被new出来的时候 就完成初始化
  6 
  7     public PersonDao(Context context) {
  8         dbOpenHelper = new MyDBOpenHelper(context);
  9         // dbOpenHelper.getReadableDatabase()
 10         // dbOpenHelper.getWritableDatabase()
 11     }
 12 
 13     // 增删改查
 14 
 15     /**
 16      * 往数据库添加一条数据
 17      */
 18     public void add(String name, String phone) {
 19         boolean result = find(name);
 20         if (result)
 21             return;
 22 
 23         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
 24         if (db.isOpen()) {
 25             db.execSQL("insert into person (name,phone) values (?,?)",
 26                     new Object[] { name, phone });
 27             // 关闭数据库 释放数据库的链接
 28             db.close();
 29         }
 30     }
 31 
 32     /**
 33      * 查找数据库的操作
 34      */
 35     public boolean find(String name) {
 36         boolean result = false;
 37         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
 38         if (db.isOpen()) {
 39             Cursor cursor = db.rawQuery("select * from person where name=?",
 40                     new String[] { name });
 41             if (cursor.moveToFirst()) {
 42                 int index = cursor.getColumnIndex("phone"); // 得到phone在表中是第几列
 43                 String phone = cursor.getString(index);
 44                 Log.i(TAG, "phone =" + phone);
 45                 result = true;
 46 
 47             }
 48             // 记得关闭掉 cursor
 49             cursor.close();
 50             result = false;
 51             // 释放数据库的链接
 52             db.close();
 53         }
 54         return result;
 55     }
 56 
 57     /**
 58      * 删除一条记录
 59      * 
 60      * @param name
 61      */
 62     public void delete(String name) {
 63         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
 64         if (db.isOpen()) {
 65             db.execSQL("delete from person where name =?",
 66                     new Object[] { name });
 67             db.close();
 68         }
 69     }
 70 
 71     /**
 72      * 更新一条记录
 73      * 
 74      */
 75     public void update(String name, String newname, String newphone) {
 76         SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
 77         if (db.isOpen()) {
 78             db.execSQL("update person set name=? , phone=? where name=?",
 79                     new Object[] { newname, newphone, name });
 80             db.close();
 81         }
 82     }
 83 
 84     /**
 85      * 查找全部
 86      */
 87     public List<Person> getAllPersons() {
 88         List<Person> persons=null;
 89         SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
 90         if (db.isOpen()) {
 91             persons = new ArrayList<Person>();
 92             Cursor cursor = db.rawQuery("select * from person ", null);
 93             while (cursor.moveToNext()) {
 94                 Person person = new Person();
 95                 int nameindex = cursor.getColumnIndex("name");
 96                 int phoneindex = cursor.getColumnIndex("phone");
 97                 String name = cursor.getString(nameindex);
 98                 String phone = cursor.getString(phoneindex);
 99                 person.setName(name);
100                 person.setNumber(phone);
101                 persons.add(person);
102             }
103             cursor.close();
104             db.close();
105         }
106         return persons;
107     }
108 
109 }

2、用junit测试

 1 public class TestPersonDao extends AndroidTestCase {
 2     PersonDao dao;
 3     // 测试在执行测试代码时候的流程 
 4     //1 .new TestPersonDao 框架new出来 
 5     //2. 调用 setUp()
 6     //3. testAdd()  这个时候 上下文 才被创建出来 
 7     
 8     
 9 /*    @Override
10     protected void setUp() throws Exception {
11         dao = new PersonDao(getContext());
12         super.setUp();
13     }*/
14     public void testAdd() throws Exception{
15         PersonDao dao = new PersonDao(getContext());
16         for(int i=0;i<100;i++){
17         dao.add("lisi"+i, "123456789"+i);
18         }
19     }
20     public void testdelete() throws Exception{
21         PersonDao dao = new PersonDao(getContext());
22         dao.delete("lisi99");
23     }
24     public void testupdate() throws Exception{
25         PersonDao dao = new PersonDao(getContext());
26         dao.update("lisi98", "wangwu", "119");
27     }
28     
29     public void testFindAll() throws Exception{
30         PersonDao dao = new PersonDao(getContext());
31         List<Person>  persons  = dao.getAllPersons();
32         assertEquals(100, persons.size());
33     }
34  }

使用junit步骤：

1 Adndroid-Junit
2 1、AndroidManifest.xml添加
3 <instrumentation 
4　　 android:name="android.test.InstrumentationTestRunner"
5 　　android:targetPackage="cn.zengfansheng.db"
6 />
7 2、<application>添加节点
8 　　<uses-library android:name="android.test.runner"/>