Android：SNS客户端开发三：数据库操作(一)

      在对账号进行操作的过程中，不可避免的会使用到数据库，存储相应的账号信息。本文结合SNS客户端项目简单讲述Android上有关数据库的部分操作。

      Android上采用SQLite数据库，作为一款轻型数据库，它在Android上有着很好的表现，操作起来也十分简单。在这里也像大家推荐一款SQLite的可视化查看工具SQLite Database Browser。大家可以把Android设备中对应应用的数据库导出，并通过SQLite Database Browser查看数据库里的信息。

   首先在Android上操作SQLite需要一个工具类，我们使用这个类来控制数据版本，打开数据库以及创建对应的数据表：

public class DBHelper extends SQLiteOpenHelper {

	private static final int VERSION = 1;
	private String create_usertb;
	private String create_contexttb;
	private String create_typetb;

	public DBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	public DBHelper(Context context, String name, int version) {
		this(context, name, null, version);
	}

	public DBHelper(Context context, String name) {
		this(context, name, VERSION);
	}

	public DBHelper(Context context, String name, String create_usertb ) {
		this(context, name, VERSION);
		this.create_usertb = create_usertb;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(create_usertb);// 生成用户信息表
		System.out.println("生成user表");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		System.out.print("数据库版本" + newVersion);
		db.execSQL(create_usertb);// 更新用户信息表
		System.out.println("生成user表");
		
	}

}

 然后我们在DBOperate中对数据库进行具体操作。

public class DBOperate {

	// 数据库名称SNSHelper
	// user表，weibotext表
	private static final String DB_NAME = "SNSHelper";
	private static final String CREATE_USERTB = "create table if not exists user"
			+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,userid varchar(50),name varchar(20),type varchar(20),nickname varchar(20),"
			+ "access_token varchar(50),access_secret varchar(50),icon blob)";
	private Context context;

	public DBOperate(Context context) {
		this.context = context;
	}

	public Boolean initDB() {//初始化数据库，程序第一次启动时操作

		DBHelper dbhelper = new DBHelper(context, DB_NAME, CREATE_USERTB);
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		if (db.isOpen()) {
			db.close();
			return true;
		} else {
			return false;
		}
	}

	public Long insertUser(ContentValues values) {//添加用户操作

		DBHelper dbhelper = new DBHelper(context, DB_NAME);
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Long row_id = db.insert("user", null, values);
		db.close();
		return row_id;

	}

	public Boolean isExist(ContentValues values) {//检查改用户是否存在
		List<UserInfo> userlist = new ArrayList<UserInfo>();
		userlist = findAllUser();
		for (int i = 0; i < userlist.size(); i++) {
			if (userlist.get(i).getAccess_secret()
					.equalsIgnoreCase(values.get("access_secret").toString())
					&& userlist
							.get(i)
							.getAccess_token()
							.equalsIgnoreCase(
									values.get("access_token").toString())) {
				return true;
			}else{
				return false;
			}
		}
		if (userlist.size() == 0) {
			return false;
		} else {
			return true;
		}

	}
public List<UserInfo> findAllUser() {//查找所有的用户

		List<UserInfo> userlist = new ArrayList<UserInfo>();
		DBHelper dbhelper = new DBHelper(context, DB_NAME);
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.query("user", null, null, null, null, null,
				"id desc");
		cursor.moveToFirst();
		while ((!cursor.isAfterLast()) && (cursor.getString(1) != null)) {
			UserInfo user = new UserInfo();
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			user.setId(id);
			String userid = cursor.getString(cursor.getColumnIndex("userid"));
			System.out.println("++++++++++" + userid + "+++++++++");
			user.setName(userid);
			String name = cursor.getString(cursor.getColumnIndex("name"));
			System.out.println("++++++++++" + name + "+++++++++");
			user.setName(name);
			String nickname = cursor.getString(cursor.getColumnIndex("nickname"));
			user.setNickname(nickname);
			String type = cursor.getString(cursor.getColumnIndex("type"));
			System.out.println("++++++++++" + type + "+++++++++");
			user.setType(type);
			String access_token = cursor.getString(cursor
					.getColumnIndex("access_token"));
			user.setAccess_token(access_token);
			String access_key = cursor.getString(cursor
					.getColumnIndex("access_secret"));
			user.setAccess_secret(access_key);
			ByteArrayInputStream stream = new ByteArrayInputStream(
					cursor.getBlob(cursor.getColumnIndex("icon")));
			Drawable icon = Drawable.createFromStream(stream, "img_" + name);
			user.setIcon(icon);
			userlist.add(user);
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		return userlist;

	}

	public UserInfo findUser(int ids) {//根据数据表中的id查找用户

		UserInfo user = new UserInfo();
		DBHelper dbhelper = new DBHelper(context, DB_NAME);
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.query("user", null, "id=" + ids, null, null, null,
				null);
		Boolean isFind = cursor.moveToFirst();
		if (isFind) {
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			user.setId(id);
			String name = cursor.getString(cursor.getColumnIndex("name"));
			user.setName(name);
			String userid = cursor.getString(cursor.getColumnIndex("userid"));
			user.setUserid(userid);
			String nickname = cursor.getString(cursor.getColumnIndex("nickname"));
			user.setNickname(nickname);
			String type = cursor.getString(cursor.getColumnIndex("type"));
			user.setType(type);
			String access_token = cursor.getString(cursor
					.getColumnIndex("access_token"));
			user.setAccess_token(access_token);
			String access_key = cursor.getString(cursor
					.getColumnIndex("access_secret"));
			user.setAccess_secret(access_key);
			ByteArrayInputStream stream = new ByteArrayInputStream(
					cursor.getBlob(cursor.getColumnIndex("icon")));
			Drawable icon = Drawable.createFromStream(stream, "img_" + name);
			user.setIcon(icon);
			cursor.close();
			db.close();
			return user;
		} else {
			db.close();
			return null;
		}

	}

	public int deleteUser(int num) {//根据数据表中id删除用户

		DBHelper dbhelper = new DBHelper(context, DB_NAME);
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		int row = db.delete("user", "id=" + num, null);
		db.close();
		return row;

	}
}

 

其中用到了UserInfo类用来表示用户包含用户相应的属性

public class UserInfo implements Serializable {

	private static final long serialVersionUID = 8825186846021795496L;
	private int id;// User表中的ID
	private String name;// 用户姓名
	private String nickname;
	private String userid;//网络中的id
	private Drawable icon;// 用户头像，用二进制字符串存储在数据库中
	private String type;// 用户类型，例如SINA，QQ，RENREN等
	private String access_token;// OAuth认证token
	private String access_secret;// OAuth认证key（secrete）

	public String getUserid() {
		return userid;
	}

	public void setUserid(String userid) {
		this.userid = userid;
	}

	public String getNickname() {
		return nickname;
	}

	public void setNickname(String nickname) {
		this.nickname = nickname;
	}

	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 Drawable getIcon() {
		return icon;
	}

	public void setIcon(Drawable icon) {
		this.icon = icon;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getAccess_token() {
		return access_token;
	}

	public void setAccess_token(String access_token) {
		this.access_token = access_token;
	}

	public String getAccess_secret() {
		return access_secret;
	}

	public void setAccess_secret(String access_secret) {
		this.access_secret = access_secret;
	}

	
}

 注:将UserInfo序列化，是为了方便在后面的程序中通过Intent在不同Activity之间传递用户信息