Android学习日记（yzy）：SQLite数据库和baseAdapter

SQLite的增删功能实现并显示在ListView中。

首先，创建一个类继承SQLiteOpenHelper并覆盖最基本的onCreate（）和onUpgrade（）方法。onCreate中使用sql语句来创建表。

public class MySQliteHelper extends SQLiteOpenHelper {

    public MySQliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,int version){
        super(context,name,factory,version);
    }
    public void onCreate(SQLiteDatabase db){
        db.execSQL("CREATE  TABLE user( _id INTEGER PRIMARY KEY , name VARCHAR , password VARCHAR)");
    }

    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){

    }
}

Activity中创建继承类和SQLiteDatabase类,将数据库设置为读写模式（getWritableDatabase（）），创建ContentValues类作为Column的载体，数据库增删方法insert和

delete。cursor是光标，用于选择具体的Column或者遍历。

public class MainActivity extends Activity {

    static final private String TAG = "MainActivity";

    private EditText mEtId;
    private EditText mEtUser;
    private EditText mEtPassword;
    private ListView listView;
    MySQliteHelper sQliteHelper = null;
    SQLiteDatabase db = null;
    MyAdapter myAdapter = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        init();
    }

    private void init(){
        mEtId =  (EditText)findViewById(R.id.ET_id);
        mEtUser =  (EditText)findViewById(R.id.ET_user_name);
        mEtPassword = (EditText)findViewById(R.id.ET_password);
        listView = (ListView)findViewById(R.id.list_view);

    }

    public void onClickCreate(View view){
        sQliteHelper = new MySQliteHelper(this,"test.db",null,1);
        db = sQliteHelper.getWritableDatabase();

        ContentValues cv = new ContentValues();
        for(int i = 0;i<3;i++){
            cv.put("name","???");
            cv.put("password","??????");
            db.insert("user",null,cv);
        }
        updateListView();
    }

    public void onClickAdd(View view){
        ContentValues cv = new ContentValues();
        cv.put("name",mEtUser.getText().toString());
        cv.put("password",mEtPassword.getText().toString());
        db.insert("user",null,cv);
        updateListView();
    }

    public void onClickDelete(View view){
        db.delete("user","_id = "+"'"+mEtId.getText().toString()+"'",null);

        updateListView();
    }

    public void onClickFresh(View view){
        updateListView();
    }

    private void updateListView(){
        if (sQliteHelper == null ){
            sQliteHelper = new MySQliteHelper(this,"test.db",null,1);
            db = sQliteHelper.getWritableDatabase();
        }
        myAdapter = new MyAdapter(this,db);
//        final Cursor cursor = db.query("user",null,null,null,null,null,null);
//        String[] ColumnNames = cursor.getColumnNames();

//        ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.listview_item,
//                cursor, ColumnNames, new int[] { R.id.TV_id, R.id.TV_user_name, R.id.TV_password });

        listView.setAdapter(myAdapter);
        ReturnValue();

    }

    private void ReturnValue(){
        mEtId.setText("");
        mEtUser.setText("");
        mEtPassword.setText("");
    }

最后给两个显示在listView的方法，最简单的当然是使用自带的SimpleCursorAdapter适配器，或者继承baseAdapter类。

public class MyAdapter extends BaseAdapter {
    Context mContext;
    SQLiteDatabase db = null ;

    public MyAdapter(Context context,SQLiteDatabase db){
        super();
        this.mContext = context;

        this.db = db;
    }

    public int getCount(){
        Cursor cursor = db.query("user",null,null,null,null,null,null);
        return cursor.getCount();
    }

    public Object getItem(int position){
        return position;
    }

    public long getItemId(int position) {
        return position;
    }

    public View getView(int position , View contentView , ViewGroup parents){

        HolderView holderView = null;

        if(contentView == null ){
            holderView = new HolderView();
            contentView = LayoutInflater.from(mContext).inflate(R.layout.listview_item,null);

            holderView.mTvId = (TextView)contentView.findViewById(R.id.TV_id);
            holderView.mTvName = (TextView)contentView.findViewById(R.id.TV_user_name);
            holderView.mTvPassword = (TextView)contentView.findViewById(R.id.TV_password);

            contentView.setTag(holderView);
        }else {
            holderView = (HolderView) contentView.getTag();
        }

        Cursor cursor = db.query("user",null,null,null,null,null,null);
//        cursor.move(position-1);
        if(cursor.move(position+1)) {
            int _idColumnIndex = cursor.getColumnIndex("_id");
            int nameColumnIndex = cursor.getColumnIndex("name");
            int passwordColumnIndex = cursor.getColumnIndex("password");


        holderView.mTvId.setText(cursor.getString(_idColumnIndex));
        holderView.mTvName.setText(cursor.getString(nameColumnIndex));
        holderView.mTvPassword.setText(cursor.getString(passwordColumnIndex));
        }
        return contentView;
    }

    static class HolderView{
        public TextView mTvId;
        public TextView mTvName;
        public TextView mTvPassword;
    }
}

end

补充1：判断数据库是否存在方法，返回boolean。

private boolean IsTableExist() {
    
    SQLiteDatabase db=openOrCreateDatabase(DATABASE_NAME, 0, null);
    Cursor c=db.rawQuery("select count(*) from sqlite_master WHERE type='table' and name= 'user'", null);
    if (c.moveToFirst()){
        if (c.getInt(0)==0) {
            return false
        }
    }

    return true;
}

删除部分和所有数据库的实现：

private void clearDatabaseBydbName(String dbName) {
    deleteDatabase(dbName);
}

private void clearALLDataBases(){
    deleteFilesByDirectory(new File("data/data/"+getPackageName()+"/databases"));
}

private static void deleteFilesByDirectory(File directory) {
    if (directory != null && directory.exists() && directory.isDirectory()) {
        for (File item : directory.listFiles()) {
            item.delete();
        }
    }
}

可利用adb shell查看是否实现

adb devices， 查看adb连接的设备

adb root， root设备

adb shell ，

cd data/data/包名/databases ，进入相关数据库的存放位置

sqlite3 databasename ,到这里基本上就可以通过SQL命令来查看数据了。

补充一个最近公司项目里的的帮助类实现：

public class DBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = Config.DB_NAME;
    private static final int VERSION = Config.DB_VERSION;

    private Context context;
    private SQLiteDatabase database;

    public DBService(Context context) {
        super(context, DB_NAME, null, VERSION);
        this.context = context;
    }

    private synchronized SQLiteDatabase openDatabase() {
        if (null == database) {
            database = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
        }
        return database;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        database = db;
    }

    public synchronized void doExecSQL(String sql) throws Exception {
        SQLiteDatabase db = null;
        db = openDatabase();
        db.execSQL(sql);
    }

    public synchronized long insert(String table, String nullColumnHack, ContentValues values) {
        long result = 0L;
        SQLiteDatabase db = null;
        try {
            db = openDatabase();
            result = db.insert(table, nullColumnHack, values);
        } catch (Exception e) {
            e.printStackTrace();
        } 
        return result;
    }

    public synchronized int delete(String table, String whereClause, String[] values) {
        int result = 0;
        SQLiteDatabase db = null;
        try {
            db = openDatabase();
            result = db.delete(table, whereClause, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public synchronized int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
        int result = 0;
        SQLiteDatabase db = null;
        try {
            db = openDatabase();
            result = db.update(table, values, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        }
    
        return result;
    }

    public synchronized void doExecSQLs(String[] sql) {
        SQLiteDatabase db = openDatabase();
        // 开启事务
        db.beginTransaction();
        try {
            for (String s : sql) {
                if (!TextUtils.isEmpty(s)) {
                    db.execSQL(s);
                }
            }
     
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

 
    public synchronized Cursor query(String sql, String[] selectionArgs) {
        SQLiteDatabase db = null;
        try {
            db = openDatabase();
            return db.rawQuery(sql, selectionArgs);
        } catch (Exception e) {
            e.printStackTrace();

        } 
        return null;
    }

    public HashMap<String, String> query(String[] sql) {
        HashMap<String, String> list = new HashMap<>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = openDatabase();
            for (int i = 0; i < sql.length; i++) {
                cursor = db.rawQuery(sql[i], null);
                int count = cursor.getCount();
                list.put(i + "", count + "");
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<HashMap<String, String>> query(String sql) {
        List<HashMap<String, String>> list = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = openDatabase();
            cursor = db.rawQuery(sql, null);
            cursor.moveToFirst();
            String[] columnNames = cursor.getColumnNames();
            while (!cursor.isAfterLast()) {
                HashMap<String, String> map = new HashMap<>();
                for (String columnName : columnNames) {
                    String curs = cursor.getString(cursor.getColumnIndex(columnName));
                    if (curs == null) {
                        curs = "";
                    }
                    map.put(columnName, curs);
                }
                list.add(map);
                cursor.moveToNext();
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
        }
        return list;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void closeDataBase() {
        if (null != database && database.isOpen()) {
            database.close();
        }
    }
}

end