Android数据库基础学习

Android中数据库的学习中，我目前学了两种方式

1. 用基础类SQLiteOpenHelper操作数据库，其增删改查，则通过sql语句。

2. 用ContentProvider 和ContentResovler进行数据库的操作
             ContentProvider其实是把SQLiteOpenHelper中操作数据库的方式进行封装。
             ContentProvider和ContentResovler 的实现原理，只是个人观念，帮助理解。

一般数据库的操作步骤：

       1.编写SQLiteOpenHelper的子类，实现数据库的建立，以及数据库的升级操作。（当然了如果不用ContentProvider，也可以编写增删改查的操作）。

       2.编写ContentProvider的子类，封装步骤1的数据库操作，统一访问的入口为Uri对象。

       3.注册编写ContentProvider的子类。

       4.通过ContentResolver对象实现增删改查的操作。

 

 

 事例代码：

DataBaseConstant.java(主要存放数据库的字段以及访问数据的Uri) 

package database;
import android.net.Uri;
import android.provider.BaseColumns;

public class DataBaseConstant {

    public static final class Student implements BaseColumns {
        public static final String AUTHORITY = "database.provider.student";
        public static final String TABLE_NAME = "student";

        public static final Uri CONTENT_URI = Uri
                .parse("content://" + AUTHORITY + "/" + TABLE_NAME);

        public static final String NAME = "name";
        public static final String CLASS = "class";
    }
}

 

DataBaseString.java(存放SQLite的语句)

package database;

public class DataBaseString {
    public static final String CREATE = "CREATE TABLE IF NOT EXISTS ";
    // 设置主键
    public static final String _ID_PRIMARY = " _id INTEGER PRIMARY KEY AUTOINCREMENT,";

    // 创建student表
    public static final String CREATE_STUDENT = CREATE + DataBaseConstant.Student.TABLE_NAME + "("
            + _ID_PRIMARY
            + DataBaseConstant.Student.NAME + " TEXT,"
            + DataBaseConstant.Student.CLASS + " TEXT);";

    // 添加
    public static final String ADD_STUDENT = "INSERT INTO " + DataBaseConstant.Student.TABLE_NAME
            + "(" + DataBaseConstant.Student.NAME + "," + DataBaseConstant.Student.CLASS
            + ") VALUES";

}

 

DataBaseHelper.java(继承SQLiteOpenHelper,直接操作数据库)

package database;

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

public class DataBaseHelper extends SQLiteOpenHelper {

    public DataBaseHelper(Context context) {
        // 创建数据库
        super(context, "data.db", null, 3);
    }

    /**
     * 该方法只有在数据库不存在时才调用
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 执行SQL语句，创建表
        db.execSQL(DataBaseString.CREATE_STUDENT);
    }

    /**
     * 因为SQLiteOpenHelper可是SQLite的辅助类，可以操作数据库 所以我们可以获得数据库，执行SQL语句
     * 
     * @param initialValues
     */
    public void add(ContentValues initialValues) {
        SQLiteDatabase db = getWritableDatabase();
        String s = DataBaseString.ADD_STUDENT;
        ContentValues values = (initialValues != null) ? new ContentValues(
                initialValues) : new ContentValues();
        s = s + "('" + values.getAsString(DataBaseConstant.Student.NAME) + "', '"
                + values.getAsString(DataBaseConstant.Student.CLASS) + "');";
        db.execSQL(s);
    }

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

    }
}

DataBaseProvider.java(封装数据库的操作，也就是封装上一个类的实例，将数据暴露出来，也就是数据的增删该查通过Uri进行访问)

在DataBaseProvider中的getType方法到底有没有用呢？答案在下面的链接中

http://blog.csdn.net/wanghui115521/article/details/42555141

package database;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;

/**
 * 将数据库的包装类的增删改查的方法，进行封装
 * @author Administrator
 *
 */
public class DataBaseProvider extends ContentProvider {
    //UriMatcher 功能，存储Uri ，并进行匹配。
    private static final UriMatcher URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);

    static {
        //集合路径匹配，匹配返回码是1
        URI_MATCHER.addURI(DataBaseConstant.Student.AUTHORITY, "student", 1);
        //个体路径匹配，匹配返回码是2
        URI_MATCHER.addURI(DataBaseConstant.Student.AUTHORITY, "student/#", 2);
    }
    
    private DataBaseHelper mDataBaseHelper;

    @Override
    public boolean onCreate() {
        mDataBaseHelper = new DataBaseHelper(getContext());
        return true;
    }

    @Override
    public String getType(Uri url) {
        int match = URI_MATCHER.match(url);
        switch (match) {
            case 1:
                return "vnd.android.cursor.dir/" + "student";
            case 2:
                return "vnd.android.cursor.item/" + "student";  
            default:
                throw new IllegalArgumentException("Unknown URL");
        }
    }
    
    @Override
    public Uri insert(Uri url, ContentValues initialValues) {
        if (URI_MATCHER.match(url) != 1) {
            throw new IllegalArgumentException("Cannot insert into URL: " + url);
        }

        ContentValues values = (initialValues != null) ? new ContentValues(
                initialValues) : new ContentValues();

        SQLiteDatabase db = mDataBaseHelper.getWritableDatabase();

        long rowId = db.insert(DataBaseConstant.Student.TABLE_NAME,
                DataBaseConstant.Student.NAME, values);

        if (rowId < 0) {
            throw new SQLException("Failed to insert row into " + url);
        }

        //Uri的工具类
        Uri noteUri = ContentUris
                .withAppendedId(DataBaseConstant.Student.CONTENT_URI, rowId);
        getContext().getContentResolver().notifyChange(noteUri, null);
        return noteUri;
    }

    @Override
    public int delete(Uri url, String where, String[] whereArgs) {
        SQLiteDatabase db = mDataBaseHelper.getWritableDatabase();
        int count;
        switch (URI_MATCHER.match(url)) {

            case 1:
                count = db.delete(DataBaseConstant.Student.TABLE_NAME, where, whereArgs);
                break;
            case 2:
                String segment = url.getPathSegments().get(1);

                if (TextUtils.isEmpty(where)) {
                    where = "_id=" + segment;
                } else {
                    where = "_id=" + segment + " AND (" + where + ")";
                }
                count = db.delete(DataBaseConstant.Student.TABLE_NAME, where, whereArgs);
                break;
            default:
                throw new IllegalArgumentException("Cannot delete from URL: " + url);
        }

        getContext().getContentResolver().notifyChange(url, null);
        return count;
    }

    @Override
    public int update(Uri url, ContentValues values, String where,
            String[] whereArgs) {
        int count;
        long rowId = 0;
        int match = URI_MATCHER.match(url);
        SQLiteDatabase db = mDataBaseHelper.getWritableDatabase();

        switch (match) {
            case 1:
                count = db.update(DataBaseConstant.Student.TABLE_NAME, values, where,
                        whereArgs);
                break;
            case 2:
                String segment = url.getPathSegments().get(1);
                rowId = Long.parseLong(segment);
                count = db.update(DataBaseConstant.Student.TABLE_NAME, values, "_id="
                        + rowId, null);
                break;
            default:
                throw new UnsupportedOperationException("Cannot update URL: " + url);
        }

        getContext().getContentResolver().notifyChange(url, null);
        return count;

    }

    @Override
    public Cursor query(Uri url, String[] projectionIn, String selection,
            String[] selectionArgs, String sortOrder) {

        SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder();
        int match = URI_MATCHER.match(url);

        switch (match) {
            case 1:
                qBuilder.setTables(DataBaseConstant.Student.TABLE_NAME);
                break;
            case 2:
                qBuilder.setTables(DataBaseConstant.Student.TABLE_NAME);
                qBuilder.appendWhere("_id="
                + url.getPathSegments().get(1)); 
                break;
            default:
                throw new IllegalArgumentException("Unknown URL " + url);
        }

//        String orderBy;
//        if (TextUtils.isEmpty(sortOrder)) {
//            orderBy = DbConstant.LoginInfoConstant.DEFAULT_SORT_ORDER;
//        } else {
//            orderBy = sortOrder;
//        }

        SQLiteDatabase db = mDataBaseHelper.getReadableDatabase();
        Cursor ret = qBuilder.query(db, projectionIn, selection, selectionArgs,
                null, null, null);

        if (ret == null) {
        } else {
            ret.setNotificationUri(getContext().getContentResolver(), url);
        }

        return ret;
    }
}

 MainActivity.java(注意：该类中注释的代码，其实是直接操作数据库的代码，并不通过ContentResolver进行访问）

package database;

import android.app.Activity;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.database.ContentObserver;
import android.database.Cursor;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.Handler;
import android.text.TextUtils;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import com.example.testandroidui.R;

public class MainActivity extends Activity implements OnClickListener {

    private EditText name, cla;
    public TextView data;
    private Button delete, insert, query;
    public ContentResolver cr;

    //DataBaseHelper mDataBaseHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.database_main);
        //mDataBaseHelper = new DataBaseHelper(this);// 获得数据库辅助对象

        cr = getContentResolver();
        init();

    }

    public void init() {
        name = (EditText) findViewById(R.id.name);
        cla = (EditText) findViewById(R.id.cla);
        data = (TextView) findViewById(R.id.data);
        delete = (Button) findViewById(R.id.delete);
        insert = (Button) findViewById(R.id.insert);
        query = (Button) findViewById(R.id.query);

        delete.setOnClickListener(this);
        insert.setOnClickListener(this);
        query.setOnClickListener(this);

        // cr注册监听 ,监听数据库变化
        cr.registerContentObserver(DataBaseConstant.Student.CONTENT_URI,
                true, new DataObserver(new Handler()));
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.delete:
                if (!TextUtils.isEmpty(name.getText().toString())
                        && !TextUtils.isEmpty(cla.getText().toString())) {
                    ContentValues values = new ContentValues();
                    values.put(DataBaseConstant.Student.NAME,
                            name.getText().toString());
                    values.put(DataBaseConstant.Student.CLASS,
                            cla.getText().toString());

                    cr.delete(DataBaseConstant.Student.CONTENT_URI,
                            DataBaseConstant.Student.NAME + " = ? AND "
                                    + DataBaseConstant.Student.CLASS
                                    + " = ? ",
                            new String[] {
                                    name.getText().toString(), cla.getText().toString()
                            });
                }

                break;
            case R.id.insert:
                if (!TextUtils.isEmpty(name.getText().toString())
                        && !TextUtils.isEmpty(cla.getText().toString())) {

                    ContentValues values = new ContentValues();
                    values.put(DataBaseConstant.Student.NAME,
                            name.getText().toString());
                    values.put(DataBaseConstant.Student.CLASS,
                            cla.getText().toString());

                    cr.insert(DataBaseConstant.Student.CONTENT_URI, values);
                    // mDataBaseHelper.add(values);//直接通过数据库辅助对象添加数据
                }
                break;
            case R.id.query:
                new QueryTextTask().execute();
                break;
            default:
                break;
        }
    }

    /**
     * @author Administrator 数据库变化监听
     */
    public class DataObserver extends ContentObserver {

        public DataObserver(Handler handler) {
            super(handler);
        }

        public void onChange(boolean selfChange) {
            // 执行的操作
            new QueryTextTask().execute();
        }

    }

    class QueryTextTask extends AsyncTask<Void, Integer, String> {

        QueryTextTask() {
        }

        /**
         * 运行在UI线程中，在调用doInBackground()之前执行
         */
        @Override
        protected void onPreExecute() {
        }

        /**
         * 后台运行的方法，可以运行非UI线程，可以执行耗时的方法
         */
        @Override
        protected String doInBackground(Void... params) {
            Cursor c = cr.query(DataBaseConstant.Student.CONTENT_URI,
                    new String[] {
                            DataBaseConstant.Student.NAME, DataBaseConstant.Student.CLASS
                    }, null, null, null);
            String num = "";
            while (c.moveToNext()) {
                String nameString = c.getString(c.getColumnIndex(DataBaseConstant.Student.NAME));
                String classString = c.getString(c.getColumnIndex(DataBaseConstant.Student.CLASS));
                num = num + "name:" + nameString + " , class:" + classString + ";";
            }
            return num;
        }

        /**
         * 运行在ui线程中，在doInBackground()执行完毕后执行
         */
        @Override
        protected void onPostExecute(String s) {
            data.setText(s);
        }
    }
}

database_main.xml主界面布局文件

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

    <EditText
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入姓名" />

    <EditText
        android:id="@+id/cla"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入班级" />

    <Button
        android:id="@+id/delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="delete" />

    <Button
        android:id="@+id/query"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="query" />

    <Button
        android:id="@+id/insert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="insert" />

    <TextView
        android:id="@+id/data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="0.81"
        android:hint="数据库" />

</LinearLayout>

ContentProvider在AndroidManifest注册，并将访问路径写入authorities属性中

 <provider
            android:name="database.DataBaseProvider"
            android:authorities="database.provider.student" >
        </provider>