Android SQLite Learning Guide

I,SQLite Introduction

 

On the Android platform, an embedded relational database-SQLite is integrated. sqlite3 supports null, integer, real (floating point number), text (string text), and blob (binary object) data types, although it supports only five types, sqlite3 actually accepts data types such as varchar (N), char (N), decimal (P, S, it is only converted to the corresponding five data types during operation or storage. The biggest feature of SQLite is that you can save any type of data to any field, regardless of the Data Type declared in this column. For example, you can store strings in integer fields, floating point numbers in Boolean fields, or date values in numeric fields. But there is one exception: the field defined as integer primary key can only store 64-bit integers. When saving data other than Integers to this field, an error will occur. In addition, when parsing the create table statement, SQLite ignores the data type information following the field name in the create table statement.

 

II,Curd of SQLite

Android provides a class named sqlitedatabase, which encapsulates APIs for database operations. This class can be used to create, query, and update data) and delete operations (crud ). For sqlitedatabase learning, we should master the execsql () and rawquery () methods. The execsql () method can be used to execute SQL statements with changing behaviors such as insert, delete, update, and create table. The rawquery () method can be used to execute select statements. Sqlitedatabase also provides operation methods for adding, deleting, updating, and querying: insert (), delete (), update (), and query (). These methods are actually used by cainiao who are not familiar with SQL syntax.ProgramYou can directly use the execsql () and rawquery () Methods to add, delete, update, and query data.

 

III,SQLite Transaction Management

Use the begintransaction () method of sqlitedatabase to start a transaction. When the program executes the endtransaction () method, it checks whether the transaction flag is successful. If it is successful, the transaction is committed, otherwise, roll back the transaction. When an application needs to commit a transaction, it must use the settransactionsuccessful () method to set the transaction flag to successful before the program executes the endtransaction () method. If the settransactionsuccessful () method is not called, transactions are rolled back by default.

 

III,Create and update data tables using SQLite

If the application uses the SQLite database, you need to create the database table structure used by the application and add some initialization records when using the software for the first time. In addition, when upgrading the software, you also need to update the data table structure. In the Android system, a class named sqliteopenhelper is provided for us to manage database versions. This class is an abstract class that must be inherited before it can be used. To manage database versions, the sqliteopenhelper class has two important methods: oncreate (sqlitedatabase dB) and onupgrade (sqlitedatabase dB, int oldversion, int newversion)

 

When the getwritabledatabase () or getreadabledatabase () method of sqliteopenhelper is called to obtain the sqlitedatabase instance used to operate the database, if the database does not exist, the android system automatically generates a database and then calls oncreate () method. The oncreate () method is called only when the database is generated for the first time. In the oncreate () method, you can generate the database table structure and add the initialization data used by some applications. The onupgrade () method is called when the database version changes. The database version is controlled by the programmer. Assume that the current database version is 1, after modifying the structure of the database table, you need to upgrade the software to update the database table structure on your mobile phone, you can set the original database version to 2 (or another value) and update the table structure in the onupgrade () method. When the number of software version upgrades is large, you can use the onupgrade () method to determine based on the original number and target version number, and then update the table structure and data.

 

Both the getwritabledatabase () and getreadabledatabase () methods can obtain a sqlitedatabase instance used to operate the database. However, the getwritabledatabase () method opens the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write data. If the getwritabledatabase () method is used, an error occurs. The getreadabledatabase () method first opens the database in read/write mode. If the disk space of the database is full, it will fail to be opened. When the opening fails, it will continue to attempt to open the database in read-only mode.

 

IV,View and manage SQLite databases in Android

You can use the Eclipse plug-in ddms in Android or the ADB tool in the android toolkit. The SQLite database in the android project is located at/data/Project package/Databases.

Find the directory shown above in file explorer in ddms and export the corresponding database directly.

ADB viewing data blocks:

Run "1" and switch to "android-sdkdirectory". Run "adb.exe" and add the shell parameter. "#" indicates that the shell command mode (ADB shell) is enabled. Note that "ADB" can only access the shell when the android simulator is running.

2) shell commands remember the two basic commands LS and CD. Similar to the Dir and Cd in the Windows Command Prompt line, these commands indicate listing the files in the current directory and entering the specified directory. After understanding the two commands, you can find the data/Project package name/Databases.

3) Find the database file and use the SQLite management tool to perform the operation. Type "sqlite3"Database Name"Then we enter the qlite management mode.

Note: sqlite3.exe is included in androidsdk. this is SQLite's official management tool, which is a command line tool. For ease of use, register the path to the system environment variable path, that is, add % android_home % to the path to run sqlite3. You can directly open the SQLite management tool.

4) SQLite Database Management

By default, the SQLite command line tool uses the; concluding remarks. Therefore, if only one line of statements is required, add; At the end, or type; In the next line, the SQLite command will be executed.

SQLite Common commands:

. Tables -- view the table list of the database

. Exit -- exit the SQLite command line

Other commands can be used at any time. Help to view help. The SQL command can be executed directly on this command line:

V,CodeCase

Case 1:

 Public   Class Sqlitehelper Extends Sqliteopenhelper {

 Private   Static  Final String database_name = "xiangqiao_db ";
 Private   Static   Final String table_name = "xiangqiao_table ";
 Private   Static   Final   Int Version = 1;

 Public Sqlitehelper (context, string name, cursorfactory factory,
 Int Version ){
 Super (Context, name, factory, version );
}

 Public Sqlitehelper (context ){
 This (Context, database_name, Null , Version );

}


@ Override
 Public   Void Oncreate (sqlitedatabase ){
 //  Create a data table  
 String SQL = "CREATE TABLE" + table_name + "(ID integer primary key autoincrement, name varchar (20), content text, time date )";
Sqlitedatabase.exe csql (SQL );
Sqlitedatabase. Close ();
}

@ Override
 Public   Void Onupgrade (sqlitedatabase dB, Int Oldversion, Int Newversion ){
 //  Todo auto-generated method stub  
 
}
}


 Public   Class Dbdao {

Sqlitehelper;

 Public Dbdao (context ){
 This . Sqlitehelper = New Sqlitehelper (context );
}

 Public String exequery (string SQL ){
Sqlitedatabase = sqlitehelper. getreadabledatabase ();
Cursor cursor = sqlitedatabase. rawquery (SQL,Null );
Stringbuffer buffer = New Stringbuffer ();
 While (Cursor. movetonext ()){
Buffer. append (cursor. getint (0). append ("")
. Append (cursor. getstring (1). append ("")
. Append (cursor. getstring (2). append ("")
. Append (cursor. getstring (3). append ("");
}
Cursor. Close ();
Sqlitedatabase. Close ();
Sqlitehelper. Close ();

Return Buffer. tostring ();
}

 Public   Void Exedo (string SQL ){
Sqlitedatabase = sqlitehelper. getwritabledatabase ();
Sqlitedatabase.exe csql (SQL );
Sqlitedatabase. Close ();
Sqlitehelper. Close ();
}
}

 
 

 Public   Class Mainactivity Extends Activity {

 Public   Void Dodatabase (){
Dbdao = New Dbdao (getapplicationcontext ());
 //  Add  
 Dbdao.exe do ("insert into xiangqiao_table values (null, 'xiangqiao', 'xiangqiao content', '2017-11-10 12:10:11 ')");
 //  Change  
 Dbdao.exe do ("Update xiangqiao_table set name = '000000' where id = 1 ");
 // Query  
 String resultmongodbdao.exe query ("select * From xiangqiao_table order by id desc ");
 //  Delete  
 Dbdao.exe do ("delete from xiangqiao_table where id = 2 ");
}

Case 2:

View code

 Public   Class Dbhelper Extends Sqliteopenhelper {

 Private  Final   Static String database_name = "sec_db ";
 Private   Final   Static   Int Database_version = 1;
 Private   Final   Static String table_name = "sec_pwd ";
 Public   Final   Static String field_id = "_ id ";
 Public  Final   Static String field_title = "sec_title ";

 Public Dbhelper (context)
{
 Super (Context, database_name, Null , Database_version );
}

@ Override
 Public   Void Oncreate (sqlitedatabase dB ){
 //  Todo auto-generated method stub 
 String SQL = "CREATE TABLE" + table_name + "(" + field_id + "integer primary key autoincrement ,"
+ Field_title + "text );";
Db.exe csql (SQL );


}

@ Override
 Public   Void Onupgrade (sqlitedatabase dB, Int Oldversion, Int Newversion ){
 //  Todo auto-generated method stub  
 String SQL = "Drop table if exists" + table_name;
Db.exe csql (SQL );
Oncreate (db );
}

 Public Cursor select ()
{
Sqlitedatabase DB = This . Getreadabledatabase ();
Cursor cursor = dB. Query (table_name, Null , Null , Null , Null , Null , "_ Id DESC ");
 Return Cursor;
}

 Public   Long Insert (String title)
{
Sqlitedatabase DB = This . Getwritabledatabase ();
Contentvalues CV = New Contentvalues ();
Cv. Put (field_title, title );
 Long Row = dB. insert (table_name, Null , CV );
 Return Row;
}

 Public   Void Delete ( Int ID)
{
Sqlitedatabase DB = This . Getwritabledatabase ();
String where = field_id + "=? ";
String [] wherevalue = {INTEGER. tostring (ID )};
DB. Delete (table_name, where, wherevalue );
}

 Public   Void Update ( Int ID, String title)
{
Sqlitedatabase DB = This . Getwritabledatabase ();
String where = field_id + "=? ";
String [] wherevalue = {INTEGER. tostring (ID )};
Contentvalues CV = New Contentvalues ();
Cv. Put (field_title, title );
DB. Update (table_name, CV, where, wherevalue );
}
}