Android中SQLite应用详解

参考文章:http://blog.csdn.net/liuhe688/article/details/6715983

本文只是记录一下sqlite应用的过程。

 

 

程序结构如下:

其中,DBManger继承DBHelper, Person是一个实体类,MyActivity是一个主界面,main.xml里面定义了一个listview。

  1. 编写DBHelper
    1. package com.example.myapp;
    2.  
    3. import android.content.Context;
    4. import android.database.sqlite.SQLiteDatabase;
    5. import android.database.sqlite.SQLiteOpenHelper;
    6.  
    7. /**
    8.  * Created by zhuxuekui on 2015/5/18.
    9.  */
    10. public class DBHelper extends SQLiteOpenHelper {
    11.     private static final String name = "test.db";
    12.     private static final int version = 1;
    13.     @Override
    14.     public void onCreate(SQLiteDatabase sqLiteDatabase) {
    15.         sqLiteDatabase.execSQL("create table if not exists person"
    16.         + "(_id integer primary key autoincrement , name varchar , age integer, info text )");
    17.     }
    18.  
    19.     @Override
    20.     public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    21.         sqLiteDatabase.execSQL("alter table person add column other siring");
    22.     }
    23.  
    24.     public DBHelper(Context context) {
    25.         super(context, name, null, version);
    26.     }
    27. }

此类在数据库第一次创建时,通过构造方法执行。 然后执行onCreate,创建person表,除非我们卸载应用,不然就不会再次执行oncreate,当系统发现版本变化之后,我们会调用onUpgrade去更新,这里我们执行某些修改表的操作。

其中,我们通过version去判断是不是需要更新。

  1. 接下来,我们编写Person数据表对应的实体类
    1. package com.example.myapp;
    2.  
    3. /**
    4.  * Created by zhuxuekui on 2015/5/18.
    5.  */
    6. public class Person {
    7.     public int _id;
    8.     public String name;
    9.     public int age;
    10.     public String info;
    11.  
    12.     public Person(String name, int age, String info) {
    13.         this.name = name;
    14.         this.age = age;
    15.         this.info = info;
    16.     }
    17.     public Person()
    18.     {
    19.  
    20.     }
    21.  
    22. }

    实体类,没啥好讲,加入我们声明成private,这就需要set,get一下了。封装好然后下面在调用。

  2. 下面就是重头戏了,主要涉及数据库的增删改查
    1.  
    2. import java.util.ArrayList;
    3. import java.util.List;
    4.  
    5. import android.content.ContentValues;
    6. import android.content.Context;
    7. import android.database.Cursor;
    8. import android.database.sqlite.SQLiteDatabase;
    9.  
    10. public class DBManager {
    11.    private DBHelper helper;
    12.    private SQLiteDatabase db;
    13.  
    14.    public DBManager(Context context) {
    15.       helper = new DBHelper(context);
    16.       //因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
    17.       //所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
    18.       db = helper.getWritableDatabase();
    19.    }
    20.  
    21.    /**
    22.     * add persons
    23.     * @param persons
    24.     */
    25.    public void add(List<Person> persons) {
    26.         db.beginTransaction(); //开始事务
    27.         try {
    28.            for (Person person : persons) {
    29.               db.execSQL("INSERT INTO person VALUES(null, ?, ?, ?)", new Object[]{person.name, person.age, person.info});
    30.            }
    31.            db.setTransactionSuccessful(); //设置事务成功完成
    32.         } finally {
    33.            db.endTransaction(); //结束事务
    34.         }
    35.    }
    36.  
    37.    /**
    38.     * update person's age
    39.     * @param person
    40.     */
    41.    public void updateAge(Person person) {
    42.       ContentValues cv = new ContentValues();
    43.       cv.put("age", person.age);
    44.       db.update("person", cv, "name = ?", new String[]{person.name});
    45.    }
    46.  
    47.    /**
    48.     * delete old person
    49.     * @param person
    50.     */
    51.    public void deleteOldPerson(Person person) {
    52.       db.delete("person", "age >= ?", new String[]{String.valueOf(person.age)});
    53.    }
    54.  
    55.    /**
    56.     * query all persons, return list
    57.     * @return List<Person>
    58.     */
    59.    public List<Person> query() {
    60.       ArrayList<Person> persons = new ArrayList<Person>();
    61.       Cursor c = queryTheCursor();
    62.         while (c.moveToNext()) {
    63.            Person person = new Person();
    64.            person._id = c.getInt(c.getColumnIndex("_id"));
    65.            person.name = c.getString(c.getColumnIndex("name"));
    66.            person.age = c.getInt(c.getColumnIndex("age"));
    67.            person.info = c.getString(c.getColumnIndex("info"));
    68.            persons.add(person);
    69.         }
    70.         c.close();
    71.         return persons;
    72.    }
    73.  
    74.    /**
    75.     * query all persons, return cursor
    76.     * @return Cursor
    77.     */
    78.    public Cursor queryTheCursor() {
    79.         Cursor c = db.rawQuery("SELECT * FROM person", null);
    80.         return c;
    81.    }
    82.  
    83.    /**
    84.     * close database
    85.     */
    86.    public void closeDB() {
    87.       db.close();
    88.    }
    89. }

    在DBManager中实例化dbHelper,然后通过dbHelper得到一个SQLiteDatabase对象,作为整个应用的数据库实例;在add方法中,我们应用了事务去处理,先beginTransaction,然后设置事务成功,结束事务。这里就不需要提交了commit了。这是另一种事务的写法。当然,在执行增删改的过程中,我们可以通过exec (sql语句)来完成,也可以通过db.update() db.insert() 等方法完成。或者需要声明ContentValues 对象。

  3. 编写xml文件,用于主界面的布局

    界面部分没啥可讲,基本功。

  4. 编写主要业务逻辑部分
    1. package com.scott.db;
    2.  
    3. import java.util.ArrayList;
    4. import java.util.HashMap;
    5. import java.util.List;
    6. import java.util.Map;
    7.  
    8. import android.app.Activity;
    9. import android.database.Cursor;
    10. import android.database.CursorWrapper;
    11. import android.os.Bundle;
    12. import android.view.View;
    13. import android.widget.ListView;
    14. import android.widget.SimpleAdapter;
    15. import android.widget.SimpleCursorAdapter;
    16.  
    17.  
    18. public class MainActivity extends Activity {
    19.  
    20.    private DBManager mgr;
    21.    private ListView listView;
    22.  
    23.     @Override
    24.     public void onCreate(Bundle savedInstanceState) {
    25.         super.onCreate(savedInstanceState);
    26.         setContentView(R.layout.main);
    27.         listView = (ListView) findViewById(R.id.listView);
    28.         //初始化DBManager
    29.         mgr = new DBManager(this);
    30.     }
    31.  
    32.     @Override
    33.     protected void onDestroy() {
    34.        super.onDestroy();
    35.        //应用的最后一个Activity关闭时应释放DB
    36.        mgr.closeDB();
    37.     }
    38.  
    39.     public void add(View view) {
    40.        ArrayList<Person> persons = new ArrayList<Person>();
    41.  
    42.        Person person1 = new Person("Ella", 22, "lively girl");
    43.        Person person2 = new Person("Jenny", 22, "beautiful girl");
    44.        Person person3 = new Person("Jessica", 23, "sexy girl");
    45.        Person person4 = new Person("Kelly", 23, "hot baby");
    46.        Person person5 = new Person("Jane", 25, "a pretty woman");
    47.  
    48.        persons.add(person1);
    49.        persons.add(person2);
    50.        persons.add(person3);
    51.        persons.add(person4);
    52.        persons.add(person5);
    53.  
    54.        mgr.add(persons);
    55.     }
    56.  
    57.     public void update(View view) {
    58.        Person person = new Person();
    59.        person.name = "Jane";
    60.        person.age = 30;
    61.        mgr.updateAge(person);
    62.     }
    63.  
    64.     public void delete(View view) {
    65.        Person person = new Person();
    66.        person.age = 30;
    67.        mgr.deleteOldPerson(person);
    68.     }
    69.  
    70.     public void query(View view) {
    71.        List<Person> persons = mgr.query();
    72.        ArrayList<Map<String, String>> list = new ArrayList<Map<String, String>>();
    73.        for (Person person : persons) {
    74.           HashMap<String, String> map = new HashMap<String, String>();
    75.           map.put("name", person.name);
    76.           map.put("info", person.age + " years old, " + person.info);
    77.           list.add(map);
    78.        }
    79.        SimpleAdapter adapter = new SimpleAdapter(this, list, android.R.layout.simple_list_item_2,
    80.                 new String[]{"name", "info"}, new int[]{android.R.id.text1, android.R.id.text2});
    81.        listView.setAdapter(adapter);
    82.     }
    83.  
    84.     public void queryTheCursor(View view) {
    85.        Cursor c = mgr.queryTheCursor();
    86.        startManagingCursor(c); //托付给activity根据自己的生命周期去管理Cursor的生命周期
    87.        CursorWrapper cursorWrapper = new CursorWrapper(c) {
    88.           @Override
    89.           public String getString(int columnIndex) {
    90.              //将简介前加上年龄
    91.              if (getColumnName(columnIndex).equals("info")) {
    92.                 int age = getInt(getColumnIndex("age"));
    93.                 return age + " years old, " + super.getString(columnIndex);
    94.              }
    95.              return super.getString(columnIndex);
    96.           }
    97.        };
    98.        //确保查询结果中有"_id"列
    99.       SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2,
    100.             cursorWrapper, new String[]{"name", "info"}, new int[]{android.R.id.text1, android.R.id.text2});
    101.       ListView listView = (ListView) findViewById(R.id.listView);
    102.       listView.setAdapter(adapter);
    103.     }
    104. }

    这里面的代码和DBManger里面的代码结构上很相似,这里面在query处,我们用到了simpleAdapter.。这种主要接受list对象,Hashmap也可以。然后,做一些适配。结果如下:

posted on 2015-05-18 22:58  zhuxuekui3  阅读(218)  评论(0编辑  收藏  举报