【Android學習專題】数据存储篇：SQLite
SkySeraph Feb 5th 2012 SZTCL
Email：zgzhaobo@gmail.com QQ：452728574
-----------------------------------------------------------------------------------------------------------------------------------------------------------
前一篇：【Android学习专题】数据存储篇：SharedPreferences/Files/SDCard
-----------------------------------------------------------------------------------------------------------------------------------------------------------
【SQLite】
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Ⅰ 测试操作
1 操作（gif）
本来录制了gif，但是博客园限制了大小，无法上传，就截图一个吧。。。
2 SQLite数据库保存地址
3 SQLite数据库查看（SQLiteSpy，该软件请参阅Refs-10）
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Ⅱ 操作
* 1 在AndroidManifest文件中加入sdcard操作权限
* <!--在SDCard中创建与删除文件权限 -->
* <uses-permissioandroid:name="android.permission.MOUNT_UNMOUNT_FILESYSTEMS"/>
* <!--往SDCard写入数据权限 -->
* <uses-permissionandroid:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
* 2 确认sdcard的存在
* android.os.Environment.getExternalStorageState().equals(android.os.Environment.MEDIA_MOUNTED)
* 3 获取扩展存储设备的文件目录
* android.os.Environment.getExternalStorageDirectory();
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Ⅲ 常用方法
Cursor游标方法
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Ⅳ 实例源码
1 java源码
1 public class sqlite extends Activity
2 {
3 // 按钮和编辑框对象
4 private Button mBtn1, mBtn2, mBtn3, mBtn4, mBtn5, mBtn6;
5 private EditText mEt1,mEt2,mEt3;
6 // 数据库对象
7 //SQLiteDatabase mDB = null;
8 MyDBAdapter mDBAdapter;
9
10 Context mContext = null;
11
12 // 添加记录时的id累加标记
13 int sqlite_id = 0;
14
15 @Override
16 protected void onCreate(Bundle savedInstanceState)
17 {
18 // TODO Auto-generated method stub
19 super.onCreate(savedInstanceState);
20 mContext = this;
21 setContentView(R.layout.mydatastorage_sqlite);
22
23 mEt1 = (EditText) findViewById(R.id.mydatastorage_sqlite_ET01);
24 mEt2 = (EditText) findViewById(R.id.mydatastorage_sqlite_ET02);
25 mEt3 = (EditText) findViewById(R.id.mydatastorage_sqlite_ET03);
26 mEt1.setInputType(InputType.TYPE_NULL);//禁止弹出键盘
27 mEt2.setInputType(InputType.TYPE_NULL);
28 mBtn1 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn01);
29 mBtn2 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn02);
30 mBtn3 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn03);
31 mBtn4 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn04);
32 mBtn5 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn05);
33 mBtn6 = (Button) findViewById(R.id.mydatastorage_sqlite_Btn06);
34
35 mBtn1.setOnClickListener(new ClickEvent());
36 mBtn2.setOnClickListener(new ClickEvent());
37 mBtn3.setOnClickListener(new ClickEvent());
38 mBtn4.setOnClickListener(new ClickEvent());
39 mBtn5.setOnClickListener(new ClickEvent());
40 mBtn6.setOnClickListener(new ClickEvent());
41 }
42
43 class ClickEvent implements View.OnClickListener
44 {
45 @Override
46 public void onClick(View v)
47 {
48 if(v == mBtn1) // 创建数据库
49 {
50 mDBAdapter = new MyDBAdapter(mContext);
51 mDBAdapter.open();
52 }
53 else if(v == mBtn2) // 关闭数据库
54 {
55 mDBAdapter.close();
56 }
57 else if(v == mBtn3) // 添加数据
58 {
59 mDBAdapter = new MyDBAdapter(mContext);
60 mDBAdapter.open();
61 try
62 {
63 int num = Integer.valueOf(mEt3.getText().toString()).intValue(); //获得EditText输入密码数据
64 Log.e("添加数据-密码", Integer.toString(num));
65 String data = mEt2.getText().toString(); //获得EditText输入文本
66 Log.e("添加数据-文本", data);
67 mDBAdapter.insertData(num, data);//插入
68 sqlite_id++;//记录id
69
70 } catch (Exception e)
71 {
72 // TODO: handle exception
73 }
74 }
75 else if(v == mBtn4) // 查询数据
76 {
77 try
78 {
79 int id = Integer.valueOf(mEt1.getText().toString()).intValue(); // 获得id
80 Log.e("查询数据-ID", Integer.toString(id));
81 Cursor cur = mDBAdapter.fetchData(id);
82 String data = cur.getString(2);
83 int num = Integer.valueOf(cur.getString(1)).intValue();
84 Log.e("查询数据-密码", Integer.toString(num));
85 Log.e("查询数据-文本", data);
86 mEt2.setText(data);
87 mEt3.setText(num);
88 } catch (Exception e)
89 {
90 // TODO: handle exception
91 }
92
93 }
94 else if(v == mBtn5) // 更新/修改数据
95 {
96 try
97 {
98 int id = Integer.parseInt(mEt1.getText().toString()); //获得id
99 int num = Integer.parseInt(mEt3.getText().toString()); //获得数据
100 String data = mEt2.getText().toString(); //获得文本
101 mDBAdapter.updateData(id, num, data);
102 } catch (Exception e)
103 {
104 // TODO: handle exception
105 }
106 }
107 else if(v == mBtn6) // 删除数据
108 {
109 try
110 {
111 int id = Integer.parseInt(mEt1.getText().toString()); //获得数据
112 mDBAdapter.deleteData(id);
113 sqlite_id--;
114 if (sqlite_id < 0)
115 {
116 sqlite_id = 0;
117 }
118 } catch (Exception e)
119 {
120 // TODO: handle exception
121 }
122
123 }
124 }
125 }
126 }
2 xml布局文件
1 <?xml version="1.0" encoding="utf-8"?>
2 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
3 android:layout_width="fill_parent"
4 android:layout_height="fill_parent"
5 android:orientation="vertical" >
6
7 <TextView
8 android:layout_width="fill_parent"
9 android:layout_height="wrap_content"
10 android:text="SQLite Test"
11 android:textSize="25dp" />
12
13 <LinearLayout
14 android:layout_width="fill_parent"
15 android:layout_height="wrap_content"
16 android:orientation="vertical" >
17
18 <Button
19 android:id="@+id/mydatastorage_sqlite_Btn01"
20 android:layout_width="fill_parent"
21 android:layout_height="wrap_content"
22 android:text="创建SQLite数据库" >
23 </Button>
24
25 <Button
26 android:id="@+id/mydatastorage_sqlite_Btn02"
27 android:layout_width="fill_parent"
28 android:layout_height="wrap_content"
29 android:text="关闭SQLite数据库" >
30 </Button>
31
32 <LinearLayout
33 android:layout_width="fill_parent"
34 android:layout_height="wrap_content" >
35
36 <Button
37 android:id="@+id/mydatastorage_sqlite_Btn03"
38 android:layout_width="wrap_content"
39 android:layout_height="wrap_content"
40 android:text="插入数据" >
41 </Button>
42
43 <Button
44 android:id="@+id/mydatastorage_sqlite_Btn04"
45 android:layout_width="wrap_content"
46 android:layout_height="wrap_content"
47 android:text="查询数据" >
48 </Button>
49
50 <Button
51 android:id="@+id/mydatastorage_sqlite_Btn05"
52 android:layout_width="wrap_content"
53 android:layout_height="wrap_content"
54 android:text="修改数据" >
55 </Button>
56
57 <Button
58 android:id="@+id/mydatastorage_sqlite_Btn06"
59 android:layout_width="wrap_content"
60 android:layout_height="wrap_content"
61 android:text="删除数据" >
62 </Button>
63 </LinearLayout>
64 </LinearLayout>
65
66 <LinearLayout
67 android:layout_width="fill_parent"
68 android:layout_height="wrap_content" >
69
70 <TextView
71 android:layout_width="wrap_content"
72 android:layout_height="wrap_content"
73 android:text="ID："
74 android:textSize="20dp" />
75
76 <EditText
77 android:id="@+id/mydatastorage_sqlite_ET01"
78 android:layout_width="fill_parent"
79 android:layout_height="wrap_content"
80 android:height="50px"
81 android:hint="请输入待查询/删除数据的ID" >
82 </EditText>
83 </LinearLayout>
84
85 <LinearLayout
86 android:layout_width="fill_parent"
87 android:layout_height="wrap_content" >
88
89 <TextView
90 android:layout_width="wrap_content"
91 android:layout_height="wrap_content"
92 android:text="姓名:"
93 android:textSize="20dp" />
94
95 <EditText
96 android:id="@+id/mydatastorage_sqlite_ET02"
97 android:layout_width="fill_parent"
98 android:layout_height="wrap_content"
99 android:height="50px"
100 android:hint="请输入姓名"
101 android:textStyle="bold"
102 android:singleLine="false" >
103 </EditText>
104 </LinearLayout>
105
106 <LinearLayout
107 android:layout_width="fill_parent"
108 android:layout_height="wrap_content" >
109
110 <TextView
111 android:layout_width="wrap_content"
112 android:layout_height="wrap_content"
113 android:text="密码:"
114 android:textSize="20dp" />
115
116 <EditText
117 android:id="@+id/mydatastorage_sqlite_ET03"
118 android:layout_width="fill_parent"
119 android:layout_height="wrap_content"
120 android:height="50px"
121 android:hint="请输入密码"
122 android:inputType="textPassword">
123 </EditText>
124 </LinearLayout>
125
126 </LinearLayout>
3 MyDBAdapter类
1 package com.skyseraph.android.project.mydatastorage;
2
3 import android.content.ContentValues;
4 import android.content.Context;
5 import android.database.Cursor;
6 import android.database.SQLException;
7 import android.database.sqlite.SQLiteDatabase;
8 import android.database.sqlite.SQLiteOpenHelper;
9
10 public class MyDBAdapter
11 {
12 // 用于打印log
13 private static final String MyDBAdapter_TAG = "MyDBAdapter";
14 // 表中一条数据的名称
15 public static final String MyDBAdapter_KEY_ID = "_id";
16 // 表中一条数据的内容
17 public static final String MyDBAdapter_KEY_NUM = "num";
18 // 表中一条数据的id
19 public static final String MyDBAdapter_KEY_DATA = "data";
20 // 数据库名称为data
21 private static final String MyDBAdapter_DB_NAME = "MyDB.db";
22 // 数据库表名
23 private static final String MyDBAdapter_DB_TABLE = "table1";
24 // 数据库版本
25 private static final int MyDBAdapter_DB_VERSION = 1;
26 // 本地Context对象
27 private Context mContext = null;
28 // 创建一个表
29 private static final String MyDBAdapter_DB_CREATE = "CREATE TABLE " + MyDBAdapter_DB_TABLE +
30 " (" + MyDBAdapter_KEY_ID + " INTEGER PRIMARY KEY,"
31 + MyDBAdapter_KEY_NUM + " INTERGER," + MyDBAdapter_KEY_DATA + " TEXT)";
32 // 执行open（）打开数据库时，保存返回的数据库对象
33 private SQLiteDatabase mSQLiteDatabase = null;
34 // 由SQLiteOpenHelper继承过来
35 private DatabaseHelper mDatabaseHelper = null;
36
37 private static class DatabaseHelper extends SQLiteOpenHelper
38 {
39 /* 构造函数-创建一个数据库 */
40 DatabaseHelper(Context context)
41 {
42 // 当调用getWritableDatabase() 或 getReadableDatabase()方法时 则创建一个数据库
43 super(context, MyDBAdapter_DB_NAME, null, MyDBAdapter_DB_VERSION);
44 }
45
46 /* 创建一个表 */
47 @Override
48 public void onCreate(SQLiteDatabase db)
49 {
50 // 数据库没有表时创建一个
51 db.execSQL(MyDBAdapter_DB_CREATE);
52 }
53
54 /* 升级数据库 */
55 @Override
56 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
57 {
58 db.execSQL("DROP TABLE IF EXISTS notes");
59 onCreate(db);
60 }
61 }
62
63 /* 构造函数-取得Context */
64 public MyDBAdapter(Context context)
65 {
66 mContext = context;
67 }
68
69
70 // 打开数据库，返回数据库对象
71 public void open() throws SQLException
72 {
73 mDatabaseHelper = new DatabaseHelper(mContext);
74 mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
75 }
76
77
78 // 关闭数据库
79 public void close()
80 {
81 mDatabaseHelper.close();
82 }
83
84 /* 插入一条数据 */
85 public long insertData(int num, String data)
86 {
87 ContentValues initialValues = new ContentValues();
88 initialValues.put(MyDBAdapter_KEY_NUM, num);
89 initialValues.put(MyDBAdapter_KEY_DATA, data);
90
91 return mSQLiteDatabase.insert(MyDBAdapter_DB_TABLE, MyDBAdapter_KEY_ID, initialValues);
92 }
93
94 /* 删除一条数据 */
95 public boolean deleteData(long rowId)
96 {
97 return mSQLiteDatabase.delete(MyDBAdapter_DB_TABLE, MyDBAdapter_KEY_ID + "=" + rowId, null) > 0;
98 }
99
100 /* 通过Cursor查询所有数据 */
101 public Cursor fetchAllData()
102 {
103 return mSQLiteDatabase.query(MyDBAdapter_DB_TABLE, new String[] { MyDBAdapter_KEY_ID,
104 MyDBAdapter_KEY_NUM, MyDBAdapter_KEY_DATA }, null, null, null, null, null);
105 }
106
107 /* 查询指定数据 */
108 public Cursor fetchData(long rowId) throws SQLException
109 {
110
111 Cursor mCursor =
112
113 mSQLiteDatabase.query(true, MyDBAdapter_DB_TABLE, new String[] { MyDBAdapter_KEY_ID,
114 MyDBAdapter_KEY_NUM, MyDBAdapter_KEY_DATA }, MyDBAdapter_KEY_ID + "=" + rowId,
115 null, null, null, null, null);
116 /*参数含义：#、表名称、列名称数组、条件子句、条件子句、分组列、分组条件、排序列、分页查询限制*/
117 if (mCursor != null)
118 {
119 mCursor.moveToFirst();//移到第一条记录
120 }
121 return mCursor;
122
123 }
124
125 /* 更新一条数据 */
126 public boolean updateData(long rowId, int num, String data)
127 {
128 ContentValues args = new ContentValues();
129 args.put(MyDBAdapter_KEY_NUM, num);
130 args.put(MyDBAdapter_KEY_DATA, data);
131
132 return mSQLiteDatabase.update(MyDBAdapter_DB_TABLE, args,
133 MyDBAdapter_KEY_ID + "=" + rowId, null) > 0;
134 }
135 }
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Refs：
1 http://blog.csdn.net/jiahui524
2 http://www.cnblogs.com/linjiqin/archive/2011/05/26/2059182.html
3 http://www.cnblogs.com/linjiqin/archive/2011/05/27/2059937.html
4 http://blog.csdn.net/hellogv/article/details/6019301
5 http://blog.csdn.net/hellogv/article/details/6011934
6 http://blog.csdn.net/like7xiaoben/article/details/7089887
7 http://blog.csdn.net/like7xiaoben/article/details/7098775
8 http://www.sqlite.org/
9 http://blog.csdn.net/xys289187120/article/details/6661099
10 http://download.csdn.net/download/xys289187120/3481140
-----------------------------------------------------------------------------------------------------------------------------------------------------------
