Learn and use SQLite

Create a database and create a table 1. Create mydatabasehelper inherit from Sqliteopenhelper (abstract class, must implement OnCreate () and Onupgrade () method)
2. Make the database-building-table directive a string Create_book constant and execute the build table in the OnCreate () function

public class MyDatabaseHelper extends SQLiteOpenHelper {

    // Make the database table creation instruction into a string CREATE_BOOK constant
    public static final String CREATE_BOOK = "create table book ("
            + "id integer primary key autoincrement,"
            + "author text,"
            + "price real,"
            + "pages integer,"
            + "name text)";
    public MyDatabaseHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super (context, name, factory, version);
    }

    @Override
    public void onCreate (SQLiteDatabase db) {
        db.execSQL (CREATE_BOOK); // Execute the table
        Log.d (TAG, "Database initialization completed");
    }

    @Override
    public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
    }
    }
3. Create a new database in MianActivity ()
    private MyDatabaseHelper dbHelper = new MyDatabaseHelper (this, "BookStore.db", null, 1);
    // Create a new MyDatabaseHelper object, context; database name; the third parameter allows us to return a custom Cursor when querying data, which is generally passed in null; database version number
    dbHelper.getWritableDatabase ();
 

Upgrade database method 1 (overwrite) requirements: add a new table, Category
Because the database BookStore.db already exists, the onCreate () method will not be executed again. In the onCreate () method, adding the table cannot be updated.

Solution: Update the database using the onUpgrade () method
Add table constants; create tables in the onCreate () method; delete existing tables in the upGreate () method, and re-execute the onCreate () method; change the database version number when referring to the database

    public class MyDatabaseHelper extends SQLiteOpenHelper {

        // Make the database table creation instruction into a string CREATE_BOOK constant
        public static final String CREATE_BOOK = "create table book ("
                + "id integer primary key autoincrement,"
                + "author text,"
                + "price real,"
                + "pages integer,"
                + "name text)";
        // integer represents an integer, real represents a floating-point type, text represents a text type, and blob represents a binary type. In addition, in the above table creation statement, we also
        // Use the primary key to set the id column as the primary key, and use the autoincrement keyword to indicate that the id column is self-incrementing

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

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

        @Override
        public void onCreate (SQLiteDatabase db) {
            db.execSQL (CREATE_BOOK); // Execute the table
            db.execSQL (CREATE_CATEGORY);
            Log.d (TAG, "Database initialization completed");
        }

        // When a database version change is detected, the code in onUpgrade () will be executed
        @Override
        public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
            // Call the SQL statement, delete if there is a table, and then call the onCreate () method again
            db.execSQL ("drop table if exists Book");
            db.execSQL ("drop table if exists Category");
            onCreate (db);
        }
    }
Method 2 for upgrading the database (add-on type) Requirements: Change the database according to the requirements, add a new table, Category
Due to the method of overwrite database update, the database will be reset, resulting in user data loss and cannot be used in the product


Solution: Give each version number its changed content, then judge the version number of the current database in the onUpgrade () method, and then execute the corresponding change.
    @Override
    public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
        // Call the SQL statement, delete if there is a table, and then call the onCreate () method again
        / **
         * Note time: 20170117
         * Code function: update the database by overlay, delete the previous database and create a new one
         * Annotation Reason: Learn a new database update method
        db.execSQL ("drop table if exists Book");
        db.execSQL ("drop table if exists Category");
        onCreate (db);
         * /
        switch (newVersion)
        {
            case 2: db.execSQL (CREATE_CATEGORY);
                //! !! !! Note, no break;
            case 3: db.execSQL ("alter table Book add column category_id integer");
                //! !! !! Note, no break;
                // Because no matter which version of the installation is covered, other updates need to be installed. When installing the third version from the second version, you only need to update case3 to ensure that the database is up to date.
                Log.d (TAG, "The 3rd version database update was successful");
                break;
            default:
                Log.d (TAG, "Database update failed");
                break;
        }
    }
 

Add, delete, modify and check operations (SQL statement operations)
There are no more than four operations on data, CRUD. Where C stands for Create, R stands for Retrieve, U stands for Update, and D stands for Delete

SQLiteDatabase db = dbBookStoreHelper.getWritableDatabase ();

    // New two data
    db.execSQL ("insert into Book (name, author, pages, price) values (?,?,?,?)", new String [] {"The Da Vinci Code", "Dan Brown", "454", "16.96"});
    db.execSQL ("insert into Book (name, author, pages, price) values (?,?,?,?)", new String [] {"The Lost Symbol", "Dan Brown", "510", " 19.95 "});
 
    // Query all data
    db.rawQuery ("select * from Book", null);
   
    // Change the book "The Da Vinci Code" to 10.99
    db.execSQL ("update Book set price =? where name =?", new String [] {"10.99", "The Da Vinci Code"});
  
    // Delete books over 500 pages
    db.execSQL ("delete from Book where pages>?", new String [] {"500"});
Add, delete, change and check operations (Android syntax operations)
Increase data
insert () method, 1. table name; 2. NULL is automatically assigned to some nullable columns when no data is specified; 3. ContentValues object, which provides a series of put () method overloads To add data to ContentValues, you only need to pass in each column name in the table and the corresponding data to be added.
//! !! !! Assemble data before inserting data
SQLiteDatabase db = dbHelper.getWritableDatabase ();
ContentValues values = new ContentValues ();
// start to assemble the first data
values.put ("name", "The Da Vinci Code");
values.put ("author", "Dan Brown");
values.put ("pages", 454);
values.put ("price", 16.96);
db.insert ("Book", null, values); // insert the first data
values.clear ();
// start to assemble the second piece of data
values.put ("name", "The Lost Symbol");
values.put ("author", "Dan Brown ");
values.put ("pages", 510);
values.put ("price", 19.95);
db.insert ("Book", null, values); // insert the second piece of data
Change data
update () method, 1. table name; 2. ContentValues object, the update data is assembled here; the third and fourth parameters are used to restrict the update of the data in a row or rows, if not specified, the default Is to update all rows

SQLiteDatabase db = dbHelper.getWritableDatabase ();
ContentValues values = new ContentValues ();
values.put ("price", 10.99);
db.update ("Book", values, "name =?", new String [] {"The DaVinci Code"});
values.clean ();
delete data
delete () method, 1. table name; the second and third parameters are used to constrain the deletion of a row or rows of data, if not specified, all rows are deleted by default

db.delete ("Book", "pages>?", new String [] {"300"});
Query data
query () method, 1. table name; 2. specify which columns to query, if not specified, all columns are queried by default; 3. the third and fourth parameters are used to constrain the query of a row or rows If you do n’t specify it, the data of all rows will be queried by default. 5. If you specify the columns that need to be group by, if you do n’t specify it, you will not perform the group by operation on the query results. 6. If the data after group by is further filtered, it means that No filtering is performed. 7. Specify the sorting method of the query results. If not specified, the default sorting method is used.

 

query () method parameter corresponding to SQL part description
table from table_name specifies the table name of the query
columns select column1, column2 specify the column names of the query
selection where column = value constraint specifying where
selectionArgs-provide specific values for placeholders in where
groupBy group by column specifies the columns that require group by
having having column = value further constrains the results after group by
orderBy order by column1, column2 specifies how the query results are sorted
 

 

Cursor cursor = db.query ("Book", null, "1", null, null, null, null);
    if (cursor! = null)
    {
        if (cursor.moveToFirst ())
        {
            do {// until type loop
                String name = cursor.getString (cursor.getColumnIndex ("name"));
                String author = cursor.getString (cursor.getColumnIndex ("author"));
                Double price = cursor.getDouble (cursor.getColumnIndex ("price"));
                int pages = cursor.getInt (cursor.getColumnIndex ("pages"));
                Log.d (TAG, "name =" + name);
                Log.d (TAG, "author =" + author);
                Log.d (TAG, "price =" + price);
                Log.d (TAG, "pages =" + pages);
            } while (cursor.moveToNext ());
        }
    }
    cursor.close ();
  Use transaction
Things have to start and end, like a transfer, the money will be returned to the original account before it reaches the other party's account


Requirements: Delete the old database and replace the new one. Solution: use beginTransaction, setTransactionSuccessful, and endTransaction to monitor the execution of the transaction. Once the execution fails, return to the original database.
    db.beginTransaction (); // Begin transaction
    try {
        db.delete ("Book", null, null);
        if (true) {
            // Manually throw an exception here to make the transaction fail
            throw new NullPointerException ();
        }
        db.execSQL ("insert into Book (name, author, pages, price) values (?,?,?,?)",
                new String [] {"Marxism 2", "Chinese Communist Party", "1000", "100.00"}); // It is actually harmonious
        db.setTransactionSuccessful (); // The transaction has been executed successfully
    } catch (Exception e) {
        e.printStackTrace ();
    } finally {
        db.endTransaction (); // End transaction
    }
 

Learn and use SQLite