【Android】SQLite

1，概述

android端提供的轻量级数据库，

2，实例I

（1）创建SQLiteOpenHelper

public class DBHelper extends SQLiteOpenHelper {

    private static DBHelper sDBHelper;

    public static void init(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        sDBHelper = new DBHelper(context, name, factory, version);
    }

    public static DBHelper getInstance() {
        if (sDBHelper == null) {
            throw new RuntimeException("DBHelper must be init!");
        }
        return sDBHelper;
    }

    public DBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public DBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version, @Nullable DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    public DBHelper(@Nullable Context context, @Nullable String name, int version, @NonNull SQLiteDatabase.OpenParams openParams) {
        super(context, name, version, openParams);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建数据库
        db.execSQL("create table user(u_id int not null primary key,u_name varchar(50) not null,age int)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 数据库升级时回调
    }
}

（2）初始化

 （3）使用

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        //原生SQLite
        DBHelper.getInstance().getWritableDatabase().execSQL("insert into table values(id,key1,key2)");
    }
}

除使用sql语句外，可以使用insert、update、delete、query方法，具体方法如下，

public long insert(String table, String nullColumnHack, ContentValues values)

//whereClause是where语句，如"id > ?" ,? 占位符在whereArgs中传入;
public int update(String table, ContentValues values, String whereClause, String[] whereArgs)

public int delete(String table, String whereClause, String[] whereArgs)

public Cursor query(boolean distinct, String table, String[] columns,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit)

public Cursor query(boolean distinct, String table, String[] columns,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit, CancellationSignal cancellationSignal) 

public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy)

public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy, String limit)

3，实例II

public class DataBaseHelper extends SQLiteOpenHelper {

    public static final String TAG = "DataBaseHelper";
    private static final String CREATE_TEXT_TABLE = "create table Text(id integer primary key autoincrement,"
            + "title varchar,"
            + "text text,"
            + "update_time varchar)";
    private SQLiteDatabase db;


    private DataBaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        Log.d(TAG, "创建db");
    }

    public static DataBaseHelper newInstance(Context context) {
        return new DataBaseHelper(context, "demo.db", null, 1);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TEXT_TABLE);
        Log.d(TAG, "创建db" + "   " + db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void insertText(Text text) {
        this.db = getWritableDatabase();
        String insert = "insert into Text (title,text,update_time) values(?,?,?)";
        this.db.execSQL(insert, new Object[]{text.getTitle(), text.getText(), text.getUpdate_time()});
        this.db.close();

        Log.d(TAG, "insert " + text);
    }

    public void insertDraftText(Text text) {
        deleteDraftText();
        this.db = getWritableDatabase();
        String insert = "insert into Text (id,title,text,update_time) values(-1,?,?,?)";
        this.db.execSQL(insert, new Object[]{text.getTitle(), text.getText(), text.getUpdate_time()});
        this.db.close();
        Log.d(TAG, "draft " + text);
    }

    public void deleteDraftText() {
        deleteTextById(-1);
        Log.d(TAG, "remove draft ");
    }

    public void updateText(Text text) {
        this.db = getWritableDatabase();
        String update = "update Text set title=?,text=?,update_time=? where id = ?";
        this.db.execSQL(update, new Object[]{text.getTitle(), text.getText(), text.getUpdate_time(), text.getId()});
        this.db.close();
        Log.d(TAG, "update " + text);
    }


    public void deleteTextById(int id) {
        this.db = getWritableDatabase();
        String delete = "delete from Text where id = " + id;
        db.execSQL(delete);
        this.db.close();
        Log.d(TAG, "delete " + id);
    }

    public void deleteTextByIds(List<Integer> ids) {
        this.db = getWritableDatabase();
        for (int id : ids) {
            String delete = "delete from Text where id = " + id;
            db.execSQL(delete);
            Log.d(TAG, "delete " + id);
        }
        this.db.close();
    }

    public List<Text> queryAll() {
        this.db = getReadableDatabase();
        Cursor cursor = null;
        List<Text> textList = new ArrayList<>();
        try {
            // id = -1 草稿
            cursor = db.query("Text", null, "id >= 0", null, null, null, "id desc", null);

            boolean hasNext = cursor.moveToFirst();
            while (hasNext) {
                String id = cursor.getString(cursor.getColumnIndex("id"));
                String title = cursor.getString(cursor.getColumnIndex("title"));
                String text = cursor.getString(cursor.getColumnIndex("text"));
                String update_time = cursor.getString(cursor.getColumnIndex("update_time"));
                textList.add(new Text(
                        Integer.parseInt(id),
                        title,
                        text,
                        update_time
                ));
                hasNext = cursor.moveToNext();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        this.db.close();
        Log.d(TAG, "queryAll " + textList.toString());
        return textList;
    }

    public Text queryOneById(int id) {
        this.db = getReadableDatabase();
        Cursor cursor = null;
        Text obj = new Text();
        try {
            cursor = db.query("Text", null, "id=?", new String[]{String.valueOf(id)}, null, null, null, null);

            cursor.moveToFirst();
            String title = cursor.getString(cursor.getColumnIndex("title"));
            String text = cursor.getString(cursor.getColumnIndex("text"));
            String update_time = cursor.getString(cursor.getColumnIndex("update_time"));
            obj.setId(id);
            obj.setText(text);
            obj.setTitle(title);
            obj.setUpdate_time(update_time);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        this.db.close();
        Log.d(TAG, "queryOneById " + obj);
        return obj;
    }
}