Android中使用Sqlite資料庫 （二） 增刪改查

標籤：sqlite

定義介面

package com.example.android_db.service;import java.util.List;import java.util.Map;public interface PersonService {    public boolean addPerson(Object[] params);                                      public boolean deletePerson(Object[] params);                                      public boolean updatePerson(Object[] params);                                      public Map<String,String> viewPerson(String[] selectionArgs);                                      public List<Map<String,String>> listPersonMaps(String[] selectionArgs);}

實現介面：

package com.example.android_db.dao;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.android_db.db.DbOpenHelper;import com.example.android_db.service.PersonService;public class PersonDao implements PersonService {    private DbOpenHelper helper = null;    public PersonDao(Context context) {        // TODO Auto-generated constructor stub        helper = new DbOpenHelper(context);    }    @Override    public boolean addPerson(Object[] params) {        // TODO Auto-generated method stub        boolean flag = false;        //實現對資料庫的添加刪除和修改查詢的功能        SQLiteDatabase database = null;        try {            String sql = "insert into person(name,address,sex) values(?,?,?)";            database = helper.getWritableDatabase();//實現對資料庫的寫的操作            database.execSQL(sql, params);            flag = true;        } catch (Exception e) {            // TODO: handle exception            e.printStackTrace();        }finally{            if(database!=null){                database.close();            }        }        return flag;    }    @Override    public boolean deletePerson(Object[] params) {        // TODO Auto-generated method stub        boolean flag = false;        SQLiteDatabase database = null;        try {            String sql = "delete from person where id = ? ";            database = helper.getWritableDatabase();            database.execSQL(sql, params);            flag = true;        } catch (Exception e) {            // TODO: handle exception        }finally{            if(database!=null){                database.close();            }        }        return flag;    }    @Override    public boolean updatePerson(Object[] params) {        // TODO Auto-generated method stub        boolean flag = false;        SQLiteDatabase database = null;        try {            String sql = "update person set name = ? ,address = ?, sex = ? where id = ? ";            database = helper.getWritableDatabase();            database.execSQL(sql, params);            flag = true;        } catch (Exception e) {            // TODO: handle exception        }finally{            if(database!=null){                database.close();            }        }        return flag;    }    @Override    public Map<String, String> viewPerson(String[] selectionArgs) {        Map<String,String> map = new HashMap<String, String>();        SQLiteDatabase database = null;        try {            String sql = "select * from person where id = ? ";            database = helper.getReadableDatabase();            Cursor cursor = database.rawQuery(sql, selectionArgs);            //獲得資料庫的列的個數            int colums = cursor.getColumnCount();            while(cursor.moveToNext()){                for(int i=0;i<colums;i++){                    String cols_name = cursor.getColumnName(i);                    String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));                    if(cols_value==null){                        cols_value = "";                    }                    map.put(cols_name, cols_value);                }            }        } catch (Exception e) {            // TODO: handle exception        }finally{            if(database!=null){                database.close();            }        }        return map;    }    @Override    public List<Map<String, String>> listPersonMaps(String[] selectionArgs) {        // TODO Auto-generated method stub        List<Map<String,String>> list = new ArrayList<Map<String,String>>();        String sql = "select * from person ";        SQLiteDatabase database = null;        try {            database = helper.getReadableDatabase();            Cursor cursor = database.rawQuery(sql, selectionArgs);            int colums = cursor.getColumnCount();            while(cursor.moveToNext()){                Map<String,String> map = new HashMap<String, String>();                for(int i=0;i<colums;i++){                    String cols_name = cursor.getColumnName(i);                    String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));                    if(cols_value==null){                        cols_value="";                    }                    map.put(cols_name, cols_value);                }                list.add(map);            }        } catch (Exception e) {            // TODO: handle exception        }finally{            if(database!=null){                database.close();            }        }        return list;    }}

實現SQLiteOpenHelper的子類

package com.example.android_db.db;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DbOpenHelper extends SQLiteOpenHelper {    private static String name = "mydb.db";// 表示資料庫的名稱    private static int version = 2;// 表示資料庫的版本號碼    public DbOpenHelper(Context context) {        super(context, name, null, version);    }    // 當資料庫建立的時候，是第一次被執行,完成對資料庫的表的建立    @Override    public void onCreate(SQLiteDatabase db) {        // TODO Auto-generated method stub        //支援的資料類型：整型資料，字串類型，日期類型，二進位的資料類型，        String sql = "create table person(id integer primary key autoincrement,name varchar(64),address varchar(64))";        db.execSQL(sql);    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        // TODO Auto-generated method stub        String sql = "alter table person add sex varchar(8)";        db.execSQL(sql);    }}

測試類別：

package com.example.android_db.test;import java.util.List;import java.util.Map;import com.example.android_db.dao.PersonDao;import com.example.android_db.db.DbOpenHelper;import com.example.android_db.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class MyTest extends AndroidTestCase {    public MyTest() {        // TODO Auto-generated constructor stub    }           public void createDb(){        DbOpenHelper helper = new DbOpenHelper(getContext());        helper.getWritableDatabase();    }    public void insertDB(){        PersonService service = new PersonDao(getContext());        Object[] params = {"李斯","廣西","女"};        boolean flag = service.addPerson(params);        System.out.println("--->>"+flag);    }           public void deleteDB(){        PersonService service = new PersonDao(getContext());        Object[] params = {1};        boolean flag = service.deletePerson(params);        System.out.println("--->>"+flag);    }           public void updateDB(){        PersonService service = new PersonDao(getContext());        Object[] params = {"王五","上海","不祥","3"};        service.updatePerson(params);    }           public void viewDB(){        PersonService service = new PersonDao(getContext());        String[] selectionArgs = {"3"};        Map<String, String> map = service.viewPerson(selectionArgs);        Log.i("Test", "-->>"+map.toString());    }           public void listDB(){        PersonService service = new PersonDao(getContext());                   List<Map<String,String>> list = service.listPersonMaps(null);        Log.i("Test", "-->>"+list.toString());    }}