What is SQLite

SQLite is a software library that implements a self-sufficient, serverless, zero configuration and transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. SQLite source code is not subject to copyright restrictions.

Here are some useful websites for learning about SQLite:

1,SQLite Home Page -The official SQLite website provides the latest SQLite installation version, the latest SQLite information and a complete SQLite tutorial.
2,PHP SQLite3 -The website provides full details of PHP support for SQLite 3 database.
3,DBD-SQLite-0.31 -The SQLite Perl driver driver is used with the Perl DBI module.
4,DBI-1.625 -The Perl DBI module provides a common interface to any database, including SQLite.
5,SQLite Python -sqlite3 python module is written by Gerhard Haring. It provides a SQL interface compatible with the DB-API 2.0 specification.

Project brief

This APP mainly realizes two functions:
1. Realize the operation of database (create database, store data - register, query and use data - login).
2. Basic page Jump and data carrying during jump.

Layout file

activity_main.xml

 <GridLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:rowCount="5"
        android:columnCount="2">
        <TimePicker
            android:layout_columnSpan="2"/>
        <TextView
            android:text="user name:"
            android:layout_row="1"
            android:layout_column="0"
            android:layout_marginLeft="65dp"
            />
        <EditText
            android:id="@+id/username"
            android:layout_row="1"
            android:layout_column="1"
            android:layout_marginLeft="-200dp"
            android:ems="7"
            />
        <TextView
            android:text="password:"
            android:layout_row="2"
            android:layout_column="0"
            android:layout_marginLeft="80dp"
            />
        <EditText
            android:id="@+id/password"
            android:layout_row="2"
            android:layout_column="1"
            android:inputType="textPassword"
            android:layout_marginLeft="-200dp"
            android:ems="7"
            />
        <LinearLayout
            android:weightSum="1.5"
            android:layout_gravity="center"
            android:layout_marginTop="20dp"
            android:layout_marginLeft="50dp"
            >
            <Button
                android:id="@+id/login"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Sign in"
                android:layout_weight="0.5"
                />
            <Button
                android:id="@+id/resign"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="register"
               android:layout_weight="0.5" />
            <Button
                android:id="@+id/btn_new"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Reset"
                android:layout_weight="0.5"
                />
        </LinearLayout>
        <DatePicker
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:visibility="invisible"
            android:layout_columnSpan="2"/>
    </GridLayout>

The effects are as follows:

activity_success.xml

  <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:gravity="center"
        android:orientation="vertical"
        >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="JSP Struts "
            android:textSize="25dp"
            android:textColor="@android:color/holo_blue_light"
            />
        <TextView
            android:id="@+id/tv_welcome"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="xxx Hello!"
            android:textSize="25dp"
            android:textColor="@android:color/holo_red_light"
            />
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Welcome"
            android:textSize="20dp"
            android:textColor="@android:color/holo_red_light"
            />
        <ImageView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginTop="10dp"
            android:src="@drawable/hello"/>
        <TextView
            android:id="@+id/tv_time"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Login time: xxxx-xx-xx"
            android:layout_marginTop="10dp"
            />
        <Button
            android:id="@+id/btn_back"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="15dp"
            android:text="return"
            android:textSize="20dp"/>
    </LinearLayout>

The effects are as follows:

activity_fault.xml

<LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:gravity="center"
        android:orientation="vertical"
        >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="JSP Struts "
            android:textSize="25dp"
            android:textColor="@android:color/holo_blue_light"
            />
        <TextView
            android:id="@+id/tv_welcome"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Wrong password!"
            android:textSize="25dp"
            android:textColor="@android:color/holo_red_light"
            />
        <ImageView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginTop="10dp"
            android:src="@drawable/hello"/>
        <TextView
            android:id="@+id/tv_time"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Please go back and log in again"
            android:layout_marginTop="10dp"
            />
        <Button
            android:id="@+id/btn_back"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="15dp"
            android:text="return"
            android:textSize="20dp"/>
    </LinearLayout>

The effects are as follows:

Database creation

In order to connect the android app to the database, we must first create a database we need. It is worth mentioning that because it is an app on a mobile device, there are still some differences between the database application and the web database. The most significant point is that we need to use code to create the database, And the database is stored directly on our mobile device.

DateBaseHelper.java

Here, we wrote the DateBaseHelper class to implement the SQLiteOpenHelper method.

/**
*DATEBASE_NAME Is the name of our database, VERSION_CODE is the version number of the database,
*TABLE_NAME Is the name of the table in our database;
*Constants like this will be defined in the Constants.java file later
*/
public class DateBaseHelper extends SQLiteOpenHelper {
    private static final  String TAG="DatabaseHelper";
    public DateBaseHelper(@Nullable Context context) {
        super(context, Constants.DATEBASE_NAME, null, Constants.VERSION_CODE);
    }  

//Create a database (this will only take effect when the program runs for the first time. The db file will be stored in the mobile device and will not be created again in the next run)
    public void onCreate(SQLiteDatabase db) {
        Log.d(TAG,"Create database...");
        //Create a field in the database. Here, only a name field and a password field are created
        String sql ="create table "+Constants.TABLE_NAME+"(name varchar(20),password varchar(20))";
        db.execSQL(sql);
    }

//Realize increase
    public void insert(SQLiteDatabase sqLiteDatabase, String name,String password){
    //Declare key value pair values
        ContentValues values=new ContentValues();
    //Insert values named name and password into the "name" and "password" fields of the key value pair values, respectively
        values.put("name",name);
        values.put("password",password);
    //Insert the key value pair values into the database
        sqLiteDatabase.insert(Constants.TABLE_NAME,null,values);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //Callback when upgrading the database (the version number needs to be changed before upgrading)
        Log.d(TAG,"Upgrade database...");
    }
}

Constants.java

public class Constants {
    public static String DATEBASE_NAME="10_14homework.db";//Database name
    public static int VERSION_CODE= 1;//Version number
    public static final String TABLE_NAME="customers";//Table name
}

Preparation of APP

DateUtil.java

Because our app has a function to return the login time in real time after login, as shown in the following figure:

In order to output the time in the expected format, I wrote a DateUtil class to store the time format. In fact, there is only one class: SimpleDateFormat (output the system time in format)

public class DateUtil {
//Here are three commonly used time display formats, which have a high degree of freedom and can be designed according to their own needs
    public static String getNowDateTime() {
    //Year month day hour: minute: Second
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
        return sdf.format(new Date());
    }

    public static String getNowTime() {
     //Hour: minute: Second
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss"); 
        return sdf.format(new Date());
    }

    public static String getNowTimeDetail() {
    //Hour: minute: second. Millisecond
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss.SSS");  
        return sdf.format(new Date());
    }

}

MainActivity.java

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
//Variable declaration
    SQLiteDatabase sqLiteDatabase;
    DateBaseHelper helper;
    private EditText username;
    private EditText password;
    private String name_str;
    private String paswd_str;
    private Button resign;
    private Button login;
    private Button btn_new;
    private String mstr="";
    private final static String TAG="MainActivity";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        //Control positioning
        helper=new DateBaseHelper(this);  //Delivery context
        username=(EditText) findViewById(R.id.username);
        password=(EditText) findViewById(R.id.password);
        resign=(Button) findViewById(R.id.resign);
        login=(Button) findViewById(R.id.login);
        btn_new=(Button)findViewById(R.id.btn_new);
        
        //① Open the database by reading and writing. Once the disk space of the database is full, the database can only be read but not written. If the getWritableDatabase() method is used, an error will occur
        //② There is also a getReadableDatabase() method to open the database in read-write mode. If the disk space of the database is full, it will fail to open. When the opening fails, it will continue to try
        //Open the database as read-only. If the problem is solved successfully, the read-only database object will be closed and a read-write database object will be returned.
        sqLiteDatabase = helper.getWritableDatabase();
        
        //Set button listening
        resign.setOnClickListener(this);
        login.setOnClickListener(this);
        btn_new.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        // Assign button events by id
        switch (v.getId()) {
        //Registration button function
            case R.id.resign:
            //Registration related pop-up settings. If it is determined to submit, the registration will be completed and the data will be stored in the database
            //AlerDialog: dialog control
                new AlertDialog.Builder(MainActivity.this).setTitle("System prompt")
                        .setMessage("Are you sure to submit?")
                        //Configure listening for the OK button
                        .setPositiveButton("determine", new DialogInterface.OnClickListener() {

                            @Override
                            public void onClick(DialogInterface arg0, int arg1) {
                                // Get the content in EditView, convert it into string type and store it in variable
                                name_str = username.getText().toString();
                                paswd_str = password.getText().toString();
                                
                                //Set a cursor for the data table. Cursor is a cursor based on the name field. query is a method to obtain data according to conditions
                                Cursor cursor = sqLiteDatabase.query(Constants.TABLE_NAME, new String[]{"name"}, "name=?", new String[]{name_str}, null, null, null);
                                //If the cursor finds the required name, it returns registered. Otherwise, it inserts data using the previously written insert method
                                if (cursor.getCount() != 0) {
                                    Toast.makeText(MainActivity.this, "The user is already registered!", Toast.LENGTH_SHORT).show();
                                } else {
                                    helper.insert(sqLiteDatabase, name_str, paswd_str);
                                    Toast.makeText(MainActivity.this, "login was successful,Please login!", Toast.LENGTH_SHORT).show();
                                }
                            }
                        }).setNegativeButton("return", new DialogInterface.OnClickListener() {

                    public void onClick(DialogInterface arg0, int arg1) {
                        //This is the operation after clicking back, because it is not necessary, so it doesn't matter if he jumps out directly.

                    }
                }).show();
                break;
                
          //Login button function, the above explanation has been omitted
            case R.id.login:
                String user_str = username.getText().toString();
                String psw_str = password.getText().toString();
                //When the account or password is empty
                if (user_str.equals("")) {
                    Toast.makeText(this, "Account or password cannot be blank", Toast.LENGTH_SHORT).show();
                }else {
                    Cursor cursor = sqLiteDatabase.query(Constants.TABLE_NAME, new String[]{"password"}, "name=?", new String[]{user_str}, null, null, null);
                    //Traverse the cursor to find the value of password corresponding to name
                    if(cursor.moveToNext()){
                        String psw_query=cursor.getString(cursor.getColumnIndex("password"));
                        //When the password corresponding to the user name is the same as the entered password
                        if(psw_str.equals(psw_query)){
                            Toast.makeText(this, "Login succeeded!", Toast.LENGTH_SHORT).show();
                            //Jump to the successActivity page
                            Intent intent=new Intent (MainActivity.this,successActivity.class);
                            //intent will carry the value of mstr on and name it with username
                            intent.putExtra("username",mstr);
                            //Start jump event
                            startActivity(intent);
                            break;
                        }
                        //When the password is entered incorrectly
                        else{
                        //Jump to the FaultActivity page
                            Intent intent2=new Intent(MainActivity.this,FaultActivity.class);
                            startActivity(intent2);
                        }
                    }
                    //After traversal, it is found that the value of name carried by the cursor cannot be found in the table
                    else{
                        Toast.makeText(this, "Account does not exist, please register first!", Toast.LENGTH_SHORT).show();
                    }
                }
                break;

         //Reset button function
            case R.id.btn_new:
            //Clear the text of EditView
                username.setText("");
                password.setText("");
                break;
            default:
                break;
        }
    }
}

Page writing after login success and failure

successfulActivity.java

public class successActivity extends AppCompatActivity implements View.OnClickListener {
	//Variable declaration
    private String mStr="";
    private String mTime;
    private final static String TAG="successActivity";
    private TextView tv_welcome;
    private TextView tv_time;
    private Button btn_back;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_success);
        btn_back=(Button)findViewById(R.id.btn_back);
        logintime();
        welcome();
        btn_back.setOnClickListener(this);
    }

//Return login time
    private void logintime() { 
        tv_time=(TextView)findViewById(R.id.tv_time);
        //Return the login time in the format of getNowDateTime in DateUtil
        mTime = String.format("login time %s", DateUtil.getNowDateTime());
        tv_time.setText(mTime);
    }

//Use the user information carried during the previous jump to the welcome interface
    private void welcome(){
    //Get intent
        Intent intent=getIntent();
        //Gets the value of the data named username in intent
        String username = intent.getStringExtra("username");
        tv_welcome=(TextView)findViewById(R.id.tv_welcome);
        mStr=String.format("%s Hello!", username);
        tv_welcome.setText(mStr);
    }

    @Override
    //Return to the implementation jump back to the login interface
    public void onClick(View v) {
        Intent intent2=new Intent(successActivity.this,MainActivity.class);
        startActivity(intent2);
    }
}

FaultActivity.java

//There's nothing fancy on this page
public class FaultActivity extends AppCompatActivity implements View.OnClickListener {

    private Button btn_back;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_fault);
        btn_back=(Button)findViewById(R.id.btn_back);
        btn_back.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        Intent intent=new Intent(FaultActivity.this,MainActivity.class);
        startActivity(intent);
    }
}