How to use the SQLite of Android development _android

Preface

SQLite is a lightweight small database, although relatively small, but the function is comparatively perfect, some common database basic functions also have, in the current embedded system to use the database is more, because it occupies very little system resources. Android system is no exception, but also the use of SQLite, this section of the study in the Andorid How to use the database to store data, and SQLite complete a simple new, updated, query, delete and other operations.

　　Experiment Description:

When using the SQLite database in Android, you need to use ADB to aid debugging, and if you want to use ADB in the CMD command line under Windows, you must first configure the environment variables, I am here with the user environment variable path:c:\program files\ Android-sdk\platform-tools;

After configuring the environment variables, you need to open the Android emulator (which is not true for the simulator) before entering the ADB shell into the Linux shell environment in CMD. If you start the emulator and you enter the ADB shell command with the error:device not found error prompt, you can kill the ADB process and then restart the process, and enter the following command in CMD:

　　ADB kill-server

ADB start-server

If you want to use SQLite in Android, you typically need to write a new class that inherits an Android Sqliteopenhelper that provides access to the database. In this experiment we create a new class under the SRC folder, the class name is Databasehelper, the class inherits the Sqliteopenhelper class, and the class that inherits the Sqliteopenhelper class must have its own constructor, so according to Mars teacher's code, 3 constructors are written in the program, and the 3 functions are distinguished by the number of arguments, and the function with fewer parameters is because some of the other parameters have been fixed, and they are both showing or implicitly calling the constructor of the parent class, and the following is the Sqliteopenhelper constructor.

　　Public Sqliteopenhelper (Context context, String name, sqlitedatabase.cursorfactory Factory, int version)

The first parameter is the class itself; the second parameter is the name of the database; The 3rd parameter is used to set the cursor object, which is generally set to NULL, and parameter four is the version number of the database.

　　public void Execsql (String sql)

The function is a function in the class sqlitedatabase that executes an SQL statement command whose contents are the parameters of the function. Therefore, the parameter SQL needs to conform to the SQL syntax rules.

　　Public Contentvalues ()

Contentvalues is used to store data in the database class, but also the use of key-value pairs to store data, a bit like content and bundle. This constructor is an empty dataset that establishes a default size.

　　Experiment Steps and Results:

　　Interface design:

The interface design of the program is relatively simple, because of the need to complete 6 functions, so there are only 6 buttons in the interface, each button corresponds to a function, the function of these buttons is to create a new database, update the version of the database, insert records into the database, update the records in the database, query the records in the database, Deletes records from the database. Its interface effect is as follows:

　　

　　To establish a database:

Run the simulator, click the Create SQLite Database button, create a tornadomeet named "", the version number 1, this time you can see the program terminal display "Create a SQLite database" words. In cmd, enter the following command:adb SHELL;CD data; CD data; ls; cd com.example.sqlite_test; LS; command, where Com.example.splite is the package name of the program, enter the last LS command, you can see 2 folders cache, lib. Then, after clicking, continue to the LS command to view, one more database folder. Using the command CD database to enter the folder, LS found inside there are databases, screenshots are as follows:

　　

Continue typing sqlite3 tornadomeet.dbon the cmd command line, where tornadomeet the newly established database name after you click to create the database. Input . Schema;(Note that there is a point above) that appears as follows:

　　

You can see that the database has 2 tables, and you can see the SQLite statements created by these 2 tables, the first of which represents Android, and the second represents the new one.

Continue to enter the command select * from User1; (Note that at this point, because it's a real sqlite statement, you'll want to end the semicolon in the command line to query the table in the database that you set up User1, and find nothing inside.)

　　Update database version:

In this step, when the Update SQLite database button is pressed, we create a new one in the listener function, with the database named "Tornadomeet" unchanged, but set its version number to 2. Because the version number changes, the program automatically invokes the Onupgrade method of the Sqliteopenhelper subclass, which simply prints a single statement in the method.

　　Database insert:

Click the Insert button of the simulator to enter the SQLite corresponding database and input select * from User1; After the command (with a semicolon) you can see the following display: 1 | Tornado, our database successfully inserts a record that is statically given in the program.

　　To update the contents of the database:

The update operation is equivalent to executing the SQL UPDATE statement, the syntax is:update table_name SET # # #col =## #1 WHERE # # #col = # # #2

This procedure changes the name of a record inserted above to Tornadomeet, so when the Update button is pressed, tornado the select * from User1 in Cmd to see that record becomes 1 | It's tornadomeet.

　　Query action:

Query operation using the Query method in Sqliteopenhelper, where the query refers to the column name to query, the actual query process is a cursor, at the beginning point to the table head, the next one to move down until the condition of the query to meet the output, and then terminate the query.

　　Delete action:

The delete operation is similar to the previous one, using the Delete method in Sqliteopenhelper, by specifying the column name as a value, and then deleting the record.

The following is the result of ADB background debugging after clicking the new SQLite database button, inserting the record button, updating the record button, inserting the Record button again, and deleting the button.

　　

It can be seen from this that the deletion was successful.

　　The main part of the experiment Code and Comments (appendix has the experimental Project, code download link):

Copy Code code 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
Creates an object of the Databasehelper class, which is a single Java file, using the 2-parameter constructor, the established data
The name of the library is tornadomeet.db
Databasehelper database_helper = new Databasehelper (mainactivity.this, "tornadomeet.db");
Only call Getreadabledatabase () or getwriteabledatabase () function to return a Sqlitedatabase object
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) {
Generates a Contentvallues object that is used to store data
Contentvalues values = new Contentvalues ();
Values.put ("id", 1);//Note the type of value to match
Values.put ("name", "Tornado");
Databasehelper database_helper = new Databasehelper (mainactivity.this, "tornadomeet.db");
Sqlitedatabase db = Database_helper.getwritabledatabase ()//Here is the database to get writable
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 is the table name, Parameter 2 is the updated value, Parameter 3 represents the column name that satisfies the condition, and parameter 4 is the value of the column
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 ();
The syntax of the query, parameter 1 is the table name, Parameter 2 is the column name in the table, parameter 3 is the column name to query, and the parameter is the value of the corresponding column, and the function returns a cursor
Cursor Cursor = Db.query ("user1", new string[]{"id", "name"}, "Id=?", New string[]{"1"}, NULL, NULL, NULL);
Traverse each record
while (Cursor.movetonext ()) {
String name = cursor.getstring (Cursor.getcolumnindex ("name"))//Return value with column name named
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 ();
Delete the record directly named 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 Code code 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

Classes that inherit the Sqliteopenhelper class must have their own constructors
The constructor of the parent class is called directly with 4 parameters. The first parameter is the class itself; the second parameter is the name of the database;
The 3rd parameter is used to set the cursor object, which is generally set to NULL, and parameter four is the version number of the database.
Public Databasehelper (Context context, String name, cursorfactory factory, int verson) {
Super (context, name, Factory, Verson);
}

The constructor has 3 parameters, because it fixed the 3rd argument of the above function as null
Public Databasehelper (Context context, String name, int verson) {
This is (context, name, NULL, Verson);
}

The constructor has only 2 parameters, and the base hill of the above function is fixed with the version number.
Public Databasehelper (Context context, String name) {
This is (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, so the statement in the argument needs to conform to the SQL syntax, which is to 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 Code code 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 flow of SQLite in Andorid.

Appendix:

Experimental Engineering Code Download