Android-use SQLiteDatabase to operate SQLite Databases

Android-use SQLiteDatabase to operate SQLite Databases
In addition to using files or SharedPreferences to store data, you can also choose to use the SQLite database to store data. On the Android platform, an embedded relational database-SQLite is integrated. SQLite3 supports NULL, INTEGER, REAL (floating point number), TEXT (string TEXT), and BLOB (binary object) data types, although it supports only five types, sqlite3 actually accepts data types such as varchar (n), char (n), decimal (p, s, it is only converted to the corresponding five data types during operation or storage. The biggest feature of SQLite is that you can save various types of data to any field without worrying about the data type declared by the field. For example, you can store strings in Integer fields, floating point numbers in Boolean fields, or date values in numeric fields. But there is one exception: the field defined as integer primary key can only store 64-bit integers. When saving data other than Integers to this field, an error will occur. In addition, when parsing the create table statement, SQLite ignores the data type information following the field name in the create table statement. For example, the following statement ignores the type information of the name field: create table person (personid integer primary key autoincrement, name varchar (20) SQLite can parse most of the standard SQL statements, such as: query statement: select * from table name where Condition Clause group by grouping statement having... for example, select * from person order by id desc select name from person group by name having count (*)> 1. The paging SQL statement is similar to mysql, the following SQL statement Get 5 Records, skip the first 3 Records select * from Account limit 5 offset 3 or select * from Account limit 3 or 5 insert statement: insert into Table Name (Field List) values (Value List ). For example, insert into person (name, age) values ('chuanzhi ', 3) update statement: update table name set field name = value where Condition Clause. For example, update person set name = 'chuanzhi 'where id = 10 delete statement: delete from table name where Condition Clause. For example: delete from person where id = 10l when writing database application software, we need to consider this problem: because the software we developed may be installed on mobile phones of many users, if the application uses the SQLite database, we must create the database table structure used by the application and add some initialization records when the user first uses the software. In addition, when the software is upgraded, you also need to update the data table structure. Then, how can we automatically create the database tables required by the application on the user's mobile phone when the user first uses or upgrades the software? We cannot manually create database tables on every mobile phone that requires the software to be installed? This requirement is required for every database application. Therefore, in the Android system, an abstract class named SQLiteOpenHelper is provided, which must be inherited before it can be used, it manages the database version to meet the previous requirements. To manage database versions, the SQLiteOpenHelper class provides two important methods: onCreate (SQLiteDatabase db) and onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ), the former is used to generate a database table when you first use the software, and the latter is used to update the database table structure when you upgrade the software. When the getWritableDatabase () or getReadableDatabase () method of SQLiteOpenHelper is called to obtain the SQLiteDatabase instance used to operate the database, if the database does not exist, the Android system automatically generates a database and then calls onCreate () method. The onCreate () method is called only when the database is generated for the first time. In the onCreate () method, you can generate the database table structure and add the initialization data used by some applications. The onUpgrade () method is called when the database version changes. Generally, the version number needs to be changed during software upgrade. The database version is controlled by programmers, assume that the current database version is 1 and the database table structure is modified due to business changes. In this case, you need to upgrade the software to update the database table structure on your mobile phone, to achieve this goal, you can set the original database version to 2 (some people may ask if it is set to 3 rows? Of course, if you want to, set it to 100) and update the table structure in the onUpgrade () method. When the number of software version upgrades is large, you can use the onUpgrade () method to determine based on the original number and target version number, and then update the table structure and data. Both the getWritableDatabase () and getReadableDatabase () methods can obtain a SQLiteDatabase instance used to operate the database. However, the getWritableDatabase () method opens the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write data. If the getWritableDatabase () method is used, an error occurs. The getReadableDatabase () method first opens the database in read/write mode. If the disk space of the database is full, it will fail to be opened. When the opening fails, it will continue to attempt to open the database in read-only mode. Lpublic class DatabaseHelper extends SQLiteOpenHelper {// The class is not instantiated. It cannot be used as a parameter of the parent class constructor and must be declared as static private static final String name = "itcast "; // database name private static final int version = 1; // database version public DatabaseHelper (Context context) {// The third parameter CursorFactory specifies the factory class for obtaining a cursor instance during query execution. If it is set to null, it indicates that the system uses the default factory class super (context, name, null, version) ;}@ Override public void onCreate (SQLiteDatabase db) {db.exe cSQL ("CREATE TAB Le if not exists person (personid integer primary key autoincrement, name varchar (20), age INTEGER) ") ;}@ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {db.exe cSQL ("drop table if exists person"); onCreate (db) ;}} above onUpgrade () method when the database version changes, the database table on the user's mobile phone is deleted and re-created. This is generally not possible in actual projects. The correct method is to consider that the data stored in the database will not be lost when updating the database table structure. L



========================================================== ========================================================== = /**

*@ AuthorAdministratorYangchao

* To manage database versions, the SQLiteOpenHelper class provides two important methods,

* They are onCreate (SQLiteDatabaseDb) And onUpgrade (SQLiteDatabaseDb,IntOldVersion,IntNewVersion)

*/

Public ClassDBOpenHelperExtendsSQLiteOpenHelper

{

Private Static FinalStringDatabaseName= "Test. db"; // Database Name

Private Static Final Int DatabaseVersion= 1; // database version

PublicDBOpenHelper (Context context)

{

Super(Context,DatabaseName,Null,DatabaseVersion);

}

/*

* Used to generate database tables when software is used for the first time

*/

@ Override

Public VoidOnCreate (SQLiteDatabase db)

{

Db.exe cSQL ("create table person (personId integer primary key autoincrement, name varchar (20 ))");

}

/*

* Updates the database table structure during software upgrade.

* Called only when the database version changes

*/

@ Override

Public VoidOnUpgrade (SQLiteDatabase db,IntOldVersion,IntNewVersion)

{

}

}

Certificate ------------------------------------------------------------------------------------------------------------------------------------

Public ClassPersonService

{

DBOpenHelper dbOpenHelper;

PublicPersonService (){}

PublicPersonService (Context context)

{

This. DbOpenHelper =NewDBOpenHelper (context );

}

/**

* Save Method

*@ ParamPerson

*/

Public VoidSave (Person person)

{

SQLiteDatabase database = dbOpenHelper. getWritableDatabase ();

String SQL = "insert into person (personId, name) values (?, ?) ";

Database.exe cSQL (SQL,NewObject [] {person. getPersonId (), person. getName ()});

}

/**

* Change Method

*@ ParamPerson

*/

Public VoidUpdate (Person person)

{

SQLiteDatabase database = dbOpenHelper. getWritableDatabase ();

String SQL = "update person set name =? Where personId =? ";

Database.exe cSQL (SQL,NewObject [] {person. getName (), person. getPersonId ()});

}

/**

* Deletion Method

*@ ParamPerson

*/

Public VoidDelete (Person person)

{

SQLiteDatabase database = dbOpenHelper. getWritableDatabase ();

String SQL = "delete from person where personId =? ";

Database.exe cSQL (SQL,NewObject [] {person. getPersonId ()});

}

/**

* Search for a single record

*@ ParamId

*@ Return

*/

PublicPerson find (Integer id)

{

SQLiteDatabase database = dbOpenHelper. getReadableDatabase ();

String SQL = "select * from person where personId =? ";

Cursor cursor = database. rawQuery (SQL,NewString [] {id. toString ()});

If(Cursor. moveToFirst ())

{

String name = cursor. getString (cursor. getColumnIndex ("name "));

IntPersonId = cursor. getInt (cursor. getColumnIndex ("personId "));

Return NewPerson (personId, name );

}

Return Null;

}

/**

* Query by PAGE

*@ ParamOffset

*@ ParamMaxResult

*@ Return

*/

PublicList GetScrollData (Integer offset, Integer maxResult)

{

List Persons = NewArrayList ();

SQLiteDatabase database = dbOpenHelper. getReadableDatabase ();

String SQL = "select * from person limit ?, ? ";

Cursor cursor = database. rawQuery (SQL,NewString [] {offset. toString (), maxResult. toString ()});

While(Cursor. moveToNext ())

{

IntPersonId = cursor. getInt (cursor. getColumnIndex ("personId "));

String name = cursor. getString (cursor. getColumnIndex ("name "));

Persons. add (NewPerson (personId, name ));

}

ReturnPersons;

}

/**

* Retrieve the total number of records

*@ Return

*/

Public LongGetCount ()

{

SQLiteDatabase database = dbOpenHelper. getReadableDatabase ();

String SQL = "select count (*) from person ";

Cursor cursor = database. rawQuery (SQL,Null);

Cursor. moveToFirst ();

ReturnCursor. getLong (0 );

}

}

============================================== Test ==========================================

Public ClassDBTestExtendsAndroidTestCase

{

Private Static FinalStringTAG= "DBTest ";

Public VoidTestCreateDB ()

{

DBOpenHelper helper =NewDBOpenHelper (This. GetContext ());

/*

* When the getWritableDatabase () or getReadableDatabase () method of SQLiteOpenHelper is called

* When operating the SQLiteDatabase instance of a database, if the database does not exist, the Android system automatically generates a database,

* Call the onCreate () method.

*/

Helper. getWritableDatabase ();

}

Public VoidTestSava ()ThrowsException

{

PersonService service =NewPersonService (This. GetContext ());

Service. save (NewPerson (1, "Yang Chao "));

Service. save (NewPerson (2, "Yang Chao 1 "));

Service. save (NewPerson (3, "Yang Chao 2 "));

}

Public VoidTestDelete ()

{

}

Public VoidTestFind ()

{

PersonService service =NewPersonService (This. GetContext ());

Person person = service. find (4 );

Log.I(TAG, Person. toString ());

}

Public VoidTestFenye ()

{

}

Public VoidTestCount ()

{

}

}