SQLite case 1: using SQLiteDatabase to operate database and table

SQLite case 1: using SQLiteDatabase to operate database and table

1, Operation effect

2, Implementation steps
1. Create an Android Application SQLiteDatabaseDemo

2. Prepare the background picture background.jpg and copy it to the mipmap directory under res

3. Main layout resource file activity main.xml

<?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:background="@mipmap/background"
    android:orientation="vertical"
    android:gravity="center"
    android:padding="10px"
    tools:context="net.lm.ied.sqlitedatabase.MainActivity">

    <Button
        android:id="@+id/btn_create_or_open_db"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doCreateOrOpenDB"
        android:text="@string/create_or_open_db"/>

    <Button
        android:id="@+id/btn_create_tale"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doCreateTable"
        android:text="@string/create_table"/>

    <Button
        android:id="@+id/btn_add_record"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doAddRecord"
        android:text="@string/add_record"/>
    <Button
        android:id="@+id/btn_update_record"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doUpdateRecord"
        android:text="@string/update_record" />

    <Button
        android:id="@+id/btn_display_all_records"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doDisplayAllRecords"
        android:text="@string/display_all_records" />

    <Button
        android:id="@+id/btn_delete_all_records"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doDeleteAllRecords"
        android:text="@string/delete_all_records" />

    <Button
        android:id="@+id/btn_delete_table"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doDeleteTable"
        android:text="@string/delete_table" />

    <Button
        android:id="@+id/btn_delete_db"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:onClick="doDeleteDB"
        android:text="@string/delete_db" />

</LinearLayout>

4. String resource file strings.xml

<resources>
    <string name="app_name">SQLiteDatabase Usage example</string>
    <string name="create_or_open_db">Create or open database</string>
    <string name="create_table">Create table</string>
    <string name="add_record">Add table record</string>
    <string name="update_record">Update table record</string>
    <string name="display_all_records">Show all table records</string>
    <string name="delete_all_records">Delete all table records</string>
    <string name="delete_table">Delete table</string>
    <string name="delete_db">Delete database</string>
    <string name="action_settings">Set up</string>
</resources>

5. Main interface class MainActivity

(1)Defining constants and variables
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;

public class MainActivity extends Activity {

    /**
     *Database name
     */
    private final String DB_NAME = "student.db";
    /**
     * Table name
     */
    private final String TABLE_NAME = "student";
    /**
     * File access mode private mode
     */
    private final int MODE = Context.MODE_PRIVATE;
    /**
     * SQLite data base
     */
    private SQLiteDatabase db;
    /**
     * Student ID
     */
    private int id;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        //Using resource layout file to set up user interface
        setContentView(R.layout.activity_main);
    }
}

(2) Create or open database button click event handling code

/**
 * Create or open database
 *
 * @param view
 */
public void doCreateOrOpenDB(View view) {
    // Determine whether there is a database
    if (databaseList().length == 0) {
        // Create database
        db = openOrCreateDatabase(DB_NAME, MODE, null);
        // Prompt the user to create the database successfully
        Toast.makeText(this, "Congratulations, database[" + DB_NAME + "]Created successfully!", Toast.LENGTH_SHORT).show();
    } else {
        // Open database
        db = openOrCreateDatabase(DB_NAME, MODE, null);
        // Prompt the user to open the database successfully
        Toast.makeText(this, "Congratulations, database[" + DB_NAME + "]Open successfully!", Toast.LENGTH_SHORT).show();
    }
}

Run the program, the results are as follows:

Click create or open database for the first time:

Click create or open database for the second time:

(3) Create the method istableexisting (string tablename) to determine whether the table exists

/**
 * Judge whether the table exists
 *
 * @param tableName
 * @return
 */
private boolean isTableExisted(String tableName) {
    // Define SQL string
    String strSQL = "SELECT * FROM sqlite_master WHERE type = ? AND name = ?";
    // Execute SQL query, return cursor
    Cursor cursor = db.rawQuery(strSQL, new String[] {"table", tableName});
    // Determine whether there are records in the cursor
    return cursor.getCount() > 0;
}

(4) Create table button click event handling code

/**
 * Create table
 *
 * @param view
 */
public void doCreateTable(View view) {
    // Judge whether the database object is empty
    if (db == null) {
        // Judge whether the database exists
        if (databaseList().length == 0) {
            Toast.makeText(this, "Please create database[" + DB_NAME + "]. ", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(this, "Please open the database[" + DB_NAME + "]. ", Toast.LENGTH_SHORT).show();
        }
    } else {
        // Judge whether the table exists
        if (isTableExisted(TABLE_NAME)) {
            // Pop up toast to prompt that the user table already exists
            Toast.makeText(this, "Table [" + TABLE_NAME + "]Already exists!", Toast.LENGTH_LONG).show();
        } else {
            try {
                // Define SQL string
                String strSQL = "CREATE TABLE " + TABLE_NAME + "(id integer, name text, gender text)";
                // Execute SQL statement
                db.execSQL(strSQL);
                // Prompt the user to create the table successfully
                Toast.makeText(this, "Table created successfully!", Toast.LENGTH_LONG).show();
            } catch (SQLException e) {
                // Prompt user to create table failed
                Toast.makeText(this, "Failed to create table!", Toast.LENGTH_LONG).show();
            }
        }
    }
}

Run the program, the results are as follows:

Without clicking Create or open database, directly click create table:

Click create or open database, and then click create table:

At this time, click create table again:

github project code:( https://github.com/Liumce/SQLiteDatabase.git)

Tags: Android Database SQL SQLite

Posted on Tue, 05 May 2020 04:59:05 -0400 by maff20