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); } }