Data storage for Android development II: SQLite database storage [Source Code download for free]

SQLite database storage method for Android Development

Introduction:SQLite is a very popular embedded database that supports SQL queries and uses only a small amount of memory. Android integrates SQLite at runtime, so every android application can use the SQLite database. Using SQLite is quite simple for developers familiar with SQL. Yes, because JDBC is not suitable for memory-limited devices such as mobile phones, Android Developers need to learn new APIs to use SQLite. This article mainly explains SQLite
Basic usage in the android environment.

 

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.

 

Android integrates SQLite Database

Android integrates SQLite at runtime, so every android application 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.

(The above content is taken from the Internet)

Next we will learn how to use SQLite in Android development.

 

Let's first create a database:

Private Static class databasehelper extends sqliteopenhelper {Private Static int version = 1; Public databasehelper (context, string name, cursorfactory factory, int version) {super (context, name, factory, version);} public databasehelper (context, string name) {This (context, name, version);} public databasehelper (context, string name, int Version) {This (context, name, null, version);} // this method is called when the database is generated for the first time, generally, the Data Table @ overridepublic void oncreate (sqlitedatabase dB) is generated in this method {// sqlstring SQL = "CREATE TABLE DBK (Title Text No null, body text no null) "; log. I ("DBK: createdb", sql1_mongodb.exe csql (SQL); dB. close () ;}// the Android system automatically calls this method when the database needs to be upgraded, generally, here we will delete the table and create a new table. @ overridepublic void onupgrade (sqlitedatabase dB, int oldversion, int newversion ){}}

 

Databasehelper is a helper class that generates a database and manages the database version,

When the getwritabledatabase () and getreadabledatabase () methods of this class are called in the program for write and read operations, if there is no database at that time, the android system will automatically create a database.

 

I wrote a little thing about adding, deleting, modifying, and querying databases. Let's take a look at the specific process as an example:

The interface is as follows:

 

Insert

The code for inserting data is as follows:

Private void insertitem () {databasehelper = new databasehelper (sqliteactivity. this, "DBK"); sqlitedatabase DB = databasehelper. getwritabledatabase (); string sql1 = "insert into DBK (title, body) values ('first data', 'android has a lot of money tour ')"; string sql2 = "insert into DBK (title, body) values ('second data', 'android development interesting ')"; try {log. I ("DBK: sql1", sql1); log. I ("DBK: sql2", sql2)mongodb.execsql(sql1)mongodb.exe csql (sql2); settitle ("data inserted successfully");} catch (exception e) {settitle ("data inserted failed ");} DB. close ();}

 

DB. Close (); is to close the database. If you do not use the database in a short period of time after you use it, we recommend that you close the database to save resources.

 

Query

I added a dialog box in the query to facilitate understanding,

The Code is as follows:

Btn2 = (button) findviewbyid (R. id. btn2); btn2.setonclicklistener (New onclicklistener () {@ overridepublic void onclick (view v) {databasehelper helper = new databasehelper (sqliteactivity. this, "DBK"); // defines sqlitedatabase DB = helper. getreadabledatabase (); string Col [] = {"title", "body"};/** the first parameter for executing the database query function: Table name second parameter: information of the column to be queried * third parameter: condition, which is equivalent to the where part of SQL. If you want to query it, It is null * Fourth parameter: if there is "?" in the third parameter" Symbol, then the string character of the fourth parameter will replace the third "?" PART * fifth parameter: defines whether the queried data is grouped, which is equivalent to SQL group by. null is not grouped. * Sixth parameter: equivalent to the seventh parameter of SQL having: it is equivalent to ordey by of SQL to describe whether to sort data. If it is null, It is not sorted */cursor cusor = dB. query ("DBK", Col, null, null); If (cusor. movetonext () {String title = cusor. getstring (cusor. getcolumnindex ("title"); string body = cusor. getstring (cusor. getcolumnindex ("body"); Final integer num = cusor. getcount (); settitle (title); // a dialog box is displayed to display the first data Dialog (sqliteactivity. this, title, body, num ). show ();} else {// if there is no data in the database, the system prompts new alertdialog. builder (sqliteactivity. this ). settitle ("no data in database "). setpositivebutton ("OK", new dialoginterface. onclicklistener () {@ overridepublic void onclick (dialoginterface dialog, int which ){}}). create (). show ();} dB. close ();}});

 

I wrote a detailed comment, so I don't need to explain anything here? The code in the prompt box for successful query will not be posted here. I will pack it in the source code.

 

Modify

The modified code is as follows, and the comments have been written in detail for everyone:

Btn3 = (button) findviewbyid (R. id. btn3); btn3.setonclicklistener (New onclicklistener () {@ overridepublic void onclick (view v) {contentvalues ARGs = new contentvalues (); args. put ("body", "modified! "); String [] Title = new string [] {" second data entry "}; databasehelper helper = new databasehelper (sqliteactivity. this, "DBK"); // defines the write database operation sqlitedatabase DB = helper. getwritabledatabase ();/** modify the first parameter of the database: The second parameter of the database table name: the field to be modified and the content to be updated * The third parameter is: condition, it is equivalent to the where section in SQL *. The fourth parameter is: if the third parameter contains "?"" Symbol, then the string character of the fourth parameter will replace the third "?" PART */DB. Update ("DBK", argS, "Title =? ", Title); DB. Close ();}});

 

 

Delete

The delete operation code is as follows:

Btn4 = (button) findviewbyid (R. id. btn4); btn4.setonclicklistener (New onclicklistener () {@ overridepublic void onclick (view v) {try {databasehelper helper = new databasehelper (sqliteactivity. this, "DBK"); // defines the write database operation sqlitedatabase DB = helper. getwritabledatabase ();/** the first parameter in the delete operation is: The second parameter in the database table name is: condition, which is equivalent to the where part in SQL * The third parameter is: if the third parameter contains "?" Symbol, then the string character of the fourth parameter will replace the third "?" PART */DB. delete ("DBK", "Title = 'second data'", null); settitle ("delete record with title as second data"); dB. close () ;}catch (exception e ){}}});

 

If you want to do Android development, you will certainly store data on Android. SQLite is a good choice. This article mainly introduces such operations as operating the SQLite database. After reading this article, you can learn basic operations on the SQLite database on Android to further learn about Android development.

You can try again and add a prompt for the modification? Test whether the oncreate method in databasehelper is executed every time you perform operations on the database? We can't add db.exe csql ("Drop table if exists DBK") in oncreate? Can you share your test results?

[Click here to download the source code]