Database of Android Development SQLite

The database used in Android is an open source database SQLite database, the following is the main detailed introduction of the use of SQLite:

An Introduction to SQLite

when we write database applications, we need to consider the problem: because the software we develop may be installed on many users ' phones, if the application uses SQLite database, we must create the database table structure used by the application when the user first uses the software, and add some initialization records, also need to update the data table structure when the software is upgraded. So how do we make it possible for users to automatically create database tables on their phone when they first use or upgrade their software? You can't let us create a database table manually on every phone that needs to install this software? Because this requirement is for every database application , the Android system provides us with an abstract class called sqliteopenhelper , which must inherit it to be used, It does this by managing the database version to meet the requirements presented earlier.

to enable the management of database versions,Sqliteopenhelperclass provides two important methods, namelyonCreate(SqlitedatabaseDB)and theOnupgrade(SqlitedatabaseDB,intoldversion,intnewversion),The former is used to generate database tables when the software is first used, which updates the database table structure when upgrading software. When callingSqliteopenhelperof theGetwritabledatabase()orGetreadabledatabase()method Gets the database that is used to manipulate theSqlitedatabaseinstance, if the database does not exist,Androidthe system automatically generates a database and then callsonCreate()method,onCreate()method is called when the database is first generated,onCreate()in the methodcan generate database table structures and add initialization data that some applications use. Onupgrade()MethodIt is called when the version of the database is changed, and the version number is usually changed when the software is upgraded., and the version of the database is controlled by the programmer, assuming that the current version of the database is1, due to business changes, modify the database table structure, this time need to upgrade the software, upgrade the software when you want to update the database table structure of the user's phone, in order to achieve this goal, the original database version can be set to2 (have classmates asked to set the3Is that OK? Of course you can, if you like, set to -also OK), and inOnupgrade()method to implement the table structure update. When the version of the software is upgraded more frequently, theOnupgrade()The method can be judged according to the original number and the target version number, then make the corresponding table structure and data update.

Getwritabledatabase () and getreadabledatabase() methods can obtain a sqlitedatabase for manipulating the database instance. But the getwritabledatabase() method opens the database in read-write mode, and once the database is full of disk space, the database is read-only and cannot be written, if you use Getwritabledatabase() the database will be opened with an error. the getreadabledatabase() method opens the database in read-write mode, and if the database has a full disk space, it will fail to open and will continue to attempt to open the database as read-only when the open fails.

1 Complete the CREATE DATABASE operation: Define Dbopenhelper to inherit the Sqliteopenhelper class, override the OnCreate method to create the database, and execute Onupgrade update the data table information when the version number changes.

Package Com.andy.service;import Android.content.context;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqliteopenhelper;public class Dbopenhelper extends sqliteopenhelper{public       Dbopenhelper (Context context) {Super (context, "andy.db", NULL, 0);} Database version number starting from 0 for the first time, the update operation for database information is performed without adding one time @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 (), phone varchar (+) NULL) "); @Overridepublic void Onupgrade (sqlitedatabase db, int orderversion, int newversion) {db.execsql ("ALTER TABLE person ADD am Ount integer ");}}

2 Defining the implementation of the Personservice database operations class

Package Com.andy.service;import Java.util.arraylist;import Java.util.list;import android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import Com.andy.domain.Person;public Class Personservice {private Dbopenhelper dbopenhelper;public Personservice (context context) {Dbopenhelper = new Dbopenhelper (context);} The way to open a database is getwritabledatabase getreadabledatabase if the database is not full, return writablepublic void Save (person person) { Sqlitedatabase sqlitedatabase = Dbopenhelper.getwritabledatabase (); Sqlitedatabase.execsql ("INSERT into the person (name, Phone, amount) VALUES (?,?,?) ", new string[] {person.getname (), Person.getphone (), String.valueof (Person.getamount ())}); public void update (person person) {Sqlitedatabase sqlitedatabase = Dbopenhelper.getwritabledatabase (); Sqlitedatabase.execsql ("Update person set name=?, phone=?, amount=?") where PersonID =? ", new string[] {person.getname (), Person.getphone (), Person.getamount (). toString (), Person.getid (). ToString ()});} public void Delete (int id) {Sqlitedatabase sqlitedatabase = Dbopenhelper.getwritabledatabase (); sqlitedatabase.execsql ("Delete from person where personid=?", new integer[] {ID});} Public person find (Integer ID) {Sqlitedatabase sqlitedatabase = Dbopenhelper.getreadabledatabase ();//Read data cursor cursor = Sqlitedatabase.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")), int amount = Cursor.getint (Cursor.getcolumnindex (" Amount ")); return new person (PersonID, name, phone, amount);} Cursor.close ();//cursor needs to close return null;} Public list<person> getscrolldata (int offset, int maxresult) {Sqlitedatabase sqlitedatabase = Dbopenhelper.getreadabledatabase (); list<person> persons = new arraylist<person> (); cursor cursor = Sqlitedatabase.rawquery ("SELECT * from Person ORDER by PersonID limit?,?", new string[] {string.valueof (offset), string.valueof (Maxresult)}); whi Le (Cursor.movetonext ()) {int PersonID = Cursor.getint (Cursor.getcolumnindex ("PersonID")); String name = cursor.getstring (Cursor.getcolumnindex ("name")); String phone = cursor.getstring (Cursor.getcolumnindex ("Phone")), int amount = Cursor.getint (Cursor.getcolumnindex (" Amount "));p Ersons.add (new person (PersonID, name, phone, amount));} Cursor.close (); return persons;} Public long GetCount () {Sqlitedatabase sqlitedatabase = Dbopenhelper.getreadabledatabase (); cursor cursor = sqlitedatabase.rawquery ("SELECT count (*) from person", null); Cursor.movetofirst (); Long result = Cursor.getlong (0); Cursor.close (); return result;} Public Cursor getcursorscrolldata (int offset, int maxresult) {Sqlitedatabase sqlitedatabase = Dbopenhelper.getreadabledatabase (); cursor cursor = sqlitedatabase.rawquery ("Select PersonID _id, name, phone, amount from person order by PersonID limit?,? ", New string[]{string.valueof (offset), string.valueof (Maxresult)}); return cursor;} Test transaction transaction public void Payment () {Sqlitedatabase sqlitedatabase = Dbopenhelper.getwritabledatabase (); Sqlitedatabase.begintransaction (); Try{sqlitedatabase.execsql ("update person set amount = amount-10 where PersonID = 1") ; Sqlitedatabase.execsql ("update person Set amount = Amount + where PersonID = 2"); sqlitedatabase.settransactionsuccess Ful ();} Finally{sqlitedatabase.endtransaction ();//End transaction There are two situations in which commit rollback//The Commit of a thing is determined by the transaction's flag, and if the transaction's flag is true, the transaction commits, or it rolls back. By default, the state of a transaction is false//regardless of whether the rollback or commit must be performed at the end of the transaction to be executed in try-finally}}}

3 The person class to use

Package Com.andy.domain;public class Person {private Integer id;private string name;private string Phone;private Integer A Mount;public person () {}public person (string name, string phone, Integer amount) {super (); this.name = Name;this.phone = Pho Ne;this.amount = amount;} Public person (integer ID, string name, string phone, Integer amount) {super (); this.id = Id;this.name = Name;this.phone = P Hone;this.amount = amount;} Public Integer GetId () {return ID;} public void SetId (Integer id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public String Getphone () {return phone;} public void Setphone (String phone) {this.phone = phone;} Public Integer Getamount () {return amount;} public void Setamount (Integer amount) {this.amount = amount;}  @Overridepublic String toString () {return "person [id=" + ID + ", name=" + name + ", phone=" + phone+ ", amount=" + Amount + "]";}}

Database of Android Development SQLite