How to store SQLite databases in Android

In Android, SQLite is used directly for database storage. After a database is created in an Android Application, the database file is stored under/data/application package name/databases.

 

Using SQLite in Android involves the following three classes or interfaces:
1. sqliteopenhelper
* Sqliteopenhelper constructor. Generally, the parameter name of the database to be created is passed.
* Called when oncreate creates a database
* Called when onupgrade version is updated
* Getreadabledatabase: Create or open a read-only Database
* Getwritabledatabase: Create or open a read/write Database

2. sqlitedatabase
* Open or create a database using openorcreatedatabase
* Insert adds a record.
* Delete deletes a record.
* Query records
* Update record
* Execsql executes an SQL statement
* Close to close the database

3. cursor
* Total number of getcount records
* Isfirst determines whether the first record is used
* Islast determines whether the last record is used
* Move movetofirst to the first record
* Move movetolast to the last record
* Move to a specified record
* Move movetonext to the next record
* Move movetoprevious to the previous record
* Getcolumnindexorthrow obtains the column index based on the column name.
* Getint: Get the int type value of the specified column index.
* Getstring obtains the string type value of the specified column index.
Note: Some methods are overloaded. You can familiarize yourself with docs.

 

The following is the complete database operation code: android_sqlite.rar

1. To create a database, you only need to customize a class to inherit from sqliteopenhelper. You must implement at least three methods in the subclass of sqliteopenhelper:
* Constructor that calls the constructor of the parent class sqliteopenhelper. Four parameters are required: context (for example, an activity), Database Name, an optional cursor Factory (usually null), and a database version in use.
* For the oncreate method, a sqlitedatabase object is required as the parameter. Fill in the table and initialization data for this object as needed.
* The onupgrade method requires three parameters: A sqlitedatabase object, an old version number, and a new version number.

/** <Br/> * Database Operation Assistant class <br/> * @ author zuolongsnil <br/> */<br/> public class androidsqliteopenhelper extends sqliteopenhelper {</P> <p> // database name <br/> Public static final string dbname = "android. DB "; <br/> // database version <br/> Public static final int version = 2; <br/> // table creation statement, case Insensitive <br/> Private Static final string createtable = "create table" <br/> + person. tablename <br/> + "(ID string, name string, Gender int, age INT)"; </P> <p> Public androidsqliteopenhelper (context) {<br/> super (context, dbname, null, version ); <br/>}</P> <p> // create a table <br/> @ override <br/> Public void oncreate (sqlitedatabase dB) {<br/> db.exe csql (createtable ); <br/>}</P> <p> // update table <br/> @ override <br/> Public void onupgrade (sqlitedatabase dB, int oldversion, int newversion) {<br/> This. deletedb (db); <br/> This. oncreate (db); <br/>}</P> <p> // delete a table <br/> private void deletedb (sqlitedatabase dB) {<br/> db.exe csql ("Drop table if exists" + person. tablename); <br/>}< br/>}

2. operate database tables, including adding, deleting, modifying, and querying. (The following code uses the first method)
There are two ways to operate database tables: execsql to execute SQL statements; insert, delete, update, and query to take part of the SQL statement as a parameter.
Note: When querying a database, execute the SQL statement using the rawquery method of sqlitedatabase instead of execsql.

/** <Br/> * database management class <br/> * @ author zuolongsnil <br/> */<br/> public class databasemanager {</P> <p> private androidsqliteopenhelper dbhelper; </P> <p> Public databasemanager (context) {<br/> dbhelper = new androidsqliteopenhelper (context ); <br/>}</P> <p> // insert record <br/> Public int insert (person) {<br/> log. E ("SQLite", "---- insert ----"); <br/> sqlitedatabase DB = dbhelper. getwrit Abledatabase (); <br/> dB. begintransaction (); <br/> try {<br/> db.exe csql ("insert into" + person. tablename <br/> + "values (?, ?, ?, ?) ", New object [] {person. ID, <br/> person. name, person. gender, person. age}); <br/> dB. settransactionsuccessful (); <br/>}catch (exception e) {<br/> return 0; <br/>} finally {<br/> dB. endtransaction (); <br/>}< br/> dB. close (); <br/> return 1; <br/>}</P> <p> // delete a record <br/> Public int Delete (person) {<br/> log. E ("SQLite", "---- Delete ----"); <br/> sqlitedatabase DB = dbhelper. getwritabledatabase (); <br/> DB. begintransaction (); <br/> try {<br/> db.exe csql ("delete from" + person. tablename + "where id =? ", <Br/> new object [] {person. id}); <br/> dB. settransactionsuccessful (); <br/>}catch (exception e) {<br/> return 0; <br/>} finally {<br/> dB. endtransaction (); <br/>}< br/> dB. close (); <br/> return 1; <br/>}</P> <p> // update record <br/> Public int Update (person) {<br/> log. E ("SQLite", "---- update ----"); <br/> sqlitedatabase DB = dbhelper. getwritabledatabase (); <br/> dB. begintransaction (); <br/> try {<br/> Db.exe csql ("Update" + person. tablename <br/> + "Set Name = ?, Gender = ?, Age =? Where id =? ", New object [] {<br/> person. name, person. gender, person. age, person. id}); <br/> dB. settransactionsuccessful (); <br/>}catch (exception e) {<br/> return 0; <br/>} finally {<br/> dB. endtransaction (); <br/>}< br/> dB. close (); <br/> return 1; <br/>}</P> <p> // query record <br/> Public arraylist <person> query (string ID) {<br/> log. E ("SQLite", "---- query ----"); <br/> sqlitedatabase DB = dbhelper. getreadabledatabase (); <Br/> cursor; <br/> person; <br/> arraylist <person> List = new arraylist <person> (); <br/> // If fileid is null or "", query all records. <br/> If (ID = NULL | ID. equals ("") {<br/> cursor = dB. rawquery ("select * from" + person. tablename, null); <br/>}else {<br/> cursor = dB. rawquery ("select * from" + person. tablename <br/> + "where id =? ", New string [] {ID}); <br/>}< br/> while (cursor. movetonext () {<br/> person = new person (); <br/> person. id = cursor. getstring (cursor. getcolumnindex ("ID"); <br/> person. name = cursor. getstring (cursor. getcolumnindex ("name"); <br/> person. gender = cursor. getstring (cursor. getcolumnindex ("gender"); <br/> person. age = cursor. getint (cursor. getcolumnindex ("Age"); <br/> log. E ("SQLite", person. tostring (); <br/> list. add (person); <br/>}< br/> cursor. close (); <br/> dB. close (); <br/> If (list. size () = 0) {<br/> log. E ("SQLite", "***** no data in the table *****"); <br/>}< br/> return list; <br/>}< br/>}

3. Enter SQLite in shell and use SQL statements to operate the database, as shown in.

* Start the command line and run the ADB shell command, provided that a simulator is started.

 

* Enter the/data/application package name/databases folder. Here is/data/code. SQLite/databases (make sure that the database has been created for this application, otherwise there is no databases directory under the package name ).

* View the database files under the databases directory. The database file is Android. DB.

* Run the sqlite3 command to access SQLite and use SQL statements to operate the database. Run the "sqlite3 Android. DB" command.

* Use the ". Tables" command to query tables in the database. We can see that there is a person table.

* You can use an SQL statement to operate on this table. For example, you can use "select * From person;" to query records in the table.

* Use the ". Quit" command to exit SQLite.