1 目标从sqllite中读取数据并显示如下：
MainActivity对应的界面
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);
}
/**
* 数据库的版本号更新时回调此方法，
* 更新数据库的内容(删除表，添加表，修改表)
*/
@Override
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) {
super();
this.id = id;
this.name = name;
this.age = 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.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
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) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
//如果数据库打开，执行添加的操作
if (db.isOpen()) {
db.execSQL("delete from person where _id = ?;",new Integer[]{id});
//数据库关闭
db.close();
}
}
/**
* 根据id找到记录，并且修改姓名
* @param id
* @param name
*/
public void update(int id,String name) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
//如果数据库打开，执行添加的操作
if (db.isOpen()) {
db.execSQL("update person set name = ? where _id = ?;",new Object[]{name,id});
//数据库关闭
db.close();
}
}
/**
* 查询所有
* @return
*/
public List<Person> queryAll() {
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if (db.isOpen()) {
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;
}
db.close();
}
return null;
}
/**
* 根据id查询人
* @param id
* @return
*/
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);
cursor.close();
db.close();
return new Person(_id, name, age);
}
db.close();
}
return null;
}
}
6 第二种操作数据库的方式
package com.itheima28.sqlitedemo.dao;
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;
import android.util.Log;
import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;
import com.itheima28.sqlitedemo.entities.Person;
/**
* 不使用SQL的方式进行操作
* @author toto
*/
public class PersonDao2 {
private static final String TAG = "PersonDao2";
//数据库的帮助类对象
private PersonSQLiteOpenHelper mOpenHelper;
public PersonDao2(Context context) {
mOpenHelper = new PersonSQLiteOpenHelper(context);
}
/**
* 添加到person表一条数据
* @param person
*/
public void insert(Person person) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
//如果数据库打开，执行添加的操作
if (db.isOpen()) {
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);
//数据库关闭
db.close();
}
}
/**
* 根据id删除记录
*/
public void delete(int id) {
//获得可写的数据库对象
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
//如果数据库打开，执行添加的操作
if (db.isOpen()) {
String whereClause = "_id = ?";
String[] whereArgs = {id + ""};
int count = db.delete("person", whereClause, whereArgs);
Log.i(TAG,"删除了：" + count + "行");
//数据库关闭
db.close();
}
}
/**
* 根据id找到记录，并且修改姓名
* @param id
* @param name
*/
public void update(int id,String name) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
//如果数据库打开，执行添加的操作
if (db.isOpen()) {
ContentValues values = new ContentValues();
values.put("name", name);
int count = db.update("person", values, "_id = ?", new String[]{id + ""});
Log.i(TAG, "修改了：" + count + "行");
//数据库关闭
db.close();
}
}
/**
* 查询所有的结果集
* @return
*/
public List<Person> queryAll() {
// 获得一个只读的数据库对象
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if(db.isOpen()) {
// 需要的列
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) {
List<Person> personList = new ArrayList<Person>();
while(cursor.moveToNext()) {
// 向下移一位, 知道最后一位, 不可以往下移动了,停止.
id = cursor.getInt(0);
name = cursor.getString(1);
age = cursor.getInt(2);
personList.add(new Person(id, name, age));
}
cursor.close();
db.close();
return personList;
}
db.close();
}
return null;
}
/**
* 根据id查询人
* @param id
* @return
*/
public Person queryItem(int id) {
// 获得一个只读的数据库对象
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if(db.isOpen()) {
// 需要的列
String[] columns = {"_id", "name", "age"};
// 选择条件, 给null查询所有
String selection = "_id = ?";
// 选择条件的参数, 会把选择条件中的? 替换成数据中的值
String[] selectionArgs = {id + ""};
// 分组语句 group by name
String groupBy = null;
// 过滤语句
String having = null;
// 排序
String orderBy = null;
Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
// cursor对且象不为null, 并可以移动到第一行
if(cursor != null && cursor.moveToFirst()) {
int _id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
cursor.close();
db.close();
return new Person(_id, name, age);
}
db.close();
}
return null;
}
}
7 TestCase才是personDao，注意项目中是通过单元测试来添加数据的
package com.itheima28.sqlitedemo.test;
import java.util.List;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
import android.util.Log;
import com.itheima28.sqlitedemo.dao.PersonDao;
import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;
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() {
PersonDao dao = new PersonDao(getContext());
dao.delete(1);
}
/**
* 更新数据
*/
public void testUpdate() {
PersonDao dao = new PersonDao(getContext());
dao.update(3, "李四");
}
/**
* 查询所有的列表信息
*/
public void testQueryAll() {
PersonDao dao = new PersonDao(getContext());
List<Person> personList = dao.queryAll();
for (Person person : personList) {
Log.i(TAG, person.toString());
}
}
/**
* 查询条项
*/
public void testQueryItem() {
PersonDao dao = new PersonDao(getContext());
Person person = dao.queryItem(4);
Log.i(TAG, person.toString());
}
/**
* 事务操作
*/
public void testTransaction() {
PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
SQLiteDatabase db = openHelper.getWritableDatabase();
if (db.isOpen()) {
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测试第二种方式操作数据库
package com.itheima28.sqlitedemo.test;
import java.util.List;
import com.itheima28.sqlitedemo.dao.PersonDao2;
import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;
import com.itheima28.sqlitedemo.entities.Person;
import android.test.AndroidTestCase;
import android.util.Log;
public class TestCase2 extends AndroidTestCase {
private static final String TAG = "TestCase";
public void test() {
//数据库什么时候创建
PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
//第一次连接数据库时创建数据库文件 onCreate会被调用
openHelper.getReadableDatabase();
}
/**
* 添加
*/
public void testInsert() {
PersonDao2 dao = new PersonDao2(getContext());
dao.insert(new Person(0, "zhouqi", 88));
}
public void testDelete() {
PersonDao2 dao = new PersonDao2(getContext());
dao.delete(8);
}
public void testUpdate() {
PersonDao2 dao = new PersonDao2(getContext());
dao.update(3, "fengjie");
}
public void testQueryAll() {
PersonDao2 dao = new PersonDao2(getContext());
List<Person> personList = dao.queryAll();
for (Person person : personList) {
Log.i(TAG, person.toString());
}
}
public void testQueryItem() {
PersonDao2 dao = new PersonDao2(getContext());
Person person = dao.queryItem(4);
Log.i(TAG, person.toString());
}
}
9 MainActivity的代码如下：
package com.itheima28.sqlitedemo;
import java.util.List;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
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;
import com.itheima28.sqlitedemo.entities.Person;
public class MainActivity extends Activity {
private List<Person> personList;
@Override
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();
}
@Override
public Object getItem(int position) {
// TODO Auto-generated method stub
return null;
}
@Override
public long getItemId(int position) {
// TODO Auto-generated method stub
return 0;
}
/**
* 此方法返回的是ListView的列表中某一行的View对象
* position 当前返回的view的索引位置
* convertView 缓存对象
* parent 就是ListView对象
*/
@Override
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的代码如下：
package com.itheima28.sqlitedemo;
import java.util.List;
import android.app.Activity;
import android.os.Bundle;
import android.view.LayoutInflater;
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;
import com.itheima28.sqlitedemo.entities.Person;
public class MainActivity2 extends Activity {
private List<Person> personList;
@Override
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();
}
@Override
public Object getItem(int position) {
// TODO Auto-generated method stub
return null;
}
@Override
public long getItemId(int position) {
// TODO Auto-generated method stub
return 0;
}
/**
* 此方法返回的是ListView的列表中某一行的View对象
* position 当前返回的view的索引位置
* convertView 缓存对象
* parent 就是ListView对象
*/
@Override
public View getView(int position, View convertView, ViewGroup parent) {
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;
}
}
}
