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"