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