【Android数据存储】SQLite使用实例(附源码)

实例: 会员信息管理

功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 6.查找会员

 

数据库基类 – DBHelper.java

01package com.wirelessqa.sqlite;

02 

03import android.content.Context;

04import android.database.sqlite.SQLiteDatabase;

05import android.database.sqlite.SQLiteOpenHelper;

06import android.util.Log;

07 

08/**

09 * DBHelper继承了SQLiteOpenHelper，作为维护和管理数据库的基类

10 * @author bixiaopeng 2013-2-16 下午3:05:52

11 */

12public class DBHelper  extends SQLiteOpenHelper{

13 

14    public static final String DB_NAME = "wirelessqa.db";

15    public static final String DB_TABLE_NAME = "info";

16    private static final int DB_VERSION=1;

17    public DBHelper(Context context) {

18        //Context context, String name, CursorFactory factory, int version

19        //factory输入null,使用默认值

20        super(context, DB_NAME, null, DB_VERSION);

21    }

22    //数据第一次创建的时候会调用onCreate

23    @Override

24    public void onCreate(SQLiteDatabase db) {      

25        //创建表

26          db.execSQL("CREATE TABLE IF NOT EXISTS info" + 

27                    "(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age INTEGER, website STRING,weibo STRING)");

28          Log.i(WirelessQA.TAG, "create table");

29    }

30    //数据库第一次创建时onCreate方法会被调用，我们可以执行创建表的语句，当系统发现版本变化之后，会调用onUpgrade方法，我们可以执行修改表结构等语句

31    @Override

32    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

33        //在表info中增加一列other

34        //db.execSQL("ALTER TABLE info ADD COLUMN other STRING"); 

35        Log.i("WIRELESSQA", "update sqlite "+oldVersion+"---->"+newVersion);

36    }

37 

38}

数据库业务操作 – DBManager.java

001package com.wirelessqa.sqlite;

002 

003import java.util.ArrayList;

004import java.util.List;

005 

006import android.content.ContentValues;

007import android.content.Context;

008import android.database.Cursor;

009import android.database.sqlite.SQLiteDatabase;

010import android.util.Log;

011 

012/**

013 *DBManager是建立在DBHelper之上，封装了常用的业务方法

014 * @author bixiaopeng 2013-2-16 下午3:06:26

015 */

016public class DBManager {

017 

018    private DBHelper       helper;

019    private SQLiteDatabase db;

020 

021    public DBManager(Context context){

022        helper = new DBHelper(context);

023        db = helper.getWritableDatabase();

024    }

025 

026    /**

027     * 向表info中增加一个成员信息

028     *

029     * @param memberInfo

030     */

031    public void add(List<MemberInfo> memberInfo) {

032        db.beginTransaction();// 开始事务

033        try {

034            for (MemberInfo info : memberInfo) {

035                Log.i(WirelessQA.TAG, "------add memberInfo----------");

036                Log.i(WirelessQA.TAG, info.name + "/" + info.age + "/" + info.website + "/" + info.weibo);

037                // 向表info中插入数据

038                db.execSQL("INSERT INTO info VALUES(null,?,?,?,?)", new Object[] { info.name, info.age, info.website,

039                        info.weibo });

040            }

041            db.setTransactionSuccessful();// 事务成功

042        } finally {

043            db.endTransaction();// 结束事务

044        }

045    }

046 

047    /**

048     * @param _id

049     * @param name

050     * @param age

051     * @param website

052     * @param weibo

053     */

054    public void add(int _id, String name, int age, String website, String weibo) {

055        Log.i(WirelessQA.TAG, "------add data----------");

056        ContentValues cv = new ContentValues();

057        // cv.put("_id", _id);

058        cv.put("name", name);

059        cv.put("age", age);

060        cv.put("website", website);

061        cv.put("weibo", weibo);

062        db.insert(DBHelper.DB_TABLE_NAME, null, cv);

063        Log.i(WirelessQA.TAG, name + "/" + age + "/" + website + "/" + weibo);

064    }

065 

066    /**

067     * 通过name来删除数据

068     *

069     * @param name

070     */

071    public void delData(String name) {

072        // ExecSQL("DELETE FROM info WHERE name ="+"'"+name+"'");

073        String[] args = { name };

074        db.delete(DBHelper.DB_TABLE_NAME, "name=?", args);

075        Log.i(WirelessQA.TAG, "delete data by " + name);

076 

077    }

078 

079    /**

080     * 清空数据

081     */

082    public void clearData() {

083        ExecSQL("DELETE FROM info");

084        Log.i(WirelessQA.TAG, "clear data");

085    }

086 

087    /**

088     * 通过名字查询信息,返回所有的数据

089     *

090     * @param name

091     */

092    public ArrayList<MemberInfo> searchData(final String name) {

093        String sql = "SELECT * FROM info WHERE name =" + "'" + name + "'";

094        return ExecSQLForMemberInfo(sql);

095    }

096 

097    public ArrayList<MemberInfo> searchAllData() {

098        String sql = "SELECT * FROM info";

099        return ExecSQLForMemberInfo(sql);

100    }

101 

102    /**

103     * 通过名字来修改值

104     *

105     * @param raw

106     * @param rawValue

107     * @param whereName

108     */

109    public void updateData(String raw, String rawValue, String whereName) {

110        String sql = "UPDATE info SET " + raw + " =" + " " + "'" + rawValue + "'" + " WHERE name =" + "'" + whereName

111                     + "'";

112        ExecSQL(sql);

113        Log.i(WirelessQA.TAG, sql);

114    }

115 

116    /**

117     * 执行SQL命令返回list

118     *

119     * @param sql

120     * @return

121     */

122    private ArrayList<MemberInfo> ExecSQLForMemberInfo(String sql) {

123        ArrayList<MemberInfo> list = new ArrayList<MemberInfo>();

124        Cursor c = ExecSQLForCursor(sql);

125        while (c.moveToNext()) {

126            MemberInfo info = new MemberInfo();

127            info._id = c.getInt(c.getColumnIndex("_id"));

128            info.name = c.getString(c.getColumnIndex("name"));

129            info.age = c.getInt(c.getColumnIndex("age"));

130            info.website = c.getString(c.getColumnIndex("website"));

131            info.weibo = c.getString(c.getColumnIndex("weibo"));

132            list.add(info);

133        }

134        c.close();

135        return list;

136    }

137 

138    /**

139     * 执行一个SQL语句

140     *

141     * @param sql

142     */

143    private void ExecSQL(String sql) {

144        try {

145            db.execSQL(sql);

146            Log.i("execSql: ", sql);

147        } catch (Exception e) {

148            Log.e("ExecSQL Exception", e.getMessage());

149            e.printStackTrace();

150        }

151    }

152 

153    /**

154     * 执行SQL，返回一个游标

155     *

156     * @param sql

157     * @return

158     */

159    private Cursor ExecSQLForCursor(String sql) {

160        Cursor c = db.rawQuery(sql, null);

161        return c;

162    }

163 

164    public void closeDB() {

165        db.close();

166    }

167 

168}

会员信息的JavaBean – MemberInfo.java

01package com.wirelessqa.sqlite;

02 

03/**

04 * 会员信息的javabean

05 * @author bixiaopeng 2013-2-16 下午3:07:02

06 */

07public class MemberInfo {

08 

09    public int    _id;

10    public String name;

11    public int age;

12    public String website;

13    public String weibo;

14    public MemberInfo(){}

15    public MemberInfo(int _id,String name,int age,String website,String weibo){

16        this._id = _id;

17        this.name = name;

18        this.age = age;

19        this.website = website;

20        this.weibo = weibo;

21    }

22 

23}

首页显示 – MainActivity.java

显示结果页 – DisplayActivity.java

01package com.wirelessqa.sqlite;

02 

03import android.os.Bundle;

04import android.widget.TextView;

05 

06/**

07 * 显示结果

08 * @author bixiaopeng 2013-2-16 下午3:06:36

09 */

10public class DisplayActivity extends MainActivity{

11    private String result = null;

12    private TextView display = null;

13    @Override

14    protected void onCreate(Bundle savedInstanceState) {

15        super.onCreate(savedInstanceState);

16        setContentView(R.layout.activity_display);

17        Bundle extras = getIntent().getExtras();

18       result = extras.getString("searchResult");

19       display = (TextView)findViewById(R.id.display_txt);

20       display.setText(result);

21 

22    }

23}

源码下载：http://download.csdn.net/detail/wirelessqa/5066148

本文链接：【Android数据存储】SQLite使用实例

转载声明：本站文章若无特别说明，皆为原创，转载请注明来源：WirelessQA，谢谢！^^