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; }
} } |
