Android知识点八(sqllite数据库操作，以及sqlite+handler+XListView实现上拉刷新下拉加载)

    1.DBHelper类数据库辅助类:

   

public class DBHelper extends SQLiteOpenHelper {
	private static final int version = 1;
	public DBHelper(Context context, String name) {
		super(context, name, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table if not exists Helper("
				+ "id integer primary key autoincrement," + "username varchar,"
				+ "userage varchar," + "userPhone varchar," + "userQQ varchar)";
		try {
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion != newVersion) {
			db.execSQL("DROP TABLE IF EXISTS Helper");
			onCreate(db);
		}
	}

}

2.BDDAO逻辑类:

public class BDDAO {
	private static final String DATABASE_NAME = "Helper.db";
	public static final String ROOT = Environment.getExternalStorageDirectory()
			.getPath();
	private Context mContext;
	private DBHelper mDBHelper;

	public BDDAO(Context context) {
		this.mContext = context;
		String name = DATABASE_NAME;
		File file = Environment.getExternalStorageDirectory();
		if (file.canWrite()) {
			String path = ROOT + File.separator + "Helper/";
			File dbDir = new File(path);
			if (!dbDir.exists()) {
				dbDir.mkdirs();
			}
			name = path + name;
		}
		this.mDBHelper = new DBHelper(mContext, name);
	}

	/**
	 * 关闭DB
	 */
	public void close() {
		if (mDBHelper != null) {
			mDBHelper.close();
		}
	}

	/**
	 * 获取所有数据
	 */
	public List<User> getUserList() {
		List<User> groups = new ArrayList<User>();
		SQLiteDatabase db = mDBHelper.getReadableDatabase();
		try {
			Cursor cur = db.query("Helper", new String[] { "*" }, null, null,
					null, null, null);
			while (cur.moveToNext()) {
				User info = makeUserCursor(cur);
				groups.add(info);
			}
			cur.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return groups;
	}

	/**
	 * 根据查询条件查询
	 * 
	 * @param selection
	 *            数据库字段
	 * @param param
	 *            实体字段
	 * @return
	 */
	public List<User> queryUser(String selection, String[] param) {
		List<User> groups = new ArrayList<User>();
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		try {

			Cursor cur = db.query("Helper", new String[] { "*" }, selection,
					param, null, null, null);

			while (cur.moveToNext()) {
				User info = makeUserCursor(cur);
				groups.add(info);
			}
			cur.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return groups;
	}

	/**
	 * 分页
	 * @param pagenum
	 * @param pageSize
	 * @return
	 */
	public List<User> queryUser(int pagenum, int pageSize) {
		List<User> groups = new ArrayList<User>();
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		try {
			Cursor cur = db
					.rawQuery(
							"select id,username,userage,userPhone,userQQ from Helper limit ?,?",
							new String[] { (pagenum - 1) * pageSize + "",
									pageSize + "" });
			while (cur.moveToNext()) {
				User info = makeUserCursor(cur);
				groups.add(info);
			}
			cur.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return groups;
	}

	/**
	 * 根据查询条件查询(有排序)
	 * 
	 * @param selection
	 *            数据库字段
	 * @param param
	 *            实体字段
	 * @param orderby
	 *            排序
	 * @return
	 */
	public List<User> queryUser(String selection, String[] param, String orderby) {
		List<User> groups = new ArrayList<User>();
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		try {
			Cursor cur = db.query("Helper", new String[] { "*" }, selection,
					param, null, null, orderby);

			while (cur.moveToNext()) {
				User info = makeUserCursor(cur);
				groups.add(info);
			}
			cur.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close();
		}

		return groups;
	}

	private User makeUserCursor(Cursor cur) {
		User u = new User();
		u.setUserage(cur.getString(cur.getColumnIndex("userage")));
		u.setUsername(cur.getString(cur.getColumnIndex("username")));
		u.setUserphone(cur.getString(cur.getColumnIndex("userPhone")));
		u.setUserqq(cur.getString(cur.getColumnIndex("userQQ")));
		return u;
	}

	/**
	 * 新增
	 * 
	 * @param u
	 * @return
	 */
	public long addUser(User u) {
		long rowid = 0;
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		ContentValues values = makeContentValues(u);
		try {
			db.beginTransaction();
			rowid = db.insert("Helper", null, values);
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			db.close();
		}
		return rowid;
	}

	private ContentValues makeContentValues(User u) {
		ContentValues mContentValues = new ContentValues();
		mContentValues.put("userage", u.getUserage());
		mContentValues.put("username", u.getUsername());
		mContentValues.put("userPhone", u.getUserphone());
		mContentValues.put("userQQ", u.getUserqq());
		return mContentValues;
	}

	/**
	 * 更新
	 * 
	 * @param u
	 * @return
	 */
	public boolean updateUser(User u) {
		boolean b_result = false;

		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		ContentValues values = makeContentValues(u);

		try {
			db.beginTransaction();
			long rowid = db.update("Helper", values, "id=?",
					new String[] { String.valueOf(u.getId()) });
			db.setTransactionSuccessful();
			if (rowid > 0)
				b_result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			db.close();
		}

		return b_result;
	}

	/**
	 * 删除(根据ID)
	 * 
	 * @param id
	 * @return
	 */
	public boolean deleteUser(int id) {
		boolean b_result = false;
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		try {
			db.beginTransaction();
			long rowid = db.delete("Helper", "Id=?",
					new String[] { String.valueOf(id) });
			db.setTransactionSuccessful();
			if (rowid > 0)
				b_result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			db.close();
		}
		return b_result;
	}

	/**
	 * 根据查询条件删除
	 * 
	 * @param selection
	 * @param param
	 * @return
	 */
	public boolean deleteUser(String selection, String[] param) {
		boolean b_result = false;
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		try {
			db.beginTransaction();
			long rowid = db.delete("Helper", selection, param);
			db.setTransactionSuccessful();
			if (rowid > 0)
				b_result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			db.close();
		}
		return b_result;
	}

	/**
	 * 是否存在这条记录
	 * 
	 * @param selection
	 * @param param
	 * @return
	 */
	public boolean isExit(String selection, String[] param) {
		SQLiteDatabase db = mDBHelper.getWritableDatabase();
		try {
			Cursor cur = db.query("Helper", new String[] { "*" }, selection,
					param, null, null, null);

			while (cur.moveToNext()) {
				return true;
			}
			cur.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return false;
	}

}

3.UI展示类:

public class ListUserActivity extends Activity {
	private XListView mXListView;
	private Handler mHandler;
	private BDDAO mBDDAO;
	private UserAdapter mUserAdapter;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.user_list_act);
		mHandler = new Handler();
		mBDDAO = new BDDAO(this);
		mXListView = (XListView) findViewById(R.id.xlistview);
		mXListView.setOnXListViewListener(new OnXListViewListener() {
			@Override
			public void onRefresh(XListView v) {
				v.setTag(String.valueOf("1"));
				this.onLoadMore(v);
			}

			@Override
			public void onLoadMore(XListView v) {
				mHandler.post(new Runnable() {
					@Override
					public void run() {
						List<User> list = mBDDAO.queryUser(Integer.valueOf(String.valueOf(mXListView.getTag())),5);
						if (list.size() > 0) {
							int page = Integer.parseInt(String.valueOf(mXListView.getTag()));
							mXListView.setTag(page + 1);
							if (page == 1) {
								mUserAdapter = new UserAdapter(ListUserActivity.this, list);
								mXListView.setAdapter(mUserAdapter);
								mXListView.onRefreshComplete();
							} else {
								if (list.size() > 0) {
									mUserAdapter.addAll(list);
									mXListView.onLoadMoreComplete();
								} else {
									mXListView.onLoadMoreComplete();
									Toast.makeText(ListUserActivity.this,"没有更多的数据了...", Toast.LENGTH_LONG).show();
								}
							}
						}else{
							mXListView.onLoadMoreComplete();
							Toast.makeText(ListUserActivity.this,"没有更多的数据了...", Toast.LENGTH_LONG).show();
						}
					}
				});
			}
		});

		initData();
	}

	void initData() {
		UIHelper.showLoadDialog(this, "正在加载...");
		mHandler.post(new Runnable() {
			@Override
			public void run() {
				List<User> list = mBDDAO.queryUser(Integer.valueOf(String.valueOf(mXListView.getTag())), 5);
				mUserAdapter = new UserAdapter(ListUserActivity.this, list);
				mXListView.setAdapter(mUserAdapter);
				UIHelper.cloesLoadDialog();
			}
		});
	}

其中的Adapter,bean,xml就不给出了比较简单,XListView可自行在网上下载!