杜仲教學: 3月 2019

2019年3月18日星期一

[Android]_ch8.19_SQLiteDB模糊查詢LIKE

[Android]_ch8.19_SQLiteDB模糊查詢LIKE

public ArrayList<DBData> likeQuery(DBData data){

SQLiteDatabase db=getWritableDatabase();

String[] selectionArgs={"%"+data.getAddress()+"%"};

Cursor cursor=db.rawQuery("SELECT * FROM teacher WHERE address LIKE?", selectionArgs);

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

while(cursor.moveToNext()){

String s[]=new String[cursor.getColumnCount()];

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

s[i]=cursor.getString(i);

}

DBData dbdata=new DBData(s[0], s[1], s[2], s[3]);

list.add(dbdata);

}

return list;

}

[Android]_ch8.18_連接外部資料庫

[Android]_ch8.18_連接外部資料庫

public ArrayList<DBData> queryDB(Context context){

SQLiteDatabase db=context.openOrCreateDatabase("YPU.db", Context.MODE_PRIVATE, null);

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

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

while(cursor.moveToNext()){

String s[]=new String[cursor.getColumnCount()];

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

s[i]=cursor.getString(i);

}

DBData data=new DBData(s[0], s[1], s[2], s[3]);

list.add(data);

}

return list;

}

[Android]_ch8.17_利用SQLite3建立資料庫

2019年3月15日星期五

[Android]_ch8.16_SQLiteDB刪除語法2(物件方法)

[Android]_ch8.16_SQLiteDB刪除語法2(物件方法)

刪除資料的步驟如下：

1.呼叫getWritableDatabase()建立、開啟資料庫並回傳資料庫物件

SQLiteDatabase db = getWritableDatabase();

2.設定刪除的條件。

String whereClause="id='"+id+"'";

int count=db.delete(TABLE_NAME, whereClause, null);

呼叫delete()執行刪除後會回傳成功刪除筆數。

public int deleteDBMethod(DBData data){

SQLiteDatabase db=getWritableDatabase();

String whereClause="address='"+data.getAddress()+"'";

int count=db.delete("teacher", whereClause, null);

return count;

}

btn16.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et2.getText().toString();

String name=et3.getText().toString();

String phoneNo=et4.getText().toString();

String address=et5.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

int count=dbhp.deleteDBMethod(data);

Toast.makeText(MainActivity.this, "刪除"+count+"筆成功!", Toast.LENGTH_SHORT).show();

AL=dbhp.queryAll();

showData(0);

}

});

[Android]_ch8.17_Linux指令介紹

[Android]_ch8.17_Linux指令介紹

-rw-r--r-- 1 root root 1617 Sep 22 12:42 passwd

2019年3月13日星期三

[Android]_ch8.15_SQLiteDB條件查詢rawQuery方法

[Android]_ch8.15_SQLiteDB條件查詢rawQuery方法

public ArrayList<DBData> keyWordQuery(DBData data){

SQLiteDatabase db=getWritableDatabase();

String selectionArgs[]={data.getAddress()};

Cursor cursor=db.rawQuery("SELECT * FROM teacher WHERE address=?;", selectionArgs);

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

while (cursor.moveToNext()) {

String s[]=new String[cursor.getColumnCount()];

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

s[i]=cursor.getString(i);

}

DBData dbdata=new DBData(s[0], s[1], s[2], s[3]);

list.add(dbdata);

}

return list;

}

btn14.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

AL=dbhp.keyWordQuery(data);

showData(0);

showOnTv1();

}

});

public void showOnTv1(){

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

if(AL.size()>0){

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

String data=AL.get(i).getId()+"\t"+AL.get(i).getName()+"\t"+AL.get(i).getPhoneNo()+"\t"+AL.get(i).getAddress()+"\n";

sb.append(data);

}

}else{

sb.append("查詢不到資料!");

}

tv1.setText(sb);

}

[Android]_ch8.14_SQLiteDB條件查詢query方法

[Android]_ch8.14_SQLiteDB條件查詢query方法

public ArrayList<DBData> queryAll(DBData data){

SQLiteDatabase db=getWritableDatabase();

String[] columns={"id","name","phoneNo","address"};

String selection="address='"+data.getAddress()+"';";

Cursor cursor=db.query("teacher", columns, selection, null, null, null, null);

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

while (cursor.moveToNext()) {

String s[]=new String[cursor.getColumnCount()];

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

s[i]=cursor.getString(i);

}

DBData dbdata=new DBData(s[0], s[1], s[2], s[3]);

list.add(dbdata);

}

return list;

}

btn13.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

AL=dbhp.queryAll(data);

showData(0);

}

});

2019年3月11日星期一

[Android]_ch8.13_SQLiteDB更新語法3(物件方法)

[Android]_ch8.13_SQLiteDB更新語法3(物件方法)

public int updateDBMethod(DBData data){

SQLiteDatabase db=getWritableDatabase();

String id=data.getId();

String name=data.getName();

String phoneNo=data.getPhoneNo();

String address=data.getAddress();

ContentValues values=new ContentValues();

values.put("id", id);

values.put("name", name);

values.put("phoneNo", phoneNo);

values.put("address", address);

String whereClause="id='"+id+"';";

int count=db.update("teacher", values, whereClause, null);

return count;

}

btn12.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

int count=dbhp.updateDBMethod(data);

Toast.makeText(MainActivity.this, "更新"+count+"筆資料", Toast.LENGTH_SHORT).show();

AL=dbhp.queryAll();

showData(0);

}

});

[Android]_ch8.12_SQLiteDB新增語法3(物件方法)

[Android]_ch8.12_SQLiteDB新增語法3(物件方法)

public long insertDBMethod(DBData data){

SQLiteDatabase db=getWritableDatabase();

String id=data.getId();

String name=data.getName();

String phoneNo=data.getPhoneNo();

String address=data.getAddress();

ContentValues values=new ContentValues();

values.put("id", id);

values.put("name", name);

values.put("phoneNo", phoneNo);

values.put("address", address);

long rowId=db.insert("teacher", null, values);

return rowId;

}

btn11.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

long rowId=dbhp.insertDBMethod(data);

if(rowId==-1)

Toast.makeText(MainActivity.this, "很抱歉新增失敗!rowId="+rowId, Toast.LENGTH_SHORT).show();

else

Toast.makeText(MainActivity.this, "恭喜新增成功!rowId="+rowId, Toast.LENGTH_SHORT).show();

AL=dbhp.queryAll();

showData(0);

}

});

2019年3月8日星期五

[Android]_ch8.11_SQLiteDB刪除語法1(字串參數)

[Android]_ch8.11_SQLiteDB刪除語法1(字串參數)

public void deleteDB(String address){

SQLiteDatabase db=getWritableDatabase();

db.execSQL("DELETE FROM teacher WHERE address='"+address+"';");

}

btn10.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String address=et4.getText().toString();

dbhp.deleteDB(address);

Toast.makeText(MainActivity.this, "刪除成功!", Toast.LENGTH_SHORT).show();

AL=dbhp.queryAll();

showData(0);

}

});

[Android]_ch8.10_SQLiteDB更新語法2(物件導向)

[Android]_ch8.10_SQLiteDB更新語法2(物件導向)

public void updateDB(DBData data){

SQLiteDatabase db=getWritableDatabase();

String id=data.getId();

String name=data.getName();

String phoneNo=data.getPhoneNo();

String address=data.getAddress();

db.execSQL("UPDATE teacher SET id='"+id+"',name='"+name+"',phoneNo='"+phoneNo+"',address='"+address+"' WHERE id='"+id+"';");

}

btn9.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

dbhp.updateDB(data);

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

AL=dbhp.queryAll();

showData(0);

}

});

[Android]_ch8.9_SQLiteDB更新語法1(字串參數)

[Android]_ch8.9_SQLiteDB更新語法1(字串參數)

public void updateDB(String id,String name,String phoneNo,String address){

SQLiteDatabase db=getWritableDatabase();

db.execSQL("UPDATE teacher SET id='"+id+"',name='"+name+"',phoneNo='"+phoneNo+"',address='"+address+"' WHERE id='"+id+"';");

}

btn8.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

dbhp.updateDB(id, name, phoneNo, address);

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

AL=dbhp.queryAll();

showData(0);

}

});

2019年3月6日星期三

[Android]_ch8.8_SQLiteDB新增語法2(物件導向)

[Android]_ch8.8_SQLiteDB新增語法2(物件導向)

public void insertDB(DBData data){

SQLiteDatabase db=getWritableDatabase();

String id=data.getId();

String name=data.getName();

String phoneNo=data.getPhoneNo();

String address=data.getAddress();

db.execSQL("INSERT INTO teacher VALUES('"+id+"','"+name+"','"+phoneNo+"','"+address+"');");

}

btn7.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

DBData data=new DBData(id, name, phoneNo, address);

dbhp.insertDB(data);

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

AL=dbhp.queryAll();

showData(index);

}

});

[Android]_ch8.7_SQLiteDB瀏覽資料(物件導向)

[Android]_ch8.7_SQLiteDB瀏覽資料(物件導向)

package com.derek.ch25_db;

public class DBData {

String id;

String name;

String phoneNo;

String address;

public DBData(String id,String name,String phoneNo,String address){

this.id=id;

this.name=name;

this.phoneNo=phoneNo;

this.address=address;

}

public String getId(){

return id;

}

public String getName(){

return name;

}

public String getPhoneNo(){

return phoneNo;

}

public String getAddress(){

return address;

}

}

public ArrayList<DBData> queryAll(){

SQLiteDatabase db=getWritableDatabase();

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

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

while(cursor.moveToNext()){

String id=cursor.getString(0);

String name=cursor.getString(1);

String phoneNo=cursor.getString(2);

String address=cursor.getString(3);

DBData data=new DBData(id, name, phoneNo, address);

list.add(data);

}

return list;

}

btn5.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

index=index-1;

if(index<0)

index=AL.size()-1;

showData(index);

}

});

btn6.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

index=index+1;

if(index>=AL.size())

index=0;

showData(index);

}

});

public void showData(int index){

if(AL.size()>0){

tv1.setText("第幾筆/總筆數.."+(index+1)+"/"+AL.size());

DBData data=AL.get(index);

et1.setText(data.getId());

et2.setText(data.getName());

et3.setText(data.getPhoneNo());

et4.setText(data.getAddress());

}else{

tv1.setText("第幾筆/總筆數...0/0");

et1.setText("");

et2.setText("");

et3.setText("");

et4.setText("");

}

2019年3月4日星期一

[Android]_ch8.6_SQLiteDB新增語法1

[Android]_ch8.6_SQLiteDB新增語法1

public void insertDB(String id,String name,String phoneNo,String address){

SQLiteDatabase db=getWritableDatabase();

db.execSQL("INSERT INTO teacher VALUES('"+id+"','"+name+"','"+phoneNo+"','"+address+"');");

}

btn4.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

String id=et1.getText().toString();

String name=et2.getText().toString();

String phoneNo=et3.getText().toString();

String address=et4.getText().toString();

dbhp.insertDB(id, name, phoneNo, address);

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

}

});

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);

}

});

}

訂閱：文章 (Atom)

2019年3月18日 星期一

2019年3月15日 星期五

2019年3月13日 星期三

2019年3月11日 星期一

2019年3月8日 星期五

2019年3月6日 星期三

2019年3月4日 星期一

2019年3月18日星期一

2019年3月15日星期五

2019年3月13日星期三

2019年3月11日星期一

2019年3月8日星期五

2019年3月6日星期三

2019年3月4日星期一