手把手使用Android自带SQLite数据库（2）—— 建立和访问数据库

前情提要：手把手使用Android自带SQLite数据库（1）—— 建立核心文件

接前情提要，本文介绍如何在App中对数据库进行各项操作。

一、数据库的创建

上一篇文章中TimeTableProvider类onCreate()函数是这样的

public boolean onCreate() {
    mOpenHelper = new TimeTableDbHelper(getContext());
    return true;
}

因此当TimeTableProvider第一次创建时，数据库TimeTable及表CourseInfo和ExecutingSchedule也已经被创建。而TimeTableProvider在Manifest.xml中配置过了，即当App开始运行时TimeTableProvider自动加载，不需要其他操作。

总而言之，数据库和表在App第一次启动时自动创建，不需要额外操作。

二、增、删、改操作

我们把增、删、改的操作全部封装在Utils.java这个类中：

public class Utils {
    private static final String LOG_TAG = "Utils";
    
    /********************DATABASE********************************/
    static public void insert(Context c,CourseInfo course)
    {
        TimeTableDbHelper myDbHelper = TimeTableDbHelper.getInstance(c);
        SQLiteDatabase db = myDbHelper.getWritableDatabase();

        ContentValues content = new ContentValues();
        content.put(TimeTableContract.CourseInfo.COLUMN_COURSE_NAME,course.courseName);
        content.put(TimeTableContract.CourseInfo.COLUMN_TEACHER_NAME,course.teacherName);
        content.put(TimeTableContract.CourseInfo.COLUMN_STUDENT_NAME,course.studentName);
        content.put(TimeTableContract.CourseInfo.COLUMN_START_DATE,course.startDate);
        content.put(TimeTableContract.CourseInfo.COLUMN_PRICE,course.price);
        content.put(TimeTableContract.CourseInfo.COLUMN_NOTE,course.note);
        content.put(TimeTableContract.CourseInfo.COLUMN_NUMBER_OF_CLASSES,course.numberOfClasses);
        content.put(TimeTableContract.CourseInfo.COLUMN_COURSE_SCHEDULE,course.schedule);
        content.put(TimeTableContract.CourseInfo.COLUMN_IS_TODAY_START,course.isTodayStart);
        long course_id = db.insert(TimeTableContract.CourseInfo.TABLE_NAME,null,content);//单条增加

        /*ExecutingSchedule*/
        int numberOfClasses = course.courseCardArrayList.size();
        ContentValues[] ExecutingScheduleContents= new ContentValues[numberOfClasses];

        for (int i = 0;i < numberOfClasses;i++)
        {
            ExecutingScheduleContents[i] = new ContentValues();
            ExecutingScheduleContents[i].put(TimeTableContract.ExecutingSchedule.COLUMN_COURSE_ID,course_id);
            ExecutingScheduleContents[i].put(TimeTableContract.ExecutingSchedule.COLUMN_SEQUENCE_ID,i);
            ExecutingScheduleContents[i].put(TimeTableContract.ExecutingSchedule.COLUMN_DATE,course.courseCardArrayList.get(i).date);
            ExecutingScheduleContents[i].put(TimeTableContract.ExecutingSchedule.COLUMN_WEEKDAY,course.courseCardArrayList.get(i).weekday);
            ExecutingScheduleContents[i].put(TimeTableContract.ExecutingSchedule.COLUMN_START_TIME,course.courseCardArrayList.get(i).start_hour_minutes);
            ExecutingScheduleContents[i].put(TimeTableContract.ExecutingSchedule.COLUMN_END_TIME,course.courseCardArrayList.get(i).end_hour_minutes);
        }

        long returnValue1 = c.getContentResolver().bulkInsert(TimeTableContract.ExecutingSchedule.CONTENT_URI ,ExecutingScheduleContents);//批量增加

    }

    static public void updateClassStatus(Context c,int id,String status)
    {
        ContentValues value = new ContentValues();
        value.put(TimeTableContract.ExecutingSchedule.COLUMN_LEARN_STATUS,status);
        Uri uri = TimeTableContract.ExecutingSchedule.buildExecutingScheduleUriWithId(id);
        long returnValue = c.getContentResolver().update(uri,value,null,null);//修改
        Log.e(LOG_TAG,"updateClassStatus: " + String.valueOf(returnValue));

    }
    
    static public void deleteScheduleById(Context c,int id)
    {
        Uri uri = TimeTableContract.ExecutingSchedule.buildExecutingScheduleUriWithId(id);
        long returnValue = c.getContentResolver().delete(uri,null,null);
        Log.e(LOG_TAG,"deleteScheduleById: " + String.valueOf(returnValue));//删除

    }
    
    
    static public void updateSequenceIdAndLeaveState(Context c,int id,int sequenceId,String leaveState)
    {
        ContentValues value = new ContentValues();
        value.put(TimeTableContract.ExecutingSchedule.COLUMN_SEQUENCE_ID,sequenceId);
        value.put(TimeTableContract.ExecutingSchedule.COLUMN_LEAVE_STATUS,leaveState);
        Uri uri = TimeTableContract.ExecutingSchedule.buildExecutingScheduleUriWithId(id);
        long returnValue = c.getContentResolver().update(uri,value,null,null);//修改多个column
        Log.e(LOG_TAG,"updateSequenceId: " + String.valueOf(returnValue));

    }
    
    
}

其中 <单条增加> 这个操作没有使用TimeTableProvider提供的接口，而是直接操作数据库。可以参考批量增加的接口bulkinsert把它修改为TimeTableProvider的insert接口。

在App中调用这些接口即可。

三、查询

我们在Fragment中使用LoadManager异步加载ContentProvider中管理的数据，实现数据查询功能。代码如下：

public class CourseListFragment extends Fragment implements LoaderManager.LoaderCallbacks<Cursor> {
    String CURSOR_LOG_TAG = "CURSOR";
    static final int COURSE_LIST_LOADER = 0;
    // TODO: Customize parameter argument names
    private static final String ARG_COLUMN_COUNT = "column-count";
    // TODO: Customize parameters
    private int mColumnCount = 2;
    private ArrayList<CourseInformation> course_info_list = new ArrayList<CourseInformation>();
    private OnListFragmentInteractionListener mListener;
    RecyclerView recyclerView ;
    CourseListFragment mThis;

    /**
     * Mandatory empty constructor for the fragment manager to instantiate the
     * fragment (e.g. upon screen orientation changes).
     */
    public CourseListFragment() {
    }

    // TODO: Customize parameter initialization
    @SuppressWarnings("unused")
    public static CourseListFragment newInstance(int columnCount) {
        CourseListFragment fragment = new CourseListFragment();
        Bundle args = new Bundle();
        args.putInt(ARG_COLUMN_COUNT, columnCount);
        fragment.setArguments(args);
        return fragment;
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        if (getArguments() != null) {
            mColumnCount = getArguments().getInt(ARG_COLUMN_COUNT);
        }
    }

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        View view = inflater.inflate(R.layout.fragment_course_list, container, false);
        ……
        getLoaderManager().restartLoader(COURSE_LIST_LOADER, null, this);
        ……
        mThis = this;
        return view;
    }


    @Override
    public void onAttach(Context context) {
        super.onAttach(context);

    }

    @Override
    public void onDetach() {
        super.onDetach();
        mListener = null;
    }

    /**
     * This interface must be implemented by activities that contain this
     * fragment to allow an interaction in this fragment to be communicated
     * to the activity and potentially other fragments contained in that
     * activity.
     * <p/>
     * See the Android Training lesson <a href=
     * "http://developer.android.com/training/basics/fragments/communicating.html"
     * >Communicating with Other Fragments</a> for more information.
     */
    public interface OnListFragmentInteractionListener {
        // TODO: Update argument type and name
        void onListFragmentInteraction(CourseInformation item);
    }

    /*cursor loader*/
    @NonNull
    @Override
    public Loader<Cursor> onCreateLoader(int id, @Nullable Bundle args) {
        Uri uri = null;
        String sortOrder = TimeTableContract.CourseInfo._ID;
        Log.e(CURSOR_LOG_TAG, " onCreateLoader = " + TimeTableContract.CourseInfo.CONTENT_URI);
        return new CursorLoader(getActivity(),
                TimeTableContract.CourseInfo.CONTENT_URI,
                null,
                null,
                null,
                sortOrder);
    }

    @Override
    public void onLoadFinished(@NonNull Loader<Cursor> cursorLoader, Cursor cursor) {
        if ((cursor == null)||(cursor.getCount() == 0)) {
            Log.e(CURSOR_LOG_TAG, " return cursorLoader.getId() = " + cursorLoader.getId());
            return;
        }

        int cursorCount = cursor.getCount();
        Log.e(CURSOR_LOG_TAG,"cursor.getCount() = " + cursor.getCount());
        cursor.moveToFirst();
        /*complete learning cards info*/
        course_info_list.clear();
        for (int i = 0;i < cursorCount;i++)
        {
            CourseInformation info = new CourseInformation();
            info.id = cursor.getInt(cursor.getColumnIndex(TimeTableContract.CourseInfo._ID));
            info.courseName = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_COURSE_NAME));
            info.teacherName = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_TEACHER_NAME));
            info.studentName = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_STUDENT_NAME));
            info.price = cursor.getInt(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_PRICE));
            info.numberOfClasses = cursor.getInt(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_NUMBER_OF_CLASSES));
            info.schedule = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_COURSE_SCHEDULE));
            info.note = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_NOTE));
            info.startDate = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_START_DATE));
            info.isTodayStart = cursor.getString(cursor.getColumnIndex(TimeTableContract.CourseInfo.COLUMN_IS_TODAY_START));
            course_info_list.add(info);

            Log.e(CURSOR_LOG_TAG,"COURSE =  " + info.id + info.courseName + ":" + info.teacherName + ":" + info.schedule);

            cursor.moveToNext();
        }

        

    }

    @Override
    public void onLoaderReset(@NonNull Loader<Cursor> loader) {

    }
    @Override
    public void onResume() {
        super.onResume();
        getLoaderManager().restartLoader(COURSE_LIST_LOADER, null, this);
        Log.e(CURSOR_LOG_TAG,"ON RESUME");
    }




}

实现关注点：

1、fragment实现接口 LoaderManager.LoaderCallbacks<Cursor> ，实现三个必需函数

函数	说明
onCreateLoader()	指定需要查询的数据Urir地址并开始查询。
onLoadFinished()	返回查询的数据，处理数据。
onLoaderReset()	释放Cursor中数据时的相关处理。

2、定义Loader，这里我们只涉及一种查询，因此定义一个LoaderId：COURSE_LIST_LOADER。LoaderManager可以处理多个Loader，通过LoaderId加以区分。

3、启动Loader ，在OnCreateView 和OnResume中 重新启动Loader来获取最新的数据。

 

至此，如何在App中完成SQlite数据库的操作的介绍就结束了。

下一篇文章我们将介绍，如何在App中引入预置的SQlite数据库。

---------------------------------------------------------------------

That's All.Thankyou!