Android Learning Note (41): Use of SQLite

SQLite is a very popular embedded relational database, light load, fast, and open source. In Android, runtime provides SQLite, so we can use SQLite, and is the complete sqlite. SQLite provides a SQL interface, just like a normal database. But the Android API does not use JDBC,JDBC to consume too much resources.

SQLite supports most SQL-92 standards, does not support: FOREIGN KEY constraints, nested transactions, right OUTER join, full OUTER join, and some flavors of ALTER TABLE. And we use SQLite on the handheld terminal, generally do not involve too complex database processing, in addition to appeals, other SQL, including Tirger, transaction, etc. are supported, should say that SQLite provides the functionality is enough.

and general SQL database comparison, the biggest difference is the data type, for example, we define a table of a column of the data type is integet, if at the time of inserting this value string, in SQLite is the package will produce an error, We can use the data type of the definition table as a hint to illustrate the expected type of data, but it does not really make sense. If you really need to limit it, you have to judge the rest of the program.

1. Build our Database

In MySQL and other databases, the first step is to create the database, the second step is to create the table, if necessary, plus our initial prefabricated data. The use of SQLite on Android is the same. Slightly more specifically, we need to achieve this by inheriting the Sqliteopenhelper class. For abstract class Sqliteopenhelper inheritance, you need to rewrite: 1) constructor,2) onCreate () and Onupgrade (), as illustrated below.

In this example, we create a database called bebook_db, which has a table called MyTable, with three columns: _id,name,weight. We'll show you how to create a database, how to create a table in a database, how to delete a table, and how to update a database.

/* For abstract class Sqliteopenhelper inheritance, need to rewrite: 1) constructor,2) onCreate () and Onupgrade () * */
public class Chapter22db extends sqliteopenhelper{
public static final String database_bame = "bebook_db";

/* Step 1: Rewrite the constructor, inherit the super constructor, create the database */
Public chapter22db (Context context) {
/* The first parameter is the current environment
* The second parameter, String name, is a database file, or null if the data is stored in memory.
* The third parameter is Sqlitedatabase.cursorfactory factory, which holds the cursor, the default setting is NULL
* The fourth parameter is the version of the int version database, starting with 1, if the version is old, it is updated with Onupgrade (), and if the version is new, it is published by Ondowngrade (). For example, I want to change the MyTable table, add a column, or modify the initialized data, or the program becomes complex, I need to add a table, when I need to increase the number of versions, at the time of loading, it will be in the SQLite database updates, this is very important, see also Onupgrade () Description of */
Super (context,database_bame,null,1);
}

/*step 2: Rewrite OnCreate (), if the Android system first created our database (that is, when the call Getwritabledatabase () or getreadbledatabase () is described later), the OnCreate will be called (), where the database is created (although the database name is populated in the constructor, but the creation of the database is OnCreate () automatically. In this case, the table is created and the initial data is written */
public void OnCreate (Sqlitedatabase db) {
The statement creating the Table:sql is "Create TABLE constants (_id INTEGER PRIMARY KEY autoincrement,title TEXT, value REAL);" We can directlyDb.execsql (SQLCommand) to execute SQL language with no return value, such as Create,delete,update,insert,drop.
Db.execsql ("CREATE TABLE mytable (_id INTEGER PRIMARY KEY autoincrement, Name text,weight REAL);");

The following is the addition of three raw data, if the table to increase, delete, change, check, will be described in detail later. The following data comes from Android's own gravity table, supposedly for sensor management, Android has taken into account our use of Android phones on the moon and Mars ^_^, programmers are sometimes really boring ...
Contentvalues CV = new Contentvalues ();

Cv.put ("Name", "Gravity, Earth");
Cv.put ("Weight", Sensormanager.gravity_earth);
Db.insert ("MyTable", "Name", CV);

Cv.put ("Name", "Gravity, Mars");
Cv.put ("Weight", sensormanager.gravity_mars);
Db.insert ("MyTable", "Name", CV);

Cv.put ("Name", "Gravity, Moon");
Cv.put ("Weight", Sensormanager.gravity_moon);
Db.insert ("MyTable", "Name", CV);
}

/* Step 3: Rewrite Onupgrade (), if the version is higher than the original, will call Onupgrade (), in this example, we delete the original table, created according to the new requirements */
public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {
This time againto execute a SQL language with no return value through Db.execsql (SQLCommand), delete the table
Db.execsql ("DROP TABLE IF EXISTS mytable");
OnCreate (DB);
}

}

2. Associating with a database

As in MySQL, the creation of a database, the creation of tables, and the filling of initial data, other operations typically occur in activity and user interaction. Recall how we handled the Linux environment, first to create a connection to the database, the same for Android, and at the end of the activity we need to disconnect the connection to release the resources.

Public class Chapter22test1 extends listactivity{
    private sqlitedatabase  db = null ;
    private cursor cursor = NULL;//In subsequent interactions with the ListView

    protected void OnCreate (Bundle savedinstancestate) {
        super.oncreate ( Savedinstancestate);
       //Get an instance of SQLite that handles Sqliteopenhelper subclasses, if read-only, can take Getreadabledatabase (), In this example, we award the data to be sqlitedatabase by the operation of the instance, using the writable way.
          db= (New chapter22db (Getapplicationcontext ())) . Getwritabledatabase ();   
    }

    protected void OnDestroy () {
        Super.ondestroy ();
      //release and database connection
         db.close ();
    }
}

3. Operation of the table

There are two ways to work with tables, one is raw, that is to give SQL statements directly, and the other is to use the method given in Sqlitedatabase, which is called API mode. The following two ways to increase, delete, change, check the experiment.

3.1 Adding a row of data

When setting up a table, Db.execsql (SQLCommand) is used to execute the SQL language with no return value, which is raw. Another way to do this is to add the original data, by Db.insert ("MyTable", "Name",<contentvalues values>); The second parameter is more specific. SQL is not running to join an empty row. If the second parameter is not set to NULL, the case is processed and the value of the corresponding column is set to "null".

Raw mode.
Db. Execsql ("INSERT into MyTable (name,weight) VALUES (' Test1 ', 1.0);");
API way, through Db.insert ("MyTable", "name", <contentvalues values>), to handle, where contentvalues is used to store names and values, The noun that corresponds to the column of the table and the data in its row.
Contentvalues values =new contentvalues (2);//contentvalues has two values
Values.put ("Name", "Test2"); A column is named name and the data is Test2
Values.put ("Weight", 2.0); A column named weight with a data of 2.0
Db. Insert ("MyTable", "Name", values);

3.2 Deleting a row of data

Raw mode
Db. Execsql ("DELETE from mytable WHERE name= ' Test1 ';");
API mode by: Delete (string table, string whereclause, string[] Whereargs)
Db. Delete ("MyTable", "Name=?", {"Test1"});

3.3 Updating a row of data

Raw mode
Db. Execsql ("UPDATE mytable SET weight=5.0 WHERE name= ' Test1 ';");
API mode by: Update (string table, contentvaluesvalues, stringwhereclause, string[] Whereargs)
String[] name = {"Test1"};
Contentvalues values =new contentvalues (2);
Values.put ("Name", "Test1");
Values.put ("Weight", 5.0);
Db. Update ("MyTable", Values, "Name=", Name);

3.4 Querying and Cursors cursor

None of the three operations above are return values, and the query select does not return the cursor cursors. Here are two ways to query

Raw mode, with return value, in db.rawquery (SQL statement) mode
Cursor result1 =db. Rawquery ("Select _id,name,weight from MyTable ORDER by Name", NULL);
/api mode, with return value, with public Cursor query (string table, string[] columns, string selection, string[] Selectionargs, String GroupB Y, String has, string is the same)
string[] Columns ={"Name", "Weight"};
string[] name ={"Name"};
Cursor result2 = db. Query ("MyTable", Columns, "Name=?", Name, NULL, NULL, NULL);

Cursors and iterator interfaces are somewhat similar, and for cursor result we can read the data in the following way:

Result. Movetofirst ();
while (!result. Isafterlast()) {
int id = result.getint (0);
String name = result.getstring (1);
Double weight = result.getdouble (2);
System.out.println ("\ t" +id + "\t[" + name + "]\t" +weight);
Result. MoveToNext ();
}
Result. Close ();

Through the cursor we can read the details of the database, combined to the Android learning Note (20): Regression simple listview, we can data with arraylist

However, the cursor actually has information, we can also use CursorAdapter to deal with, the next time we will be on the basis of this example, through the CursorAdapter and the ListView combined to give an example.

"Now Guo Degang on the internet to make so many things, in ethics, there are so many problems, I do not understand why so many people like him?" "On the afternoon of September 28, Jiang Kun in Guangzhou, two hours, he said to learn to sing, tightly to the audience's ears dragged to his mouth." He also expressed strong doubts about the popularity of Guo Degang crosstalk. See http://ent.qq.com/a/20111002/000037.htm. This is the word of the Chinese-style vicious and shameless actor. 1, can not be in the art of accusations against people, on the moral, even to the ethics, curse; 2, and can not be morally, point out the illustration, anyway, the direct a hat cover over, this thinking and way of doing things, still pernicious influence, frightening and uneasy. Better bred than a shrew, more villain and vicious than a shrew, is an insult to a vixen compared with a shrew.

RELATED links: My Andriod development related articles

Android Learning Note (41): Use of SQLite