SQLite操作——Android

SQLite是一个功能全面的轻量关系型数据库，支持SQL语法，Android系统中SQLiteDatabase类封装了操作数据库的API。

在Android中使用SQLite的步骤：
1.创建数据库只需要继承SQLiteOpenHelper类，实现构造器，onCreate(),onUpgrad();
①构造器中需要四个参数（Context 、数据库的Name、游标工厂（可为null）、数据库版本号）；
②onCreate(SQLiteDatabase db),创建表；
③onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion),当数据库版本发生变化时，便会调用该方法。
2.对数据库进行增删改查操作
①拼写SQL语句，执行db.execSQL()进行数据操作,但是查询操作执行db.rawQuery();
②使用SQLiteDatabase的CRUD方法，insert()、query()、update()、delete()

public class DBHleper exptens SQLiteOpenHelper{
    public DBHleper(Context context){
        super(context,DBCongfig.DB_NAME,null,DBConfig.DB_VERSION);
    }

    public DBHelper(Context context,String name,SQLiteDatabase.CursorFactory factory){
        super(context,name,factory,version);
    }

    public void onCreate(SQLiteDatabase db){
        db.execSQL(DBConfig.CREATE_TAB_MAMNER);
    }

    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
        if(oldVersion == newVersion) return;
        db.execSQL(DBConfig.DELETE_TAB_NAME);
        onCreate(db);
    }
}

public class BaseDB{
    private DBHelper dbHelper;
    private SQLiteDatabase db;
    public BaseDB(Context context){
        dbHelper = new DBHelper(context);
        db = dbHelper.getWritsbleDatabase();
    }

    public void cloaseDB(){
        if(db.isOpen()){
            db.close();
        }
    }

    public void openDB(){
        if(db != null && db.isOpen()) return;
        db = dbHelper.getReadableDatabase();
    }
}

public class MemberDBService extends BaseDB{
    public MemberDBService(Context context){
        super(context);
    }

    public long insertMemberData(MemberModel memberModel){
        ContentValues contextValues = new ContentValues();
        contentValues.put(DBConfig.MEMBER_ID,memberModel.getMemberID());
        contentValues.put(DBConfing.MEMBER_NAME,memberModel.getMemberName());
        return db.insert(DBConfig.TAB_MEMBER_NAME,null,contentValues);
    }

    public void inserMemberListData(List<MemberModel> memberModelList){
        db.beginTransaction();
        try{
            int size = memberModelList.size();
            for(int i= 0;i  < size;i++){
                insertMemberData(memberList.get(i));
            }
            db.setTransactionSuccessful();
        }catch(Exception e){
            e.printStackTrace();
        }finall{
            db.endTransaction();
        }

    }

    public void deleteMember(MemberModel memberModel){
        db.delete(DBConfig.TAB_MAMBER_NAME,DBConfig.MEMBER_ID+"=?",new String[]{memberModel.getMemberId()})
    }

    public void updateMember(MemberModel memberModel){
        ContentValues contentValues = new ContentValues();
        contentValues.put(DBConfig.MEMBER_NAME,memberModel.getMemberName());
db.update(DBConfig.TAB_MEMBER_NAME,contentValues,DBConfig.MEMBER_ID+"=?",new String[]{memberModel.getMemberId()})
    }

    public List<MemberModel> getMemberModelList(){
        Cursor cursor = db.query(DBconfig.TAB_MEMBAE_NAME,null,null,null,null,null,null);
        ArrayList<MemberModel> memberList  = new ArrayList<MemberMode>();
        while（cursor.moveToNext）{
            MemberModel memberModel = new MemberModel();
            memberModel.setMemberId(cursor.getString(DBConfig.MEMBER_ID_INDEX));
            memberModel.setMemberName(cursor.getString(DBConfig.MEMBER_NAME));
            memberList.add(memberModel);
        }
        curcor.close();
        return memberList;                      
    }
}

public class DBConfig{
    public static final String DB_NAME = "test.db";
    public static final int DB_VERSION = 1; 

    public static final String TAB_MEMBAE_NAME="member";
    public static final String CREATE_TAB_MEMBER = "create tab member if not exist(_id integer primary key autoincrement,memberId varchar(64),memberName varchar(64));";
    public static final String DELETE_TAB_MEMBER = "drop tab member;"; 
    public static final String MEMBER_ID = "memberId";
    public static final String MEMBER_NAME = "memberName";
    public static final int INDEX_ID = 0;
    public static final int INDEX_MEMBER_ID = 1;
    public static final int INDEX_MEMBER_NAME = 2;
}

使用：

MemberDBService memberDBService = new MemberDBService(context);
memberDBService.insert(new MemberModel());
memberDBService.closeDB();  

https://github.com/piratecode/DBTest