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())){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;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
