Android基础，小白人眼中，SQLite数据的使用方法

DbHelper

package com.Database;

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

public class DbHelper extends SQLiteOpenHelper {

    private static String db_name = DbSql.db_name;

    private static final int version = DbSql.version;

    private static DbHelper instance;

    public synchronized static DbHelper getInstance(Context context){

        if (instance != null){
            instance = null;
        }

        instance = new DbHelper(context, db_name);

        return instance;
    }

    private DbHelper(Context context, String db_name) {
        super(context, db_name, null, version);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);

        System.out.println("打开数据库");

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("创建数据库");
        db.execSQL(DbSql.create_user_table);
    }

    /**
     * 数据库版本更新
     * @param db
     * @param oldVersion 原数据库版本
     * @param newVersion 新数据库版本
     */
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        switch (oldVersion) {
            case 1:
                db.execSQL(DbSql.create_user_table);
            case 2:
                db.execSQL(DbSql.alter_user_add);
                db.execSQL(DbSql.alter_user_upd);
            case 3:
                db.execSQL(DbSql.alter_user_del);
            default:
                break;
        }

    }
}

DbSql

package com.Database;

public interface DbSql {


    String db_name = "data.sqlite";
    int version = 3;

    /**
     * version : 1
     */

    //用户表
    String user_table_name = "tb_user";
    String create_user_table = " create table if not exists " + user_table_name + "(" +
            "user_id integer primary key autoincrement, " +     //主键
            "username           text, " +                       //帐号
            "password          text, " +                        //密码
            "update_time        text)";                         //更新时间


    /**
     * version : 2
     */
    //用户表新增字段
    String alter_user_add = "";

    //用户表修改字段
    String alter_user_upd = "";

    /**
     * version : 3
     */
    //用户表删除字段
    String alter_user_del = "";
}

ResultSet

package com.Database;

import android.annotation.TargetApi;
import android.content.ContentResolver;
import android.database.CharArrayBuffer;
import android.database.ContentObserver;
import android.database.Cursor;
import android.database.DataSetObserver;
import android.net.Uri;
import android.os.Build;
import android.os.Bundle;
import android.support.annotation.NonNull;
import android.util.Log;

public class ResultSet implements Cursor {

    private Cursor _cursor;

    public ResultSet(@NonNull Cursor cursor) {
        _cursor = cursor;
    }

    public boolean next() {
        return _cursor.moveToNext();
    }

    public Cursor getCursor() {
        return _cursor;
    }

    public void close() {
        if (_cursor == null) return;
        _cursor.close();
        _cursor = null;
    }

    public byte[] getBlob(String columnName) {
        return getBlob(getColumnIndex(columnName));
    }

    @Override
    public byte[] getBlob(int columnIndex) {
        if (columnIndex == -1) return null;
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return null;
                case FIELD_TYPE_FLOAT: return null;
                case FIELD_TYPE_STRING: return _cursor.getString(columnIndex).getBytes();
                case FIELD_TYPE_BLOB: return _cursor.getBlob(columnIndex);
                case FIELD_TYPE_NULL: return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(), "获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return null;
    }

    public String getString(String columnName) {
        return getString(getColumnIndex(columnName), null);
    }

    public String getString(int columnIndex, String charsetName) {
        if (columnIndex == -1) return null;
        if (charsetName == null) { charsetName = "utf-8"; }
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return String.valueOf(_cursor.getLong(columnIndex));
                case FIELD_TYPE_FLOAT: return String.valueOf(_cursor.getDouble(columnIndex));
                case FIELD_TYPE_STRING: return _cursor.getString(columnIndex);
                case FIELD_TYPE_BLOB: return new String(_cursor.getBlob(columnIndex), charsetName);
                case FIELD_TYPE_NULL: return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(),"获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return null;
    }

    @Override
    public String getString(int columnIndex) {
        return getString(columnIndex, null);
    }

    public short getShort(String columnName) {
        return getShort(getColumnIndex(columnName));
    }

    @Override
    public short getShort(int columnIndex) {
        if (columnIndex == -1) return 0;
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return _cursor.getShort(columnIndex);
                case FIELD_TYPE_FLOAT: return (short) Math.ceil(_cursor.getDouble(columnIndex));
                case FIELD_TYPE_STRING:
                    String value = _cursor.getString(columnIndex);
                    if (value != null && value.equalsIgnoreCase("null")) return 0;
                    return Short.parseShort(value);
                case FIELD_TYPE_BLOB: return 0;
                case FIELD_TYPE_NULL: return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(),"获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return 0;
    }

    public int getInt(String columnName) {
        return getInt(getColumnIndex(columnName));
    }

    @Override
    public int getInt(int columnIndex) {
        if (columnIndex == -1) return 0;
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return _cursor.getInt(columnIndex);
                case FIELD_TYPE_FLOAT: return (int) Math.ceil(_cursor.getDouble(columnIndex));
                case FIELD_TYPE_STRING:
                    String value = _cursor.getString(columnIndex);
                    if (value != null && value.equalsIgnoreCase("null")) return 0;
                    return Integer.parseInt(value);
                case FIELD_TYPE_BLOB: return 0;
                case FIELD_TYPE_NULL: return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(),"获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return 0;
    }

    public long getLong(String columnName) {
        return getLong(getColumnIndex(columnName));
    }

    @Override
    public long getLong(int columnIndex) {
        if (columnIndex == -1) return 0;
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return _cursor.getLong(columnIndex);
                case FIELD_TYPE_FLOAT: return (long) Math.ceil(_cursor.getDouble(columnIndex));
                case FIELD_TYPE_STRING:
                    String value = _cursor.getString(columnIndex);
                    if (value != null && value.equalsIgnoreCase("null")) return 0;
                    return Long.parseLong(value);
                case FIELD_TYPE_BLOB: return 0;
                case FIELD_TYPE_NULL: return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(),"获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return 0;
    }

    public float getFloat(String columnName) {
        return getFloat(getColumnIndex(columnName));
    }

    @Override
    public float getFloat(int columnIndex) {
        if (columnIndex == -1) return 0;
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return _cursor.getLong(columnIndex);
                case FIELD_TYPE_FLOAT: return _cursor.getFloat(columnIndex);
                case FIELD_TYPE_STRING:
                    String value = _cursor.getString(columnIndex);
                    if (value != null && value.equalsIgnoreCase("null")) return 0;
                    return Float.parseFloat(value);
                case FIELD_TYPE_BLOB: return 0;
                case FIELD_TYPE_NULL: return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(),"获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return 0;
    }

    public double getDouble(String columnName) {
        return getDouble(getColumnIndex(columnName));
    }

    @Override
    public double getDouble(int columnIndex) {
        if (columnIndex == -1) return 0;
        int type = getType(columnIndex);
        try {
            switch (type) {
                case FIELD_TYPE_INTEGER: return _cursor.getLong(columnIndex);
                case FIELD_TYPE_FLOAT: return _cursor.getDouble(columnIndex);
                case FIELD_TYPE_STRING:
                    String value = _cursor.getString(columnIndex);
                    if (value != null && value.equalsIgnoreCase("null")) return 0;
                    return Double.parseDouble(value);
                case FIELD_TYPE_BLOB: return 0;
                case FIELD_TYPE_NULL: return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(getClass().getName(),"获取字段:" + _cursor.getColumnName(columnIndex) + "出错:" + e.getMessage());
        }
        return 0;
    }

    @TargetApi(Build.VERSION_CODES.HONEYCOMB)
    @Override
    public int getType(int columnIndex) {
        return _cursor.getType(columnIndex);
    }

    @Override
    public boolean isClosed() {
        return _cursor == null;
    }

    @Override
    public int getCount() {
        return _cursor.getCount();
    }

    @Override
    public int getPosition() {
        return _cursor.getPosition();
    }

    @Override
    public boolean move(int offset) {
        return _cursor.move(offset);
    }

    @Override
    public void copyStringToBuffer(int columnIndex, CharArrayBuffer buffer) {
        _cursor.copyStringToBuffer(columnIndex,buffer);
    }

    @Override
    public boolean moveToPosition(int position) {
        return _cursor.moveToPosition(position);
    }

    @Override
    public boolean moveToFirst() {
        return _cursor.moveToFirst();
    }

    @Override
    public boolean moveToLast() {
        return _cursor.moveToLast();
    }

    @Override
    public boolean moveToNext() {
        return _cursor.moveToNext();
    }

    @Override
    public boolean moveToPrevious() {
        return _cursor.moveToPrevious();
    }

    @Override
    public boolean isFirst() {
        return _cursor.isFirst();
    }

    @Override
    public boolean isLast() {
        return _cursor.isLast();
    }

    @Override
    public boolean isBeforeFirst() {
        return _cursor.isBeforeFirst();
    }

    @Override
    public boolean isAfterLast() {
        return _cursor.isAfterLast();
    }

    @Override
    public int getColumnIndex(String columnName) {
        return _cursor.getColumnIndex(columnName);
    }

    @Override
    public int getColumnIndexOrThrow(String columnName) throws IllegalArgumentException {
        return _cursor.getColumnIndexOrThrow(columnName);
    }

    @Override
    public String getColumnName(int columnIndex) {
        return _cursor.getColumnName(columnIndex);
    }

    @Override
    public String[] getColumnNames() {
        return _cursor.getColumnNames();
    }

    @Override
    public int getColumnCount() {
        return _cursor.getColumnCount();
    }

    @Override
    public boolean isNull(int columnIndex) {
        return _cursor.isNull(columnIndex);
    }

    @Override
    public void deactivate() {
        _cursor.deactivate();
    }

    @Override
    public boolean requery() {
        return _cursor.requery();
    }

    @Override
    public void registerContentObserver(ContentObserver observer) {
        _cursor.registerContentObserver(observer);
    }

    @Override
    public void unregisterContentObserver(ContentObserver observer) {
        _cursor.unregisterContentObserver(observer);
    }

    @Override
    public void registerDataSetObserver(DataSetObserver observer) {
        _cursor.registerDataSetObserver(observer);
    }

    @Override
    public void unregisterDataSetObserver(DataSetObserver observer) {
        _cursor.unregisterDataSetObserver(observer);
    }

    @Override
    public void setNotificationUri(ContentResolver cr, Uri uri) {
        _cursor.setNotificationUri(cr, uri);
    }

    @TargetApi(Build.VERSION_CODES.KITKAT)
    @Override
    public Uri getNotificationUri() {
        return _cursor.getNotificationUri();
    }

    @Override
    public boolean getWantsAllOnMoveCalls() {
        return _cursor.getWantsAllOnMoveCalls();
    }

    @Override
    public Bundle getExtras() {
        return _cursor.getExtras();
    }

    @TargetApi(Build.VERSION_CODES.M)
    public void setExtras(Bundle extras) {
        _cursor.setExtras(extras);
    }


    @Override
    public Bundle respond(Bundle extras) {
        return _cursor.respond(extras);
    }
}

Dao

package com.Database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class Dao {

    private static Dao instance;

    public synchronized static Dao getInstance(Context context) {

        if (instance != null){
            instance = null;
        }

        instance = new Dao(context);

        return instance;
    }

    private DbHelper helper;
    private SQLiteDatabase db;

    public Dao(Context context){
        helper = DbHelper.getInstance(context);
    }

    public interface OnCallback {
        void onComplete(Object result);
        void onFailed(Exception e);
    }

    //增加
    public boolean upd(String sql, Object[] obj){

        try{
            db = helper.getWritableDatabase();
            db.execSQL(sql,obj);
        }catch (Exception e){
            return false;
        }finally {
            close();
        }
        return true;
    }

    //删除
    public boolean del(String sql){

        try{
            db = helper.getWritableDatabase();
            db.execSQL(sql);
        }catch (Exception e){
            return false;
        }finally {
            close();
        }
        return true;
    }

    //查询
    public ResultSet query(String sql) {

        ResultSet rs;

        try {
            db = helper.getReadableDatabase();
            Cursor cursor = db.rawQuery(sql, null);
            rs = new ResultSet(cursor);
        }catch (Exception e){
            return null;
        }

        return rs;
    }

    public void close() {
        if (db != null){
            db.close();
        }
    }
}