Android对SQLITE数据库的操作的类包含在android.database.sqlite这个包里面,如下所示,列出了这个包包含的接口和类。最常用的是SQLOpenHelper用来打开、创建一个数据库;SQLiteDatabase提供一系列方法管理数据库,包括对数据的查询、插入、修改、表的添加、删除等等。
android.database.sqlite
接口
SQLiteCursorDriver SQLiteDatabase.CursorFactory
类
SQLiteClosable SQLiteCursor SQLiteDatabase SQLiteOpenHelper SQLiteProgram SQLiteQuery SQLiteQueryBuider SQLiteStatement
SQLiteHelper是对SQLiteOpenHelper的简单封装,只是封装了数据库名和初始数据库创建表的操作。DataHelper实现数据查询、更新等操作;这个是手机客户端微博程序中的代码,数据库存储用户信息。
对数据库的操作类似下面的封装是比较常见。
package com.android.caigang.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; import com.android.caigang.model.UserInfo; 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(); } } } |
package com.android.caigang.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; import com.android.caigang.model.UserInfo; 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; } } |