android sqlite 操作

作者：wangjunwj


介绍
在 Android 中使用 SQLite, ContentProvider 
数据库支持(SQLite) - Android 开发平台提供了操作 SQLite 数据库的相关 API
内容提供器(ContentProvider) - 当数据需要在应用程序之间共享时，可以在某程序中使用 ContentProvider 定义 URI， 以使其它应用程序可以通过此 URI 访问指定的数据


1、SQLite 的 Demo
DatabaseHelper.java

Java代码
代码  
package com.webabcd.SQLite; 
 
import android.content.Context; 
import android.database.sqlite.SQLiteDatabase; 
import android.database.sqlite.SQLiteOpenHelper; 
import android.database.sqlite.SQLiteDatabase.CursorFactory; 
 
// 数据库操作的 Helper 类 
public class DatabaseHelper extends SQLiteOpenHelper { 
 
    DatabaseHelper(Context context, String name, CursorFactory cursorFactory, int version) { 
        super(context, name, cursorFactory, version); 
    } 
 
    @Override 
    public void onCreate(SQLiteDatabase db) { 
        // TODO 创建数据库后，对数据库的操作 
    } 
 
    @Override 
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
        // TODO 更改数据库版本的操作 
    } 
 
    @Override 
    public void onOpen(SQLiteDatabase db) { 
        super.onOpen(db); 
         
        // TODO 每次成功打开数据库后首先被执行 
    } 
} 
 
 
Main.java 
 
代码  
package com.webabcd.SQLite; 
 
import java.util.Random; 
 
import android.app.Activity; 
import android.content.ContentValues; 
import android.database.Cursor; 
import android.database.SQLException; 
import android.database.sqlite.SQLiteDatabase; 
import android.os.Bundle; 
import android.view.View; 
import android.widget.Button; 
import android.widget.TextView; 
 
public class Main extends Activity { 
 
    private DatabaseHelper dbHelper; 
 
    private static final String DATABASE_NAME = "db.db"; 
    private static final int DATABASE_VERSION = 1; 
    private static final String TABLE_NAME = "employee"; 
 
    TextView txtMsg; 
 
    /** Called when the activity is first created. */ 
    @Override 
    public void onCreate(Bundle savedInstanceState) { 
        super.onCreate(savedInstanceState); 
        setContentView(R.layout.main); 
 
        dbHelper = new DatabaseHelper(this, DATABASE_NAME, null, 
                DATABASE_VERSION); 
 
        txtMsg = (TextView) this.findViewById(R.id.txtMsg); 
 
        Button btn1 = (Button) this.findViewById(R.id.btn1); 
        btn1.setText("创建表"); 
        btn1.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                CreateTable(); 
            } 
        }); 
 
        Button btn2 = (Button) this.findViewById(R.id.btn2); 
        btn2.setText("插入 3 条记录"); 
        btn2.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                insertItem(); 
            } 
        }); 
 
        Button btn3 = (Button) this.findViewById(R.id.btn3); 
        btn3.setText("删除全部记录"); 
        btn3.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                deleteItem(); 
            } 
        }); 
 
        Button btn4 = (Button) this.findViewById(R.id.btn4); 
        btn4.setText("更新指定数据"); 
        btn4.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                updateItem(); 
            } 
        }); 
 
        Button btn5 = (Button) this.findViewById(R.id.btn5); 
        btn5.setText("显示全部数据"); 
        btn5.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                showItems(); 
            } 
        }); 
 
        Button btn6 = (Button) this.findViewById(R.id.btn6); 
        btn6.setText("删除表"); 
        btn6.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                dropTable(); 
            } 
        }); 
    } 
 
    // 创建数据表 
    private void CreateTable() { 
        SQLiteDatabase db = dbHelper.getWritableDatabase(); 
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME 
                + " (ID INTEGER PRIMARY KEY, Name VARCHAR, Age INTEGER);"; 
        try { 
            db.execSQL(sql); 
            txtMsg.append("数据表成功创建\n"); 
        } catch (SQLException ex) { 
            txtMsg.append("数据表创建错误\n" + ex.toString() + "\n"); 
        } 
    } 
 
    // 插入数据 
    private void insertItem() { 
        SQLiteDatabase db = dbHelper.getWritableDatabase(); 
         
        try { 
            Random random = new Random(); 
            for (int i = 0; i < 3; i++) { 
                String sql = "insert into " + TABLE_NAME 
                        + " (name, age) values ('name" + String.valueOf(i) 
                        + "', " + random.nextInt() + ")"; 
                // execSQL() - 执行指定的 sql 
                db.execSQL(sql); 
            } 
            txtMsg.append("成功插入 3 条数据\n"); 
        } catch (SQLException ex) { 
            txtMsg.append("插入数据失败\n" + ex.toString() + "\n"); 
        } 
    } 
 
    // 删除数据 
    private void deleteItem() { 
        try { 
            SQLiteDatabase db = dbHelper.getWritableDatabase(); 
            db.delete(TABLE_NAME, " id < 999999", null); 
            txtMsg.append("成功删除数据\n"); 
        } catch (SQLException e) { 
            txtMsg.append("删除数据失败\n"); 
        } 
    } 
 
    // 更新数据 
    private void updateItem() { 
        SQLiteDatabase db = dbHelper.getWritableDatabase(); 
 
        try { 
            ContentValues values = new ContentValues(); 
            values.put("name", "批量更新后的名字"); 
 
            db.update(TABLE_NAME, values, "id<?", new String[] { "3" }); 
            txtMsg.append("成功更新数据\n"); 
        } catch (SQLException e) { 
            txtMsg.append("更新数据失败\n"); 
        } 
    } 
 
    // 查询数据 
    private void showItems() { 
        SQLiteDatabase db = dbHelper.getReadableDatabase(); 
 
        try { 
            String[] column = { "id", "name", "age" }; 
            Cursor cursor = db.query(TABLE_NAME, column, null, null, null, 
                    null, null); 
            Integer num = cursor.getCount(); 
            txtMsg.append("共 " + Integer.toString(num) + " 条记录\n"); 
            cursor.moveToFirst(); 
 
            while (cursor.getPosition() != cursor.getCount()) { 
                txtMsg.append(Integer.toString(cursor.getPosition()) + "," 
                        + String.valueOf(cursor.getString(0)) + "," 
                        + cursor.getString(1) + "," 
                        + String.valueOf(cursor.getString(2)) + "\n"); 
                cursor.moveToNext(); 
            } 
        } catch (SQLException ex) { 
            txtMsg.append("读取数据失败\n" + ex.toString() + "\n"); 
        } 
    } 
 
    // 删除数据表 
    private void dropTable() { 
        SQLiteDatabase db = dbHelper.getWritableDatabase(); 
        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME; 
        try { 
            db.execSQL(sql); 
            txtMsg.append("数据表删除成功\n"); 
        } catch (SQLException ex) { 
            txtMsg.append("数据表删除错误\n" + ex.toString() + "\n"); 
        } 
    } 
} 
 
 
2、ContentProvider 的 Demo 
MyUser.java 
 
代码  
package com.webabcd.contentprovider; 
 
import android.net.Uri; 
import android.provider.BaseColumns; 
 
// 自定义 ContentProvider 所需的实体类 
public class MyUser { 
 
    // 必须要有 _id 字段。本例中 BaseColumn 类中已经包含了 _id 字段 
    public static final class User implements BaseColumns { 
         
        // 定义 CONTENT_URI 
        public static final Uri CONTENT_URI = Uri.parse("content://com.webabcd.MyContentProvider"); 
 
        // 表数据列 
        public static final String USER_NAME = "USER_NAME"; 
    } 
} 
 
 
MyContentProvider.java 
 
代码  
package com.webabcd.contentprovider; 
 
import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileOutputStream; 
 
import org.apache.http.util.EncodingUtils; 
 
import android.content.ContentProvider; 
import android.content.ContentUris; 
import android.content.ContentValues; 
import android.database.Cursor; 
import android.database.MatrixCursor; 
import android.net.Uri; 
 
// 继承 ContentProvider 以实现自定义的 ContentProvider（基于文件的信息存储） 
public class MyContentProvider extends ContentProvider { 
 
    private File file; 
    private FileOutputStream out; 
    private FileInputStream in; 
 
    // ContentProvider 的删除数据接口 
    @Override 
    public int delete(Uri uri, String selection, String[] selectionArgs) { 
        // TODO Auto-generated method stub 
        return 0; 
    } 
 
    @Override 
    public String getType(Uri uri) { 
        // TODO Auto-generated method stub 
        return null; 
    } 
 
    // ContentProvider 的插入数据接口  
    @Override 
    public Uri insert(Uri uri, ContentValues values) { 
 
        try { 
            out = new FileOutputStream(file); 
            out.write(values.getAsString(MyUser.User.USER_NAME).getBytes()); 
            out.close(); 
 
            int rowId = 0; 
            Uri rowUri = ContentUris.appendId( 
                    MyUser.User.CONTENT_URI.buildUpon(), rowId).build(); 
            getContext().getContentResolver().notifyChange(rowUri, null); 
 
            return rowUri; 
        } catch (Exception e) { 
            return null; 
        } 
    } 
 
    // 创建用于保存信息的文件 
    @Override 
    public boolean onCreate() { 
        try { 
            // 每个包中应用程序的私有目录为：/data/data/包名/ 
            // SD 卡目录为：/sdcard 
            file = new File("/data/data/com.webabcd.contentprovider/", 
                    "demo.txt"); 
            if (!file.exists()) 
                file.createNewFile(); 
 
            return true; 
        } catch (Exception ex) { 
            return false; 
        } 
    } 
 
    // ContentProvider 的查询数据接口 
    @Override 
    public Cursor query(Uri uri, String[] projection, String selection, 
            String[] selectionArgs, String sortOrder) { 
         
        String content; 
 
        try { 
            in = new FileInputStream(file); 
            int length = (int) file.length(); 
            byte[] buffer = new byte[length]; 
            in.read(buffer, 0, length); 
            content = EncodingUtils.getString(buffer, "UTF-8"); 
            in.close(); 
 
            String[] columns = new String[] { MyUser.User._ID, MyUser.User.USER_NAME }; 
            MatrixCursor cur = new MatrixCursor(columns); 
            String[] values = new String[] { "0", content }; 
            cur.moveToFirst(); 
            cur.addRow(values); 
 
            return cur; 
        } catch (Exception e) { 
            return null; 
        } 
    } 
 
    // ContentProvider 的更新数据接口 
    @Override 
    public int update(Uri uri, ContentValues values, String selection, 
            String[] selectionArgs) { 
        // TODO Auto-generated method stub 
        return 0; 
    } 
} 
 
 
Main.java 
 
代码  
package com.webabcd.contentprovider; 
 
import java.util.Random; 
 
import android.app.Activity; 
import android.content.ContentUris; 
import android.content.ContentValues; 
import android.database.Cursor; 
import android.net.Uri; 
import android.os.Bundle; 
import android.provider.Contacts; 
import android.provider.Contacts.People; 
import android.util.Log; 
import android.view.View; 
import android.widget.Button; 
import android.widget.Toast; 
 
/*
* 几个常用的系统内置的 ContentProvider 如下： 
* content://media/internal/images  这个URI将返回设备上存储的所有图片
* content://contacts/people/ 这个URI将返回设备上的所有联系人信息
* content://contacts/people/45 这个URI返回单个结果（联系人信息中ID为45的联系人记录）
*/ 
public class Main extends Activity { 
    /** Called when the activity is first created. */ 
    @Override 
    public void onCreate(Bundle savedInstanceState) { 
        super.onCreate(savedInstanceState); 
        setContentView(R.layout.main); 
 
        Button btn1 = (Button) this.findViewById(R.id.btn1); 
        btn1.setText("新增联系人记录"); 
        btn1.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                Random random = new Random(); 
                insertRecords("name" + String.valueOf(random.nextInt()), String 
                        .valueOf(random.nextInt())); 
            } 
        }); 
 
        Button btn2 = (Button) this.findViewById(R.id.btn2); 
        btn2.setText("查看联系人记录"); 
        btn2.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                displayRecords(); 
            } 
        }); 
 
        Button btn3 = (Button) this.findViewById(R.id.btn3); 
        btn3.setText("清除联系人记录"); 
        btn3.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                deleteRecords(); 
            } 
        }); 
 
        Button btn4 = (Button) this.findViewById(R.id.btn4); 
        btn4.setText("更新联系人记录"); 
        btn4.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                // 此处只是演示，id 来自 People._ID ，可参见 displayRecords() 是如何获取 id 的 
                int id = 0; 
                updateRecord(id, "修改后的name"); 
            } 
        }); 
 
        Button btn5 = (Button) this.findViewById(R.id.btn5); 
        btn5.setText("新增记录到 MyContentProvider"); 
        btn5.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                insertRecord2MyContentProvider("webabcd"); 
            } 
        }); 
 
        Button btn6 = (Button) this.findViewById(R.id.btn6); 
        btn6.setText("获取记录从 MyContentProvider"); 
        btn6.setOnClickListener(new Button.OnClickListener() { 
            public void onClick(View v) { 
                displayRecord2MyContentProvider(); 
            } 
        }); 
    } 
 
    // 调用 ContentProvider 的插入接口 
    private void insertRecords(String name, String phoneNum) { 
        ContentValues values = new ContentValues(); 
        values.put(People.NAME, name); 
        Uri uri = getContentResolver().insert(People.CONTENT_URI, values); 
        Log.d("MyDebug", uri.toString()); 
        Uri numberUri = Uri.withAppendedPath(uri, 
                People.Phones.CONTENT_DIRECTORY); 
        Log.d("MyDebug", numberUri.toString()); 
 
        values.clear(); 
        values.put(Contacts.Phones.TYPE, People.Phones.TYPE_MOBILE); 
        values.put(People.NUMBER, phoneNum); 
        getContentResolver().insert(numberUri, values); 
    } 
 
    // 调用 ContentProvider 的查询接口 
    private void displayRecords() { 
        String[] columns = new String[] { People._ID, People.NAME, 
                People.NUMBER }; 
        Uri contacts = People.CONTENT_URI; 
        Log.d("MyDebug", contacts.toString()); 
        Cursor cur = managedQuery(contacts, columns, // 要返回的数据字段 
                null, // WHERE子句 
                null, // WHERE 子句的参数 
                null // Order-by子句 
        ); 
 
        if (cur.moveToFirst()) { 
            String id = null; 
            String name = null; 
            String phoneNo = null; 
            while (cur.getPosition() != cur.getCount()) { 
                id = cur.getString(cur.getColumnIndex(People._ID)); 
                name = cur.getString(cur.getColumnIndex(People.NAME)); 
                phoneNo = cur.getString(cur.getColumnIndex(People.NUMBER)); 
 
                Toast.makeText(this, id + " / " + name + " / " + phoneNo, 
                        Toast.LENGTH_SHORT).show(); 
                cur.moveToNext(); 
            } 
        } 
    } 
 
    // 调用 ContentProvider 的删除接口 
    private void deleteRecords() { 
        Uri uri = People.CONTENT_URI; 
        Log.d("MyDebug", uri.toString()); 
        getContentResolver().delete(uri, null, null); 
        // getContentResolver().delete(uri, "NAME=" + "'name'", null); 
    } 
 
    // 调用 ContentProvider 的更新接口 
    private void updateRecord(int recordNo, String name) { 
        Uri uri = ContentUris.withAppendedId(People.CONTENT_URI, recordNo); 
        Log.d("MyDebug", uri.toString()); 
        ContentValues values = new ContentValues(); 
        values.put(People.NAME, name); 
        getContentResolver().update(uri, values, null, null); 
    } 
 
     
    // 调用自定义 ContentProvider 的插入接口 
    private void insertRecord2MyContentProvider(String name) { 
        ContentValues values = new ContentValues(); 
        values.put(MyUser.User.USER_NAME, name); 
        getContentResolver().insert(MyUser.User.CONTENT_URI, values); 
 
    } 
 
    // 调用自定义 ContentProvider 的查询接口 
    private void displayRecord2MyContentProvider() { 
        String[] columns = new String[] { MyUser.User.USER_NAME }; 
        Uri uri = MyUser.User.CONTENT_URI; 
        Cursor cur = managedQuery(uri, columns, null, null, null); 
 
        while (cur.getPosition() != cur.getCount()) { 
            String id = cur.getString(cur.getColumnIndex(People._ID)); 
            String name = cur.getString(cur.getColumnIndex(MyUser.User.USER_NAME)); 
            Toast.makeText(this, 
                    id + " / " + name, 
                    Toast.LENGTH_SHORT).show(); 
            cur.moveToNext(); 
        } 
    } 
} 
 
AndroidManifest.xml 
 
代码  
<?xml version="1.0" encoding="utf-8"?> 
<manifest xmlns:android="http://schemas.android.com/apk/res/android" 
      package="com.webabcd.contentprovider" 
      android:versionCode="1" 
      android:versionName="1.0"> 
    <application android:icon="@drawable/icon" android:label="@string/app_name"> 
        <activity android:name=".Main" 
                  android:label="@string/app_name"> 
            <intent-filter> 
                <action android:name="android.intent.action.MAIN" /> 
                <category android:name="android.intent.category.LAUNCHER" /> 
            </intent-filter> 
        </activity> 
         
        <!-- 
            配置一个自定义的 ContentProvider" 
        --> 
        <provider android:name="MyContentProvider" android:authorities="com.webabcd.MyContentProvider" /> 
    </application> 
    <uses-permission android:name="android.permission.WRITE_CONTACTS"></uses-permission> 
    <uses-permission android:name="android.permission.READ_CONTACTS"></uses-permission> 
    <uses-sdk android:minSdkVersion="3" /> 
</manifest>