Android using SQLite Select,Insert,Update,Delete

Android using SQLite Select,Insert,Update,Delete

Last time I told you all about SQLite and use the basic information, I believe that friends of SQLite have some understanding, and that it today, I'll share with you how to use SQLite in Android.
Now the mainstream mobile devices like the Android, iPhone and so the use SQLite as the storage engine of complex data, in our time developing applications for the mobile device, perhaps we should use to our SQLite to store large amounts of data, so we need to have a mobile device SQLite development skills on. For the Android platform, the system built a rich API for developers to operate SQLite, we can easily complete access to the data.
Here to tell you about SQLite common method of operation, for convenience, I will write the code in the Activity's onCreate in:

Override 
    protected  void  onCreate (Bundle savedInstanceState) { 
        Super .onCreate (savedInstanceState); 
         
        // Open or create test.db database 
        SQLiteDatabase db = openOrCreateDatabase ( "test.db" , Context.MODE_PRIVATE,  null ); 
        db.execSQL ( "DROP TABLE IF EXISTS person" ); 
        // Create the person table 
        db.execSQL ( "CREATE TABLE person (_id INTEGER PRIMARY KEY AUTOINCREMENT, VARCHAR name, age SMALLINT)" ); 
        Person person =  new  Person (); 
        person.name =  "John" ; 
        person.age =  30 ; 
        // Insert data 
        db.execSQL ( "?? INSERT INTO person VALUES (NULL,,)" ,  new  Object [] {person.name, person.age}); 
         
        person.name =  "David" ; 
        person.age =  33 ; 
        // ContentValues ​​store data in the form of key-value pairs 
        ContentValues ​​cv =  new  ContentValues ​​(); 
        cv.put ( "name" , person.name); 
        cv.put ( "age" , person.age); 
        // Insert the data ContentValues 
        db.insert ( "person" ,  null , cv); 
         
        cv =  new  ContentValues ​​(); 
        cv.put ( "age" ,  35 ); 
        // Update the data 
        db.update ( "person" , cv,  "name =?" ,  new  String [] { "John" }); 
         
        Cursor c = db.rawQuery ( "SELECT * FROM person WHERE age> =?" ,  new  String [] { "33" }); 
        while  (c.moveToNext ()) { 
            int  _id = c.getInt (c.getColumnIndex ( "_id" )); 
            String name = c.getString (c.getColumnIndex ( "name" )); 
            int  age = c.getInt (c.getColumnIndex ( "age" )); 
            Log.i ( "db" ,  "_id =>"  + _id +  ", name =>"  + name +  ", age =>"  + age); 
         
        c.close (); 
         
        // Delete data 
        db.delete ( "person" ,  "? age <" ,  new  String [] { "35" }); 
         
        // Close the current database 
        db.close (); 
         
        // Delete test.db database 
// DeleteDatabase ("test.db"); 
     

After executing the above code, the system will generate a "test.db" database file in / data / data / [PACKAGE_NAME] / databases directory, as shown:



The above code basically cover most of the database operation; for add, update, and delete it, we can use

db.executeSQL (String sql); 
db.executeSQL (String SQL, Object [] bindArgs); // SQL statement using placeholders, then the second parameter is the actual parameter set 

In addition to the unified form, they each have their own methods of operation:

db.insert (String table, String nullColumnHack, ContentValues ​​values); 
db.update (String table, Contentvalues ​​values, String whereClause, String whereArgs); 
db.delete (String table, String whereClause, String whereArgs); 

The first parameter of the above three methods are said to be operating table; insert the second parameter indicates that if the insertion of the data in each column is empty, you need to specify the name of a column in a row, this system column is set to NULL, not errors; insert the third parameter is the variable ContentValues ​​type, is key to the composition of the Map, key representatives of the column name, value representing the columns values ​​to be inserted; update of the second parameters are very similar, but it is key to update the field value value for the latest third parameter whereClause expressed WHERE expression, such as "age>? and age <?" and so on, the last parameter is a placeholder whereArgs The actual parameter values; Arguments delete method is the same.
Here's what queries. Query with respect to several operating above is more complicated, because we are faced with a variety of query conditions, so the system also takes into account this complexity, provides us with a more extensive inquiry form:

db.rawQuery (String sql, String [] selectionArgs); 
db.query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy); 
db.query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit); 
db.query (String distinct, String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit);

The above are some common query method, the first one of the most simple, all the SQL statements are organized into a string, using placeholders instead of the actual parameters, selectionArgs is a placeholder for the actual parameter set; following several parameters are very similar, columns indicate the name of the column all set to query, selection represents the conditional statement after WHERE, you can use placeholders, groupBy designated grouping column names, having specified grouping criteria, with groupBy use, orderBy specify the sort column name, limit specified paging parameters, distinct can specify "true" or "false" indicates whether or not to filter duplicates. Note that, selection, groupBy, having, orderBy, limit these parameters are not included in "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT" and other SQL keywords.
Finally, they also return a Cursor object that represents the data set cursor, somewhat similar to JavaSE the ResultSet.
Here is a common method Cursor object:

c.move ( int  offset);  // current position as a reference, move to the specified line 
c.moveToFirst ();     // move to the first row 
c.moveToLast ();      // move to the last row 
c.moveToPosition ( int  position);  // move to the specified line 
c.moveToPrevious ();  // move to the previous line 
c.moveToNext ();      // move to the next line 
c.isFirst ();         // points to the first article 
c.isLast ();      // points to last 
c.isBeforeFirst ();   // points to the first before 
; c.isAfterLast ()     // if point after the last one 
c.isNull ( int  columnIndex);   // the specified column is empty (column cardinality 0) 
c.isClosed ();        // cursor is turned off 
c.getCount ();        // total number of data items 
c.getPosition ();     // Returns the number of rows in the cursor is pointing to the current 
c.getColumnIndex (String columnName); // Returns the name of a column corresponding to the column index value 
c.getString ( int  columnIndex);    // Returns the value of the specified column of the current row 


In the above code example, has used this method for several commonly used some more information about, you can refer to the official documentation for instructions.
Finally, after we completed the operation of the database, remember to call SQLiteDatabase the close () method releases the database connection, or prone SQLiteException.
SQLite is above the basic applications, but in the actual development, in order to better manage and maintain the database, we will package a database operation class inherits from SQLiteOpenHelper class, then the class-based, repackaging our business logic methods
Here, we have an example to explain the specific usage, we create a new project named db, structured as follows:




Which DBHelper inherited SQLiteOpenHelper, as a base class to maintain and manage the database, DBManager is based on DBHelper, encapsulates the common business method, Person is our person table corresponding JavaBean, MainActivity that we display interface.
Let us first look at DBHelper:

public  synchronized  SQLiteDatabase getReadableDatabase () { 
    if  (mDatabase! =  null  && mDatabase.isOpen ()) { 
        // If mDatabase not empty and has opened the direct return 
        return  mDatabase; 
     
 
    if  (mIsInitializing) { 
        // If you are initializing an exception is thrown 
        throw  new  IllegalStateException ( "getReadableDatabase Called recursively" ); 
     
 
    // Start instantiation database mDatabase 
 
    try  { 
        // Note that this is called getWritableDatabase () method 
        return  getWritableDatabase (); 
    }  catch  (SQLiteException e) { 
        if  (mName ==  null ) 
            throw  e;  // Can not Open a temp database Read-only! 
        Log.e (TAG,  "Could not Open"  + mName +  "for Writing (Will try Read-only):" , e); 
     
 
    // If you can not open the database in read-write mode places open read-only 
 
    SQLiteDatabase db =  null ; 
    try  { 
        mIsInitializing =  true ; 
        String path = mContext.getDatabasePath (mName) .getPath (); // Get the database path 
        // Open the database in read-only mode 
        db = SQLiteDatabase.openDatabase (path, mFactory, SQLiteDatabase.OPEN_READONLY); 
        if  (db.getVersion ()! = mNewVersion) { 
            throw  new  SQLiteException ( "Can not upgrade from Version Read-only database"  + db.getVersion () +  "to" 
                    + MNewVersion +  ":"  + path); 
         
 
        onOpen (db); 
        Log.w (TAG,  "Opened"  + mName +  "in Read-only mode" ); 
        mDatabase = db; // specify the new open database mDatabase 
        return  mDatabase; // Returns the open database 
    }  finally  { 
        mIsInitializing =  false ; 
        if  (db! =  null  && db! = mDatabase) 
            db.close (); 
     
 


In getReadableDatabase () method, first determine whether the database instance already exists and is open, if it is, then the direct return that instance, or else trying to get a read-write mode database instance, if they get the disk space is full, etc. if it fails, then the database is opened in read-only mode, access to the database instance and return, and then assigned to mDatabase latest open database instance. Since there may be calls to getWritableDatabase () method, we will look at the:

public  synchronized  SQLiteDatabase getWritableDatabase () { 
    if  (mDatabase! =  null  && mDatabase.isOpen () &&! mDatabase.isReadOnly ()) { 
        // If mDatabase not empty and is not a read-only mode is turned on returns that instance 
        return  mDatabase; 
     
 
    if  (mIsInitializing) { 
        throw  new  IllegalStateException ( "getWritableDatabase Called recursively" ); 
     
 
    // If we have a read-only database open, someone could be using it 
    // (Though they should not), which would cause a lock to be held on 
    // The file, and our attempts to open the database read-write would 
    // Fail waiting for the file lock. To prevent that, we acquire the 
    // Lock on the read-only database, which shuts out other users. 
 
    boolean  success =  false ; 
    SQLiteDatabase db =  null ; 
    // If mDatabase not empty then locked to prevent other operations 
    if  (mDatabase! =  null ) 
        mDatabase.lock (); 
    try  { 
        mIsInitializing =  true ; 
        if  (mName ==  null ) { 
            db = SQLiteDatabase.create ( null ); 
        }  else  { 
            // Open or create a database 
            db = mContext.openOrCreateDatabase (mName,  0 , mFactory); 
         
        // Get the database version (database, if you just created, version 0) 
        int  Version = db.getVersion (); 
        // Compare versions (our code version mNewVersion 1) 
        if  (Version! = mNewVersion) { 
            db.beginTransaction (); // start transaction 
            try  { 
                if  (Version ==  0 ) { 
                    // Execute our onCreate method 
                    onCreate (db); 
                }  else  { 
                    // If we apply the upgrade mNewVersion 2, and the original version of a method of execution onUpgrade 
                    onUpgrade (db, version, mNewVersion); 
                 
                db.setVersion (mNewVersion); // set up the latest version 
                db.setTransactionSuccessful (); // Set the transaction success 
            }  finally  { 
                db.endTransaction (); // end the transaction 
             
         
 
        onOpen (db); 
        success =  true ; 
        return  ; db // Returns the read-write mode database instance 
    }  finally  { 
        mIsInitializing =  false ; 
        if  (success) { 
            // Open success 
            if  (mDatabase! =  null ) { 
                // If it has a value mDatabase to close 
                try  { 
                    mDatabase.close (); 
                }  catch  (Exception e) { 
                 
                mDatabase.unlock (); // Unlock 
             
            mDatabase = db; // assignment to mDatabase 
        }  else  { 
            // Open the case of failure: unlocked, closed 
            if  (mDatabase! =  null ) 
                mDatabase.unlock (); 
            if  (db! =  null ) 
                db.close (); 
         
     
 


As you can see, a few key steps, first determine mDatabase if not empty open read-only mode is not directly returned, otherwise if mDatabase not empty the lock, and then begin to open or create a database, compare versions, according to The version number to call the appropriate method to set a new version number for the database, and finally release the old is not empty of mDatabase and unlock, open the new database instance confers mDatabase, and returns the latest instance.
After reading the above process, you perhaps know a lot, if not in the face of disk space, etc., getReadableDatabase () will generally return and getWritableDatabase () the same database instance, so we use getWritableDatabase in DBManager constructor () Gets the database instance is used throughout the application is feasible. Of course, if you're really worried about that happening, then you can start () Gets the data instance with the getWritableDatabase, if an exception is encountered, then trying () Gets the instance with the getReadableDatabase, of course, this time instance you can read but not write access to a.
Finally, let's look at how to use these data manipulation methods to display data, the following is MainActivity.java layout files and code:

<? XML  Version = "1.0"  encoding = "UTF-8" ?> 
< LinearLayout  xmlns: Android = "http://schemas.android.com/apk/res/android" 
    Android: Orientation = "Vertical" 
    Android: layout_width = "fill_parent" 
    Android: layout_height = "fill_parent" > 
    < Button 
        Android: layout_width = "fill_parent" 
        Android: layout_height = "wrap_content" 
        Android: text = "add" 
        Android: onClick = "add" /> 
    < Button 
        Android: layout_width = "fill_parent" 
        Android: layout_height = "wrap_content" 
        Android: text = "Update" 
        Android: onClick = "Update" /> 
    < Button 
        Android: layout_width = "fill_parent" 
        Android: layout_height = "wrap_content" 
        Android: text = "delete" 
        Android: onClick = "delete" /> 
    < Button 
        Android: layout_width = "fill_parent" 
        Android: layout_height = "wrap_content" 
        Android: text = "Query" 
        Android: onClick = "Query" /> 
    < Button 
        Android: layout_width = "fill_parent" 
        Android: layout_height = "wrap_content" 
        Android: text = "queryTheCursor" 
        Android: onClick = "queryTheCursor" /> 
    < ListView 
        Android: ID = "@ + ID / listView" 
        Android: layout_width = "fill_parent" 
        Android: layout_height = "wrap_content" /> 
</ LinearLayout > 


Package  com.scott.db; 
 
Import  java.util.ArrayList; 
Import  java.util.HashMap; 
Import  java.util.List; 
Import  java.util.Map; 
 
Import  android.app.Activity; 
Import  android.database.Cursor; 
Import  android.database.CursorWrapper; 
Import  android.os.Bundle; 
Import  android.view.View; 
Import  android.widget.ListView; 
Import  android.widget.SimpleAdapter; 
Import  android.widget.SimpleCursorAdapter; 
 
 
public  class  MainActivity  extends  Activity { 
    
    Private  DBManager MGR; 
    Private  ListView listView; 
     
    Override 
    public  void  onCreate (Bundle savedInstanceState) { 
        Super .onCreate (savedInstanceState); 
        setContentView (R.layout.main); 
        listView = (ListView) findViewById (R.id.listView); 
        // Initialize DBManager 
        MGR =  new  DBManager ( this ); 
     
     
    Override 
    protected  void  onDestroy () { 
        Super .onDestroy (); 
        Activity close the last application should release DB // 
        mgr.closeDB (); 
     
     
    public  void  add (View View) { 
        ArrayList <Person> persons =  new  ArrayList <Person> (); 
         
        Person1 person =  new  Person ( "Ella" ,  22 ,  "Lively Girl" ); 
        Person2 person =  new  Person ( "Jenny" ,  22 ,  "beautiful Girl" ); 
        Person3 person =  new  Person ( "Jessica" ,  23 ,  "Sexy Girl" ); 
        Person4 person =  new  Person ( "Kelly" ,  23 ,  "Hot Baby" ); 
        Person5 person =  new  Person ( "Jane" ,  25 ,  "a Pretty woman" ); 
         
        persons.add (person1); 
        persons.add (person2); 
        persons.add (person3); 
        persons.add (person4); 
        persons.add (person5); 
         
        mgr.add (persons); 
     
     
    public  void  Update (View View) { 
        Person person =  new  Person (); 
        person.name =  "Jane" ; 
        person.age =  30 ; 
        mgr.updateAge (person); 
     
     
    public  void  delete (View View) { 
        Person person =  new  Person (); 
        person.age =  30 ; 
        mgr.deleteOldPerson (person); 
     
     
    public  void  Query (View View) { 
        List <Person> persons = mgr.query (); 
        ArrayList <Map <String, String >> List =  new  ArrayList <Map <String, String >> (); 
        for  (Person person: persons) { 
            HashMap <String, String> map =  new  HashMap <String, String> (); 
            map.put ( "name" , person.name); 
            map.put ( "info" , person.age +  "years old,"  + person.info); 
            list.add (map); 
         
        SimpleAdapter Adapter =  new  SimpleAdapter ( this , List, android.R.layout.simple_list_item_2, 
                    new  String [] { "name" ,  "info" },  new  int [] {android.R.id.text1, android.R.id.text2}); 
        listView.setAdapter (adapter); 
     
     
    public  void  queryTheCursor (View View) { 
        Cursor c = mgr.queryTheCursor (); 
        startManagingCursor (c);  // entrusted to activity according to their life cycle to manage the life cycle of Cursor 
        CursorWrapper cursorWrapper =  new  CursorWrapper (c) { 
            Override 
            public  String getString ( int  columnIndex) { 
                // Will be added before the age Introduction 
                if  (getColumnName (columnIndex) .equals ( "info" )) { 
                    int  age = getInt (getColumnIndex ( "age" )); 
                    return  age +  "years old,"  +  Super .getString (columnIndex); 
                 
                return  Super .getString (columnIndex); 
             
         
        // Make sure that the query results in "_id" column 
        SimpleCursorAdapter Adapter =  new  SimpleCursorAdapter ( this , android.R.layout.simple_list_item_2,  
                cursorWrapper,  new  String [] { "name" ,  "info" },  new  int [] {android.R.id.text1, android.R.id.text2}); 
        ListView listView = (ListView) findViewById (R.id.listView); 
        listView.setAdapter (adapter); 
     
 


It should be noted that the application of SimpleCursorAdapter, when we use this adapter, we must first get a Cursor object, and there are several issues: how to manage the life cycle of Cursor, if the packaging Cursor, Cursor result set is what needs attention.
If you manually to manage Cursor words will be very troublesome, there is a certain risk, there will be mishandled during operation if abnormal, fortunately Activity provides us startManagingCursor (Cursor cursor) method, it will be based Activity lifecycle to manage current The Cursor object, Below is a description of the method:

The article mentioned, startManagingCursor method to manage the life cycle of the current Cursor object based Activity lifecycle, that when he stopped Activity Cursor will automatically call the deactivate method, disable the cursor back to the screen when the Activity it calls Cursor The requery method again query, when Activity destroyed managed Cursor will automatically shut down the release.
How to package Cursor: we will use to CursorWrapper objects to package our Cursor object, we need to achieve data conversion work, this CursorWrapper actually implements the Cursor interface. We inquire acquired Cursor Cursor fact is quoted, but the system actually returned to us must be an object instance of a class that implements the Cursor interface, we use CursorWrapper wrap this instance, and then use the results SimpleCursorAdapter displayed on the list.
Cursor result set needs to pay attention to what: One of the most important to note is that in our result set must contain an "_id" column, otherwise SimpleCursorAdapter will betray you, why should I? This stems from the norm because SQLite, the main key to "_id" as the standard. Solution three: first, when construction of the table according to the specification do; the second, when a query with an alias, for example: SELECT id AS _id FROM person; third, in CursorWrapper make a fuss:

CursorWrapper cursorWrapper =  new  CursorWrapper (c) { 
    Override 
    public  int  getColumnIndexOrThrow (String columnName)  throws  IllegalArgumentException { 
        if  (columnName.equals ( "_id" )) { 
            return  Super .getColumnIndex ( "ID" ); 
         
        return  Super .getColumnIndexOrThrow (columnName); 
     
 


If you try to get the "_id" column corresponding index from CursorWrapper, we will return query results in "id" column corresponding to the index.
Finally, we look at the outcome:





Share this

Related Posts

Previous
Next Post »