1 目标从sqllite中读取数据并显示如下：
mainactivity对应的界面
mainactivity2对应的界面
2 配置android的清单文件
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.itheima28.sqlitedemo"
android:versioncode="1"
android:versionname="1.0" >
<instrumentation
android:name="android.test.instrumentationtestrunner"
android:targetpackage="com.itheima28.sqlitedemo">
</instrumentation>
<uses-sdk
android:minsdkversion="8"
android:targetsdkversion="19" />
<application
android:allowbackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/apptheme" >
<uses-library android:name="android.test.runner"/>
<!--
运行的时候改变 com.itheima28.sqlitedemo.mainactivity
或com.itheima28.sqlitedemo.mainactivity2来显示不同的界面
-->
<activity
android:name="com.itheima28.sqlitedemo.mainactivity"
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>
</manifest>
3 编写操作数据库用的工具personsqliteopenhelper
package com.itheima28.sqlitedemo.db;
import android.content.context;
import android.database.sqlite.sqlitedatabase;
import android.database.sqlite.sqliteopenhelper;
import android.util.log;
/**
* 数据库帮助类，用于创建和管理数据库
* @author toto
*/
public class personsqliteopenhelper extends sqliteopenhelper {
private static final string tag = "personsqliteopenhelper";
/**
* 数据库的构造函数
* @param context
*
* name 数据库名称
* factory 游标工程
* version 数据库的版本号 不可以小于1
*/
public personsqliteopenhelper(context context) {
//open declaration android.database.sqlite.sqliteopenhelper.sqliteopenhelper(
// context context,
// string name,
// cursorfactory factory,
// int version)
super(context, "itheima28.db", null, 1);
}
* 数据库第一次创建时回调此方法
* 初始化
@override
public void oncreate(sqlitedatabase db) {
//操作数据库
string sql = "create table person(_id integer primary key, name varchar(20), age integer);";
db.execsql(sql);
* 数据库的版本号更新时回调此方法，
* 更新数据库的内容(删除表，添加表，修改表)
public void onupgrade(sqlitedatabase db, int oldversion, int newversion) {
if (oldversion == 1 && newversion == 2) {
log.i(tag, "数据库更新啦");
//在person表中添加一个余额列balance
db.execsql("alter table person add balance integer;");
}
}
4 编写person实体
package com.itheima28.sqlitedemo.entities;
public class person {
private int id;
private string name;
private int age;
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;
public int getage() {
return age;
public void setage(int age) {
this.age = age;
public person() {
super();
public person(int id, string name, int age) {
@override
public string tostring() {
return "person [id=" + id + ", name=" + name + ", age=" + age + "]";
5第一种直接通过sql的方式操作数据库：
package com.itheima28.sqlitedemo.dao;
import java.util.arraylist;
import java.util.list;
import android.database.cursor;
import com.itheima28.sqlitedemo.db.personsqliteopenhelper;
import com.itheima28.sqlitedemo.entities.person;
public class persondao {
//数据库的帮助类对象
private personsqliteopenhelper mopenhelper;
public persondao(context context) {
mopenhelper = new personsqliteopenhelper(context);
* 添加到person表一条数据库
* @param person
public void insert(person person) {
sqlitedatabase db = mopenhelper.getwritabledatabase();
//如果数据库打开，执行添加的操作
if (db.isopen()) {
//执行添加到数据库的操作
db.execsql("insert into person(name,age) values(?,?);",new object[]{
person.getname(),
person.getage()
});
//数据库关闭
db.close();
* 根据id删除记录
* @param id
public void delete(int id) {
db.execsql("delete from person where _id = ?;",new integer[]{id});
* 根据id找到记录，并且修改姓名
* @param name
public void update(int id,string name) {
db.execsql("update person set name = ? where _id = ?;",new object[]{name,id});
* 查询所有
* @return
public list<person> queryall() {
sqlitedatabase db = mopenhelper.getreadabledatabase();
cursor cursor = db.rawquery("select _id, name, age from person;", null);
if (cursor != null && cursor.getcount() > 0) {
list<person> personlist = new arraylist<person>();
int id;
string name;
int age;
while (cursor.movetonext()) {
id = cursor.getint(0); //取到第0列的数据id
name = cursor.getstring(1); //取姓名
age = cursor.getint(2);
personlist.add(new person(id,name,age));
}
cursor.close();
db.close();
return personlist;
}
return null;
* 根据id查询人
public person queryitem(int id) {
sqlitedatabase db = mopenhelper.getreadabledatabase(); // 获得一个只读的数据库对象
if(db.isopen()) {
cursor cursor = db.rawquery("select _id, name, age from person where _id = ?;", new string[]{id + ""});
if(cursor != null && cursor.movetofirst()) {
int _id = cursor.getint(0);
string name = cursor.getstring(1);
int age = cursor.getint(2);
return new person(_id, name, age);
6 第二种操作数据库的方式
import android.content.contentvalues;
* 不使用sql的方式进行操作
public class persondao2 {
private static final string tag = "persondao2";
public persondao2(context context) {
* 添加到person表一条数据
contentvalues values = new contentvalues();
//key作为要存储的列名，value对象列的值
values.put("name", person.getname());
values.put("age", person.getage());
long id = db.insert("person", "name", values);
log.i(tag, "id:" + id);
//获得可写的数据库对象
string whereclause = "_id = ?";
string[] whereargs = {id + ""};
int count = db.delete("person", whereclause, whereargs);
log.i(tag,"删除了：" + count + "行");
values.put("name", name);
int count = db.update("person", values, "_id = ?", new string[]{id + ""});
log.i(tag, "修改了：" + count + "行");
* 查询所有的结果集
// 获得一个只读的数据库对象
sqlitedatabase db = mopenhelper.getreadabledatabase();
// 需要的列
string[] columns = {"_id", "name", "age"};
// 选择条件, 给null查询所有
string selection = null;
// 选择条件的参数, 会把选择条件中的? 替换成数据中的值
string[] selectionargs = null;
// 分组语句 group by name
string groupby = null;
// 过滤语句
string having = null;
// 排序
string orderby = null;
cursor cursor = db.query("person", columns, selection, selectionargs, groupby, having, orderby);
int id;
string name;
int age;
if(cursor != null && cursor.getcount() > 0) {
while(cursor.movetonext()) {
// 向下移一位, 知道最后一位, 不可以往下移动了,停止.
id = cursor.getint(0);
name = cursor.getstring(1);
personlist.add(new person(id, name, age));
string[] columns = {"_id", "name", "age"};
string selection = "_id = ?";
string[] selectionargs = {id + ""};
// 分组语句 group by name
string groupby = null;
// 过滤语句
string having = null;
// 排序
// cursor对且象不为null, 并可以移动到第一行
7 testcase才是persondao，注意项目中是通过单元测试来添加数据的
package com.itheima28.sqlitedemo.test;
import android.test.androidtestcase;
import com.itheima28.sqlitedemo.dao.persondao;
import com.itheima28.sqlitedemo.entities.person;
public class testcase extends androidtestcase{
private static final string tag = "testcase";
public void test() {
//数据库什么时候创建
personsqliteopenhelper openhelper = new personsqliteopenhelper(getcontext());
//第一次连接数据库时创建数据库文件 .oncreate会被调用
openhelper.getreadabledatabase();
* 向数据库中插入一条数据
public void testinsert() {
persondao dao = new persondao(getcontext());
dao.insert(new person(0,"田七",28));
* 删除数据
public void testdelete() {
dao.delete(1);
* 更新数据
public void testupdate() {
dao.update(3, "李四");
* 查询所有的列表信息
public void testqueryall() {
list<person> personlist = dao.queryall();
for (person person : personlist) {
log.i(tag, person.tostring());
* 查询条项
public void testqueryitem() {
person person = dao.queryitem(4);
log.i(tag, person.tostring());
* 事务操作
public void testtransaction() {
sqlitedatabase db = openhelper.getwritabledatabase();
try {
//开启事务
db.begintransaction();
//1.从张三账户中扣1000块钱
db.execsql("update person set balance = balance - 1000 where name = 'zhangsan';");
//atm机，挂掉了
//int result = 10 / 0;
//2.向李四账户中加1000块钱
db.execsql("update person set balance = balance + 1000 where name = 'lisi';");
//标记事务成功
db.settransactionsuccessful();
} finally {
//停止事务
db.endtransaction();
db.close();
数据库截图如下：
8 testcase2测试第二种方式操作数据库
import com.itheima28.sqlitedemo.dao.persondao2;
public class testcase2 extends androidtestcase {
//第一次连接数据库时创建数据库文件 oncreate会被调用
* 添加
persondao2 dao = new persondao2(getcontext());
dao.insert(new person(0, "zhouqi", 88));
dao.delete(8);
dao.update(3, "fengjie");
9 mainactivity的代码如下：
package com.itheima28.sqlitedemo;
import android.app.activity;
import android.os.bundle;
import android.view.view;
import android.view.viewgroup;
import android.widget.baseadapter;
import android.widget.listview;
import android.widget.textview;
import com.itheima28.sqlitedemo.dao.persondao;
public class mainactivity extends activity {
private list<person> personlist;
protected void oncreate(bundle savedinstancestate) {
super.oncreate(savedinstancestate);
setcontentview(r.layout.activity_main);
listview mlistview = (listview) findviewbyid(r.id.listview);
persondao dao = new persondao(this);
personlist = dao.queryall();
// 把view层对象listview和控制器baseadapter关联起来
mlistview.setadapter(new myadapter());
/**
* @author andong
* 数据适配器
*/
class myadapter extends baseadapter {
private static final string tag = "myadapter";
/**
* 定义listview的数据的长度
*/
@override
public int getcount() {
return personlist.size();
}
public object getitem(int position) {
// todo auto-generated method stub
return null;
public long getitemid(int position) {
return 0;
* 此方法返回的是listview的列表中某一行的view对象
* position 当前返回的view的索引位置
* convertview 缓存对象
* parent 就是listview对象
*/
public view getview(int position, view convertview, viewgroup parent) {
textview tv = null;
if(convertview != null) { // 判断缓存对象是否为null, 不为null时已经缓存了对象
log.i(tag, "getview: 复用缓存" + position);
tv = (textview) convertview;
} else { // 等于null, 说明第一次显示, 新创建
log.i(tag, "getview: 新建" + position);
tv = new textview(mainactivity.this);
}
tv.settextsize(25);
person person = personlist.get(position); // 获得指定位置的数据, 进行对textview的绑定
tv.settext(person.tostring());
return tv;
10 mainactivity2的代码如下：
import android.view.layoutinflater;
public class mainactivity2 extends activity {
view view = null;
if(convertview == null) {
// 布局填充器对象, 用于把xml布局转换成view对象
layoutinflater inflater = mainactivity2.this.getlayoutinflater();
view = inflater.inflate(r.layout.listview_item, null);
} else {
view = convertview;
// 给view中的姓名和年龄赋值
textview tvname = (textview) view.findviewbyid(r.id.tv_listview_item_name);
textview tvage = (textview) view.findviewbyid(r.id.tv_listview_item_age);
person person = personlist.get(position);
tvname.settext("姓名: " + person.getname());
tvage.settext("年龄: " + person.getage());
return view;