How to Use SQLite in Android Development

Preface

SQLite is a lightweight small database. Although it is relatively small, it has relatively complete functions, and some common basic functions of databases are also available, in the current embedded system, this database is used a lot because it occupies a small amount of system resources. SQLite is also used in the Android system. This section describes how to use the database in andorid to store data and complete simple creation, update, and query of SQLite, delete.

　　Lab description:

When using the SQLite database in Android, you need to use adb for debugging. To use adb in the cmd command line in windows, you must first configure the environment variable, here is the configured user environment variable.Path: C: \ Program Files \ android-sdk \ platform-tools;

After environment variables are configured, open the android simulator before entering the adb shell in cmd to enter the linux shell environment (this article is for the simulator, not the real machine ). If the simulator is started and the adb shell command is enteredError: device not foundIf an error occurs, you can kill the adb process and restart the process. In cmd, enter the following command:

　　Adb kill-server

Adb start-server

If you want to use SQLite in android, you generally need to write a new class, which inherits the helper class SQLiteOpenHelper provided by android to access the database. In this experiment, we create a new class under the src folder named DatabaseHelper, which inherits the SQLiteOpenHelper class, and the class that inherits the SQLiteOpenHelper class must have its own constructor, therefore, according to the Code of the mars instructor, three constructor functions are written in the program. The difference between the three functions is that the number of parameters is different, the number of parameters is small because other parameters have been fixed, and they both show or implicitly call the constructor of the parent class. The following is the constructor of SQLiteOpenHelper.

　　Public SQLiteOpenHelper (Context context, String name, SQLiteDatabase. CursorFactory factory, int version)

The first parameter is the class itself; the second parameter is the database name; the second parameter is used to set the cursor object, which is generally set to null; and the fourth parameter is the database version number.

　　Public void execSQL (String SQL)

This function is a function in the SQLiteDatabase class. Its function is to execute an SQL statement command. The content of this statement is the parameter of this function. Therefore, the SQL parameter must comply with the SQL syntax rules.

　　Public ContentValues ()

ContentValues is a class used to store data in databases. It also uses key-value pairs to store data, a bit similar to content and bundle. This constructor is used to create an empty dataset of the default size.

　　Experiment steps and results:

　　Interface Design:

The Interface Design of this program is relatively simple. Because the six functions need to be completed, there are only six buttons on the interface. Each button corresponds to a function. The functions of these buttons are new databases in turn, update the database version, insert records into the database, update records in the database, query records in the database, and delete records in the database. The interface effect is as follows:

　　

　　Create a database:

Run the simulator and click the create sqlite database button to create a database named "tornadomeet" with the version number 1. At this time, the program terminal displays the "create a sqlite database. Enter the following command in cmd:Adb shell; cd data; ls; cd com. example. sqlite_test; ls;Command, where com. example. splite is the package name of the program. after entering the last ls command, we can see two folders cache and lib. Then, click the ls command to continue viewing. A database folder is added. Use commandsCd databaseAfter entering the folder, ls finds a database in it, as shown below:

　　

Continue to input in cmd command lineSqlite3 tornadomeet. db(Tornadomeet indicates the name of the newly created database after you click Create Database .) Input. Schema; (Note that the previous point is shown as follows:

　　

We can see that the database has two tables, and we can see the sqlite statements created for these two tables. The first one indicates the built-in android statements, and the second one indicates the new ones.

Continue to enter the commandSelect * from user1;(Note that this is a real sqlite statement, so you should end with a semicolon in the command line) query the user1 table in the database you created and find that there is nothing in it.

　　Update database version:

In this step, when you press the update sqlite database button, we create a new database in the listener function. The database name is "tornadomeet", but the version number is set to 2. Because the version number has changed, the program will automatically call the onUpgrade method of the subclass of SQLiteOpenHelper. This program only outputs a statement in this method.

　　Database insertion:

Click the insert button of the simulator to enter the database corresponding to sqlite, and enterSelect * from user1;After the command (with a semicolon), we can see the following: 1 | tornado, indicating that a record is successfully inserted in our database. The record content is static in the program.

　　Update database content:

The UPDATE operation is equivalent to executing the SQL UPDATE statement. Syntax:UPDATE table_name SET ### col #### 1 WHERE ### col #### 2

In this program, the name of the record inserted above has tornado changed to tornadomeet. Therefore, when you press the update button, you can use it again in cmd.Select * from user1We can see that the record has changed to 1 | tornadomeet.

　　Query operation:

The query operation uses the query method in SQLiteOpenHelper. The query here refers to the query by column name. In the actual query process, there is a cursor that points to the header at the beginning and moves down the next time, the query is output when the query conditions are met, and then the query is terminated.

　　Delete operation:

The delete operation is similar to the previous one. Use the delete method in SQLiteOpenHelper to specify a column name as a value and then delete the record.

The following figure shows the debugging result of the adb background after you click Create SQLite database, insert record, update record, and insert record again:

　　

The deletion operation is successful.

　　The code and comments of the experiment are as follows ):

Copy codeThe Code is as follows: package com. example. sqlite_test;

Import android. app. Activity;
Import android. content. ContentValues;
Import android. database. Cursor;
Import android. database. sqlite. SQLiteDatabase;
Import android. OS. Bundle;
Import android. view. Menu;
Import android. view. View;
Import android. view. View. OnClickListener;
Import android. widget. Button;

Public class MainActivity extends Activity {

Private Button create_database = null;
Private Button update_database = null;
Private Button insert = null;
Private Button update = null;
Private Button query = null;
Private Button delete = null;
@ Override
Public void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
SetContentView (R. layout. activity_main );

Create_database = (Button) findViewById (R. id. create_database );
Create_database.setOnClickListener (new CreateDatabaseOnClickListener ());
Update_database = (Button) findViewById (R. id. update_database );
Update_database.setOnClickListener (new UpdateDatabaseOnClickListener ());
Insert = (Button) findViewById (R. id. insert );
Insert. setOnClickListener (new InsertOnClickListener ());
Update = (Button) findViewById (R. id. update );
Update. setOnClickListener (new UpdateOnClickListener ());
Query = (Button) findViewById (R. id. query );
Query. setOnClickListener (new QueryOnClickListener ());
Delete = (Button) findViewById (R. id. delete );
Delete. setOnClickListener (new DeleteOnClickListener ());
}

Public class CreateDatabaseOnClickListener implements OnClickListener {

Public void onClick (View v ){
// TODO Auto-generated method stub
// Create a DatabaseHelper class object, which is a separate java file. Here we use the constructor with two parameters to create data
// The Library name is tornadomeet. db
DatabaseHelper database_helper = new DatabaseHelper (MainActivity. this, "tornadomeet. db ");
// A SQLiteDatabase object can be returned only after the getReadableDatabase () or getWriteableDatabase () function is called.
SQLiteDatabase db = database_helper.getReadableDatabase ();
}
}

Public class UpdateDatabaseOnClickListener implements OnClickListener {

Public void onClick (View v ){
// TODO Auto-generated method stub
DatabaseHelper database_helper = new DatabaseHelper (MainActivity. this, "tornadomeet. db", 2 );
SQLiteDatabase db = database_helper.getReadableDatabase ();
}
}

Public class InsertOnClickListener implements OnClickListener {

Public void onClick (View v ){
// Generate the contentvallues object, which is used to store data
ContentValues values = new ContentValues ();
Values. put ("id", 1); // note that the value type must match
Values. put ("name", "tornado ");
DatabaseHelper database_helper = new DatabaseHelper (MainActivity. this, "tornadomeet. db ");
SQLiteDatabase db = database_helper.getWritableDatabase (); // you can obtain the writable database.
Db. insert ("user1", null, values );
}
}

Public class UpdateOnClickListener implements OnClickListener {

Public void onClick (View v ){
// TODO Auto-generated method stub
DatabaseHelper database_helper = new DatabaseHelper (MainActivity. this, "tornadomeet. db ");
SQLiteDatabase db = database_helper.getWritableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "tornadomeet ");
// Parameter 1 indicates the table name, parameter 2 indicates the updated value, parameter 3 indicates the column name that meets the conditions, and parameter 4 indicates the value under the column name.
Db. update ("user1", values, "id =? ", New String [] {" 1 "});
}
}

Public class QueryOnClickListener implements OnClickListener {

Public void onClick (View v ){
// TODO Auto-generated method stub
DatabaseHelper database_helper = new DatabaseHelper (MainActivity. this, "tornadomeet. db ");
SQLiteDatabase db = database_helper.getWritableDatabase ();
// Query syntax. Parameter 1 is the table name; parameter 2 is the column name in the table; parameter 3 is the column name to be queried; parameter 2 is the value of the corresponding column; the function returns a cursor
Cursor cursor = db. query ("user1", new String [] {"id", "name"}, "id =? ", New String [] {" 1 "}, null );
// Traverse each record
While (cursor. moveToNext ()){
String name = cursor. getString (cursor. getColumnIndex ("name"); // return the value of the column name.
System. out. println ("query ---->" + name );
}
}
}

Public class DeleteOnClickListener implements OnClickListener {

Public void onClick (View v ){
// TODO Auto-generated method stub
DatabaseHelper database_helper = new DatabaseHelper (MainActivity. this, "tornadomeet. db ");
SQLiteDatabase db = database_helper.getWritableDatabase ();
// Directly Delete the record corresponding to tornadomeet
Db. delete ("user1", "name =? ", New String [] {" tornadomeet "});
}

}

@ Override
Public boolean onCreateOptionsMenu (Menu menu ){
GetMenuInflater (). inflate (R. menu. activity_main, menu );
Return true;
}
}

DatabaseHelper. java:Copy codeThe Code is as follows: package com. example. sqlite_test;

Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteDatabase. CursorFactory;
Import android. database. sqlite. SQLiteOpenHelper;

Public class DatabaseHelper extends SQLiteOpenHelper {

Private static final int VERSON = 1; // default database version

// The class that inherits the SQLiteOpenHelper class must have its own Constructor
// This constructor has four parameters and calls the constructor of the parent class directly. The first parameter is the class itself, and the second parameter is the database name;
// The third parameter is used to set the cursor object. Here it is generally set to null. Parameter 4 is the database version number.
Public DatabaseHelper (Context context, String name, CursorFactory factory, int verson ){
Super (context, name, factory, verson );
}

// The constructor has three parameters because it sets the 3rd parameters of the preceding function to null.
Public DatabaseHelper (Context context, String name, int verson ){
This (context, name, null, verson );
}

// The constructor has only two parameters. The base hill of the above function fixes the version number.
Public DatabaseHelper (Context context, String name ){
This (context, name, VERSON );
}

// This function is called when the database is first created
@ Override
Public void onCreate (SQLiteDatabase arg0 ){
// TODO Auto-generated method stub
System. out. println ("create a sqlite database ");
// ExecSQL () is the SQL statement in the execution parameter. Therefore, the statements in the parameter must comply with the SQL syntax. Here we create a table.
Arg0.execSQL ("create table user1 (id int, name varchar (20 ))");
}

@ Override
Public void onUpgrade (SQLiteDatabase arg0, int arg1, int arg2 ){
// TODO Auto-generated method stub
System. out. println ("update a sqlite database ");
}

}

Activity_main.xml:Copy codeThe Code is as follows: <RelativeLayout xmlns: android = "http://schemas.android.com/apk/res/android"
Xmlns: tools = "http://schemas.android.com/tools"
Android: layout_width = "match_parent"
Android: layout_height = "match_parent">

<Button
Android: id = "@ + id/create_database"
Android: layout_width = "fill_parent"
Android: layout_height = "wrap_content"
Android: layout_alignParentBottom = "true"
Android: text = "@ string/create_database"
/>

<Button
Android: id = "@ + id/update_database"
Android: layout_width = "fill_parent"
Android: layout_height = "wrap_content"
Android: layout_above = "@ id/create_database"
Android: layout_alignParentLeft = "true"
Android: text = "@ string/update_database"/>

<Button
Android: id = "@ + id/insert"
Android: layout_width = "fill_parent"
Android: layout_height = "wrap_content"
Android: layout_above = "@ id/update_database"
Android: layout_alignParentLeft = "true"
Android: text = "@ string/insert"/>

<Button
Android: id = "@ + id/update"
Android: layout_width = "fill_parent"
Android: layout_height = "wrap_content"
Android: layout_above = "@ id/insert"
Android: layout_alignParentLeft = "true"
Android: text = "@ string/update"/>

<Button
Android: id = "@ + id/query"
Android: layout_width = "fill_parent"
Android: layout_height = "wrap_content"
Android: layout_alignParentLeft = "true"
Android: layout_above = "@ id/update"
Android: text = "@ string/query"
/>
<Button
Android: id = "@ + id/delete"
Android: layout_width = "fill_parent"
Android: layout_height = "wrap_content"
Android: layout_alignParentLeft = "true"
Android: layout_above = "@ id/query"
Android: text = "@ string/delete"
/>

</RelativeLayout>

Conclusion: Through this experiment, we have a preliminary understanding of the use process of SQLite in andorid.

Appendix:

Download lab project code