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

最新推荐文章于 2021-05-25 19:52:12 发布

原创最新推荐文章于 2021-05-25 19:52:12 发布 · 1.6w 阅读

30 ·

CC 4.0 BY-SA版权

玩转 Android开发专栏收录该内容

26 篇文章

订阅专栏

本文提供了一个SQLite数据库的应用实例，包括数据库的创建、基本增删改查操作及事务处理等关键步骤。

实例: 会员信息管理

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

数据库基类 – DBHelper.java

 
package com.wirelessqa.sqlite;
 
 
 
import android.content.Context;
 
import android.database.sqlite.SQLiteDatabase;
 
import android.database.sqlite.SQLiteOpenHelper;
 
import android.util.Log;
 
 
 
/**
 
 * DBHelper继承了SQLiteOpenHelper，作为维护和管理数据库的基类
 
 * @author bixiaopeng 2013-2-16 下午3:05:52
 
 */
 
public class DBHelper  extends SQLiteOpenHelper{
 
 
 
    public static final String DB_NAME = "wirelessqa.db";
 
    public static final String DB_TABLE_NAME = "info";
 
    private static final int DB_VERSION=1;
 
    public DBHelper(Context context) {
 
        //Context context, String name, CursorFactory factory, int version
 
        //factory输入null,使用默认值
 
        super(context, DB_NAME, null, DB_VERSION);
 
    }
 
    //数据第一次创建的时候会调用onCreate
 
    @Override
 
    public void onCreate(SQLiteDatabase db) {      
 
        //创建表
 
          db.execSQL("CREATE TABLE IF NOT EXISTS info" + 
 
                    "(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age INTEGER, website STRING,weibo STRING)");
 
          Log.i(WirelessQA.TAG, "create table");
 
    }
 
    //数据库第一次创建时onCreate方法会被调用，我们可以执行创建表的语句，当系统发现版本变化之后，会调用onUpgrade方法，我们可以执行修改表结构等语句
 
    @Override
 
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
        //在表info中增加一列other
 
        //db.execSQL("ALTER TABLE info ADD COLUMN other STRING"); 
 
        Log.i("WIRELESSQA", "update sqlite "+oldVersion+"---->"+newVersion);
 
    }
 
 
 
}

数据库业务操作 – DBManager.java

 
package com.wirelessqa.sqlite;
 
 
 
import java.util.ArrayList;
 
import java.util.List;
 
 
 
import android.content.ContentValues;
 
import android.content.Context;
 
import android.database.Cursor;
 
import android.database.sqlite.SQLiteDatabase;
 
import android.util.Log;
 
 
 
/**
 
 *DBManager是建立在DBHelper之上，封装了常用的业务方法
 
 * @author bixiaopeng 2013-2-16 下午3:06:26
 
 */
 
public class DBManager {
 
 
 
    private DBHelper       helper;
 
    private SQLiteDatabase db;
 
 
 
    public DBManager(Context context){
 
        helper = new DBHelper(context);
 
        db = helper.getWritableDatabase();
 
    }
 
 
 
    /**
 
     * 向表info中增加一个成员信息
 
     *
 
     * @param memberInfo
 
     */
 
    public void add(List<MemberInfo> memberInfo) {
 
        db.beginTransaction();// 开始事务
 
        try {
 
            for (MemberInfo info : memberInfo) {
 
                Log.i(WirelessQA.TAG, "------add memberInfo----------");
 
                Log.i(WirelessQA.TAG, info.name + "/" + info.age + "/" + info.website + "/" + info.weibo);
 
                // 向表info中插入数据
 
                db.execSQL("INSERT INTO info VALUES(null,?,?,?,?)", new Object[] { info.name, info.age, info.website,
 
                        info.weibo });
 
            }
 
            db.setTransactionSuccessful();// 事务成功
 
        } finally {
 
            db.endTransaction();// 结束事务
 
        }
 
    }
 
 
 
    /**
 
     * @param _id
 
     * @param name
 
     * @param age
 
     * @param website
 
     * @param weibo
 
     */
 
    public void add(int _id, String name, int age, String website, String weibo) {
 
        Log.i(WirelessQA.TAG, "------add data----------");
 
        ContentValues cv = new ContentValues();
 
        // cv.put("_id", _id);
 
        cv.put("name", name);
 
        cv.put("age", age);
 
        cv.put("website", website);
 
        cv.put("weibo", weibo);
 
        db.insert(DBHelper.DB_TABLE_NAME, null, cv);
 
        Log.i(WirelessQA.TAG, name + "/" + age + "/" + website + "/" + weibo);
 
    }
 
 
 
    /**
 
     * 通过name来删除数据
 
     *
 
     * @param name
 
     */
 
    public void delData(String name) {
 
        // ExecSQL("DELETE FROM info WHERE name ="+"'"+name+"'");
 
        String[] args = { name };
 
        db.delete(DBHelper.DB_TABLE_NAME, "name=?", args);
 
        Log.i(WirelessQA.TAG, "delete data by " + name);
 
 
 
    }
 
 
 
    /**
 
     * 清空数据
 
     */
 
    public void clearData() {
 
        ExecSQL("DELETE FROM info");
 
        Log.i(WirelessQA.TAG, "clear data");
 
    }
 
 
 
    /**
 
     * 通过名字查询信息,返回所有的数据
 
     *
 
     * @param name
 
     */
 
    public ArrayList<MemberInfo> searchData(final String name) {
 
        String sql = "SELECT * FROM info WHERE name =" + "'" + name + "'";
 
        return ExecSQLForMemberInfo(sql);
 
    }
 
 
 
    public ArrayList<MemberInfo> searchAllData() {
 
        String sql = "SELECT * FROM info";
 
        return ExecSQLForMemberInfo(sql);
 
    }
 
 
 
    /**
 
     * 通过名字来修改值
 
     *
 
     * @param raw
 
     * @param rawValue
 
     * @param whereName
 
     */
 
    public void updateData(String raw, String rawValue, String whereName) {
 
        String sql = "UPDATE info SET " + raw + " =" + " " + "'" + rawValue + "'" + " WHERE name =" + "'" + whereName
 
                     + "'";
 
        ExecSQL(sql);
 
        Log.i(WirelessQA.TAG, sql);
 
    }
 
 
 
    /**
 
     * 执行SQL命令返回list
 
     *
 
     * @param sql
 
     * @return
 
     */
 
    private ArrayList<MemberInfo> ExecSQLForMemberInfo(String sql) {
 
        ArrayList<MemberInfo> list = new ArrayList<MemberInfo>();
 
        Cursor c = ExecSQLForCursor(sql);
 
        while (c.moveToNext()) {
 
            MemberInfo info = new MemberInfo();
 
            info._id = c.getInt(c.getColumnIndex("_id"));
 
            info.name = c.getString(c.getColumnIndex("name"));
 
            info.age = c.getInt(c.getColumnIndex("age"));
 
            info.website = c.getString(c.getColumnIndex("website"));
 
            info.weibo = c.getString(c.getColumnIndex("weibo"));
 
            list.add(info);
 
        }
 
        c.close();
 
        return list;
 
    }
 
 
 
    /**
 
     * 执行一个SQL语句
 
     *
 
     * @param sql
 
     */
 
    private void ExecSQL(String sql) {
 
        try {
 
            db.execSQL(sql);
 
            Log.i("execSql: ", sql);
 
        } catch (Exception e) {
 
            Log.e("ExecSQL Exception", e.getMessage());
 
            e.printStackTrace();
 
        }
 
    }
 
 
 
    /**
 
     * 执行SQL，返回一个游标
 
     *
 
     * @param sql
 
     * @return
 
     */
 
    private Cursor ExecSQLForCursor(String sql) {
 
        Cursor c = db.rawQuery(sql, null);
 
        return c;
 
    }
 
 
 
    public void closeDB() {
 
        db.close();
 
    }
 
 
 
}

会员信息的JavaBean – MemberInfo.java

 
package com.wirelessqa.sqlite;
 
 
 
/**
 
 * 会员信息的javabean
 
 * @author bixiaopeng 2013-2-16 下午3:07:02
 
 */
 
public class MemberInfo {
 
 
 
    public int    _id;
 
    public String name;
 
    public int age;
 
    public String website;
 
    public String weibo;
 
    public MemberInfo(){}
 
    public MemberInfo(int _id,String name,int age,String website,String weibo){
 
        this._id = _id;
 
        this.name = name;
 
        this.age = age;
 
        this.website = website;
 
        this.weibo = weibo;
 
    }
 
 
 
}

首页显示 – MainActivity.java

显示结果页 – DisplayActivity.java

 
package com.wirelessqa.sqlite;
 
 
 
import android.os.Bundle;
 
import android.widget.TextView;
 
 
 
/**
 
 * 显示结果
 
 * @author bixiaopeng 2013-2-16 下午3:06:36
 
 */
 
public class DisplayActivity extends MainActivity{
 
    private String result = null;
 
    private TextView display = null;
 
    @Override
 
    protected void onCreate(Bundle savedInstanceState) {
 
        super.onCreate(savedInstanceState);
 
        setContentView(R.layout.activity_display);
 
        Bundle extras = getIntent().getExtras();
 
       result = extras.getString("searchResult");
 
       display = (TextView)findViewById(R.id.display_txt);
 
       display.setText(result);
 
 
 
    }
 
}