因为使用到了单元测试,所以看一下AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.test_sqlite"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="17" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="com.example.test_sqlite.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>
<uses-library android:name="android.test.runner" />
</application>
<uses-permission android:name="android.permission.RUN_INSTRUMENTATION" />
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:label="Test for my app"
android:targetPackage="com.example.test_sqlite" />
</manifest>
activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="horizontal" >
<TextView
android:id="@+id/name"
android:layout_width="100dp"
android:layout_height="wrap_content"
android:text="用户名" />
<TextView
android:id="@+id/phone"
android:layout_width="120dp"
android:layout_height="wrap_content"
android:text="手机号" />
<TextView
android:id="@+id/amount"
android:layout_width="100dp"
android:layout_height="wrap_content"
android:text="余额" />
</LinearLayout>
<ListView
android:id="@+id/listView"
android:layout_width="fill_parent"
android:layout_height="wrap_content" />
</LinearLayout>
ListView的Item布局文件
item.xml<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="horizontal" >
<TextView
android:id="@+id/name"
android:layout_width="100dp"
android:layout_height="wrap_content"
android:textSize="20dp"/>
<TextView
android:id="@+id/phone"
android:layout_width="120dp"
android:layout_height="wrap_content"
android:textSize="20dp"/>
<TextView
android:id="@+id/amount"
android:layout_width="100dp"
android:layout_height="wrap_content"
android:textSize="20dp"/>
</LinearLayout>
Person.java
package com.example.test_sqlite.domain;
public class Person {
private Integer id;
private String name;
private String phone;
private int amount;
public Person() {
}
public Person(Integer id, String name, String phone, int amount) {
this.id = id;
this.name = name;
this.phone = phone;
this.amount = amount;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public int getAmount() {
return amount;
}
public void setAmount(int amount) {
this.amount = amount;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", phone=" + phone
+ ", amount=" + amount + "]";
}
}
PersonService.java
package com.example.test_sqlite.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import com.example.test_sqlite.domain.Person;
public class PersonService {
private SQLHelper sqlHelper;
public PersonService(Context context) {
this.sqlHelper = new SQLHelper(context);
}
/**
* 保存
* */
public void save(Person person) {
SQLiteDatabase db = sqlHelper.getWritableDatabase(); // 创建多个db的时候使用的依然是同一个对象
String sql = "insert into person(name, phone, amount) values(?, ?, ?)";
db.execSQL(sql, new Object[]{person.getName(), person.getPhone(), person.getAmount()});
}
/**
* 根据id删除对应记录
* */
public void delete(int id){
SQLiteDatabase db = sqlHelper.getWritableDatabase();
String sql = "delete from person where personid=?";
db.execSQL(sql, new Object[]{id});
}
/**
* 更新
* */
public void update(Person person){
SQLiteDatabase db = sqlHelper.getWritableDatabase();
String sql = "update person set name=?,phone=?,amount=? where personid=?";
db.execSQL(sql, new Object[]{person.getName(), person.getPhone(), person.getAmount(), person.getId()});
}
public Person findById(Integer id){
SQLiteDatabase db = sqlHelper.getReadableDatabase();
String sql = "select * from person where personid=?";
Cursor cursor = db.rawQuery(sql, new String[]{id.toString()});
Person person = new Person();
if(cursor.moveToFirst()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
person.setId(personid);
person.setName(name);
person.setPhone(phone);
person.setAmount(amount);
}
return person;
}
/**
* 分页查询
* @param offset 跳过前面的几条记录
* @param maxResult 要查询几条记录
* @return
*/
public List<Person> getScrollData(int offset, int maxResult){
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = sqlHelper.getReadableDatabase();
String sql = "select * from person order by personid asc limit ?,?";
Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(offset), String.valueOf(maxResult)});
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
persons.add(new Person(personid, name, phone, amount));
}
db.close();
return persons;
}
/**
* 返回Cursor的分页查询
* */
public Cursor getCursorScrollData(int offset, int maxResult){
SQLiteDatabase db = sqlHelper.getReadableDatabase();
String sql = "select personid as _id, name, phone, amount from person order by personid asc limit ?,?";
Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(offset), String.valueOf(maxResult)});
return cursor;
}
/**
* 获取总记录数
* */
public long getCount(){
SQLiteDatabase db = sqlHelper.getReadableDatabase();
String sql = "select count(*) from person";
Cursor cursor = db.rawQuery(sql, null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}
public void payment(){
SQLiteDatabase db = sqlHelper.getWritableDatabase();
db.beginTransaction();
try {
db.execSQL("update person set amount=amount-10 where personid=1");
db.execSQL("update person set amount=amount+10 where personid=3");
db.setTransactionSuccessful();
} catch (SQLException e) {
e.printStackTrace();
} finally {
db.endTransaction();
}
/*
* 结束事务有两种情况:commit,rollback
* 事物的提交或者回滚是由事务的标志决定的,如果事务的标志位True,事务就会提交,默认情况下,事务的标志位False
*
*
* */
}
public SQLHelper getSqlHelper() {
return sqlHelper;
}
public void setSqlHelper(SQLHelper sqlHelper) {
this.sqlHelper = sqlHelper;
}
}
SQLHelper.java
package com.example.test_sqlite.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class SQLHelper extends SQLiteOpenHelper{
public static final int VERSION = 2;
public static final String DBNAME = "lipeng.db"; // 默认保存在:/data/data/<包>/databases/
String sql = "CREATE TABLE person(personid integer primary key autoincrement,name varchar(20),phone varchar(12))";
String sql2 = "ALTER TABLE person ADD amount int NULL";
public SQLHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, DBNAME, null, VERSION);
}
public SQLHelper(Context context) {
super(context, DBNAME, null, VERSION);
}
/**
* 在数据库第一次被创建的时候使用
* */
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(sql);
}
/**
* 数据库文件版本号变更的时候调用
* */
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(sql2);
}
}
MainActivity.java
package com.example.test_sqlite;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.example.adapter.PersonAdapter;
import com.example.test_sqlite.domain.Person;
import com.example.test_sqlite.service.PersonService;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity {
private ListView listView = null;
PersonService service = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
service = new PersonService(this);
listView = (ListView) findViewById(R.id.listView);
listView.setOnItemClickListener(new ItemClickListener());
// show1();
show2();
// show3();
}
private final class ItemClickListener implements OnItemClickListener{
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
ListView lView = (ListView) parent;
/*类型一:这是相对于自定义适配器,只能用show3()
Person person = (Person)lView.getItemAtPosition(position);
Toast.makeText(getApplicationContext(), person.toString(), Toast.LENGTH_SHORT).show();*/
/* 类型二:这是相对于SimpleCursorAdapter,只能用show2()的程序*/
Cursor cursor = (Cursor) lView.getItemAtPosition(position);
int _id = cursor.getInt(cursor.getColumnIndex("_id"));
Toast.makeText(getApplicationContext(), String.valueOf(_id), Toast.LENGTH_SHORT).show();
}
}
/**
* 自定义适配器显示
*/
private void show3() {
List<Person> persons = service.getScrollData(0, 20);
PersonAdapter adapter = new PersonAdapter(this, persons, R.layout.item);
listView.setAdapter(adapter);
}
private void show2() {
Cursor cursor = service.getCursorScrollData(0, 20);
SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter(this, R.layout.item, cursor, new String[]{"name", "phone", "amount"}, new int[]{R.id.name, R.id.phone, R.id.amount});
listView.setAdapter(simpleCursorAdapter);
}
/**
* 显示listView
* */
private void show1() {
List<Person> persons = service.getScrollData(0, 5);
List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
for(Person person : persons){
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", person.getName()); // key:随便取名字
map.put("phone", person.getPhone());
map.put("amount", person.getAmount());
map.put("id", person.getId());
list.add(map);
}
SimpleAdapter adapter = new SimpleAdapter(this, list, R.layout.item, new String[]{"name", "phone", "amount"}, new int[]{R.id.name, R.id.phone, R.id.amount});
listView.setAdapter(adapter);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
PersonAdapter.java
package com.example.adapter;
import java.util.List;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
import com.example.test_sqlite.R;
import com.example.test_sqlite.domain.Person;
public class PersonAdapter extends BaseAdapter{
private List<Person> persons; // 在绑定的数据
private int resource; //绑定的条目界面
private LayoutInflater inflater = null; // 使用xml文件生成一个对应的view对象
public PersonAdapter(Context context, List<Person> persons, int resource) {
this.persons = persons;
this.resource = resource;
inflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); // 取得系统内置的布局填充服务
}
public final class ViewCache{
public TextView nameView;
public TextView phoneView;
public TextView amountView;
}
@Override
public int getCount() {
return persons.size();
}
@Override
public Object getItem(int position) {
return persons.get(position);
}
@Override
public long getItemId(int position) {
return position;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
/*
* 在listView中如果已经存在view,显示下一页的时候就不会再创建view了;如果不存在就创建
* */
TextView nameView = null;
TextView phoneView = null;
TextView amountView = null;
if(convertView == null){
convertView = inflater.inflate(resource, null);// 生成条目对象
nameView = (TextView) convertView.findViewById(R.id.name);
phoneView = (TextView) convertView.findViewById(R.id.phone);
amountView = (TextView) convertView.findViewById(R.id.amount);
ViewCache viewCache = new ViewCache();
viewCache.nameView = nameView;
viewCache.phoneView = phoneView;
viewCache.amountView = amountView;
convertView.setTag(viewCache);
} else {
ViewCache cache = (ViewCache) convertView.getTag();
nameView = cache.nameView;
phoneView = cache.phoneView;
amountView = cache.amountView;
}
// 实现数据绑定
Person person = persons.get(position);
nameView.setText(person.getName());
phoneView.setText(person.getPhone());
amountView.setText(String.valueOf(person.getAmount()));
return convertView;
}
}
PersonServiceTest.java
package com.example.test;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import com.example.test_sqlite.domain.Person;
import com.example.test_sqlite.service.PersonService;
import com.example.test_sqlite.service.SQLHelper;
public class PersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testCreateDB() {
SQLHelper helper = new SQLHelper(getContext());
helper.getWritableDatabase();
}
public void testSave() {
// 虽然每个方法里面都有该语句,但是不能放在上面,但可以放在重写的setup方法里面
PersonService service = new PersonService(getContext());
Person person = new Person(null, "lipeng", "15099144116", 100);
for (int i = 0; i < 20; i++) {
service.save(person);
}
}
public void testDelete() {
PersonService service = new PersonService(getContext());
service.delete(2);
}
public void testUpdate() {
PersonService service = new PersonService(getContext());
Person person = service.findById(2);
person.setName("lipengsdfdsf");
person.setPhone("132145646489");
person.setAmount(20);
service.update(person);
}
public void testFindById() {
PersonService service = new PersonService(getContext());
Person person = service.findById(2);
Log.i(TAG, person.toString());
}
public void testFindByPage() {
PersonService service = new PersonService(getContext());
List<Person> persons = service.getScrollData(0, 20);
for(Person person : persons){
Log.i(TAG,person.toString());
}
}
public void testGetCount() {
PersonService service = new PersonService(getContext());
long count = service.getCount();
Log.i(TAG, "count:" + count);
}
/**
* 模拟转账
* */
public void testUpdateAmount(){
PersonService service = new PersonService(getContext());
Person person1 = service.findById(1);
Person person2 = service.findById(3);
person1.setAmount(100);
person2.setAmount(50);
service.update(person1);
service.update(person2);
}
/**
* 测试转账
* */
public void testPayment(){
PersonService service = new PersonService(getContext());
service.payment();
}
}
OtherPersonServiceTest.java
package com.example.test;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import com.example.test_sqlite.domain.Person;
import com.example.test_sqlite.service.OtherPersonService;
import com.example.test_sqlite.service.SQLHelper;
public class OtherPersonServiceTest extends AndroidTestCase {
private static final String TAG = "OtherPersonServiceTest";
public void testCreateDB() {
SQLHelper helper = new SQLHelper(getContext());
helper.getWritableDatabase();
}
public void testSave() {
OtherPersonService service = new OtherPersonService(getContext());
Person person = new Person(null, "lipeng23", "15099123213", 100);
service.save(person);
}
public void testDelete() {
OtherPersonService service = new OtherPersonService(getContext());
service.delete(10);
}
public void testUpdate() {
OtherPersonService service = new OtherPersonService(getContext());
Person person = service.findById(8);
person.setName("lipengsdfdsf");
person.setPhone("132145646489");
person.setAmount(20);
service.update(person);
}
public void testFindById() {
OtherPersonService service = new OtherPersonService(getContext());
Person person = service.findById(8);
Log.i(TAG, person.toString());
}
public void testFindByPage() {
OtherPersonService service = new OtherPersonService(getContext());
List<Person> persons = service.getScrollData(2, 3);
for(Person person : persons){
Log.i(TAG,person.toString());
}
}
public void testGetCount() {
OtherPersonService service = new OtherPersonService(getContext());
long count = service.getCount();
Log.i(TAG, "count:" + count);
}
}