在Android中創建和打開數據庫要使用openOrCreateDatabase()方法來實現,返回壹個
SQLiteDatabase對象
mSQLiteDatabase = this.openOrCreateDatabase("Examples_08_10",MODE_PRIVATE,null);
2、創建表
使用execSQL方法創建表
String CREATE_TABLE = "CREATE_TABLE table1(_id INTEGER PRIMARY KEY,num INTERGER,data TEXT)";
mSQLitedatabase.execSQL(CREATE_TABLE);
3、向表中添加壹條數據
可以使用insert方法來添加數據,但是insert方法要求把數據都打包到ContentValues中,ContentValues
其實就是壹個Map,Key值是字段名稱,Value值是字段的值,通過ContentValues的put方法就可以把數據
放到ContentValues對象中,然後插入到表中
ContentValues cv = new ContentValues();
cv.put(TABLE_NUMBER,1);
cv.put(TABLE_DATA,"測試數據庫數據");
mSQLiteDatabase.insert(TABLE_NAME,null,cv);
這裏也可以使用execSQL方法來執行壹條插入的SQL語句
String INSERT_DATA = "INSERT INTO table1(_id,num,data)values(1,1,'通過SQL語句插入')";
mSQLiteDatabase.execSQL(INSERT_DATA);
4、從表中刪除數據
mSQLiteDatabase.delete("Examples_08_10.db","WHERE _id" + 0,null);
String DELETE_DATA = "DELETE FROM table1 WHERE_id=1";
mSQLiteDatabase.execSQL(DELETE_DATA);
5、修改表中的數據
ContentValues cv = new ContentValues();
cv.put(TABLE_NUMBER,1);
cv.put(TABLE_DATA,"修改後的數據");
mSQLiteDatabase.execSQL("table1",cv,"num" + "=" + Integer.toString(0),null);
6、關閉數據庫
mSQLiteDatabase.close();
7、刪除指定表
mSQliteDatabase.execSQl("DROP TABLE table1");
8、刪除數據庫
this.deleteDatabase("Examples_08_10.db");
9、查詢表中的某條數據
在Android中查詢數據是通過Cursor類來實現的,當我們使用SQliteDatabase.query()方法時
會返回壹個Cursor對象,Cursor指向的是每壹條數據,它提供了很多有關查詢的方法,具體方法
Cursor cur = mSQLiteDatabase.rawQuery("SELECT * FROM table",null);
if(cur !=null){
if(cur.moveToFirst()){
do{
int numColumn =cur.getColumnIndex("num");
int num = cur.getInt(numColumn);
}while(cur.moveToNext());
}
}
public class Activity01 extends Activity{
private static int mCount = 0;
//數據庫對象
private SQLiteDatabase mSQLiteDatabase = null;
//數據庫名
private final static String DATABASE_NAME = "Examples_08_11.db"
//表名
private final static String TABLE_ID = "_id";
private final static String TABLE_NUM = "num";
private final static String TABLE_DATA = "data";
//創建表的SQL語句
private final static String CREATE_TABLE = "CREATE TABLE"+TABLE_NAME + "("
+ TABLE_ID + "INTEGER PRIMARY KEY,"+TABLE_NUM + "INTERGER,"+TABLE_DATA + "
TEXT)";
//線性布局
LinearLayout m_LinearLayout = null;
//列表視圖-顯示數據庫的數據
ListView m_ListView = null;
public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState){
m_LinearLayout = new LinearLayout(this);
m_LinearLayout.setOrientation(LinearLayout.VERTICAL);
m_LinearLayout.setBackgroundColor(android.graphics.Color.BLACK);
//創建listview對象
m_ListView = new ListView(this);
LinearLayout.LayoutParams param = new LinearLayout.LayoutParams(LinearLayout.LayoutParams.FILL_PARENT,
LinearLayout.Params.WRAP_CONTENT);
m_ListView.setBackgroundColor(Color.BLACK);
//添加m_ListView到m_LinearLayout中
m_LinearLayout.addView(m_ListView,param);
//設置顯示m_LinearLayout的布局
setContentView(m_LinearLayout);
//打開已經存在的數據庫
mSQLiteDatabase = this.openOrCreateDatabase(DATABASE_NAME,MODE_PRIVATE,null);
//獲取數據庫Phones中的Cursor
try{
//在數據庫中創建壹個表
mSQLiteDatabase.execSQL(CREATE_TABLE);
}
catch(Exception e){
eUpdateAdapter();
}
}
public boolean onKeyUp(int keyCode,KeyEvent event){
switch(keyCode){
case KeyEvent.KEYCODE_DPAD_LEFT:
AddData();
break;
case KeyEvent.KEYCODE_DAPD_RIGHT:
DeleteData();
break;
case KeyEvent.KEYCODE_DAPD_1:
UpData();
break;
case KeyEvent.KEYCODE_DAPD_2:
DeleteTable();
break;
case KeyEvent.KEYCODE_DAPD_3:
DeleteDataBase();
break;
}
}
return true;
}
//刪除數據庫
public void DeleteDataBase(){
this.deleteDatabase(DATABASE_NAME);
this.finish();
}
//刪除壹個表
public void DeleteTable(){
mSQLiteDatabase.execSQL("DROP TABLE" + TABLE_NAME);
this.finish();
}
//更新壹條數據
public void UpData(){
ContentValues cv = new ContentValues();
cv.put(TABLE_NUM,mCount);
cv.put(TABLE_DATA,"修改後的數據" + mCount);
//更新數據
mSQLiteDatabase.update(TABLE_NAME,cv,TABLE_NUM + "=" + Integer.toString(mCount -1),null);
UpdataAdapter();
}
public void AddData(){
ContentValues cv = new ContentValues();
cv.put(TABLE_NUM,mCount);
cv.put(TABLE_DATA,"測試數據庫數據" + mCount);
//插入數據
mSQLiteData.insert(TABLE_NAME,null,cv);
mCount++;
UpdataAdapter();
}
public void DeleteData(){
mSQLiteDatabase.execSQL("DELETE FROM" + TABLE_NAME + "WHERE _id=" +Integer.toString(mCount));
mCount--;
if(mCount<0){
mCount--;
}
UpdataAdapter();
}
//更新視圖顯示
public void UpdateAdapter(){
//獲取數據庫Phones的Cursor
Cursor cur = mSQLiteDatabase.query(TABLE_NAME,new String[]{TABLE_ID,TABLE_NUM,TABLE_DATA},null,null,null,null,null);
mCount = cur.getCount();
if(cur !=null && cur.getCount()>=0){
ListAdapter adapter = new SimpleCusorAdapter(this,android.R.layout.simple_list_item_2,cur,new String[]{TABLE_NUM,TABLE_DATA},new int[]{android.R.id.text1,android.R.id.text2});
m_ListView.setAdapter(adapter);
}
}
//按鍵事件處理
public boolean onKeyDown(int keyCode,KeyEvent event){
if(keyCode == KeyEvent.KEYCODE.KEYCODE_BACK){
//退出時不要忘記關閉
mSQLiteDatabase.close();
this.finish();
return true;
}
return super.onKeyDown(keyCode,event);
}
}
SQliteOpenHelper應用
前面我們已經學過了SQLite編程基礎,但是在實際開發中,為了更好的管理和維護數據庫,我們會封裝壹個繼承自SQLiteOpenHelper類的數據庫操作類。SQLiteOpenHelper的構造方法中分別需要傳入Context、數據庫名稱、CursorFactory(壹般傳入null,否則為默認數據庫)、
數據庫的版本號(不能為負數)同樣在SQLiteOpenHelper中首先執行的是onCreate方法(當數據庫第壹次被創建時)。當然,在構造函數時並沒有真正創建數據庫,而是調用getWritableDatabase或者getReadableDatabase方法時才真正去創建數據庫,並且返回壹個SQLiteDatabase
對象。因此,我們就可以輕松的修改上壹節了。
public class MyDataBaseAdapter{
//用於打印日誌
private static final String TAG = "MyDataAdapter";
//表中壹條數據的名稱
private static final String KEY_ID = "_id";
//表中壹條數據的內容
private static final String KEY_NUM = "num";
//表中壹條數據的id
private static final String KEY_DATA = "data";
//數據庫的名稱
private static final String DB_NAME = "Examples_8_11.db";
//數據庫表名
private static final String DB_TABLE = "table1";
//數據庫版本
private static final int DB_VERSION = 1;
//本地Context對象
private Context mContext = null;
private static final String DB_CREATE ="CREATE TABLE" + DB_TABLE + "(" +KEY_ID+"INTEGER PRIMARY KEY,"+KEY_NUM+"INTERGER,"+KEY_DATA+"TEXT)";
//執行open()打開數據庫時,保存返回的數據庫對象
private SQLiteDatabase mSQLiteDatabase = null;
//由SQLiteOpenHelper繼承過來
private DatabaseHelper mDatabaseHelper = null;
private static class DatabaseHelper extends SQLiteOpenHelper{
//構造函數創建壹個數據庫
DatabHelper(Context context){
//當調用getWritableDatabase()
//或getReadableDatabase()方法時
//則創建壹個數據庫
super(context,DB_NAME,null,DB_VERSION);
}
//創建壹個表
public void onCreate(SQLiteDatabase db){
//數據庫沒有表時創建壹個
db.execSQL(DB_CREATE);
}
//升級數據庫
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
db.execSQL("DROP TABLE IF EXISTS notes");
onCreate(db);
}
}
//構造函數
public MyDataBaseAdapter(Context mContext){
mContext = context;
}
//打開數據庫
public void open()throws SQLException{
mDatabaseHelper = new DatabaseHelper(mContext);
mSQLiteDatabase = mDatabasehelper.getWritableDatabase();
}
//關閉數據庫
public void close(){
mDatabaseHelper.close();
}
//插入壹條數據
public long insertData(int num,String data){
ContentValues initialvalues = new ContentValues();
initialValues.put(KEY_NUM,num);
initialValues.put(KEY_DATA,data);
return mSQLiteDatabase.insert(DB_TABLE,KEY_ID,initialValues);
}
//刪除壹條數據
public boolean deleteData(long rowId){
return mSQLiteDatabase.delete(DB_TABLE,KEY_ID+"="+rowId,null)>0;
}
//通過Cursor查詢所有數據
public Cursor fetchAllData(){
return mSQLiteDatabase.query(DB_TABLE,new String[]{KEY_ID,KEY_NUM,KEY_DATA},null,null,null,null,null);
}
//查詢指定數據
public Cursor fetchData(long rowId,null,null,null,null,null);
if(mCursor !=null){
mCursor.moveToFirst();
}
return mCursor;
}
//更新壹條數據
public boolean updateData(long rowId,int num,String data){
ContentValues args = new ContentValues();
args.put(KEY_NUM,num);
args.put(KEY_DATA,data);
return mSQLiteDatabase.update(DB_TABLE,args,KEY_ID+"="+rowId,null)>0;
}
}
如何使用MyDataBaseAdapter
public class Activity01 extends mCount = 0;
//線性布局
LinearLayout m_LinearLayout = null;
//列表視圖-顯示數據庫中的數據
ListView mListView = null;
MyDataBaseAdapter m_MyDataBaseAdapter;
public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
//創建線性布局對象
m_LinearLayout = new LinearLayout(this);
m_LinearLayout.setOrientation