android SQLite数据库小结

android SQLite数据库总结

???????????????????????????????????? + "]";

???????? }

}



?????? 3.编写MyOpenHelper类

?????? MyOpenHelper继承自SQLiteOpenHelper类。我们需要创建数据表，必须重写onCreate（更新时重写onUpgrade方法）方法，在这个方法中创建数据表。

package com.changcheng.sqlite;



import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;



public class MyOpenHelper extends SQLiteOpenHelper {



???????? private static final String name = "contants"; // 数据库名称

???????? private static final int version = 1; // 数据库版本



???????? public MyOpenHelper(Context context) {

?????????????????? /**

??????????????????? * CursorFactory指定在执行查询时获得一个游标实例的工厂类。 设置为null，则使用系统默认的工厂类。

??????????????????? */

?????????????????? super(context, name, null, version);

???????? }



???????? @Override

???????? public void onCreate(SQLiteDatabase db) {

?????????????????? // 创建contacts表，SQL表达式时提供的字段类型和长度仅为提高代码的可读性。

?????????????????? db.execSQL("CREATE TABLE IF NOT EXISTS contacts("

???????????????????????????????????? + "_id integer primary key autoincrement,"

???????????????????????????????????? + "name varchar(20)," + "phone varchar(50))");

???????? }



???????? @Override

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

?????????????????? // 仅演示用，所以先删除表然后再创建。

?????????????????? db.execSQL("DROP TABLE IF EXISTS contacts");

?????????????????? this.onCreate(db);

???????? }

}



4.编写ContactsService类

?????? ContactsService类主要实现对业务逻辑和数据库的操作。

package com.changcheng.sqlite.service;



import java.util.ArrayList;

import java.util.List;

import android.content.Context;

import android.database.Cursor;

import com.changcheng.sqlite.MyOpenHelper;

import com.changcheng.sqlite.entity.Contact;



public class ContactsService {



???????? private MyOpenHelper openHelper;



???????? public ContactsService(Context context) {

?????????????????? this.openHelper = new MyOpenHelper(context);

???????? }



???????? /**

????????? * 保存

????????? *

????????? * @param contact

????????? */

???????? public void save(Contact contact) {

?????????????????? String sql = "INSERT INTO contacts (name, phone) VALUES (?, ?)";

?????????????????? Object[] bindArgs = { contact.getName(), contact.getPhone() };

?????????????????? this.openHelper.getWritableDatabase().execSQL(sql, bindArgs);

???????? }



???????? /**

????????? * 查找

????????? *

????????? * @param id

????????? * @return

????????? */

???????? public Contact find(Integer id) {

?????????????????? String sql = "SELECT _id,name, phone FROM contacts WHERE _id=?";

?????????????????? String[] selectionArgs = { id + "" };

?????????????????? Cursor cursor = this.openHelper.getReadableDatabase().rawQuery(sql,

???????????????????????????????????? selectionArgs);

?????????????????? if (cursor.moveToFirst())

??????????????????????????? return new Contact(cursor.getInt(0), cursor.getString(1), cursor

?????????????????????????????????????????????? .getString(2));

?????????????????? return null;

???????? }



???????? /**

????????? * 更新

????????? *

????????? * @param contact

????????? */

???????? public void update(Contact contact) {

?????????????????? String sql = "UPDATE contacts SET name=?, phone=? WHERE _id=?";

?????????????????? Object[] bindArgs = { contact.getName(), contact.getPhone(),

???????????????????????????????????? contact.get_id() };

?????????????????? this.openHelper.getWritableDatabase().execSQL(sql, bindArgs);

???????? }



???????? /**

????????? * 删除

????????? *

????????? * @param id

????????? */

???????? public void delete(Integer id) {

?????????????????? String sql = "DELETE FROM contacts WHERE _id=?";

?????????????????? Object[] bindArgs = { id };

?????????????????? this.openHelper.getReadableDatabase().execSQL(sql, bindArgs);

???????? }



???????? /**

????????? * 获取记录数量

????????? *

????????? * @return

????????? */

???????? public long getCount() {

?????????????????? String sql = "SELECT count(*) FROM contacts";

?????????????????? Cursor cursor = this.openHelper.getReadableDatabase().rawQuery(sql,

???????????????????????????????????? null);

?????????????????? cursor.moveToFirst();

?????????????????? return cursor.getLong(0);

???????? }



???????? /**

????????? * 获取分页数据

????????? *

????????? * @param startIndex

????????? * @param maxCount

????????? * @return

????????? */

???????? public List<Contact> getScrollData(long startIndex, long maxCount) {

?????????????????? String sql = "SELECT _id,name,phone FROM contacts LIMIT ?,?";

?????????????????? String[] selectionArgs = { String.valueOf(startIndex),

???????????????????????????????????? String.valueOf(maxCount) };

?????????????????? Cursor cursor = this.openHelper.getReadableDatabase().rawQuery(sql,

???????????????????????????????????? selectionArgs);

?????????????????? List<Contact> contacts = new ArrayList<Contact>();

?????????????????? while (cursor.moveToNext()) {

??????????????????????????? Contact contact = new Contact(cursor.getInt(0),

?????????????????????????????????????????????? cursor.getString(1), cursor.getString(2));

??????????????????????????? contacts.add(contact);

?????????????????? }

?????????????????? return contacts;

???????? }



???????? /**

????????? * 获取分页数据，提供给SimpleCursorAdapter使用。

????????? *

????????? * @param startIndex

????????? * @param maxCount

????????? * @return

????????? */

???????? public Cursor getScrollDataCursor(long startIndex, long maxCount) {

?????????????????? String sql = "SELECT _id,name,phone FROM contacts LIMIT ?,?";

?????????????????? String[] selectionArgs = { String.valueOf(startIndex),

???????????????????????????????????? String.valueOf(maxCount) };

?????????????????? Cursor cursor = this.openHelper.getReadableDatabase().rawQuery(sql,

???????????????????????????????????? selectionArgs);

?????????????????? return cursor;

???????? }

}



?????? 5.编写测试类

?????? 编写一个针对ContactsService的测试类，测试ContactsService类中的各个方法是否正确。

package com.changcheng.sqlite.test;



import java.util.List;

import com.changcheng.sqlite.MyOpenHelper;

import com.changcheng.sqlite.entity.Contact;

import com.changcheng.sqlite.service.ContactsService;

import android.database.Cursor;

import android.test.AndroidTestCase;

import android.util.Log;



public class ContactsServiceTest extends AndroidTestCase {



???????? private static final String TAG = "ContactsServiceTest";



???????? // 测试创建表

???????? public void testCreateTable() throws Throwable {

?????????????????? MyOpenHelper openHelper = new MyOpenHelper(this.getContext());

?????????????????? openHelper.getWritableDatabase();

???????? }



???????? // 测试save

???????? public void testSave() throws Throwable {

?????????????????? ContactsService contactsService = new ContactsService(this.getContext());

?????????????????? Contact contact1 = new Contact(null, "tom", "13898679876");

?????????????????? Contact contact2 = new Contact(null, "lili", "13041094909");

?????????????????? Contact contact3 = new Contact(null, "jack", "13504258899");

?????????????????? Contact contact4 = new Contact(null, "heary", "1335789789");

?????????????????? contactsService.save(contact1);

?????????????????? contactsService.save(contact2);

?????????????????? contactsService.save(contact3);

?????????????????? contactsService.save(contact4);

???????? }



???????? // 测试find

???????? public void testFind() throws Throwable {

?????????????????? ContactsService contactsService = new ContactsService(this.getContext());

?????????????????? Contact contact = contactsService.find(1);

?????????????????? Log.i(TAG, contact.toString());

???????? }



???????? // 测试update

???????? public void testUpdate() throws Throwable {

?????????????????? ContactsService contactsService = new ContactsService(this.getContext());

?????????????????? Contact contact = contactsService.find(1);

?????????????????? contact.setPhone("1399889955");

?????????????????? contactsService.update(contact);

???????? }



???????? // 测试getCount

???????? public void testGetCount() throws Throwable {

?????????????????? ContactsService contactsService = new ContactsService(this.getContext());

?????????????????? Log.i(TAG, contactsService.getCount() + "");

???????? }



???????? // 测试getScrollData

???????? public void testGetScrollData() throws Throwable {

?????????????????? ContactsService contactsService = new ContactsService(this.getContext());

?????????????????? List<Contact> contacts = contactsService.getScrollData(0, 3);

?????????????????? Log.i(TAG, contacts.toString());

???????? }

???????

???????? // 测试getScrollDataCursor

???????? public void testGetScrollDataCursor() throws Throwable {

?????????????????? ContactsService contactsService = new ContactsService(this.getContext());

?????????????????? Cursor cursor = contactsService.getScrollDataCursor(0, 3);

?????????????????? while (cursor.moveToNext()) {

??????????????????????????? Contact contact = new Contact(cursor.getInt(0),

?????????????????????????????????????????????? cursor.getString(1), cursor.getString(2));

??????????????????????????? Log.i(TAG, contact.toString());

?????????????????? }

???????? }



}

?????? 启用测试功能，不要忘记在AndroidManifest.xml文件中加入测试环境。为application元素添加一个子元素：<uses-library android:name="android.test.runner"/>，为application元素添加一个兄弟元素：<instrumentation android:name="android.test.InstrumentationTestRunner"???? android:targetPackage="com.changcheng.sqlite" android:label="Tests for My App" />。



?????? SQLite数据库以单个文件存储，就像微软的Access数据库。有一个查看SQLite数据库文件的工具——SQLite Developer，我们可以使用它来查看数据库。Android将创建的数据库存放在”/data/data/ com.changcheng.sqlite/databases/contacts”，我们将它导出然后使用SQLite Developer打开。



?????? 6.分页显示数据

?????? 我们在ContactsService类中，提供了一个获取分页数据的方法。我们将调用它获取的数据，使用ListView组件显示出来。



?????? 编辑mail.xml：

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

???????? android:orientation="vertical" android:layout_width="fill_parent"

???????? android:layout_height="fill_parent">

???????? <!-- ListView -->

???????? <ListView android:layout_width="fill_parent"

?????????????????? android:layout_height="fill_parent" android:id="@+id/listView" />



</LinearLayout>



在mail.xml所在目录里添加一个contactitem.xml：

<?xml version="1.0" encoding="utf-8"?>

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

???????? android:layout_width="wrap_content" android:layout_height="wrap_content">



???????? <!-- contact.id -->

???????? <TextView android:layout_width="30dip" android:layout_height="wrap_content"

?????????????????? android:textSize="20sp" android:id="@+id/tv_id" />



???????? <!-- contact.name -->

???????? <TextView android:layout_width="150dip" android:layout_height="wrap_content"

?????????????????? android:textSize="20sp" android:layout_toRightOf="@id/tv_id"

?????????????????? android:layout_alignTop="@id/tv_id" android:id="@+id/tv_name" />



???????? <!-- contact.phone -->

???????? <TextView android:layout_width="150dip" android:layout_height="wrap_content"

?????????????????? android:textSize="20sp" android:layout_toRightOf="@id/tv_name"

?????????????????? android:layout_alignTop="@id/tv_name" android:id="@+id/tv_phone" />



</RelativeLayout>





?????? 编辑AndroidSQLite类：

package com.changcheng.sqlite;



import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import com.changcheng.sqlite.R;

import com.changcheng.sqlite.entity.Contact;

import com.changcheng.sqlite.service.ContactsService;

import android.app.Activity;

import android.database.Cursor;

import android.os.Bundle;

import android.view.View;

import android.widget.AdapterView;

import android.widget.ListView;

import android.widget.SimpleAdapter;

import android.widget.Toast;

import android.widget.AdapterView.OnItemClickListener;



public class AndroidSQLite extends Activity {

???????? /** Called when the activity is first created. */

???????? @Override

???????? public void onCreate(Bundle savedInstanceState) {

?????????????????? super.onCreate(savedInstanceState);

?????????????????? setContentView(R.layout.main);

?????????????????? // 获取分页数据

?????????????????? ContactsService contactsService = new ContactsService(this);

?????????????????? List<Contact> contacts = contactsService.getScrollData(0, 3);

?????????????????? // 获取ListView

?????????????????? ListView lv = (ListView) this.findViewById(R.id.listView);

?????????????????? // 生成List<? extends Map<String, ?>>数据

?????????????????? List<HashMap<String, Object>> data = new ArrayList<HashMap<String, Object>>();

?????????????????? for (Contact contact : contacts) {

??????????????????????????? HashMap<String, Object> item = new HashMap<String, Object>();

??????????????????????????? item.put("_id", contact.get_id());

??????????????????????????? item.put("name", contact.getName());

??????????????????????????? item.put("phone", contact.getPhone());

??????????????????????????? data.add(item);

?????????????????? }

?????????????????? // 生成Adapter

?????????????????? SimpleAdapter adapter = new SimpleAdapter(this, data,

???????????????????????????????????? R.layout.contactitem, new String[] { "_id", "name", "phone" },

???????????????????????????????????? new int[] { R.id.tv_id, R.id.tv_name, R.id.tv_phone });

?????????????????? // 设置ListView适配器

?????????????????? lv.setAdapter(adapter);

?????????????????

?????????????????? // 为ListView添加事件

?????????????????? lv.setOnItemClickListener(new OnItemClickListener() {



??????????????????????????? @Override

??????????????????????????? public void onItemClick(AdapterView<?> parent, View view,

?????????????????????????????????????????????? int position, long id) {

???????????????????????????????????? HashMap<String, Object> item = (HashMap<String, Object>) parent

??????????????????????????????????????????????????????? .getItemAtPosition((int) id);

???????????????????????????????????? Toast.makeText(AndroidSQLite.this, item.get("name").toString(),

??????????????????????????????????????????????????????? 1).show();

??????????????????????????? }



?????????????????? });

???????? }

}



上面编写的分页显示数据比较麻烦，Android为我们提供了一个SimpleCursorAdapter类。使用它可以方便的显示分页数据。将AndroidSQLite类修改为：

?????

package com.changcheng.sqlite;



import com.changcheng.sqlite.R;

import com.changcheng.sqlite.service.ContactsService;

import android.app.Activity;

import android.database.Cursor;

import android.os.Bundle;

import android.widget.ListView;

import android.widget.SimpleCursorAdapter;



public class AndroidSQLite extends Activity {

???????? /** Called when the activity is first created. */

???????? @Override

???????? public void onCreate(Bundle savedInstanceState) {

?????????????????? super.onCreate(savedInstanceState);

?????????????????? setContentView(R.layout.main);



?????????????????? // 获取分页数据

?????????????????? ContactsService contactsService = new ContactsService(this);

?????????????????? Cursor cursor = contactsService.getScrollDataCursor(0, 3);

?????????????????? // 获取ListView

?????????????????? ListView lv = (ListView) this.findViewById(R.id.listView);

?????????????????? // 创建Adapter

?????????????????? SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,

???????????????????????????????????? R.layout.contactitem, cursor, new String[] { "_id", "name",

??????????????????????????????????????????????????????? "phone" }, new int[] { R.id.tv_id, R.id.tv_name,

??????????????????????????????????????????????????????? R.id.tv_phone });

?????????????????? // 设置ListView适配器

?????????????????? lv.setAdapter(adapter);



?????????????????? // 为ListView添加事件

?????????????????? lv.setOnItemClickListener(new OnItemClickListener() {



??????????????????????????? @Override

??????????????????????????? public void onItemClick(AdapterView<?> parent, View view,

?????????????????????????????????????????????? int position, long id) {

???????????????????????????????????? Cursor cursor = (Cursor) parent

??????????????????????????????????????????????????????? .getItemAtPosition((int) position);

???????????????????????????????????? Toast.makeText(AndroidSQLite.this, cursor.getString(1), 1)

??????????????????????????????????????????????????????? .show();

??????????????????????????? }

?????????????????? });

???????? }

}



?????? OK，在Android中的SQLite操作总结结束！