Basic operation of SQLite database

SQLite is a lightweight database that adheres to the acid-based relational database management system, which is contained in a relatively small C library. It is the public domain project established by D.richardhipp. It is designed to be embedded, and has been used in many embedded products, it occupies a very low resource, in the embedded device, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, also compared to MySQL, PostgreSQL, the two open source world-renowned database management system, is processing faster than they do. The first alpha version of SQLite was born in May 2000. To 2015 has been 15 years, SQLite also ushered in a version of SQLite 3 has been released.

The following is a brief introduction to its application in wirelessly:

1. First write your own class Myopenhelper inheritance Sqliteopenhelper,sqliteopenhelper is an abstract class, you need to rewrite the construction method and implement two methods. As follows:

 PackageCom.example.sqlite;ImportAndroid.content.Context;Importandroid.database.sqlite.SQLiteDatabase;Importandroid.database.sqlite.SQLiteDatabase.CursorFactory;ImportAndroid.database.sqlite.SQLiteOpenHelper; Public classMyopenhelperextendsSqliteopenhelper {/*** Override the constructor method because the parent class has no parameterless constructor *@paramContext *@paramname *@paramFactory *@paramversion*/     PublicMyopenhelper (Context context, String name, Cursorfactory factory,intversion) {        Super(context, name, Factory, version); }    /*** When the database is created, this method invokes the*/@Override Public voidonCreate (Sqlitedatabase db) {System.out.println ("The database was created"); Db.execsql ("CREATE table person (_id Integer primary key autoincrement," + "name varchar (ten), salary varchar), Phon e integer (20)) "); }    /*** This method is called when the database is upgraded*/@Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {System.out.println ("The database has been upgraded"); }}

2. Test in the test class, adding and removing changes, the following demonstrates the first, write their own SQL statement:

 Packagecom.example.sqlite.test;ImportCom.example.sqlite.MyOpenHelper;ImportAndroid.database.Cursor;Importandroid.database.sqlite.SQLiteDatabase;Importandroid.test.AndroidTestCase; Public classTestCaseextendsAndroidtestcase {PrivateMyopenhelper Oh; PrivateSqlitedatabase DB; /*** Test Database Creation * Even if run two times, the database will only be created once*/     Public voidtest1 () {Myopenhelper Oh=NewMyopenhelper (GetContext (), "people.db",NULL, 1); //If the database does not exist, create the database first, and then get the readable writable database object, if the database exists, open it directlySqlitedatabase db =oh.getwritabledatabase (); //The return is no different from the above, unless the internal storage space is full, and the read-only database is returned//Sqlitedatabase db = Oh.getreadabledatabase ();    }    /*** After the test framework has been initially completed, this method calls before the test method executes*/@Overrideprotected voidSetUp ()throwsException {Super. SetUp (); Oh=NewMyopenhelper (GetContext (), "people.db",NULL, 1); DB=oh.getwritabledatabase (); }    /*** After the test method is executed, this method calls*/@Overrideprotected voidTearDown ()throwsException {Super. TearDown ();    Db.close (); }            /*** Test Insert*/     Public voidtest2 () {Db.execsql (INSERT into person (name, salary, phone) VALUES (?,?,?) ",                 Newobject[]{"Airbnb", "15000", 151762}); }        /*** Test Delete*/     Public voidtest3 () {Db.execsql ("Delete from the person where name=?",Newobject[]{"Airbnb"}); }        /*** Test Updates*/     Public voidtest4 () {Db.execsql ("Update person set phone=?" Where Name=? ",Newobject[]{123456, "Airbnb"}); }        /*** Test Query*/     Public voidTest5 () {//Notice that the API has changed, and the second parameter is the fill of the SQL statement?cursor cursor = db.rawquery ("Select name, Salary from",NULL);  while(Cursor.movetonext ()) {//get the value of a column by column indexString name = cursor.getstring (Cursor.getcolumnindex ("name")); String Salary= Cursor.getstring (Cursor.getcolumnindex ("salary")); SYSTEM.OUT.PRINTLN (Name+",,,"+salary); }    }    }

3. Test in the test class, adding and removing changes, the following shows the second, call the API:

 Public voidInsertapi () {//encapsulates all data to be inserted into the Contentvalues objectContentvalues values =Newcontentvalues (); Values.put ("Name", "Chen"); Values.put ("Salary", "16000"); Values.put ("Phone", 15176290645L); //returns the most recently inserted primary key value (table name, fill null on line < only use when values are empty; Insert content)        Longid = db.insert ("Person",NULL, values);    SYSTEM.OUT.PRINTLN (ID); }         Public voidDeleteapi () {//returns the number of deleted rows (fill in table name, condition, condition?)        introws = Db.delete ("Person", "name=?") And _id=? ",Newstring[]{"Airbnb", "10"});    SYSTEM.OUT.PRINTLN (rows); }         Public voidUpdateapi () {contentvalues values=Newcontentvalues (); Values.put ("Salary", "777777"); //The number of rows affected (table name, changed content, condition, condition, fill) is returned        introws = db.update ("Person", values, "_id=?",Newstring[]{"7"});    SYSTEM.OUT.PRINTLN (rows); }         Public voidSelectapi () {//(table name, query field <null for all fields, query criteria, query criteria, fill, groupby,having,orderby,limit)cursor cursor = db.query ("Person",NULL, "Name=?",Newstring[]{"Chen Button"},                 NULL,NULL,NULL,NULL);  while(Cursor.movetonext ()) {String name= Cursor.getstring (Cursor.getcolumnindex ("name")); String Salary= Cursor.getstring (Cursor.getcolumnindex ("salary")); String Phone= Cursor.getstring (Cursor.getcolumnindex ("Phone")); SYSTEM.OUT.PRINTLN (Name+ "," +salary+ "," +phone); }    }

The above is simple additions and deletions to the operation, you can download a SQLite expert visualization tool for viewing.

4. Transaction management, simulating an example of a transfer

 Public voidtransaction () {Try {            //Open Transactiondb.begintransaction (); Contentvalues Values=Newcontentvalues (); Values.put ("Salary", "19000"); Db.update ("Person", values, "_id=?",Newstring[]{"11"});            Values.clear (); Values.put ("Salary", "13000"); Db.update ("Person", values, "_id=?",Newstring[]{"12"}); //int i = 2/0; //If you throw an exception here, the code that sets the successful execution of the transaction does not execute, the SQL statement is rolled back, and update does not take effect//SET Transaction Execution Successdb.settransactionsuccessful (); } Catch(Exception e) {e.printstacktrace (); } finally {            //Close the transaction, commit at the same time, if the transaction execution has been set successfully, then the SQL statement will take effect, and conversely, the SQL statement rollbackdb.endtransaction (); }    }

Basic operation of SQLite database