《移動應用程式設計基礎》實驗5 資料庫實踐——簡單日記本

實驗名稱：

實驗5 資料庫實踐——簡單日記本

所使用的工具軟體及環境：

JDK1.8，Android Studio

一、實驗目的：

實驗目的：

掌握安卓資料庫的應用

二、實驗內容：

模擬一個日記本程式

實作簡單日記的新建、修改、洗掉、查詢功能，運行效果如下圖，

當點擊右下方的添加按鈕時如下圖所示，

當點擊ListView的Item時，將顯示日記的詳細資訊，

當點擊ListView的右邊的編輯圖片按鈕時，將進入編輯日記模式，

當點擊Toolbar中的值為“編輯”的TextView控制元件時，顯示如下，

使用SQLiteExpert軟體查看生成的資料庫

當使用Sqlite資料庫時，Android系統對其存放的資料庫檔案位于/data/data/[包名]/databases/*.db，如下圖所示，在安裝sdk目錄下運行monitor.bat檔案：

默認情況下，data目錄是禁止非root用戶訪問的，這時，我們就需要root權限來查看，首先打開Android SDK下的platform-tools目錄，然后按住Shift鍵并按下滑鼠右鍵，點擊“在此處打開命令視窗(W)”，如下圖所示，

然后在命令視窗輸入以下命令：

adb root

這時，我們就可以訪問/data目錄了，之后使用pull命令來復制檔案，

adb pull /data/data/com.example.sqlitedemo/databases

/DiaryDB.db DiaryDB.db

默認情況下，檔案將存放在D:\WorkSpace\ASSDK\platform-tools目錄中，

之后，打開SQLiteExpert軟體（需網上下載），依次點擊FileàOpen Database，選中剛付訓出的資料庫檔案：

在左邊的串列中，選中diary表，之后選擇右上方的Data選項，就可以查看資料，選擇旁邊的Design選項可以查看表的結構，其下方的“加號”、“減號”、“對號”、“叉號”分別為插入一條資料、洗掉選中的資料、提交更改、撤銷更改，

實驗要求：

完成實驗內容所有代碼；
圖文并重的方式敘述實作程序；
展示運行結果，

實驗程序：

Android中封裝了一個SQLiteOpenHelper抽象類，我們需要創建SQLiteOpenHelper的子類并覆寫onCreate方法，在此方法中撰寫我們需要的SQL陳述句，使用契約類SQLiteContract來定義URI、表格和列名稱的常數的容器，契約類允許我們跨同一軟體包中的所有其他類使用相同的常數，

public final class SQLiteContract {

//為了防止使用者不小心實體化類的構造方法，

//使建構式私有化，

private SQLiteContract () {}

//此內部類定義日記表的內容

public static class DiaryEntry implements BaseColumns {

public static final String TABLE_NAME = "diary";

public static final String COLUMN_NAME_TITLE = "title";

public static final String COLUMN_NAME_CONTENT = "content";

public static final String COLUMN_NAME_TIME = "time";

}

//其他表內容

}

繼承SQLiteOpenHelper并覆寫onCreate方法和onUpgrade方法，其中onUpgrade方法是一個設定資料庫版本號的方法，需要我們注意的是，當我們實體化DBHelper 時（比如new DBHelper），onCreate方法只會執行一次，即資料庫表只會創建一次，當資料庫版本號增加時，系統將呼叫onUpgrade方法，我們可以將升級的SQL陳述句放在此方法內完成資料庫的升級，

1 public class DiaryDbHelper extends SQLiteOpenHelper {

2 public static final int DATABASE_VERSION = 1;

3 public static final String DATABASE_NAME = "DiaryDB.db";

4 public DiaryDbHelper(Context context) {

5 super(context, DATABASE_NAME, null, DATABASE_VERSION);

6 }

7 @Override

8 public void onCreate(SQLiteDatabase db) {

9 db.execSQL(SQL_CREATE_ENTRIES);

10 }

11 @Override

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

13 db.execSQL(SQL_DELETE_ENTRIES);

14 onCreate(db);

15 }

16 }

向資料庫中插入一條資料時，可以使用ContentValues 物件傳遞至SQLiteDatabase物件的insert() 方法將資料插入資料庫，

SQLiteDatabase db = dbHelper.getWritableDatabase();

//設定插入值

ContentValues values = new ContentValues();

values.put(SQLiteContract.DiaryEntry.COLUMN_NAME_TITLE, title);

values.put(SQLiteContract.DiaryEntry.COLUMN_NAME_CONTENT, content);

values.put(SQLiteContract.DiaryEntry.COLUMN_NAME_TIME, date);

//執行插入方法

long newRowId = db.insert(SQLiteContract.DiaryEntry.TABLE_NAME, null, values);

db.close();

可以使用query方法查詢資料

SQLiteDatabase db = dbHelper.getReadableDatabase();

String[] projection = {

SQLiteContract.DiaryEntry._ID,

SQLiteContract.DiaryEntry.COLUMN_NAME_TITLE,

SQLiteContract.DiaryEntry.COLUMN_NAME_CONTENT,

SQLiteContract.DiaryEntry.COLUMN_NAME_TIME

};

String selection = SQLiteContract.DiaryEntry._ID + " = ?";

String[] selectionArgs = { id };

String sortOrder =SQLiteContract.DiaryEntry.COLUMN_NAME_TIME + " DESC";

Cursor c = db.query(

SQLiteContract.DiaryEntry.TABLE_NAME,

projection, selection, selectionArgs, null, null, sortOrder

);

c.moveToFirst();

String title= c.getString(c.getColumnIndex(SQLiteContract.DiaryEntry.

COLUMN_NAME_TITLE))

c.close();

db.close();

更新資料可以使用update方法

SQLiteDatabase db = dbHelper.getWritableDatabase();

ContentValues values = new ContentValues();

values.put(SQLiteContract.DiaryEntry.COLUMN_NAME_TITLE, title);

String selection = SQLiteContract.DiaryEntry._ID + " = ?";

String[] selectionArgs = { id };

int count = db.update(

SQLiteContract.DiaryEntry.TABLE_NAME,

values,

selection,

selectionArgs);

db.close();

洗掉資料可以使用delete方法

SQLiteDatabase db = dbHelper.getWritableDatabase();

String selection = SQLiteContract.DiaryEntry._ID + " = ?";

String[] selectionArgs = { id };

db.delete(SQLiteContract.DiaryEntry.TABLE_NAME, selection, selectionArgs);

db.close();

三、實驗結果測驗（完整所有代碼在資源下載壓縮包中，文章結尾有資源下載鏈接）

4個java檔案以及5個布局檔案

部分主要代碼：

//MainActivity.java
package com.example.mgh.diary;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Paint;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.TextView;

import java.util.ArrayList;

public class MainActivity extends Activity implements View.OnClickListener {
    public static ArrayList<info> info = new ArrayList<>();
    private ListView mlistview;
    public static SQLiteDatabase db;
    public Button buttonHide;
    public Button btn_search;
    public ImageView iv;
    private EditText editText;
    public Button look_button;

    public int a=0;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        init();
    }

    private void init() {
        info = new ArrayList<>();
        //editText = (EditText) findViewById(R.id.editText);
        btn_search = (Button) findViewById(R.id.btn_search);
        //buttonHide = (Button) findViewById(R.id.buttonHide);
        iv = (ImageView) findViewById(R.id.imageView);
        //buttonHide.setOnClickListener(this);
        btn_search.setOnClickListener(this);
        iv.setOnClickListener(this);

        //list監聽
        mlistview = (ListView) findViewById(R.id.mlistview);
        db = new MyHelper(this).getWritableDatabase();
        Cursor cursor = db.query("diary", null, null,
                null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
// 遍歷Cursor物件，取出資料
                info.add(new info(cursor.getString(cursor.getColumnIndex("name")),
                        cursor.getString(cursor.getColumnIndex("content")),
                                cursor.getInt(cursor.getColumnIndex("_id"))));
            } while (cursor.moveToNext());
        }

        db.close();
        updateAdapter();


    }

    public void updateAdapter() {
        /*View vi=View.inflate(MainActivity.this,R.layout.for_list,null);
        look_button=(Button)vi.findViewById(R.id.look_Button);
        look_button.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                Intent intent = new Intent(MainActivity.this, Show_diary.class);
                db = new MyHelper(MainActivity.this).getWritableDatabase();
                startActivity(intent);
            }
        });*/
        MyAdapter myAdapter = new MyAdapter();
        mlistview.setAdapter(myAdapter);
        mlistview.setOnItemClickListener(new AdapterView.OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                Intent intent = new Intent(MainActivity.this, Show_diary.class);
                intent.putExtra("id", i);
                db = new MyHelper(MainActivity.this).getWritableDatabase();
                startActivity(intent);

            }
        });
    }


    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.imageView:
                Intent intent = new Intent(this, Add_diary.class);
                intent.putExtra("id", info.size());
                startActivity(intent);
                a=1;
            case R.id.btn_search:
                if(a==0) {
                    btn_search.setText("取消");
                    look_button.setVisibility(View.VISIBLE);
                    a=1;
                }
                else {
                    btn_search.setText("編輯");
                    look_button.setVisibility(View.INVISIBLE);
                    a=0;
                }
                /*Intent intent2 = new Intent(this, Delete_diary.class);
                intent2.putExtra("id", info.size());
                startActivity(intent2);*/
                /*info = new ArrayList<>();

                buttonHide.setVisibility(View.VISIBLE);
                db = new MyHelper(this).getWritableDatabase();
                Cursor cursor = db.query("diary", new String[]{"_id,name,content"},
                        "name like ?", new String[]{"%"+editText.getText().toString()+"%"}, null, null,
                        null, null);
                if (cursor.moveToFirst()) {
                    do {
// 遍歷Cursor物件，取出資料
                        info.add(new info(cursor.getString(cursor.getColumnIndex("name")),
                                cursor.getString(cursor.getColumnIndex("content")),
                                cursor.getInt(cursor.getColumnIndex("_id"))));
                    } while (cursor.moveToNext());
                }

                db.close();
                updateAdapter();*/
                break;
        }
    }

    class MyAdapter extends BaseAdapter {

        public MyAdapter() {
            super();
        }

        public int getCount() {
            return info.size();
        }

        @Override
        public Object getItem(int i) {
            return info.get(i).name;
        }

        @Override
        public long getItemId(int i) {
            return i;
        }

        @SuppressLint("WrongConstant")
        @Override
        public View getView(int i, View view, ViewGroup viewGroup) {
            ViewHolder viewholder;
            if (view == null) {
                view = LayoutInflater.from(getApplicationContext()).inflate(R.layout.for_list, viewGroup, false);
                viewholder = new ViewHolder();
                viewholder.tv = (TextView) view.findViewById(R.id.name);
            } else {
                viewholder = (ViewHolder) view.getTag();
            }

            //View vi=View.inflate(MainActivity.this, R.layout.for_list,null);
            look_button=(Button)view.findViewById(R.id.button3);
            //洗掉按鈕不可見
            look_button.setVisibility(View.INVISIBLE);
            look_button.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    /*int id;
                    Intent intent = getIntent();
                    id = intent.getIntExtra("id", -1);
                    MainActivity.db.delete("diary", "_id=?", new String[]{String.valueOf(MainActivity.info.get(id).id)});
                    MainActivity.db.close();*/
                    /*SQLiteDatabase db = dbHelper.getWritableDatabase();
                    String selection = SQLiteContract.DiaryEntry._ID + " = ?";
                    String[] selectionArgs = { id };
                    db.delete(SQLiteContract.DiaryEntry.TABLE_NAME, selection, selectionArgs);
                    db.close();*/

                }
            });

            viewholder.tv.setText(String.valueOf(i + 1) + ": " + info.get(i).name);
            viewholder.tv.getPaint().setFlags(Paint.UNDERLINE_TEXT_FLAG);
            return view;
        }

        class ViewHolder {
            TextView tv;
        }


    }

    class info {
        String name;
        String content;
        int id;

        public info(String name, String content, int id) {
            this.name = name;
            this.content = content;
            this.id = id;
        }
    }

}

//Add_diary.java
package com.example.mgh.diary;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import org.jetbrains.annotations.Nullable;

public class Add_diary extends Activity {
    public int id;
    private EditText name;
    private EditText diary;
    private Button btn;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.add_diary);
        name = (EditText) findViewById(R.id.diary_name);
        diary = (EditText) findViewById(R.id.diary);
        btn = (Button) findViewById(R.id.submit);

        Intent intent = getIntent();
        id = intent.getIntExtra("id", -1);

    }

    public void sub(View v) {
        ContentValues values = new ContentValues();
        values.put("name", name.getText().toString());
        values.put("content", diary.getText().toString());
        MainActivity.db = new MyHelper(this).getWritableDatabase();

        MainActivity.db.insert("diary", null, values);
        MainActivity.db.close();

        Toast.makeText(this, "日志保存成功", Toast.LENGTH_SHORT).show();
        Intent intent = new Intent(this, MainActivity.class);
        startActivity(intent);
    }


}

//MyHelper.java
package com.example.mgh.diary;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyHelper extends SQLiteOpenHelper{

    public MyHelper(Context context){
        super(context,"dia.db",null,5);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("create table diary(_id integer primary key autoincrement," +
                "name varchar(11),content varchar(1000))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

//Show_diary.java
package com.example.mgh.diary;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;

import org.jetbrains.annotations.Nullable;

//import android.support.annotation.Nullable;

public class Show_diary extends Activity {
    private EditText name;
    private EditText ed;
    int id;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.show_diary);

        name = (EditText) findViewById(R.id.name);
        ed = (EditText) findViewById(R.id.content);
        Intent intent = getIntent();
        id = intent.getIntExtra("id", -1);

        name.setText(MainActivity.info.get(id).name);
        ed.setText(MainActivity.info.get(id).content);
    }

    public void backandchange(View v) {

        ContentValues values=new ContentValues();
        values.put("name",name.getText().toString());
        values.put("content",ed.getText().toString());

        MainActivity.db.update("diary", values,"_id=?", new String[]{String.valueOf(MainActivity.info.get(id).id)});
        MainActivity.db.close();


        Intent intent = new Intent(this, MainActivity.class);
        startActivity(intent);

    }

    public void del(View v) {
        MainActivity.db.delete("diary", "_id=?", new String[]{String.valueOf(MainActivity.info.get(id).id)});
        MainActivity.db.close();

        Intent intent = new Intent(this, MainActivity.class);
        startActivity(intent);
    }
}