Android SQLite DB的封裝

標籤：android   style   io   ar   os   sp   for   java   strong   

DbOpenHelper:

package com.example.db_demo01.DB;

 

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

 

public class DbOpenHelper extends SQLiteOpenHelper {

 

   private static String name = "mydb.db";    //資料庫名

   private static int version = 1;            //版本號碼

  

   public DbOpenHelper(Context context) {

      super(context, name, null, version);

   }

 

   @Override

   public void onCreate(SQLiteDatabase db) {

      String sql = "create table person(id integer primary key autoincrement, name varchar(64), address varchar(64))";

      db.execSQL(sql);     //建立表

   }

 

   @Override

   public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2){

      //更新表結構時要同時更新版本號碼

   }

}

DB操作的封閉:

 

package com.example.db_demo01.DB;

 

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;

 

public class DB {

   private DbOpenHelper helper = null;

   public DB(Context context){

      helper = new DbOpenHelper(context);

   }

  

   public boolean ExecSQL(String sql){

      boolean flag = false;

      SQLiteDatabase database = null;

      try {

         database = helper.getWritableDatabase();

         database.execSQL(sql);

         flag = true;

      } catch (Exception e) {

         e.printStackTrace();

      }finally{

         if(database != null){

            database.close();

         }

      }

      return flag;

   }

   //插入

   public boolean Insert(String sql){

      return ExecSQL(sql);

   }

   //刪除

   public boolean Delete(String sql){

      return ExecSQL(sql);

   }

   //更新

   public boolean Update(String sql){

      return ExecSQL(sql);

   }

   //查詢

   public List<Map<String, String>> Query(String sql){

      SQLiteDatabase database = null;

      List<Map<String, String>> list = new ArrayList<Map<String, String>>();

      try {

         database = helper.getWritableDatabase();

         Cursor cursor = database.rawQuery(sql, null);

         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) {

         e.printStackTrace();

      }finally{

         if(database != null){

            database.close();

         }

      }

      return list;

   }

  

}

單元測試類:

 

package com.example.db_demo01.test;

 

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

import android.test.AndroidTestCase;

import com.example.db_demo01.DB.DB;

import com.example.db_demo01.DB.DbOpenHelper;

 

public class MyTest extends AndroidTestCase {

   public MyTest(){}

   public void createDb(){

      DbOpenHelper helper = new DbOpenHelper(getContext());

      helper.getWritableDatabase();

   }

   public void insert(){

      DB db = new DB(getContext());

      String sql = "insert into person(name, address) values(‘鄧‘, ‘zhbit‘)";

      db.Insert(sql);

   }

  

   public void delete(){

      DB db = new DB(getContext());

      String sql = "delete from person where name = ‘鄧‘";

      System.out.println(db.Delete(sql));

   }

  

   public void update(){

      DB db = new DB(getContext());

      String sql = "update person set name = ‘鄧2‘ where name = ‘鄧‘";

      System.out.println(db.Delete(sql));

   }

  

   public void query(){

      DB db = new DB(getContext());

      String sql = "select * from person";

      List<Map<String, String>> list = new ArrayList<Map<String, String>>();

      list = db.Query(sql);

      System.out.println(list.toString());

   }

}

Android SQLite DB的封裝