Android development tutorial-data storage (2) SQLite

Hi everyone!

Today we will focus on the usage of SQLite in Android.

Easy:

The office room and the programmer in the office room;
Programmers write programs and exchange wine with the program.
When you wake up, you just sit online and get drunk and stay asleep;
Drunk wine wake up day after day, online next year after year.
I hope the computer room will never bow to the boss;
Mercedes Benz and BMW are fun and programmers on the bus.
When others laugh at me, I laugh at myself;
I don't see the beautiful girl on the street, which is a programmer. Ah...

SQLite Introduction

Most applications require data operations. Android applications are no exception. Where should local data be stored? Android uses an open-source SQL database unrelated to the operating system-the well-known SQLite database. SQLite is a lightweight database designed for embedded systems and occupies a very small amount of resources. In embedded devices, only a few hundred KB is required, many large companies develop products that exist.

Lightweight
To use SQLite, you only need to bring a dynamic library to enjoy all its functions, and the size of the dynamic library is quite small.

Independence
The core engine of the SQLite database does not need to rely on third-party software or be "installed ".

Isolation
All information (such as tables, views, and triggers) in the SQLite database is contained in a folder for convenient management and maintenance.

Cross-platform
SQLite currently supports most operating systems, not only computer operating systems but also many mobile phone systems, such as Android.

Multilingual Interface
The SQLite Database supports multi-language programming interfaces.

Security
SQLite databases implement independent transaction processing through database-level exclusivity and shared locks. This means that multiple processes can read data from the same database at the same time, but only one can write data.

Although SQLite is small, the supported SQL statements are not inferior to those of other open-source databases. The supported SQL statements include:

Sqlite basic data types include:

VARCHAR character type

NVARCHAR (15) mutable,

TEXT,

INTEGER,

FLOAT,

BOOLEAN,

CLOB character large object,

BLOB Binary large object,

TIMESTAMP date type,

NUMERIC (10, 5) NUMERIC type

Varying character (24 ),

National varying character (16)

Sqlite also provides JDBC drivers

Class. forName ("org. sqlite. JDBC ");

Connection conn = DriverManager. getConnection ("jdbc: sqlite: filename ");

// Filename is your SQLite data name

Use SQLiteOpenHelper to operate databases

GetReadableDatabase () to obtain readable SQLiteDatabase

GetWritableDatabase () to obtain writable SQLiteDatabase

OnCreate (SQLiteDatabase)

OnDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)

OnUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)

Database Operations

1. encapsulate an SQLite operation class and use the method inheriting SQLiteOpenHelper here

SQLiteOpenHelper
SQliteOpenHelper is an abstract class used to manage database creation and version management. To use it, you must implement its

OnCreate (SQLiteDatabase ),

OnUpgrade (SQLiteDatabase, int, int) Method
OnCreate: the database is executed when it is created for the first time, such as creating tables and initializing data.
OnUpgrade: it is executed when the database needs to be updated, such as deleting a long-time table and creating a new table.

2. Implement the constructor and abstract method of the SQLiteOpenHelper class.

Package TSD. Jason. DB;

Import android. content. ContentValues;
Import android. content. Context;
Import android. database. Cursor;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Import android. database. sqlite. SQLiteDatabase. CursorFactory;

Public class DBHelp extends SQLiteOpenHelper {

Private final static String DATABASE_NAME = "StudentDB"; // Database Name
Private final static int DATABASE_VERSION = 1; // default database version
Private final static String TABLE_NAME = "StudentInfo"; // data table name
Public final static String S_ID = "sid"; // column name
Public final static String S_NAME = "sName ";
Public final static String S_SEX = "sSex ";
Public final static String S_AGE = "sAge ";
Public final static String S_ADDRESS = "sAddress ";

/** SQLiteOpenHelper class Constructor (Note: required constructor)
*
* This constructor is used to create databases and tables. (If NO database or table exists during the first execution, a database or table will be created and will not be created in the future)
* @ Param context object
* @ Param name: Database name
* @ Param factory create the factory class of Cursor. The parameter is used to create a custom Cursor.
* @ Param version: database version number
*/
Public DBHelp (Context context, String name, CursorFactory factory,
Int version ){
Super (context, name, factory, version );

}

/** During data operations (add, delete, modify, and query)
*
* @ Param context object
* @ Param name: Database name
* @ Param factory create the factory class of Cursor. The parameter is used to create a custom Cursor.
* @ Param version: database version number
*/
Public DBHelp (Context context ){
This (context, DATABASE_NAME, null, DATABASE_VERSION );
}

/** You need to modify it based on the version number.
*
* This constructor is used to modify databases and tables (modify existing databases and tables)
* @ Param context object
* @ Param name: Database name
* @ Param factory create the factory class of Cursor. The parameter is used to create a custom Cursor.
* @ Param version: database version number
*/
Public DBHelp (Context context, int version ){
This (context, DATABASE_NAME, null, version );
}

@ Override
Public void onCreate (SQLiteDatabase db ){
// TODO Auto-generated method stub
System. out. println ("automatically executed when the database is run or modified for the first time ");

String createTableSQL = "create table" + TABLE_NAME + "("
+ S_ID + "integer primary key autoincrement ,"
+ S_NAME + "nvarchar (10 ),"
+ S_SEX + "nvarchar (2 ),"
+ S_AGE + "int ,"
+ S_ADDRESS + "nvarchar (50 )"
+ ")";
Db.exe cSQL (createTableSQL); // execSQL () is required for SQL Execution ()
}

@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
System. out. println ("------------------------- a modified library function is called ");

}

@ Override
Public void onOpen (SQLiteDatabase db ){
// TODO Auto-generated method stub
Super. onOpen (db );
System. out. println ("executed when the database is opened ");
}



}

 

Create a database

DBHelp dbHelp = new DBHelp (SQLiteActivity. this );
// When the SQLiteDatabase object is obtained, the system automatically checks whether the database and table are created for the first time. If the database and table are not created, the onCreate function in the DBHelp class is executed.
SQLiteDatabase db = dbHelp. getReadableDatabase ();
Toast. makeText (SQLiteActivity. this, "created successfully", Toast. LENGTH_SHORT). show ();

GetWritableDatabase () if you need to operate (add, delete, modify) The database, use

GetReadableDatabase () query data

Delete and rename a database

 

 

View the database under adb

1. Use the ls command to query the directory structure

2. Enter the corresponding folder by using the cd folder name.

3. Enter the data folder twice.

4. Go to the package where you created the project.

5. Enter databases

6. Access the database through sqlite3 StudentDB

At the sqlite> prompt, enter

The. help Command shows many commands.

. Tables view all tables

You can use the. tables command to query all table names in the database.

The query statement is the same as that in SQL Server. A semicolon is required for the end of each SQL statement;

Add data

Btn5.setOnClickListener (new Button. OnClickListener (){
@ Override
Public void onClick (View v ){

DBHelp dbHelp = new DBHelp (SQLiteActivity. this );
SQLiteDatabase db = dbHelp. getWritableDatabase ();
ContentValues contentValues = new ContentValues ();
ContentValues. put ("sName", txtName. getText (). toString ());
ContentValues. put ("sSex", "male ");
If (dbHelp. InsertData (db, contentValues ))
Toast. makeText (SQLiteActivity. this, "added successfully", Toast. LENGTH_SHORT). show ();
Else
Toast. makeText (SQLiteActivity. this, "failed to add", Toast. LENGTH_SHORT). show ();
}
});

Modify data

Btn6.setOnClickListener (new Button. OnClickListener (){
@ Override
Public void onClick (View v ){

DBHelp dbHelp = new DBHelp (SQLiteActivity. this );
SQLiteDatabase db = dbHelp. getWritableDatabase ();
ContentValues contentValues = new ContentValues ();
ContentValues. put ("sName", txtsName. getText (). toString ());
String whereClause = "sid =? "; // Use the condition value of the data to be modified? Placeholder
String [] whereArgs = new String [] {txtsID. getText (). toString ()};
If (dbHelp. UpdateData (db, contentValues, whereClause, whereArgs ))
Toast. makeText (SQLiteActivity. this, "modified successfully", Toast. LENGTH_SHORT). show ();
Else
Toast. makeText (SQLiteActivity. this, "failed to modify", Toast. LENGTH_SHORT). show ();
}
});

Delete data

Btn7.setOnClickListener (new Button. OnClickListener (){
@ Override
Public void onClick (View v ){

DBHelp dbHelp = new DBHelp (SQLiteActivity. this );
SQLiteDatabase db = dbHelp. getWritableDatabase ();
String whereClause = "sid =? "; // Use the condition value of the data to be deleted? Placeholder
String [] whereArgs = new String [] {txtID. getText (). toString ()};
If (dbHelp. DeleteData (db, whereClause, whereArgs ))
Toast. makeText (SQLiteActivity. this, "deleted successfully", Toast. LENGTH_SHORT). show ();
Else
Toast. makeText (SQLiteActivity. this, "failed to delete", Toast. LENGTH_SHORT). show ();
}
});

Query data

RawQuery ()

RawQuery () is the simplest solution. For example:

Cursor c = db. rawQuery (
"SELECT * FROM Student WHERE sid = 1 AND sname = 'hangsan'", null );

Query ()

A Cursor will be returned, which is the SQLite database Cursor of Android.

Operation cursor

 

Use the getCount () method to obtain the number of records in the result set. Use the moveToFirst (), moveToNext (), and isAfterLast () Methods to traverse all records. Use getColumnNames () to obtain the field name; use getColumnIndex () to convert a field to a field number, use getString (), getInt (), and other methods to obtain the value of the current record of a given field, and use the requery () method to re-execute the query to obtain the cursor; use the close () method to release cursor resources; SimpleCursorAdapterSimpleCursorAdapter can use this class to bind ListView

SimpleCursorAdapter simple = new SimpleCursorAdapter (SQLiteActivity. this, android. r. layout. simple_list_item_1, cur, new String [] {"sName", "sSex"}, new int [] {android. r. id. text1, android. r. id. text2 });

StudentList. setAdapter (simple );

Note: When using this class to bind data, you must pay attention to the primary key name of sqlite. Because simpleCursorAdapter only recognizes _ id, when you use simpleCursorAdapter of sqlite, you must name the primary key of the data table as _ id. Otherwise, the java. lang. IllegalArgumentException: column '_ id' does not exist error occurs.

Query data

Public void BinderData (){
DBHelp dbHelp = new DBHelp (SQLiteActivity. this );
SQLiteDatabase db = dbHelp. getReadableDatabase ();
/* Parameters
* Table Name
* Columns: array of column names to be queried. All columns use null.
* Selection the condition to be queried does not use null
* The value corresponding to the selectionArgs condition to be queried is not null.
* The groupBy group does not use null.
* Null is not used for filtering again after having grouping
* OrderBy sorting does not use null.
**/
Cursor cur = db. query ("StudentInfo", null );
// Cursor cur = db. rawQuery ("select * from StudentInfo", null );

// Because the primary key name used in this example is not _ id, SimpleCursorAdapter cannot be used directly.
// SimpleCursorAdapter simple = new SimpleCursorAdapter (SQLiteActivity. this, android. r. layout. simple_list_item_1, cur, new String [] {"sName", "sSex"}, new int [] {android. r. id. text1, android. r. id. text2 });
// StudentList. setAdapter (simple );
ArrayList <HashMap <String, Object> students = new ArrayList <HashMap <String, Object> ();
HashMap <String, Object> hs;
For (cur. moveToFirst ();! Cur. isAfterLast (); cur. moveToNext ()){
System. out. println (cur. getInt (cur. getColumnIndex ("sid") + "aaaaaaaaa ");
Hs = new HashMap <String, Object> ();
Hs. put ("sID", cur. getInt (cur. getColumnIndex ("sid ")));
Hs. put ("sName", cur. getString (cur. getColumnIndex ("sName ")));
Hs. put ("sSex", cur. getString (cur. getColumnIndex ("sSex ")));
Students. add (hs );
}
SimpleAdapter sim = new SimpleAdapter (SQLiteActivity. this, students, android. r. layout. simple_list_item_1, new String [] {"sName", "sSex"}, new int [] {android. r. id. text1, android. r. id. text2 });
StudentList. setAdapter (sim );
}

The Code has been uploaded to the Beijing tianshengda website. You are welcome to download it.