Android sqlite3 programming details

The SQLite database is very powerful and easy to use. The general operations of the SQLite database include: create a database, open a database, create a table, add data to the table, delete data from the table, modify data in the table, close the database, delete the specified table, delete the database, and query a certain data in the table. Next we will learn these basic operations separately.

1. Create and open a database

You can use openorcreatedatabase to create and open a database in Android.
Method, because it automatically checks whether the database exists. If yes, it is enabled, but does not exist, a database is created. If the database is created successfully,
Sqlitedatabase object. Otherwise, an exception filenotfoundexception is thrown. Create a name
The database is "examples_06_05.db", and a sqlitedatabase object msqlitedatabase is returned. ,

1. msqlitedatabase = This. openorcreatedatabase ("example_06_05.db", mode_private, null );

2. Create a table

A database can contain multiple tables. Each piece of data is stored in a specified table. To create a table, execsql is used to execute an SQL statement. Execsql can execute most of the SQL statements. Next we will create a table named Table1 with three fields. The Code is as follows:

1. String create_table = "create table Table1 (_ id integer primary key, num integer, Data Text)"; <br/> 2. msqlitedatabase.exe csql (create_table );

3. Add a data entry to the table

You can use the insert method to add data, but the insert method requires that all data be packaged into contentvalues,
Contentvalues is actually a map. The key value is the field name, and the value is the field value. Put through contentvalues
You can put the data in contentvalues and insert it to the table. The specific implementation is as follows:

Java code 1. contentvalues CV = new contentvalues (); <br/> 2. CV. put (table_num, 1); <br/> 3. CV. put (table_data, "Test Data"); <br/> 4. msqlitedatabase. insert (table_name, null, CV); <br/> 5. <br/> 6. // execsql can be used to execute an "insert" SQL statement. The Code is as follows: <br/> 7. string insert_data = "insert into Table1 (_ id, num, data) values (1, 1, 'insert using SQL statements ')"; <br/> 8. msqlitedatabase.exe csql (insert_data );4. delete data from the table

You can use the delete method to delete data. The following code deletes the data whose field "_ id" is equal to 1: 1. msqlitedatabase. delete ("examples_06_05.db", "where_id =" + 0, null); <br/> 2. execute the SQL statement using the execsql method to delete data as follows: <br/> 3. string delete_data = "delete from Table1 where _ id = 1"; <br/> 4. msqlitedatabase.exe csql (delete_data );5. Modify Table Data

If a data error is found after the data is added, you can use the updata method to update the data. The following code modifies the data whose "num" value is 0: 1. contentvalues CV = new contentvalues (); <br/> 2. CV. put (table_num, 3); <br/> 3. CV. put (table_data, "modified data"); <br/> 4. msqlitedatabase. update ("Table1" CV, "num" + "=" + integer. tostring (0), null );6. Shut down the database

It is very important to close the database, which is also easy to forget. The method for closing is simple. You can directly use the close method of sqlitedatabase. The Code is as follows: Msqlitedatabase. Close ();7. delete a specified table

Here we use the execsql Method for implementation. The specific code is as follows: 1. msqlitedatabase.exe csql ("Drop table Table1 ");8. delete a database

To delete a database, use the deletedatabase method. The Code is as follows: 1. This. deletedatabase ("examples_06_05.db ");9. query a data entry in the table

In Android, data is queried through the cursor class. When we use the sqlitedatabase. Query () method, we will get a cursor object, which points to each piece of data. It provides many query methods. The specific method is as follows:

Method description

Move takes the current position as a reference and moves the cursor to the specified position. If the cursor is successfully moved, true is returned. If the cursor fails, false is returned.

Movetoposition: move the cursor to the specified position. If the value is successful, true is returned. If the value is failed, false is returned.

Movetonext moves the cursor forward to a position. If the cursor is successfully moved, true is returned. If the cursor is failed, false is returned.

Movetolast moves the cursor backward to a position. If it succeeds, true is returned. If it fails, false is returned.

Movetofirst moves the cursor to the first row. If the row is successfully moved, true is returned. If the row fails, false is returned.

Isbeforefirst returns whether the cursor points to the first data item

After isafterlast returns whether the cursor points to the last data item



Isclosed returns whether cursor is disabled

Isfirst: Indicates whether cursor points to the first data item.

Islast returns whether cursor points to the last data item

Isnull returns whether the value at the specified position is null

Getcount returns the total number of data items.

Getint returns the specified index data in the current row.

The following code uses cursor to query data in the database:

1. cursor cur = msqlitedatabase. rawquery ("select * from table", null); <br/> 2. If (cur! = NULL) {<br/> 3. if (cur. movetofirst () {<br/> 4. do {<br/> 5. int numcolumn = cur. getcolumnindex ("num"); <br/> 6. int num = cur. getint (numcolumn); <br/> 7 .} while (cur. movetonext (); <br/> 8 .} <br/> 9 .}Finally, I would like to remind you that the sqlitedatabase should be closed in time. Otherwise, a sqliteexception may be thrown. From: http://byandby.javaeye.com/blog/833964