Android SQLite detailed and sample code _android

The introductory guide to using the SQLite database in Android is intended to be shared with you in the following sections,

1, what is SQLite

2. Using SQLite in Android

One, what is SQLite

SQLite is an open source, lightweight, embedded, relational database. It was in 2000 by D. Richard Hipp, which supports almost all modern programming languages such as Java, Net, PHP, Ruby, Python, Perl, C, and support Windows, Linux, Unix, Mac OS, Android, Almost all of the mainstream operating system platforms such as iOS.

SQLite is widely used in Apple, Adobe, Google's products. If you have to lift an example of your application SQLite, if your machine has a thunderbolt, please open the Thunder installation directory, search for Sqlite3.dll, is not found its figure? If you have PowerWord, then open his installation directory will also see the existence of Sqlite.dll. Yes, SQLite has long been widely used in the various products we touch, and of course we learn it today because the Android-recommended database in Android development is also built with a fully supported database of SQLite.

Characteristics of SQLite:

1. Acid Services
2.0 Configuration – No installation and management configuration required
3. A complete database stored in a single disk file
4. Database files can be shared freely between machines in different byte order
5. Support Database size to 2TB
6. Small enough, roughly 30,000 lines C code, 250K
7. Faster than some popular databases operate in most ordinary databases
8. Simple, Easy API
9. Includes TCL binding, while supporting other languages via wrapper
10. The source code of good annotation, and has more than 90% test coverage
11. Independence: No additional dependencies
Source full open, you can use it for any purpose, including selling it
13. Support for a variety of development languages, C, PHP, Perl, Java, Asp.net,python

Recommended Sqlite client management tools, Firefox plugin Sqlite manger

Second, the use of SQLite Android

We still learn through an example, and the relevant explanations are written in code comments.

1, a new project lesson15_hellosqlite,activity named Mainhellosqlite.java

2, the preparation of user interface Res/layout/main.xml, ready to add (insert) Delete (delete) (update) check (select) four buttons, prepare a drop-down list spinner, display the data in the table.

<? xml version = "1.0" encoding = "utf-8"?>
<linearlayout android: layout_height = "fill_parent" android: layout_width = "fill_parent" android: orientation = "vertical" xmlns: android = "http://schemas.android.com/apk/res/android">

    <textview android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: textsize = "20sp" android: layout_margintop = "5dp" android: id = "@ + id / TextView01" android: text = "SQLite basic operations" >
    </ textview>

    <button android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: textsize = "20sp" android: layout_margintop = "5dp" android: id = "@ + id / Button01" android: text = "增 | insert" android: minwidth = "200dp"> </ button>

    <button android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: textsize = "20sp" android: layout_margintop = "5dp" android: id = "@ + id / Button02" android: text = "Delete | delete" android: minwidth = "200dp"> </ button>

    <button android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: textsize = "20sp" android: layout_margintop = "5dp" android: id = "@ + id / Button03" android: text = "改 | update" android: minwidth = "200dp"> </ button>

    <button android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: textsize = "20sp" android: layout_margintop = "5dp" android: id = "@ + id / Button04" android: text = "查 | select" android: minwidth = "200dp"> </ button>

    <spinner android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: layout_margintop = "5dp" android: id = "@ + id / Spinner01" android: minwidth = "200dp">
    </ spinner>

    <textview android: layout_height = "wrap_content" android: layout_width = "wrap_content" android: textsize = "20sp" android: layout_margintop = "5dp" android: id = "@ + id / TextView02"> </ textview>
</ linearlayout>

3, in the same directory in Mainhelosqlite.java a new database operation Auxiliary class Dbhelper.java, the contents are as follows:

package android.basic.lesson15;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {

     public DbHelper (Context context, String name, CursorFactory factory,
             int version) {
         super (context, name, factory, version);
     }

     // Run this method when the auxiliary class is established
     @Override
     public void onCreate (SQLiteDatabase db) {

         String sql = "CREATE TABLE pic (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, fileName VARCHAR, description VARCHAR)";
         db.execSQL (sql);
     }

     @Override
     public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
     }

}

4, the contents of the Mainhellosqlite.java are as follows:

package android.basic.lesson15;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.Button;
import android.widget.SimpleCursorAdapter;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

public class MainHelloSqlite extends Activity {

    // SQLiteDatabase object
    SQLiteDatabase db;
    //data storage name
    public String db_name = "gallery.sqlite";
    //Table Name
    public String table_name = "pic";

    // Auxiliary class name
    final DbHelper helper = new DbHelper (this, db_name, null, 1);

    / ** Called when the activity is first created. * /
    @Override
    public void onCreate (Bundle savedInstanceState) {
        super.onCreate (savedInstanceState);
        setContentView (R.layout.main);

        // UI component
        Button b1 = (Button) findViewById (R.id.Button01);
        Button b2 = (Button) findViewById (R.id.Button02);
        Button b3 = (Button) findViewById (R.id.Button03);
        Button b4 = (Button) findViewById (R.id.Button04);

        // Get the database object from the auxiliary class
        db = helper.getWritableDatabase ();

        //Initialization data
        initDatabase (db);
        // Update the data in the drop-down list
        updateSpinner ();

        // Define a button click listener
        OnClickListener ocl = new OnClickListener () {

            @Override
            public void onClick (View v) {

                // ContentValues object
                ContentValues cv = new ContentValues ();
                switch (v.getId ()) {

                // Add button
                case R.id.Button01:

                    cv.put ("fileName", "pic5.jpg");
                    cv.put ("description", "Picture 5");
                    // Add method
                    long long1 = db.insert ("pic", "", cv);
                    // Return line number after adding successfully, -1 after failing
                    if (long1 == -1) {
                        Toast.makeText (MainHelloSqlite.this,
                                "ID is" + long1 + "Image addition failed!", Toast.LENGTH_SHORT)
                                .show ();
                    } else {
                        Toast.makeText (MainHelloSqlite.this,
                                "ID is" + long1 + "Picture added successfully!", Toast.LENGTH_SHORT)
                                .show ();
                    }
                    // Update the drop-down list
                    updateSpinner ();
                    break;

                // Delete the data line whose description is 'Picture 5'
                case R.id.Button02:
                    // delete method
                    long long2 = db.delete ("pic", "description = 'picture 5'", null);
                    // Remove 0 if it fails to delete, return the number of deleted if successful
                    Toast.makeText (MainHelloSqlite.this, "deleted" + long2 + "records",
                            Toast.LENGTH_SHORT) .show ();
                    // Update the drop-down list
                    updateSpinner ();
                    break;

                // Update the data line whose file name is 'pic5.jpg'
                case R.id.Button03:

                    cv.put ("fileName", "pic0.jpg");
                    cv.put ("description", "Picture 0");
                    // Update method
                    int long3 = db.update ("pic", cv, "fileName = 'pic5.jpg'", null);
                    // Remove 0 if it fails to delete, return the number of deleted if successful
                    Toast.makeText (MainHelloSqlite.this, "Updated" + long3 + "Records",
                            Toast.LENGTH_SHORT) .show ();
                    // Update the drop-down list
                    updateSpinner ();
                    break;

                // Query all current data
                case R.id.Button04:
                    Cursor c = db.query ("pic", null, null, null, null,
                            null, null);
                    //cursor.getCount () is the number of records
                    Toast.makeText (MainHelloSqlite.this,
                            "Currently Total" + c.getCount () + "Records, shown below one by one:"
                            Toast.LENGTH_SHORT) .show ();
                    // cyclic display
                    for (c.moveToFirst () ;! c.isAfterLast (); c.moveToNext ()) {
                        Toast.makeText (MainHelloSqlite.this,
                                "Article" + c.getInt (0) + "data, file name is" + c.getString (1) + ", description is" + c.getString (2),
                                Toast.LENGTH_SHORT) .show ();
                    }
                    // Update the drop-down list
                    updateSpinner ();
                    break;
                }
            }
        };

        // bind a listener to the button
        b1.setOnClickListener (ocl);
        b2.setOnClickListener (ocl);
        b3.setOnClickListener (ocl);
        b4.setOnClickListener (ocl);

    }

    // Initialize the table
    public void initDatabase (SQLiteDatabase db) {
        ContentValues cv = new ContentValues ();

        cv.put ("fileName", "pic1.jpg");
        cv.put ("description", "Picture 1");
        db.insert (table_name, "", cv);

        cv.put ("fileName", "pic2.jpg");
        cv.put ("description", "Picture 2");
        db.insert (table_name, "", cv);

        cv.put ("fileName", "pic3.jpg");
        cv.put ("description", "Figurepackage android.basic.lesson15;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.Button;
import android.widget.SimpleCursorAdapter;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

public class MainHelloSqlite extends Activity {

    // SQLiteDatabase object
    SQLiteDatabase db;
    //data storage name
    public String db_name = "gallery.sqlite";
    //Table Name
    public String table_name = "pic";

    // Auxiliary class name
    final DbHelper helper = new DbHelper (this, db_name, null, 1);

    / ** Called when the activity is first created. * /
    @Override
    public void onCreate (Bundle savedInstanceState) {
        super.onCreate (savedInstanceState);
        setContentView (R.layout.main);

        // UI component
        Button b1 = (Button) findViewById (R.id.Button01);
        Button b2 = (Button) findViewById (R.id.Button02);
        Button b3 = (Button) findViewById (R.id.Button03);
        Button b4 = (Button) findViewById (R.id.Button04);

        // Get the database object from the auxiliary class
        db = helper.getWritableDatabase ();

        //Initialization data
        initDatabase (db);
        // Update the data in the drop-down list
        updateSpinner ();

        // Define a button click listener
        OnClickListener ocl = new OnClickListener () {

            @Override
            public void onClick (View v) {

                // ContentValues object
                ContentValues cv = new ContentValues ();
                switch (v.getId ()) {

                // Add button
                case R.id.Button01:

                    cv.put ("fileName", "pic5.jpg");
                    cv.put ("description", "Picture 5");
                    // Add method
                    long long1 = db.insert ("pic", "", cv);
                    // Return line number after adding successfully, -1 after failing
                    if (long1 == -1) {
                        Toast.makeText (MainHelloSqlite.this,
                                "ID is" + long1 + "Image addition failed!", Toast.LENGTH_SHORT)
                                .show ();
                    } else {
                        Toast.makeText (MainHelloSqlite.this,
                                "ID is" + long1 + "Picture added successfully!", Toast.LENGTH_SHORT)
                                .show ();
                    }
                    // Update the drop-down list
                    updateSpinner ();
                    break;

                // Delete the data line whose description is 'Picture 5'
                case R.id.Button02:
                    // delete method
                    long long2 = db.delete ("pic", "description = 'picture 5'", null);
                    // Remove 0 if it fails to delete, return the number of deleted if successful
                    Toast.makeText (MainHelloSqlite.this, "deleted" + long2 + "records",
                            Toast.LENGTH_SHORT) .show ();
                    // Update the drop-down list
                    updateSpinner ();
                    break;

                // Update the data line whose file name is 'pic5.jpg'
                case R.id.Button03:

                    cv.put ("fileName", "pic0.jpg");
                    cv.put ("description", "Picture 0");
                    // Update method
                    int long3 = db.update ("pic", cv, "fileName = 'pic5.jpg'", null);
                    // Remove 0 if it fails to delete, return the number of deleted if successful
                    Toast.makeText (MainHelloSqlite.this, "Updated" + long3 + "Records",
                            Toast.LENGTH_SHORT) .show ();
                    // Update the drop-down list
                    updateSpinner ();
                    break;

                // Query all current data
                case R.id.Button04:
                    Cursor c = db.query ("pic", null, null, null, null,
                            null, null);
                    //cursor.getCount () is the number of records
                    Toast.makeText (MainHelloSqlite.this,
                            "Currently Total" + c.getCount () + "Records, shown below one by one:"
                            Toast.LENGTH_SHORT) .show ();
                    // cyclic display
                    for (c.moveToFirst () ;! c.isAfterLast (); c.moveToNext ()) {
                        Toast.makeText (MainHelloSqlite.this,
                                "Article" + c.getInt (0) + "data, file name is" + c.getString (1) + ", description is" + c.getString (2),
                                Toast.LENGTH_SHORT) .show ();
                    }
                    // Update the drop-down list
                    updateSpinner ();
                    break;
                }
            }
        };

        // bind a listener to the button
        b1.setOnClickListener (ocl);
        b2.setOnClickListener (ocl);
        b3.setOnClickListener (ocl);
        b4.setOnClickListener (ocl);

    }

    // Initialize the table
    public void initDatabase (SQLiteDatabase db) {
        ContentValues cv = new ContentValues ();

        cv.put ("fileName", "pic1.jpg");
        cv.put ("description", "Picture 1");
        db.insert (table_name, "", cv);

        cv.put ("fileName", "pic2.jpg");
        cv.put ("description", "Picture 2");
        db.insert (table_name, "", cv);

        cv.put ("fileName", "pic3.jpg");
        cv.put ("description", "FigureSlice 3 ");
        db.insert (table_name, "", cv);

        cv.put ("fileName", "pic4.jpg");
        cv.put ("description", "Picture 4");
        db.insert (table_name, "", cv);

    }

    // Update the drop-down list
    public void updateSpinner () {

        // define UI components
        final TextView tv = (TextView) findViewById (R.id.TextView02);
        Spinner s = (Spinner) findViewById (R.id.Spinner01);

        // Get the data from the database and put it into the cursor Cursor object
        final Cursor cursor = db.query ("pic", null, null, null, null, null,
                null);

        // Create a simple cursor matcher
        SimpleCursorAdapter adapter = new SimpleCursorAdapter (this,
                android.R.layout.simple_spinner_item, cursor, new String [] {
                        "fileName", "description"}, new int [] {
                        android.R.id.text1, android.R.id.text2});
        adapter.setDropDownViewResource (android.R.layout.simple_spinner_dropdown_item);

        // Set the matcher to the drop-down list
        s.setAdapter (adapter);

        // Define the child element selection listener
        OnItemSelectedListener oisl = new OnItemSelectedListener () {

            @Override
            public void onItemSelected (AdapterView <?> parent, View view,
                    int position, long id) {
                cursor.moveToPosition (position);
                tv.setText ("The description of the current pic is:" + cursor.getString (2));
            }

            @Override
            public void onNothingSelected (AdapterView <?> parent) {
            }
        };

        // Bind child elements to the drop-down list and select a listener
        s.setOnItemSelectedListener (oisl);
    }

    // Delete data in the table when the window is destroyed
    @Override
    public void onDestroy () {
        super.onDestroy ();
        db.delete (table_name, null, null);
        updateSpinner ();
    }
}

5, run the program to see the results.

This example uses the Insert,delete,update,query method that Sqlitedatabase has already encapsulated, and interested students can use the Sqlitedatabase execsql () method and the Rawquery () method to achieve it. All right, here's the story.