Android應用開發之資料庫操作詳解

最新 07-15

安卓使用的資料庫是sqlite

創建方式：新建一個類繼承SQLiteOpenHelper

package org.dreamtech.sqlite;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class MyOpenHelper extends SQLiteOpenHelper { public MyOpenHelper(Context context) { super(context, "dreamtech.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } } 這裡第二個參數：資料庫的名字，第三個參數為結果集（游標）寫成null即可，最後一個參數為資料庫的版本，寫1即可在MainActivity中新建實例即可創建資料庫： package org.dreamtech.sqlite;import android.os.Bundle;import android.app.Activity;import android.database.sqlite.SQLiteDatabase;public class MainActivity extends Activity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); MyOpenHelper myOpenHelper = new MyOpenHelper(getApplicationContext()); // 獲取一個可寫的資料庫，如果資料庫不存在，則新建資料庫 SQLiteDatabase sqLiteDatabase = myOpenHelper.getWritableDatabase(); } } 好的，運行即可創建資料庫成功接下來看下MyOpenHelper中的方法：oncreate：資料庫第一次創建時候執行的方法適合於初始化表結構示例： @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table info(_id integer primary key autoincrement,name varchar(20))"); } 另一個方法：onupgrade方法：當資料庫版本升級的時候調用（上邊設置為1版本，如果升級成2，會運行這裡的方法）示例：這裡可以修改表結構 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("alter table info add tel varchar(20)"); } 注意：版本只能升，不能降級接下來做一個增刪改查的小案例：使用原始方法： package org.dreamtech.sqlite;import android.os.Bundle;import android.view.View;import android.app.Activity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class MainActivity extends Activity { private MyOpenHelper myOpenHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); myOpenHelper = new MyOpenHelper(getApplicationContext()); } // 增 public void click1(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); db.execSQL("insert into info(name,tel) values(?,?)", new Object[] { "zhangsan", "110" }); db.close(); } // 刪 public void click2(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); db.execSQL("delete from info where name=?", new Object[] { "zhangsan" }); db.close(); } // 改 public void click3(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); db.execSQL("update info set tel=? where name=?", new Object[] { "120", "zhangsan" }); db.close(); } // 查 public void click4(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from info", null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { String name = cursor.getString(1); String tel = cursor.getString(2); System.out.println(name + " " + tel); } } db.close(); }} 確實成功了，但是存在問題：sql語句只要一處出錯，將難以發現，浪費大量時間唯一的優點：多表查詢通常操作資料庫是採用谷歌封裝好的API：優點：不易寫錯，有返回值，方便開發缺點：涉及多表操作不便 package org.dreamtech.sqlite;import android.os.Bundle;import android.view.View;import android.widget.Toast;import android.app.Activity;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class MainActivity extends Activity { private MyOpenHelper myOpenHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); myOpenHelper = new MyOpenHelper(getApplicationContext()); } // 增 public void click1(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("name", "zhangsan"); values.put("tel", "110"); // 返回新行的id long insert = db.insert("info", null, values); db.close(); if (insert > 0) { Toast.makeText(getApplicationContext(), "add 成功", Toast.LENGTH_LONG) .show(); } else { Toast.makeText(getApplicationContext(), "add 失敗", Toast.LENGTH_LONG) .show(); } } // 刪 public void click2(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); // 返回值：影響的行數 int delete = db.delete("info", "name=?", new String[] { "zhangsan" }); db.close(); Toast.makeText(getApplicationContext(), "刪除了" + delete + "行", Toast.LENGTH_LONG).show(); } // 改 public void click3(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("tel", "666"); // 返回值：更新了多少行 int update = db.update("info", values, "name=?", new String[] { "zhangsan" }); db.close(); Toast.makeText(getApplicationContext(), "更新了" + update + "行", Toast.LENGTH_LONG).show(); } // 查 public void click4(View v) { SQLiteDatabase db = myOpenHelper.getWritableDatabase(); Cursor cursor = db.query("info", new String[] { "tel" }, "name=?", new String[] { "zhangsan" }, null, null, null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { String tel = cursor.getString(0); System.out.println(tel); } } db.close(); }} 事務操作：執行一段邏輯，要麼同時成功，要麼同時失敗這裡寫一個邏輯轉賬的案例 package org.dreamtech.transaction;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class MyOpenHelper extends SQLiteOpenHelper { public MyOpenHelper(Context context) { super(context, "Account.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table info(_id integer primary key autoincrement,name varchar(20),money varchar(20))"); db.execSQL("insert into info("name","money") values("a","2000")"); db.execSQL("insert into info("name","money") values("b","5000")"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } } package org.dreamtech.transaction;import android.os.Bundle;import android.app.Activity;import android.database.sqlite.SQLiteDatabase;import android.view.Menu;import android.view.View;import android.widget.Toast;public class MainActivity extends Activity { private MyOpenHelper myOpenHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); myOpenHelper = new MyOpenHelper(getApplicationContext()); } // 使用事務進行轉賬 public void click(View v) { SQLiteDatabase db = myOpenHelper.getReadableDatabase(); // 開啟事務 db.beginTransaction(); try { // 轉賬邏輯 db.execSQL("update info set money=money-100 where name=?", new Object[] { "a" }); // 加入這一行則不會成功 // int a = 10/0; db.execSQL("update info set money=money+100 where name=?", new Object[] { "b" }); // 表示當前事務成功（自動提交） db.setTransactionSuccessful(); } catch (Exception e) { Toast.makeText(getApplicationContext(), "轉賬失敗", Toast.LENGTH_LONG) .show(); } finally { // 關閉事務 db.endTransaction(); } }}

加入10/0一行的效果：

本文由職坐標整理並發布，希望對同學們有所幫助。了解更多詳情請關注職坐標移動開發之Android頻道！

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！

請您繼續閱讀更多來自 職坐標在線 的精彩文章:

TAG:職坐標在線 |