Learning notes for Android using SQLite

<1>SQLite Introduction

SQLite is a very popular embedded database. It supports the SQL language and has good performance only with a small amount of memory. In addition, it is open-source and can be used by anyone. Many open-source projects (Mozilla, PHP, Python) Use SQLite.

SQLite consists of the following components: SQL Compiler, kernel, backend, and attachment. SQLite makes debugging, modification, and expansion of SQLite kernel more convenient by using virtual machines and virtual database engine (vdbe ).

SQLite basically conforms to SQL-92 standards, and other major SQL database is no difference. Its advantage is its efficiency. The Android runtime environment contains the complete SQLite.

The biggest difference between SQLite and other databases is its support for data types. When creating a table, you can specify the Data Type of a column in the create table statement, however, you can put any data type into any column. When a value is inserted into the database, SQLite checks its type. If this type does not match the associated column, SQLite will try to convert the value to the column type. If the conversion fails, the value is stored as its own type. For example, you can put a string in the integer column. SQLite calls this "weak type" (manifest typing .).

In addition, SQLite does not support some standard SQL functions, especially the foreign key constraints (foreign key constrains), nested transcaction and right Outer Join and full outer join, and some ALTER TABLE functions.

In addition to the above functions, SQLite is a complete SQL System with complete triggers and transactions.

<2>Android integrates SQLite Database

Android integrates SQLite at runtime, so every Android ApplicationProgramYou can use the SQLite database. For developers familiar with SQL, using SQLite in Android development is quite simple. However, JDBC consumes too much system resources, so JDBC is not suitable for memory-constrained devices such as mobile phones. Therefore, Android provides some new APIs to use the SQLite database. In Android development, programmers need to learn to use these Apis.

The database is stored in data/<project folder>/databases.

<3>Using SQLite database in Android Development

Activites can access a database through content provider or service. The following describes in detail how to create a database, add data, and query a database.

<4>Create a database

Android does not automatically provide databases. To use SQLite in Android applications, you must create your own database, create tables, indexes, and fill in data.

* Android provides sqliteopenhelper to help you create a database. As long as you inherit the sqliteopenhelper class, you can easily create a database. The sqliteopenhelper class encapsulates the logic used to create and update databases based on the needs of application development. Sqliteopenhelper subclasses must implement at least three methods:

• constructor: calls the constructor of the parent class sqliteopenhelper. This method requires four parameters: context (for example, an activity), Database Name, and an optional cursor Factory (usually null ), an integer that represents the database model version you are using.
• oncreate () method, which requires a sqlitedatabase object as a parameter. Fill in the table and initialize data for this object as needed.
• onupgrage () method, which requires three parameters: A sqlitedatabase object, an old version number, and a new version number, in this way, you can understand how to transform a database from an old model to a new model.

<Textarea class = "Java" style = "width: 432px; Height: 192px; margin: 1.111px;" name = "code" readonly> package Mars. sqlite3.db; </P> <p> Import android. content. context; <br/> Import android. database. SQLite. sqlitedatabase; <br/> Import android. database. SQLite. sqliteopenhelper; <br/> Import android. database. SQLite. sqlitedatabase. cursorfactory; </P> <p> // databasehelper serves as an Assistant class to access SQLite and provides two functions. <br/> // first, getreadabledatabase (), getwritabledatabase () can obtain the sqlitedatabse object, through which you can operate on the database <br/> // second, provides two callback functions: oncreate () and onupgrade, when creating and upgrading databases, perform your own Operations </P> <p> public class databasehelper extends sqliteopenhelper {</P> <p> Private Static final int version = 1; </textarea>// This constructor must exist in the subclass of sqliteoepnhelper <br/> Public databasehelper (context, string name, cursorfactory factory, <br/> int Version) {<br/> // The constructor in the parent class must be called through Super <br/> super (context, name, factory, version ); <br/> // todo auto-generated constructor stub <br/>}< br/> Public databasehelper (context, string name) {<br/> This (context, name, version); <br/>}< br/> Public databasehelper (context, string name, int version) {<br/> This (context, name, null, version); <br/>}</P> <p> // This function is executed when the database is created for the first time. In fact, when the sqlitedatabse object is obtained for the first time, <br/> @ override <br/> Public void oncreate (sqlitedatabase dB) {<br/> // todo auto-generated method stub <br/> system. out. println ("Create a database"); <br/> // execsql function used to execute SQL statements <br/> db.exe csql ("CREATE TABLE user (ID int, name varchar (20) "); <br/>}</P> <p> @ override <br/> Public void onupgrade (sqlitedatabase dB, int oldversion, int newversion) {<br/> // todo auto-generated method stub <br/> system. out. println ("update a database"); <br/>}</P> <p >}< br/>

Next we will discuss how to create tables, insert data, and delete tables.

Call the getreadabledatabase () or getwriteabledatabase () method to obtain the sqlitedatabase instance. The method to call depends on whether you need to change the database content.

<5>Create tables and Indexes

To create tables and indexes, you must call the execsql () method of sqlitedatabase to execute DDL statements. If no exception exists, this method does not return a value.

Db.exe csql ("create table user (ID int, name varchar (20 ))");

<6>Add data to a table(Insert)

One way is to use the insert () of the sqlitedatabase object (). These methods take part of the SQL statement as a parameter. Example:

// Generate the contentvalues object <br/> contentvalues values = new contentvalues (); <br/> // you want to insert a key-value pair to the object. The key-value pair is the column name, the value is the value that you want to insert into this column. The value must be consistent with the data type in the database. <br/> values. put ("ID", 1); <br/> values. put ("name", "zhangsan"); <br/> databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db", 2); <br/> sqlitedatabase DB = dbhelper. getwritabledatabase (); <br/> // call the insert method to insert data to the database. <br/> dB. insert ("user", null, values );

<7> Update Data)

Use Update () of the sqlitedatabase object (). These methods take part of the SQL statement as a parameter. Example:

// Obtain a writable sqlitedatabase object <br/> databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db"); <br/> sqlitedatabase DB = dbhelper. getwritabledatabase (); <br/> contentvalues values = new contentvalues (); <br/> values. put ("name", "zhangsanfeng "); <br/> // The first parameter is the name of the table to be updated <br/> // The second parameter is a contentvaleus object <br/> // The third parameter is the WHERE clause. <br/> dB. update ("user", values, "id =? ", New string [] {" 1 "});

Similar to DDL in SQL

Update user set name = 'zhangsanfeng' where id = 1

<8> Query)

Use the query () and cursor of the sqlitedatabase object.

Cursor:

No matter how you perform the query, a cursor is returned. This is the SQLite database cursor of Android. You can use the cursor:

Use the getcount () method to obtain the number of records in the result set;

Use the movetofirst (), movetonext (), and isafterlast () Methods to traverse all records;

Get the field name through getcolumnnames;

Use getcolumnindex () to convert it into a field number;

Use getstring (), getint (), and other methods to obtain the value of the current record of the given field;

Query the cursor through requery () method;

Use the close () method to release cursor resources;

These methods take part of the SQL statement as a parameter. Example:

Public cursor query (string table, string [] columns, string selection, string [] selectionargs, <br/> string groupby, string having, string orderby, string limit)

The first parameter table is the name of the data table.

String [] columns, and this parameter corresponds to the "column name" in the SQL statement"

The string followed by where is the string selection parameter corresponding to the android method. In Android method, there is usually a parameter related to this: String [] selectionargs. When the selection parameter contains a question mark "? ", Then selectionargs will use

String selection = "city =? ";

In this case, we must assign the value string [] selectionargs = {"Beijing"} In selectionargs "};

That is to say, the string in selectionargs is the variable corresponding to the question mark in selection. In fact, the filtering condition city in selection can be dynamically assigned, rather than being written to the program. During query () execution, the strings in selectionargs are correctly escaped and replaced with the corresponding? To form a complete selection string. It is a bit like string. Format ().

Obviously, the string groupby parameter is the string corresponding to the group by parameter in the SQL statement. Group by is used together with Aggregate functions such as sum. You can check the Usage Details on the Internet.

The string having parameter corresponds to the character string after the SQL statement having and must be used together with the aggregate function.

The string orderby parameter corresponds to the string following the SQL statement order.

The limit parameter indicates the number of returned rows.

 

Databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db"); <br/> sqlitedatabase DB = dbhelper. getreadabledatabase (); <br/> cursor = dB. query ("user", new string [] {"ID", "name"}, "id =? ", New string [] {" 1 "}, null); <br/> while (cursor. movetonext () {<br/> string name = cursor. getstring (cursor. getcolumnindex ("name"); <br/> system. out. println ("query --->" + name); <br/>}