Android之SQLite3命令行管理數據庫

SQLite適合移動設備的數據存儲,有處理速度快,占用資源少等優點,不需要安裝部署,內嵌到程序中作為其一部分.http://www.sqlite.org/

SQLite的數據庫文件位於/data/data/your-app-name/databases目錄下.

使用數據庫最基本就是增刪改查操作.下面的示例是使用SQLite進行增刪改查的操作.

我們需要一個輔助類繼承SQLiteOpenHelper類

1. package com.sumq;
3. import Android.content.Context;
4. import android.database.sqlite.SQLiteDatabase;
5. import android.database.sqlite.SQLiteDatabase.CursorFactory;
6. import android.database.sqlite.SQLiteOpenHelper;
8. public class MySQLiteHelper extends SQLiteOpenHelper{
10. public MySQLiteHelper(Context context, String name, CursorFactory factory,
11. int version) {
12. super(context, name, factory, version);
13. // TODO Auto-generated constructor stub
14. }
16. /*
17. *當數據庫被創建時,首次執行該方法
18. *一般將創建表等初始化操作放在該方法中執行
19. */
20. @Override
21. public void onCreate(SQLiteDatabase db) {
22. // TODO Auto-generated method stub
23. db.execSQL("create table if not exists user_info(id integer primary key,name varchar,vip integer)");
24. }
26. /*當版本升級時調用該函數
27. *參數一為要更新的數據庫
28. *參數二傳入老的版本號
29. *參數三傳入新的版本號
30. */
31. @Override
32. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
33. // TODO Auto-generated method stub
35. }
37. /*
38. *看需要,該方法在每次打開數據庫時被調用
39. */
40. @Override
41. public void onOpen(SQLiteDatabase db) {
42. // TODO Auto-generated method stub
43. super.onOpen(db);
44. }
45. }

1. package com.sumq;
3. import android.app.Activity;
4. import android.content.ContentValues;
5. import android.database.Cursor;
6. import android.database.sqlite.SQLiteDatabase;
7. import android.os.Bundle;
8. import android.widget.TextView;
10. public class UISQLiteActivity extends Activity {
12. TextView tv;
13. MySQLiteHelper mySQLiteHelper;
15. /** Called when the activity is first created. */
16. @Override
17. public void onCreate(Bundle savedInstanceState) {
18. super.onCreate(savedInstanceState);
19. setContentView(R.layout.main);
20. tv = (TextView)findViewById(R.id.tv);
21. mySQLiteHelper = new MySQLiteHelper(this, "test.db", null, 1);
22. insertAndUpdateData(mySQLiteHelper);
23. String result = queryData(mySQLiteHelper);
24. tv.setText("名字\t等級\t"+result);
25. }
27. private void insertAndUpdateData(MySQLiteHelper mySQLiteHelper){
28. //獲取數據庫對象
29. SQLiteDatabase db = mySQLiteHelper.getWritableDatabase();
30. //第一種插入數據庫的方法直接寫sql語句
31. db.execSQL("insert into user_info(name,vip) values('user1',1)");
32. //第二種調用insert方法
33. ContentValues values = new ContentValues();
34. values.put("name", "user2");
35. values.put("vip", "2");
36. db.insert("user_info", null, values);
37. //更新level=2 的數據
38. values.clear();
39. values.put("name", "user2");
40. values.put("vip", "3");
41. db.update("user_info", values, "vip = ?", new String[]{"2"});
43. db.close();
44. }
46. private String queryData(MySQLiteHelper mySQLiteHelper){
47. String result ="";
48. SQLiteDatabase db = mySQLiteHelper.getReadableDatabase();
49. Cursor cursor = db.query("user_info", null, null, null, null, null, "id asc");
50. while (cursor.moveToNext()) {
51. result = result+cursor.getString(cursor.getColumnIndex("name"))+" ";
52. result = result+cursor.getInt(cursor.getColumnIndex("vip"))+" \n";
53. }
54. cursor.close();
55. db.close();
56. return result;
57. }
59. @Override
60. protected void onDestroy() {
61. SQLiteDatabase db = mySQLiteHelper.getWritableDatabase();
62. db.delete("user_info", "1", null);
63. super.onDestroy();
64. }
65. }

執行程序結果顯示