Ah ah ah ah ah oh, write today, fried chicken Simple Database SQLite creation, library additions and deletions to search

La La la la, it doesn't take long, I write code comments directly are statements, SQL statements and APIs to manipulate the database, the statement parameters I will comment

SQLite database
Api:sqliteopenhelper to use to create a database
You must define a construction method:

// arg1: Name of database file // arg2: Cursor Factory // arg3: Database version  Public int version) {}
Called when the database is created: OnCreate method
Called when the database is upgraded: Onupgrade method

2 Create a database:

//Create a Openhelper objectMyopenhelper Oh =NewMyopenhelper (GetContext (), "person.db",NULL, 1);//Obtain the database object, if the database does not exist, first create the database, then obtain, if present, directly getSqlitedatabase db =oh.getwritabledatabase ();*getwritabledatabase (): Open a writable database*getreadabledatabase (): Open read-only database when disk space is low, otherwise open read-write database*Create a table when you create a database Public voidonCreate (Sqlitedatabase db) {//TODO auto-generated Method StubDb.execsql ("CREATE TABLE Person" (_id integer primary key autoincrement, name Char (TEN), Phone char (), Money Integer (20)) ");}

Database additions and deletions change
Some simple SQL statements:

INSERT into person (name, phone, money) VALUES (' Zhang San ', ' 159874611 ', += ' John Doe ' and _id = 4= 6000 where na me = ' John Doe '= ' Zhang San ';

Execute SQL statements to implement additions and deletions

//InsertDb.execsql ("INSERT into person (name, phone, money) VALUES (?,?,?);",Newobject[]{"Zhang San", 15987461, 75000});//FindCursor cs = db.rawquery ("Select _id, Name, money from person where name =?;",Newstring[]{"Zhang San"});//This method is called before the test method executesprotected voidSetUp ()throwsException {Super. SetUp ();//get the virtual context objectOh =NewMyopenhelper (GetContext (), "people.db",NULL, 1);}

# # #使用api实现增删改查

//Insert//Save the data to be stored in the database as a key-value pairContentvalues CV =Newcontentvalues (); Cv.put ("Name", "Liu Can"); Cv.put ("Phone", 1651646); Cv.put ("Money", 3500);//The return value is the primary key of the row and returns 1 if an error occursLongi = Db.insert ("Person",NULL, CV);//Delete//The return value is the number of rows deletedinti = db.delete ("Person", "_id =?") and name =? ",Newstring[]{"1", "Zhang San"});*Modify Contentvalues CV=Newcontentvalues (); Cv.put ("Money", 25000);inti = db.update ("Person", CV, "name =?",Newstring[]{"Zhao Si"});//Enquiry//arg1: The field to query//arg2: Query Criteria//Arg3: Placeholder for populating query criteriaCursor cs = db.query ("Person",Newstring[]{"Name", "Money"}, "name =?",Newstring[]{"Zhang San"},NULL,NULL,NULL); while(Cs.movetonext ()) {//gets the index value of the specified columnString name = cs.getstring (Cs.getcolumnindex ("name")); String Money= Cs.getstring (Cs.getcolumnindex ("Money")); SYSTEM.OUT.PRINTLN (Name+ ";" +Money );}

Transaction:

What is a transaction

Ensure that multiple SQL statements either succeed at the same time or fail at the same time
Most common case: bank transfer
Transaction API

Try {// open transaction db.begintransaction (); ..... // SET Transaction Execution Success  finally{// Close transaction // If the transaction execution succeeds at this time, the SQL statement takes effect, otherwise it does not take effect  db.endtransaction ();}

First create class myopenhelper inherit from Sqliteopenhelper

 Public classMyopenhelperextendsSqliteopenhelper { PublicMyopenhelper (Context context) {//1: Database file name//2: Cursor factory, cursor equivalent to result set, NULL using default Factory//3: Version, cannot be less than 1, for upgrade        Super(Context, "people.db",NULL, 1); }    //called when a database is created@Override Public voidonCreate (Sqlitedatabase db) {//Create a tableDb.execsql ("CREATE TABLE Person" (_id integer primary key autoincrement, name Char (TEN), Phone char (), salary Integer (10)) "); }    //called when the database is upgraded@Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {System.out.println ("Database Upgrade"); }}

XML file: Create a new test class and develop the instruction set package, class library

<uses-SDK        android:minsdkversion= "8"        android:targetsdkversion= "/>"    < Instrumentation android:name= "Android.test.InstrumentationTestRunner"        android:targetpackage= " Com.ace.sqlite ">        </instrumentation>    <application        android:allowbackup=" true "         Android:icon= "@drawable/ic_launcher"        android:label= "@string/app_name"         android:theme= "@style/apptheme" >        <uses-library android:name= "Android.test.runner"/>    

 Public classTestextendsandroidtestcase{PrivateMyopenhelper Oh; PrivateSqlitedatabase DB;  Public voidTest () {//Get Virtual ContextMyopenhelper Oh =NewMyopenhelper (GetContext ()); //If the database does not exist, create it, then open it, and if it exists, open it directly .Sqlitedatabase db =oh.getwritabledatabase (); }        //called before the test method executes@Overrideprotected voidSetUp ()throwsException {Super. SetUp (); Oh=NewMyopenhelper (GetContext ()); DB=oh.getwritabledatabase (); } @Overrideprotected voidTearDown ()throwsException {Super. TearDown ();    Db.close (); }         Public voidInsert () {//Db.execsql ("INSERT into person (name, phone, salary) VALUES (?,?,?)", New object[]{"Ya", "13503723845", 5000} );Db.execsql ("INSERT into person (name, phone, salary) VALUES (?,?,?)",Newobject[]{"De ma", "18543843843", 2000}); Db.execsql (INSERT into person (name, phone, salary) VALUES (?,?,?),Newobject[]{"Mundo", "12345678912", 3000}); }         Public voidDelete () {Db.execsql ("Delete from person where name =?",Newobject[]{"Ya-Sok"}); }         Public voidUpdate () {Db.execsql ("Update person Set salary =?" WHERE name =? ",Newobject[]{5200, "Ya-Sok"}); }         Public voidSelect () {cursor cursor= Db.rawquery ("SELECT * FROM person",NULL); //move the pointer to the next line         while(Cursor.movetonext ()) {//get the column index first by column name, and then get the contents of the columnString name = cursor.getstring (Cursor.getcolumnindex ("name")); String Phone= Cursor.getstring (Cursor.getcolumnindex ("Phone")); intSalary = Cursor.getint (Cursor.getcolumnindex ("salary")); SYSTEM.OUT.PRINTLN (Name+ ";" + Phone + ";" +salary); }    }         Public voidInsertapi () {contentvalues values=Newcontentvalues (); Values.put ("Name", "Female Gun"); Values.put ("Phone", "1388888888"); Values.put ("Salary", "8000"); //return value-1, insert failed        LongL = Db.insert ("Person",NULL, values);    System.out.println (l); }         Public voidDeleteapi () {inti = db.delete ("Person", "_id =?",Newstring[]{"6"});    System.out.println (i); }         Public voidUpdateapi () {contentvalues values=Newcontentvalues (); Values.put ("Name", "De bang"); inti = db.update ("Person", values, "_id =?",Newstring[]{"2"});    System.out.println (i); }         Public voidSelectapi () {//arg1: Fields for Queries//arg2: Where Condition of the query//Arg3:where placeholder for a conditioncursor cursor = db.query ("Person",NULL,NULL,NULL,NULL,NULL,NULL,NULL);  while(Cursor.movetonext ()) {String name= cursor.getstring (1); String Phone= Cursor.getstring (2); intSalary = Cursor.getint (3); SYSTEM.OUT.PRINTLN (Name+ ";" + Phone + ";" +salary); }    }         Public voidtransaction () {Try{            //Open Transactiondb.begintransaction (); Contentvalues Values=Newcontentvalues (); Values.put ("Salary", 4500); Db.update ("Person", values, "name =?",Newstring[]{"Ya-Sok"}); //empty the contents of valuesvalues.clear (); Values.put ("Salary", 2500); Db.update ("Person", values, "name =?",New"string[]{"?}); //int i = 1/0;//This sentence is to break the transaction and let the data roll back//set transaction execution success, rollback if this line of code is not executed at commitdb.settransactionsuccessful (); }        Catch(Exception e) {e.printstacktrace (); }        finally{            //closing transactions, submitting datadb.endtransaction (); }    }}

Reproduced please indicate the source Acein20160131

Ah ah ah ah ah oh, write today, fried chicken Simple Database SQLite creation, library additions and deletions to search