操作Sqlite数据库

1、编写MyDbHelper类继承自SQLiteOpenHelper。代码如下：

 1 package com.gnnuit.db;
 2 
 3 import android.content.Context;
 4 import android.database.sqlite.SQLiteDatabase;
 5 import android.database.sqlite.SQLiteOpenHelper;
 6 
 7 public class MyDbHelper extends SQLiteOpenHelper {
 8     /**
 9      * 
10      * @param context应用程序上下文
11      * @param name数据库的名字
12      * @param factory查询数据库的游标工厂一般情况下用SDK默认的
13      * @param version数据库的版本版本号必须不小1
14      * 
15      */
16     public MyDbHelper(Context context) {
17         super(context, "person.db", null, 2);
18 
19     }
20 
21     @Override
22     public void onCreate(SQLiteDatabase db) {
23         System.out.println("我只有第一次创建才执行");
24         db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");
25 
26     }
27 
28     // 在mydbOpenHelper 在数据库第一次被创建的时候 会执行onCreate();
29     @Override
30     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
31         System.out.println("我只有在更新时才执行");
32         db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL ");
33     }
34 
35 }

2、编写增删改查的dao类。代码如下：

  1 package com.gnnuit.dao;
  2 
  3 import java.util.ArrayList;
  4 import java.util.List;
  5 
  6 import android.content.Context;
  7 import android.database.Cursor;
  8 import android.database.sqlite.SQLiteDatabase;
  9 import android.util.Log;
 10 
 11 import com.gnnuit.db.MyDbHelper;
 12 import com.gnnuit.domain.Person;
 13 
 14 public class PersonDao {
 15     private static final String TAG = "PersonDao";
 16     private MyDbHelper dbHelper;
 17 
 18     public PersonDao(Context context) {
 19         dbHelper = new MyDbHelper(context);
 20     }
 21 
 22     public PersonDao() {
 23 
 24     }
 25 
 26     /**
 27      * 向数据库添加一条数据
 28      * 
 29      * @param name
 30      * @param phone
 31      */
 32     public void add(String name, String phone) {
 33         SQLiteDatabase db = dbHelper.getWritableDatabase();
 34         if (db.isOpen()) {
 35             db.execSQL("insert into person(name,phone) values(?,?); ",
 36                     new Object[] { name, phone });
 37             db.close();
 38         }
 39     }
 40 
 41     /**
 42      * 查询数据
 43      * 
 44      * @param name根据姓名查询
 45      * @return 查询是否成功
 46      */
 47     public boolean get(String name) {
 48         SQLiteDatabase db = dbHelper.getReadableDatabase();
 49         boolean result = false;
 50         if (db.isOpen()) {
 51             Cursor cursor = db.rawQuery(
 52                     "select phone from person where name=?",
 53                     new String[] { name });
 54             if (cursor.moveToFirst()) {
 55                 int phoneIndex = cursor.getColumnIndex("phone");
 56                 String phone = cursor.getString(phoneIndex);
 57                 Log.i(TAG, "phone=" + phone);
 58                 result = true;
 59             }
 60             cursor.close();
 61             db.close();
 62         }
 63         return result;
 64     }
 65 
 66     /**
 67      * 删除一条数据
 68      * 
 69      * @param name根据姓名删除
 70      */
 71     public void delete(String name) {
 72         SQLiteDatabase db = dbHelper.getWritableDatabase();
 73         if (db.isOpen()) {
 74             db.execSQL("delete from person where name=?", new Object[] { name });
 75             db.close();
 76         }
 77     }
 78 
 79     /**
 80      * 修改数据
 81      * 
 82      * @param name
 83      * @param phone
 84      */
 85     public void update(String name, String phone) {
 86         SQLiteDatabase db = dbHelper.getWritableDatabase();
 87         if (db.isOpen()) {
 88             db.execSQL("update person set phone=? where name=?", new Object[] {
 89                     phone, name });
 90             db.close();
 91         }
 92     }
 93 
 94     /**
 95      * 获得所有数据
 96      * 
 97      * @return
 98      */
 99     public List<Person> getAll() {
100         SQLiteDatabase db = dbHelper.getReadableDatabase();
101         List<Person> persons = null;
102         if (db.isOpen()) {
103             Cursor cursor = db.rawQuery("select * from person", null);
104             persons = new ArrayList<Person>();
105             while (cursor.moveToNext()) {
106                 Person p = new Person();
107                 int nameIndex = cursor.getColumnIndex("name");
108                 int phoneIndex = cursor.getColumnIndex("phone");
109                 String name = cursor.getString(nameIndex);
110                 String phone = cursor.getString(phoneIndex);
111                 p.setName(name);
112                 p.setPhone(phone);
113                 persons.add(p);
114             }
115             cursor.close();
116             db.close();
117         }
118         return persons;
119     }
120 }