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();