Android SQLite Detailed

In the development of the project, we will use the database more or less. In Android, we generally use SQLite, because Android in the Android.database.sqlite package encapsulates many of the SQLite operating APIs. I wrote a demo to summarize the use of SQLite, hosted on GitHub, you can click to download the APK, you can also click to download the source code. The demo is as follows:

when using SQLite, I recommend that you download a local SQLite client to verify the operation, and then transfer it to Android if the SQL statement that was written locally runs correctly. I'm using a SQLite Expert Personal. First create a class that inherits from the Sqliteopenhelper, and override the OnCreate () and Onupgrade () methods.  Public classOrderdbhelperextendssqliteopenhelper{Private Static Final intDb_version = 1; Private Static FinalString db_name = "Mytest.db";  Public Static FinalString table_name = "Orders";  PublicOrderdbhelper (Context context) {Super(Context, Db_name,NULL, db_version); } @Override Public voidonCreate (sqlitedatabase sqlitedatabase) {//CREATE TABLE Orders (Id integer primary key, customname text, Orderprice integer, country text);String sql = "CREATE table if not EXISTS" + table_name + "(Id integer primary key, customname text, Orderprice integer, Country text) ";    Sqlitedatabase.execsql (SQL); } @Override Public voidOnupgrade (Sqlitedatabase sqlitedatabase,intOldversion,intnewversion) {String SQL= "DROP TABLE IF EXISTS" +table_name;        Sqlitedatabase.execsql (SQL);    OnCreate (sqlitedatabase); }} This class is primarily used to build databases and build tables, and we create a Orderdao to handle all data manipulation methods. In Orderdao clock instantiation orderdbhelper: PublicOrderdao (Context context) { This. Context =context; Ordersdbhelper=NewOrderdbhelper (context);} Database Operation no outside: "Adding and removing to check and change." For the "additions and deletions" of the table content transformation operations, we need to call Getwritabledatabase (), at the time of execution can call the common execsql (String sql) method or the corresponding operation Api:insert (), delete (), Update (). For "Check", you need to call Getreadabledatabase (), you can not use the Execsql method, you have to use the query () or Rawquery () method. Let's start with the introduction. Add Data in my demo, there are two ways to add data: Initialize the data when entering the demo program, first determine whether there is data in the table, if there is no data in the table, I will first add some data. When initializing the data, because one time to add more data, so I directly use the Execsql method: DB=ordersdbhelper.getwritabledatabase ();d b.begintransaction ();d B.execsql ("INSERT into" + Orderdbhelper.table_name + "(Id, Customname, Orderprice, Country) VALUES (1, ' Arc ', ' + ') ');d B.execsql ("INSERT into" + Orderdbhelper.table_name + "(Id, Customname, Orderprice, Country) VALUES (2, ' Bor ', $, ' USA ')");d B.execsql ("INSERT into" + Orderdbhelper.table_name + "(Id, Customname, Orderprice, Country) VALUES (3, ' Cut ', $, ' Japan ')");d B.execsql ("INSERT into" + Orderdbhelper.table_name + "(Id, Customname, Orderprice, Country) VALUES (4, ' Bor ', +, ' USA ')");d B.execsql ("INSERT into" + Orderdbhelper.table_name + "(Id, Customname, Orderprice, Country) VALUES (5, ' Arc ', ' I ') ');d B.execsql ("INSERT into" + Orderdbhelper.table_name + "(Id, Customname, Orderprice, Country) VALUES (6, ' Doom ', $, ' China ')");d b.settransactionsuccessful (); Insert a new piece of data we can also use Insert (String table,string nullcolumnhack,contentvalues values) method to insert, contentvalues internal implementation is hashmap, but the two are still different, contenvalues key can only be string type, value can only store the basic type of data, such as String,int, Cannot store objects such things: Publiccontentvalues () {//Choosing a default size of 8 based on analysis of typical//consumption by applications.Mvalues =NewHashmap<string, object> (8);} Using the Insert () method, we insert a new piece of data (7, "Jne", "China"), for the operation of modifying data we generally treat as a transaction (Transaction): DB=ordersdbhelper.getwritabledatabase ();d b.begintransaction ();//INSERT INTO Orders (Id, Customname, Orderprice, Country) VALUES (7, "Jne", and "China");Contentvalues contentvalues =Newcontentvalues (); Contentvalues.put ("Id", 7); Contentvalues.put ("Customname", "Jne"); Contentvalues.put ("Orderprice", 700); Contentvalues.put ("Country", "China");d B.insertorthrow (Orderdbhelper.table_name,NULL, Contentvalues);d b.settransactionsuccessful (); Delete data delete data in addition to Execsql there is a delete (String table,string whereclause, String[] Whereargs), Whereclause is the delete condition, Whereargs is the delete condition value array. DB=ordersdbhelper.getwritabledatabase ();d b.begintransaction ();//Delete from Orders where Id = 7Db.delete (Orderdbhelper.table_name, "Id =?",NewString[]{string.valueof (7});d b.settransactionsuccessful (), and then look at the deleted source code, which will be assembled to delete the condition and delete the condition value array: Public intDelete (string table, String Whereclause, string[] whereargs) {acquirereference (); Try{sqlitestatement Statement=NewSqlitestatement ( This, "DELETE from" + table +                (! Textutils.isempty (whereclause)? "WHERE" + Whereclause: ""), Whereargs); Try {            returnStatement.executeupdatedelete (); } finally{statement.close (); }    } finally{releasereference (); }} modifying data and inserting data is very similar to the method called, except Execsql can also be update (String table,contentvalues values,string whereclause, string[] Whereargs): DB=ordersdbhelper.getwritabledatabase ();d b.begintransaction ();//Update Orders Set orderprice = + where Id = 6Contentvalues CV =Newcontentvalues (); Cv.put ("Orderprice", 800);d b.update (Orderdbhelper.table_name, CV,"Id =?",        NewString[]{string.valueof (6)});d b.settransactionsuccessful (); Find data find data There are two methods, one is public Cursor query (String table,string[] columns,string Selection,string[] selectionargs,string groupby,string having,string orderby,string limit); the other one is the public Cursor Rawquery (String sql, string[] selectionargs).        Rawquery's writing is similar to the above execsql, this does not introduce, the parameters of the query method are as follows: Table name: Columns: Column name array selection: Conditional words, equivalent to where Selectionargs: Conditional words, parameter array groupBy: grouped columns having: grouping conditions ORDER BY: Queue limit: Paged query limit Cursor: The return value, equivalent to Result set ResultSet we can see that the returned type is Cursor,cursor is a cursor interface that provides a way to traverse the results of the query, such as moving the Pointer Method move () to get the column value method. Cursor cursors are commonly used as follows: Let's start by checking the user name"Bor"the information: DB=ordersdbhelper.getreadabledatabase ();//SELECT * from Orders where customname = ' Bor 'cursor =db.query (Orderdbhelper.table_name, Order_columns,"Customname =?",        NewString[] {"Bor"},        NULL,NULL,NULL);if(Cursor.getcount () > 0) {List<Order> orderlist =NewArraylist<order>(Cursor.getcount ());  while(Cursor.movetonext ()) {Order Order=parseorder (cursor);    Orderlist.add (order); }    returnOrderList;} Of course, we can also query the total number, the minimum value, and so on, to query the total number of users country to China for example: DB=ordersdbhelper.getreadabledatabase ();//Select COUNT (Id) from the Orders where country = ' China 'cursor =db.query (Orderdbhelper.table_name,Newstring[]{"COUNT (Id)"},        "Country =?",        NewString[] {"China"},        NULL,NULL,NULL);if(Cursor.movetofirst ()) {count= Cursor.getint (0);} At this point SQLite is finished, you can download the demo detailed view. There are some other dry things in the demo, we can dig digging. Of course, there are some shortcomings in the demo, I will update, as far as possible to let users through the demo can learn how to use SQLite. 

Android SQLite Detailed