Sqlite数据库存储数据

Sqlite数据库的使用，这里直接贴代码

数据库Helper类：

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.qihancloud.aibank.utils.LogUtil;

/**
 * Created by admin on 2017/6/19.
 */

public class BankDBHelper extends SQLiteOpenHelper implements BankProviderMetaData
{
    private final static String TAG = "bankdbhelper";
    private static BankDBHelper instance;

    public static BankDBHelper getInstance(Context context)
    {
        if (instance == null)
        {
            instance = new BankDBHelper(context.getApplicationContext());
        }
        return instance;
    }

    public BankDBHelper(Context context)
    {
        super(context, DB_NAME, null, VERSION);
        LogUtil.i(TAG, "==BankDBHelper==");
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        String TABLESQL = "create table if not exists "
                + TABLE_NAME + " ("
                + BANK_ID + " integer primary key,"
                + BANK_QLINK_ID + " integer,"
                + BANK_QUESTION + " text,"
                + BANK_ANSWER + " text,"
                + BANK_keyword + " text,"
                + BANK_TIME + " integer,"
                + BANK_URL + " text)";

        db.execSQL(TABLESQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion)
    {
        LogUtil.d(TAG, "Upgrading" + oldVersion + " to " + newVersion + "all old data");
        switch (oldVersion)
        {
            case 1:
                LogUtil.i(TAG, "===onUpgrade==oldVersion=");

            case 2:
        }
    }

    public void closeDB()
    {
        if (instance != null)
        {
            try
            {
                SQLiteDatabase db = instance.getWritableDatabase();
                db.close();
            } catch (Exception e)
            {
                e.printStackTrace();
            }
            instance = null;
        }
    }

}

Provider类：

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.support.annotation.Nullable;
import android.support.v4.util.LogWriter;

import com.qihancloud.aibank.utils.LogUtil;

/**
 * Created by admin on 2017/6/19.
 */

public class BankProvider extends ContentProvider
{
    private static UriMatcher uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
    private static final int AIBANKS = 1;
    private static final int AIBANK = 2;
    private static final String TAG = "BankProvider";

    private BankDBHelper dbHelper;
    private SQLiteDatabase db;
    private Context mContext = getContext();

    /**
     * 这部分就相当于为外部程序准备好一个所有地址匹配集合
     */
    static
    {
        uriMatcher.addURI(BankProviderMetaData.AUTHORITY, BankProviderMetaData.TABLE_NAME, AIBANKS);

        uriMatcher.addURI(BankProviderMetaData.AUTHORITY, BankProviderMetaData.TABLE_NAME + "/#", AIBANK);
    }

    @Override
    public boolean onCreate()
    {
        LogUtil.i(TAG, "==onCreate==");
        dbHelper = new BankDBHelper(getContext());
        return (dbHelper == null) ? false : true;
    }

    @Nullable
    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder)
    {
        db = dbHelper.getReadableDatabase();
        switch (uriMatcher.match(uri))
        {
            case AIBANKS:
                //查询所有的
                return db.query(BankProviderMetaData.TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder);

            case AIBANK://查询某个Id的信息
                long id = ContentUris.parseId(uri);
                String where = "_id=" + id;
                if (selection != null && !"".equals(selection))
                {
                    where = selection + " and " + where;
                }
                return db.query(BankProviderMetaData.TABLE_NAME, projection, where, selectionArgs,
                        null, null, sortOrder);
            default:
                throw new IllegalArgumentException("unKnow Uri" + uri.toString());
        }
    }

    @Nullable
    @Override
    public String getType(Uri uri)
    {
        switch (uriMatcher.match(uri))
        {
            case AIBANKS:
                return BankProviderMetaData.CONTENT_LIST;
            case AIBANK:
                return BankProviderMetaData.CONTENT_ITEM;
            default:
                throw new IllegalArgumentException("Unknow Uri" + uri.toString());
        }
    }

    @Nullable
    @Override
    public Uri insert(Uri uri, ContentValues contentValues)
    {
        //取得数据库操作实例
        db = dbHelper.getWritableDatabase();
        switch (uriMatcher.match(uri))
        {
            case AIBANKS:
                //执行添加，返回行号，如果主健字段是自增长的，那么行号会等于主键id
                long rowId = db.insert(BankProviderMetaData.TABLE_NAME, null,contentValues);

                Uri insertUri = ContentUris.withAppendedId(uri, rowId);

                //发出数据变化通知(Projector表的数据发生变化)
                getContext().getContentResolver().notifyChange(uri, null);

                return insertUri;
            default:
                //不能识别uri
                throw new IllegalArgumentException("This is a unKnow Uri" + uri.toString());
        }
    }

    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs)
    {
        db = dbHelper.getWritableDatabase();
        int count = 0;
        switch (uriMatcher.match(uri))
        {
            case AIBANKS:
                count = db.delete(BankProviderMetaData.TABLE_NAME, selection, selectionArgs);
                return count;

            case AIBANK:
                long id = ContentUris.parseId(uri);
                String where = "_id=" + id;
                if (selection != null && !"".equals(selection))
                {
                    where = selection + " and " + where;
                }
                count = db.delete(BankProviderMetaData.TABLE_NAME, where, selectionArgs);
                return count;

            default:
                throw new IllegalArgumentException("unKnow Uri" + uri.toString());
        }
    }

    @Override
    public int update(Uri uri, ContentValues contentValues, String selection, String[] selectionArgs)
    {
        int count = 0;
        db = dbHelper.getWritableDatabase();
        switch (uriMatcher.match(uri))
        {
            case AIBANKS:
                count = db.update(BankProviderMetaData.TABLE_NAME, contentValues, selection, selectionArgs);
                return count;
            case AIBANK:
                long id = ContentUris.parseId(uri);
                String where = "_id=" + id;
                if (selection != null && !"".equals(selection))
                {
                    where = selection + " and " + where;
                }
                count =  db.update(BankProviderMetaData.TABLE_NAME, contentValues, where, selectionArgs);
                return count;

            default:
                throw new IllegalArgumentException("This is a unKnow Uri" + uri.toString());
        }
    }
}

BankProviderMetaData 常量类：

import android.net.Uri;

/**
 * Created by admin on 2016/6/7.
 */
public interface BankProviderMetaData
{
    // 定义外部访问的Authority
    public static final String AUTHORITY = "com.qihancloud.aibank";

    // 数据库名称
    public static final String DB_NAME = "aibank.db";
    // 数据库版本
    public static final int VERSION = 1;

    // 表名
    public static final String TABLE_NAME = "databank";

    // 外部程序访问本表的uri地址
    //Uri:content://com.qihancloud.aibank/databank
    public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + TABLE_NAME);

    // projector 列名
    public static final String BANK_ID = "id";                //问题序号int:1
    public static final String BANK_QLINK_ID = "qlinkid";     //问题序号int:1
    public static final String BANK_QUESTION = "question";    //什么问题标题
    public static final String BANK_ANSWER = "answer";        //问题答案
    public static final String BANK_keyword = "keyword";      //关键字
    public static final String BANK_TIME = "createtime";      //创建时间
    public static final String BANK_URL = "url";              //图片链接


    //默认排序
    public static final String SORT_ORDER = "_id desc";

    //得到aibank表中的所有记录
    public static final String CONTENT_LIST = "vnd.android.cursor.dir/aibank";

    //得到一个表信息
    public static final String CONTENT_ITEM = "vnd.android.cursor.item/aibank";
}

使用工具类：

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;

import com.qihancloud.aibank.activity.MainActivity;
import com.qihancloud.aibank.beans.Files;
import com.qihancloud.aibank.utils.LogUtil;

import java.util.List;

/**
 * Created by admin on 2017/6/19.
 */

public class BankUtils
{
    //查询保存的数据
    public static int queryData(Context context, String columnName)
    {
        ContentResolver contentResolver = context.getContentResolver();
        // 查找id为1的数据
        Cursor cursor = contentResolver.query(BankProviderMetaData.CONTENT_URI, null, null, null, null);

        //这里必须要调用 c.moveToFirst将游标移动到第一条数据,不然会出现index -1 requested , with a size of 1错误
        //cr.query返回的是一个结果集。
        int value = 0;
        if (cursor.moveToFirst())
        {
            do
            {
                // 获取字段的值
                value = cursor.getInt(cursor.getColumnIndex(columnName));
            } while (cursor.moveToNext());
        }
        //Log.i("queryProjector", columnName + "=" + value);
        cursor.close();
        return value;
    }

    //查询保存的所有数据
    public static Cursor queryDatas(Context context)
    {
        ContentResolver contentResolver = context.getContentResolver();
        String order = BankProviderMetaData.BANK_ID;
        // 查找id为1的数据
        Cursor cursor = contentResolver.query(BankProviderMetaData.CONTENT_URI, null, null, null, order);

        //这里必须要调用 c.moveToFirst将游标移动到第一条数据,不然会出现index -1 requested , with a size of 1错误
        //cr.query返回的是一个结果集。
        return cursor;
    }

    /**
     * 插入数据
     *
     * @param context
     * @param values
     */
    public static void insertdata(Context context, ContentValues[] values)
    {
        ContentResolver resolver = context.getContentResolver();
        Uri uri = Uri.parse("content://com.qihancloud.aibank/databank");
        //添加一条记录
        //resolver.insert(uri, values);
        resolver.bulkInsert(uri,values);
    }

    /**
     * 插入所有的数据
     * @param context
     * @param infos
     */
    public static void insertDatas(Context context,List<Files> infos)
    {
        int size = infos.size();
        SQLiteDatabase db = BankDBHelper.getInstance(context).getWritableDatabase();
        db.beginTransaction();
        if(size > 0)
        {
            Files info;
            for (int i = 0; i < size; i++)
            {
                info = infos.get(i);
                db.execSQL("insert into databank (id,qlinkid,question,answer,keyword,createtime,url)values('"
                        + info.getId() + "','"
                        + info.getQlinkid() + "','"
                        + info.getQuestion() + "','"
                        + info.getAnswer() + "','"
                        + info.getKeyword() + "','"
                        + info.getCreatetime() + "','"
                        + info.getUrl() + "')");
            }
            db.setTransactionSuccessful();
            db.endTransaction();
        }
    }

    public static void deleteDatas(Context context)
    {
        ContentResolver resolver = context.getContentResolver();
        //content://com.qihancloud.aibank/aibank
        Uri uri = Uri.parse("content://com.qihancloud.aibank/databank");
        //添加一条记录
        resolver.delete(uri, null, null);
    }

    public static Cursor queryIndistinct(Context context,String coluname,String keyword)
    {
        /*//1.使用这种query方法%号前不能加' ;
        Cursor c_test = mDatabase.query(tab_name, new String[]{tab_field02}, tab_field02 + "  LIKE ? ", new String[]{"%" + str[0] + "%"}, null, null, null);
        //2.使用这种query方法%号前必须加'  ;
        //  Cursor  c_test=mDatabase.query(tab_name, new String[]{tab_field02},tab_field02+"  like '%" + str[0] + "%'", null, null, null, null);
        //3.使用这种方式必须在%号前加'  ;
        String current_sql_sel = "SELECT  * FROM " + tab_name + " where " + tab_field02 + " like '%" + str[0] + "%'";
        //Cursor c_test = mDatabase.rawQuery(current_sql_sel, null);*/
        ContentResolver contentResolver = context.getContentResolver();
        String order = BankProviderMetaData.BANK_ID;
        // 查找id为1的数据

        //1.表 2.结果字段 3.条件 4.组合条件 5.排序方式
        Cursor cursor = contentResolver.query(BankProviderMetaData.CONTENT_URI, null, coluname + "  like '%" + keyword + "%'", null, order);

        //这里必须要调用 c.moveToFirst将游标移动到第一条数据,不然会出现index -1 requested , with a size of 1错误
        //cr.query返回的是一个结果集。
        return cursor;

    }

    public static Cursor queryKeyWord(Context context,String[] projection)
    {
        /*//1.使用这种query方法%号前不能加' ;
        Cursor c_test = mDatabase.query(tab_name, new String[]{tab_field02}, tab_field02 + "  LIKE ? ", new String[]{"%" + str[0] + "%"}, null, null, null);
        //2.使用这种query方法%号前必须加'  ;
        //  Cursor  c_test=mDatabase.query(tab_name, new String[]{tab_field02},tab_field02+"  like '%" + str[0] + "%'", null, null, null, null);
        //3.使用这种方式必须在%号前加'  ;
        String current_sql_sel = "SELECT  * FROM " + tab_name + " where " + tab_field02 + " like '%" + str[0] + "%'";
        //Cursor c_test = mDatabase.rawQuery(current_sql_sel, null);*/
        ContentResolver contentResolver = context.getContentResolver();
        String order = BankProviderMetaData.BANK_ID;
        // 查找id为1的数据

        //1.表 2.结果字段 3.条件 4.组合条件 5.排序方式
        Cursor cursor = contentResolver.query(BankProviderMetaData.CONTENT_URI, projection, null, null, order);

        //这里必须要调用 c.moveToFirst将游标移动到第一条数据,不然会出现index -1 requested , with a size of 1错误
        //cr.query返回的是一个结果集。
        return cursor;

    }
}