Details about features and concepts of the SQLite database in android!

Details about features and concepts of the SQLite database in android!

2. Database Operation Process
[Usage conditions] Prepare a DATABASE system (MySQL) --> Create a DATABASE --> Create/design a TABLE --> Data Operations
[Data operations] Connection --> prepare SQL statements (String SQL) --> Execute SQL statements (Statement/PreparedStatement) --> processing result (int/ResultSet)




3. Use SQLite
1) Create a database
OpenOrCreateDatabase (String name, int mode, CursorFactory factory)
String name: name of the database file, such as users. db. SQLite can be used without requiring the database file extension.
Int mode: the access mode. constants should be used.
CursorFactoty factory: cursor factory, usually null.
After the database is created, it is saved under/data/package name/databases /.


2) create a data table
The return value of openOrCreateDatabase () is the SQLiteDatabase object. The execSQL (String SQL) method of this object can be used to execute SQL commands.
Create table dictionary (_ id integer primary key autoincrement, en VARCHAR (50) not null unique, zh VARCHAR (50) not null );


3) Data Operations
ExecSQL (String SQL) is used to execute a single SQL syntax without variables, that is, the SQL syntax does not require pre-compilation. It is usually represented by operations related to data tables. execSQL (String SQL, object [] bindArgs) is used to perform operations related to the data itself.
The execSQL () method does not return values. It cannot be used for queries, nor is it recommended for adding, modifying, or deleting operations.
Android officially recommends that execSQL () not be used to perform any data operations for addition, deletion, modification, and query.


4) understand the ContentValues class
In data operations, insert/update... this type of data is required for the method. In essence, HashMap is operated. The Key corresponds to the field name in the data table, and the Value corresponds to the data of the field in the operation.


5) Use db. insert (String table, String nullColumnHack, ContentValues values) to insert records
String table: table Name
String nullColumnHack: required only when the value of the 3rd parameter values is null or no value (values. size = 0). It is used to preserve that the SQL statement is complete.
ContentValues values: configure the field to be operated and the Field Value
Returned value:-1 is returned when an error occurs. Otherwise, the ID of the inserted record is returned.


6) Use db. update (table, values, whereClause, whereArgs) to modify records
String table: table Name
ContentValues values: Same as above
WhereClause: where clause. Do not add the "where" character. The value of this parameter is "_ id =? Or (username =? And password = ?) "
WhereArgs: the parameter in the where clause, which must match the question mark in whereClause.
Returned value: Number of affected rows


7) Use db. delete (String table, String whereClause, String [] whereArgs) to delete records
The parameters are described above.
Returned value: Number of affected rows


8) query records using db. query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit)
String table: table Name
String [] columns: Field List
String selection: see the previous whereClause
String [] selectionArgs: see the previous whereArgs
String groupBy: Group By clause
String having: Having clause
String orderBy: Order By (SORT) clause
String limit: limit (paging) clause


9) know Cursor
Similar to the JDBC ResultSet Interface
Master the getXXXX () and moveXXXX () Methods


10) know SQLiteOpenHelper
Constructor Parameters
Context context: Context object
String name: Database name, file name, for example, tarena. db
CursorFactory factory: cursor factory. The value is null.
Int version: The current version. It cannot be earlier than the previous version number of the same application.

OnCreate (): executed when the database is created
OnUpgrade (): executed during database version upgrade

GetReadableDatabase () and getWritableDatabase (). getReadableDatabase () tries to obtain the writable SQLiteDatabase. However, when the storage space is insufficient, it returns the read-only SQLiteDatabase to avoid program errors.


11) SimpleCursorAdapter
Use Cursor as the SimpleAdapter of the data source. The Cursor must contain the _ id field. The value of the String [] from parameter is ColumnName in Cursor.



** Char and varchar in the database
Char is fixed length. If the length of a string is not enough, fill in spaces on the right of the original character. For example, if char (8) is saved as "xyz ", varchar is variable-length, and the length is determined by the string itself.

** SQLite can accept any data type for any field in the database that does not have strict requirements on the data type. However, it is not recommended to fill in the data at will.