Path to Android Development 12-SQLite Database

SQLite Database

SQLite is an open-source embedded relational database, which was developed by D. richard hipp is released, which reduces the overhead of Application Data Management. SQLite is portable, easy to use, small, efficient, and reliable. Currently, sqlite3 is integrated in Android. SQLite does not support static data types, but uses column relationships. This means that its data type does not have the table column attribute, but has the data attribute. When a value is inserted into the database, SQLite checks its type. If this type does not match the associated column, SQLite will try to convert the value to the column type. If the conversion fails, the value is stored as its own type. SQLite
Supports null, integer, real, text, and BLOB data types. 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. If your primary key is integer, you can only store 6 4-digit integers. If you store data other than integers in this field, an error will occur. In addition, when SQLite parses the reate TABLE statement, it will ignore the data type information following the field name in the create table statement.

Features of SQLite

The SQLite database has the following features:

1. Zero Configuration

Sqlite3 does not require installation, configuration, startup, shutdown, or database instance configuration. When the system crashes, it does not need to perform any restoration operations and is automatically restored when the database is used next time.

2. Portable

It runs on Windows, Linux, BSD, Mac OS X, and some commercial Unix systems, such as Sun's Solaris and IBM's Aix. Similarly, it can also work in many embedded operating systems, such as Android, QNX, VxWorks, Palm OS, symbin, and Windows CE.

3. Compact

SQLite is designed to be lightweight and self-contained. With a header file and a lib library, you can use relational databases without starting any system processes.

4. Simple

SQLite has simple and easy-to-use APIs.

5. Reliable

The source code of SQLite reaches 100% branch test coverage.

Use sqliteopenhelper abstract class to create a database

The abstract class sqliteopenhelper is used to manage database versions, which is not required.

To manage database versions, the sqliteopenhelper class provides two important methods: oncreate (sqlitedatabasedb) and onupgrade (sqlitedatabase dB, int oldversion, intnewversion) used to generate a database table when you first use the software. The latter is used to update the database table structure when you upgrade the software.

 

PublicSQLiteOpenHelper(Context, string name,

Sqlitedatabase. cursorfactory factory, int Version)

Context: Indicates the application context.

Name: Indicates the name of the database.

Factory: Indicates the record set cursor factory, which is used to generate the record set cursor. The record set cursor iterates the query results. We will continue to introduce it later.

Version: Indicates the database version. If you need to change the version number when upgrading the software in the future, the onupgrade (sqlitedatabase dB, int oldversion, int newversion) method will be called, this method is suitable for modifying the database table structure when the software is updated.

Lab procedure

1. Create Database databasehelper

Public classDatabaseHelperExtendsSQLiteOpenHelper { StaticStringDbname= "MyAndroid_db.db "; Static int Version= 1;   PublicDatabaseHelper (Context context ){ Super(Context,Dbname,Null, Version); } // It will be called when used for the first time to create a database Public voidOnCreate (SQLiteDatabase db ){ String SQL = "create table person11 (personid integer primary key Autoincrement, name varchar (20), age integer )"; Db.exe cSQL (SQL ); }   Public voidOnUpgrade (SQLiteDatabase db, IntOldVersion, IntNewVersion ){ StringSQL= "Drop table if exists person "; OnCreate (db ); } }

2. Write test classes for testing

Public voidOnUpgrade (SQLiteDatabase db, IntOldversion,IntNewversion ){ // StringSQL= "Drop table if exists person "; // Log. I ("tag", "I deleted "); // Oncreate (DB); String SQL = "ALTER TABLE person add phone char (20) null "; Db.exe csql (SQL ); }

3. database update Test

First, modify the version number (incrementing)

Then run the test method testCreateDb () again ()

Crud

Lab procedure

Create a PersonService business class

PackageCN. class3g. Service; ... Public classPersonservice {   PrivateDatabasehelper dbhelper; PrivateContextContext;   PublicPersonservice (context ){ This. Context = context; Dbhelper =NewDatabasehelper (context ); }   Public voidSave (person ){ Sqlitedatabase DB = dbhelper. getwritabledatabase (); // StringSQL= "Insert into person (name, age) values ('Tom ', 21 )"; // Db.exe csql (SQL);   // Prevent user input data errors, such as: Name = "t' Om" String SQL = "insert into person (name, age) values (?,?) "; Db.exe csql (SQL,NewObject [] {person. getname (), person. getage ()}); }   Public voidUpdate (person,IntID ){ Sqlitedatabase DB = dbhelper. getwritabledatabase (); String SQL = "Update person set name = ?, Age =? Where personid =? "; Db.exe csql (SQL,NewObject [] {person. getname (), person. getage (), Id }); }   PublicPerson find (IntID ){ Sqlitedatabase DB = dbhelper. getreadabledatabase (); String SQL = "select * From person where personid =? "; Cursor cursor = dB. rawquery (SQL,NewString [] {string.Valueof(ID )});   If(Cursor. moveToNext ()){ Person person =NewPerson (); Person. setName (cursor. getString (cursor. getColumnIndex ("name "))); Person. setId (cursor. getInt (0 )); Person. setAge (cursor. getInt (2 ));   Cursor. close (); // close the cursor ReturnPerson; } Return null; }   Public voidDelete (IntId ){ SQLiteDatabase db = dbHelper. getReadableDatabase (); String SQL = "delete from person where personid =? "; Db.exe cSQL (SQL,NewObject [] {id }); }   PublicList <Person> getScrollData (IntStartIdx, IntCount ){   SQLiteDatabase db = dbHelper. getReadableDatabase (); String SQL = "select * from person limit ?,? "; Cursor cursor = db. rawQuery (SQL, NewString [] {String.Valueof(StartIdx ), String.Valueof(Count )});   List <Person> list =NewArrayList <Person> (); While(Cursor. moveToNext ()){ Person p =NewPerson (); P. setId (cursor. getInt (0 )); P. setName (cursor. getString (1 )); P. setAge (cursor. getInt (2 )); List. add (p ); } Cursor. close (); ReturnList; } Public longGetRecordsCount (){ SQLiteDatabase db = dbHelper. getReadableDatabase (); String SQL = "select count (*) from person "; Cursor cursor = db. rawQuery (SQL,Null); Cursor. moveToFirst (); LongCount = cursor. getInt (0 ); Cursor. close (); ReturnCount; } }

Add the corresponding test method to the test class cn. class3g. db. PersonServiceTest.

PackageCn. class3g. db; ... Public classPersonServiceTestExtendsAndroidTestCase { Public voidTestSave ()ThrowsThrowable { PersonService service =NewPersonService (This. GetContext ()); Person person =NewPerson (); Person. setName ("zhangxiaoxiao "); Service. save (person ); Person person2 =NewPerson (); Person2.setName ("laobi "); Service. save (person2 ); Person person3 =NewPerson (); Person3.setName ("lili "); Service. save (person3 ); Person person4 =NewPerson (); Person4.setName ("zhaoxiaogang "); Service. save (person4 ); } Public voidTestUpdate ()ThrowsThrowable { PersonService ps =NewPersonService (This. GetContext ()); Person person =NewPerson ("Ton", 122 ); Ps. update (person, 2); // you need to view the Ton id value in the database. } Public voidTestFind ()ThrowsThrowable { PersonService ps =NewPersonservice (This. Getcontext ()); Person = ps. Find (2 ); Log.I("Tag", person. tostring ()); } Public voidTestdelete ()ThrowsThrowable { Personservice PS =NewPersonservice (This. Getcontext ()); PS. Delete (2 ); } Public voidTestscroll ()ThrowsThrowable { Personservice service =NewPersonservice (This. Getcontext ()); List <person> personlist = service. getscrolldata (3, 2 ); Log.I("Tag", personlist. tostring ()); } Public voidTestcount ()ThrowsThrowable { Personservice service =NewPersonservice (This. Getcontext ()); LongCount = service. getrecordscount (); Log.I("Tag", String.ValueOf(Count )); } }

Common exceptions

Android. database. sqlite. SQLiteException: Can't upgrade read-only database from version 0 to 1:

This error is basically caused by SQL problems. Check the SQL carefully.