1.首先在專案res資料夾建立一個raw資料夾，將SQLite檔案放入此資料夾
public class DBManager {
private final int BUFFER_SIZE = 400000;
public static final String DB_NAME = "db2.db"; // 保存的資料庫檔案名
public static final String PACKAGE_NAME = "tw.android";
public static final String DB_PATH = "/data"
+ Environment.getDataDirectory().getAbsolutePath() + "/"
+ PACKAGE_NAME;// 在手機裡存放資料庫的位置(/data/data/tw.android/db2.db)
private SQLiteDatabase database;
private Context context;
public DBManager(Context coNtext) {
this.context = coNtext;
}
public SQLiteDatabase getDatabase() {
return database;
}
public void setDatabase(SQLiteDatabase database) {
this.database = database;
}
public void openDatabase() {
this.database = this.openDatabase(DB_PATH + "/" + DB_NAME);
}
private SQLiteDatabase openDatabase(String dbfile) {
try {
if (!(new File(dbfile).exists())) {
Log.i("have db2????????", "no");
// 判斷資料庫檔案是否存在，若不存在則執行導入，否則直接打開資料庫
InputStream is = this.context.getResources().openRawResource(
R.raw.db2); // 欲導入的資料庫
FileOutputStream fos = new FileOutputStream(dbfile);
byte[] buffer = new byte[BUFFER_SIZE];
int count = 0;
while ((count = is.read(buffer)) > 0) {
fos.write(buffer, 0, count);
}
fos.close();
is.close();
}
SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbfile,
null);
return db;
} catch (FileNotFoundException e) {
Log.e("Database", "File not found");
e.printStackTrace();
} catch (IOException e) {
Log.e("Database", "IO exception");
e.printStackTrace();
}
return null;
}
public void closeDatabase() {
this.database.close();
}
}
3.主要檔案Main.java搭配基本SQLite指令
public class Main extends Activity {
private SQLiteDatabase db;
private EditText mEdtName, mEdtAddr, mEdtUpdate1, mEdtUpdate2, mEdtDelete,
mEdtSQL;
private TextView mTextList1, mTextList2;
private Button mBtnAdd, mBtnUpdate, mBtnDelete, mBtnSQL;
public DBManager dbHelper;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
setupViewComponent();
dbHelper = new DBManager(this);
dbHelper.openDatabase();
db = dbHelper.getDatabase();
queryList();
}
private void setupViewComponent() {
mTextList1 = (TextView) findViewById(R.id.textList1);
mTextList2 = (TextView) findViewById(R.id.textList2);
mEdtName = (EditText) findViewById(R.id.edtName);
mEdtAddr = (EditText) findViewById(R.id.edtAddr);
mEdtUpdate1 = (EditText) findViewById(R.id.edtUpdate1);
mEdtUpdate2 = (EditText) findViewById(R.id.edtUpdate2);
mEdtDelete = (EditText) findViewById(R.id.edtDelete);
mEdtSQL = (EditText) findViewById(R.id.edtSQL);
mBtnAdd = (Button) findViewById(R.id.btnAdd);
mBtnUpdate = (Button) findViewById(R.id.btnUpdate);
mBtnDelete = (Button) findViewById(R.id.btnDelete);
mBtnSQL = (Button) findViewById(R.id.btnSQL);
mBtnAdd.setOnClickListener(onClickBtnAdd);
mBtnUpdate.setOnClickListener(onClickBtnUpdate);
mBtnDelete.setOnClickListener(onClickBtnDelete);
mBtnSQL.setOnClickListener(onClickBtnSQL);
}
/* 新增 */
private Button.OnClickListener onClickBtnAdd = new Button.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
/* 方法1 直接下指令 */
if (mEdtName.getText().toString().isEmpty() == false) { // name有值
db.execSQL("insert into user('name') values ('"
+ mEdtName.getText().toString() + "')");
}
if (mEdtAddr.getText().toString().isEmpty() == false) { // address有值
db.execSQL("insert into location('address') values ('"
+ mEdtAddr.getText().toString() + "')");
}
/* 方法2 */
// if(mEdtName.getText().toString().isEmpty() == false){ // name有值
// ContentValues newRow = new ContentValues();
// newRow.put("name", mEdtName.getText().toString());
// db.insert("user", null, newRow);
// }
// if (mEdtAddr.getText().toString().isEmpty() == false) {
// //address有值
// ContentValues newRow = new ContentValues();
// newRow.put("address", mEdtAddr.getText().toString());
// db.insert("location", null, newRow);
// }
queryList();
}
};
/* 修改 */
private Button.OnClickListener onClickBtnUpdate = new Button.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
/* 方法1 直接下指令 */
db.execSQL("UPDATE location SET address='"
+ mEdtUpdate2.getText().toString() + "' WHERE address='"
+ mEdtUpdate1.getText().toString() + "'");
/* 方法2 */
// ContentValues newRow3 = new ContentValues();
// newRow3.put("address", mEdtUpdate2.getText().toString());
// db.update("location", newRow3,
// "address='"+mEdtUpdate1.getText().toString()+"'", null);
queryList();
}
};
/* 刪除 */
private Button.OnClickListener onClickBtnDelete = new Button.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
/* 方法1 直接下指令 */
db.execSQL("DELETE FROM location WHERE address='"
+ mEdtDelete.getText().toString() + "'");
/* 方法2 */
// db.delete("location",
// "address='"+mEdtDelete.getText().toString()+"'", null);
queryList();
}
};
private Button.OnClickListener onClickBtnSQL = new Button.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Cursor cursor = db.rawQuery(mEdtSQL.getText().toString(), null); // 搜尋用rawQuery
cursor.moveToFirst();
mTextList1.setText(cursor.getString(0) + cursor.getString(1));
while (cursor.moveToNext())
mTextList1.append("\n" + cursor.getString(0) + " "
+ cursor.getString(1));
}
};
/* 搜尋 */
void queryList() {
/* 方法1 直接下指令 */
String queryUser = "SELECT * FROM user";
Cursor cursor1 = db.rawQuery(queryUser, null); // 搜尋用rawQuery
String queryLocation = "SELECT * FROM location";
Cursor cursor2 = db.rawQuery(queryLocation, null); // 搜尋用rawQuery
/* 方法2 */
// Cursor cursor1 = db.query("user", new String[] { "Id", "name"},
// null, null, null, null, null);
// Cursor cursor2 = db.query("location", new String[] { "Id",
// "address"},
// null, null, null, null, null);
cursor1.moveToFirst();
mTextList1
.setText(cursor1.getString(0) + " " + cursor1.getString(1));
while (cursor1.moveToNext())
mTextList1.append("\n" + cursor1.getString(0) + " "
+ cursor1.getString(1));
cursor2.moveToFirst();
mTextList2
.setText(cursor2.getString(0) + " " + cursor2.getString(1));
while (cursor2.moveToNext())
mTextList2.append("\n" + cursor2.getString(0) + " "
+ cursor2.getString(1));
}
/* 離開程式 */
public boolean onKeyDown(int keycode, KeyEvent event) {
if (keycode == KeyEvent.KEYCODE_BACK) {
// 關閉資料庫
dbHelper.closeDatabase();
finish();
return true;
}
return super.onKeyDown(keycode, event);
}
@Override
public void onDestroy() {
super.onDestroy();
// Kill myself
android.os.Process.killProcess(android.os.Process.myPid());
}
}
4.main.xml配置
5.完成如圖
SQLite db2.db 檔案下載
參考來源:
http://fecbob.pixnet.net/blog/post/43610863
http://hscc.cs.nctu.edu.tw/~lincyu/Android/Chapter11.pdf
