Android SQLite简单使用

DataBaseHelper实现：

/**
 * DataBaseHelper
 * Create by dyj on 2022226 21:44 pm
 */
public class DataBaseHelper extends SQLiteOpenHelper {
    /**
     * 创建支出表语句
     */
    private static final String CREATE_BILL = "create table if not exists table_bill(" + "id integer primary key autoincrement,"
            + "year integer," + "month integer," + "day integer,"
            + "consumption char(5)," + "remarks text," + "amount char(20))";
    /**
     * 创建收入账单表语句
     */
    private static final String CREATE_INCOME = "create table if not exists table_income(" + "id integer primary key autoincrement,"
            + "year integer," + "month integer," + "day integer,"
            + "consumption char(5),remarks text,amount char(20))";
    /**
     * 创建笔记表语句
     */
    private static final String CREATE_NOTE = "create table if not exists table_note(" + "id integer primary key autoincrement,"
            + "year integer,month integer,day integer,"
            + "title char(20),content text)";

    /**
     * 数据库表升级语句
     */
    private static final String UPDATE_BILL = "drop table if exists table_bill";
    private static final String UPDATE_INCOME = "drop table if exists table_income";
    private static final String UPDATE_NOTE = "drop table if exists table_note";

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

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_BILL);
        sqLiteDatabase.execSQL(CREATE_INCOME);
        sqLiteDatabase.execSQL(CREATE_NOTE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL(UPDATE_BILL);
        sqLiteDatabase.execSQL(UPDATE_INCOME);
        sqLiteDatabase.execSQL(UPDATE_NOTE);
    }
}

DataBaseManager代码：

/**
 * 数据库管理类型
 * 单例
 */
public class DataBaseManager {

    private static final String TAG = "DataBaseManager";
    /**
     * 单例实现
     *
     * @return DataBaseManager实例化对象
     */
    public static DataBaseManager dataBaseManager;
    public static DataBaseHelper dataBaseHelper;

    public static synchronized DataBaseManager getInstance() {
        if (dataBaseManager == null) {
            dataBaseManager = new DataBaseManager();
        }
        return dataBaseManager;
    }


    /**
     * 创建数据库
     *
     * @param context 上下文对象
     * @param name    数据库名字
     */
    public void createDataBase(Context context, String name) {
        if (dataBaseHelper == null) {
            dataBaseHelper = new DataBaseHelper(context, name, null, 1);
            Log.i("MainActivity", "创建数据库");
        }
    }


    /**
     * 添加一笔支出账单到数据库
     *
     * @param bill 支出账单对象
     */
    public void insertBill(Bill bill) {
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("year", bill.getYear());
        values.put("month", bill.getMonth());
        values.put("day", bill.getDay());
        values.put("consumption", bill.getConsumption());
        values.put("remarks", bill.getRemarks());
        values.put("amount", bill.getAmount());
        Log.e(TAG,values.toString());
        sqLiteDatabase.insert("table_bill", null, values);
        Log.i("MainActivity", "插入数据");
    }

    /**
     * 添加一笔收入到数据库
     *
     * @param income 收入账单对象
     */
    public void insertIncome(Income income) {
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("year", income.getYear());
        values.put("month", income.getMonth());
        values.put("day", income.getDay());
        values.put("consumption", income.getConsumption());
        values.put("remarks", income.getRemarks());
        values.put("amount", income.getAmount());
        Log.e(TAG,values.toString());
        sqLiteDatabase.insert("table_income", null, values);
    }

    /**
     * 添加一条新的笔记到数据库
     * @param note 笔记对象
     */
    public void insertNote(Note note){
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("year", note.getYear());
        values.put("month", note.getMonth());
        values.put("day", note.getDay());
        values.put("title", note.getTitle());
        values.put("content", note.getContent());
        sqLiteDatabase.insert("table_note", null, values);

    }

    /**
     * 查询支出账单
     *
     * @return 所有账单列表
     */
    public List<Bill> readBill() {
        List<Bill> list = new ArrayList<>();
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query("table_bill", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
                int year = cursor.getInt(cursor.getColumnIndexOrThrow("year"));
                int month = cursor.getInt(cursor.getColumnIndexOrThrow("month"));
                int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));
                String consumption = cursor.getString(cursor.getColumnIndexOrThrow("consumption"));
                String remarks = cursor.getString(cursor.getColumnIndexOrThrow("remarks"));
                String amount = cursor.getString(cursor.getColumnIndexOrThrow("amount"));
                Bill bill = new Bill(id, year, month, day, consumption, remarks, amount);
                list.add(bill);
            } while (cursor.moveToNext());
        }
        Log.i("MainActivity", "读数据" + list.toString());
        return list;
    }

    /**
     * 查询收入账单
     *
     * @return 所有账单列表
     */
    public List<Bill> readIncome() {
        List<Bill> list = new ArrayList<>();
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query("table_income", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
                int year = cursor.getInt(cursor.getColumnIndexOrThrow("year"));
                int month = cursor.getInt(cursor.getColumnIndexOrThrow("month"));
                int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));
                String consumption = cursor.getString(cursor.getColumnIndexOrThrow("consumption"));
                String remarks = cursor.getString(cursor.getColumnIndexOrThrow("remarks"));
                String amount = cursor.getString(cursor.getColumnIndexOrThrow("amount"));
                Bill bill = new Bill(id,year,month,day,consumption,remarks,amount);
                list.add(bill);
            } while (cursor.moveToNext());
        }
        Log.i("MainActivity", "读数据" + list.toString());
        return list;
    }

    public List<Note> readNote(){
        List<Note> list = new ArrayList<>();
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query("table_note", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
                int year = cursor.getInt(cursor.getColumnIndexOrThrow("year"));
                int month = cursor.getInt(cursor.getColumnIndexOrThrow("month"));
                int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));
                String title = cursor.getString(cursor.getColumnIndexOrThrow("title"));
                String content = cursor.getString(cursor.getColumnIndexOrThrow("content"));
                Note note = new Note(id, year, month, day, title, content);
                list.add(note);
            } while (cursor.moveToNext());
        }
        Log.i("MainActivity", "读数据" + list.toString());
        return list;
    }

    /**
     * 根据关键字搜索笔记
     * @param keyword 关键字
     * @return 含有关键字的笔记列表
     */
    public List<Note> readNoteByStr(String keyword){
        List<Note> list = new ArrayList<>();
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query("table_note", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndexOrThrow("id"));
                int year = cursor.getInt(cursor.getColumnIndexOrThrow("year"));
                int month = cursor.getInt(cursor.getColumnIndexOrThrow("month"));
                int day = cursor.getInt(cursor.getColumnIndexOrThrow("day"));
                String title = cursor.getString(cursor.getColumnIndexOrThrow("title"));
                String content = cursor.getString(cursor.getColumnIndexOrThrow("content"));
                if(TextUtil.containCharacter(content,keyword)||TextUtil.containCharacter(title,keyword)){
                    Note note = new Note(id, year, month, day, title, content);
                    list.add(note);
                }
            } while (cursor.moveToNext());
        }
        Log.i("MainActivity", "读数据" + list.toString());
        return list;
    }

    /**
     * 删除表中的一条数据
     * @param table 表名称
     * @param id 数据id
     */
    public void deleteBill(String table,int id) {
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getWritableDatabase();
        sqLiteDatabase.delete(table, "id==?", new String[]{Integer.toString(id)});
    }


    /**
     * 修改账单
     *
     * @param id               账单id
     * @param consumption_type 消费类型
     * @param in_out           收入还是支出
     * @param payment_method   支付方式
     * @param amount           金额
     * @param time             时间
     */
    public void updateBill(int id, String consumption_type, int in_out, int payment_method, double amount, String time) {
        SQLiteDatabase sqLiteDatabase = dataBaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("consumption_type", consumption_type);
        values.put("in_out", in_out);
        values.put("amount", payment_method);
        values.put("amount", amount);
        values.put("time", time);
        sqLiteDatabase.update("table_bill", values, "id = ?", new String[]{Integer.toString(id)});
    }
}

在BaseActivity中初始化数据库：

 DataBaseManager.getInstance().createDataBase(this, Constant.DATA_BILL_NAME);//数据库初始化