Android数据库操作类 增加、删除、修改
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; } }