Under what circumstances do we use databases for data storage? When large amounts of data with the same structure need to be stored.

SqliteOpenHelper

Create a database step:
1. To create a class integration SqliteOpenHelper, we need to add a constructor to implement two methods, oncreate and onupgrade.
    Introduction of parameters in the construction method:

    // context, name: The name of the database file factory: used to create cursor objects, by default null 
    // version: The version number of the database. Starting from 1, if it changes, the onUpgrade method will be called. After 4.0, it can only be upgraded.
    super(context, "info.db", null,1);


2. Creating an object of this help class and calling the getReadableDatabase() method will help us create and open a database.

3. Overwriting oncreate and onupgrdate methods:
    The oncreate method is called when the database is first created; it is especially suitable for initializing the table structure and needs to execute sql statements; SQLiteDatabase db can be used to execute sql statements.

    // onUpgrade database version number changes before execution; especially suitable for table structure modification



Both getWritableDatabase and getReadableDatabase in the help class object can help us obtain a database operation object SqliteDatabase.

Difference:
getReadableDatabase:
    First try to open the database in read-write mode. If the disk space is full, he will try to open the database in read-only mode again.
getWritableDatabase:
    Open the database directly by reading and writing. If the disk space is full, it will report the error directly.

2 Addition, deletion and modification of database in Android

1.Create an object of a help class, call it getReadableDatabase Method, return one SqliteDatebase object

2.Use SqliteDatebase Object call execSql()Do additions and deletions,call rawQuery Method Query.

******Characteristic:Addition, deletion and alteration have no return value and cannot be judged. sql Statement is successfully executed. sql Statements written manually are prone to errors



private MySqliteOpenHelper mySqliteOpenHelper;
public InfoDao(Context context){
    //Create a Help Class Object
    mySqliteOpenHelper = new MySqliteOpenHelper(context);


}

public void add(InfoBean bean){

    //Executing sql statements requires sqliteDatabase objects
    //Initialize database creation by calling getReadableDatabase method
    SQLiteDatabase  db = mySqliteOpenHelper.getReadableDatabase();
    //The placeholder values in sql:sql statements, bindArgs: SQL statements
    db.execSQL("insert into info(name,phone) values(?,?);", new Object[]{bean.name,bean.phone});
    //Close database objects
    db.close();
}

public void del(String name){


    //Executing sql statements requires sqliteDatabase objects
    //Initialize database creation by calling getReadableDatabase method
    SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();
    //The placeholder values in sql:sql statements, bindArgs: SQL statements
    db.execSQL("delete from info where name=?;", new Object[]{name});
    //Close database objects
    db.close();

}
public void update(InfoBean bean){

    //Executing sql statements requires sqliteDatabase objects
    //Initialize database creation by calling getReadableDatabase method
    SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();
    //The placeholder values in sql:sql statements, bindArgs: SQL statements
    db.execSQL("update info set phone=? where name=?;", new Object[]{bean.phone,bean.name});
    //Close database objects
    db.close();

}
public void query(String name){

    //Executing sql statements requires sqliteDatabase objects
    //Initialize database creation by calling getReadableDatabase method
    SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();
    //sql:sql statement, selectionArgs: Query the value of the conditional placeholder and return a cursor object
    Cursor cursor = db.rawQuery("select _id, name,phone from info where name = ?", new String []{name});
    //Parsing Data in Cursor
    if(cursor != null && cursor.getCount() >0){//Determine whether data exists in cursor

        //Loop through the result set to get the contents of each row
        while(cursor.moveToNext()){//Conditions, whether the cursor can be positioned on the next line
            //get data
            int id = cursor.getInt(0);
            String name_str = cursor.getString(1);
            String phone = cursor.getString(2);
            System.out.println("_id:"+id+";name:"+name_str+";phone:"+phone);
        }
        cursor.close();//Close the result set

    }
    //Close database objects
    db.close();

}

Transaction of database

Transactions: multiple executions sql Statements are executed successfully at the same time or fail at the same time. Some cannot succeed or some fail.

//Bank transfer


//Click the button to execute the method
public void transtation(View v){
    //1. Create an object for a help class
    BankOpenHelper bankOpenHelper = new BankOpenHelper(this);
    //2. Call database to help getReadable Database of class object create database, initialize table data, get a SqliteDatabase object to do transfer (sql statement)
    SQLiteDatabase db = bankOpenHelper.getReadableDatabase();
    //3. Transfer, reduce Li Si's money by 200, Zhang San plus 200
    db.beginTransaction();//Open a database transaction
    try {
        db.execSQL("update account set money= money-200 where name=?",new String[]{"Li Si"});
        int i = 100/0;//Simulate an anomaly
        db.execSQL("update account set money= money+200 where name=?",new String[]{"Zhang San"});

        db.setTransactionSuccessful();//All sql statements in markup transactions were successfully executed
    } finally {
        db.endTransaction();//Determine whether the transaction's markup succeeds or, if not, roll back the sql statement executed before the error 
    }
}