06 - 数据操作二：sqlite数据库与listview显示

数据库查询操作

public class DBhelper extends SQLiteOpenHelper {

	public DBhelper(Context context ) {
		super(context, "yzadb", null, 3);
		//null 默认的系统游标工厂  版本号1不建议0  创建数据库
	}
	//数据库第一次被创建的时候调用
	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("进入");
		//创建表
		String sql="create table person(id integer primary key autoincrement," +
				" name varchar(50))"; 
		db.execSQL(sql);
	}
	//数据库版本号变化的时候调用
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//String sql="alter table person add phone varchar(50)";
		String sql="alter table person add amount integer";
		db.execSQL(sql);
	}
}

public class PersonService {
	private DBhelper db;

	public PersonService(Context c) {
		this.db = new DBhelper(c);
	}

	public void save(Person person) {
		SQLiteDatabase d = db.getWritableDatabase();
		String sql = "insert into person(name,phone,amount) values(?,?,?)";
		d.execSQL(sql, new Object[] { person.getName(), person.getPhone() ,person.getAmount()});
		d.close();// 只有一个地方使用可以不关闭
	}

	public void delete(Integer id) {
		SQLiteDatabase d = db.getWritableDatabase();
		String sql = "delete from person where id= ?";
		d.execSQL(sql, new Object[] { id });
		d.close();// 只有一个地方使用可以不关闭
	}

	public void update(Person person) {
		SQLiteDatabase d = db.getWritableDatabase();
		String sql = "update person set name = ?,phone=?,amount=? where id=?";
		d.execSQL(sql, new Object[] { person.getName(), person.getName(),person.getAmount(),
				person.getId() });
		d.close();// 只有一个地方使用可以不关闭

	}

	public Person find(Integer id) {
		Person p = new Person();
		SQLiteDatabase d = db.getReadableDatabase();
		String sql = "select * from person where id=?";
		Cursor cur = d.rawQuery(sql, new String[] { id.toString() });
		if (cur.moveToFirst()) {
			p.setId(cur.getInt(cur.getColumnIndex("id")));
			p.setName(cur.getString(cur.getColumnIndex("name")));
			p.setPhone(cur.getString(cur.getColumnIndex("phone")));
			p.setAmount(cur.getInt(cur.getColumnIndex("amount"))); 
			return p;
		}
		cur.close();
		d.close();// 只有一个地方使用可以不关闭
		return null;
	}

	public List<Person> getpage(int begin, int max) {
		List<Person> ps = new ArrayList<Person>();
		SQLiteDatabase d = db.getReadableDatabase();
		String sql = "select * from person order by id asc limit ?,?";
		Cursor cur = d.rawQuery(sql, new String[] { String.valueOf(begin),
				String.valueOf(max) });
		while(cur.moveToNext()){
			Person p = new Person();
			p.setId(cur.getInt(cur.getColumnIndex("id")));
			p.setName(cur.getString(cur.getColumnIndex("name")));
			p.setPhone(cur.getString(cur.getColumnIndex("phone")));
			p.setAmount(cur.getInt(cur.getColumnIndex("amount"))); 
			ps.add(p);
			 
		}
		return ps;
	}

	public int getCount() {
		SQLiteDatabase d = db.getReadableDatabase();
		String sql = "select count(*) from person  ";
		Cursor cur = d.rawQuery(sql, null);
		cur.moveToFirst();
		int resrult = cur.getInt(0);
		return resrult;

	}

	public void payment() { //事务控制
		SQLiteDatabase d = db.getWritableDatabase();
		d.beginTransaction();
		try {
			String sql1 = "update person set amount=amount-1 where id=1 ";
			String sql2 = "update person set amount=amount+1 where id=2 ";
			d.execSQL(sql1);
			d.execSQL(sql2);
			d.setTransactionSuccessful();// 设置事务标志为true 不设置则false
		} finally {
			d.endTransaction();// 结束事务，若事务标志为true则提交 否则回滚
		}
	}
}

listview显示

main.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="wrap_content"
    android:orientation="vertical"   >
    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="fill_parent"
        android:orientation="horizontal" >
        <TextView
            android:text="姓名"
            android:layout_width="100dp"
            android:layout_height="wrap_content" />
        <TextView
            android:text="电话"
            android:layout_width="150dp"
            android:layout_height="wrap_content" />
        <TextView
            android:text="金额"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>
    <ListView
        android:id="@+id/listview"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent" />
</LinearLayout>

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:layout_width="100dp"
        android:layout_height="wrap_content"
        android:id="@+id/name" />
    <TextView
        android:layout_width="150dp"
        android:layout_height="wrap_content"
        android:id="@+id/phone" />
	<TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:id="@+id/amount" />
</LinearLayout>

SimpleAdapter 简单适配器

public class SqliteActivity extends Activity {
	/** Called when the activity is first created. */
	private ListView listView;
	private PersonService ps;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		ps = new PersonService(this);
		listView = (ListView) this.findViewById(R.id.listview);
		show();
	}

	private void show() {
		List<Person> pl = this.ps.getpage(0, 20);
		List<HashMap<String, Object>> data = new ArrayList<HashMap<String, Object>>();
		for (Person p : pl) {
			HashMap<String, Object> item = new HashMap<String, Object>();
			item.put("name", p.getName());
			item.put("phone", p.getPhone());
			item.put("amount", p.getAmount());
			item.put("id", p.getId());
			data.add(item);
		}
		SimpleAdapter sa = new SimpleAdapter(this, data, R.layout.item,
				new String[] { "name", "phone", "amount" }, new int[] {
						R.id.name, R.id.phone, R.id.amount });
		listView.setAdapter(sa);

	}
}

SimpleCursorAdapter简单游标适配器

private void show2() { //利用游标显示 结果中必须包含"_id"这个字段,可以改表或者别名
		SimpleCursorAdapter sca = new SimpleCursorAdapter(this, R.layout.item,
				ps.getCursorpage(0, 20), new String[] { "name", "phone",
						"amount" }, new int[] { R.id.name, R.id.phone,
						R.id.amount });
		listView.setAdapter(sca);
	}

自定义适配器

public class PersonAdapter extends BaseAdapter {

	private List<Person> pl; // 绑定的数据
	private int resource;// 绑定界面
	private LayoutInflater inflater;

	public PersonAdapter(Context context, List<Person> pl, int resource) {
		super();
		this.pl = pl;
		this.resource = resource;
		inflater = (LayoutInflater) context
				.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
	}

	@Override
	public int getCount() {
		return pl.size();
	}

	@Override
	public Object getItem(int paramInt) {
		return pl.get(paramInt);
	}

	@Override
	public long getItemId(int paramInt) {
		return paramInt;
	}

	@Override
	public View getView(int paramInt, View paramView, ViewGroup paramViewGroup) {
		if (paramView == null) {// 第一页则新建
			paramView = inflater.inflate(resource, null);// 生成条目界面对象
		}
		TextView nameView = (TextView) paramView.findViewById(R.id.name);
		TextView phoneView = (TextView) paramView.findViewById(R.id.phone);
		TextView amountView = (TextView) paramView.findViewById(R.id.amount);
		Person p = pl.get(paramInt);
		nameView.setText(p.getName());// 数据绑定
		phoneView.setText(p.getPhone());
		amountView.setText(p.getAmount().toString());
		return paramView;
	}
}

private void show3() { //自定义适配器
		List<Person> pl = this.ps.getpage(0, 30);
		PersonAdapter pa= new PersonAdapter(this, pl, R.layout.item);
		listView.setAdapter(pa);
	}