android 的SQLite数据库应用的Demo

     

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>

12.运行结果如下：有点。。。

posted @   程序员Android的博客  阅读(129)  评论(0)  编辑  收藏  举报
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 周边上新：园子的第一款马克杯温暖上架
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源！
点击右上角即可分享
微信分享提示
AI IDE Trae