Android ch8 5 SQLiteDB新增與查詢

[Android]_ch8.5_SQLiteDB新增與查詢

package com.derek.ch25_db;



import java.util.ArrayList;

import java.util.List;



import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;



public class DBHP extends SQLiteOpenHelper{

public DBHP(Context context){

super(context, "Gjun", null, 2);

}



@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL("CREATE TABLE teacher (id TEXT,name TEXT,phoneNo TEXT,address TEXT);");

}



@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("DROP TABLE IF EXISTS taecher;");

onCreate(db);

}

public void insertDB(){

SQLiteDatabase db=getWritableDatabase();

db.execSQL("INSERT INTO teacher VALUES('1','杜仲','0912-345678','台北');");

db.execSQL("INSERT INTO teacher VALUES('2','陳二','0912-345678','新竹');");

db.execSQL("INSERT INTO teacher VALUES('3','張三','0912-345678','台中');");

db.execSQL("INSERT INTO teacher VALUES('4','李四','0912-345678','台南');");

db.execSQL("INSERT INTO teacher VALUES('5','王五','0912-345678','新竹');");

}

public ArrayList<String> queryDB(){

SQLiteDatabase db=getWritableDatabase();

Cursor cursor=db.rawQuery("SELECT * FROM teacher;",null);

ArrayList<String> list=new ArrayList<String>();

while(cursor.moveToNext()){

String msg="";

for(int i=0;i<cursor.getColumnCount();i++){

msg=msg+cursor.getString(i)+"\t";

}

list.add(msg);

}

return list;

}

}

package com.derek.ch25_db;

import java.util.ArrayList;

import android.app.Activity;

import android.os.Bundle;

import android.view.Menu;

import android.view.MenuItem;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.TextView;

import android.widget.Toast;

public class MainActivity extends Activity {

TextView tv1;

Button btn1,btn2;

DBHP dbhp;

    @Override

    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

        btn1=(Button)findViewById(R.id.button1);

        btn2=(Button)findViewById(R.id.button2);

        tv1=(TextView)findViewById(R.id.textView1);

        if(dbhp==null)

        dbhp=new DBHP(MainActivity.this);

        else

        Toast.makeText(MainActivity.this, "資料庫已存在不需再建立!", Toast.LENGTH_SHORT).show();

        

        

        btn1.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

dbhp.insertDB();

Toast.makeText(MainActivity.this, "新增成功", Toast.LENGTH_SHORT).show();

}

});

        btn2.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

ArrayList<String> list=dbhp.queryDB();

StringBuilder sb=new StringBuilder("查詢結果如下:\n");

if(list.size()>0){

for(int i=0;i<list.size();i++){

sb.append(list.get(i)+"\n");

}

}else{

Toast.makeText(MainActivity.this, "查詢不到資料...", Toast.LENGTH_SHORT).show();

}

tv1.setText(sb);

}

});

    }

}