Use SQLite database in Android applications (Chuanzhi podcast video notes)

Use the SQLite database in Android applications

The database file generated by the application is saved as/data/application package name/databases/name. dB. The generated file can be opened by SQLite expert.

(Name is custom. For example, my application is saved in:/data/COM. sinaapp. SQL/databases/ssun. DB)

Sqliteactivity. Java source code:

Package COM. sinaapp. SQL; </P> <p> Import COM. sinaapp. SQL. r; </P> <p> Import android. app. activity; <br/> Import android. OS. bundle; </P> <p> public class sqliteactivity extends activity {<br/>/** called when the activity is first created. */<br/> @ override <br/> Public void oncreate (bundle savedinstancestate) {<br/> super. oncreate (savedinstancestate); <br/> setcontentview (R. layout. main); <br/>}< br/>}

Pseron. Java source code:

Package COM. sinaapp. domain; </P> <p> public class person {<br/> private string name; <br/> private integer ID; <br/> private string phone; <br/> private integer amount; </P> <p> Public Person () {}</P> <p> @ override <br/> Public String tostring () {<br/> return "person [name =" + name + ", id =" + ID + ", phone =" + phone <br/> + ", amount = "+ amount +"] "; <br/>}</P> <p> Public Person (string name, integer ID, string phone, integer amount) {<br/> This. name = Name; <br/> This. id = ID; <br/> This. phone = phone; <br/> This. amount = amount; <br/>}</P> <p> Public Person (string name, string phone, integer amount) {<br/> This. name = Name; <br/> This. phone = phone; <br/> This. amount = amount; <br/>}</P> <p> Public integer getamount () {<br/> return amount; <br/>}</P> <p> Public void setamount (integer amount) {<br/> This. amount = amount; <br/>}</P> <p> Public String getname () {<br/> return name; <br/>}</P> <p> Public void setname (string name) {<br/> This. name = Name; <br/>}< br/> Public integer GETID () {<br/> return ID; <br/>}< br/> Public void setid (integer ID) {<br/> This. id = ID; <br/>}< br/> Public String getphone () {<br/> return phone; <br/>}< br/> Public void setphone (string phone) {<br/> This. phone = phone; <br/>}< br/>

Dbopenhelper. Java source code:

Package COM. sinaapp. service; </P> <p> Import android. content. context; <br/> Import android. database. SQLite. sqlitedatabase; <br/> Import android. database. SQLite. sqliteopenhelper; </P> <p> public class dbopenhelper extends sqliteopenhelper {</P> <p> Public dbopenhelper (context) {<br/> super (context, "ssun. DB ", null, 3); // Changes the database version number, the onupgrade method <br/>}</P> <p> @ override <br/> Public void oncreate (sqlitedatabase dB) will be executed) {// method used for the first time <br/> // todo auto-generated method stub <br/> db.exe csql ("create table person (ID integer primary key autoincrement, name varchar (20), phone varchar (20) "); <br/>}</P> <p> @ override <br/> Public void onupgrade (sqlitedatabase dB, int oldversion, int newversion) {<br/> // todo auto-generated method stub <br/> db.exe csql ("alter table person add amount int (10 )"); <br/>}</P> <p >}< br/>

Personservice. Java source code:

Package COM. sinaapp. service; </P> <p> Import Java. util. arraylist; <br/> Import Java. util. list; </P> <p> Import android. content. context; <br/> Import android. database. cursor; <br/> Import android. database. SQLite. sqlitedatabase; </P> <p> Import COM. sinaapp. domain. person; </P> <p> public class personservice {<br/> private dbopenhelper helper; </P> <p> Public personservice (context) {<br/> This. helper = new dbopen Helper (context); <br/>}</P> <p> Public void payment () {<br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> try {<br/> dB. begintransaction (); <br/> db.exe csql ("Update person set amount = amount-10 where id = 3 "); <br/> db.exe csql ("Update person set amount = Amount + 10 where id = 4"); <br/> dB. settransactionsuccessful (); <br/>}finally {<br/> dB. endtransaction (); <br/>}</P> <p>/** <br/> * added Add record <br/> * @ Param person <br/> */<br/> Public void add (person) {<br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> db.exe csql ("insert into person (name, phone, amount) values (?,?,?) ", <Br/> new object [] {person. getname (), person. getphone (), person. getamount ()}); <br/>}</P> <p>/** <br/> * delete a record <br/> * @ Param integer ID of the record to be deleted <br/> */<br/> Public void Delete (integer) {<br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> db.exe csql ("delete from person where id =? ", New object [] {INTEGER }); <br/>}</P> <p>/** <br/> * update record <br/> * @ Param person update record <br/> */< br/> Public void Update (person) {<br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> db.exe csql ("Update person set name = ?, Phone = ?, Amount =? Where id =? ", <Br/> new object [] {person. getname (), person. getphone (), person. getamount (), person. GETID ()}); <br/>}</P> <p>/** <br/> * query record <br/> * @ Param integer <br/> * @ return <br/> */<br/> public person find (integer) {<br/> person = NULL; <br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> cursor = dB. rawquery ("select * From person where id =? ", <Br/> New String [] {integer. tostring ()}); <br/> If (cursor. movetofirst () {<br/> int id = cursor. getint (cursor. getcolumnindex ("ID"); <br/> string name = cursor. getstring (cursor. getcolumnindex ("name"); <br/> string phone = cursor. getstring (cursor. getcolumnindex ("phone"); <br/> int amount = cursor. getint (cursor. getcolumnindex ("amount"); <br/> person = new person (name, ID, phone, amount); <br/>}< br/> retur N person; <br/>}</P> <p>/** <br/> * display records by page <br/> * @ Param start record number <br/> *@ param length record length <br/> * @ return <br/> */<br/> public list <person> getpersons (integer start, integer length) {<br/> List <person> Persons = new arraylist <person> (); <br/> person = NULL; <br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> cursor = dB. rawquery ("select * From person order by ID limit ?,? ", <Br/> New String [] {start. tostring (), length. tostring ()}); <br/> while (cursor. movetonext () {<br/> int id = cursor. getint (cursor. getcolumnindex ("ID"); <br/> string name = cursor. getstring (cursor. getcolumnindex ("name"); <br/> string phone = cursor. getstring (cursor. getcolumnindex ("phone"); <br/> int amount = cursor. getint (cursor. getcolumnindex ("amount"); <br/> person = new person (name, ID, phone, amount); <br/> persons. add (person); <br/> person = NULL; <br/>}< br/> cursor. close (); <br/> return persons; <br/>}</P> <p>/** <br/> * Number of records <br/> * @ return <br/> */<br/> public integer getcounts () {<br/> sqlitedatabase DB = helper. getwritabledatabase (); <br/> cursor = dB. rawquery ("select count (*) from person", null); <br/> cursor. movetofirst (); <br/> integer counts = cursor. getint (0); <br/> return counts; <br/>}</P> <p>

Unit Test testservice. Java source code:

Package COM. sinaapp. test; </P> <p> Import Java. util. list; </P> <p> Import android. test. androidtestcase; <br/> Import android. util. log; </P> <p> Import COM. sinaapp. domain. person; <br/> Import COM. sinaapp. service. dbopenhelper; <br/> Import COM. sinaapp. service. personservice; </P> <p> public class testservice extends androidtestcase {<br/> Public void testcreatedb () {<br/> dbopenhelper helper = new dbopenhelper (this. getcontext (); <br/> helper. getwritabledatabase (); <br/>}</P> <p> Public void testadd () {<br/> personservice service = new personservice (this. getcontext (); <br/> service. add (new person ("luolin", "12345", 100); <br/>}</P> <p> Public void testupdate () {<br/> personservice service = new personservice (this. getcontext (); <br/> service. update (new person ("Zhang", 4, "2222222", 200); <br/>}</P> <p> Public void testfind () {<br/> personservice service = new personservice (this. getcontext (); <br/> person = service. find (1); <br/> log. I ("testservice", person. tostring (); <br/>}</P> <p> Public void testdel () {<br/> personservice service = new personservice (this. getcontext (); <br/> service. delete (2); <br/>}</P> <p> Public void testgetcounts () {<br/> personservice service = new personservice (this. getcontext (); <br/> integer I = service. getcounts (); <br/> log. I ("testservice", I. tostring (); <br/>}</P> <p> Public void testgetpersons () {<br/> personservice service = new personservice (this. getcontext (); <br/> List <person> Persons = service. getpersons (0, 4); <br/> for (person P: Persons) {<br/> log. I ("testservice", p. tostring (); <br/>}</P> <p> Public void testpayment () {<br/> personservice service = new personservice (this. getcontext (); <br/> service. payment (); <br/>}< br/>

Androidmanifest. xml

<? XML version = "1.0" encoding = "UTF-8"?> <Br/> <manifest xmlns: Android = "http://schemas.android.com/apk/res/android" <br/> package = "com. sinaapp. SQL "<br/> Android: versioncode =" 1 "<br/> Android: versionname =" 1.0 "> </P> <p> <uses-SDK Android: minsdkversion = "8"/> </P> <p> <application <br/> Android: icon = "@ drawable/ic_launcher" <br/> Android: label = "@ string/app_name"> <br/> <activity <br/> Android: Name = ". sqliteactivity "<br/> Android: Label =" @ string/app_name "> <br/> <intent-filter> <br/> <action Android: Name =" android. intent. action. main "/> <br/> <category Android: Name =" android. intent. category. launcher "/> <br/> </intent-filter> <br/> </activity> <br/> <uses-library Android: Name =" android. test. runner "/> <br/> </Application> <br/> <instrumentation Android: Name =" android. test. instrumentationtestrunner "<br/> Android: targetpackage =" com. sinaapp. SQL "/> </P> <p> </manifest>