Android数据存储之Sqlite数据库的俩种使用方式

一概述
在Android开发中数据存储包括 SharedPreference、文件存储、数据库存储、ContentProvider、网络存储，本文讲述使用Sqlite数据库存储的俩种基本使用方式

1、第一种使用SQL语句实现

        编写代码逻辑:使用SqliteOpenHelper类创建一个数据库                   创建一个访问数据库增删改查的的接口                   封装一个访问数据库增删改查的具实现类

一、DBhelper类

package db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBhelper extends SQLiteOpenHelper {    private static DBhelper mDbHelper = null;    private static final String db_name = "friend.db";    private static final int version = 1;    private static final String CREATE_MESSAGE_TABLE = "create table message_info(_id integer primary key autoincrement," +            "message_id integer,message_userhead text,message_context text,message_imagename text," +            "message_username text,message_time text)";    private static final String DORP_MESSAGE_TABLE = "drop table if exists message_info";    private DBhelper(Context context) {        super(context, db_name, null, version);    }    public static DBhelper getInstance(Context context) {        if (mDbHelper == null) {                synchronized (new Object())                {                     if (mDbHelper == null)                     {                          mDbHelper = new DBhelper(context);                     }                }        }        return mDbHelper;    }    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL(CREATE_MESSAGE_TABLE);    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        db.execSQL(DORP_MESSAGE_TABLE);        db.execSQL(CREATE_MESSAGE_TABLE);    }}

2、封装一个泛型的增删改查的接口

package db;import java.util.List;public interface myDbServer<T> {     //插入多条     public void insertMore(List<T> params);     //插入一条     public void insertOne(T params);     //删除一条     public void deleteOne(int  params);     //查询多条     public List<T> getMore();     //查询一条     public  T getOne(int parmas);}

3、根据存储的JavaBean写实现类

package db;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import java.util.LinkedList;import java.util.List;import Bean.MessageBean;public class MessageDao implements myDbServer<MessageBean> {    private DBhelper mDBhelper = null;    private Context context = null;    public MessageDao(Context context) {        this.context = context;        mDBhelper = DBhelper.getInstance(this.context);    }    /**     * 插入多条说说信息     *     * @param params     */    @Override    public void insertMore(List<MessageBean> params) {        String sql = "insert into message_info(message_id,message_username,message_userhead,message_context," +                "message_time,message_imagename)" + "values(?,?,?,?,?,?)";        SQLiteDatabase db = mDBhelper.getWritableDatabase();        db.delete("message_info",null,null);        for (MessageBean messageBean : params) {            db.execSQL(sql, new Object[]{messageBean.getId(), messageBean.getUserName(), messageBean.getUserHead(),                    messageBean.getContext(), messageBean.getTime(), messageBean.getImageName()});        }        db.close();    }    /**     * 插入一条说说信息     *     * @param params     */    @Override    public void insertOne(MessageBean params) {        String sql = "insert into message_info(message_id,message_username,message_userhead,message_context," +                "message_time,message_imagename)" + "values(?,?,?,?,?,?)";        SQLiteDatabase db = mDBhelper.getWritableDatabase();        db.execSQL(sql, new Object[]{params.getId(), params.getUserName(), params.getUserHead(), params.getContext(),                params.getTime(), params.getImageName()});        db.close();    }    /**     * 删除一条说说信息     *     * @param params     */    @Override    public void deleteOne(int params) {        String sql = "delete from message_info where message_id  = ? ";        SQLiteDatabase db = mDBhelper.getWritableDatabase();        db.execSQL(sql, new Object[]{params});        db.close();    }    /**     * 获取多条说说信息     *     * @return     */    @Override    public List<MessageBean> getMore() {        String sql = "select*from message_info";        List<MessageBean> list = new LinkedList<MessageBean>();        SQLiteDatabase db = mDBhelper.getReadableDatabase();        Cursor cursor = db.rawQuery(sql, null);        while (cursor.moveToNext()) {            MessageBean messageBean = new MessageBean();            messageBean.setContext(cursor.getString(cursor.getColumnIndex("message_context")));            messageBean.setImageName(cursor.getString(cursor.getColumnIndex("message_imagename")));            messageBean.setUserHead(cursor.getString(cursor.getColumnIndex("message_userhead")));            messageBean.setUserName(cursor.getString(cursor.getColumnIndex("message_username")));            messageBean.setTime(cursor.getString(cursor.getColumnIndex("message_time")));            messageBean.setId(cursor.getInt(cursor.getColumnIndex("message_id")));            list.add(messageBean);        }        cursor.close();        db.close();        return list;    }    /**     * 获取一条信息     *     * @param parmas     * @return     */    @Override    public MessageBean getOne(int parmas) {        String SQL = "select*from message_info where message_id = ?";        SQLiteDatabase db = mDBhelper.getReadableDatabase();        Cursor cursor = db.rawQuery(SQL, new String[]{parmas + ""});        MessageBean messageBean = new MessageBean();        messageBean.setContext(cursor.getString(cursor.getColumnIndex("message_context")));        messageBean.setImageName(cursor.getString(cursor.getColumnIndex("message_imagename")));        messageBean.setUserHead(cursor.getString(cursor.getColumnIndex("message_userhead")));        messageBean.setUserName(cursor.getString(cursor.getColumnIndex("message_username")));        messageBean.setTime(cursor.getString(cursor.getColumnIndex("message_time")));        messageBean.setId(cursor.getInt(cursor.getColumnIndex("message_id")));        cursor.close();        db.close();        return messageBean;    }}

写好以上的类后就可以在访问到网络的数据后存储在数据库中了，把从服务器下载的图片资源可以保存在本地。

sql语句
1、创表语句 ： create table 表名( _id Integer primary key autoincrement,user_id Integer,user_name text,…..字段名 数据类型)

2、删表语句：drop table if exists 表名

3、查询语句：select*from 表名 where id = ?

4、删除语句：delete from 表名 where id = ?

5、插入语句：insert into 表名 （字段名….）values(?,?,?…)

6、更新语句：update 表名 set user_name = ? where id = ?

第二种方式使用SQLiteDatabase中方法

1、db.insert();
2、db.delete();
3、db.update();
4、db.query();