Crud operation of Android SQLite database (case)

Article catalog

1. Summary of knowledge points

1. Add components dynamically (when querying data)
2. Creation of SQLite database and CRUD operation

2. Case introduction

CRUD: add, delete, modify and query

This case mainly summarizes the CRUD operation of the database.

  • Database name: product.db
  • Table name: information
  • Fields: id(integer type auto increment, primary key), name(varchar(20)), price(integer)

3. Layout file

Page display:
The bottom data is the data that appears after clicking query. It is not available by default


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".SqliteActivity">

    <EditText
        android:id="@+id/et_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Please enter Name"
    />

    <EditText
        android:id="@+id/et_passwd"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:inputType="number"
        android:hint="Please enter Price"
        />
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical">
    <Button
        android:id="@+id/bt_insert"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="insert"
        />
        <Button
            android:id="@+id/bt_delete"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="delete"
            />
        <Button
            android:id="@+id/bt_update"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="modify"
            />
        <Button
            android:id="@+id/bt_select"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="query"
            />
    </LinearLayout>
    
	<!-- Add components dynamically, Used in query -->
    <LinearLayout
        android:id="@+id/linearLayoutOut"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical"/>
    
</LinearLayout>

4. Code

4.1 entity class:

Priduct.java

package com.example.sqlitedemo;

import android.content.Intent;

public class Product {
    private int id;
    private String name;
    private int price;

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public Product() {
    }

    public Product(int id, String name, int price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }
}

4.2 create database

MySqLiteHelper.java

package com.example.sqlitedemo;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;


import androidx.annotation.Nullable;

public class MySqLiteHelper extends SQLiteOpenHelper {

    private Context context;
    /**
     *
     * Second parameter: name database name
     * The third parameter: cursorFactory: cursor factory "object nu1l
     * The fourth parameter: version version 1,2
     *
     * */
    public MySqLiteHelper(@Nullable Context context) {
        super(context, "product.db",null, 1);
        this.context = context;
    }

    /**
     * Called the first time a database is created
     *
     * Write the table creation statement in this method.
     * */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE information(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "name VARCHAR(20)," +
                "price INTEGER)");

        Toast.makeText(context, "Data table created successfully",Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

4.3 CRUD operation

SqliteActivity.java
The parameters are specified at the bottom.

package com.example.sqlitedemo;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.TextView;
import android.widget.Toast;

public class SqliteActivity extends AppCompatActivity  {

    private MySqLiteHelper helper;
    private Button bt_insert;
    private Button bt_delete;
    private Button bt_update;
    private Button bt_select;
    private EditText editName;
    private EditText editPasswd;
    private SQLiteDatabase db;
    private ContentValues values;
    private LinearLayout linearLayoutOut;
    private LinearLayout linearLayoutIn;
    private LinearLayout.LayoutParams layoutParams;
    private TextView textName;
    private TextView textPrice;
    private TextView textId;


    /**
     * initialization
    * */
    public  void init(){
        editName = findViewById(R.id.et_name);
        editPasswd = findViewById(R.id.et_passwd);
        bt_insert = findViewById(R.id.bt_insert);
        bt_delete = findViewById(R.id.bt_delete);
        bt_update = findViewById(R.id.bt_update);
        bt_select = findViewById(R.id.bt_select);
        linearLayoutOut = findViewById(R.id.linearLayoutOut);
        //Set properties for linearLayout
        layoutParams = new LinearLayout.LayoutParams(
                LinearLayout.LayoutParams.WRAP_CONTENT,
                LinearLayout.LayoutParams.WRAP_CONTENT
        );
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        init();

        //Create data table
        helper =  new MySqLiteHelper(this);
        //Call getWritableDatabase() or getReadableDatabase() to create a database
        db = helper.getWritableDatabase();

        /**
        * insert data
        * the row ID of the newly inserted row, or -1 if an error occurred
        * Insert successfully returned new rows, failed - 1
        *
        * */
        bt_insert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                values = new ContentValues();
                //Judge whether there is value in the input box
                if (editName.getText().length() != 0 &&  editPasswd.getText().length() != 0) {
                    values.put("name",editName.getText().toString());
                    values.put("price",editPasswd.getText().toString());
                    long id = db.insert("information", null, values);
                    if (id != -1) {
                        Toast.makeText(SqliteActivity.this,"successful",Toast.LENGTH_SHORT).show();
                    }else {
                        Toast.makeText(SqliteActivity.this,"failure",Toast.LENGTH_SHORT).show();
                    }
                }else {
                    Toast.makeText(SqliteActivity.this,"Name or Price Cannot be empty",Toast.LENGTH_SHORT).show();
                }
            }
        });

        /**
        * Delete data
        *
        * Mode 1: dB. Delete ("information", "id = 1", null);
        * Method 2: dB. Delete ("information", "name =?", new string [] {"1"});
        * First parameter: table name
        * Second parameter: where clause
        * Second parameter: where clause
        * Return the number of rows, 0 deletion failed
        * Delete data by name
        * */
        bt_delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                //
                int information = db.delete("information","name = ?", new String[]{editName.getText().toString()});
                if (information != 0 ){
                    Toast.makeText(SqliteActivity.this,"delete successful", Toast.LENGTH_LONG).show();
                }else{
                    Toast.makeText(SqliteActivity.this,"delete failure", Toast.LENGTH_LONG).show();
                }

            }
        });

        /*
        * Update data
        * Modify price according to name
        * First parameter: data table
        * Second parameter: values
        * Third parameter: content of where clause
        *
        * */
        bt_update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                values = new ContentValues();
                values.put("price",editPasswd.getText().toString());
                int information = db.update("information", values, "name = ?", new String[]{editName.getText().toString()});
                if (information != 0 ){
                    Toast.makeText(SqliteActivity.this,"delete successful", Toast.LENGTH_LONG).show();
               }else{
                    Toast.makeText(SqliteActivity.this,"delete failure", Toast.LENGTH_LONG).show();
                }
            }
        });

        /*
        * Find data
        * Find all data
        *
        * */
        bt_select.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                values = new ContentValues();
                //query
                Cursor cursor = db.query("information", null, null, null, null, null, null);
              /*
               * cursor Interpretation of some methods in
               * You can get the data getxxx of a certain data by the column number(
               * You can get the index number getColumnIndex(String columnName) of a column through the column name
               * moveToFirst(),moveToLast(), moveToNext()
               * If moveToFirst returns false, what does it mean? No data is queried
               * If the return value of moveToNext is false, what does it mean? The query result has been traversed and there is no next one
               *
               * */
                //Judge whether it is found
                if (cursor != null && cursor.moveToFirst()) {

                    //Judge whether there is existing data, and delete if there is
                    if (textId != null){
                        Log.i("Cleared", "Okk");
                        linearLayoutOut.removeAllViews();
                    }
                    //Query results
                    //Traversing query results through cursors
                    do {
                        //get data
                        int dateId = cursor.getInt(0);
                        String dateName = cursor.getString(cursor.getColumnIndex("name"));
                        int datePrice = cursor.getInt(2);
                        Product product = new Product(dateId,dateName,datePrice);
                        Log.i("Acquired data",product.toString());
                        linearLayoutIn = new LinearLayout(SqliteActivity.this);
                        textId = new TextView(SqliteActivity.this);
                        textName = new TextView(SqliteActivity.this);
                        textPrice= new TextView(SqliteActivity.this);
                        linearLayoutIn.setOrientation(LinearLayout.HORIZONTAL);
                        //Display Id
                        textId.setText("Id:"+product.getId()+"    ");
                        //Display name
                        textName.setText("Name:"+product.getName()+"    ");
                        //Show price
                        textPrice.setText("Price:"+product.getPrice());
                        //Add textView to linearLayoutIn
                        linearLayoutIn.addView(textId,layoutParams);
                        linearLayoutIn.addView(textName,layoutParams);
                        linearLayoutIn.addView(textPrice,layoutParams);
                        //Add linearLayoutIn to LinearLayoutOut
                        linearLayoutOut.addView(linearLayoutIn);
                    } while (cursor.moveToNext());
                }
            }
        });
    }
}

5. Extension

5.1. Difference between getWritableDatabase() and getReadableDatabase():

Android can get one using getWritableDatabase() and getReadableDatabase() methods
SQLiteDatabase instance used to manipulate the database. (getWritableDatabase() method will be called in getReadableDatabase() method)

  • The getWritableDatabase() method opens the database in a read-write mode. Once the disk space of the database is full, the database can only read but not write. If the getWritableDatabase() method is used, an error will occur.

  • getReadableDatabase() method is to open the database in read-write mode first. If the disk space of the database is full, it will fail to open. After the failure, it will continue to try to open the database in read-only mode. If the problem is resolved successfully, the read-only database object is closed and a read-write database object is returned.

5.2. Interface description of SQLiteDataBase object

  • 1. query() interface for SQLiteDataBase object:

    /**
     * Query the given table, returning a {@link Cursor} over the result set.
     *
     * @param table The table name to compile the query against.(Table name to query.)
     * @param columns A list of which columns to return. Passing null will
     *            return all columns, which is discouraged to prevent reading
     *            data from storage that isn't going to be used.(If it is blank, all columns will be returned. It is not recommended to set it as blank, if not all columns will be returned.)
     * @param selection A filter declaring which rows to return, formatted as an
     *            SQL WHERE clause (excluding the WHERE itself). Passing null
     *            will return all rows for the given table.
     * 			 (where Clause to declare the requirement of the row to be returned, and if it is empty, all rows of the table are returned. )
     * @param selectionArgs You may include ?s in selection, which will be
     *         replaced by the values from selectionArgs, in order that they
     *         appear in the selection. The values will be bound as Strings.
     * 			( where Condition value corresponding to clause)
     * @param groupBy A filter declaring how to group rows, formatted as an SQL
     *            GROUP BY clause (excluding the GROUP BY itself). Passing null
     *            will cause the rows to not be grouped.
     * 			 (Group method: if it is blank, it will not be grouped.)
     * @param having A filter declare which row groups to include in the cursor,
     *            if row grouping is being used, formatted as an SQL HAVING
     *            clause (excluding the HAVING itself). Passing null will cause
     *            all row groups to be included, and is required when row
     *            grouping is not being used.
     * 			 (having Condition. If it is blank, all (not recommended) will be returned
     * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
     *            (excluding the ORDER BY itself). Passing null will use the
     *            default sort order, which may be unordered.
     *            (Sorting method; if it is blank, it is the default sorting method)
     * @return A {@link Cursor} object, which is positioned before the first entry. Note that
     * {@link Cursor}s are not synchronized, see the documentation for more details.
     * @see Cursor
     */
    public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy) {

        return query(false, table, columns, selection, selectionArgs, groupBy,
                having, orderBy, null /* limit */);
    }

  • 2. insert() interface for SQLiteDataBase object:
    /**
     * Convenience method for inserting a row into the database.
     *
     * @param table the table to insert the row into(Table name)
     * @param nullColumnHack optional; may be <code>null</code>.
     *            SQL doesn't allow inserting a completely empty row without
     *            naming at least one column name.  If your provided <code>values</code> is
     *            empty, no column names are known and an empty row can't be inserted.
     *            If not set to null, the <code>nullColumnHack</code> parameter
     *            provides the name of nullable column name to explicitly insert a NULL into
     *            in the case where your <code>values</code> is empty.
     * 			  ( When the values parameter is empty or there is no content in it, our insert will fail (an empty row is not allowed to be inserted in the underlying database). In order to prevent this, we need to specify a column name here. If we find an empty row to be inserted, we will set the value of the column name you specified to null, and then insert it into the database. )
     * @param values this map contains the initial column values for the
     *            row. The keys should be the column names and the values the
     *            column values
     * 		      (A ContentValues object, similar to a map, stores values in the form of key value pairs. )
     * @return the row ID of the newly inserted row, or -1 if an error occurred
     */
    public long insert(String table, String nullColumnHack, ContentValues values) {
        try {
            return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
        } catch (SQLException e) {
            Log.e(TAG, "Error inserting " + values, e);
            return -1;
        }
    }


  • 3. update() interface for SQLiteDataBase object:
    /**
     * Convenience method for updating rows in the database.
     *
     * @param table the table to update in(Table name to update)
     * @param values a map from column names to new column values. null is a
     *            valid value that will be translated to NULL.
     * 	          
     * @param whereClause the optional WHERE clause to apply when updating.
     *            Passing null will update all rows.
     * 			(Optional where statement)
     * @param whereArgs You may include ?s in the where clause, which
     *            will be replaced by the values from whereArgs. The values
     *            will be bound as Strings.
     *           (whereClause What is the value of the expression in the statement? Occupation parameter list)
     * @return the number of rows affected
     */
    public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
        return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE);
    }


  • 4. delete() interface for SQLiteDataBase object:
    /**
     * Convenience method for deleting rows in the database.
     *
     * @param table the table to delete from(Table name)
     * @param whereClause the optional WHERE clause to apply when deleting.
     *            Passing null will delete all rows.
     *            (Optional where statement)
     * @param whereArgs You may include ?s in the where clause, which
     *            will be replaced by the values from whereArgs. The values
     *            will be bound as Strings.
     *            (whereClause What is the value of the expression in the statement? Occupation parameter list)
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
     */
    public int delete(String table, String whereClause, String[] whereArgs) {
        acquireReference();
        try {
            SQLiteStatement statement =  new SQLiteStatement(this, "DELETE FROM " + table +
                    (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
            try {
                return statement.executeUpdateDelete();
            } finally {
                statement.close();
            }
        } finally {
            releaseReference();
        }
    }

Tags: Android Database SQL SQLite

Posted on Sun, 17 May 2020 03:20:04 -0700 by exploo