【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