Android数据库操作类实例 实体类：UserInfo.java
package my.db;
import java.io.Serializable;
import android.graphics.drawable.Drawable;
public class UserInfo implements Serializable {
public static final String ID = "_id" ;
public static final String USERID = "userId" ;
public static final String TOKEN = "token" ;
public static final String TOKENSECRET = "tokenSecret" ;
public static final String USERNAME = "userName" ;
public static final String USERICON = "userIcon" ;
private String id ;
private String userId ; // 用户id
private String token ;
private String tokenSecret ;
private String userName ;
private Drawable userIcon ;
//getter and setter省略
}
SqliteHelper类：
package my.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class SqliteHelper extends SQLiteOpenHelper{
//用来保存UserID、Access Token、Access Secret的表名
public static final String TB_NAME= "users";
public SqliteHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
//创建表
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL( "CREATE TABLE IF NOT EXISTS "+
TB_NAME+ "("+
UserInfo. ID+ " integer primary key,"+
UserInfo. USERID+ " varchar,"+
UserInfo. TOKEN+ " varchar,"+
UserInfo. TOKENSECRET+ " varchar,"+
UserInfo. USERNAME+ " varchar,"+
UserInfo. USERICON+ " blob"+
")"
);
Log. e("Database" ,"onCreate" );
}
//更新表
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL( "DROP TABLE IF EXISTS " + TB_NAME );
onCreate(db);
Log. e("Database" ,"onUpgrade" );
}
//更新列
public void updateColumn(SQLiteDatabase db, String oldColumn, String newColumn, String typeColumn){
try{
db.execSQL( "ALTER TABLE " +
TB_NAME + " CHANGE " +
oldColumn + " "+ newColumn +
" " + typeColumn
);
} catch(Exception e){
e.printStackTrace();
}
}
}
CRUD类DataHelper:
package my.db;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
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.graphics.Bitmap;
import android.graphics.drawable.Drawable;
import android.util.Log;
public class DataHelper {
// 数据库名称
private static String DB_NAME = "weibo.db" ;
// 数据库版本
private static int DB_VERSION = 2;
private SQLiteDatabase db ;
private SqliteHelper dbHelper ;
public DataHelper(Context context) {
dbHelper = new SqliteHelper(context, DB_NAME , null , DB_VERSION );
db = dbHelper .getWritableDatabase();
}
public void Close() {
db .close();
dbHelper .close();
}
// 获取users表中的UserID、Access Token、Access Secret的记录
public List<UserInfo> GetUserList(Boolean isSimple) {
List<UserInfo> userList = new ArrayList<UserInfo>();
Cursor cursor = db .query(SqliteHelper. TB_NAME , null , null , null , null ,
null , UserInfo. ID + " DESC" );
cursor.moveToFirst();
while (!cursor.isAfterLast() && (cursor.getString(1) != null )) {
UserInfo user = new UserInfo();
user.setId(cursor.getString(0));
user.setUserId(cursor.getString(1));
user.setToken(cursor.getString(2));
user.setTokenSecret(cursor.getString(3));
if (!isSimple) {
user.setUserName(cursor.getString(4));
ByteArrayInputStream stream = new ByteArrayInputStream(cursor.getBlob(5));
Drawable icon = Drawable. createFromStream(stream, "image" );
user.setUserIcon(icon);
}
userList.add(user);
cursor.moveToNext();
}
cursor.close();
return userList;
}
// 判断users表中的是否包含某个UserID的记录
public Boolean HaveUserInfo(String UserId) {
Boolean b = false ;
Cursor cursor = db .query(SqliteHelper. TB_NAME , null , UserInfo. USERID
+ "=?" , new String[]{UserId}, null , null , null );
b = cursor.moveToFirst();
Log. e( "HaveUserInfo" , b.toString());
cursor.close();
return b;
}
// 更新users表的记录，根据UserId更新用户昵称和用户图标
public int UpdateUserInfo(String userName, Bitmap userIcon, String UserId) {
ContentValues values = new ContentValues();
values.put(UserInfo. USERNAME , userName);
// BLOB类型
final ByteArrayOutputStream os = new ByteArrayOutputStream();
// 将Bitmap压缩成PNG编码，质量为100%存储
userIcon.compress(Bitmap.CompressFormat. PNG , 100, os);
// 构造SQLite的Content对象，这里也可以使用raw
values.put(UserInfo. USERICON , os.toByteArray());
int id = db .update(SqliteHelper. TB_NAME , values, UserInfo. USERID + "=?" , new String[]{UserId});
Log. e( "UpdateUserInfo2" , id + "" );
return id;
}
// 更新users表的记录
public int UpdateUserInfo(UserInfo user) {
ContentValues values = new ContentValues();
values.put(UserInfo. USERID , user.getUserId());
values.put(UserInfo. TOKEN , user.getToken());
values.put(UserInfo. TOKENSECRET , user.getTokenSecret());
int id = db .update(SqliteHelper. TB_NAME , values, UserInfo. USERID + "="
+ user.getUserId(), null );
Log. e( "UpdateUserInfo" , id + "" );
return id;
}
// 添加users表的记录
public Long SaveUserInfo(UserInfo user) {
ContentValues values = new ContentValues();
values.put(UserInfo. USERID , user.getUserId());
values.put(UserInfo. TOKEN , user.getToken());
values.put(UserInfo. TOKENSECRET , user.getTokenSecret());
Long uid = db .insert(SqliteHelper. TB_NAME , UserInfo. ID , values);
Log. e( "SaveUserInfo" , uid + "" );
return uid;
}
// 添加users表的记录
public Long SaveUserInfo(UserInfo user, byte [] icon) {
ContentValues values = new ContentValues();
values.put(UserInfo. USERID , user.getUserId());
values.put(UserInfo. USERNAME , user.getUserName());
values.put(UserInfo. TOKEN , user.getToken());
values.put(UserInfo. TOKENSECRET , user.getTokenSecret());
if (icon!= null ){
values.put(UserInfo. USERICON , icon);
}
Long uid = db .insert(SqliteHelper. TB_NAME , UserInfo. ID , values);
Log. e( "SaveUserInfo" , uid + "" );
return uid;
}
// 删除users表的记录
public int DelUserInfo(String UserId) {
int id = db .delete(SqliteHelper. TB_NAME ,
UserInfo. USERID + "=?" , new String[]{UserId});
Log. e( "DelUserInfo" , id + "" );
return id;
}
public static UserInfo getUserByName(String userName,List<UserInfo> userList){
UserInfo userInfo = null ;
int size = userList.size();
for ( int i=0;i<size;i++){
if (userName.equals(userList.get(i).getUserName())){
userInfo = userList.get(i);
break ;
}
}
return userInfo;
}
}