sqllite的基本使用封装

记录一下sqllite的基本使用封装，使用时替换其中的部分代码即可

public class DBHelper {

    private static DBConnection helper;
    private static DBHelper dbHelper;

    public static DBHelper getInstance(Context context)
    {
        if(dbHelper == null){
            dbHelper = new DBHelper(context);
        }
        return dbHelper;
    }

    public DBHelper(Context context){
        super();
        getDB(context);
    }

    private static DBConnection getDB(Context context){

        if(helper == null){
            helper = new DBConnection(context);
        }
        return helper;
    }



    public void addManyPro(ArrayList<ChannelInfo> prolist)
    {
    	 SQLiteDatabase db = helper.getWritableDatabase();
    	 db.beginTransaction();
    	 for(int i = 0;i<prolist.size();i++)
    	 {
    		 ContentValues contentValues = new ContentValues();
    	     contentValues.put(UserSchema.channel_id,prolist.get(i).getId());
    	     contentValues.put(UserSchema.channel_name,prolist.get(i).getChannelName());
    	     contentValues.put(UserSchema.group_name, prolist.get(i).getGroupName());
    	     contentValues.put(UserSchema.url, prolist.get(i).getUrl());
    	     db.insert(UserSchema.TABLE_NAME, null, contentValues);
    	 }
    	 db.setTransactionSuccessful();
    	 db.endTransaction();
    	 db.close();
    }

    public void addPro(ChannelInfo pro)
    {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(UserSchema.channel_id,pro.getId());
        contentValues.put(UserSchema.channel_name,pro.getChannelName());
        contentValues.put(UserSchema.group_name, pro.getGroupName());
        contentValues.put(UserSchema.url, pro.getUrl());
        db.insert(UserSchema.TABLE_NAME, null, contentValues);
        db.close();

    }


    public void DropTable() {
        SQLiteDatabase db = null;
        try {
        	
            db = helper.getWritableDatabase();
            String sql = "DROP TABLE " + UserSchema.TABLE_NAME;
            db.execSQL(sql);

            String create_sql = "CREATE TABLE link_info(id integer primary key autoincrement,channel_id integer,channel_name varchar(100),"
                    + "url varchar(200),group_name varchar(50))";
            db.execSQL(create_sql);
        } catch (Exception ex) {

        }
        finally {
            if(db!=null){
                db.close();
            }
        }

    }

    public void DropDatabase(Context ctx, String DATABASE_NAME) {
        try {
            ctx.deleteDatabase(DATABASE_NAME);
        } catch (Exception ex) {

        }
    }

    public void update(ContentValues values, String where, String[] whereArgs) {

        SQLiteDatabase db = helper.getWritableDatabase();
        db.update(UserSchema.TABLE_NAME, values, where, whereArgs);
        db.close();
    }

    public void delete(String where, String[] whereArgs) {

        SQLiteDatabase db = helper.getWritableDatabase();
        db.delete(UserSchema.TABLE_NAME, where, whereArgs);
        db.close();
    }

    public void delDB(String DBName) {

        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("DROP Database " + DBName);
        db.close();
    }

    public int queryChannelSize() {
        int count = 0;
        Cursor cursor = null;
        SQLiteDatabase db = null;
        try {
            db = helper.getReadableDatabase();
            cursor = db.rawQuery("select count(*)from link_info",null);
            cursor.moveToFirst();
            count = cursor.getInt(0);
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            if(cursor!=null){
                cursor.close();
            }
            if(db!=null){
                db.close();
            }
        }

        return count;
    }

    public ArrayList<ChannelInfo> query() {

        try {
            final SQLiteDatabase db = helper.getReadableDatabase();
            Cursor c = null;
           /* c = db.query(UserSchema.TABLE_NAME, null, where, whereArgs, null,
                    null, orderBy, limit);*/
            c = db.query(UserSchema.TABLE_NAME, new String[] {UserSchema.channel_id, UserSchema.channel_name,UserSchema.url,UserSchema.group_name },
                    null, null, null, null, null);
            ArrayList<ChannelInfo> lst = new ArrayList<ChannelInfo>();

            while (c != null && c.moveToNext()) {
                ChannelInfo item = new ChannelInfo();
                item.setId(c.getInt(c.getColumnIndex("channel_id")));
                item.setChannelName(c.getString(c.getColumnIndex("channel_name")));
                item.setUrl(c.getString(c.getColumnIndex("url")));
                item.setGroupName(c.getString(c.getColumnIndex("group_name")));
                lst.add(item);
            }
            if (c != null)c.close();
            return lst;
        } catch (Exception e) {
            // TODO: handle exception
        }

        return null;

    }

    public ArrayList<String> queryChannelListName() {
        ArrayList<String> channelList = new ArrayList<>();
        try {
            final SQLiteDatabase db = helper.getReadableDatabase();
            Cursor c = null;
           /* c = db.query(UserSchema.TABLE_NAME, null, where, whereArgs, null,
                    null, orderBy, limit);*/
            c = db.query(UserSchema.TABLE_NAME, new String[] {UserSchema.channel_id, UserSchema.channel_name,UserSchema.url,UserSchema.group_name },
                    null, null, null, null, null);
            ArrayList<ChannelInfo> lst = new ArrayList<ChannelInfo>();

            while (c != null && c.moveToNext()) {
                channelList.add(c.getString(c.getColumnIndex("channel_name")));
            }
            if (c != null)c.close();
            return channelList;
        } catch (Exception e) {
            // TODO: handle exception
        }

        return null;

    }

    public ArrayList<String> queryGroupList() {
        ArrayList<String> channelList = new ArrayList<>();
        try {
            final SQLiteDatabase db = helper.getReadableDatabase();
            Cursor c = null;
           /* c = db.query(UserSchema.TABLE_NAME, null, where, whereArgs, null,
                    null, orderBy, limit);*/
           String sql = "SELECT DISTINCT group_name FROM link_info;";
            c = db.rawQuery(sql,null);
            while (c != null && c.moveToNext()) {
                channelList.add(c.getString(c.getColumnIndex("group_name")));
            }
            if (c != null)c.close();
            return channelList;
        } catch (Exception e) {
            // TODO: handle exception
        }

        return null;

    }

    public ArrayList<String> queryUrlList() {
        ArrayList<String> channelList = new ArrayList<>();
        try {
            final SQLiteDatabase db = helper.getReadableDatabase();
            Cursor c = null;
           /* c = db.query(UserSchema.TABLE_NAME, null, where, whereArgs, null,
                    null, orderBy, limit);*/
            c = db.query(UserSchema.TABLE_NAME, new String[] {UserSchema.channel_id, UserSchema.channel_name,UserSchema.url,UserSchema.group_name },
                    null, null, null, null, null);
            ArrayList<ChannelInfo> lst = new ArrayList<ChannelInfo>();

            while (c != null && c.moveToNext()) {
                channelList.add(c.getString(c.getColumnIndex("url")));
            }
            if (c != null)c.close();
            return channelList;
        } catch (Exception e) {
            // TODO: handle exception
        }

        return null;

    }

    public ArrayList<ChannelInfo> query(String groupName) {

        try {
            final SQLiteDatabase db = helper.getReadableDatabase();
            Cursor c = null;
           /* c = db.query(UserSchema.TABLE_NAME, null, where, whereArgs, null,
                    null, orderBy, limit);*/
            String sql = "select * from link_info where group_name='"+groupName+"'";
            Log.v("aaa", "channellist database sql : "+sql);
            c = db.rawQuery(sql,null);
           ArrayList<ChannelInfo> lst = new ArrayList<ChannelInfo>();

            while (c != null && c.moveToNext()) {
                ChannelInfo item = new ChannelInfo();
                item.setId(c.getInt(c.getColumnIndex("channel_id")));
                item.setChannelName(c.getString(c.getColumnIndex("channel_name")));
                item.setUrl(c.getString(c.getColumnIndex("url")));
                item.setGroupName(c.getString(c.getColumnIndex("group_name")));
                lst.add(item);
            }
            if (c != null)c.close();
            return lst;
        } catch (Exception e) {
            // TODO: handle exception
        }

        return null;

    }

    public interface UserSchema {
        String TABLE_NAME = "link_info";
        String channel_id = "channel_id";
        String channel_name = "channel_name";
        String group_name = "group_name";
        String url = "url";
    }

    public static class DBConnection extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "netlink.db";
        private static final int DATABASE_VERSION = 1;

        private DBConnection(Context ctx) {
            super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            String create_sql = "CREATE TABLE link_info(id integer primary key autoincrement,channel_id integer,channel_name varchar(100),"
                    + "url varchar(200),group_name varchar(50))";
            db.execSQL(create_sql);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            String sql=" DROP TABLE IF EXISTS "+UserSchema.TABLE_NAME;
            db.execSQL(sql);
            onCreate(db);
        }

    }

}