android 的SQLite数据库应用的Demo

转载

mb5fdcadbf25fde 2012-07-05 12:38:00

文章标签 android sqlite java 文章分类 代码人生

1.Sqlite 数据库是一个开源的嵌入式关系数据库。具有很好的可移植性，容易使用，体积小，高效且可靠的功能.支持null 、integer、real、 text、blob数据类型

而且可以解析SQL语句。

如查询语句：select * from 表名； where 条件子句；groupby 分组字句；having。。。order by排序子句

分页语句：selec *fromAccount limit 5offset 3 或者 select * from Account limit 3,5

插入语句：intsert into表名（字段列表）；values（值列表）

更新语句：update 表名；set字段名=值；where条件子句

删除语句：delete from 表名；where条件子句

2.了解过这些后我们就可以看下面的实现过程了

3.首先看看布局

item.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content" >

    <TextView
        android:id="@+id/personid"
        android:layout_width="60px"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/name"
        android:layout_width="160px"
        android:layout_height="wrap_content"
        android:layout_alignTop="@id/personid"
        android:layout_toRightOf="@id/personid"
        android:gravity="center_horizontal" />

    <TextView
        android:id="@+id/age"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignTop="@id/name"
        android:layout_toRightOf="@id/name" />

</RelativeLayout>

main.xml布局如下

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

    <RelativeLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/idtitle"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="编号" />

        <TextView
            android:id="@+id/nametitle"
            android:layout_width="200px"
            android:layout_height="wrap_content"
            android:layout_alignTop="@id/idtitle"
            android:layout_toRightOf="@id/idtitle"
            android:gravity="center_horizontal"
            android:text="姓名" />

        <TextView
            android:id="@+id/agetitle"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignTop="@id/nametitle"
            android:layout_toRightOf="@id/nametitle"
            android:text="年龄" />
    </RelativeLayout>

    <ListView
        android:id="@+id/personlist"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>

4.接着看一下怎样创建数据库的DatabaseHelper.java

package com.wang;

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

public class DatabaseHelper extends SQLiteOpenHelper {

  private static final String NAME = "sharp.db";
  private static final int version = 1;

  public DatabaseHelper(Context context) {
    super(context, NAME, null, version);

  }

  // 第一次创建数据库是时候被调用
  public void onCreate(SQLiteDatabase db) {

    db
        .execSQL("CREATE TABLE person (personid integer primary key autoincrement,name varchar(20),age integer)");

  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS person");
    onCreate(db);
  }

}

5.接着看看怎样实现添加，删除，更新，分页，获取记录总数的实现方法PersonService.java

package com.wang;

import java.util.ArrayList;
import java.util.List;

import android.R.color;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class PersonService {

  private DatabaseHelper databaseHelper;
  private Context context;

  public PersonService(Context context) {
    this.context = context;
    databaseHelper = new DatabaseHelper(context);

  }

  // 添加
  public void save(Person person) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    db.execSQL("insert into person(name,age) values(?,?)", new Object[] {
        person.getName(), person.getAge() });

  }

  // 更新操作
  public void update(Person person) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    db.execSQL("update person set name=?,age=?where personid=?",
        new Object[] { person.getName(), person.getAge(),
            person.getId() });

  }

  // 查询操作
  public Person find(Integer id) {
    SQLiteDatabase db = databaseHelper.getReadableDatabase();

    Cursor cursor = db.rawQuery(
        "select personid,name,age from person where personid=?",
        new String[] { String.valueOf(id) });

    // 迭代记录集
    if (cursor.moveToNext()) {
      Person person = new Person();
      // 将查询的字段放在person标准
      person.setId(cursor.getInt(cursor.getColumnIndex("personid")));
      person.setName(cursor.getString(1));
      person.setAge(cursor.getInt(2));
      return person;

    }
    cursor.close();
    return null;

  }

  // 删除操作
  public void delete(Integer id) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    db.execSQL("delete from person where personid=?", new Object[] { id });

  }

  // //
  // 数据分页操作
  public List<Person> getScrollData(int firstResult, int maxResult) {

    List<Person> persons = new ArrayList<Person>();
    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    Cursor cursor = db.rawQuery(
        "select personid,name,age from person limit ?,?",
        new String[] { String.valueOf(firstResult),
            String.valueOf(maxResult) });

    while (cursor.moveToNext()) {

      Person person = new Person();
      person.setId(cursor.getInt(cursor.getColumnIndex("personid")));
      person.setName(cursor.getString(1));
      person.setAge(cursor.getInt(2));
      persons.add(person);

    }
    cursor.close();

    return persons;

  }

  // 获取记录的总数

  public long getCount() {
    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    // 没有占位符的时候置为空null
    Cursor cursor = db.rawQuery("select count(*)from person", null);
    cursor.moveToFirst();
    long count = cursor.getLong(0);
    cursor.close();

    return count;

  }

}

6.添加，删除，更新，分页，获取记录总数的实现方法所要调用的类如下

Person.java

package com.wang;

public class Person {

  private Integer id;
  private String name;
  private Integer age;

  public Person() {
  }

  public Person(String name, Integer age) {
    this.name = name;
    this.age = age;

  }

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public Integer getAge() {
    return age;
  }

  public void setAge(Integer age) {
    this.age = age;
  }

  @Override
  public String toString() {

    return "Person[age=" + age + ",id=" + id + ",name=" + name + "]";
  }

}

7..添加，删除，更新，分页，获取记录总数的实现方法所要调用的类如下DBTest.java

package com.wang;

import com.wang.DatabaseHelper;

import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase {
  public void testCreaDB() throws Throwable {

    DatabaseHelper databaseHelper = new DatabaseHelper(this.getContext());
    databaseHelper.getWritableDatabase();

  }

}

8.测试一下数据库的.添加，删除，更新，分页，获取记录总数的实现方法PersonServiceTest.java

package com.wang;

import java.util.List;

import android.Manifest.permission;
import android.test.AndroidTestCase;
import android.util.Log;

public class PersonServiceTest extends AndroidTestCase {

  private static final String TAG = "PersonServiceTest";

  // 测试添加方法
  public void testSave() throws Throwable {
    PersonService personService = new PersonService(this.getContext());
    System.out.println("1111");
    for (int i = 0; i < 10; i++) {
      Person person = new Person("xiaowang" + i, 22);

      personService.save(person);
    }
    System.out.println("1111");
  }

  // 测试查找方法

  public void testFind() throws Throwable {

    PersonService personService = new PersonService(this.getContext());
    Person person = personService.find(1);
    Log.i(TAG, person.toString());
  }

  // 测试更新方法

  public void testupdate() throws Throwable {
    PersonService personService = new PersonService(this.getContext());
    Person person = personService.find(1);
    person.setName("xiaowang");
    personService.update(person);

  }

  // 测试记录总数方法

  public void testcount() throws Throwable {

    PersonService personService = new PersonService(this.getContext());
    Log.i(TAG, personService.getCount() + "");
  }

  // 测试分页方法

  public void testgetScrolldata() throws Throwable {
    PersonService personService = new PersonService(this.getContext());

    List<Person> persons = personService.getScrollData(0, 3);
    for (Person person : persons) {
      Log.i(TAG, person.toString());

    }
  }

  // 测试删除方法

  public void testdelete() throws Throwable {
    PersonService personService = new PersonService(this.getContext());
    personService.delete(1);
  }

}

9.还有另外一种方法如下，只需改动PersonService.java和PersonServiceTest.java就可以实现同样的功能OtherPersonService.java

package com.wang;

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;

public class OtherPersonService {
  private DatabaseHelper databaseHelper;
  private Context context;
  public OtherPersonService(Context context) {
    this.context = context;
    databaseHelper = new DatabaseHelper(context);

  }

  // 添加
  public void save(Person person) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();

    ContentValues values=new ContentValues();
    values.put("name", person.getName());
    values.put("age", person.getAge());
    db.insert("person", "name", values);
  }
  // 更新操作
  public void update(Person person) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();

    ContentValues values=new ContentValues();
    values.put("name", person.getName());
    values.put("age", person.getAge());
    db.update("person", values,"personid",new String[] {String.valueOf(person.getId())});
  }

  // 查询操作
  public Person find(Integer id) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();

    Cursor cursor = db.query("person", new String[]{"personid","name","age"}, "personid", new String []{String.valueOf(id)}, null, null, null);


    // 迭代记录集
    if (cursor.moveToNext()) {
      Person person = new Person();
      // 将查询的字段放在person标准
      person.setId(cursor.getInt(0));
      person.setName(cursor.getString(1));
      person.setAge(cursor.getInt(2));
      return person;

    }

    return null;

  }
  // 删除操作
  public void delete(Integer id) {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    db.delete("person", "personid=?", new String[]{String.valueOf(id)});

  }
  // 数据分页操作
  public List<Person> getScrollData(int firstResult, int maxResult) {

    List<Person> persons = new ArrayList<Person>();
    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    Cursor cursor = db.query("person", new String[]{"personid","name","age"}, null, null, null, null, "personid desc",firstResult+","+maxResult);


    while (cursor.moveToNext()) {

      Person person = new Person();
      person.setId(cursor.getInt(0));
      person.setName(cursor.getString(1));
      person.setAge(cursor.getInt(2));
      persons.add(person);

    }


    return persons;

  }
  // 获取记录的总数

  public long getCount() {
    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    // 没有占位符的时候置为空null
    Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null);
    if (cursor.moveToNext()) {
      return cursor.getLong(0);
    }


    return 0;

  }



}

10.另外一种方法如下，只需改动PersonService.java和PersonServiceTest.java就可以实现同样的功能OtherPersonServiceTest.java

package com.wang;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

public class OtherPersonServiceTest extends AndroidTestCase {

  private static final String TAG = "OtherPersonServiceTest";

  // 测试添加方法
  public void testSave() throws Throwable {
    OtherPersonService personService = new OtherPersonService(this.getContext());
    System.out.println("1111");
    for (int i = 0; i < 10; i++) {
      Person person = new Person("xiaowang" + i, 22);

      personService.save(person);
    }
    System.out.println("1111");
  }


  // 测试查找方法

  public void testFind() throws Throwable {

    OtherPersonService otherPersonService = new OtherPersonService(this.getContext());
    Person person = otherPersonService.find(1);
    Log.i(TAG, person.toString());
  }

  // 测试更新方法

  public void testupdate() throws Throwable {
    OtherPersonService otherPersonService = new OtherPersonService(this.getContext());
    Person person = otherPersonService.find(1);
    person.setName("xiaowang");
    otherPersonService.update(person);

  }
  // 测试记录总数方法

  public void testcount() throws Throwable {

    OtherPersonService otherPersonService = new OtherPersonService(this.getContext());
    Log.i(TAG, otherPersonService.getCount() + "");
  }
  // 测试分页方法

  public void testgetScrolldata() throws Throwable {
    OtherPersonService otherPersonService = new OtherPersonService(this.getContext());

    List<Person> persons = otherPersonService.getScrollData(0, 3);
    for (Person person : persons) {
      Log.i(TAG, person.toString());

    }
  }

  // 测试删除方法

  public void testdelete() throws Throwable {
    OtherPersonService otherPersonService = new OtherPersonService(this.getContext());
    otherPersonService.delete(1);
  }


}

11.亲！最重要的也是最容易忘的权限问题

<uses-library android:name="android.test.runner" />

<instrumentation

android:name="android.test.InstrumentationTestRunner"

android:label="Tests for My App"

android:targetPackage="com.wang" >

</instrumentation>

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.wang"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="10" />

    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <uses-library android:name="android.test.runner" />

        <activity
            android:name=".SqlitedatabasedemoActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:label="Tests for My App"
        android:targetPackage="com.wang" >
    </instrumentation>

</manifest>