資料庫操作(建立資料庫,建立表格,新增,修改,刪除,查詢)

1.UI的xml檔

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:text="建立資料庫" />

    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView1"
        android:layout_below="@+id/textView1"
        android:layout_marginTop="17dp"
        android:text="建立資料庫" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/button1"
        android:layout_below="@+id/button1"
        android:text="建立表格" />

    <Button
        android:id="@+id/button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/button2"
        android:layout_below="@+id/button2"
        android:text="insert" />

    <Button
        android:id="@+id/button4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/button3"
        android:layout_below="@+id/button3"
        android:text="update" />

    <Button
        android:id="@+id/button5"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/button4"
        android:layout_below="@+id/button4"
        android:text="delete" />

    <Button
        android:id="@+id/button6"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/button5"
        android:layout_below="@+id/button5"
        android:text="select" />

</RelativeLayout>

2.主程式程式碼

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.TextView;
import android.widget.Toast;
import android.content.Context;
import android.content.DialogInterface;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.database.Cursor;
import android.app.AlertDialog;

public class MainActivity extends Activity {

Button cd, ct, in, up, de, se;
TextView tx;
SQLiteDatabase db;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
tx = (TextView) this.findViewById(R.id.textView1);
cd = (Button) this.findViewById(R.id.button1);
ct = (Button) this.findViewById(R.id.button2);
in = (Button) this.findViewById(R.id.button3);
up = (Button) this.findViewById(R.id.button4);
de = (Button) this.findViewById(R.id.button5);
se = (Button) this.findViewById(R.id.button6);
cd.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
db = MainActivity.super.openOrCreateDatabase(
"member.db", Activity.MODE_PRIVATE,
null);

db.close();
Toast.makeText(MainActivity.this, "Create Database Completed",
Toast.LENGTH_SHORT).show();
}

});

ct.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
try{
createTable();
Toast.makeText(MainActivity.this, "Create Table Completed",
Toast.LENGTH_SHORT).show();
}catch(Exception io){
Toast.makeText(MainActivity.this, "Table Already Create",
Toast.LENGTH_SHORT).show();
db.close();
}
}

});

in.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
insert();
Toast.makeText(MainActivity.this, "Insert Completed",
Toast.LENGTH_SHORT).show();
}

});

up.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
update();
Toast.makeText(MainActivity.this, "Update Completed",
Toast.LENGTH_SHORT).show();
}

});

de.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
delete();
Toast.makeText(MainActivity.this, "Delete Completed",
Toast.LENGTH_SHORT).show();
}

});

se.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
select();
Toast.makeText(MainActivity.this, "Select Completed",
Toast.LENGTH_SHORT).show();
}

});
}

public void createTable() {
db = MainActivity.super.openOrCreateDatabase("member.db",
Activity.MODE_PRIVATE, null);
String DATABASE_TABLE = "member";
String DATABASE_CREATE_TABLE = "create table "
+ DATABASE_TABLE
+ "( _id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,ID varchar, name varchar, pwd varchar, age INTEGER);";

db.execSQL(DATABASE_CREATE_TABLE);
db.close();
}

public void insert() {
db = MainActivity.super.openOrCreateDatabase("member.db",
Activity.MODE_PRIVATE, null);
String inSQL = "insert into member(ID,name,pwd,age) values ('A01','李小強','2468',30);";
db.execSQL(inSQL);
db.close();
}

public void update() {
db = MainActivity.super.openOrCreateDatabase("member.db",
Activity.MODE_PRIVATE, null);
String inSQL = "update member set pwd='0000' where ID='A01';";
db.execSQL(inSQL);
db.close();
}

public void delete() {
db = MainActivity.super.openOrCreateDatabase("member.db",
Activity.MODE_PRIVATE, null);
String inSQL = "delete from member;";
db.execSQL(inSQL);
db.close();
}

public void select() {
db = MainActivity.super.openOrCreateDatabase("member.db",
Activity.MODE_PRIVATE, null);
Cursor c = db.rawQuery("select * from member;", null);
String data = "";
c.moveToFirst();
for (int i = 0; i < c.getCount(); i++) {
for (int j = 1; j < c.getColumnCount(); j++) {
data += c.getString(j) + ",";
}
data += "\n";
c.moveToNext();
}

new AlertDialog.Builder(this).setMessage(data)
.setPositiveButton("OK", new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface arg0, int arg1) {

}

}).show();
db.close();
}

}