sqlite数据库操作基础（一）

dong5300

一、SqliteHelper类建立表


package com.peacebird.birdnest.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.peacebird.birdnest.utils.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.USERNAME+" varchar"+
")"
);
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();
}
}
}


二、DataHelper类进行表的操作


package com.peacebird.birdnest.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.peacebird.birdnest.utils.UserInfo;

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();
}
//获取users表中的UserID、username的记录
/**
* TODO Put here a description of what this method does.
*
* @return
*/
public List<UserInfo> GetUserList()
{
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.setUserName(cursor.getString(1));
userList.add(user);
cursor.moveToNext();
}
cursor.close();
return userList;
}

//判断users表中的是否包含某个UserID的记录
public Boolean HaveUserInfo(String userName)
{
Boolean b=false;
Cursor cursor=db.query(SqliteHelper.TB_NAME, null, UserInfo.USERNAME + "=" + userName, null, 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 + "=" + UserId, null);
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.ID, user.getUserId());
values.put(UserInfo.USERNAME, user.getUserName());
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;
}
}