SQLite
SQLite is an ultra-lightweight embedded database with a size of only a few hundred KB, but its syntax supports standard SQL syntax and also follows the database's ACID transactions, so it is easy for developers who have learned other databases to master its use.
I won't introduce the sql syntax, just look at the usage in Android
SQLiteOpenHelper - encapsulated database operation auxiliary class, need to be rewrite
Rewrite method
onCreate: Initialize the database, create tables, and add initial data
onUpgrade: Database operations during database version upgrade, such as backup and deletion of databases, etc.
Common methods
getReadableDatabase() Get SQLiteDatabase object and operate on the database
getWritableDatabase() Get SQLiteDatabase object and operate on the database
the difference:When the disk space is full or unwritable, method 1 will obtain a read-only database object, while method 2 will report an error. Under normal circumstances, all database objects are read-write.
import ;
import ;
import ;
public class DBHelper extends SQLiteOpenHelper {
private static final String name="my";//Database name private static final int version=1;// Version number // When rewriting the construction method, select one with fewer parameters. public DBHelper(Context context) {
//1: Context 2: Database name 3: Cursor creation factory 4: Database version The version can only be an integer 1 2 3.. super(context, name, null, version);
}
//Domain initialization SQLiteDatabase database operation object //It is usually only called during the first run and version update @Override
public void onCreate(SQLiteDatabase db) {
//Create a database. The primary key is increased by default. ("create table student(" +
"_id integer not null primary key autoincrement," +
"name varchar(20)," +
"phone varchar(11)," +
"gender varchar(2))");
//Add a test data ("insert into student values(null,?,?,?)"
,new Object[]{"Little Black","12345678901","male"});
}
/**
* Called during version upgrade
* Modify version to 2 to indicate the version upgrade and this method will be called
* @param db database operation object
* @param oldVersion old version number
* @param newVersion new version number
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
The creation of an Android database requires creating an object to create it
Create a database class object in the activity class
Create a data object and you can manipulate data through SQLiteDatabase. The difference between the two acquisition methods has been mentioned above.
//Create data DBHelper helper =new DBHelper(this); //Calling the data operation object SQLiteDatabase dbWrite=(); SQLiteDatabase dbRead=();
SQLiteDatabase provides us with many methods to operate data
Delete: (int) delete(String table,String whereClause,String[] whereArgs)
table: table name
whereClause: where condition Column name Placeholder id=?
whereArgs: parameter value array
Add: (long) insert(String table,String nullColumnHack,ContentValues values)
nullColumnHack: is an empty column
ContentValues values: Store added data through key value pairs. The key is the column value is the value.
The insert method is the method of splicing strings. If ContentValues is empty, a spliced SQL statement cannot be executed, an error will be reported. So a column that can be empty can also be executed when ContentValues is empty. If you are interested, you can look at the source code.
Update: (int) update(String table, ContentValues values, String whereClause, String[] whereArgs)
Parameters mean the same as above
Query: (Cursor) query(boolean distinct,String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
The return value is a cursor. This query has many parameters. For a simple query, it is better to write a SQL statement.
boolean distinct
String table table name
String[] columns to query
String selection query criteria
String[] selectionArgs query parameter value
String groupBy group
String having grouping conditions
String orderBy sort
String limit pagination query limit
Close the database: (void) close()
Execute a sql statement: (void) execSQL(String sql) add, delete, modify and search
Query query sql: (Cursor) rawQuery(String sql, String[] selectionArgs)
Transactions
try {
();//Open transaction // ();
// ();
(); //It will roll back if it is not set successfully } catch (Exception e) {
();
}finally {
();
}
Post code, execute sql statements and encapsulated methods
Query
private void query() {
StringBuffer sb=new StringBuffer("select * from student where 1=1");
// Parameter set List<String> params=new ArrayList<>();
if(!(id)){
(" and _id=?");
(id);
}
if(!(phone)){
(" and phone=?");
(phone);
}
if(!(name)){
(" and name=?");
(name);
}
if(!(gender)){
(" and gender=?");
(gender);
}
SQLiteDatabase db=();
String [] projection=new String [()];
(projection);
//Return value Cursor Cursor cursor=((),projection);
//Judge whether the cursor is empty and whether there is a value while(cursor!=null&&()){
// getColumnIndex gets the subscript of the column () gets the value of the specified column String name=(("name"));
Integer id=(("_id"));
}
}
If you can't use the parameters, just loop while iterate.
Cursor c = ("student",null,null,null,null,null);//Query and obtain cursor
renew
private void update() {
StringBuffer sb=new StringBuffer("update student set ");
List params=new ArrayList();
if(!(phone)){
("phone=?,");
(phone);
}
if(!(name)){
("name=?,");
(name);
}
if(!(gender)){
("gender=?,");
(gender);
}
if (()!=0){
//There is a "," at the end of the string of the update operation splicing. //Delete the last digit "," (()-1);
(" where 1=1");
//Update the data line by id if(!(id)){
(" and _id=?");
(id);
}else{
(this,"Please fill in the id",Toast.LENGTH_SHORT).show();
return;
}
SQLiteDatabase db=();
Object [] o=new Object[()];
(o);//Storing data in the specified array ((),o);
}
}
ContentValues cv = new ContentValues();//Instantiate ContentValues("name","123");//Add fields and contents to be changedString whereClause = "phone=?";//Modify the conditionsString[] whereArgs = {"12312313213"};//Modify the parameters of the condition("student",cv,whereClause,whereArgs);//Perform modification
delete
private void delete() {
getAllText();
//Split SQL statement StringBuffer sb=new StringBuffer("delete from student where 1=1");
//Save the list of parameters List params=new ArrayList();
//Judgement conditions Dynamic splicing if(!(id)){
(" and _id=?");
(id);
}
if(!(phone)){
(" and phone=?");
(phone);
}
if(!(name)){
(" and name=?");
(name);
}
if(!(gender)){
(" and gender=?");
(gender);
}
SQLiteDatabase db=();
if (()!=0){
Object [] o=new Object[()];
(o);//Storing data in the specified array //Execute deletion ((),o);
}else{
(());
}
(this,"Delete Completed",Toast.LENGTH_SHORT).show();
}
String whereClause = "name=?";//Delete conditionsString[] whereArgs = {"123"};//Delete conditional parameters("student",whereClause,whereArgs);//Perform deletion
Increase
private void insert() {//Save data to object array Object [] o=new Object[]{name,phone,gender};
//Get the database operation object SQLiteDatabase db=();
//sql:sql statement bingArgs: parameter array ("insert into student values(null,?,?,?)",o);
//Close the connection ();
(this,"Add success",Toast.LENGTH_SHORT).show();
}
ContentValues cv = new ContentValues();//Instantiate a ContentValues to load the data to be inserted("name","123");
("student",null,cv);//Perform an insert operation
The above is all the content of this article. I hope that the content of this article will help you study or work. I also hope to support me more!