《移动应用程序设计基础》实验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);
    }
}