Android Studio introduces the usage process of SQLite database through login function

Foreword:

Introduction to SQLite: A lightweight database is a relational database management system that complies with ACID and is contained in a relatively small C library. It is a public domain project established by D. RichardHipp. Its design goal is embedded, and it has been used in many embedded products at present. It occupies very low resources. In embedded devices, it may only need a few hundred K memory. It can support mainstream operating systems such as Windows/Linux/Unix, and can be combined with many programming languages, such as Tcl, C #, PHP, Java, etc., as well as ODBC interface. Compared with Mysql and PostgreSQL, two open source world-famous database management systems, its processing speed is faster than them. The first Alpha version of SQLite was born in May 2000.

SQLite database, which is widely used in small web applications including browsers, IOS, Android and some portable requirements.

Next, I will introduce the use of SQLite database in the actual Android project through a login function.

Common operations of SQLite database:

Including table creation, table deletion, addition, deletion, modification and query. The syntax of SQL is as follows:

Form building:

create table if not exists  Table name ( Field 1  Type ( Length ), Field 2  Type ( Length ),...)

Delete table:

drop table if exists  Table name 

Add:

insert into  Table name  ( Field 1, Field 2, Field 3 ...) values ( Value 1, Value 2, Value 3 ...);

insert into  Target data table  select * from  Source data table ;

Delete:

delete from  Table name  where  Conditional expression 

Change:

update  Table name  set  Field 1= Value 1, Field 2= Value 2... where  Conditional expression 

Check:

select * from  Table name  where  Conditional expression 

Example:

1. First, create an DBHelper class (DBOpenHelper. java)

You will perform the operations of building database and table here

package com.hyl.dao;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
/**
 * @programName: DBOpenHelper.java
 * @programFunction: database helper class
 * @createDate: 2018/09/29
 * @author: AnneHan
 * @version:
 * xx.  yyyy/mm/dd  ver  author  comments
 * 01.  2018/09/29  1.00  AnneHan  New Create
 */
public class DBOpenHelper extends SQLiteOpenHelper {
  public DBOpenHelper(Context context,String name, CursorFactory factory,
            int version){
    super(context, name, factory, version);
  }
  @Override
  // Called when the database is first created, 1 Generally, you can perform the operations of building databases and tables 
  //Sqlite There is no separate Boolean storage type, which uses the INTEGER As a storage type, 0 For false , 1 For true
  public void onCreate(SQLiteDatabase db){
    //user table
    db.execSQL("create table if not exists user_tb(_id integer primary key autoincrement," +
        "userID text not null," +
        "pwd text not null)");
  }
  @Override// Automatically executes when the version of the database changes 
  public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
  }
}

2. Enter the login interface

When you click the login button, you will go to the database to query and judge whether the account number exists (Query query example)

/**
 * login event
 * @param v
 */
public void OnMyLoginClick(View v){
  // Judge account number / Does the password have input processing ...
  // Call DBOpenHelper  ( qianbao.db Is the name of the database created) 
  DBOpenHelper helper = new DBOpenHelper(this,"qianbao.db",null,1);
  SQLiteDatabase db = helper.getWritableDatabase();
  // According to the account number entered on the screen, / Password to the database for query ( user_tb Is a table name) 
  Cursor c = db.query("user_tb",null,"userID=? and pwd=?",new String[]{ Parameter 1 Value of , Parameter 2 Value of },null,null,null);
  // If there is data query 
  if(c!=null && c.getCount() >= 1){
    // You can print out the queried value and display it in the background / View 
    /*String[] cols = c.getColumnNames();
    while(c.moveToNext()){
      for(String ColumnName:cols){
        Log.i("info",ColumnName+":"+c.getString(c.getColumnIndex(ColumnName)));
      }
    }*/
    c.close();
    db.close();
    this.finish();
  }
  // If no data is queried 
  else{
    Toast.makeText(this, " Incorrect entry of mobile phone number or password! ", Toast.LENGTH_SHORT).show();
  }
}

3. If the account does not exist, you need to register a new account (Insert new example)

import com.hyl.dao.DBOpenHelper;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
/**
 * register event
 * @param v
 */
public void OnMyRegistClick(View v){
  // Processing of judging the format of the value input by the user ...
  // Call DBOpenHelper
  DBOpenHelper helper = new DBOpenHelper(this,"qianbao.db",null,1);
  SQLiteDatabase db = helper.getWritableDatabase();
  // Go to the database to query according to the account number entered on the screen 
  Cursor c = db.query("user_tb",null,"userID=?",new String[]{ Parameter 1 Value of },null,null,null);
  // If the data is queried, the account number already exists 
  if(c!=null && c.getCount() >= 1){
    Toast.makeText(this, " The user already exists ", Toast.LENGTH_SHORT).show();
    c.close();
  }
  // If no data is queried, go to the database insert1 Pen data 
  else{
    //insert data
    ContentValues values= new ContentValues();
    values.put("userID"," The value entered on the screen ");
    values.put("pwd"," The value entered on the screen ");
    long rowid = db.insert("user_tb",null,values);
    Toast.makeText(this, " Successful registration ", Toast.LENGTH_SHORT).show();// Prompt information 
    this.finish();
  }
  db.close();
}

4. If the user forgets his password, he needs to reset the password (Update modification example)

  /**
 * confirm event
 */
private void confirmInfo() {
  // The process of judging the value input by the user on the interface ...

  // Call DBOpenHelper
  DBOpenHelper helper = new DBOpenHelper(this,"qianbao.db",null,1);
  SQLiteDatabase db = helper.getWritableDatabase();
  // According to the account number entered on the screen, / Password goes to the database for query 
  Cursor c = db.query("user_tb",null,"userID=?",new String[]{editPhone.getText().toString()},null,null,null);
  // If the data is queried, it means that the account number exists, and the password can be reset 
  if(c!=null && c.getCount() >= 1){
    ContentValues cv = new ContentValues();
    cv.put("pwd", editPhone.getText().toString());//editPhone Controls on the interface 
    String[] args = {String.valueOf(editPhone.getText().toString())};
    long rowid = db.update("user_tb", cv, "userID=?",args);
    c.close();
    db.close();
    Toast.makeText(this, " Password reset successful! ", Toast.LENGTH_SHORT).show();
    this.finish();
  }
  // If no data is found, prompt the user to register in the registration interface 
  else{
    new AlertDialog.Builder(this)
        .setTitle(" Prompt ")
        .setMessage(" This user does not exist, please register at the registration interface! ")
        .setPositiveButton(" Determine ", new DialogInterface.OnClickListener() {
          public void onClick(DialogInterface dialog, int whichButton) {
            setResult(RESULT_OK);
            Intent intent=new Intent( Current password reset interface .this, Registration interface .class);
             Current password reset interface .this.startActivity(intent);
          }
        })
        .setNegativeButton(" Cancel ", new DialogInterface.OnClickListener() {
          public void onClick(DialogInterface dialog, int whichButton) {
            return;
          }
        })
        .show();
  }
}