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;