Detailed description of SQLiteDatabase operations and sqlitedatabase operations

Detailed description of SQLiteDatabase operations and sqlitedatabase operations
Today, I spent some time summing up data-related knowledge. The SQLiteDatabase database of the android system is more troublesome than the ormLite database (a third-party database, however, I am familiar with such database operations, so I used this database. If you have any mistakes, please criticize and correct it. Thank you.
1. SQLiteDatabase itself is a database operation class, but if you want to perform database operations, you also need android. database. sqlite. SQLiteOpenHelper class help. When executing an SQL statement, the execSQL () method cannot return a description of the execSQL () method whose value is Android
2. SQLiteOpenHelper (taken from the practice of Li Xinghua android) SQLiteOpenHelper is an auxiliary class for database operations and an abstract class. Therefore, you need to define its sub-classes and re-write the corresponding abstract methods in the sub-classes.
The SQLiteOpenHelper class defines three callback methods. 2.1 onCreate () This method generates the corresponding database table when you use the database for the first time. However, this method is not called when instantiating the SQLiteOpenHelper class object, but getReadableDatabase () is called through the object () or getWriteableDatabase () method. 2.2 onUpgrade () This method is called when the database is upgraded. Generally, you can delete the data table in this method. After deleting the table, the onCreate () method is often called to re-create a new data table. 2.3 onopen () This method is called when the database is opened, but generally users do not need to rewrite this method.
3. Sample Code 3.1 for adding, deleting, modifying, and querying databases in Android is as follows:
3.2 The host code is as follows:

Package com. example. contentvaluespractice; import android. app. activity; import android. content. contentValues; import android. database. cursor; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteOpenHelper; import android. OS. bundle; import android. view. view; public class MainActivity extends Activity {private SQLiteDatabase db; @ Override protected void onCreate (Bundle savedI NstanceState) {super. onCreate (savedInstanceState); setContentView (R. layout. activity_main); SQLiteOpenHelper helper = new DatabaseHelper (this); // SQLiteOpenHelper help class for database operations // The oncreate method is executed to return to the database. Db databases can be created only when the getWritetableDatabase () or getReadableDatabase () method is executed and the Oncreate () method is executed. getWritableDatabase ();} // you can insert data to a database using an SQL statement or a public void insert (View view) method provided by android) {// SQL statement Method for Data insertion String SQL = "insert into personData (name, age, birthday) values ('dmk', null, '2017-9-10 ')"; // run the SQL statement to insert db.exe cSQL (SQL ); // method 2 provided by android for data insertion // use ContentValues to perform the insert operation ContentValues, which is equivalent to a Map in java with a key-Value Pair Form ContentValues cv = new ContentValues (); cv. put ("name", "dmj"); cv. put ("age", 26); db. insert ("personData", null, cv);} // you can delete data from a database by using an SQL statement, one is to use the public void delete (View view) {// 1. string whereClause = "name =? "; // Deletion condition String [] whereArgs = {" dmk "}; db. delete ("personData", whereClause, whereArgs); // 2. execSQL is used to implement String SQL = "delete from personData where name = 'dmj'"; db.exe cSQL (SQL );} // two methods for updating data in the database: public void update (View view) {// 1. use ContentValues cv = new ContentValues (); cv. put ("name", "df"); // Add the field and content to be changed // String whereClause = "name =? "; // String [] whereArgs = {" dmk "}; // db. update ("personDate", cv, whereClause, whereArgs); db. update ("personData", cv, "name =? ", New String [] {" dmk "}); // 2. execSQL: String SQL = "update [personData] set age = 20 where name = 'dmj'"; db.exe cSQL (SQL); // execSQL () the method cannot return a value.} // query data 1/** public Cursor query (String table, String [] columns, String selection, String [] selectionArgs, string groupBy, String having, String orderBy, String limit) parameter description: table name colums: column name array selection: Condition Clause, equivalent to where example "name =? "SelectionArgs: An Example of the parameter array of the Condition Statement: new String [] {" dmk "} groupBy: group having: group condition orderBy: Sorting class limit: pagination query restriction Cursor: return value, equivalent to ResultSet */public void query (View view) {Cursor cs = db. query ("personData", null, null); if (cs. moveToNext () {for (int I = 0; I <cs. getCount (); I ++) {cs. move (I); String name = cs. getString (cs. getColumnIndex ("name"); int age = cs. getInt (cs. getColumnIndex ("age"); Sy Stem. out. println (name + ":" + age); System. out. println (cs. getColumnIndex ("name"); System. out. println (cs. getColumnIndex ("age"); System. out. println (cs. getColumnCount () ;}}// Data Query 2 // query with parameters implemented through rawQuery/* Cursor c = db. rawQuery ("select * from user where username =? ", New Stirng [] {" Jack Johnson "}); if (cursor. moveToFirst () {String password = c. getString (c. getColumnIndex ("password"); */public void rawquery (View view) {Cursor c = db. rawQuery ("select * from personData where name =? ", New String [] {" dmj "}); if (c. moveToNext () {int age = c. getInt (c. getColumnIndex ("age"); System. out. println (age );}}}

3.3DatabaseHelpter class code:

<Pre name = "code" class = "java"> <pre name = "code" class = "java"> package com. example. contentvaluespractice; import android. content. context; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteOpenHelper; public class DatabaseHelper extends SQLiteOpenHelper {private static final String DB_NAME = "person. db "; // database name private static final int version = 1; // database version public Databas EHelper (Context context) {super (context, DB_NAME, null, version) ;}@ Override public void onCreate (SQLiteDatabase db) {// This method is only available when getWritableDatabase () is called () or the getReadableDatabase () method will execute String SQL = "create table personData (" + "id integer primary key," + "name varchar (20) not null, "+" age Integer, "+" birthday date) "; db.exe cSQL (SQL);} // if the value of DATABASE_VERSION is changed to 2, the system finds that the current database version is different, onUpgrade @ Override Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {db.exe cSQL ("........ ");}}

4. Method for cursor

Method Name	Method description
GetCount ()	Total number of records
IsFirst ()	Determine if the first record is used
IsLast ()	Determine whether the last record is used
MoveToFirst ()	Move to the first record
MoveToLast ()	Move to the last record
Move (int offset)	Move to the specified record
MoveToNext ()	Move to scare record
MoveToPrevious ()	Move to the previous record
GetColumnIndex (String columnName)	Obtains the int type value of the specified column index.

5 Contenvalues considerations

A Construction Method of insert

Public long insert (String table, String nullColumnHack, ContentValues values)

 

Table

Name of the table to insert data

 

Values

ContentValues object, similar to a map that stores values through key-value pairs.

 

NullColumnHack

When the values parameter is null or there is no content in it, insert will fail (the underlying database cannot insert a blank row). To prevent this, you must specify a column name here, if you find that the row to be inserted is empty, the column name you specified is set to null and then inserted into the database. By observing the insertWithOnConflict method of the source code, we can see that when initialValues of the ContentValues type is null or size <= 0, nullColumnHack settings will be added to the SQL statement.

If nullColumnHack is not added, the final result of the SQL statement is similar to insert into tableName () values (NULL); this is not allowed.

If nullColumnHack is added, the SQL statement will change to insert into tableName (nullColumnHack) values (null); this is acceptable.