Storing data using an embedded relational SQLite database

On the Android platform, an embedded relational database is integrated-sqlite,sqlite3 supports NULL, INTEGER, REAL (floating-point number), text (string literal), and blob (binary object) data types, although it supports only five types, In fact, Sqlite3 also accepts data types such as varchar (n), char (n), and Decimal (p,s), except that they are converted to the corresponding five data types when they are operated or saved. 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 a field of type Integer, or hold a floating-point number in a Boolean field, or hold a date-type value in a character field. There is one exception: a field defined as an integer PRIMARY key can store only 64-bit integers, and when you save data other than integers to such a field, an error occurs. In addition, when writing the CREATE table statement, you can omit the data type information that follows the field name, such as the following statement you can omit the type information for the Name field: CREATE table person (PersonID integer PRIMARY key AutoIncrement, name varchar) SQLite can parse most of the standard SQL statements, such as: query statement: SELECT * from table name where conditional clause GROUP BY group words have ... order by Sort clauses such as: SELECT * FROM person        SELECT * from the person ORDER by ID DESC  &NB sp;     select name from person GROUP by name has COUNT (*) >1 page sql is similar to MySQL, the following SQL statement gets 5 records, Skip 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 (' Wisdom ', 3) UPDATE statement: The Update table name set field name = value WHERE Condition clause. such as: Update person set name= ' wisdom ' where id=10 DELETE statement: Delete from table name where conditional clause. For example: Delete from person  where id=10  gets the ID value since the growth of the added record: SELECT Last_insert_rowid ()   Use Sqliteopenhelper to get the Sqlitedatabase instance used to manipulate the database   This is Dbopenhelper class inheritance sqliteopenhelper 

Package Com.example.service;import Android.content.context;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.sqliteopenhelper;public Class Dbopenhelper extends Sqliteopenhelper {public Dbopenhelper (context context) {Super (context, "example.db", NULL, 2) ;//Under < package >/databases/} @Overridepublic void OnCreate (Sqlitedatabase db) {//database is called Db.execsql each time it is created ("Create TABLE Person (PersonID Integer primary key autoincrement, name varchar (20)) "); @Overridepublic void Onupgrade (sqlitedatabase db, int arg1, int arg2) {//database version changed is called Db.execsql ("ALTER TABLE person ADD Phone VARCHAR (+) NULL "); Add a column to the table//drop table IF EXISTS person to delete tables}}

The following code completes the operation of the increase and deletion check.

Package Com.example.service;import Java.util.arraylist;import Java.util.list;import Com.example.domain.Person; Import Android.content.context;import Android.database.cursor;import android.database.sqlite.SQLiteDatabase; public class Personservice {private Dbopenhelper dbopenhelper;public Personservice (context context) {This.dbopenhelper = new Dbopenhelper (context);} /** * New record * @param person */public void Save (person person) {sqlitedatabase db=dbopenhelper.getwritabledatabase (); Db.execsql ("INSERT into person (name, phone) VALUES (?,?)", New Object[]{person.getname (), Person.getphone ()}); /** * Delete record * @param person */public void Delete (Integer id) {sqlitedatabase db=dbopenhelper.getwritabledatabase (); Db.execsql ("Delete from person where personid=?", New Object[]{id}); /** * Update record * @param person */public void Update (person person) {sqlitedatabase db=dbopenhelper.getwritabledatabase (); Db.execsql ("Update person set name=?", phone=?) Where personid=? ", New Object[]{person.getname (), Person.getphone (), Person.getid ()});} /** * Find record * @param ID record ID * @return */public person find (Integer ID) {sqlitedatabase db=dbopenhelper.getreadabledatabase ( ); Cursor cursor=db.rawquery ("select * from person where personid=?", New String[]{id.tostring ()}); if (Cursor.movetofirst ( ) {int Personid=cursor.getint (Cursor.getcolumnindex ("PersonID")); String name=cursor.getstring (Cursor.getcolumnindex ("name")); String phone=cursor.getstring (Cursor.getcolumnindex ("Phone")); return new person (PersonID, name, phone);} Cursor.close (); return null;} /** * Paging Get record * @param offset skips the number of records in front * @param maxresult How many records are displayed per page * @return */public list<person> getscrolldata (in T offset,int maxresult) {sqlitedatabase db=dbopenhelper.getreadabledatabase (); List<person> persons=new arraylist<person> (); Cursor cursor=db.rawquery ("SELECT * from Person ORDER by PersonID ASC limit?,?", New string[]{string.valueof (offset), stri Ng.valueof (Maxresult)}), while (Cursor.movetonext ()) {int Personid=cursor.getint (Cursor.getcolumnindex ("PersOnid ")); String name=cursor.getstring (Cursor.getcolumnindex ("name")); String phone=cursor.getstring (Cursor.getcolumnindex ("Phone"));p Ersons.add (new person (PersonID, name, phone));} Cursor.close (); return persons;} /** * Get records total number of bars * @return */public long GetCount () {sqlitedatabase db=dbopenhelper.getreadabledatabase (); Cursor cursor=db.rawquery ("SELECT count (*) from person", null); Cursor.movetofirst (); long Result=cursor.getlong (0); Cursor.close (); return result;}}

You can also use the system to insert, delete, Udate and other methods to implement additions and deletions

1. 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 access to the data corresponding to the put (string key, Xxx value) and getasxxx (String key) method,  key is the field name, value is the field value, XXX refers to a variety of commonly used data types, such as: String, Integer, and so on. Sqlitedatabase db = Databasehelper.getwritabledatabase (); Contentvalues values = new Contentvalues () values.put ("name", "Preach Wisdom Podcast"), Values.put ("Age", 4); 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, the execution of the Insert () method inevitably adds a record, and if the third argument is null, adds a record with null for the field value other than the primary key. 2. Use of the Delete () method: Sqlitedatabase db = Databasehelper.getwritabledatabase ();d b.delete ("person", "personid<?", New string[]{"2"});d B.close (); The above code is used to remove records PersonID less than 2 from the person table. 3. Use of the update () method: Sqlitedatabase db = Databasehelper.getwritabledatabase (); Contentvalues values = new Contentvalues () values.put ("name", "Preach Intelligence Podcast"),//key is the field name, value is db.update ("person", values, " Personid=? ", New string[]{" 1 "});d B.close (); The above code is used to change the value of the name field of the record PersonID equals 1 in the person table to" Preach Intelligence podcast ". 4. The query () method actually splits the SELECT statement into a number of components and then acts as an input parameter to the method: Sqlitedatabase db = daTabasehelper.getwritabledatabase (); cursor cursor = db.query ("Person", new string[]{"Personid,name,age"}, "name like", New string[]{"% smart%"}, NULL, NULL, "PE Rsonid desc ",", "); while (Cursor.movetonext ()) {         int PersonID = Cursor.getint (0); Gets the value of the first column, the index of the first column starts at 0         String name = cursor.getstring (1);//Gets the value of the second column          int age = Cursor.getint (2);//Gets the value of the third column}cursor.close ();d b.close (); The above code is used to find the name field from the person table contains "wisdom" records, matching records sorted in descending order of PersonID, the results of the sorting is skipped the first record, only 2 records are obtained. Query (table, columns, selection, Selectionargs, GroupBy, have, to-do, limit) methods The meaning of each parameter: Table: Table name. Corresponds to the part of the SELECT statement following the FROM keyword. If you are a multi-table union query, you can separate the two table names with a comma. Columns: The name of the column to query. Corresponds to the part of the SELECT statement after the SELECT keyword. selection: The query condition clause, which is equivalent to the part after the WHERE keyword in the SELECT statement, allows the placeholder "?" to be used in the conditional clause. Selectionargs: Corresponds to the value of the placeholder in the selection statement, where the value in the array must match the position of the placeholder in the statement, or there will be an exception. GroupBy: Equivalent to the part of the SELECT statement after the GROUP by keyword having: Equivalent to the part of the SELECT statement after the HAVING keyword by: Equivalent to the part after the order by keyword of the SELECT statement, such as: PersonID DESC, age ASC; Limit: Specifies the offset and the number of records fetched, which is equivalent to the portion of the SELECT statement following the Limit keyword.

Package Com.example.service;import Java.util.arraylist;import Java.util.list;import Com.example.domain.Person; Import Android.content.contentvalues;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;public class Otherpersonservice {private Dbopenhelper DbOpenHelper;public Otherpersonservice (Context context) {This.dbopenhelper = new Dbopenhelper (context);} /** * New record * @param person */public void Save (person person) {sqlitedatabase db=dbopenhelper.getwritabledatabase (); Contentvalues values=new contentvalues () values.put ("name", Person.getname ()); Values.put ("Phone", Person.getphone ( ));d B.insert ("person", null, values);} /** * Delete record * @param person */public void Delete (Integer id) {sqlitedatabase db=dbopenhelper.getwritabledatabase (); Db.delete ("Person", "personid=?", New String[]{id.tostring ()}); /** * Update record * @param person */public void Update (person person) {sqlitedatabase db=dbopenhelper.getwritabledatabase (); Contentvalues values=new ConTentvalues (); Values.put ("Name", Person.getname ()), Values.put ("Phone", Person.getphone ());d b.update ("person", Values, "personid=", New String[]{person.getid (). toString ()}); /** * Find record * @param ID record ID * @return */public person find (Integer ID) {sqlitedatabase db=dbopenhelper.getreadabledatabase ( ); Cursor cursor=db.query ("person", NULL, "Personid=?", New String[]{id.tostring ()}, NULL, NULL, NULL); if ( Cursor.movetofirst ()) {int Personid=cursor.getint (Cursor.getcolumnindex ("PersonID")); String name=cursor.getstring (Cursor.getcolumnindex ("name")); String phone=cursor.getstring (Cursor.getcolumnindex ("Phone")); return new person (PersonID, name, phone);} Cursor.close (); return null;} /** * Paging Get record * @param offset skips the number of records in front * @param maxresult How many records are displayed per page * @return */public list<person> getscrolldata (in T offset,int maxresult) {sqlitedatabase db=dbopenhelper.getreadabledatabase (); List<person> persons=new arraylist<person> (); Cursor cursor=db.query ("person", NULL, NULL, null,null,null, "personID ASC ", offset+", "+maxresult"), while (Cursor.movetonext ()) {int Personid=cursor.getint (Cursor.getcolumnindex (" PersonID ")); String name=cursor.getstring (Cursor.getcolumnindex ("name")); String phone=cursor.getstring (Cursor.getcolumnindex ("Phone"));p Ersons.add (new person (PersonID, name, phone));} Cursor.close (); return persons;} /** * Get records total number of bars * @return */public long GetCount () {sqlitedatabase db=dbopenhelper.getreadabledatabase (); Cursor cursor=db.query ("person", New string[]{"COUNT (*)"}, NULL, NULL, NULL, NULL, NULL); Cursor.movetofirst (); long Result=cursor.getlong (0); Cursor.close (); return result;}}