噹噹噹~00:00這個時候來發篇文章吧!
接續前兩篇
[Day16]Android學習-資料庫介紹-資料操縱(1)
[Day17]Android學習-資料庫介紹-SQLiteDataBase類別(2)
資料庫的介紹,這篇就來實際操作吧!
首先,建立兩種版面配置,分別為ListView選項的設計以及資料庫操作介面。
完整程式碼
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<com.google.android.material.textfield.TextInputLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1">
<com.google.android.material.textfield.TextInputEditText
android:id="@+id/edtId"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="ID"/>
</com.google.android.material.textfield.TextInputLayout>
<com.google.android.material.textfield.TextInputLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1">
<com.google.android.material.textfield.TextInputEditText
android:id="@+id/edtName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="水果名稱"/>
</com.google.android.material.textfield.TextInputLayout>
<com.google.android.material.textfield.TextInputLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1">
<com.google.android.material.textfield.TextInputEditText
android:id="@+id/edtPrice"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="水果價格"/>
</com.google.android.material.textfield.TextInputLayout>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/btnAppend"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="新增" />
<Button
android:id="@+id/btnUpdate"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="更新" />
<Button
android:id="@+id/btnSelect"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="查詢" />
</LinearLayout>
<ListView
android:id="@+id/lstView"
android:layout_width="match_parent"
android:layout_height="match_parent"
/>
</LinearLayout>
完整程式碼
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:id="@+id/txtId"
android:layout_width="wrap_content"
android:layout_height="50dp"
android:layout_margin="10dp"
android:textSize="40dp"
android:text="ID"
/>
<ImageView
android:id="@+id/imgView"
android:layout_width="50dp"
android:layout_height="50dp"
android:layout_margin="10dp"
android:layout_toRightOf="@id/txtId"
tools:srcCompat="@tools:sample/avatars" />
<TextView
android:id="@+id/txtName"
android:layout_width="wrap_content"
android:layout_height="25dp"
android:textSize="20dp"
android:layout_marginLeft="30dp"
android:layout_toRightOf="@id/imgView"
android:text="Name"
android:layout_alignTop="@+id/imgView"
/>
<TextView
android:id="@+id/txtPrice"
android:layout_width="wrap_content"
android:layout_height="25dp"
android:layout_alignBottom="@+id/imgView"
android:layout_marginLeft="30dp"
android:layout_toRightOf="@id/imgView"
android:text="Price"
android:textSize="20dp" />
</RelativeLayout>
UI設計完成後,接者要設計控制流程,開發者使各元件各自擁有預期的功能。
public class FruitDB {
private SQLiteDatabase db=null;
private final static String TABLE_NAME="TableFruit";
private final static String _ID="_id";
private final static String NAME="name";
private final static String PRICE="price";
private final static String CREATE_TABLE="CREATE TABLE "+TABLE_NAME+" ("+_ID+" INTEGER PRIMARY KEY,"+NAME+" TEXT,"+PRICE+" INTEGER )";
private Context context;
......
}
public void open() throws SQLException {
try{
db=context.openOrCreateDatabase("fruitdb.db",Context.MODE_PRIVATE,null);
db.execSQL(CREATE_TABLE);
}
catch (Exception e){
Toast.makeText(context,"fruitdb.db 已建立",Toast.LENGTH_LONG).show();
}
}
public void append(String name,String price){
String insert_text="INSERT INTO "+TABLE_NAME+"( "+NAME+","+PRICE+") values ('"+name+"',"+price+")";
db.execSQL(insert_text);
}
public void update(String name,String price,long id){
String update_text="UPDATE "+TABLE_NAME+" SET "+NAME+"='"+name+"',"+PRICE+"="+price+" WHERE "+_ID+"="+id;
db.execSQL(update_text);
}
public void delete(long id){
String delete_text="DELETE FROM "+TABLE_NAME+" WHERE "+_ID+"="+id;
db.execSQL(delete_text);
public Cursor select(long id){
String select_text="SELECT * FROM "+TABLE_NAME+" WHERE "+_ID+"="+id;
Cursor cursor=db.rawQuery(select_text,null);
return cursor;
}
public Cursor select_all(){
String select_text="SELECT * FROM "+TABLE_NAME;
Cursor cursor=db.rawQuery(select_text,null);
return cursor;
}
完整程式碼
public class FruitDB {
//建立SQLiteDatabase物件
private SQLiteDatabase db=null;
private final static String TABLE_NAME="TableFruit";
// 建立欄位名稱
// 水果編排編號
private final static String _ID="_id";
// 水果名稱
private final static String NAME="name";
// 水果價格
private final static String PRICE="price";
// 建立水果表格
private final static String CREATE_TABLE="CREATE TABLE "+TABLE_NAME+" ("+_ID+" INTEGER PRIMARY KEY,"
+NAME+" TEXT,"+PRICE+" INTEGER )";
private Context context;
// FruitDB的建構式
public FruitDB(Context context){
this.context=context;
}
// 建立open()方法,資料庫存執行開啟資料庫,尚未存在則建立資料庫
public void open() throws SQLException {
try{
// 建立資料庫並指定權限
db=context.openOrCreateDatabase("fruitdb.db",Context.MODE_PRIVATE,null);
// 建立表格
db.execSQL(CREATE_TABLE);
}
catch (Exception e){
Toast.makeText(context,"fruitdb.db 已建立",Toast.LENGTH_LONG).show();
}
}
// 建立新增、修改(更新)、刪除,資料操作
// execSQL完整輸入SQL語法實現,資料操作
// 建立方法append()
public void append(String name,String price){
String insert_text="INSERT INTO "+TABLE_NAME+"( "+NAME+","+PRICE+") values ('"+name+"',"+price+")";
db.execSQL(insert_text);
}
// 建立方法update()
public void update(String name,String price,long id){
String update_text="UPDATE "+TABLE_NAME+" SET "+NAME+"='"+name+"',"+PRICE+"="+price+" WHERE "+_ID+"="+id;
db.execSQL(update_text);
}
// 建立方法delete()
public void delete(long id){
String delete_text="DELETE FROM "+TABLE_NAME+" WHERE "+_ID+"="+id;
db.execSQL(delete_text);
}
// 建立查詢方法select(),查詢單筆資料
// rawQuery完整輸入SQL語法實現資料查詢
public Cursor select(long id){
String select_text="SELECT * FROM "+TABLE_NAME+" WHERE "+_ID+"="+id;
Cursor cursor=db.rawQuery(select_text,null);
return cursor;
}
// 建立查詢方法select_all(),查詢所有資料
// rawQuery完整輸入SQL語法實現資料查詢
public Cursor select_all(){
String select_text="SELECT * FROM "+TABLE_NAME;
Cursor cursor=db.rawQuery(select_text,null);
return cursor;
}
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViews();
db=new FruitDB(MainActivity.this);
db.open();
cursor=db.select_all();
UpdateListView(cursor);
}
lstView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
cursor.moveToPosition(position);
edtId.setText(cursor.getString(0));
edtName.setText(cursor.getString(1));
edtPrice.setText(cursor.getString(2));
}
});
lstView.setSelector(android.R.drawable.alert_light_frame);
lstView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView<?> adapterView, View view, int position, long id) {
try{
cursor.moveToPosition(position);
edtId.setText(cursor.getString(0));
edtName.setText(cursor.getString(1));
edtPrice.setText(cursor.getString(2));
final int _id=Integer.parseInt(cursor.getString(0));
String name=cursor.getString(1);
String price=cursor.getString(2);
new AlertDialog.Builder(MainActivity.this)
.setMessage("確定刪除\nID="+id+"\n"+name+"\n售價="+price)
.setPositiveButton("確認", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
// 執行刪除動作
db.delete(cursor.getInt(0));
// 更新顯示畫面
UpdateListView(cursor=db.select_all());
}
}).setNegativeButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
}
}).show();
}catch (Exception e){
Toast.makeText(MainActivity.this,"刪除失敗",Toast.LENGTH_LONG).show();
}
return false;
}
});
}
case R.id.btnAppend:{
try{
db.append(edtName.getText().toString(),edtPrice.getText().toString());
UpdateListView(cursor=db.select_all());
}catch (Exception e){
Toast.makeText(MainActivity.this,"新增失敗",Toast.LENGTH_LONG).show();
}
break;
}
case R.id.btnUpdate:{
try{
db.update(edtName.getText().toString(), edtPrice.getText().toString(),Integer.parseInt(edtId.getText().toString()));
UpdateListView(cursor=db.select_all());
imm.toggleSoftInput(0, InputMethodManager.HIDE_NOT_ALWAYS);
}catch (Exception e){
Toast.makeText(MainActivity.this,"更新失敗",Toast.LENGTH_LONG).show();
}
break;
}
case R.id.btnSelect:{
Cursor select_cursor;
select_cursor=db.select(Integer.parseInt(edtId.getText().toString()));
UpdateListView(select_cursor);
break;
}
public void UpdateListView(Cursor fruitcursor){
MyAdapter adapter =new MyAdapter(fruitcursor);
lstView.setAdapter(adapter);
cursor=fruitcursor;
}
public class MyAdapter extends BaseAdapter{
private Cursor cursor;
public MyAdapter(Cursor cursor){
this.cursor=cursor;
}
@Override
public int getCount() {
return cursor.getCount();
}
@Override
public Object getItem(int position) {
return null;
}
@Override
public long getItemId(int position) {
cursor.moveToPosition(position);
return cursor.getInt(0);
}
@Override
public View getView(int position, View view, ViewGroup viewGroup) {
View getview=view;
cursor.moveToPosition(position);
getview=getLayoutInflater().inflate(R.layout.fruit_layout,null);
ImageView imgView=(ImageView)getview.findViewById(R.id.imgView);
TextView txtName=(TextView)getview.findViewById(R.id.txtName);
txtName.setText(cursor.getString(1));
TextView txtPrice=(TextView)getview.findViewById(R.id.txtPrice);
txtPrice.setText(String.valueOf(cursor.getString(2)));
TextView txtId=(TextView)getview.findViewById(R.id.txtId);
txtId.setText(String.valueOf(cursor.getString(0)));
switch (cursor.getString(1).trim()){
case "apple":{
imgView.setImageResource(R.drawable.apple);
break;
}
case "banana": {
imgView.setImageResource(R.drawable.banana);
break;
}
case "orange":{
imgView.setImageResource(R.drawable.orange);
break;
}
case "strawberry":{
imgView.setImageResource(R.drawable.strawberry);
break;
}
case "grape": {
imgView.setImageResource(R.drawable.grape);
break;
}
default:{
imgView.setImageResource(R.drawable.fruit);
Log.d("debug",cursor.getString(1));
break;
}
}
return getview;
}
}
getview=getLayoutInflater().inflate(R.layout.fruit_layout,null);
ImageView imgView=(ImageView)getview.findViewById(R.id.imgView);
TextView txtName=(TextView)getview.findViewById(R.id.txtName);
txtName.setText(cursor.getString(1));
TextView txtPrice=(TextView)getview.findViewById(R.id.txtPrice);
txtPrice.setText(String.valueOf(cursor.getString(2)));
TextView txtId=(TextView)getview.findViewById(R.id.txtId);
txtId.setText(String.valueOf(cursor.getString(0)));
switch (cursor.getString(1).trim()){
case "apple":{
imgView.setImageResource(R.drawable.apple);
break;
}
case "banana": {
imgView.setImageResource(R.drawable.banana);
break;
}
case "orange":{
imgView.setImageResource(R.drawable.orange);
break;
}
case "strawberry":{
imgView.setImageResource(R.drawable.strawberry);
break;
}
case "grape": {
imgView.setImageResource(R.drawable.grape);
break;
}
default:{
imgView.setImageResource(R.drawable.fruit);
Log.d("debug",cursor.getString(1));
break;
}
完整程式碼
public class MainActivity extends AppCompatActivity {
// 建立欄位名稱
// 水果編排編號
private final static String _ID="_id";
// 水果名稱
private final static String NAME="name";
// 水果價格
private final static String PRICE="price";
private ListView lstView;
private Button btnAppend,btnUpdate,btnSelect;
private TextInputEditText edtId,edtName,edtPrice;
private FruitDB db;
private Cursor cursor;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 尋找各個元件的ID
findViews();
// 建立FruitDB物件db
db=new FruitDB(MainActivity.this);
// 查詢目前資料庫擁有的資料
db.open();
cursor=db.select_all();
// 更新ListView重新顯示資料
UpdateListView(cursor);
}
private void findViews() {
btnAppend=(Button)findViewById(R.id.btnAppend);
btnUpdate=(Button)findViewById(R.id.btnUpdate);
btnSelect=(Button)findViewById(R.id.btnSelect);
edtId=(TextInputEditText)findViewById(R.id.edtId);
edtName=(TextInputEditText)findViewById(R.id.edtName);
edtPrice=(TextInputEditText)findViewById(R.id.edtPrice);
lstView=(ListView)findViewById(R.id.lstView);
// 建立按鈕的偵聽器
btnAppend.setOnClickListener(listener);
btnUpdate.setOnClickListener(listener);
btnSelect.setOnClickListener(listener);
// 匿名類別偵聽
lstView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
cursor.moveToPosition(position);
edtId.setText(cursor.getString(0));
edtName.setText(cursor.getString(1));
edtPrice.setText(cursor.getString(2));
}
});
lstView.setSelector(android.R.drawable.alert_light_frame);
lstView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView<?> adapterView, View view, int position, long id) {
try{
cursor.moveToPosition(position);
edtId.setText(cursor.getString(0));
edtName.setText(cursor.getString(1));
edtPrice.setText(cursor.getString(2));
final int _id=Integer.parseInt(cursor.getString(0));
String name=cursor.getString(1);
String price=cursor.getString(2);
new AlertDialog.Builder(MainActivity.this)
.setMessage("確定刪除\nID="+id+"\n"
+name+"\n售價="+price)
.setPositiveButton("確認", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
// 執行刪除動作
db.delete(cursor.getInt(0));
// 更新顯示畫面
UpdateListView(cursor=db.select_all());
}
})
.setNegativeButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
}
})
.show();
}catch (Exception e){
Toast.makeText(MainActivity.this,"刪除失敗",Toast.LENGTH_LONG).show();
}
return false;
}
});
}
public Button.OnClickListener listener=new Button.OnClickListener(){
@Override
public void onClick(View view) {
switch(view.getId()){
case R.id.btnAppend:{
try{
// 執行新增動作
db.append(edtName.getText().toString(),edtPrice.getText().toString());
// 更新顯示畫面
UpdateListView(cursor=db.select_all());
}catch (Exception e){
Toast.makeText(MainActivity.this,"新增失敗",Toast.LENGTH_LONG).show();
}
break;
}
case R.id.btnUpdate:{
try{
// 執行修改動作
db.update(edtName.getText().toString(), edtPrice.getText().toString(),Integer.parseInt(edtId.getText().toString()));
// 更新顯示畫面
UpdateListView(cursor=db.select_all());
}catch (Exception e){
Toast.makeText(MainActivity.this,"更新失敗",Toast.LENGTH_LONG).show();
}
break;
}
case R.id.btnSelect:{
Cursor select_cursor;
select_cursor=db.select(Integer.parseInt(edtId.getText().toString()));
UpdateListView(select_cursor);
break;
}
}
}
};
// 建立更新ListView的方法
public void UpdateListView(Cursor fruitcursor){
MyAdapter adapter =new MyAdapter(fruitcursor);
adapter.notifyDataSetChanged();
lstView.setAdapter(adapter);
cursor=fruitcursor;
}
public class MyAdapter extends BaseAdapter{
private Cursor cursor;
public MyAdapter(Cursor cursor){
this.cursor=cursor;
}
@Override
public int getCount() {
return cursor.getCount();
}
@Override
public Object getItem(int position) {
return null;
}
@Override
public long getItemId(int position) {
cursor.moveToPosition(position);
return cursor.getInt(0);
}
@Override
public View getView(int position, View view, ViewGroup viewGroup) {
View getview=view;
cursor.moveToPosition(position);
getview=getLayoutInflater().inflate(R.layout.fruit_layout,null);
ImageView imgView=(ImageView)getview.findViewById(R.id.imgView);
TextView txtName=(TextView)getview.findViewById(R.id.txtName);
txtName.setText(cursor.getString(1));
TextView txtPrice=(TextView)getview.findViewById(R.id.txtPrice);
txtPrice.setText(String.valueOf(cursor.getString(2)));
TextView txtId=(TextView)getview.findViewById(R.id.txtId);
txtId.setText(String.valueOf(cursor.getString(0)));
switch (cursor.getString(1).trim()){
case "apple":{
imgView.setImageResource(R.drawable.apple);
break;
}
case "banana": {
imgView.setImageResource(R.drawable.banana);
break;
}
case "orange":{
imgView.setImageResource(R.drawable.orange);
break;
}
case "strawberry":{
imgView.setImageResource(R.drawable.strawberry);
break;
}
case "grape": {
imgView.setImageResource(R.drawable.grape);
break;
}
default:{
imgView.setImageResource(R.drawable.fruit);
Log.d("debug",cursor.getString(1));
break;
}
}
return getview;
}
}
}
點擊選單效果及資料回傳至EditText
查詢資料結果
刪除資料ID=5
這篇文章較長,辛苦大家了~
若文章有誤,歡迎大家提出建議。
Thank you for your time.