Summary

Create and modify the SQLite database in Android code, we can refer to the Android documentation Save data using SQLite , we need to take two basic steps in Android to set up SQLite to interact with the database.As follows:

1. Define a schema and contract - Create schema and contract classes
2. Create a database using an SQLOpenHelper - Create a database using SQLiteOpenHelper

After these two steps, you can create, read, update, and delete our data.

Create Schema and Contract Classes

Creating a database schema is really about planning the database structure.So we need to ask two questions:

1. What is the name of the form?
2. What are the names and data types of the columns in the table?

Remember the following statement?

CREATE TABLE <table_name> (<column_name_1> <data_type_1>, <column_name_2> <data_type_2>, ...);

Take the pet shelter scenario for example:

Attribute	Storage Class
Name (pet name)	TEXT (Text)
Breed (Variety)	TEXT (Text)
Gender (mother and father)	INTEGER (0-Unknown, 1-Public, 2-Mother)
Weight	INTEGER (for simplicity)

Draw tables, plan database structure

Create Contract class

Why use the Contract class?

Consider two examples of creating tables:

String makeTableStatement = "CREATE TABLE entry (_id INTEGER PRIMARY KEY, entryid TEXT, title TEXT, subtitle TEXT)";

---

String SQL_CREATE_ENTRIES = "CREATE TABLE" + FeedEntry.TABLE_NAME + "(" + FeedEntry._ID + "INTEGER PRIMARY KEY," + FeedEntry.COLUMN_NAME_ENTRY_ID + "TEXT," + FeedEntry.COLUMN_NAME_TITLE+"TEXT);"

Looking at the code above, we found that the table name and column name are stored in a constant in the second statement that created the table.When generating SQL instructions, you eliminate the possibility of spelling errors or accidentally capitalize letters that should not be capitalized.If you want to change the actual column name, you only need to change it in one place.

Summarize three reasons for using the Contract class:

• Help us define the architecture and specify where to look for database constants;
• It helps us avoid spelling errors when generating SQL instructions;
• Make it easier for us to update the database schema

Create Contract Class

Based on the previously planned database structure, which we defined, we create a package named data under the main package of the project, and then create a new java class named PetContract under the repackage, which is decorated with final because it is used only to provide constants and we do not need to extend or implement anything for this external class.Using the schema we defined, create an internal class for each table and a constant for each column heading, as follows:

public final class PetContract {
    private PetContract() {}

    public static final class PetEntry implements BaseColumns {

        public final static String TABLE_NAME = "pets";

        public final static String _ID = BaseColumn._ID;
        public final static String COLUMN_PET_NAME = "name";
        public final static String COLUMN_PET_BREED = "breed";
        public final static String COLUMN_PET_GENDER = "gender";
        public final static String COLUMN_PET_WEIGHT = "weight";

        public final static int GENDER_UNKONWN = 0;
        public final static int GENDER_MALE = 1;
        public final static int GENDER_FORMALE = 2;
    }
}

Create a database using SQLiteOpenHelper

Create a new PetDbHelper class in the dada package that inherits from the SQLiteOpenHelper class.We need to override the onCreate() and onUpGrade() methods and create constants for the database name and version, remember to create constructors for that class, and create a string constant for the SQLite directive used to create the table.

/**
 * Database helper for Pets app. Manages database creation and version management.
 */
public class PetDbHelper extends SQLiteOpenHelper {

    public static final String LOG_TAG = PetDbHelper.class.getSimpleName();

    /** Name of the database file */
    private static final String DATABASE_NAME = "shelter.db";

    /**
     * Database version. If you change the database schema, you must increment the database version.
     */
    private static final int DATABASE_VERSION = 1;

    /**
     * Constructs a new instance of {@link PetDbHelper}.
     *
     * @param context of the app
     */
    public PetDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * This is called when the database is created for the first time.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create a String that contains the SQL statement to create the pets table
        String SQL_CREATE_PETS_TABLE =  "CREATE TABLE " + PetEntry.TABLE_NAME + " ("
                + PetEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + PetEntry.COLUMN_PET_NAME + " TEXT NOT NULL, "
                + PetEntry.COLUMN_PET_BREED + " TEXT, "
                + PetEntry.COLUMN_PET_GENDER + " INTEGER NOT NULL, "
                + PetEntry.COLUMN_PET_WEIGHT + " INTEGER NOT NULL DEFAULT 0);";

        // Execute the SQL statement
        db.execSQL(SQL_CREATE_PETS_TABLE);
    }

    /**
     * This is called when the database needs to be upgraded.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // The database is still at version 1, so there's nothing to do be done here.
    }
}

So much has been written that we don't know if the database is working properly, so we can check it in the following way.

Check to see if the database is working:

• Create the displayDatabaseInfo() method and place it in our code so that it works only if SQL works properly.
• Download the database locally and view it using SQL statements at the terminal.

 @Override
    protected void onCreate(Bundle savedInstanceState) {
    ...
    displayDatabaseInfo()
    }

/**
     * Temporary helper method to display information in the onscreen TextView about the state of
     * the pets database.
     */
    private void displayDatabaseInfo() {
        // To access our database, we instantiate our subclass of SQLiteOpenHelper
        // and pass the context, which is the current activity.
        PetDbHelper mDbHelper = new PetDbHelper(this);

        // Create and/or open a database to read from it
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Perform this raw SQL query "SELECT * FROM pets"
        // to get a Cursor that contains all rows from the pets table.
        Cursor cursor = db.rawQuery("SELECT * FROM " + PetEntry.TABLE_NAME, null);
        try {
            // Display the number of rows in the Cursor (which reflects the number of rows in the
            // pets table in the database).
            TextView displayView = (TextView) findViewById(R.id.text_view_pet);
            displayView.setText("Number of rows in pets database table: " + cursor.getCount());
        } finally {
            // Always close the cursor when you're done reading from it. This releases all its
            // resources and makes it invalid.
            cursor.close();
        }
    }

Connect Database Process

PetDbHelper mDbHelper = new PetDbHelper(this);
SQLiteDatabase db = mDbHelper.getReadableDatabase();

Request database, mDbHelper will check if a database already exists?

If it does not exist, an instance of PetDbHelper will use the onCreate() method to create a database, then create an instance object of SQLiteDatabase and return it to Activity.

If the database already exists, the instance of PetDbHelper will not call the onCreate() method. Instead, an instance object of SQLiteDatabase will be created, associated with the existing database, and returned to the Activity of the requested database.

Ultimately, it helps us create the SQLiteDatabase object that the SQLiteDatabase object is associated with the shelter database, and then it allows us to communicate the SQLite instructions to the shelter database.

Extract database from device

Understanding the database connection process allows us to detect whether the database is functioning properly.Of course, there is another way we can actually see the petDbHelper class creating the database by looking at the device's file system, and then the.db file appears when the database is actually created.

Before that, we could turn on the mobile app information to make the pets app clear about caching and storing data.Also comment out the displayDatabaseInfo() method in the code.Click the Android Device Monitor button in Android Studio, select the emulator we are currently using, and go to File Explorer. This feature allows us to browse the device's file system, click on the data folder, click on the data folder inside, find the package we are applying to, open the package, look for the file, and we will find no.db file. Then I willTheir getReadableDatabase() method has not yet been called.

When we restore the displayDatabaseInfo() method we just commented out and run the code, we can see that there is a shelter.db database file under the package in the file system.Run again and no new files will appear.If you want to see the contents of the shelter.db file, you can download the file through DDMS, save it locally on your computer, and view it in the terminal through SQL language.

Database Objects and Inserted Data

In Android Documentation Put information into a database Medium, give us an example.

// Gets the data repository in write mode
SQLiteDatabase db = mDbHelper.getWritableDatabase();

// Create a new map of values, where column names are the keys
ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_TITLE, title);
values.put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle);

// Insert the new row, returning the primary key value of the new row
long newRowId = db.insert(FeedEntry.TABLE_NAME, null, values);

ContentValues stores a large number of key-value pairs, keys are column names in the database, and values are inserted values.Give an example:

ContentValues values = new ContentValues();
values.put(PetEntry.COLUMN_PET_NAME, "Garfield");
values.put(PetEntry.COLUMN_PET_BREED, "Tabby");
values.put(PetEntry.COLUMN_PET_GENDER, PetEntry.GENDER_MALE);
values.put(PetEntry.COLUMN_PET_WEIGHT, 7);
db.insert(PetEntry.TABLE_NAME, null, values);

Note that the insert() method returns the newly inserted ID, and if an error occurs it returns -1.

Database Query Method

We can read the database using the db.rawquery() method, but it is not recommended.Just as there is a SQLiteDatabase.insert() method, we can use the SQLiteDatabase.query() method to read the database, which helps us build queries and avoid syntax errors.Let's look at the Android documentation to see:

SQLiteDatabase db = mDbHelper.getReadableDatabase();

// Define a projection that specifies which columns from the database
// you will actually use after this query.
String[] projection = {
    BaseColumns._ID,
    FeedEntry.COLUMN_NAME_TITLE,
    FeedEntry.COLUMN_NAME_SUBTITLE
    };

// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };

// How you want the results sorted in the resulting Cursor
String sortOrder =
    FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";

Cursor cursor = db.query(
    FeedEntry.TABLE_NAME,   // The table to query
    projection,             // The array of columns to return (pass null to get all)
    selection,              // The columns for the WHERE clause
    selectionArgs,          // The values for the WHERE clause
    null,                   // don't group the rows
    null,                   // don't filter by row groups
    sortOrder               // The sort order
    );

Looking at the code above, we found that we first defined a string array projection, which actually refers to the column name we want to get, like this statement:

SELECT name, breed FROM pets;

Defining a projection allows us to specify the specific columns we want to get, and by default, all columns are obtained, similar to:

SELECT * FROM pets;

The projection size can affect performance.

Call the query() method, which has a large number of input parameters and represents different parts of the SELECT statement.The first parameter, projection, is described above, and selections and selectionArgs deal with optional WHERE conditions.For example, select a single pet based on ID:

SELECT * FROM pets WHERE _id = 1;

This is the case with the selections and selectionArgs properties:

// Define 'where' part of query
String selection = PetEntry._ID + "?";
// Specify arguments in placeholder order
String[] selectionArgs = {"1"}; 

The selection parameter is of type String after the WHERE keyword, which is used here? As a placeholder and then populated with the value in the selectionArgs parameter, which is an array of strings and is responsible for replacing the question mark in the selection here, where it is set to 1.

Why use? Number and selectionArgs instead of writing directly as 1?

There's no difference here. We can set selectionArgs to null and change the? Sign to 1.In some cases, however, the selection may come from tables, and using placeholders is a security measure against SQL injection attacks, in which users do not play their cards by routine, edit some code class content inputs, and make our query statements ambiguous.

When the query() method is called, a Cursor object is returned, which is an object that captures all subsets of the database.

What is Cursor

In short, Cursor is an object that represents multiple rows of content in a database.Suppose the following is the architecture of our entire database:

If we call the query() method on this table in this database, we can specify the value we want to return, and the information will be returned to us as a Cursor object.

If our choice parameter is

SELECT * FROM pets

The Cursor object we get, which contains the rows in the database.

If we want to specify further that only the pet name Toto row in the pets table be selected, the CUrsor object we get is the row in which the pet name in all rows is Toto, and the results are as follows:

Let's look at another example, suppose you choose name and breed from the pets table, which is to reduce the white pigeon to return only these two columns:

That is, the table is reduced to return only these two columns, and the returned Cursor object contains all rows, but only the details of the name and breed columns.

Cursor contains methods that allow us to access and browse Cursor objects, and if Cursor contains multiple lines, we can browse through them.

Cursor.getCount()

The Cursor object represents the rows and columns in the database. In addition, it provides the current position in those rows. To get specific data, we need to move the Cursor to the exact rows we need.

When we first get Cursor, the location starts at -1, which is an invalid location, the first available location is 0, then increments gradually.

Cursor.moveToFirst

This method moves the location in Cursor to the first row in the result, which gives us access to the data in the first record.

Cursor.moveToLast

This method will jump us to the last line here.

Cursor.moveToPosition(int position)

This method moves the Cursor location to the specified location.

The above methods all return Boolean-type values, which can help us determine if we actually moved to that location.For example, if we've moved the Cursor to the last line, calling the method that moves down will return false.

We can use different get methods to get specific values from the database.

Use the getColumnIndex(String columnName) method to get the index of the column by name.Give an example:

One thing to note about Cursor is that when you're done with it, remember to call cursor.close(), which will completely empty the Cursor and make it invalid.This method is called only after the complete operation is complete.Without closing Cursor, performance will be degraded due to memory leaks.

Article Content Summary From Youda School Town Course Android Foundation: Data Storage

The article is only used as a personal record for learning. If there are any inappropriate points, please correct them. Thank you.