The use of embedded relational SQLite database storage data
On the Android platform, an embedded relational database is integrated--sqlite,sqlite3 supports NULL, INTEGER, real (floating-point numbers), text (string literals), and BLOB (binary object) data types, although it supports only five types, In practice, however, Sqlite3 also accepts data types such as varchar (n), char (n), decimal (p,s), except that it is converted to the corresponding five types of data at the time of operation or save. the biggest feature of SQLite is that you can save all types of data to any field without worrying about what the data type of the field declaration is. For example, you can hold a string in an integer-type field, or hold a floating-point number in a Boolean field, or hold a date-type value in a character field. One exception: A field defined as an integer PRIMARY key can store only 64-bit integers, and an error occurs when you save data other than integers to such a field. Also, when you write a Create TABLE statement, you can omit the data type information that follows the field name, such as the following statement, where you can omit the type information of the Name field:
CREATE TABLE person (PersonID Integer primary key autoincrement, name varchar (20))
SQLite can parse most standard SQL statements, such as:
Copy Code code as follows:
Query statement: SELECT * from table name where conditional clause GROUP by group to have ... order by sort clause
For example: SELECT * FROM person
SELECT * FROM person ORDER BY id DESC
Select name from person GROUP by name has COUNT (*) >1
Paging SQL is similar to MySQL, the following SQL statement gets 5 records, skipping the previous 3 records
SELECT * From account limit 5 offset 3 or select * From account limit 3,5
Insert statement: INSERT INTO Table name (field list) VALUES (value list). such as: INSERT into person (name, age) VALUES (' Preach wisdom ', 3)
UPDATE statement: Update table name set field name = value WHERE Condition clause. such as: Update person set name= ' pass Wisdom ' where id=10
Delete statement: Delete from table name WHERE Condition clause. such as: delete from person where id=10
Ii. using Sqliteopenhelper to manage database versioning
When we are writing database application software, the question needs to be considered: because the software we develop may be installed on many users ' phones, and if the application uses the SQLite database, we must create a database table structure to use and add some initialization records when the user first uses the software. In addition, the data table structure needs to be updated when the software is upgraded. So how can we implement a database table that automatically creates the application needs on the user's phone when the user first uses or upgrades the software? Never let us create database tables manually on every phone that needs to have this software installed? Because this requirement is faced by every database application, the Android system provides us with an abstract class called Sqliteopenhelper that must be inherited in order to be used, and it is through the management of the database version that the preceding requirements are implemented.
To manage the database version, the Sqliteopenhelper class provides two important methods, namely, OnCreate (Sqlitedatabase db) and Onupgrade (sqlitedatabase db, int oldversion, int newversion),The former is used to generate database tables when the software is first used, which updates the database table structure when upgrading the software. When the Sqliteopenhelper getwritabledatabase () or Getreadabledatabase () method is invoked to obtain the Sqlitedatabase instance used to manipulate the database, if the database does not exist, The Android system automatically generates a database, then calls the OnCreate () method, and the OnCreate () method is invoked when the database is first built, and in the OnCreate () method, the database table structure is generated and some initialization data used by the application is added. The Onupgrade () method is invoked when the version of the database changes. Generally in the software upgrade to change the version number, and the version of the database is controlled by the programmer, assuming that the database is now 1 version, due to changes in the Business database table structure, this time need to upgrade the software, Upgrade the software to update the user's phone database table structure, in order to achieve this, you can set the original database version to 2 (with a classmate asked to set to 3 lines?) Of course, if you like, set to 100 also line, and in the Onupgrade () method to implement the table structure update. When the software version of the upgrade more frequently, then in the Onupgrade () method can be based on the original number and target version number to judge, and then make the corresponding table structure and data updates.
Both the Getwritabledatabase () and Getreadabledatabase () methods can obtain a sqlitedatabase instance that is used to manipulate the database. However, the Getwritabledatabase () method opens the database read-write, and once the database is full of disk space, the database is read-only and cannot be written, and if you use Getwritabledatabase () to open the database, an error occurs. The Getreadabledatabase () method opens the database read-write, and if the database has full disk space, it will fail, and will continue to attempt to open the database as read-only when the failure is turned on.
Note: Getwritabledatabase (), getreadabledatabase the difference is that when the database is full, the former will be the error, call the latter does not, so if not update the database, it is best to call the latter to obtain the database connection.
Code:
Copy Code code as follows:
public class Databasehelper extends Sqliteopenhelper {
Class is not instantiated, is a parameter that cannot be used as a parent class constructor, and must be declared as a static
Private static final String name = "Ljqdb"; Database name
private static final int version = 1; Database version
Public Databasehelper {
The third parameter, cursorfactory, specifies that the factory class that obtains a cursor instance when executing the query is set to NULL, representing the factory class using the system default
Super (context, name, null, version);
}
@Override
public void OnCreate (Sqlitedatabase db) {
Db.execsql ("CREATE TABLE IF not EXISTS person" (
PersonID Integer PRIMARY key autoincrement, name varchar (age integer) ");
}
@Override
public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {
Db.execsql ("ALTER TABLE person ADD phone VARCHAR () NULL"); Add a column to the table
drop table IF EXISTS person deletes tables
}
}
In the actual project development, when the database table structure is updated, the user should avoid the loss of data stored in the database.
Iii. using Sqlitedatabase to operate the SQLite database
Android provides a class called Sqlitedatabase that encapsulates the API for manipulating databases that can be used to add (Create), query (Retrieve), update (update), and delete (delete) Operations (these operations are referred to as CRUD). for sqlitedatabase Study, we should focus on mastering the Execsql () and Rawquery () methods. The Execsql () method executes SQL statements that have changed behavior, such as inserts, deletes, update, and create table; the Rawquery () method executes the SELECT statement.
Examples of the use of the Execsql () method:
Copy Code code as follows:
Sqlitedatabase db = ...;
Db.execsql ("INSERT into person (name, age) VALUES (' Lin Yi-chin ', 24)");
Db.close ();
Executing the above SQL statement adds a record to the person table. In practice, the "Lin Yi-chin" parameter values in the statements are provided by the user input interface, and if the user input content is spelled into the INSERT statement above, when the user input contains single quotes, There is a syntax error in the SQL statement that is spelled out by the group. To solve this problem, you need to escape the single quotation marks, which translates single quotes into two single quotes. Sometimes users will also input like "&" These special SQL symbols, in order to ensure that the group spelled the SQL statement correctly syntax, must be in the SQL statements of these special SQL symbols are escaped, obviously, for each of the SQL statements do this kind of processing work is more cumbersome. The Sqlitedatabase class provides an overloaded Execsql (String sql, object[] Bindargs) method that solves the problem mentioned earlier because this method supports the use of placeholder arguments (?).Examples of use are as follows:
Copy Code code as follows:
Sqlitedatabase db = ...;
Db.execsql ("INSERT into person (name, age) VALUES (?,?)", New object[]{"Intelligence Podcast", 4});
Db.close ();
The first parameter of the Execsql (String sql, object[] Bindargs) method is the SQL statement, the second parameter is the value of the placeholder parameter in the SQL statement, and the order of the parameter values in the array corresponds to the position of the placeholder.
The Sqlitedatabase rawquery () is used to execute the SELECT statement, using the following example :
Copy Code code as follows:
Sqlitedatabase db = ...;
Cursor Cursor = Db.rawquery ("SELECT * from person", NULL);
while (Cursor.movetonext ()) {
int PersonID = cursor.getint (0); Gets the value of the first column, with the index of the first column starting at 0
String name = cursor.getstring (1);//Get the value of the second column
int age = Cursor.getint (2);//Get the value of the third column
}
Cursor.close ();
Db.close ();
The first parameter of the Rawquery () method is the SELECT statement, the second parameter is the value of the placeholder parameter in the SELECT statement, and the parameter can be set to NULL if the SELECT statement does not use a placeholder. Examples of SELECT statements with placeholder parameters are as follows:
Copy Code code as follows:
Cursor Cursor = Db.rawquery ("select * from person where name is like?") And age=? ", new string[]{"% Lin Yi-chin% "," 4 "});
Cursor is a result set cursor that is used to randomly access the result set, and if you are familiar with JDBC, cursor is similar to the ResultSet effect in JDBC. You can use the MoveToNext () method to move the cursor from the current row to the next line, or True if you have moved past the last row of the result set and the result is false. In addition cursor also has a common movetoprevious () method (used to move the cursor from the current row to the previous line, if it has moved past the first row of the result set, the return value is false, otherwise true), Movetofirst () method, which is used to move the cursor to the first row of the result set, true if the result set is empty, the return value is false, or true) and the Movetolast () method (used to move the cursor to the last row of the result set, if the result set is empty, and the return value is false).
In addition to the Execsql () and Rawquery () methods described earlier, Sqlitedatabase specifically provides actions for adding, deleting, updating, querying: Insert (), delete (), update (), and query (). These methods are actually for those who are unfamiliar with SQL syntax, for those familiar with SQL syntax, directly using the Execsql () and Rawquery () methods to execute SQL statements can complete the data add, delete, update, query operations.
The Insert () method is used to add data, and the data for each field is stored using contentvalues. Contentvalues is similar to map, which provides the put (string key, Xxx value) and getasxxx (string key) methods for accessing data, and key is the field name, and value is the field value. XXX refers to a variety of commonly used data types, such as: String, Integer, and so on.
Copy Code code as follows:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Contentvalues values = new Contentvalues ();
Values.put ("name", "Lin Yi-chin");
Values.put ("Age", 24);
Long rowID = Db.insert ("person", null, values);//Returns the line number of the newly added record, regardless of the primary key ID
Regardless of whether the third parameter contains data, executing the Insert () method inevitably adds a record, and if the third argument is empty, a record is added that has a null field value other than the primary key. Inside the Insert () method actually completes the addition of the data by constructing the Insert SQL statement, and the second parameter of the Insert () method is used to specify the name of the null field. I'm sure you'll be wondering about this parameter. Is this: if the third parameter values are null or the number of elements is 0, because the Insert () method requires that a record be added with a null value other than the primary key, the INSERT statement must be given a field name in order to satisfy the needs of the SQL syntax. For example, insert into person (name) values (NULL), if no field name is given, the INSERT statement becomes this way: INSERT into person () values (), which obviously does not satisfy standard SQL syntax. For field names, it is recommended that you use a field other than the primary key, and that the primary key field value will not be null after you execute an INSERT statement that resembles insert into person (PersonID) VALUES (NULL), if you use an integer type of primary key field. If the third argument values are not null and the number of elements is greater than 0, you can set the second argument to null.
Use of the Delete () method:
Copy Code code as follows:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Db.delete ("Person", "personid<?", New string[]{"2"});
Db.close ();
The code above is used to delete records PersonID less than 2 from the person table.
Use of the Update () method:
Copy Code code as follows:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Contentvalues values = new Contentvalues ();
Values.put ("name", "Lin Yi-chin");//key is a field name and value is
Db.update ("person", Values, "personid=", New string[]{"1"});
Db.close ();
The code above is used to change the value of the name field of a record in the person table to PersonID equal to 1 to "Lin Yi-chin".
The query () method actually splits the SELECT statement into several components and then acts as an input parameter to the method:
Copy Code code as follows:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Cursor Cursor = db.query ("Person", new string[]{"Personid,name,age"}, "name like?", New string[]{"%ljq%"}, NULL, NULL, "p Ersonid desc "," 1,2 ");
while (Cursor.movetonext ()) {
int PersonID = cursor.getint (0); Gets the value of the first column, with the index of the first column starting at 0
String name = cursor.getstring (1);//Get the value of the second column
int age = Cursor.getint (2);//Get the value of the third column
}
Cursor.close ();
Db.close ();
The code above is used to find the name field from the person table that contains "pass-wise" records, the matching records are sorted in descending order by PersonID, and the sorted results are skipped over the first record, and only 2 records are obtained.
Query (table, columns, selection, Selectionargs, GroupBy, having, by, limit) method The meaning of each parameter:
Table: List name. Corresponds to the section following the SELECT statement from the keyword. If you are a multiple table union query, you can separate the two table names with commas.
Columns: The name of the column to query. Corresponds to the part of the SELECT statement following the SELECT keyword.
Selection: The query condition clause, which is equivalent to the section after the SELECT statement where keyword, allows the placeholder "?" in the conditional clause.
Selectionargs: Corresponds to the value of the placeholder in the selection statement, the position of the value in the array and the position of the placeholder in the statement must be the same, otherwise there will be an exception.
GroupBy: Equivalent to the section following the SELECT statement GROUP BY keyword
Having: the equivalent of the part of a SELECT statement after having a keyword
By: Equivalent to the section following the SELECT statement order by keyword, such as: PersonID DESC, age ASC;
Limit: Specifies the offset and the number of records fetched, which is equivalent to the portion following the Limit keyword of the SELECT statement.
Iv. use Sqliteopenhelper to obtain sqlitedatabase instances for manipulating the database
public class Databasehelper extends Sqliteopenhelper {
Private static final String name = "Ljqdb"; Database name
private static final int version = 1; Database version
...... Slightly
}
public class Helloactivity extends activity {
@Override public void OnCreate (Bundle savedinstancestate) {
......
Button button = (button) This.findviewbyid (R.id.button);
Button.setonclicklistener (New View.onclicklistener () {
public void OnClick (View v) {
Databasehelper databasehelper = new Databasehelper (helloactivity.this);
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Db.execsql ("INSERT into person (name, age) VALUES (?,?)", New object[]{"Lin Yi-chin", 4});
Db.close ();
}});
}
}
The first time the getwritabledatabase () or Getreadabledatabase () method is invoked, Sqliteopenhelper caches the current Sqlitedatabase instance. The Sqlitedatabase instance normally maintains the database open, so call the close () method to release the resource when you no longer need the sqlitedatabase instance. Once the Sqlitedatabase instance is cached, multiple calls to the Getwritabledatabase () or Getreadabledatabase () methods get the same instance.
V. Use TRANSACTION Operations SQLite database
Use the Sqlitedatabase BeginTransaction () method to open a transaction that checks whether the transaction's flag is successful if the program executes to the Endtransaction () method, if the program executes to Endtransaction () The Settransactionsuccessful () method was previously invoked to set the transaction's flag for success commits the transaction and rolls back the transaction if the Settransactionsuccessful () method is not invoked.
Examples of use are as follows:
Copy Code code as follows:
Sqlitedatabase db = ...;
Db.begintransaction ()//Start transaction
try {
Db.execsql ("INSERT into person (name, age) VALUES (?,?)", New object[]{"Lin Yi-chin", 4});
Db.execsql ("Update person set name=?") Where personid=? ", New object[]{" abc ", 1});
Db.settransactionsuccessful ()//Calling this method commits the current transaction when execution to Endtransaction (), and rolls back the transaction if this method is not called
finally {
Db.endtransaction ()//is determined by the flag of the transaction to commit the transaction or to roll back the transaction
}
Db.close ();
the above two SQL statements are executed in the same transaction.
