Android数据库SQLite学习

Android SQLite数据库的使用

• DatabaseHelper 作为一个访问SQLite的助手类，提供两个方面的功能，

1. getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatabase对象，通过该对象可以对数据库进行读写操作。
2. 提供onCreate()和onUpgrade()两个回调函数，允许我们在创建和升级数据库时，进行创建、升级数据库。

创建数据库

1. 编写MyDatabaseHelper继承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 mContext;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext, "Create succeeded", 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);

    }
}

1. 然后在MainActivity中创建MyDatabaseHelper类

private MyDatabaseHelper dbHelper;
....
dbHelper = new MyDatabaseHelper(this, "BookStore.db",null, 2);
        Button createDatabase = findViewById(R.id.create_database);
        createDatabase.setOnClickListener(new UpdateListener());

插入一条数据

...
Button addData = findViewById(R.id.add_data);
addData.setOnClickListener(new AddDataListener());
...
class AddDataListener implements View.OnClickListener{
        @Override
        public void onClick(View view) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put("name", "The Da Vinci Code");
            values.put("author", "Dan Brown");
            values.put("price", 16.96);
            values.put("pages", 454);
            db.insert("Book",null,values);
            values.clear();


            values.put("name", "The Lost Symbol");
            values.put("author", "Dan Brown");
            values.put("price", 19.95);
            values.put("pages", 510);
            db.insert("Book",null,values);
            values.clear();
        }
    }

改一条数据

 Button updataData = findViewById(R.id.update_data);
 updataData.setOnClickListener(new UpdateDataListener());
...
class UpdateDataListener implements View.OnClickListener{
        @Override
        public void onClick(View view) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put("price", 10.99);
            db.update("Book", values,"name = ?",new String[]{"The Da Vinci Code"});
        }
    }

删除一条数据

 Button deleteData = findViewById(R.id.delete_data);
 deleteData.setOnClickListener(new DeleteDataListener());
 ...
 class DeleteDataListener implements View.OnClickListener{
        @Override
        public void onClick(View view) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            db.delete("Book", "pages > ?",new String[]{"500"});
        }
    }

查询数据

Button queryData = findViewById(R.id.query_data);
queryData.setOnClickListener(new QueryDataListener());
...
class QueryDataListener implements View.OnClickListener{
        @Override
        public void onClick(View view) {
            SQLiteDatabase 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"));
                    String author = cursor.getString(cursor.getColumnIndex("author"));
                    int pages= cursor.getInt(cursor.getColumnIndex("pages"));
                    double price = cursor.getDouble(cursor.getColumnIndex("price"));
                    Log.d("MainActivity","book name is" + name);
                    Log.d("MainActivity","book author is" + author);
                    Log.d("MainActivity","book pages is" + pages);
                    Log.d("MainActivity","book price is" + price);

                }while(cursor.moveToNext());
            }
            cursor.close();
        }

转载于:https://www.cnblogs.com/learnars/p/8182544.html