SQLite operations in Android

I. Introduction to SQLite
1. SQLite is a lightweight database that complies with ACID-related database management systems. It is designed to be embedded and has been used in many embedded products, this type of database occupies very low resources. In embedded devices, it may only require several hundred KB of memory. It can support mainstream operating systems such as Windows, Linux, and Unix, at the same time, it can be combined with many programming languages, such as PHP, Java, C ++, and ,. and ODBC interfaces. Compared with MySql and PostgreSQL, the two well-known open-source database management systems, the processing speed is faster than that of the two.
2. Features of SQLite
A. Lightweight:
SQLite is different from the C/S database software. It is a database engine in the process, so there is no database client or server. All the functions of SQLite can be used as long as it carries a dynamic library. The size of the dynamic library is also very small. Take version 3.6.11 as an example. The size of the dynamic library is 487 K in Windows and 347 K in Linux.
B. installation is not required:
SQLite's core engine does not require any third-party software or installation, which is similar to green software.
C. Single file:
All information in the database (such as tables and views) is contained in a single file. This file can be freely copied or moved to another machine.
D. Cross-platform/portability
In addition to mainstream operating systems such as Windows and Linux, SQLite also supports some other uncommon operating systems.
E. Weak Fields
Data in the same column can be of different types
F. Open Source
I believe everyone understands this.
3. SQLite Data Type
Generally, the database uses a fixed static data type, while SQLite uses a dynamic data type, that is, it will automatically judge the value type based on the saved value. SQLite has the following common data types:
Null: The value is null.
Varchar (n): A string with an unfixed length and a maximum of n, n <= 4000
Char (n): string with a fixed length of n, n <= 254
Integer: indicates the integer type.
Real: All values are floating values and are stored as 8-byte IEEE floating mark numbers.
The text value is a text string stored using database encoding (TUTF-8, UTF-16BE or UTF-16LE)
Blob: The value is a blob data block. It is stored in the input data format and stored as input without changing the input format.
Data: contains the year, month, and date in the format of XX-XX-XX (that is, year-month-day)
Time: Contains hours, minutes, And seconds. Format: XX (that is, hours: Minutes: seconds)
Ii. SQLiteDatabase Introduction
Android provides APIs for creating and using SQLite databases. SQLiteDatabase is a database object and provides methods to operate databases. In the android SDK directory, there is a sqlite3 tool that can be used to create databases, create tables, and execute some SQL statements. The following are common SQLiteDatabase methods.
Common SQLiteDatabase methods:

 

Method Name	Method description	Parameter description
OpenOrCreateDatabase (String path, SQLiteDatabase. CursorFactory factory)	Open or create a database	Path: Database path, factory: cursor location (that is, the row)
Insert (Sting table, String nullColumnHack, CntentValues values)	Add a record	Table: table name. nullColumnHack transfers null values. values: value to be modified (values. put (xx, xx), in the form of a key-Value Pair)
, Delete (String table, String whereClause, String [] whereArgs)	Delete a record	Table: Same as above. whereClause: where clause: A Condition in an SQL statement, for example, "id =? ", Indicates deletion by id, whereArgs: the deletion condition. For example, new String [] {" 1 "} indicates deletion of records with id = 1 (or new String. valueof (1), which indicates converting 1 to a string)
Query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy)	Query a record	Table: Same as above, columns: name of the column to be queried. Note that the last orderBy statement is executed based on the values in columns. For example, to sort by MyTime, the name of a column in MyTime must be in columns. Selection: equivalent to the where clause. SelectionArgs: Condition for execution. groupBy: group. Having: determines which columns are to be included in the cursor path, that is, if they are included in the path, they can be scanned by the cursor, or vice versa. OrderBy: sort the queried information.
Update (String table, ContentValues values, String whereClause, String [] whereArgs)	Modify record	Table: Same as above. Values: the data to be updated (in the form of a key-Value Pair ). WhereClause: WHERE clause. WhereArgs: Execution Condition
ExcSQL (string SQL)	Execute an SQL statement	SQL: SQL statement to be executed
Close ()	Close Database	No Parameter

 

To facilitate operations (such as updating databases), a subclass is usually derived from the SQLiteOpenHelper helper class.
1. A subclass is derived from the SQLiteOpenHelper helper class. (DateBaseHelper. java)
Public class DateBaseHelper extends SQLiteOpenHelper {
/**
* This constructor must be included in the subclass of SQLiteOpenHelper.
* @ Param context Current Activity
* @ Param name: name of the table (instead of the database name, this class is used to operate the database)
* @ Param factory is used to return the child class of Cursor when querying the database and pass a null value
* @ Param version refers to the current database version, which is an integer and increases accordingly.
*/
Public DateBaseHelper (Context context, String name, CursorFactory factory, int version)
{
Super (context, name, factory, version); // inherits the parent class
// TODO Auto-generated constructor stub
}
/**
* This function is executed when a database is created for the first time. Only when getreadabledatebase () is called ()
* Or getwrittleabledatebase (). This function is executed only when the first database is created.
*/
@ Override
Public void onCreate (SQLiteDatabase db)
{
/**
* Create a table named StudentInfo
* Id: primary key, which is automatically added and numbered
* Name: Student name
* ClassId: Class
* StudyId: Student ID
* Sex: Gender
* MyTime: admission time
*/
// TODO Auto-generated method stub
String SQL = "create table StudentInfo (id integer primary key autoincrement, name varchar (20), sex varchar (20), MyTime Time, MyDate Date )";
Db.exe cSQL (SQL );
}
/**
* Database update function. This function is executed when the database is updated.
*/
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
{
// TODO Auto-generated method stub
System. out. println ("database updated ");
/**
* Adding and updating a database here is the operation to be performed.
*/
}
}
2. implement various functions (MySqliteActivity. java)
A. Create A database www.2cto.com
Code:
// MySqliteActivity. this: device context, "student": Database Name (note that the name is different from the table name), 1: database version number
DateBaseHelper dbHelper = new DateBaseHelper (MySqliteActivity. this, "student", null, 1 );
DbHelper. getReadableDatabase (); // get a readable database. Note that the database can be created only after this statement is executed. That is, the above result shows that the object of the DateBaseHelper helper class does not actually create a database.
 
B. update the database
The so-called database update means that the database version number is increased upwards. Note that the database version number can only be an integer and increase progressively, for example, 1, 2, 3.
Code:
DateBaseHelper dbHelper = new DateBaseHelper (getApplicationContext (), "student", null, 2 );
DbHelper. getReadableDatebase ();
 
C. insert data (Add Table Records)
Code:
Time time = new Time ("GMT + 8") // set the Time to the eighth standard time zone.
Time. setToNow (); // set it to the current system time.
Int year = time. year;
Int month = time. month;
Int day = time. monthDay;
Int hour = time. hour;
Int minute = time. minute;
Int second = time. second;
DateBaseHelper db = new DateBaseHelper (getApplicationContext (), "student", null, 1 );
SQLiteDateBase db = dbHelper. getWritableDatabase (); // note that a writable database is obtained because data is inserted.
ContentValues values = new ContentValues ();
Values. put ("name", "zhangsan ");
Values. put ("sex", "male ");
Values. put ("MyTime", year + "-" + month + "-" + day );
Values. put ("MyDate", hour + ":" + minute + ":" + sec );
Db. insert ("StudentInfo", null, values );
Toast. makeText (MySqliteActivity. this, "data inserted successfully", Toast. LENGTH_SHORT). show ();
 
D. modify data
/**
* To modify the data, find the corresponding record based on certain conditions and modify the record.
*/
Code:
DateBaseHelper dbHelper = new DateBaseHelper (getApplicationContext (), "student", 1 );
SQLiteDatabase db = dbHelper. getReadableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "lisi ");
Values. put ("sex", "female ");
Values. put ("MyTime", "2015-6-6 ");
Values. put ("MyDate", "21:21:12 ");
String whereClause = "id =? ";
String [] whereArgs = {"8 "};
Db. update ("StudentInfo", values, whereClause, whereArgs );
Toast. makeText (getApplicationContext, "the eighth row of data has been updated", Toast. LENGTH_SHORT). show ();

E. query data
Code:
DateBaseHelper dbHelper = new DateBaseHelper (getApplicationContext (), "student", null, 1 );
SQLiteDatebase db = dbHelper. getReadableDatabase (); // obtain a readable database.
Cursor cursor = db. query ("StudentInfo", new String [] {"id", "name", "sex", "MyDate"}, "name =? ", New String [] {" zhangsan "}, null, null," MyDate "); // query returns a cursor object.
While (cursor. moveToNext () // moveToNext
{
String name = cursor. getString (cursor. getColumnIndex ("MyDate"); // obtain the value of MyDate (that is, the date value)
Toast. makeText (getApplicationContext (), name, Toast. LENGTH_SHORT). show ();
}

F. delete data (database)
Code:
DateBaseHelper dbHelper = new DateBaseHelper (getApplicationContext (), "student", null, 2 );
SQLiteDatabase db = dbHelper. getReadableDatabase ();
String whereClause = "id =? "//? As a placeholder
String [] whereArgs = {"7"}; // Delete the row of data whose id is 7
Db. delete ("StudentInfo", whereClause, whereArgs );
Toast. makeText (getApplicationContext (), "The Seventh data has been deleted", Toast. LNEGTH_SHORT). show ();
// DeleteDatabase ("student"); // delete a database (Note: delete a database instead of deleting data in the database)


From the prime time