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

woff · 發表於 2013-6-21 23:13:41

存取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

package com.example.ch8_1_querydata;
import java.util.ArrayList;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class Ch8_1_QueryData extends Activity {
private EditText etPlaceName;
private Button btnSubmit;
private DBHP dbHlp;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
connectDB();
findViews();
}
private void connectDB() {
if(dbHlp == null)
dbHlp = new DBHP(this);
dbHlp.fillDB();
}
@Override
public void onResume() {
super.onResume();
if(dbHlp == null)
dbHlp = new DBHP(this);
}
public void onPause() {
super.onPause();
if(dbHlp != null){
dbHlp.close();
dbHlp = null;
}
}
private void findViews() {
etPlaceName = (EditText)findViewById(R.id.etPlaceName);
btnSubmit = (Button)findViewById(R.id.btnSubmit);
btnSubmit.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
StringBuilder address = new StringBuilder("");
String placeName = etPlaceName.getText().toString().trim();
if(placeName.length() > 0){
ArrayList<String> addresses = dbHlp.getAddress(placeName);
if(addresses.size() > 0){
for(String addr: addresses)
address.append(addr + "\n");
}else{
address.append(getString(R.string.placeNotFound));
}
} else{
address.append(getString(R.string.inputPlaceName));
}
Toast.makeText(Ch8_1_QueryData.this,address, Toast.LENGTH_LONG).show();
}
});
}
}

複製代碼

2.設計DBHP.java

package com.example.ch8_1_querydata;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHP extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "sites";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "sitesInfo";
private static final String TABLE_CREATE =
"CREATE TABLE " + TABLE_NAME + " (id TEXT NOT NULL,name TEXT NOT NULL,phoneNo TEXT, address TEXT, PRIMARY KEY (id)); ";
public DBHP(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO 自動產生的建構子 Stub
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(TABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
public void fillDB() {
SQLiteDatabase db = getWritableDatabase();
ContentValues[] values = new ContentValues[3];
for(int i=0; i<values.length; i++)
values[i] = new ContentValues();
values[0].put("id", "yangmingshan");
values[0].put("name", "陽明山國家公園管理處");
values[0].put("phoneNo", "02-28613601");
values[0].put("address", "台北市北投區竹子湖路1之20號");
values[1].put("id", "yushan");
values[1].put("name", "玉山國家公園管理處");
values[1].put("phoneNo", "049-2773121");
values[1].put("address", "南投縣水里鄉中山路一段300號");
values[2].put("id", "taroko");
values[2].put("name", "太魯閣國家公園管理處");
values[2].put("phoneNo", "03-8621100");
values[2].put("address", "花蓮縣秀林鄉258號");
for(ContentValues row : values){
db.insert(TABLE_NAME, null, row);
}
db.close();
}
public ArrayList<String> getAddress(String name){
SQLiteDatabase db = getReadableDatabase();
String sql = "SELECT name, address FROM " + TABLE_NAME +
" WHERE name LIKE ?";
String[] args = {"%" + name + "%"};
Cursor cursor = db.rawQuery(sql, args);
ArrayList<String> addresses = new ArrayList<String>();
int columnCount = cursor.getColumnCount();
while(cursor.moveToNext()){
String name_addr = "";
for(int i=0; i<columnCount; i++)
name_addr += cursor.getString(i) + "\n ";
addresses.add(name_addr);
}
cursor.close();
db.close();
return addresses;
}
}

複製代碼

帳號		自動登錄		找回密碼
密碼				註冊

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

相關帖子