安卓使用本地SQLite数据库进行数据存储

首先根据字段对数据库进行新建

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

public class NotesDB extends SQLiteOpenHelper {

    public static final String TABLE_NAME_NOTES = "note";
    public static final String COLUMN_NAME_ID = "_id";
    public static final String COLUMN_NAME_NOTE_TITLE = "title";
    public static final String COLUMN_NAME_NOTE_CONTENT = "content";
    public static final String COLUMN_NAME_NOTE_DATE = "date";

    public NotesDB(Context context) {
        super(context, "note", null, 1);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + TABLE_NAME_NOTES + "(" + COLUMN_NAME_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_NAME_NOTE_CONTENT + " TEXT NOT NULL DEFAULT"","
                + COLUMN_NAME_NOTE_DATE + " TEXT NOT NULL DEFAULT""," + COLUMN_NAME_NOTE_TITLE +" TEXT NOT NULL DEFAULT"")";
        Log.d("SQL", sql);
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {  //更新数据库操作
        // TODO Auto-generated method stub
        /*String sql_upgrade = "alter table note add  " + COLUMN_NAME_NOTE_TITLE + " TEXT NOT NULL DEFAULT";//增加一个列title
        Log.d("SQL", sql_upgrade);
        db.execSQL(sql_upgrade);*/
    }
}

然后对数据库进行调用

DB = new NotesDB(this);
dbread = DB.getReadableDatabase();

下面简单列举所使用的功能 新增

public static void addNote(SQLiteDatabase db, String title, String content, String date) {
        ContentValues values = new ContentValues();
        //values.put("id", null);
        values.put("title", title);
        values.put("content", content);
        values.put("date", date);
        db.insert("note", null, values);
        //      String sql1="insert into "+TABLE_NAME_NOTES+"values("+"1,"+"'写作业',"+"'晚上要写作业的干活'"+")";
//      Log.d("SQL1", sql1);
//      db.execSQL(sql1);
    }

删除所有数据

public static void deleteAllNote(SQLiteDatabase db) {
        // 清空数据库中表的内容
        db.execSQL("delete from note");
    }

根据ID删除数据 deleteNoteByID

public static void deleteNoteByID(SQLiteDatabase db, String ID) {
        db.delete("note", "_id=?", new String[]{ID});
    }

根据ID更新数据 updateNoteByID

public static void updateNoteByID(SQLiteDatabase db, String ID, String title, String content, String date) {
        ContentValues values = new ContentValues();
        values.put("title", title);
        values.put("content", content);
        values.put("date", date);
        db.update("note", values, "_id=?", new String[]{ID});
    }

根据ID查询数据 findNoteByID

public static NoteModel findNoteByID(SQLiteDatabase db, String ID) {
        //String sql = "select * from note where _id=" + ID;
        Cursor cursor = db.query("note", new String[]{"_id", "content", "date", "title"},
                "_id=?", new String[]{ID}, null, null, null);
        //Cursor cursor = db.rawQuery(sql, new String[]{});
        if (cursor.moveToNext()) {
            NoteModel noteModel = new NoteModel();
            noteModel.setId(ID);
            noteModel.setContent(cursor.getString(1));
            noteModel.setDate(cursor.getString(2));
            noteModel.setTitle(cursor.getString(3));
            return noteModel;
        }
        cursor.close();
        return null;
    }

获取总记录条数 getCounts

public static long getCounts(SQLiteDatabase db) {
        Cursor cursor = db.query("note", new String[]{"count(*)"}, null, null, null, null, null);
        if (cursor.moveToNext()) {
            return cursor.getLong(0);
        }
        return 0;
    }

查询全部数据

public static ArrayList<NoteModel> findAllnote(SQLiteDatabase db) {
        ArrayList<NoteModel> list = new ArrayList<NoteModel>();
        Cursor cursor = db.rawQuery("select * from note", null);
        for (int i = 0; i < cursor.getCount(); i++) {
            cursor.moveToNext();
            NoteModel noteModel = new NoteModel();
            noteModel.setId(cursor.getString(0));
            noteModel.setContent(cursor.getString(1));
            noteModel.setDate(cursor.getString(2));
            noteModel.setTitle(cursor.getString(3));
            list.add(noteModel);
        }
        cursor.close();
        db.close();
        return list;
    }

根据关键字查询数据 findNoteByKeyword

public static ArrayList<NoteModel> findNoteByKeyword(SQLiteDatabase db, String keyword) {
        ArrayList<NoteModel> list = new ArrayList<NoteModel>();
        Cursor cursor = db.rawQuery("select * from note where title like '%" + keyword + "%'", null);
        for (int i = 0; i < cursor.getCount(); i++) {
            cursor.moveToNext();
            NoteModel noteModel = new NoteModel();
            noteModel.setId(cursor.getString(0));
            noteModel.setContent(cursor.getString(1));
            noteModel.setDate(cursor.getString(2));
            noteModel.setTitle(cursor.getString(3));
            list.add(noteModel);
        }
        cursor.close();
        db.close();
        return list;
    }