sqlite数据库

sqlite数据库

MyDatabaseHelper.class

// [1] 创建一个类继承 SQLiteOpenHelper
public class MyDatabaseHelper extends SQLiteOpenHelper {

    public static final String CREATE_BOOK = "create table Book(" +
            "id integer primary key autoincrement," +
            "author text," +
            "price real," +
            "pages integer," +
            "name text)";

    public static final String CREATE_CATEGORY = "create table Category(" +
            "id integer primary key autoincrement," +
            "category_name text," +
            "category_code integer)";

    private Context context;

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

        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // [2] 在OnCreate中执行语句
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(context, "创建成功", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //升级的时候将已经存在的表删除
        db.execSQL("drop table if exists Book");
        db.execSQL("drop table if exists Category");
        onCreate(db);
    }
}

public void myClick(View view) {

        /**
         * 创建数据库
         * @param 上下文
         * @param 数据库文件名
         * @param 游标工厂
         * @param 数据库的版本 升级的时候要指定比之前更大的数值
         */
        dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 4);
        // 获取数据库对象
        db = dbHelper.getWritableDatabase();

    }
    // 增加
    public void add(View view) {
        db = dbHelper.getWritableDatabase();
        // 创建values
        ContentValues values = new ContentValues();
        values.put("name", "alice in wonderland");
        values.put("author", "lewis");
        values.put("pages", 700);
        values.put("price", 300.11);
        db.insert("Book", null, values);
        // 清空
        values.clear();
        // 往下继续插入
        values.put("name", "One Hundred Years of Solitude");
        values.put("author", "Gabriel");
        values.put("pages", 500);
        values.put("price", 100.11);
        db.insert("Book", null, values);

    }
    // 修改
    public void update(View view) {
        db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("price",500.2);
        db.update("Book",values,"name = ?",new String[]{"alice in wonderland"});
    }
    // 删除
    public void del(View view) {
        db = dbHelper.getWritableDatabase();
        // 删除页数少于700的记录
        db.delete("Book","pages = ?",new String[]{"700"});
    }
    // 查询
    public void sel(View view){
        StringBuilder sb = new StringBuilder();
        db = dbHelper.getWritableDatabase();
        Cursor cursor = db.query("Book", null, null, null, null, null, null);
        // 将游标移到第一行
        if(cursor.moveToFirst()){
            do {
                String name = cursor.getString(cursor.getColumnIndex("name"));
                sb.append(name).append("\n");
            } while (cursor.moveToNext()); // 将游标移到下一行,如果不存在返回false
        }
        cursor.close();
        TextView mTv = (TextView) findViewById(R.id.textView);
        mTv.setText(sb.toString());
    }

也可以直接使用sql语句进行查询
如:
db.execSQL(“insert into Book (name,author) values(?,?)”,new String[]{“alice in wonderland”});
查询要使用rawQuery
如:
db.rawQuery(“select * from Book”,null);