sqlite数据库操作基础（一）

一、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;

    }

}