SQLite database operations

1. Database ideas
(1) fields in the table can be implemented using javabean.
(2) When the database is used with ContentProvider, execSQL () and rawQuery () of SQLiteDatabase are not used here () to add, delete, modify, and query data. Instead, use the update (), delete (), query () methods provided by SQLiteDatabase. because the two parameters are basically the same, you can pass them directly from ContentProvider to these built-in methods.

2 SQLiteOpenHelper Introduction
When writing database application software, we need to consider the following issues: the software may be installed on mobile phones of many users.
If the application uses the SQLite database, we must create the database table structure used by the application and add some initialization records when the user first uses the software. In addition, when the software is upgraded, you also need to update the data table structure.
Then, how can we automatically create the database tables required by the application on the user's mobile phone when the user first uses or upgrades the software? We cannot manually create database tables on every mobile phone that requires the software to be installed? (That's why it's called ...... OpenHelper)
Therefore, you can re-write the SQLiteOpenHelper onCreate () and onUpgrade () Methods to automatically call the methods when the database and database versions are changed.

3 Use DataBaseOpenHelper
DataBaseOpenHelper's instance dataBaseOpenHelper can return the instance database of the database SQLiteDatabase in the form of dataBaseOpenHelper. getWritableDatabase () and dataBaseOpenHelper. getWritableDatabase. What are the differences between the two methods? The source code shows that getReadableDatabase actually returns getWritableDatabase when there is no error. When will there be an error?
The disk space of the database is full, and the database can only be read but not written at this time. Therefore, you cannot open the database using the read/write (read and write operations) method. An error is reported at this time. Therefore, you can only open the database in read-only mode.
Therefore, when an error occurs, getReadableDatabase () is returned ()!!!

4. add, delete, modify, and query Databases
Use the SQL statement db.exe cSQL () to execute the change action. db. rawQuery () to execute the query statement.
Note:
(1) it is best to use placeholders in SQL statements to improve the security of SQL statements.
(2) public void execSQL (String SQL, Object [] bindArgs) The second parameter is the Object array.
(3) public Cursor rawQuery (String SQL, String [] selectionArgs) The second parameter is a String array.

Add example:
Db.exe cSQL ("insert into person (name, phone) values (?,?) ", New Object [] {person. getName (), person. getPhone ()});

Update example:
Public void update (Person person ){
SQLiteDatabase db = openHelper. getWritableDatabase ();
Db.exe cSQL ("update person set name = ?, Phone =? Where personid =? ", New Object [] {person. getName (), person. getPhone (), person. getId ()});
}

Deletion example:
Public void delete (int I ){
SQLiteDatabase db = openHelper. getWritableDatabase ();
Db.exe cSQL ("delete from person where personid =? ", New Object [] {String. valueOf (I )});
}

Search example:

public Person find(int personId){SQLiteDatabase db=openHelper.getWritableDatabase();Cursor cursor=db.rawQuery("select * from person where personid=?", new String[]{String.valueOf(i)});while(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;}

Note:
(1) The result of db. rawQuery () is a cursor result set. When the pointer is started, it points to the top of the result set, that is, before the first record.
(2) value operation: the specific cursor. get type (the parameter is the index value of the name of a column)
(3) Close cursor.

Statistical Example:

public int count(){SQLiteDatabase db=openHelper.getReadableDatabase(); Cursor cursor=db.rawQuery("select count(*) from person", null);int i=0;while(cursor.moveToFirst()){     i=cursor.getInt(0);     break;}return i;}

 

Paging example:

public List<Person> page(int offset,int resuletNumber){SQLiteDatabase db=openHelper.getWritableDatabase();ArrayList<Person> persons=new ArrayList<Person>();Person person;Cursor  cursor=db.rawQuery("select * from person limit ?,?",new String []{String.valueOf(offset),String.valueOf(resuletNumber)});while(cursor.moveToNext()){int personid=cursor.getInt(cursor.getColumnIndex("personid"));String name=cursor.getString(cursor.getColumnIndex("name"));String phone=cursor.getString(cursor.getColumnIndex("phone"));person=new Person(personid, name, phone);persons.add(person);}return persons;  }

5. database transactions

Public void payment () {SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.beginTransaction();try{db.exe cSQL ("update person set amount = amount-10 where personid = 1" )mongodb.exe cSQL ("update person set amount = amount + 10 where personid = 2 "); db. setTransactionSuccessful (); // set the transaction flag to successful} finally {db. endTransaction (); // end the transaction. The transaction is rolled back by default }}

There are two types of end transactions: commit transactions and roll back transactions. By default, the transaction is rolled back !!!
Whether a transaction is committed is determined by the transaction flag. If the transaction flag fails (false), the transaction is rolled back; otherwise, the transaction is committed (true.
Therefore, the transaction flag is failed (false) by default, and the transaction is rolled back.
Why should I put the end transaction in finally?
Otherwise, when the transaction encounters an exception, it will not be executed. The database will not end the transaction until a timeout period affects the performance, so it will end in finally.