Android SQLite增删改查

• 创建数据库

public class DbHelper extends SQLiteOpenHelper {

    private static final String DATABASE = "com.vlike.1.0.db";
    private static final int VERSION = 1;

//    private static final String NOTES_TABLE = "CREATE TABLE " + NotesDao.TABLE_NAME + "("
//            + NotesDao.ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
//            + NotesDao.PATH + " TEXT NOT NULL,"
//            + NotesDao.CONTENT + " TEXT NOT NULL,"
//            + NotesDao.TYPE + " TEXT NOT NULL,"
//            + NotesDao.TIME + " TEXT NOT NULL,"
//            + NotesDao.HTML + " TEXT NOT NULL)";

    private static final String NOTES_TABLE = "create table notes (" +
            "_id integer primary key autoincrement," +
            "path text not null," +
            "content text not null," +
            "type text not null," +
            "time text not null," +
            "html text not null)";

    public DbHelper(Context context) {
        super(context, DATABASE, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(NOTES_TABLE);
    }

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

    }
}

• 创建具体的数据库对象

public class NotesDao {

//    static final String TABLE_NAME = "notes";
//    static final String ID = "_id";
//    static final String PATH = "path";
//    static final String CONTENT = "content";
//    static final String TYPE = "type";
//    static final String TIME = "time";
//    static final String HTML = "html";

    private DbHelper dbHelper;

    public NotesDao(Context context) {
        dbHelper = new DbHelper(context);
    }

    public void saveNotes(Notes notes) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.beginTransaction();
            db.execSQL("insert into notes values (" +
                    "_id " + notes.getId() +
                    "path " + notes.getImagePath() +
                    "content " + notes.getContent() +
                    "type " + notes.getTagType() +
                    "time " + notes.getTime() +
                    "html " + notes.getHtml() + ")");
//            ContentValues values = new ContentValues();
//            values.put(ID, notes.getId());
//            values.put(PATH, notes.getImagePath());
//            values.put(CONTENT, notes.getContent());
//            values.put(TYPE, notes.getTagType());
//            values.put(TIME, notes.getTime());
//            values.put(HTML, notes.getHtml());
//            db.insert(TABLE_NAME, null, values);
            db.setTransactionSuccessful();
            db.endTransaction();
        }
        db.close();
    }

    public void deleteNotes(Notes notes) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.beginTransaction();
            db.execSQL("delete from notes where _id = " + notes.getId());
//            db.delete(TABLE_NAME, "_id=?", new String[]{notes.getNotesId()});
            db.setTransactionSuccessful();
            db.endTransaction();
        }
        db.close();
    
    public void updateNotes(Notes notes) {
        SQLiteDatabase db = dbHelper.getWriterDatabase();
        if （db.isOpen()) {
          db.beginTransaction();
          ContentValues values = new ContentValues();
          values.put(PATH, notes.getImagePaht());
          db.update(TABLE_NAME, values, "_id=?", new String[]{notes.getId()});
          db.setTransactionSuccessful();
          db.endTransaction();
        }
        db.close();
    }

    public List<Notes> getAllNotes() {
        List<Notes> notesList = new ArrayList<Notes>();
        Notes notes;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        if (db.isOpen()) {
            db.beginTransaction();
            Cursor cursor = db.rawQuery("select * from notes", null);
//            Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
            while (cursor.moveToNext()) {
                notes = new Notes();
                notes.setNotesId(cursor.getString(cursor.getColumnIndex("_id")));
                notes.setImagePath(cursor.getString(cursor.getColumnIndex("path")));
                notes.setContent(cursor.getString(cursor.getColumnIndex("content")));
                notes.setTagType(cursor.getString(cursor.getColumnIndex("type")));
                notes.setTime(cursor.getString(cursor.getColumnIndex("time")));
                notes.setHtml(cursor.getString(cursor.getColumnIndex("html")));
                notesList.add(notes);
            }
            cursor.close();
            db.setTransactionSuccessful();
            db.endTransaction();
            db.close();

        }
        return notesList;
    }
}