Related operation methods of lightweight database Sqlitedatabase

First, the query operation:

Query operation is more complex, the following operations are mainly:

1 db.rawquery (String sql, string[] selectionargs);   2 db.query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, string having, stri NG);   3 db.query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, string having, stri Ng, String limit);   4

1, the simplest, all the SQL statements are organized into a string, Selectionargs is the placeholder actual parameter set

2. Columns represents the set of all the names of the columns to be queried, selection represents a conditional statement after where, you can use placeholders, groupby specify the column names for grouping, having a specified grouping condition, with groupby use, order by specifying the sorted column name

3. Limit Specify paging parameters

4. Distinct can specify "true" or "false" to indicate that duplicate values should not be filtered.

Selection, GroupBy, having, ORDER by, limit these parameters do not include the "WHERE", "GROUP by", "have", "an" and "" "," and "" Limit "and other SQL keywords.
Returns a Cursor object that represents the data set's cursors, somewhat similar to the resultset in Javase.

Common methods for cursor objects:

1Cursor.move (intOffset);//moves to the specified line as a reference to the current position2Cursor.movetofirst ();//move to the first row3Cursor.movetolast ();//move to the last line4Cursor.movetoposition (intposition);//move to the specified line5Cursor.movetoprevious ();//move to Previous line6Cursor.movetonext ();//move to the next line7Cursor.isfirst ();//whether to point to the first article8Cursor.islast ();//whether to point to the last bar9Cursor.isbeforefirst ();//before you point to the first articleTenCursor.isafterlast ();//If you point to the last bar OneCursor.isnull (intCOLUMNINDEX);//Specifies whether the column is empty (column cardinality is 0) ACursor.isclosed ();//whether the cursor is closed -Cursor.getcount ();//total number of data items -Cursor.getposition ();//returns the number of rows that the current cursor points to theCursor.getcolumnindex (String columnName);//returns the column index value corresponding to a column name -Cursor.getstring (intCOLUMNINDEX);//returns the value of the specified column for the current row

Ii. additions, updates, and deletions

1    db.executesql (String sql);   2    Db.executesql (String sql, object[] bindargs); // A placeholder is used in the SQL statement, and then the second argument is the actual parameter  set 3 4 5     Db.insert (string table, String nullcolumnhack, contentvalues values);   6     db.update (string table, contentvalues values, String whereclause, String whereargs);   7     Db.delete (string table, String Whereclause, string Whereargs);  

Insert:

Table The first parameter represents the action table name; the second parameter in insert indicates that if each column of the inserted data is empty, you need to specify the name of a column in this row, and the system sets the column to NULL to avoid errors The third parameter in insert is a variable of type contentvalues, is a set of key-value pairs, Columnkey represents the column name, and Columnvalue represents the value that the column is to insert;
Update:

The second parameter of update is similar, except that it updates the field key to the most recent value value, and the third parameter, Whereclause, represents a where expression, such as "Age >?" and age <? " And so on, the Whereargs parameter is the actual parameter value of the placeholder;

Delete

The parameters are basically the same.

Third, specific examples:

1         //Open or create a test.db database2Sqlitedatabase db = Openorcreatedatabase ("Test.db", Context.mode_private,NULL); 3Db.execsql ("DROP TABLE IF EXISTS Person"); 4         //Create Person Table5Db.execsql ("CREATE TABLE person (_id INTEGER PRIMARY KEY autoincrement, name VARCHAR, age SMALLINT)"); 6Person person =NewPerson (); 7Person.name = "John"; 8Person.age = 30; 9         //Inserting DataTenDb.execsql ("INSERT into person VALUES (NULL,?,?)",Newobject[]{person.name, person.age});  One            APerson.name = "David";  -Person.age = 33;  -         //contentvalues storing data in the form of key-value pairs theContentvalues CV =Newcontentvalues ();  -Cv.put ("name", Person.name);  -Cv.put ("Age", Person.age);  -         //inserting data from a contentvalues +Db.insert ("Person",NULL, CV);  -            +CV =Newcontentvalues ();  ACv.put ("Age", 35);  at         //Update Data -Db.update ("Person", CV, "name =?",Newstring[]{"John"});  -            -Cursor C = Db.rawquery ("Select * from the person WHERE is the age >=?",Newstring[]{"33"});  -          while(C.movetonext ()) { -             int_id = C.getint (C.getcolumnindex ("_id"));  inString name = c.getstring (C.getcolumnindex ("name"));  -             intAge = C.getint (The C.getcolumnindex ("Age"));  toLOG.I ("db", "_id=>" + _id + ", name=>" + name + ", age=>" +Age );  +         }   - C.close ();  the            *         //Delete Data $Db.delete ("Person", "Age <?",Newstring[]{"35"}); Panax Notoginseng            -         //Close the current database the Db.close ();  +            A         //Delete test.db Database the //deletedatabase ("test.db");  +}

Iv. Sqliteopenhelper

Sqliteopenhelper is a helper class for sqlitedatabse that manages the creation of data and version updates. The general usage is to define a class to inherit Sqliteopenhelper and implement two callback methods, OnCreate (Sqlitedatabase db) and Onupgrade (sqlitedatabse, int oldversion, int NewVersion) to create and update the database.

You can generally get an instance of the Help class as follows: Mopenhelper = new Databasehelper (GetContext ());

He has the following three classes of definitions:

Public Sqliteopenhelper (Context context, String name, Cursorfactory factory,int version)

Public Sqliteopenhelper (Context context, String name, int version)

Public Sqliteopenhelper (Context context)

Two callback functions:

Public void OnCreate (Sqlitedatabase db)

Public void Onupgrade (sqlitedatabase db, int oldversion, int newversion)

Get Sqlitedatabase Instance

Sqlitedatabase db = Getwritabledatabase ();

Sqlitedatabase db = Getreadabledatabase ();

V. Transaction processing

The Sqlitedatabase BeginTransaction () method can open a transaction, and the program executes to the Endtransaction () method when the flag of the transaction is checked for success if the program executes to Endtransaction () The Settransactionsuccessful () method was previously called to set the transaction's flag to commit the transaction successfully, and if the Settransactionsuccessful () method was not called, the transaction is rolled back.

Transactional applications: Many times when we need to bulk insert large amounts of data into SQLite, a separate use of the Add method causes the application to respond slowly, because when SQLite inserts the data, the default statement is a transaction, and the number of data is how many disk operations there are. In order to ensure the consistency of data, avoid data loss and other situations.

1Sqlitedatabase db =dbopenhelper.getwritabledatabase ();2 //Open Transaction3 db.begintransaction ();4 Try{5             //Bulk Processing Operations6             //Do something7Db.execsql ("SQL statement",Newobject[]{});8Db.execsql ("SQL statement",Newobject[]{});9             //The transaction flag is set to be successful, and the transaction is committed when the transaction is endedTen db.settransactionsuccessful (); One } A Catch(Exception e) { - } - finally{ the            //End Transaction - db.endtransaction (); -}

Note: The above code reference:

http://blog.csdn.net/li12412414/article/details/51958774

http://blog.csdn.net/xiaanming/article/details/8679521

Related operation methods of lightweight database Sqlitedatabase