在 Android 中使用 SQLite, ContentProvider

在 Android 中使用 SQLite, ContentProvider    
数据库支持(SQLite) - Android 开发平台提供了操作 SQLite 数据库的相关 API   
内容提供器(ContentProvider) - 当数据需要在应用程序之间共享时，可以在某程序中使用 ContentProvider 定义 URI， 以使其它应用程序可以通过此 URI 访问指定的数据   
 
 
1、SQLite 的 Demo   
DatabaseHelper.java   
 
Java代码   
代码

 

18.package com.webabcd.SQLite; 19. 20.import android.content.Context; 21.import android.database.sqlite.SQLiteDatabase; 22.import android.database.sqlite.SQLiteOpenHelper; 23.import android.database.sqlite.SQLiteDatabase.CursorFactory; 24. 25.// 数据库操作的 Helper 类 26.public class DatabaseHelper extends SQLiteOpenHelper { 27. 28. DatabaseHelper(Context context, String name, CursorFactory cursorFactory, int version) { 29. super(context, name, cursorFactory, version); 30. } 31. 32. @Override 33. public void onCreate(SQLiteDatabase db) { 34. // TODO 创建数据库后，对数据库的操作 35. } 36. 37. @Override 38. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 39. // TODO 更改数据库版本的操作 40. } 41. 42. @Override 43. public void onOpen(SQLiteDatabase db) { 44. super.onOpen(db); 45. 46. // TODO 每次成功打开数据库后首先被执行 47. } 48.} 49. 50. 51.Main.java 52. 53.代码 54.package com.webabcd.SQLite; 55. 56.import java.util.Random; 57. 58.import android.app.Activity; 59.import android.content.ContentValues; 60.import android.database.Cursor; 61.import android.database.SQLException; 62.import android.database.sqlite.SQLiteDatabase; 63.import android.os.Bundle; 64.import android.view.View; 65.import android.widget.Button; 66.import android.widget.TextView; 67. 68.public class Main extends Activity { 69. 70. private DatabaseHelper dbHelper; 71. 72. private static final String DATABASE_NAME = "db.db"; 73. private static final int DATABASE_VERSION = 1; 74. private static final String TABLE_NAME = "employee"; 75. 76. TextView txtMsg; 77. 78. /** Called when the activity is first created. */ 79. @Override 80. public void onCreate(Bundle savedInstanceState) { 81. super.onCreate(savedInstanceState); 82. setContentView(R.layout.main); 83. 84. dbHelper = new DatabaseHelper(this, DATABASE_NAME, null, 85. DATABASE_VERSION); 86. 87. txtMsg = (TextView) this.findViewById(R.id.txtMsg); 88. 89. Button btn1 = (Button) this.findViewById(R.id.btn1); 90. btn1.setText("创建表"); 91. btn1.setOnClickListener(new Button.OnClickListener() { 92. public void onClick(View v) { 93. CreateTable(); 94. } 95. }); 96. 97. Button btn2 = (Button) this.findViewById(R.id.btn2); 98. btn2.setText("插入 3 条记录"); 99. btn2.setOnClickListener(new Button.OnClickListener() { 100. public void onClick(View v) { 101. insertItem(); 102. } 103. }); 104. 105. Button btn3 = (Button) this.findViewById(R.id.btn3); 106. btn3.setText("删除全部记录"); 107. btn3.setOnClickListener(new Button.OnClickListener() { 108. public void onClick(View v) { 109. deleteItem(); 110. } 111. }); 112. 113. Button btn4 = (Button) this.findViewById(R.id.btn4); 114. btn4.setText("更新指定数据"); 115. btn4.setOnClickListener(new Button.OnClickListener() { 116. public void onClick(View v) { 117. updateItem(); 118. } 119. }); 120. 121. Button btn5 = (Button) this.findViewById(R.id.btn5); 122. btn5.setText("显示全部数据"); 123. btn5.setOnClickListener(new Button.OnClickListener() { 124. public void onClick(View v) { 125. showItems(); 126. } 127. }); 128. 129. Button btn6 = (Button) this.findViewById(R.id.btn6); 130. btn6.setText("删除表"); 131. btn6.setOnClickListener(new Button.OnClickListener() { 132. public void onClick(View v) { 133. dropTable(); 134. } 135. }); 136. } 137. 138. // 创建数据表 139. private void CreateTable() { 140. SQLiteDatabase db = dbHelper.getWritableDatabase(); 141. String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME 142. + " (ID INTEGER PRIMARY KEY, Name VARCHAR, Age INTEGER);"; 143. try { 144. db.execSQL(sql); 145. txtMsg.append("数据表成功创建/n"); 146. } catch (SQLException ex) { 147. txtMsg.append("数据表创建错误/n" + ex.toString() + "/n"); 148. } 149. } 150. 151. // 插入数据 152. private void insertItem() { 153. SQLiteDatabase db = dbHelper.getWritableDatabase(); 154. 155. try { 156. Random random = new Random(); 157. for (int i = 0; i < 3; i++) { 158. String sql = "insert into " + TABLE_NAME 159. + " (name, age) values ('name" + String.valueOf(i) 160. + "', " + random.nextInt() + ")"; 161. // execSQL() - 执行指定的 sql 162. db.execSQL(sql); 163. } 164. txtMsg.append("成功插入 3 条数据/n"); 165. } catch (SQLException ex) { 166. txtMsg.append("插入数据失败/n" + ex.toString() + "/n"); 167. } 168. } 169. 170. // 删除数据 171. private void deleteItem() { 172. try { 173. SQLiteDatabase db = dbHelper.getWritableDatabase(); 174. db.delete(TABLE_NAME, " id < 999999", null); 175. txtMsg.append("成功删除数据/n"); 176. } catch (SQLException e) { 177. txtMsg.append("删除数据失败/n"); 178. } 179. } 180. 181. // 更新数据 182. private void updateItem() { 183. SQLiteDatabase db = dbHelper.getWritableDatabase(); 184. 185. try { 186. ContentValues values = new ContentValues(); 187. values.put("name", "批量更新后的名字"); 188. 189. db.update(TABLE_NAME, values, "id<?", new String[] { "3" }); 190. txtMsg.append("成功更新数据/n"); 191. } catch (SQLException e) { 192. txtMsg.append("更新数据失败/n"); 193. } 194. } 195. 196. // 查询数据 197. private void showItems() { 198. SQLiteDatabase db = dbHelper.getReadableDatabase(); 199. 200. try { 201. String[] column = { "id", "name", "age" }; 202. Cursor cursor = db.query(TABLE_NAME, column, null, null, null, 203. null, null); 204. Integer num = cursor.getCount(); 205. txtMsg.append("共 " + Integer.toString(num) + " 条记录/n"); 206. cursor.moveToFirst(); 207. 208. while (cursor.getPosition() != cursor.getCount()) { 209. txtMsg.append(Integer.toString(cursor.getPosition()) + "," 210. + String.valueOf(cursor.getString(0)) + "," 211. + cursor.getString(1) + "," 212. + String.valueOf(cursor.getString(2)) + "/n"); 213. cursor.moveToNext(); 214. } 215. } catch (SQLException ex) { 216. txtMsg.append("读取数据失败/n" + ex.toString() + "/n"); 217. } 218. } 219. 220. // 删除数据表 221. private void dropTable() { 222. SQLiteDatabase db = dbHelper.getWritableDatabase(); 223. String sql = "DROP TABLE IF EXISTS " + TABLE_NAME; 224. try { 225. db.execSQL(sql); 226. txtMsg.append("数据表删除成功/n"); 227. } catch (SQLException ex) { 228. txtMsg.append("数据表删除错误/n" + ex.toString() + "/n"); 229. } 230. } 231.}

 

2、ContentProvider 的 Demo     
MyUser.java      

代码

 

238.package com.webabcd.contentprovider; 239. 240.import android.net.Uri; 241.import android.provider.BaseColumns; 242. 243.// 自定义 ContentProvider 所需的实体类 244.public class MyUser { 245. 246. // 必须要有 _id 字段。本例中 BaseColumn 类中已经包含了 _id 字段 247. public static final class User implements BaseColumns { 248. 249. // 定义 CONTENT_URI 250. public static final Uri CONTENT_URI = Uri.parse("content://com.webabcd.MyContentProvider"); 251. 252. // 表数据列 253. public static final String USER_NAME = "USER_NAME"; 254. } 255.} 256. 257. 258.MyContentProvider.java 259. 260.代码 261.package com.webabcd.contentprovider; 262. 263.import java.io.File; 264.import java.io.FileInputStream; 265.import java.io.FileOutputStream; 266. 267.import org.apache.http.util.EncodingUtils; 268. 269.import android.content.ContentProvider; 270.import android.content.ContentUris; 271.import android.content.ContentValues; 272.import android.database.Cursor; 273.import android.database.MatrixCursor; 274.import android.net.Uri; 275. 276.// 继承 ContentProvider 以实现自定义的 ContentProvider（基于文件的信息存储） 277.public class MyContentProvider extends ContentProvider { 278. 279. private File file; 280. private FileOutputStream out; 281. private FileInputStream in; 282. 283. // ContentProvider 的删除数据接口 284. @Override 285. public int delete(Uri uri, String selection, String[] selectionArgs) { 286. // TODO Auto-generated method stub 287. return 0; 288. } 289. 290. @Override 291. public String getType(Uri uri) { 292. // TODO Auto-generated method stub 293. return null; 294. } 295. 296. // ContentProvider 的插入数据接口 297. @Override 298. public Uri insert(Uri uri, ContentValues values) { 299. 300. try { 301. out = new FileOutputStream(file); 302. out.write(values.getAsString(MyUser.User.USER_NAME).getBytes()); 303. out.close(); 304. 305. int rowId = 0; 306. Uri rowUri = ContentUris.appendId( 307. MyUser.User.CONTENT_URI.buildUpon(), rowId).build(); 308. getContext().getContentResolver().notifyChange(rowUri, null); 309. 310. return rowUri; 311. } catch (Exception e) { 312. return null; 313. } 314. } 315. 316. // 创建用于保存信息的文件 317. @Override 318. public boolean onCreate() { 319. try { 320. // 每个包中应用程序的私有目录为：/data/data/包名/ 321. // SD 卡目录为：/sdcard 322. file = new File("/data/data/com.webabcd.contentprovider/", 323. "demo.txt"); 324. if (!file.exists()) 325. file.createNewFile(); 326. 327. return true; 328. } catch (Exception ex) { 329. return false; 330. } 331. } 332. 333. // ContentProvider 的查询数据接口 334. @Override 335. public Cursor query(Uri uri, String[] projection, String selection, 336. String[] selectionArgs, String sortOrder) { 337. 338. String content; 339. 340. try { 341. in = new FileInputStream(file); 342. int length = (int) file.length(); 343. byte[] buffer = new byte[length]; 344. in.read(buffer, 0, length); 345. content = EncodingUtils.getString(buffer, "UTF-8"); 346. in.close(); 347. 348. String[] columns = new String[] { MyUser.User._ID, MyUser.User.USER_NAME }; 349. MatrixCursor cur = new MatrixCursor(columns); 350. String[] values = new String[] { "0", content }; 351. cur.moveToFirst(); 352. cur.addRow(values); 353. 354. return cur; 355. } catch (Exception e) { 356. return null; 357. } 358. } 359. 360. // ContentProvider 的更新数据接口 361. @Override 362. public int update(Uri uri, ContentValues values, String selection, 363. String[] selectionArgs) { 364. // TODO Auto-generated method stub 365. return 0; 366. } 367.} 368. 369. 370.Main.java 371. 372.代码 373.package com.webabcd.contentprovider; 374. 375.import java.util.Random; 376. 377.import android.app.Activity; 378.import android.content.ContentUris; 379.import android.content.ContentValues; 380.import android.database.Cursor; 381.import android.net.Uri; 382.import android.os.Bundle; 383.import android.provider.Contacts; 384.import android.provider.Contacts.People; 385.import android.util.Log; 386.import android.view.View; 387.import android.widget.Button; 388.import android.widget.Toast; 389. 390./* 391. * 几个常用的系统内置的 ContentProvider 如下： 392. * content://media/internal/images 这个URI将返回设备上存储的所有图片 393. * content://contacts/people/ 这个URI将返回设备上的所有联系人信息 394. * content://contacts/people/45 这个URI返回单个结果（联系人信息中ID为45的联系人记录） 395. */ 396.public class Main extends Activity { 397. /** Called when the activity is first created. */ 398. @Override 399. public void onCreate(Bundle savedInstanceState) { 400. super.onCreate(savedInstanceState); 401. setContentView(R.layout.main); 402. 403. Button btn1 = (Button) this.findViewById(R.id.btn1); 404. btn1.setText("新增联系人记录"); 405. btn1.setOnClickListener(new Button.OnClickListener() { 406. public void onClick(View v) { 407. Random random = new Random(); 408. insertRecords("name" + String.valueOf(random.nextInt()), String 409. .valueOf(random.nextInt())); 410. } 411. }); 412. 413. Button btn2 = (Button) this.findViewById(R.id.btn2); 414. btn2.setText("查看联系人记录"); 415. btn2.setOnClickListener(new Button.OnClickListener() { 416. public void onClick(View v) { 417. displayRecords(); 418. } 419. }); 420. 421. Button btn3 = (Button) this.findViewById(R.id.btn3); 422. btn3.setText("清除联系人记录"); 423. btn3.setOnClickListener(new Button.OnClickListener() { 424. public void onClick(View v) { 425. deleteRecords(); 426. } 427. }); 428. 429. Button btn4 = (Button) this.findViewById(R.id.btn4); 430. btn4.setText("更新联系人记录"); 431. btn4.setOnClickListener(new Button.OnClickListener() { 432. public void onClick(View v) { 433. // 此处只是演示，id 来自 People._ID ，可参见 displayRecords() 是如何获取 id 的 434. int id = 0; 435. updateRecord(id, "修改后的name"); 436. } 437. }); 438. 439. Button btn5 = (Button) this.findViewById(R.id.btn5); 440. btn5.setText("新增记录到 MyContentProvider"); 441. btn5.setOnClickListener(new Button.OnClickListener() { 442. public void onClick(View v) { 443. insertRecord2MyContentProvider("webabcd"); 444. } 445. }); 446. 447. Button btn6 = (Button) this.findViewById(R.id.btn6); 448. btn6.setText("获取记录从 MyContentProvider"); 449. btn6.setOnClickListener(new Button.OnClickListener() { 450. public void onClick(View v) { 451. displayRecord2MyContentProvider(); 452. } 453. }); 454. } 455. 456. // 调用 ContentProvider 的插入接口 457. private void insertRecords(String name, String phoneNum) { 458. ContentValues values = new ContentValues(); 459. values.put(People.NAME, name); 460. Uri uri = getContentResolver().insert(People.CONTENT_URI, values); 461. Log.d("MyDebug", uri.toString()); 462. Uri numberUri = Uri.withAppendedPath(uri, 463. People.Phones.CONTENT_DIRECTORY); 464. Log.d("MyDebug", numberUri.toString()); 465. 466. values.clear(); 467. values.put(Contacts.Phones.TYPE, People.Phones.TYPE_MOBILE); 468. values.put(People.NUMBER, phoneNum); 469. getContentResolver().insert(numberUri, values); 470. } 471. 472. // 调用 ContentProvider 的查询接口 473. private void displayRecords() { 474. String[] columns = new String[] { People._ID, People.NAME, 475. People.NUMBER }; 476. Uri contacts = People.CONTENT_URI; 477. Log.d("MyDebug", contacts.toString()); 478. Cursor cur = managedQuery(contacts, columns, // 要返回的数据字段 479. null, // WHERE子句 480. null, // WHERE 子句的参数 481. null // Order-by子句 482. ); 483. 484. if (cur.moveToFirst()) { 485. String id = null; 486. String name = null; 487. String phoneNo = null; 488. while (cur.getPosition() != cur.getCount()) { 489. id = cur.getString(cur.getColumnIndex(People._ID)); 490. name = cur.getString(cur.getColumnIndex(People.NAME)); 491. phoneNo = cur.getString(cur.getColumnIndex(People.NUMBER)); 492. 493. Toast.makeText(this, id + " / " + name + " / " + phoneNo, 494. Toast.LENGTH_SHORT).show(); 495. cur.moveToNext(); 496. } 497. } 498. } 499. 500. // 调用 ContentProvider 的删除接口 501. private void deleteRecords() { 502. Uri uri = People.CONTENT_URI; 503. Log.d("MyDebug", uri.toString()); 504. getContentResolver().delete(uri, null, null); 505. // getContentResolver().delete(uri, "NAME=" + "'name'", null); 506. } 507. 508. // 调用 ContentProvider 的更新接口 509. private void updateRecord(int recordNo, String name) { 510. Uri uri = ContentUris.withAppendedId(People.CONTENT_URI, recordNo); 511. Log.d("MyDebug", uri.toString()); 512. ContentValues values = new ContentValues(); 513. values.put(People.NAME, name); 514. getContentResolver().update(uri, values, null, null); 515. } 516. 517. 518. // 调用自定义 ContentProvider 的插入接口 519. private void insertRecord2MyContentProvider(String name) { 520. ContentValues values = new ContentValues(); 521. values.put(MyUser.User.USER_NAME, name); 522. getContentResolver().insert(MyUser.User.CONTENT_URI, values); 523. 524. } 525. 526. // 调用自定义 ContentProvider 的查询接口 527. private void displayRecord2MyContentProvider() { 528. String[] columns = new String[] { MyUser.User.USER_NAME }; 529. Uri uri = MyUser.User.CONTENT_URI; 530. Cursor cur = managedQuery(uri, columns, null, null, null); 531. 532. while (cur.getPosition() != cur.getCount()) { 533. String id = cur.getString(cur.getColumnIndex(People._ID)); 534. String name = cur.getString(cur.getColumnIndex(MyUser.User.USER_NAME)); 535. Toast.makeText(this, 536. id + " / " + name, 537. Toast.LENGTH_SHORT).show(); 538. cur.moveToNext(); 539. } 540. } 541.} 542. 543.AndroidManifest.xml 544. 545.代码 546.<?xml version="1.0" encoding="utf-8"?> 547.<manifest xmlns:android="http://schemas.android.com/apk/res/android" 548. package="com.webabcd.contentprovider" 549. android:versionCode="1" 550. android:versionName="1.0"> 551. <application android:icon="@drawable/icon" android:label="@string/app_name"> 552. <activity android:name=".Main" 553. android:label="@string/app_name"> 554. <intent-filter> 555. <action android:name="android.intent.action.MAIN" /> 556. <category android:name="android.intent.category.LAUNCHER" /> 557. </intent-filter> 558. </activity> 559. 560. <!-- 561. 配置一个自定义的 ContentProvider" 562. --> 563. <provider android:name="MyContentProvider" android:authorities="com.webabcd.MyContentProvider" /> 564. </application> 565. <uses-permission android:name="android.permission.WRITE_CONTACTS"></uses-permission> 566. <uses-permission android:name="android.permission.READ_CONTACTS"></uses-permission> 567. <uses-sdk android:minSdkVersion="3" /> 568.</manifest>