How to use SQLite for Android development

Objective

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

　　Experiment Description:

When using SQLite database in Android, you need to use ADB to assist debugging, 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, enter the ADB shell in CMD before entering the Linux shell environment, you need to open the Android emulator (this article is for the emulator, not the real machine). If the emulator is started and the error:device not found error message appears after entering the adb shell command, 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 re-write a class that inherits an Android-provided helper class Sqliteopenhelper that accesses 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 inheriting the Sqliteopenhelper class must have its own constructor, so according to the Mars Teacher's code, 3 constructors are written in the program, the difference between the 3 functions is that the number of arguments is different, the number of parameters is small because the other parameters have been fixed, and they are to display or implicitly call the parent class constructor, 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 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)

This function is a function of the class sqlitedatabase, and its function is to execute an SQL statement command, the contents of which is the parameters of the function. Therefore, the parameter SQL needs to conform to the SQL syntax rules.

　　Public Contentvalues ()

Contentvalues is a class for storing data in a database, and a key-value pair to hold data, a bit like content and bundles. The constructor is a set of empty datasets with a default size.

　　Experimental Steps and Results:

　　Interface design:

The interface design of the program is relatively simple, because it needs 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 database version, insert records into the database, update the records in the database, query the database records, Delete the records in the database. The interface effect is as follows:

　　

　　To establish a database:

Run the emulator, click the Create SQLite Database button, create a databases named "Tornadomeet", the version number is 1, this time you can see the program terminal display "Create a SQLite database" word. Enter the following command in CMD: adb shell;cd data; CD data; ls CD com.example.sqlite_test; ls; command, where Com.example.splite is the package name of this program, enter the last LS command after you can see that there are 2 folders cache, lib. Then after clicking continue the LS command to view, one more database folder. Use the command CD to enter the folder after the LS found inside the database, as follows:

　　

Continue to enter Sqlite3 tornadomeet.db on the cmd command line, where Tornadomeet is the newly created database name after you click Create Database. Enter the. Schema; (Note that there is a point earlier) that shows the following:

　　

You can see that there are 2 tables in this database, and you can see the SQLite statements created by these 2 tables, the first of which is the one that comes with Android, and the second one that says we're new.

Continue to enter the command select * from User1; (note at this point because it is a true SQLite statement, so at the command line to end with a semicolon) query the database of the table user1, found 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 name "Tornadomeet" unchanged, just set its version number to 2. Because the version number changes, the program automatically calls the Onupgrade method of the subclass of Sqliteopenhelper, which only outputs a statement in the method.

　　Database insert:

Click the Insert button of the emulator to enter the database of SQLite, then 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.

　　Update database content:

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

This program will be inserted in the name of a record name has tornado changed to Tornadomeet, so when the Update button is pressed, re-use the select * from User1 in CMD can see that the record becomes 1 | It's tornadomeet.

　　Query operation:

Query operations Use the Sqliteopenhelper query method, where the query refers to the column name is queried, the actual query process is a cursor, the start point to the table header, the following time after the next move down until the conditions of the query to meet the output, and then terminate the query.

　　Delete operation:

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

Here are the results of the ADB background debug after clicking the new SQLite database button, inserting the record button, updating the record button, inserting the Record button again, and deleting the button:

　　

As you can see, the delete operation is also successful.

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

Copy the 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
Create an object of the Databasehelper class, which is a single Java file, where the 2-parameter constructor is used to create the data
The name of the library 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) {
Generates a Contentvallues object that is used to store data
Contentvalues values = new Contentvalues ();
Values.put ("id", 1);//Note the type of the 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 ();
query syntax, parameter 1 is the table name, Parameter 2 is the column name in the table, parameter 3 is the name of the column to query, 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 every record
while (Cursor.movetonext ()) {
String name = cursor.getstring (Cursor.getcolumnindex ("name"));//Returns the value of the column named 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 ();
Delete the record named Tornadomeet directly
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

Classes that inherit the Sqliteopenhelper class must have their own constructors
This constructor has 4 parameters that call the constructor of the parent class directly. 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 (context, name, NULL, Verson);
}

The constructor has only 2 parameters, and the base mountain of the above function fixed the version number
Public Databasehelper (Context context, String name) {
This (context, name, Verson);
}

This function is called when the database is first built
@Override
public void OnCreate (Sqlitedatabase arg0) {
TODO auto-generated Method Stub
System.out.println ("Create a SQLite database");
Execsql () is the SQL statement inside the execution parameter, so the statement in the argument needs to conform to the SQL syntax, here is the creation of 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>

Summary: Through this experiment, the use of SQLite in the Andorid process has a preliminary understanding.

How to use SQLite for Android development