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 android.database.sqlite encapsulates a lot of SQLite-operated APIs in the package. 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 overrides onCreate() and onUpgrade() methods.

PublicClassOrderdbhelperExtendssqliteopenhelper{PrivateStaticFinalint db_version =1;PrivateStaticFinal String db_name ="Mytest.db";PublicStaticFinal String table_name ="Orders";PublicOrderdbhelper(Context context) {Super (context, db_name,NULL, db_version); }@OverridePublicvoidoncreate (sqlitedatabase sqlitedatabase) { //create TABLE Orders (Id integer primary key, customname text, Orderprice integer, Country Tex T); String sql =  "CREATE table if not EXISTS" + table_name +  (Id intege R primary Key, Customname text, Orderprice integer, Country text) "; Sqlitedatabase.execsql (SQL); }  @Override public void onupgrade (Sqlitedatabase SQLiteDatabase, Span class= "Hljs-keyword" >int oldversion, int newversion) {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:

OrderDao(Context context) {    this.context = context;    ordersDBHelper = new OrderDBHelper(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() , in the execution of the time can call the common execSQL(String sql) method or the corresponding Operation API: insert() , delete() update() . and to "check", need to call getReadableDatabase() , at this time can not use Execsql method, must use query() or rawQuery() method. Let's start with the introduction.

Add data

In my demo, there are two ways to add data:
Initializing 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 there is more data to be added at a time, I use the execSQL method directly:

db = Ordersdbhelper.getwritabledatabase ();d b.begintransaction ();d b.execsql ("InsertInto"+ Orderdbhelper.table_name +" (Id, Customname, Orderprice, country)VALUES (1,' ARC ',100,' China ')");d B.execsql ("InsertInto"+ Orderdbhelper.table_name +" (Id, Customname, Orderprice, country)VALUES (2,' Bor ',200,' USA ')");d B.execsql ("InsertInto"+ Orderdbhelper.table_name +" (Id, Customname, Orderprice, country)VALUES (3,' Cut ',500,' Japan ')");d B.execsql ("InsertInto"+ Orderdbhelper.table_name +" (Id, Customname, Orderprice, country)VALUES (4,' Bor ',300,' USA ')insert into  "+ Orderdbhelper.table_name +" (id, Customname, OrderPrice, Country) Span class= "Hljs-keyword" >values (5,  ' ARC ', 600,  ' China ') ");d B.execsql (" insert into  "+ Orderdbhelper.table_name + "(id, Customname, Orderprice, country) values (6,  ' Doom ', 200,  ' China ') ";d b.settransactionsuccessful ();   

Insert a new piece of data
We can also use the insert(String table,String nullColumnHack,ContentValues values) method to insert, the ContentValues internal implementation is HashMap , but there is a difference, ContenValues key can only be a string type, value can only store the basic type of data, such as String,int, can not store objects such things:

public ContentValues() {    // Choosing a default size of 8 based on analysis of typical    // consumption by applications.     mValues = new HashMap<String, Object>(8);}

Using the Insert () method, we insert a new data (7, "Jne", "a", "China"), which we generally treat as transactions (Transaction) for modifying data:

db = Ordersdbhelper.getwritabledatabase ();d b.begintransaction ();//INSERT INTO Orders (Id, Customname, Orderprice,  Country)VALUES (7, "Jne", "the",  "China"); Contentvalues contentvalues = new Contentvalues (); contentvalues.  Put ("Id", 7); contentvalues.  Put ("Customname", "Jne"); contentvalues.  Put ("Orderprice", contentvalues ); Put ("Country", "China");d b.insertorthrow (orderdbhelper.table_name, NULL, contentvalues); Db.settransactionsuccessful ();                

Delete data

The method of deleting the data, in addition execSQL delete(String table,String whereClause,String[] whereArgs) to Whereclause, is to delete the condition, Whereargs is to delete the condition value array.

delete from Orders where Id = 7db.delete(OrderDBHelper.TABLE_NAME, "Id = ?", new String[]{String.valueOf(7)});db.setTransactionSuccessful();

Then look at the deleted source code, which will be assembled to delete the condition and delete the condition value array:

public int delete(String table, String whereClause, String[] whereArgs) { acquireReference(); try { SQLiteStatement statement = new SQLiteStatement(this, "DELETE FROM " + table + (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs); try { return statement.executeUpdateDelete(); } finally { statement.close(); } } finally { releaseReference(); }}

modifying data

It is similar to modifying data and inserting data, except that the method called execSQL can also be update(String table,ContentValues values,String whereClause, String[] whereArgs) :

db = ordersDBHelper.getWritableDatabase();db.beginTransaction();// update Orders set OrderPrice = 800 where Id = 6ContentValues cv = new ContentValues();cv.put("OrderPrice", 800);db.update(OrderDBHelper.TABLE_NAME, cv, "Id = ?", new String[]{String.valueOf(6)});db.setTransactionSuccessful();

Find data

There are two ways to find data, one is public Cursor query(String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit); , the other is public Cursor rawQuery(String sql, String[] selectionArgs) . rawQuerythe notation is similar to the above execSQL , do not introduce here, query the parameters of the method are as follows:

• Table: Tables Name
• Columns: array of column names
• Selection: Conditional words, equivalent to where
• Selectionargs: Conditional words, parameter arrays
• GroupBy: Grouping columns
• Having: grouping conditions
• ORDER BY: Row sequence
• Limit: Paging Query restrictions
• Cursor: The return value, equivalent to the result set resultset

We can see that the returned type is Cursor Cursor 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 in the following ways:

Let's start by checking the information for the user named "Bor":

db = Ordersdbhelper.getreadabledatabase ();SELECT * from Orders where customname = ' Bor 'cursor = Db.query (Orderdbhelper.table_name, Order_columns, " Customname =? ", new string[" {" Bor "}, null, null, null); Span class= "Hljs-keyword" >if (cursor.getcount () > 0) {List <Order> orderlist = new arraylist<order> ( Cursor.getcount ()); while (cursor.movetonext ()) {Order order = Parseorder (cursor); OrderList. add (order);} return orderlist;}           

Of course, we can also query the total number, the minimum value, and so on, to query the total number of users country China, for example:

db = ordersDBHelper.getReadableDatabase();// select count(Id) from Orders where Country = ‘China‘cursor = db.query(OrderDBHelper.TABLE_NAME,        new String[]{"COUNT(Id)"}, "Country = ?", new String[] {"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