sqlite3基本操作
今天,我们将讨论有关Android中的自定义加载程序。 加载程序是从Android 3.0引入的,但借助Android兼容性库,我们也可以在从Android 1.6开始的早期版本的Android中使用加载程序。
在本教程中,我们将不讨论Internet上大量可用的loader基础。 可以在这里找到有关Android加载程序的很好的教程。 在这里,我们将讨论如何在Android中创建自定义加载程序,该加载程序可以从SQLite数据库读取数据并以POJO的集合形式返回读取的数据。
Android提供了一个“ CursorLoader”类,该类可以从内容提供者读取数据,但要直接从SQLite数据库读取数据,我们需要我们自己的自定义加载程序。 为此,编写了一个非常不错的加载器库,该库从游标读取数据,可以在这里找到它,而我从上述链接中采用了自定义加载器的想法。 但是我们的自定义加载程序能够在不使用任何内容提供程序的情况下从SQLite数据库读取数据,并将数据作为对象的集合返回。
创建一个新的Android项目,并记住使用库'android-support-v4.jar'并实现自定义加载器,如下所示:
首先,我们将使用通用方法创建通用数据源类文件“ DataSource.java”,以对数据执行CRUD操作。 代码如下:
package com.example.customloaderexample.db;
import java.util.List;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
public abstract class DataSource {
protected SQLiteDatabase mDatabase;
public DataSource(SQLiteDatabase database) {
mDatabase = database;
}
public abstract boolean insert(T entity);
public abstract boolean delete(T entity);
public abstract boolean update(T entity);
public abstract List read();
public abstract List read(String selection, String[] selectionArgs,
String groupBy, String having, String orderBy);
}
接下来,我们将使用以下代码创建模型类文件“ Test.java”。 此类表示我们要存储和从数据库检索的数据。
package com.example.customloaderexample.model;
public class Test {
private int id;
private String name;
public Test(){}
public Test(String name){
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return name;
}
}
接下来,我们将创建“ TestDataSource.java”的子类,以专门对“ Test”对象执行CRUD操作。
package com.example.customloaderexample.db;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.customloaderexample.model.Test;
public class TestDataSource extends DataSource {
public static final String TABLE_NAME = "test";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_NAME = "name";
// Database creation sql statement
public static final String CREATE_COMMAND = "create table " + TABLE_NAME
+ "(" + COLUMN_ID + " integer primary key autoincrement, "
+ COLUMN_NAME + " text not null);";
public TestDataSource(SQLiteDatabase database) {
super(database);
// TODO Auto-generated constructor stub
}
@Override
public boolean insert(Test entity) {
if (entity == null) {
return false;
}
long result = mDatabase.insert(TABLE_NAME, null,
generateContentValuesFromObject(entity));
return result != -1;
}
@Override
public boolean delete(Test entity) {
if (entity == null) {
return false;
}
int result = mDatabase.delete(TABLE_NAME,
COLUMN_ID + " = " + entity.getId(), null);
return result != 0;
}
@Override
public boolean update(Test entity) {
if (entity == null) {
return false;
}
int result = mDatabase.update(TABLE_NAME,
generateContentValuesFromObject(entity), COLUMN_ID + " = "
+ entity.getId(), null);
return result != 0;
}
@Override
public List read() {
Cursor cursor = mDatabase.query(TABLE_NAME, getAllColumns(), null,
null, null, null, null);
List tests = new ArrayList();
if (cursor != null && cursor.moveToFirst()) {
while (!cursor.isAfterLast()) {
tests.add(generateObjectFromCursor(cursor));
cursor.moveToNext();
}
cursor.close();
}
return tests;
}
@Override
public List read(String selection, String[] selectionArgs,
String groupBy, String having, String orderBy) {
Cursor cursor = mDatabase.query(TABLE_NAME, getAllColumns(), selection, selectionArgs, groupBy, having, orderBy);
List tests = new ArrayList();
if (cursor != null && cursor.moveToFirst()) {
while (!cursor.isAfterLast()) {
tests.add(generateObjectFromCursor(cursor));
cursor.moveToNext();
}
cursor.close();
}
return tests;
}
public String[] getAllColumns() {
return new String[] { COLUMN_ID, COLUMN_NAME };
}
public Test generateObjectFromCursor(Cursor cursor) {
if (cursor == null) {
return null;
}
Test test = new Test();
test.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
test.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
return test;
}
public ContentValues generateContentValuesFromObject(Test entity) {
if (entity == null) {
return null;
}
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, entity.getName());
return values;
}
}
现在,我们将创建SQLite数据库打开帮助器'DbHelper.java'。 此类扩展了SQLiteOpenHelper,它有助于我们创建初始数据库和表。
package com.example.customloaderexample.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DbHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "test.db";
private static final int DATABASE_VERSION = 1;
public DbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(TestDataSource.CREATE_COMMAND);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TestDataSource.TABLE_NAME);
onCreate(db);
}
}
接下来,我们将创建一个通用的AsyncTask子类'ContentChangingTask.java',以在后台执行内容更改。 此类是更改内容的基类。 我们将创建此基类的子类,以在后台对数据执行CRUD操作。
package com.example.customloaderexample.loader;
import android.os.AsyncTask;
import android.support.v4.content.Loader;
public abstract class ContentChangingTask<T1, T2, T3> extends
AsyncTask<T1, T2, T3> {
private Loader<?> loader=null;
ContentChangingTask(Loader<?> loader) {
this.loader=loader;
}
@Override
protected void onPostExecute(T3 param) {
loader.onContentChanged();
}
}
现在,我们将为自定义加载程序“ AbstractDataLoader.java”创建一个通用基类。 此类是我们自定义加载程序的基类。
package com.example.customloaderexample.loader;
import java.util.List;
import android.content.Context;
import android.support.v4.content.AsyncTaskLoader;
public abstract class AbstractDataLoader> extends
AsyncTaskLoader {
protected E mLastDataList = null;
protected abstract E buildList();
public AbstractDataLoader(Context context) {
super(context);
}
/**
* Runs on a worker thread, loading in our data. Delegates the real work to
* concrete subclass' buildCursor() method.
*/
@Override
public E loadInBackground() {
return buildList();
}
/**
* Runs on the UI thread, routing the results from the background thread to
* whatever is using the dataList.
*/
@Override
public void deliverResult(E dataList) {
if (isReset()) {
// An async query came in while the loader is stopped
emptyDataList(dataList);
return;
}
E oldDataList = mLastDataList;
mLastDataList = dataList;
if (isStarted()) {
super.deliverResult(dataList);
}
if (oldDataList != null && oldDataList != dataList
&& oldDataList.size() > 0) {
emptyDataList(oldDataList);
}
}
/**
* Starts an asynchronous load of the list data. When the result is ready
* the callbacks will be called on the UI thread. If a previous load has
* been completed and is still valid the result may be passed to the
* callbacks immediately.
*
* Must be called from the UI thread.
*/
@Override
protected void onStartLoading() {
if (mLastDataList != null) {
deliverResult(mLastDataList);
}
if (takeContentChanged() || mLastDataList == null
|| mLastDataList.size() == 0) {
forceLoad();
}
}
/**
* Must be called from the UI thread, triggered by a call to stopLoading().
*/
@Override
protected void onStopLoading() {
// Attempt to cancel the current load task if possible.
cancelLoad();
}
/**
* Must be called from the UI thread, triggered by a call to cancel(). Here,
* we make sure our Cursor is closed, if it still exists and is not already
* closed.
*/
@Override
public void onCanceled(E dataList) {
if (dataList != null && dataList.size() > 0) {
emptyDataList(dataList);
}
}
/**
* Must be called from the UI thread, triggered by a call to reset(). Here,
* we make sure our Cursor is closed, if it still exists and is not already
* closed.
*/
@Override
protected void onReset() {
super.onReset();
// Ensure the loader is stopped
onStopLoading();
if (mLastDataList != null && mLastDataList.size() > 0) {
emptyDataList(mLastDataList);
}
mLastDataList = null;
}
protected void emptyDataList(E dataList) {
if (dataList != null && dataList.size() > 0) {
for (int i = 0; i < dataList.size(); i++) {
dataList.remove(i);
}
}
}
}
现在,我们将为“测试”对象创建特定的加载器“ SQLiteTestDataLoader.java”,该对象是以前的通用加载器的子类。 此类是“测试”对象的自定义加载程序。 我们将把该类实例化为加载器。
package com.example.customloaderexample.loader;
import java.util.List;
import android.content.Context;
import com.example.customloaderexample.db.DataSource;
import com.example.customloaderexample.model.Test;
public class SQLiteTestDataLoader extends AbstractDataLoader<List> {
private DataSource mDataSource;
private String mSelection;
private String[] mSelectionArgs;
private String mGroupBy;
private String mHaving;
private String mOrderBy;
public SQLiteTestDataLoader(Context context, DataSource dataSource, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
super(context);
mDataSource = dataSource;
mSelection = selection;
mSelectionArgs = selectionArgs;
mGroupBy = groupBy;
mHaving = having;
mOrderBy = orderBy;
}
@Override
protected List buildList() {
List testList = mDataSource.read(mSelection, mSelectionArgs, mGroupBy, mHaving, mOrderBy);
return testList;
}
public void insert(Test entity) {
new InsertTask(this).execute(entity);
}
public void update(Test entity) {
new UpdateTask(this).execute(entity);
}
public void delete(Test entity) {
new DeleteTask(this).execute(entity);
}
private class InsertTask extends ContentChangingTask<Test, Void, Void> {
InsertTask(SQLiteTestDataLoader loader) {
super(loader);
}
@Override
protected Void doInBackground(Test... params) {
mDataSource.insert(params[0]);
return (null);
}
}
private class UpdateTask extends ContentChangingTask<Test, Void, Void> {
UpdateTask(SQLiteTestDataLoader loader) {
super(loader);
}
@Override
protected Void doInBackground(Test... params) {
mDataSource.update(params[0]);
return (null);
}
}
private class DeleteTask extends ContentChangingTask<Test, Void, Void> {
DeleteTask(SQLiteTestDataLoader loader) {
super(loader);
}
@Override
protected Void doInBackground(Test... params) {
mDataSource.delete(params[0]);
return (null);
}
}
}
接下来,我们将为启动器活动创建初始视图xml'activity_main.xml'到res / layout文件夹中。
<FrameLayoutxmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"android:layout_height="match_parent">
<fragmentclass="com.example.customloaderexample.CustomLoaderExampleListFragment"
android:id="@+id/titles"
android:layout_width="match_parent"android:layout_height="match_parent"/>
</FrameLayout>
现在,我们将创建启动器活动以启动我们的项目。 这是一个FragmentActivity,它保存我们的ListFragment并将初始记录插入数据库中。
package com.example.customloaderexample;
import java.util.List;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.app.FragmentActivity;
import com.example.customloaderexample.db.DbHelper;
import com.example.customloaderexample.db.TestDataSource;
import com.example.customloaderexample.model.Test;
public class MainFragmentActivity extends FragmentActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
DbHelper helper = new DbHelper(this);
SQLiteDatabase database = helper.getWritableDatabase();
TestDataSource dataSource = new TestDataSource(database);
List list = dataSource.read();
if(list == null || list.size() == 0){
dataSource.insert(new Test("Samik"));
dataSource.insert(new Test("Piyas"));
dataSource.insert(new Test("Sujal"));
}
helper.close();
database.close();
}
}
不要忘记将启动器活动与正确的意图过滤器(使其成为启动器活动)一起输入到“ AndroidManifest.xml”中。现在,我们将创建ListFragment以从数据库中检索Test对象的列表,并使用我们的自定义加载器进行显示。
package com.example.customloaderexample;
import java.util.List;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.support.v4.app.ListFragment;
import android.support.v4.app.LoaderManager;
import android.support.v4.content.Loader;
import android.util.Log;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import com.example.customloaderexample.db.DbHelper;
import com.example.customloaderexample.db.TestDataSource;
import com.example.customloaderexample.loader.SQLiteTestDataLoader;
import com.example.customloaderexample.model.Test;
public class CustomLoaderExampleListFragment extends ListFragment implements
LoaderManager.LoaderCallbacks<List>{
private ArrayAdapter mAdapter;
// The Loader's id (this id is specific to the ListFragment's LoaderManager)
private static final int LOADER_ID = 1;
private static final boolean DEBUG = true;
private static final String TAG = "CustomLoaderExampleListFragment";
private SQLiteDatabase mDatabase;
private TestDataSource mDataSource;
private DbHelper mDbHelper;
@Override
public void onActivityCreated(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onActivityCreated(savedInstanceState);
//setHasOptionsMenu(true);
mDbHelper = new DbHelper(getActivity());
mDatabase = mDbHelper.getWritableDatabase();
mDataSource = new TestDataSource(mDatabase);
mAdapter = new ArrayAdapter(getActivity(),
android.R.layout.simple_list_item_1);
setEmptyText("No data, please add from menu.");
setListAdapter(mAdapter);
setListShown(false);
if (DEBUG) {
Log.i(TAG, "+++ Calling initLoader()! +++");
if (getLoaderManager().getLoader(LOADER_ID) == null) {
Log.i(TAG, "+++ Initializing the new Loader... +++");
} else {
Log.i(TAG, "+++ Reconnecting with existing Loader (id '1')... +++");
}
}
// Initialize a Loader with id '1'. If the Loader with this id already
// exists, then the LoaderManager will reuse the existing Loader.
getLoaderManager().initLoader(LOADER_ID, null, this);
}
@Override
public Loader<List> onCreateLoader(int id, Bundle args) {
SQLiteTestDataLoader loader = new SQLiteTestDataLoader(getActivity(), mDataSource, null, null, null, null, null);
return loader;
}
@Override
public void onLoadFinished(Loader<List> loader, List data) {
if (DEBUG) Log.i(TAG, "+++ onLoadFinished() called! +++");
mAdapter.clear();
for(Test test : data){
mAdapter.add(test);
}
if (isResumed()) {
setListShown(true);
} else {
setListShownNoAnimation(true);
}
}
@Override
public void onLoaderReset(Loader<List> arg0) {
mAdapter.clear();
}
@Override
public void onDestroy() {
super.onDestroy();
mDbHelper.close();
mDatabase.close();
mDataSource = null;
mDbHelper = null;
mDatabase = null;
}
}
现在将项目作为Android应用程序运行,您将获得以下屏幕:
致谢:
- http://www.vogella.com/articles/AndroidSQLite/article.html
- https://github.com/commonsguy/cwac-loaderex
- http://www.androiddesignpatterns.com/2012/07/loaders-and-loadermanager-background.html
sqlite3基本操作