Android learning notes (4yi): Use of SQLite

SQLite is a very popular embedded relational database. It is light-loaded, fast, and open-source. In Android, runtime provides SQLite, so we can use SQLite, and it is a complete set of SQLite. SQLite provides SQL interfaces, which are the same as general databases. However, Android APIs do not use JDBC, and JDBC consumes too much resources.

SQLite supports most SQL-92 standards and does not support: foreign key constraints, nested transactions, right outer join, full outer join, and some flavors of alter table. we use SQLite on a handheld terminal, which generally does not involve complicated database processing. In addition to appeal, other SQL statements, including tirger and transaction, are supported, it should be said that the functionality provided by SQLite is sufficient.

Compared with a general SQL database, the biggest difference is the data type. For example, we define the data type of a column in a table as INTEGET. If the value is String at the time of insertion, in SQLite, an error occurs when a package is used. We can use the data type of the defined table as a prompt to describe the expected data type, but it does not actually play a role in detection. If you really need restrictions, You need to judge other parts of the program.

1. Create our database

In MySQL and other databases, the first step is to create a database, and the second step is to create a table. If necessary, we also add our initial pre-fabricated data. The use of SQLite in Android is the same. Specifically, we need to inherit the SQLiteOpenHelper class to achieve our goal. For the inheritance of the abstract class SQLiteOpenHelper, rewrite: 1) constructor, 2) onCreate () and onUpgrade (). The following is an example.

In this example, we create a database named bebook_db, which contains a table named mytable with three columns: _ id, Name, and Weight. The following describes how to create a database, create a table in a database, delete a table, and update a database.

/* For the inheritance of the abstract class SQLiteOpenHelper, rewrite: 1) constructor, 2) onCreate () and onUpgrade ()**/
Public class chapter22db extends sqliteopenhelper {
Public static final string database_bame = "bebook_db ";

/* Step 1: override the constructor, inherit the super constructor, and create a database */
Public chapter22db (context ){
/* The first parameter is the current environment.
* The second parameter, string name, is a database file. If the data is stored in the memory, it is null,
* The third parameter is sqlitedatabase. cursorfactory factory, which stores cursor. The default value is null.
* The fourth parameter is the version of the int version database, starting from 1. if the version is old, it is updated through onupgrade (). if the version is new, it is published through ondowngrade. For example, if I want to change the mytable table, add a column, or modify the initialized data, or the program becomes complicated, I need to add a table. In this case, I need to increase the number in the version, it is very important to update the database in SQLite only when loading. For details, refer to the onupgrade () Description */
Super (context, database_bame, null, 1 );
}

/* Step 2: override oncreate (). If the first time you create a database in the Android system (that is, when getwritabledatabase () or getreadbledatabase () is called later ), oncreate () is called. Here, the database is created (although the database name is filled in the constructor, the database is automatically created in oncreate. Create a table and write the initial data here */
Public void oncreate (sqlitedatabase dB ){
// Create a table: the SQL statement is "CREATE TABLE constants (_ id integer primary key autoincrement, title text, value real);", we can directly useDb.exe cSQL (SQLCommand) to execute SQL languages without return valuesSuch as create, delete, update, insert, and drop.
Db.exe csql ("create table mytable (_ id integer primary key autoincrement, Name text, weight real );");

// The following describes how to add three original data items. If you add, delete, modify, and query tables, we will introduce them in detail later. The following data comes from the gravity table of Android, which is said to be used for sensor management. Android has taken into account the usage of Android phones on the Moon and Mars, programmers sometimes get bored ......
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: override onupgrade (). if the version is higher than the original version, onupgrade () will be called. In this example, we delete the original table and create it based on new requirements */
Public void onupgrade (sqlitedatabase dB, int oldversion, int newversion ){
// Same this timeUse db.exe cSQL (SQLCommand) to execute SQL languages without return values., Delete the table
Db.exe csql ("Drop table if exists mytable ");
Oncreate (db );
}

}

2. associate with the database

Just like creating databases, creating tables, and filling in initial data in MySQL, other operations are generally generated by interacting with users in the activity. Let's recall how we can deal with it in the Linux environment. First, we need to create a connection to the database. The same is true for Android. In addition, when the activity ends, we need to disconnect the connection to release related resources.

Public class chapter22test1 extends listactivity {
Private sqlitedatabase DB = NULL;
Private Cursor cursor = null; // used in interaction with ListView later

Protected void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
// Obtain the SQLite instance that processes the subclass of SQLiteOpenHelper. If it is read-only, getReadableDatabase () can be used. In this example, we will use the SQLiteDatabase instance operation to add, delete, modify, and query data, adopt writable mode.
 Db = (new Chapter22Db (getApplicationContext ())). GetWritableDatabase ();
}

Protected void onDestroy (){
Super. onDestroy ();
// Release the connection to the database
Db. close ();
}
}

3. Operate tables

Table operations can be performed in RAW mode, that is, SQL statements are provided directly, and SQL operations are performed in SQLiteDatabase. The following describes how to add, delete, modify, and query these two methods.

3.1 Add a row of data

When a table is set to be created, db.exe cSQL (SQLCommand) is used to execute an SQL language without return values. This is a RAW method. Another method is provided when raw data is added before, by db. insert ("mytable", "Name", <ContentValues valueS>. The second parameter is special. SQL does not run and adds an empty row. If the second parameter is not set to null, the system will process this case and set the value of the corresponding column to "NULL ".

// RAW method.
Db.ExecSQL("Insert into mytable (Name, Weight) VALUES ('test1', 1.0 );");
// API method, through db. insert ("mytable", "Name", <ContentValues values>, it corresponds to the nouns of the columns in the table and the data in the row.
ContentValues values = new ContentValues (2); // ContentValues has two values.
Values. put ("Name", "Test2"); // a column Name is named, and the data is Test2
Values. put ("Weight", 2.0); // a column name is Weight and the data is 2.0
Db.Insert("Mytable", "Name", values );

3.2 delete a row of data

// RAW Method
Db.ExecSQL("Delete from mytable WHERE Name = 'test1 ';");
// API method, the method is: delete (StringTable, String
WhereClause, String []WhereArgs)
Db.Delete("Mytable", "Name =? ", {" Test1 "});

3.3 update a data row

// RAW Method
Db.ExecSQL("UPDATE mytable SET Weight = 5.0 WHERE Name = 'test1 ';");
// API method, the method is: update (StringTable, 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 query and Cursor

The preceding three operations do not return values, but the query SELECT statement does not. The Cursor is returned. The following two query methods are available:

// RAW mode, with return values, using db. rawQuery (SQL statement) Mode
CursorResult1 = db.RawQuery("SELECT _ id, Name, Weight from mytable order by Name", null );
/API method, with return value, using public Cursor query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy)
String [] columns = {"Name", "Weight "};
String [] name = {"Name "};
CursorResult2 = db.Query("Mytable", columns, "Name =? ", Name, null );

The Cursor is similar to the Iterator interface. For Cursor result, we can read 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();

Using Cursor, we can read the detailed information of the database and combine it with the Android learning notes (20): regression to a simple ListView, we can store data using ArrayList <HashMap <>, because real is not an object, ArrayList <HashMap <String, String> is used for storage. We have the ability to process SQLite data and ListView, so that we can write our Activity.

However, there is actually information in Cursor. We can also use CursorAdapter to process it. Next time we will give an example based on this example using CursorAdapter and ListView.

"Now, Guo Degang has so many things on the Internet. I don't understand why so many people like him because of his ethics issues ?" Jiang Kun gave a speech in Guangzhou on the afternoon of June 23, September 28. Within two hours, he learned how to tease and sing. He clasped the audience's ears to his lips. He also expressed strong doubts about the popularity of Guo Degang's crosstalk. See http://ent.qq.com/a/20111002/41037.htm. How can this be said? This is a Chinese-style vicious and shameless joke. 1. people cannot be criticized in art, and they are morally or even morally arrogant. 2. They cannot point out examples in morality, A hat is built on it. This way of thinking and doing things is still poisonous, frightening, and disturbing. It is more educated than a splash, and more vicious than a splash. Compared with a splash, it is an insult to the splash.

Related Links: My Andriod development articles