07_Android操作sqllite数据库(包括2中方式操作数据的方式),单元测试,BaseAdapter的使用,自定义view的综合使用案例

时间:2021-05-12 12:25:48


1 目标从sqllite中读取数据并显示如下:

MainActivity对应的界面

07_Android操作sqllite数据库(包括2中方式操作数据的方式),单元测试,BaseAdapter的使用,自定义view的综合使用案例

MainActivity2对应的界面          

07_Android操作sqllite数据库(包括2中方式操作数据的方式),单元测试,BaseAdapter的使用,自定义view的综合使用案例

2  配置Android的清单文件

<?xml   编写操作数据库用的工具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();

}

}

}

数据库截图如下:

07_Android操作sqllite数据库(包括2中方式操作数据的方式),单元测试,BaseAdapter的使用,自定义view的综合使用案例

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;

}

}

}