TShopping

 找回密碼
 註冊
搜索
查看: 575|回復: 0

[教學] [Android智慧型手機設計_Ch8_1_SQLite查詢功能]

[複製鏈接]
發表於 2013-6-21 23:13:41 | 顯示全部樓層 |閱讀模式
 
Push to Facebook Push to Plurk Push to Twitter 
1.jpg
存取SQLite資料庫的說明如下:

1.繼承SQLiteOpenHelper並改寫onCreate()

2.第一次呼叫getWritableDatabase()或getReadableDatabase()時會建立資料庫並自動呼叫onCreate()

3.SQLiteOpenHelper子類別內增加其他存取資料庫的方法,方便之後呼叫


SQLiteDatabase類別提供2種查詢功能的方法
1.rawQuery():僅接受單純的SQL查詢語法
rawQuery(String sql, String[] selectionArgs)
2.query():將SQL查詢語法依照query()的參數來切割
query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)


1.設計Ch8_1_QueryData.java

  1. package com.example.ch8_1_querydata;

  2. import java.util.ArrayList;
  3. import android.os.Bundle;
  4. import android.app.Activity;
  5. import android.view.Menu;
  6. import android.view.View;
  7. import android.view.View.OnClickListener;
  8. import android.widget.Button;
  9. import android.widget.EditText;
  10. import android.widget.Toast;

  11. public class Ch8_1_QueryData extends Activity {
  12. private EditText etPlaceName;
  13. private Button btnSubmit;
  14. private DBHP dbHlp;
  15. @Override
  16. protected void onCreate(Bundle savedInstanceState) {
  17. super.onCreate(savedInstanceState);
  18. setContentView(R.layout.main);
  19. connectDB();
  20. findViews();
  21. }

  22. private void connectDB() {
  23. if(dbHlp == null)
  24. dbHlp = new DBHP(this);
  25. dbHlp.fillDB();
  26. }

  27. @Override
  28. public void onResume() {
  29. super.onResume();
  30. if(dbHlp == null)
  31. dbHlp = new DBHP(this);
  32. }
  33. public void onPause() {
  34. super.onPause();
  35. if(dbHlp != null){
  36. dbHlp.close();
  37. dbHlp = null;
  38. }
  39. }        
  40. private void findViews() {
  41. etPlaceName = (EditText)findViewById(R.id.etPlaceName);
  42. btnSubmit = (Button)findViewById(R.id.btnSubmit);
  43. btnSubmit.setOnClickListener(new OnClickListener() {        
  44. @Override
  45. public void onClick(View v) {
  46. StringBuilder address = new StringBuilder("");
  47. String placeName = etPlaceName.getText().toString().trim();
  48. if(placeName.length() > 0){
  49. ArrayList<String> addresses = dbHlp.getAddress(placeName);
  50. if(addresses.size() > 0){
  51. for(String addr: addresses)
  52. address.append(addr + "\n");
  53. }else{
  54. address.append(getString(R.string.placeNotFound));        
  55. }
  56. } else{
  57. address.append(getString(R.string.inputPlaceName));
  58. }
  59. Toast.makeText(Ch8_1_QueryData.this,address, Toast.LENGTH_LONG).show();        
  60. }
  61. });
  62. }
  63. }
複製代碼
2.設計DBHP.java
  1. package com.example.ch8_1_querydata;

  2. import java.util.ArrayList;

  3. import android.content.ContentValues;
  4. import android.content.Context;
  5. import android.database.Cursor;
  6. import android.database.sqlite.SQLiteDatabase;
  7. import android.database.sqlite.SQLiteDatabase.CursorFactory;
  8. import android.database.sqlite.SQLiteOpenHelper;

  9. public class DBHP extends SQLiteOpenHelper {
  10. private static final String DATABASE_NAME = "sites";
  11. private static final int DATABASE_VERSION = 1;
  12. private static final String TABLE_NAME = "sitesInfo";
  13. private static final String TABLE_CREATE =
  14. "CREATE TABLE " + TABLE_NAME + " (id TEXT NOT NULL,name TEXT NOT NULL,phoneNo TEXT, address TEXT, PRIMARY KEY (id)); ";
  15. public DBHP(Context context) {
  16. super(context, DATABASE_NAME, null, DATABASE_VERSION);
  17. // TODO 自動產生的建構子 Stub
  18. }
  19. @Override
  20. public void onCreate(SQLiteDatabase db) {
  21. db.execSQL(TABLE_CREATE);

  22. }
  23. @Override
  24. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  25. db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
  26. onCreate(db);

  27. }        
  28. public void fillDB() {
  29. SQLiteDatabase db = getWritableDatabase();        
  30. ContentValues[] values = new ContentValues[3];
  31. for(int i=0; i<values.length; i++)
  32. values[i] = new ContentValues();

  33. values[0].put("id", "yangmingshan");
  34. values[0].put("name", "陽明山國家公園管理處");
  35. values[0].put("phoneNo", "02-28613601");
  36. values[0].put("address", "台北市北投區竹子湖路1之20號");

  37. values[1].put("id", "yushan");
  38. values[1].put("name", "玉山國家公園管理處");
  39. values[1].put("phoneNo", "049-2773121");
  40. values[1].put("address", "南投縣水里鄉中山路一段300號");

  41. values[2].put("id", "taroko");
  42. values[2].put("name", "太魯閣國家公園管理處");
  43. values[2].put("phoneNo", "03-8621100");
  44. values[2].put("address", "花蓮縣秀林鄉258號");        

  45. for(ContentValues row : values){
  46. db.insert(TABLE_NAME, null, row);
  47. }        
  48. db.close();
  49. }
  50. public ArrayList<String> getAddress(String name){
  51. SQLiteDatabase db = getReadableDatabase();
  52. String sql = "SELECT name, address FROM " + TABLE_NAME +
  53. " WHERE name LIKE ?";
  54. String[] args = {"%" + name + "%"};
  55. Cursor cursor = db.rawQuery(sql, args);
  56. ArrayList<String> addresses = new ArrayList<String>();
  57. int columnCount = cursor.getColumnCount();
  58. while(cursor.moveToNext()){
  59. String name_addr = "";
  60. for(int i=0; i<columnCount; i++)
  61. name_addr += cursor.getString(i) + "\n ";
  62. addresses.add(name_addr);        
  63. }
  64. cursor.close();
  65. db.close();
  66. return addresses;        
  67. }
  68. }
複製代碼

 

臉書網友討論
您需要登錄後才可以回帖 登錄 | 註冊 |

本版積分規則



Archiver|手機版|小黑屋|免責聲明|TShopping

GMT+8, 2016-12-11 01:00 , Processed in 0.081428 second(s), 25 queries .

本論壇言論純屬發表者個人意見,與 TShopping綜合論壇 立場無關 如有意見侵犯了您的權益 請寫信聯絡我們。

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表