【Android實驗】資料存儲與通路sqlite

實驗目的
實驗要求
實驗過程
- 功能分析：
- 實驗結果：
- 實驗的代碼
  - 1. Student類
實驗總結

分别使用sqlite3工具和Android代碼的方式建立SQLite資料庫。在完成建立資料庫的工作後，程式設計實作基本的資料庫操作功能，包括資料的添加、删除和更新

建立一個學生管理的應用，基本資訊包含學生姓名，班級，學号。采用資料庫存儲這些資訊。
應用應該至少包含資訊錄入和删除功能。
資料顯示考慮采用ListView。

構造sqlite的處理類，封裝了關于資料庫的相關處理。
界面顯示，結合課本中的例子與第三次實驗的關于ListView部分的處理。
功能，資料庫的添加資訊，删除資訊，顯示資訊等功能的實作。

使用sqlite3工具建立SQLite資料庫：

進入模拟器的位置，運作

nox_adb shell

進入指令行界面。

建立datasets檔案夾，建立stuInfo.db資料庫，并使用SQL語句建立一個名叫

studentinfo

的表。

查詢目前表的資訊以及表内的屬性資訊。

用SQL語句插入一些表項，并進行查詢

使用SQL語句删除表項之後，再進行查詢。

使用SQL語句更新表項之後，在進行查詢。

package com.example.administrator.sixthapp;

/**
 * Created by Administrator on 2018/10/22.
 */

public class Student {
    public int ID=-1;
    public String name;
    public String cls;
    public String stuNo;

    @Override
    public String toString() {
        String ans="";
        ans += this.ID;
        ans += " " + this.name;
        ans += " " + this.cls;
        ans += " " + this.stuNo;
        return ans;
    }
}

DBAdapter類

package com.example.administrator.sixthapp;

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

/**
 * Created by Administrator on 2018/10/22.
 */

public class DBAdapter {
    private static final String DB_NAME="student.db";
    private static final String DB_TABLE="studentinfo";
    private static final int DB_VERSION=1;

    private static final String KEY_ID="_id";
    private static final String KEY_NAME="name";
    private static final String KEY_CLASS="class";
    private static final String KEY_STUNO="stuNo";

    private SQLiteDatabase db;
    private final Context context;
    private DBOpenHelper dbOpenHelper;

    private static class DBOpenHelper extends SQLiteOpenHelper
    {
        public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,int version)
        {
            super(context,name,factory,version);
        }

        private static final String DB_CREATE = "create table " + DB_TABLE + "(" + KEY_ID + " integer primary key autoincrement," +
                KEY_NAME + " text not null, " + KEY_CLASS + " text not null, " + KEY_STUNO + " text not null)";

        @Override
        public void onCreate(SQLiteDatabase _db) {
            _db.execSQL(DB_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase _db, int i, int i1) {
            _db.execSQL("DROP TABLE IF EXISTS"+DB_TABLE);
            onCreate(_db);
        }
    }

    public DBAdapter(Context _context){
        context = _context;
    }
    public void close()
    {
        if(db != null)
        {
            db.close();
            db = null;
        }
    }
    public void open() throws SQLiteException
    {
        dbOpenHelper = new DBOpenHelper(context,DB_NAME,null,DB_VERSION);
        try
        {
            db = dbOpenHelper.getWritableDatabase();
        }catch(SQLiteException e)
        {
            db = dbOpenHelper.getReadableDatabase();
        }
    }
    //插入
    public long insert(Student stu)
    {
        ContentValues newValues = new ContentValues();
        newValues.put(KEY_NAME,stu.name);
        newValues.put(KEY_CLASS,stu.cls);
        newValues.put(KEY_STUNO,stu.stuNo);
        return db.insert(DB_TABLE,null,newValues);
    }
    //删除全部
    public long deleteAllData(){
        return db.delete(DB_TABLE,null,null);
    }
    //删除單條記錄
    public long deleteOneData(long id)
    {
        return db.delete(DB_TABLE,KEY_ID+"="+id,null);
    }
    //更新一條記錄
    public long updateOneData(long id, Student stu)
    {
        ContentValues updateV = new ContentValues();
        updateV.put(KEY_NAME,stu.name);
        updateV.put(KEY_CLASS,stu.cls);
        updateV.put(KEY_STUNO,stu.stuNo);
        return db.update(DB_TABLE,updateV,KEY_ID+"="+id,null);
    }
    //從cursor中提取資料
    private Student[] ConvertToStudent(Cursor cursor)
    {
        int cnt_result=cursor.getCount();
        if(cnt_result == 0 || !cursor.moveToFirst())
        {
            return null;
        }
        Student[] stus = new Student[cnt_result];
        for(int i = 0 ; i < cnt_result;i++)
        {
            stus[i] = new Student();
            stus[i].ID = cursor.getInt(0);
            stus[i].name = cursor.getString(cursor.getColumnIndex(KEY_NAME));
            stus[i].cls = cursor.getString(cursor.getColumnIndex(KEY_CLASS));
            stus[i].stuNo = cursor.getString(cursor.getColumnIndex(KEY_STUNO));
            cursor.moveToNext();
        }
        return stus;
    }
    //查詢一條資料
    public Student[] queryOneData(long id)
    {
        Cursor results = db.query(DB_TABLE,new String[]{KEY_ID,KEY_NAME,KEY_CLASS,KEY_STUNO},KEY_ID + "=" + id,null,null,null,null);
        return ConvertToStudent(results);
    }
    //查詢全部資料
    public Student[] getAllData()
    {
        Cursor results = db.query(DB_TABLE,new String[]{KEY_ID,KEY_NAME,KEY_CLASS,KEY_STUNO},null,null,null,null,null);
        return ConvertToStudent(results);
    }
}

MainActivity類

package com.example.administrator.sixthapp;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.support.v7.widget.PopupMenu;
import android.view.LayoutInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.HeaderViewListAdapter;
import android.widget.ListView;
import android.widget.Toast;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {
    EditText et_name,et_class,et_stuno,et_id;
    Button btn_add,btn_show_all,btn_delete_all,btn_clear,btn_delete_id,btn_query,btn_update;
    ArrayList<String> list;
    ArrayAdapter<String> adp;
    ListView lv;
    private DBAdapter dbAdapter;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        et_name = (EditText)findViewById(R.id.et_name);
        et_class = (EditText)findViewById(R.id.et_class);
        et_stuno = (EditText)findViewById(R.id.et_stuno);
        et_id = (EditText)findViewById(R.id.et_id);

        btn_add = (Button)findViewById(R.id.btn_add);
        btn_show_all =(Button)findViewById(R.id.btn_show_all);
        btn_delete_all = (Button)findViewById(R.id.btn_delete_all);
        btn_clear = (Button)findViewById(R.id.btn_clear);
        btn_delete_id = (Button)findViewById(R.id.btn_delete_id);
        btn_query = (Button)findViewById(R.id.btn_query);
        btn_update = (Button)findViewById(R.id.btn_update);
        lv = (ListView)findViewById(R.id.lv);

        dbAdapter = new DBAdapter(this);
        dbAdapter.open();

        list = new ArrayList<String>();
        adp = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,list);
        LayoutInflater inflater = LayoutInflater.from(MainActivity.this);
        View view = inflater.inflate(R.layout.head,null);
        lv.setAdapter(adp);

        show();
        //lv.addHeaderView(view);

        lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, final int pos, long l) {
                if(l > 0)
                {
                    PopupMenu popup = new PopupMenu(MainActivity.this,view);
                    popup.getMenuInflater().inflate(R.menu.del,popup.getMenu());
                    popup.show();
                    popup.setOnMenuItemClickListener(new PopupMenu.OnMenuItemClickListener() {
                        @Override
                        public boolean onMenuItemClick(MenuItem item) {
                            switch(item.getItemId())
                            {
                                case R.id.del:
                                    ArrayAdapter temp = (ArrayAdapter)lv.getAdapter();
                                    String s=(String)temp.getItem(pos);
                                    String[] ss = s.split(" ");
                                    System.out.println(s);
                                    dbAdapter.deleteOneData(Integer.valueOf(ss[0]));
                                    show();
                                    return true;
                                default:
                                    return false;
                            }
                        }
                    });
                }
            }
        });

        btn_add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Student stu = new Student();
                stu.name = et_name.getText().toString();
                stu.cls = et_class.getText().toString();
                stu.stuNo = et_stuno.getText().toString();

                long colunm = dbAdapter.insert(stu);

                if(colunm == -1)
                {
                    Toast.makeText(getApplicationContext(),"添加過程錯誤:error",Toast.LENGTH_SHORT).show();
                }
                else
                {
                    Toast.makeText(getApplicationContext(),"添加成功：success",Toast.LENGTH_SHORT).show();
                }

                /*
                System.out.print(name+"   "+cls+"   "+stuNo);
                HeaderViewListAdapter temp = (HeaderViewListAdapter)lv.getAdapter();
                ((ArrayAdapter)temp.getWrappedAdapter()).add(name+"   "+cls+"   "+stuNo);
                */
            }
        });

        btn_show_all.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Toast.makeText(getApplicationContext(),"顯示全部",Toast.LENGTH_SHORT).show();
                show();
            }
        });
        btn_clear.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Toast.makeText(getApplicationContext(),"清除全部",Toast.LENGTH_SHORT).show();
                ArrayAdapter temp_adp=(ArrayAdapter)lv.getAdapter();
                temp_adp.clear();
            }
        });
        btn_delete_all.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                dbAdapter.deleteAllData();
                Toast.makeText(getApplicationContext(),"删除全部",Toast.LENGTH_SHORT).show();
            }
        });
        btn_delete_id.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
               // ArrayAdapter temp = (ArrayAdapter)lv.getAdapter();
               // String s=(String)temp.getItem(Integer.valueOf(et_id.getText().toString()));
                dbAdapter.deleteOneData(Integer.valueOf(et_id.getText().toString()));
                Toast.makeText(getApplicationContext(),"已删除"+et_id.getText().toString(),Toast.LENGTH_SHORT).show();
                show();
            }
        });
        btn_query.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Student[] oneStu = dbAdapter.queryOneData(Integer.valueOf(et_id.getText().toString()));
                if (oneStu == null) {
                    Toast.makeText(getApplicationContext(),"沒有查詢到ID為"+et_id.getText().toString()+"的資訊",Toast.LENGTH_SHORT).show();
                } else
                {
                    String res = "";
                    res += "姓名："+oneStu[0].name;
                    res += "班級："+oneStu[0].cls;
                    res += "學号："+oneStu[0].stuNo;
                    Toast.makeText(getApplicationContext(),"ID為"+et_id.getText().toString()+"的資訊為："+res,Toast.LENGTH_SHORT).show();
                }

            }
        });
        btn_update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Student stu = new Student();
                stu.name = et_name.getText().toString();
                stu.cls = et_class.getText().toString();
                stu.stuNo = et_stuno.getText().toString();
                dbAdapter.updateOneData(Integer.valueOf(et_id.getText().toString()),stu);
                Toast.makeText(getApplicationContext(),"更新成功",Toast.LENGTH_SHORT).show();
            }
        });
    }

    public void show()
    {
        Student[] stus= dbAdapter.getAllData();
        ArrayAdapter temp_adp=(ArrayAdapter)lv.getAdapter();
        if(stus == null) {
            temp_adp.clear();
            return;
        }
        temp_adp.clear();
        for(int i = 0 ; i < stus.length; i++)
        {
            temp_adp.add(stus[i].toString());
        }
    }
}

<?xml version="1.0" encoding="utf-8"?>
<menu xmlns:android="http://schemas.android.com/apk/res/android">
    <item android:id="@+id/del"
        android:title="删除" />
</menu>

main.xml(界面檔案)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="match_parent"
    android:layout_height="match_parent">
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:weightSum="1">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="姓名:"
            android:textSize="20dp"
            android:layout_weight="0.17"
            android:textAlignment="center"/>
        <EditText
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="0.61"
            android:id="@+id/et_name"/>
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:weightSum="1">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="班級:"
            android:textSize="20dp"
            android:layout_weight="0.17"
            android:textAlignment="center"/>

        <EditText
            android:id="@+id/et_class"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="0.61" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:weightSum="1">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="學号:"
            android:textSize="20dp"
            android:layout_weight="0.17"
            android:textAlignment="center"/>
        <EditText
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="0.61"
            android:id="@+id/et_stuno"/>
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="添加資料"
            android:id="@+id/btn_add"
            android:layout_weight="1"
            />
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="全部顯示"
            android:id="@+id/btn_show_all"
            android:layout_weight="1"
            />
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="清除顯示"
            android:id="@+id/btn_clear"
            android:layout_weight="1"
            />
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="全部删除"
            android:id="@+id/btn_delete_all"
            android:layout_weight="1"
            />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="ID:"
            android:textSize="20dp"
            android:layout_weight="0.1"/>
        <EditText
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="3"
            android:id="@+id/et_id"/>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="ID删除"
            android:id="@+id/btn_delete_id"
            android:layout_weight="1"
            />
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="ID查詢"
            android:id="@+id/btn_query"
            android:layout_weight="1"
            />
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="ID更新"
            android:id="@+id/btn_update"
            android:layout_weight="1"
            />
    </LinearLayout>
    <ListView
        android:id="@+id/lv"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">

    </ListView>
</LinearLayout>

ListView用法：

ListView lv;
lv = (ListView)findViewById(R.id.lv);
ArrayList<String> list;
ArrayAdapter<String> adp;
list = new ArrayList<String>();
adp = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,list);
lv.setAdapter(adp);

//更改
ArrayAdapter temp_adp=(ArrayAdapter)lv.getAdapter();
temp_adp.clear();//清除
temp_adp.add(String)//添加

DBAdapter用法：

private static class DBOpenHelper extends SQLiteOpenHelper
    {
        public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,int version)
        {
            super(context,name,factory,version);
        }

        private static final String DB_CREATE = "create table " + DB_TABLE + "(" + KEY_ID + " integer primary key autoincrement," +
                KEY_NAME + " text not null, " + KEY_CLASS + " text not null, " + KEY_STUNO + " text not null)";

        @Override
        public void onCreate(SQLiteDatabase _db) {
            _db.execSQL(DB_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase _db, int i, int i1) {
            _db.execSQL("DROP TABLE IF EXISTS"+DB_TABLE);
            onCreate(_db);
        }
    }

sqlite3遇到的問題：

首先，如果使用夜神模拟器，就要到夜神模拟器的安裝路徑找adb而不能在sdk中的platform中找adb,夜神模拟器中的adb為nox_adb.exe。

sqlite3進入shell以後，如果遇到以下報錯：

mkdir failed for , Read-only file system
可以用以下指令進行解決：
```
adb shell 
mount -o remount rw /
           
```

代碼改變世界

【Android實驗】資料存儲與通路sqlite

繼續閱讀

【Android實驗】 UI設計-ListView

【Android實驗】元件通信Intent

【Android實驗】線程的使用-計時器

【Android實驗】第一個Android程式與Activity生命周期

Android程式示例

android實驗二之體重标準電腦

Android實驗九之天氣預報

【Android實驗】 資料存儲與通路sqlite

繼續閱讀

【Android實驗】資料存儲與通路sqlite