Android SQLite instance

SQLite Introduction

SQLite is a lightweight, embedded, and relational database. It is currently used in iPhone, Android, and other mobile phone systems. SQLite is portable, easy to use, small, efficient, and reliable. SQLite is embedded into applications that use it. They share the same process space, rather than a separate process. From the external perspective, it is not like an RDBMS, but inside the process, it is complete, self-contained database engine.

In Android, when you need to operate the SQLite database, you need to get a sqliteopenhelper object, while sqliteopenhelper is an abstract class. You need to inherit this class and implement some methods in this class.

1. inherit from sqliteopenhelper and you will have the following two methods:

◆ Getreadabledatabase () Create or open a query database

◆ Getwritabledatabase () Create or open a writable Database

◆ They will return the sqlitedatabase object, and the user will perform subsequent operations through the obtained sqlitedatabase object

2. At the same time, the user can override the following callback functions and call back the following methods when operating the database:

◆ Oncreate (sqlitedatabase): This method is called when the database is created for the first time. Generally, we create a database table in this method.

◆ Onupgrade (sqlitedatabase, Int, INT): When the database needs to be modified, the android system will actively call this method. In general, we delete database tables and create new database tables in this method. Of course, whether other operations are required depends on the needs of the application.

◆ Onopen (sqlitedatabase): This is the callback function when the database is opened and is generally not used.

Note:

1. The following constructor must exist in the subclass of sqliteoepnhelper:

Public databasehelper (context, string name, cursorfactory factory, int version) {// The constructor super (context, name, factory, Version) in the parent class must be called through Super );}

For convenience, you can also create other constructors with two or three parameters.

2. The public void oncreate (sqlitedatabase dB) function is executed when getreadabledatabase () is called or getwritabledatabase () is called to create a database for the first time. In fact, it is the first time that the sqlitedatabse object is obtained, to call this method.

Public void oncreate (sqlitedatabase dB) {system. out. println ("Create a database"); // execsql function is used to execute the SQL statement db.exe csql ("CREATE TABLE user (ID int, name varchar (20 ))");}

When inserting a record into a database table, you must first include the data in a contentvalues and insert a key-value pair to the object. The key-value pair is the column name, the value is the value that you want to insert into this column. The value must be consistent with the data type in the database. Call the getwritabledatabase method of databasehelper to obtain the databasehelper object that can be written, and then insert records into it. Pay attention to the transmission of parameters that call the insert, update, or query method of the databasehelper object.

In addition, after the query method is executed, a cursor is returned. The cursor first points to the previous row of the first row in the record set. Therefore, you must first call cursor. next () move the cursor to the first row of the record set, and then obtain the data.

Public class sqliteactivity extends activity {/** called when the activity is first created. */private button createbutton; private button insertbutton; private button updatebutton; private button updaterecordbutton; private button querybutton; @ override public void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); setcontentview (R. layout. main); createbutton = (button) find Viewbyid (R. id. createdatabase); updatebutton = (button) findviewbyid (R. id. updatedatabase); insertbutton = (button) findviewbyid (R. id. insert); updaterecordbutton = (button) findviewbyid (R. id. update); querybutton = (button) findviewbyid (R. id. query); createbutton. setonclicklistener (New createlistener (); updatebutton. setonclicklistener (New updatelistener (); insertbutton. setonclicklistener (New insertliste NER (); updaterecordbutton. setonclicklistener (New updaterecordlistener (); querybutton. setonclicklistener (New querylistener ();} class createlistener implements onclicklistener {@ override public void onclick (view v) {// create a databasehelper object databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db"); // only after the getreadabledatabase () method of the databasehelper object is called or the getwritabledatabase () method Or open a database sqlitedatabase DB = dbhelper. getreadabledatabase () ;}} class updatelistener implements onclicklistener {@ override public void onclick (view v) {databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db", 2); sqlitedatabase DB = dbhelper. getreadabledatabase () ;}} class insertlistener implements onclicklistener {@ override public void onclick (view v) {// generate content Values object contentvalues values = new contentvalues (); // to insert a key-value pair to the object, the key-value pair is the column name, and the value is the value to insert to the column, the value must be consistent with the data type in the database. values. put ("ID", 1); values. put ("name", "zhangsan"); databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db", 2); sqlitedatabase DB = dbhelper. getwritabledatabase (); // call the insert method to insert data into the database. insert ("user", null, values) ;}// the update operation is equivalent to executing the update statement in the SQL statement // update table _ Name set xxcol = xxx where xxxxcol = XX... class implements onclicklistener {@ override public void onclick (view arg0) {// todo auto-generated method stub // get a writable sqlitedatabase object databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db"); sqlitedatabase DB = dbhelper. getwritabledatabase (); contentvalues values = new contentvalues (); values. put ("name", "Zha Ngsanfeng "); // The first parameter is the table name to be updated // The second parameter is a contentvaleus object // The third parameter is the WHERE clause dB. update ("user", values, "id =? ", New string [] {" 1 "}) ;}} class querylistener implements onclicklistener {@ override public void onclick (view v) {system. out. println ("AAA ----------------"); log. D ("mydebug", "myfirstdebugmsg"); databasehelper dbhelper = new databasehelper (sqliteactivity. this, "test_mars_db"); sqlitedatabase DB = dbhelper. getreadabledatabase (); cursor = dB. query ("user", new string [] {"ID", "name"}, "id =? ", New string [] {" 1 "}, null); While (cursor. movetonext () {string name = cursor. getstring (cursor. getcolumnindex ("name"); system. out. println ("query --->" + name );}}}}

Appendix:

The method used to determine whether a table exists in SQLite and paste it for your reference.

/*** Determine whether a table exists * @ Param tabname table name * @ return */Public Boolean tabbleisexist (string tablename) {boolean result = false; if (tablename = NULL) {return false;} sqlitedatabase DB = NULL; cursor = NULL; try {DB = This. getreadabledatabase (); string SQL = "select count (*) as C from" + appconstant. databasename + "where type = 'table' and name = '" + tablename. trim () + "'"; cursor = dB. rawquery (SQL, null); If (cursor. movetonext () {int COUNT = cursor. getint (0); If (count> 0) {result = true ;}} catch (exception e) {// todo: handle exception} return result ;}