Android learning notes (2), android learning notes

Android learning notes (2), android learning notes
17. SQLite Database
// The paging statement is similar to that of mysql.
Select * from student limit: Number of pages displayed per page * (page-1); number of pages displayed per page // page


Public class DBOpenHelper extends SQLiteOpenHelper {


Public DBOpenHelper (Context context ){
Super (context, "itcast. db", null, 2); // <package>/databases/
}


@ Override
Public void onCreate (SQLiteDatabase db) {// called every time a database is created
Db.exe cSQL ("create table person (personid integer primary key autoincrement, name varchar (20), phone VARCHAR (12) NULL )");
}


@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("alter table person ADD amount integer ");
}
}
// Create a test
Public void testCreateDB () throws Exception {
DBOpenHelper dbOpenHelper = new DBOpenHelper (getContext ());
DbOpenHelper. getWritableDatabase ();
}
/**
* Add record
* @ Param person
*/
Public void save (Person person ){
SQLiteDatabase db = dbOpenHelper. getWritableDatabase ();
Db.exe cSQL ("insert into person (name, phone, amount) values (?,?,?) ",
New Object [] {person. getName (), person. getPhone (), person. getAmount ()});
}


The getWritableDatabase () method returns an error when the database storage space is full.
The getReadableDatabase () method is called when getWritableDatabase () reports an error and only reads data.
/**
* Query records
* @ 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 "));
Int amount = cursor. getInt (cursor. getColumnIndex ("amount "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));
Return new Person (personid, name, phone, amount );
}
Cursor. close ();
Return null;
}
/**
* Retrieve records by PAGE
* @ Param offset skips the previous number of records
* @ Param maxResult how many records are obtained per page
* @ Return
*/
Public List <Person> getScrollData (int offset, int maxResult ){
List <Person> persons = new ArrayList <Person> ();
SQLiteDatabase db = dbOpenHelper. getReadableDatabase ();
Cursor cursor = db. rawQuery ("select * from person order by personid asc limit ?,? ",
New String [] {String. valueOf (offset), String. valueOf (maxResult )});
While (cursor. moveToNext ()){
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
Int amount = cursor. getInt (cursor. getColumnIndex ("amount "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));
Persons. add (new Person (personid, name, phone, amount ));
}
Cursor. close ();
Return persons;
}
/**
* Retrieve the total number of records
* @ 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 query (), delete (), update (), and insert () Methods encapsulated by Sqlite.


18. SQLite transactions
SQLiteDatabase db = dbOpenHelper. getWritableDatabase ();
Db. beginTransaction (); // start the transaction
Try {
Db.exe cSQL ("update person set amount = amount-10 where personid = 1 ");
Db.exe cSQL ("update person set amount = amount + 10 where personid = 2 ");
Db. setTransactionSuccessful (); // set the transaction flag to True.
} Finally {
Db. endTransaction (); // ends the transaction. There are two cases: commit, rollback,
// The commit or rollback of a transaction is determined by the transaction flag. If the transaction flag is True, the transaction will be committed and no side rollback is performed, the transaction flag is False by default.
}

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.