SQLite database
1, Introduction to SQLite database
1. Integrated lightweight database in Android system
2. Features:
Lightweight uses only one dynamic library and is accessed in the form of a single file
Support multiple operating systems across platforms
Zero configuration requires no installation and can be used directly
Embedded into mobile phone
3. Inside the program, it can be accessed through the name of the database, which cannot be accessed by other applications
4. Path: data/data / application package name / database/ ****.db
5. Storage type:
NULL null
INTEGER (without int)
VARCHAR variable length character data
TEXT text string
BOOLEAN Boolean
DATE
TIME
2, Database core class
1.SQLiteOpenHelper database help class, "for database creation and version update"
(1) Define a class that inherits SQLiteOpenHelper
(2) Rewrite construction method: provide basic information of the database: context object, database name, Null, database version number
(3) Override method of parent class:
onCreate(): called when creating a database for the first time, it will only execute once -- "initialize table, initialize data"
onUpgrade()
2. Operation class of sqlitedatabase database, "operation database: execute sql statement / add / delete / modify / query"
(0) get SQLiteDatabase object
(1) Execute sql statement
void execSQL(String sql,String[] bindArgs); Execute addition, deletion and modification
Cusor rawQuery(String sql,String[] selectionArgs); Execute query
(2) Execution method – encapsulated method (for novice programmers)
insert() insert data
update() to modify data
delete() deletes data
query() query data
3, SQL Statement Review:
1. Create table:
create table student(_id integer primary key autoincrement,name varchar(30),age integer,address varchar(30));
2. Add data: insert into student values(null, 'sweet snow', 18, 'Haidian District, Beijing');
3. Modify data: update student set age=13,address = 'Cangzhou City, Hebei Province', where name = 'want sweet snow';
4. Query data:
select * from student where field = 'value'
Fuzzy query: select * from table name where name like '% small%'
select * from student order by age desc
Sum (column name)
Max (column name) maximum
Min (column name) minimum
AVG (column name) average
Count (column name) counts the number of records
5. Delete data: delete from student where id=12;
4, Database simple code:
Example: create a table student (ID, name, age address)
1. The custom class inherits SQLiteOpenHelper
Note: the oncreate method is executed only once. If you want to modify the fields of the table, you can't modify the oncreate method. You need to delete the previously generated database
//TODO 1: define class inheritance SQLiteOpenHelper
public class MyHelper extends SQLiteOpenHelper {
//TODO 2: Construction
public MyHelper(Context context) {
super(context, "student.db", null, 1);
}
//TODO 3: override the method of the parent class
/**
* Called when creating a database and executed only once -- initialize the table and initialize the data
* @param db Operation class of database
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table User(_id integer primary key autoincrement,Name varchar(20),Sex varchar(20),Age integer)");
}
/**
* Update data - if the version number of the database changes, execute this method - execute it multiple times
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2. Add, delete, modify and query methods:
public void insert(View view) {
MyHelper dbOpenHelper=new MyHelper(this);
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//Mode 1:
db.execSQL("insert into User(Name,Sex,Age) values('Zhi Qiang Yin','male',18)");
//Mode 2:
//Parameter 1 table name parameter 2: default value parameter 3: value to insert
ContentValues values = new ContentValues();
values.put("Name","Zhi Qiang Yin");
values.put("Sex","male");
values.put("Age","18");
db.insert("User",null,values);
}
//TODO: modify data
public void updateData(View view) {
MyHelper dbOpenHelper=new MyHelper(this);
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//Mode 1:
db.execSQL("update User set Age = 3 where name=?",new String[]{"Zhi Qiang Yin"});
//Method 2: parameter 1: table name parameter 2: the value to be modified parameter 3: condition parameter 4: the value in the condition? assignment
ContentValues values = new ContentValues();
values.put("Age","3");
int num=db.update("User",values,"name=?",new String[]{"Zhi Qiang Yin"});
}
//TODO: delete data
public void deleteData(View view) {
MyHelper dbOpenHelper=new MyHelper(this);
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//Mode 1:
String sql="delete from User where name = ? ";
db.execSQL(sql,new String[]{"Lei Zhang"});
//Method 2: parameter 1: table name parameter 2: the value to be modified parameter 3: condition parameter 4: the value in the condition? assignment
ContentValues values = new ContentValues();
values.put("Age","3");
int num=db.update("User",values,"name=?",new String[]{"Zhi Qiang Yin"});
}
//TODO: query data
public void query1(View view) {
//Create a database
MyHelper dbOpenHelper=new MyHelper(this);
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//Mode 1
Cursor cursor = db.rawQuery("select * from User",null);
//Mode II
Cursor cursor=db.query("User",null,null,null,null,null,null);
//Cyclic output
while (cursor.moveToNext())
{
//cursor.getColumnIndex("Name") obtains the number of the column through the column name
//Cursor.getstring (column number); The data of the column is obtained by the number of the column
String Name = cursor.getString(cursor.getColumnIndex("Name"));
String Sex = cursor.getString(cursor.getColumnIndex("Sex"));
int Age = cursor.getInt(cursor.getColumnIndex("Age"));
Log.i("yaotianxue","full name: " + Name +", Gender: "+ Sex + ", Age : "+ Age);
}
}
5, Code:
Example: create a table student (ID, name, age address)
1. The custom class inherits SQLiteOpenHelper
Note: the oncreate method is executed only once. If you want to modify the fields of the table, you can't modify the oncreate method. You need to delete the previously generated database
//TODO 1: define class inheritance SQLiteOpenHelper
public class MyHelper extends SQLiteOpenHelper {
//TODO 2: Construction
public MyHelper(Context context) {
super(context, "student.db", null, 1);
}
//TODO 3: override the method of the parent class
/**
* Called when creating a database and executed only once -- initialize the table and initialize the data
* @param db Operation class of database
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table student(_id integer primary key autoincrement,name varchar(20),age integer)");
}
/**
* Update data - if the version number of the database changes, execute this method - execute it multiple times
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2. Define the entity class according to the table name: Student.java
public class Student {
private int _id;
private String name;
private int age;
public Student(int _id, String name, int age) {
this._id = _id;
this.name = name;
this.age = age;
}
public int get_id() {
return _id;
}
public void set_id(int _id) {
this._id = _id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
3. The database tool class provides methods for adding, deleting, modifying and querying. Do not execute SQL statements in the Activity, and achieve MVC mode:
public class StudentDao {
MyHelper myHelper;
//Provide the construction method: create the database
public StudentDao(Context context) {//Pass context object
myHelper=new MyHelper(context);
}
/***
* @param student Pass the object you want to insert
* @return Is the insertion successful
*/
public boolean add(Student student){
//Get the database object, readable and writable
SQLiteDatabase db=myHelper.getWritableDatabase();
//Create a ContentValues object to encapsulate the data you want to insert
ContentValues values = new ContentValues();
values.put("name",student.getName());
values.put("age",student.getAge());
long num=db.insert("student",null,values);
//Determine whether the insertion is successful
if(num>0){
return true;
}else{
return false;
}
}
/***
* Delete data by id
* @param id
* @return Represents the number of deleted
*/
public int deleteById(int id){
//Get the database object, readable and writable
SQLiteDatabase db=myHelper.getWritableDatabase();
return db.delete("student","id=?",new String[]{id+""});
}
/***
* Delete data by name
* @param name
* @return Represents the number of deleted
*/
public int deleteByName(String name){
//Get the database object, readable and writable
SQLiteDatabase db=myHelper.getWritableDatabase();
return db.delete("student","name=?",new String[]{name+""});
}
/***
* Modify data according to id
* @param student Pass the object you want to change to
* @return
*/
public int update(Student student,int id){
//Get the database object, readable and writable
SQLiteDatabase db=myHelper.getWritableDatabase();
//Create a ContentValues object to encapsulate the data you want to insert
ContentValues values = new ContentValues();
values.put("name",student.getName());
values.put("age",student.getAge());
return db.update("student",values,"id=?",new String[]{id+""});
}
/***
*
* @return Query all data
*/
public ArrayList<Student> queryAll(){
//Get the database object, readable and writable
SQLiteDatabase db=myHelper.getWritableDatabase();
//Create collection
ArrayList<Student> list = new ArrayList<>();
//Execute query statement
Cursor cursor = db.query("student", null, null, null, null, null, null);
//Traversal cursor
while (cursor.moveToNext()){
int id=cursor.getInt(cursor.getColumnIndex("_id"));
String name=cursor.getString(cursor.getColumnIndex("name"));
int age=cursor.getInt(cursor.getColumnIndex("age"));
//Create objects and store them in the collection
Student student = new Student(id, name, age);
list.add(student);
}
return list;
}
}
4. Add, delete and modify query to display the queried data in ListView
public class Main2Activity extends AppCompatActivity {
private StudentDao dao;
private ListView listView;
private MyAdapter myAdapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
listView=findViewById(R.id.lv);
dao=new StudentDao(Main2Activity.this);
}
//TODO: insert data
public void insert(View view) {
Student student = new Student("Yao Tianxue", 34);
dao.insert(student);
}
//TODO: delete data
public void delete(View view) {
dao.deleteById(1);
}
//TODO: modify data
public void update(View view) {
Student student = new Student("Yao Tianxue 1", 78);
dao.update(student,1);//Modify the student with id 1 to
}
//TODO: query data
public void query(View view) {
//Query data
ArrayList<Student> list = dao.queryAll();
//Create adapter
myAdapter=new MyAdapter(Main2Activity.this,list);
listView.setAdapter(myAdapter);
}
}
6, Batch processing data using transactions
1. What is a transaction?
Is to deal with multiple things as one thing. That is, we are in the same boat. We should live together and over together!
In the development of Android applications, we often encounter the need for database operation. Database transaction processing is very important to improve the stability and efficiency of Android applications.
2... The benefits of business?
1. First, Android database operations, especially write operations, are very slow. Packaging all operations into one transaction can greatly improve the processing speed.
2. Ensure data consistency and make all operations in a transaction execute successfully, fail or roll back.
For example, when an application initializes, it needs to insert a large amount of data in batches. The separate use of the for loop method leads to slow application response, because when sqlite inserts data, the default statement is a transaction, and there are as many disk operations as there are data. My application starts with 5000 records, that is, 5000 disk reads and writes.
And there is no guarantee that all data can be inserted at the same time. (it is possible that part of the insertion is successful and the other part fails, and it has to be deleted later. It's too troublesome)
3. Code:
SQLiteDatabase db = myHepler.getWritableDatabase();
db.beginTransaction();// Open transaction
try {
for (int i=0;i<1000;i++) {
db.execSQL("insert into table name (field name) values");
}
db.setTransactionSuccessful();// success
}catch (Exception e){
e.printStackTrace();
}finally {
db.endTransaction();// End transaction
db.close();// close
}