Android SQLite

在实际的应用程序中，为了更好的对SQLite数据库的创建、打开以及更改进行管理，往往会编写一个继承自SQLiteOpenHelper的数据库辅助类帮助我们创建和打开数据库。

 1 public class SqliteHelper extends SQLiteOpenHelper {
 2     
 3     public static final String TB_NAME = "user";
 4     public SqliteHelper(Context context, String name, CursorFactory factory, int version) {
 5         super(context, name, factory, version);
 6         // TODO Auto-generated constructor stub
 7     }
 8     @Override
 9     public void onCreate(SQLiteDatabase db) {
10         // TODO Auto-generated method stub
11         db.execSQL("CREATE TABLE IF NOT EXISTS "+
12                 TB_NAME+"("+
13                 UserInfo.ID+" integer primary key,"+
14                 UserInfo.USERID+" varchar,"+
15                 UserInfo.TOKEN+" varchar,"+
16                 UserInfo.TOKENSECRET+" varchar,"+
17                 UserInfo.USERNAME+" varchar,"+
18                 UserInfo.USERICON+" blob"+
19                 ")"
20                 );
21         Log.e("Database","onCreate");        
22     }
23     @Override
24     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
25         // TODO Auto-generated method stub
26         db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);
27         onCreate(db);
28         Log.e("Database","onUpgrade");        
29     }
30     //更新列
31     public void updateColumn(SQLiteDatabase db, String oldColumn, String newColumn, String typeColumn){
32         try{
33             db.execSQL("ALTER TABLE " +
34                     TB_NAME + " CHANGE " +
35                     oldColumn + " "+ newColumn +
36                     " " + typeColumn
37             );
38         }catch(Exception e){
39             e.printStackTrace();
40         }
41     }
42     
43 
44 }

 然后创建一个DataHelper的辅助类来处理，增删改等操作。

public class DataHelper {
    //数据库名称
    private static String DB_NAME = "mysinaweibo.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();
    }

每次在程序中需要获得某个数据库的实例对象时，我们只需要先用这个数据库的文件名构造这样的一个数据库辅助对象，然后调用getWriteableDatabase()或者getReadableDatabase()方法，就可以获得这个数据库的SQLiteDatabase对象。

getWriteableDatabase()。以可读写的的方式创建或打开一个SQLite数据库并返回SQLiteDatabase对象。

getReadableDatabase()。创建或打开一个SQLite数据库，但并不一定只返回只读的SQLiteDatabase对象。正常情况下，会返回和getWriteableDatabase一样的对象。若出现磁盘已满或者数据库只能以只读的方式打开的情况下，则会返回一个只读的SQLiteDatabase 对象。

在DataHelper类中处理一下增删改的操作。

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 userInfo = new UserInfo();
            userInfo.setId(cursor.getString(0));
            userInfo.setUserId(cursor.getString(1));
            userInfo.setToken(cursor.getString(2));
            userInfo.setTokenSecret(cursor.getString(3));
            if(!isSimple)
            {
                userInfo.setUserName(cursor.getString(4));
                ByteArrayInputStream biStream = new ByteArrayInputStream(cursor.getBlob(5));
                Drawable drawicon = Drawable.createFromStream(biStream, "image");
                userInfo.setUserIcon(drawicon);                
            }
            userList.add(userInfo);
            cursor.moveToNext();            
        }
        cursor.close();        
        return userList;
    }
    //判断users表中的是否包含某个UserID的记录
    public Boolean HaveUserInfo(String UserId)
    {
        Boolean bExist;
        bExist=false;
        Cursor cursor = db.query(SqliteHelper.TB_NAME,null,UserInfo.USERID+ "=" +UserId, null, null, null, null);
        bExist = cursor.moveToFirst();
        cursor.close();
        return bExist;
    }
    //更新users表的记录，根据UserId更新用户昵称和用户图标
    public int UpdateUserInfo(String userName,Bitmap userIcon,String UserId)
    {
        ContentValues values = new ContentValues();
        values.put(UserInfo.USERNAME, userName);        
        final ByteArrayOutputStream  os = new ByteArrayOutputStream();
        // 将Bitmap压缩成PNG编码，质量为100%存储          
        userIcon.compress(Bitmap.CompressFormat.PNG, 100, os);        
        values.put(UserInfo.USERICON, os.toByteArray());
        
        int id = db.update(SqliteHelper.TB_NAME, values, UserInfo.USERID+"="+UserId, null);
        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);
        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 int DelUserInfo(String UserId){
        int id=  db.delete(SqliteHelper.TB_NAME, UserInfo.USERID +"="+UserId, null);
        Log.e("DelUserInfo",id+"");
        return id;
    }