Sqlite的使用

Sqlite的使用

//打开一个数据库，path为存放的路径 /data/data/com.han.myapplication/files
String path = getApplicationContext().getFilesDir().getAbsolutePath();

//读取assert目录下，的子目录的资源文件的方法
//getAssets().open("db.db");

//String path = new File(filesDir, "file.db").getAbsolutePath();

//SQLiteDatabase sqLiteDatabase = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);

---

获取Assert目录下的文件,数据库

/**
 * 载入数据库
 */
protected void copyDB(String dbName) {
    // 得到 data/data/com.xxx.xx/files 的对象
    File files = getFilesDir();
    // 生成目标文件
    File targetFile = new File(files, dbName);
    if (targetFile.exists()) {
        // 目标文件已经存在 不用再拷贝
        return;
    }

    // 资产管理器
    AssetManager assets = getAssets();
    InputStream is = null;
    FileOutputStream fos = null;
    try {
        // 打开资产目录下的文件流
        is = assets.open(dbName);

        // 生成目标文件的写入流
        fos = new FileOutputStream(targetFile);

        int len = 0;
        byte[] buffer = new byte[1024];
        while ((len = is.read(buffer)) != -1) {
            fos.write(buffer, 0, len);
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        // 关闭流
        StreamUtils.close(is);
        StreamUtils.close(fos);
    }
}


public static class StreamUtils {

    /**
     * 关闭流
     * @param stream
     */
    public static void close(Closeable stream) {
        if(stream!=null){
            try {
                stream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

---

//创建数据库
SQLiteDatabase db = createDatabase();
//插入数据
insert(db);
//查询数据
query(db);


//创建数据库
private SQLiteDatabase createDatabase() {
    MySqlite mySqlite = new MySqlite(getApplicationContext());
    // 可读 如果存储空间满 不会报错 会给你返回一个可读的数据库
    SQLiteDatabase db = mySqlite.getReadableDatabase();
    // 可写 如果存储空间满 会报错
    // helPer.getWritableDatabase();

    return db;

}

/**
 * 插入数据
 *
 * @param db
 */
private void insert(SQLiteDatabase db) {
    ContentValues values = new ContentValues();
    values.put("name", "小明");
    values.put("age", 20);
    values.put("type_id", 001);

    long insert = db.insert("people", null, values);
    if (insert != -1) {
        Log.e("insert", "插入成功");
    } else {
        Log.e("insert", "插入失败");
    }


}

/**
 * 查询数据
 *
 * @param db
 */
private void query(SQLiteDatabase db) {
    // 参数1:表名
    // 参数2:列名
    // 参数3:查询条件
    // 参数4:查询条件的值
    // 参数5:分组查询
    // 参数6:分组查询的条件
    // 参数7:排序
    // 参数8:分页查询
    Cursor cursor = db.query("people", null, null, null, null, null, null, null);

    while (cursor.moveToNext()) {
        String name = cursor.getString(cursor.getColumnIndex("name"));
        String age = cursor.getString(cursor.getColumnIndex("age"));
        int type_id = cursor.getInt(cursor.getColumnIndex("type_id"));
        Log.e("query", "name==" + name + "age==" + age + "type_id==" + type_id);
    }
    cursor.close();
    db.close();

}

数据库的增加，删除，更新，查询

---

public void add() {
    MyHelPer helPer = new MyHelPer(getContext());
    SQLiteDatabase db = helPer.getReadableDatabase();
    for (int i = 0; i < 10; i++) {
        db.execSQL("insert into student values(null,'张三',18" + i + ")");
    }

}

public void remove() {
    MyHelPer helPer = new MyHelPer(getContext());
    SQLiteDatabase db = helPer.getReadableDatabase();
    db.execSQL("delete from student where id = 1");

}

public void update() {
    MyHelPer helPer = new MyHelPer(getContext());
    SQLiteDatabase db = helPer.getReadableDatabase();
    db.execSQL("update student set name = '李四'");
}

public void query() {
    MyHelPer helPer = new MyHelPer(getContext());
    SQLiteDatabase db = helPer.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from student ", null);

    while (cursor.moveToNext()) {
        String id = cursor.getString(cursor.getColumnIndex("id"));
        String name = cursor.getString(cursor.getColumnIndex("name"));
        String age = cursor.getString(cursor.getColumnIndex("age"));
        String nick = cursor.getString(cursor.getColumnIndex("nick"));

        System.out.println("id = "+id +" name = "+name + " age = "+age +" nick = "+nick);
    }

}