Android programming operation embedded relational SQLite database example details
- 2020-12-10 00:50:57
- OfStack
An example of Android programming for embedded relational SQLite database is presented in this paper. To share for your reference, the details are as follows:
SQLite characteristics
1. A relational database, SQLite, is embedded in the Android platform. Different from other databases, SQLite does not distinguish between different types when it stores data
For example, if a field is declared as Integer, we can store a string, a field is declared as Boolean, or we can store a floating point number.
Unless the primary key is defined as Integer, only 64-bit integers can be stored
2. The database table can be created without specifying the data type, such as:
CREATE TABLEperson(id INTEGER PRIMARY KEY, name)3.SQLite supports most of the standard SQL statements, adding, deleting, changing and checking statements are common, paging query statements are the same as MySQL
SELECT * FROMperson LIMIT 20 OFFSET 10
SELECT * FROMperson LIMIT 20,10
Creating a database
1. Define class inheritance from SQLiteOpenHelper
2. Declare the constructor with four arguments
3. Override onCreate() method
4. Rewrite the upGrade() method
Example:
package cn.itcast.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class DBOpenHelper extends SQLiteOpenHelper {
/**
* create OpenHelper
* @param context context
* @param name The database name
* @param factory The cursor factory
* @param version Database version , Don't set it to 0, If it is 0 The database is created each time
*/
public DBOpenHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* When database control 1 Called the second time it is created
*/
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name)");
}
/**
* Called when the database version has changed
*/
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD balance");
}
}
public void testCreateDB() {
DBOpenHelper helper = new DBOpenHelper(getContext(), "itcast.db", null, 2);
helper.getWritableDatabase(); // Creating a database
}
CRUD operation
1. Different from JDBC to access the database, SQLite database operation does not need to load the driver, do not need to obtain the connection, can be used directly
After obtaining the SQLiteDatabase object, the SQL statement can be executed directly through this object:
SQLiteDatabase.execSQL()
SQLiteDatabase.rawQuery()
2. The difference between getReadableDatabase() and getWritableDatabase()
Looking at the source code, we find that getReadableDatabase() normally returns the same database that getWritableDatabase() takes and only opens read-only when an exception is thrown
3. Database object cache
The getWritableDatabase() method ends up using a member variable to remember the database object and decide whether to reuse it the next time it is opened
4.SQLiteDatabase encapsulates insert(), delete(), update(), query() four methods that can also operate on the database
These methods encapsulate part of the SQL statement and concatenate it with parameters
There are two ways to perform the crud operation, the first way is to write the sql statement itself to perform the operation, and the second way is to use the SQLiteDatabase class to call the responding method to perform the operation
The execSQL() method executes modified SQL statements such as insert, delete, update, and CREATETABLE; The rawQuery() method is used to execute the select statement.
Example of the first approach:
package cn.itcast.sqlite.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import cn.itcast.sqlite.DBOpenHelper;
import cn.itcast.sqlite.domain.Person;
public class SQLPersonService {
private DBOpenHelper helper;
public SQLPersonService(Context context) {
helper = new DBOpenHelper(context, "itcast.db", null, 2);// Initialize the database
}
/**
* insert 1 a Person
* @param p To insert the Person
*/
public void insert(Person p) {
SQLiteDatabase db = helper.getWritableDatabase(); // Get to the database
db.execSQL("INSERT INTO person(name,phone,balance) VALUES(?,?)", new Object[] { p.getName(), p.getPhone() });
db.close();
}
/**
* According to the ID delete
* @param id Want to delete the PERSON the ID
*/
public void delete(Integer id) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });
db.close();
}
/**
* update Person
* @param p To update the Person
*/
public void update(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("UPDATE person SET name=?,phone=?,balance=? WHERE id=?", new Object[] { p.getName(), p.getPhone(), p.getBalance(), p.getId() });
db.close();
}
/**
* According to the ID To find the
* @param id To check the ID
* @return Corresponding object , Return if not found null
*/
public Person find(Integer id) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT name,phone,balance FROM person WHERE id=?", new String[] { id.toString() });
Person p = null;
if (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(1);
Integer balance = cursor.getInt(2);
p = new Person(id, name, phone, balance);
}
cursor.close();
db.close();
return p;
}
/**
* Query all Person object
* @return Person A collection of objects , If not found , return 1 a size() for 0 the List
*/
public List<Person> findAll() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT id,name,phone,balance FROM person", null);
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()) {
Integer id = cursor.getInt(0);
String name = cursor.getString(1);
String phone = cursor.getString(2);
Integer balance = cursor.getInt(3);
persons.add(new Person(id, name, phone, balance));
}
cursor.close();
db.close();
return persons;
}
/**
* A search 1 Page data
* @param page The page number
* @param size Number of records per page
* @return
*/
public List<Person> findPage(int page, int size) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT id,name,phone,balance FROM person LIMIT ?,?" //
, new String[] { String.valueOf((page - 1) * size), String.valueOf(size) });
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()) {
Integer id = cursor.getInt(0);
String name = cursor.getString(1);
String phone = cursor.getString(2);
Integer balance = cursor.getInt(3);
persons.add(new Person(id, name, phone, balance));
}
cursor.close();
db.close();
return persons;
}
/**
* Capture number of records
* @return Record number
*/
public int getCount() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM person", null);
cursor.moveToNext();
return cursor.getInt(0);
}
}
Example of the second approach:
/**
* insert 1 a Person
* @param p To insert the Person
*/
public void insert(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", p.getName());
values.put("phone", p.getPhone());
values.put("balance", p.getBalance());
// The first 1 The parameter is the table name , The first 2 The argument is if you want to insert 1 Something specified when a record is empty 1 The name of the column , The first 3 The parameters are data
db.insert("person", null, values);
db.close();
}
/**
* According to the ID delete
* @param id Want to delete the PERSON the ID
*/
public void delete(Integer id) {
SQLiteDatabase db = helper.getWritableDatabase();
db.delete("person", "id=?", new String[] { id.toString() });
db.close();
}
/**
* update Person
* @param p To update the Person
*/
public void update(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("id", p.getId());
values.put("name", p.getName());
values.put("phone", p.getPhone());
values.put("balance", p.getBalance());
db.update("person", values, "id=?", new String[] { p.getId().toString() });
db.close();
}
/**
* According to the ID To find the
* @param id To check the ID
* @return Corresponding object , Return if not found null
*/
public Person find(Integer id) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query("person", new String[] { "name", "phone", "balance" }, "id=?", new String[] { id.toString() }, null, null, null);
Person p = null;
if (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(1);
Integer balance = cursor.getInt(2);
p = new Person(id, name, phone, balance);
}
cursor.close();
db.close();
return p;
}
/**
* Query all Person object
* @return Person A collection of objects , If not found , return 1 a size() for 0 the List
*/
public List<Person> findAll() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query("person", new String[] { "id", "name", "phone", "balance" }, null, null, null, null, "id desc");
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()) {
Integer id = cursor.getInt(0);
String name = cursor.getString(1);
String phone = cursor.getString(2);
Integer balance = cursor.getInt(3);
persons.add(new Person(id, name, phone, balance));
}
cursor.close();
db.close();
return persons;
}
/**
* A search 1 Page data
* @param page The page number
* @param size Number of records per page
* @return
*/
public List<Person> findPage(int page, int size) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query( //
"person", new String[] { "id", "name", "phone", "balance" }, null, null, null, null, null, (page - 1) * size + "," + size);
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()) {
Integer id = cursor.getInt(0);
String name = cursor.getString(1);
String phone = cursor.getString(2);
Integer balance = cursor.getInt(3);
persons.add(new Person(id, name, phone, balance));
}
cursor.close();
db.close();
return persons;
}
/**
* Capture number of records
* @return Record number
*/
public int getCount() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query( //
"person", new String[] { "COUNT(*)" }, null, null, null, null, null);
cursor.moveToNext();
return cursor.getInt(0);
}
Transaction management
1. When using the SQLite database, you can control transactions using the related methods defined in the SQLiteDatabase class
beginTransaction() opens the transaction
setTransactionSuccessful() sets the transaction success flag
endTransaction() ends the transaction
2.endTransaction() needs to be executed in finally, otherwise the transaction will end automatically only when it time out, which will reduce the database concurrency efficiency
Example:
public void remit(int from, int to, int amount) {
SQLiteDatabase db = helper.getWritableDatabase();
// Open the transaction
try {
db.beginTransaction();
db.execSQL("UPDATE person SET balance=balance-? WHERE id=?", new Object[] { amount, from });
System.out.println(1 / 0);
db.execSQL("UPDATE person SET balance=balance+? WHERE id=?", new Object[] { amount, to });
// Set transaction flags
db.setTransactionSuccessful();
} finally {
// End of the transaction
db.endTransaction();
}
db.close();
}
I hope this article is helpful for Android programming.