Android进阶篇Sqlite使用(一)

package com.APPShare.db;

import java.util.ArrayList;
import java.util.List;

import com.APPShare.Bean.AppBean;
import com.APPShare.Bean.FriendBean;
import com.APPShare.Bean.WeiboBean;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库操作 
 * [百家姓、短信、联系人的数据库操作]
 */
public class SQLiteHelper {
    private static final String TAG = "SQLiteHelper";

    public static SQLiteHelper instance;

    /**
     * 控制数据库并发标识 [一个线程就1，两个线程就是2，以此类推，没有线程在使用该对象就为0]
     */
    public static int Concurrent = 0;

    /**
     * 表中一条数据的名称
     */
    public static final String KEY_ID = "_id";

    /**
     * 数据库
     */
    private static final String DB_NAME = "appShare.db";

    /**
     * 数据库版本
     */
    private static final int DB_VERSION = 1;

    /**
     * 本地Context对象
     */
    private Context mContext = null;

    /**
     * 创建表的标示 [根据这个标示可以，创建不同的表，标示不同，所创建的表不同]
     */
    private static String TABLEMARKED = "";

    /**--------------APP信息-------------*/
    private static String DB_APP_INFO = "";
    private static String DB_TABLE_APP_INFO = "app_info";
    private static String KEY_APP_NAME = "app_name";
    private static String KEY_APP_TYPE = "app_type";
    
    /**-----------用户个人信息----------*/
    private static String DB_USER_INFO = "";
    private static String DB_TABLE_USER_INFO = "user_info";
    private static String KEY_USER_NAME = "user_name";
    private static String KEY_USER_PHOTO = "user_photo_url";
    private static String KEY_USER_DESCRIPTION = "user_description";
    
    /**----------好友信息--------------*/
    private static String DB_FRIEND_INFO = "";
    private static String DB_TABLE_FRIEND_INFO = "friend_info";
    private static String KEY_FRIEND_NAME = "friend_name";
    private static String KEY_FRIEND_PHOTO = "friend_photo";
    private static String KEY_FRIEND_DESCRIPTION = "friend_description0";
    
    /**
     * 执行open（）打开数据库时，保存返回的数据库对象
     */
    private SQLiteDatabase mSQLiteDatabase = null;

    /**
     * 由SQLiteOpenHelper继承过来
     */
    private DatabaseHelper mDatabaseHelper = null;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        /**
         * 构造函数-创建一个数据库
         * 
         * @param context
         */
        DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);

        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DB_APP_INFO);
            db.execSQL(DB_USER_INFO);
            db.execSQL(DB_FRIEND_INFO);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }

    /**
     * 构造函数-取得Context
     * 
     * @param context
     */
    public SQLiteHelper(Context context) {
        mContext = context;
    }

    public static SQLiteHelper getInstance(Context context) {
        Concurrent = Concurrent + 1;// 加入一个线程
        if (instance == null) {
            synchronized (SQLiteHelper.class) {
                if (instance == null) {
                    instance = new SQLiteHelper(context);
                    instance.open();
                }
            }
        }
        return instance;
    }

    /**
     * 创建数据库
     * 
     * @throws SQLException
     *             2012-1-11
     */
    public void open() throws SQLException {
        // 创建数据库
        DB_APP_INFO = "CREATE TABLE " + DB_TABLE_APP_INFO + " (" + KEY_ID
            + " INTEGER PRIMARY KEY," + KEY_APP_NAME + " TEXT," + KEY_APP_TYPE + " TEXT)";
        
        DB_USER_INFO = "CREATE TABLE " + DB_TABLE_USER_INFO + " (" + KEY_ID + " INTEGER PRIMARY KEY," 
            + KEY_USER_NAME + " TEXT," + KEY_USER_PHOTO + " TEXT," + KEY_USER_DESCRIPTION + " TEXT)";
        
        DB_FRIEND_INFO = "CREATE TABLE " + DB_TABLE_FRIEND_INFO + " (" + KEY_ID + " INTEGER PRIMARY KEY," 
        + KEY_FRIEND_NAME + " TEXT," + KEY_FRIEND_PHOTO + " TEXT," + KEY_FRIEND_DESCRIPTION + " TEXT)";
         
        
        if (mSQLiteDatabase != null) {
            if (mSQLiteDatabase.isOpen()){
                return;
            } else {
                mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
            }
        } else {
            mDatabaseHelper = new DatabaseHelper(mContext);
            mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
        }
    }

    /**
     * 关闭数据库 2011-12-19
     */
    public void close() {
        /** 退出一个线程,如果是最后一个线程的退出，则关闭数据库 */
        Concurrent = Concurrent - 1;
        if (Concurrent <= 0) {
            mDatabaseHelper.close();
            instance = null;
        }
    }
    
    /**插入APPInfo*/
    public boolean insertAppName(List<AppBean> appBeanList){
        boolean flag;
        long RecNo = 0;
        
        ContentValues initialValues = new ContentValues();
        mSQLiteDatabase.beginTransaction();
        
        for(int i=0;i<appBeanList.size();i++){
            initialValues.put(KEY_APP_NAME, appBeanList.get(i).getAppName());
            initialValues.put(KEY_APP_TYPE, appBeanList.get(i).getType());
            RecNo = mSQLiteDatabase.insert(DB_TABLE_APP_INFO, KEY_ID, initialValues);
            initialValues.clear();
        }
        
        mSQLiteDatabase.setTransactionSuccessful();
        mSQLiteDatabase.endTransaction();
        
        if (RecNo >= 0) {
            flag = true;
        } else {
            flag = false;
        }
        
        return flag;
    }
    
    public Cursor fetchAppName(){
        return mSQLiteDatabase.query(DB_TABLE_APP_INFO, new String[]{KEY_ID,KEY_APP_NAME},
                null, null, null, null, null);
    }
    
    /** 更新APPInfo*/
    public void updateAppName(AppBean bean){
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_APP_TYPE, bean.getType());
        
        mSQLiteDatabase.update(DB_TABLE_APP_INFO, contentValues, KEY_APP_NAME + "=" + "'" + bean.getAppName() + "'", null);
    }
    
    /** 根据Type查询APPInfo*/
    public List<AppBean> fetchAppBean(String type){
        List<AppBean> appBeans = new ArrayList<AppBean>();
        Cursor cursor = null;
        
        cursor = mSQLiteDatabase.query(DB_TABLE_APP_INFO, new String[]{KEY_ID,KEY_APP_NAME},
                KEY_APP_TYPE + "=" + "'" + type + "'", null, null, null, null);
        
        if(cursor != null){
            try {
                while (cursor.moveToNext()) {
                    AppBean appBean = new AppBean();
                    appBean.setAppName(cursor.getString(cursor.getColumnIndex(KEY_APP_NAME)));
                    appBeans.add(appBean);
                }
            } finally {
                cursor.close();// 关闭结果集
            }
        }
        return appBeans;
    }
    
    /**查找APP信息*/
    public List<AppBean> fetchAppBeans(){
        List<AppBean> appBeans = new ArrayList<AppBean>();
        Cursor cursor = null;
        
        cursor = mSQLiteDatabase.query(DB_TABLE_APP_INFO, new String[]{KEY_ID,KEY_APP_NAME},
                null, null, null, null, null);
        
        if(cursor != null){
            try {
                while (cursor.moveToNext()) {
                    AppBean appBean = new AppBean();
                    appBean.setAppName(cursor.getString(cursor.getColumnIndex(KEY_APP_NAME)));
                    appBeans.add(appBean);
                }
            } finally {
                cursor.close();// 关闭结果集
            }
        }
        return appBeans;
    }
    
    
    /**插入用户个人信息*/
    public boolean insertUserInfo(WeiboBean weiboBean){
        boolean flag;
        long RecNo = 0;
        
        ContentValues contentValues = new ContentValues();
        mSQLiteDatabase.beginTransaction();
        
        contentValues.put(KEY_USER_NAME, weiboBean.getName());
        contentValues.put(KEY_USER_PHOTO, weiboBean.getPhoto());
        contentValues.put(KEY_USER_DESCRIPTION, weiboBean.getDescription());
        
        RecNo = mSQLiteDatabase.insert(DB_TABLE_USER_INFO, KEY_ID, contentValues);
        mSQLiteDatabase.setTransactionSuccessful();
        mSQLiteDatabase.endTransaction();
        
        if(RecNo >= 0){
            flag = true;
        }else{
            flag = false;
        }
        
        return flag;
    }
    
    /**用户个人信息*/
    public WeiboBean queryUserInfo(){
        Cursor cursor = mSQLiteDatabase.query(DB_TABLE_USER_INFO, new String[]{KEY_ID,KEY_USER_NAME,KEY_USER_PHOTO,KEY_USER_DESCRIPTION}, 
                null, null, null, null, null);
        
        WeiboBean weiboBean = new WeiboBean();;
        
        if(cursor != null){
            try {
                while (cursor.moveToNext()) {
                    weiboBean.setName(cursor.getString(cursor.getColumnIndex(KEY_USER_NAME)));
                    weiboBean.setPhoto(cursor.getString(cursor.getColumnIndex(KEY_USER_PHOTO)));
                    weiboBean.setDescription(cursor.getString(cursor.getColumnIndex(KEY_USER_DESCRIPTION)));
                }
            } finally {
                cursor.close();// 关闭结果集
            }
        }
        return weiboBean;
    }
    
    /**插入好友信息*/
    public boolean insertFriendInfo(List<FriendBean> friendBeans){
        boolean flag;
        long RecNo = 0;
        
        ContentValues contentValues = new ContentValues();
        mSQLiteDatabase.beginTransaction();
        
        for(int i=0;i<friendBeans.size();i++){
            contentValues.put(KEY_FRIEND_NAME, friendBeans.get(i).getName());
            contentValues.put(KEY_FRIEND_PHOTO, friendBeans.get(i).getPhoto());
            contentValues.put(KEY_FRIEND_DESCRIPTION, friendBeans.get(i).getDescription());
            RecNo = mSQLiteDatabase.insert(DB_TABLE_FRIEND_INFO, KEY_ID, contentValues);
            contentValues.clear();
        }
        
        if(RecNo >= 0){
            flag = true;
        }else {
            flag = false;
        }
        return flag;
    }
    
    public void deleteUserIno(){
        mSQLiteDatabase.delete(DB_TABLE_USER_INFO, null, null);
    }
    
    /**查找好友信息*/
    public List<FriendBean> queryFriendInfo(){
        List<FriendBean> friendBeans = new ArrayList<FriendBean>();
        
        Cursor cursor = mSQLiteDatabase.query(DB_TABLE_FRIEND_INFO, new String[]{KEY_ID,KEY_FRIEND_NAME,KEY_FRIEND_PHOTO,KEY_FRIEND_DESCRIPTION}, 
                null, null, null, null, null);
        
        if(cursor != null){
            try {
                while (cursor.moveToNext()) {
                    FriendBean friendBean = new FriendBean();
                    friendBean.setName(cursor.getString(cursor.getColumnIndex(KEY_USER_NAME)));
                    friendBean.setPhoto(cursor.getString(cursor.getColumnIndex(KEY_USER_PHOTO)));
                    friendBean.setDescription(cursor.getString(cursor.getColumnIndex(KEY_USER_DESCRIPTION)));
                    friendBeans.add(friendBean);
                }
            } finally {
                cursor.close();// 关闭结果集
            }
        }
        
        return friendBeans;
    }
    
}