实验目的
分别使用sqlite3工具和Android代码的方式建立SQLite数据库。在完成建立数据库的工作后,编程实现基本的数据库操作功能,包括数据的添加、删除和更新。
实验要求
- 创建一个学生管理的应用,基本信息包含学生姓名,班级,学号。采用数据库存储这些信息。
- 应用应该至少包含信息录入和删除功能。
- 数据显示考虑采用ListView。
工程代码
项目工程结构图
MainActivity:
package wyx.exp_6;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import java.util.ArrayList;
import java.util.List;
public class MainActivity extends AppCompatActivity {
EditText e1;
EditText e2;
EditText e3;
EditText e4;
Button addone;
Button showall;
Button clearshow;
Button deleteall;
Button deleteone;
Button selectone;
Button updateone;
private DBAdapter db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
e1=(EditText)findViewById(R.id.name);
e2=(EditText)findViewById(R.id.age);
e3=(EditText)findViewById(R.id.height);
e4=(EditText)findViewById(R.id.ID);
addone=(Button)findViewById(R.id.addone);
showall=(Button)findViewById(R.id.showall);
clearshow=(Button)findViewById(R.id.clearshow);
deleteall=(Button)findViewById(R.id.deleteall);
deleteone=(Button)findViewById(R.id.deleteone);
selectone=(Button)findViewById(R.id.selectone);
updateone=(Button)findViewById(R.id.updateone);
final ListView lv1=(ListView)findViewById(R.id.lv1);
final List<String> list=new ArrayList<String>();
final ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,R.layout.list_item, list );
db=new DBAdapter(this);
db.open();
addone.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
People people=new People();
people.Name=e1.getText().toString();
people.Age=Integer.parseInt(e2.getText().toString());
people.Height=Float.parseFloat(e3.getText().toString());
db.insert(people);
String s="添加成功!";
list.add(s);
lv1.setAdapter(adapter);
}
});
showall.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
list.clear();
People[] peoples=db.getAllData();
if(peoples==null)
{
String s="数据库为空!!";
list.add(s);
lv1.setAdapter(adapter);
}
else{
for(int i=0;i<peoples.length;i++)
{
People people=peoples[i];
list.add(people.toString());
lv1.setAdapter(adapter);
}
}
}
});
selectone.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
list.clear();
People[] peoples=db.getOneData(Long.parseLong(e4.getText().toString()));
if(peoples==null)
{
String s="没有找到相应的数据!!";
list.add(s);
lv1.setAdapter(adapter);
}
else{
list.add(peoples[0].toString());
lv1.setAdapter(adapter);
}
}
});
deleteone.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
list.clear();
People[] peoples=db.getOneData(Long.parseLong(e4.getText().toString()));
if(peoples==null)
{
String s="没有找到相应的数据!!";
list.add(s);
lv1.setAdapter(adapter);
}
else{
db.deleteOneData(Long.parseLong(e4.getText().toString()));
}
}
});
deleteall.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
db.deleteAllData();
adapter.clear();
adapter.notifyDataSetChanged() ;
}
});
updateone.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
list.clear();
People[] peoples=db.getOneData(Long.parseLong(e4.getText().toString()));
if(peoples==null)
{
String s="没有找到相应的数据!!";
list.add(s);
lv1.setAdapter(adapter);
}
else{
People people=peoples[0];
people.Name=e1.getText().toString();
people.Age=Integer.parseInt(e2.getText().toString());
people.Height=Float.parseFloat(e3.getText().toString());
db.updateOneData(Long.parseLong(e4.getText().toString()),people);
}
}
});
clearshow.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
adapter.clear();
adapter.notifyDataSetChanged() ;
}
});
}
}
DBAdapter
package wyx.exp_6;
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 wyx on 2018/10/29.
*/
public class DBAdapter {
private static final String DB_NAME = "people.db";
private static final String DB_TABLE = "peopleinfo";
private static final int DB_VERSION = 1;
public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_AGE = "age";
public static final String KEY_HEIGHT = "height";
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_AGE+ " integer," + KEY_HEIGHT + " float);";
@Override
public void onCreate(SQLiteDatabase _db) {
_db.execSQL(DB_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
_db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
onCreate(_db);
}
}
public DBAdapter(Context _context) {
context = _context;
}
public void open() throws SQLiteException {
dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, DB_VERSION);
try {
db = dbOpenHelper.getWritableDatabase();
}catch (SQLiteException ex) {
db = dbOpenHelper.getReadableDatabase();
}
}
public void close() {
if (db != null){
db.close();
db = null;
}
}
public long insert(People people) {
ContentValues newValues = new ContentValues();
newValues.put(KEY_NAME, people.Name);
newValues.put(KEY_AGE, people.Age);
newValues.put(KEY_HEIGHT, people.Height);
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 , People people){
ContentValues updateValues = new ContentValues();
updateValues.put(KEY_NAME, people.Name);
updateValues.put(KEY_AGE, people.Age);
updateValues.put(KEY_HEIGHT, people.Height);
return db.update(DB_TABLE, updateValues, KEY_ID + "=" + id, null);
}
private People[] ConvertToPeople(Cursor cursor){
int resultCounts = cursor.getCount();
if (resultCounts == 0 || !cursor.moveToFirst()){
return null;
}
People[] peoples = new People[resultCounts];
for (int i = 0 ; i<resultCounts; i++){
peoples[i] = new People();
peoples[i].ID = cursor.getInt(0);
peoples[i].Name = cursor.getString(cursor.getColumnIndex(KEY_NAME));
peoples[i].Age = cursor.getInt(cursor.getColumnIndex(KEY_AGE));
peoples[i].Height = cursor.getFloat(cursor.getColumnIndex(KEY_HEIGHT));
cursor.moveToNext();
}
return peoples;
}
public People[] getOneData(long id) {
Cursor results = db.query(DB_TABLE, new String[] { KEY_ID, KEY_NAME, KEY_AGE, KEY_HEIGHT}, KEY_ID + "=" + id, null, null, null, null);
return ConvertToPeople(results);
}
public People[] getAllData() {
Cursor results = db.query(DB_TABLE, new String[] { KEY_ID, KEY_NAME, KEY_AGE, KEY_HEIGHT}, null, null, null, null, null);
return ConvertToPeople(results);
}
}
People:
package wyx.exp_6;
/**
* Created by wyx on 2018/10/29.
*/
public class People {
public int ID = -1;
public String Name;
public int Age;
public float Height;
@Override
public String toString() {
String result="";
result += "ID:" + this.ID + ",";
result += "姓名:" + this.Name + ",";
result += "年龄:" + this.Age + ",";
result += "身高:" + this.Height;
return result;
}
}
activity_main.xml:
<?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="wyx.exp_6.MainActivity"
tools:layout_editor_absoluteX="0dp"
tools:layout_editor_absoluteY="81dp">
<LinearLayout
android:id="@+id/linearLayout"
android:layout_width="370dp"
android:layout_height="200dp"
android:layout_marginEnd="6dp"
android:layout_marginStart="6dp"
android:layout_marginTop="8dp"
android:orientation="vertical"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintRight_toRightOf="parent"
app:layout_constraintTop_toTopOf="parent"
tools:layout_constraintLeft_creator="1"
tools:layout_constraintRight_creator="1"
tools:layout_constraintTop_creator="1">
<LinearLayout
android:layout_width="370dp"
android:layout_height="50dp"
android:orientation="horizontal">
<TextView
android:layout_width="35dp"
android:layout_height="29dp"
android:text="姓名:" />
<EditText
android:id="@+id/name"
android:layout_width="100dp"
android:layout_height="40dp" />
</LinearLayout>
<LinearLayout
android:layout_width="370dp"
android:layout_height="50dp"
android:orientation="horizontal">
<TextView
android:layout_width="35dp"
android:layout_height="29dp"
android:text="年龄:" />
<EditText
android:id="@+id/age"
android:layout_width="100dp"
android:layout_height="40dp" />
<Button
android:layout_width="80dp"
android:layout_height="40dp"
android:text="添加数据"
android:id="@+id/addone" />
<Button
android:layout_width="80dp"
android:layout_height="40dp"
android:text="全部显示"
android:id="@+id/showall" />
</LinearLayout>
<LinearLayout
android:layout_width="370dp"
android:layout_height="50dp"
android:orientation="horizontal">
<TextView
android:layout_width="35dp"
android:layout_height="29dp"
android:text="身高:" />
<EditText
android:id="@+id/height"
android:layout_width="100dp"
android:layout_height="40dp" />
<Button
android:layout_width="80dp"
android:layout_height="40dp"
android:text="清除显示"
android:id="@+id/clearshow" />
<Button
android:layout_width="80dp"
android:layout_height="40dp"
android:text="全部删除"
android:id="@+id/deleteall" />
</LinearLayout>
<LinearLayout
android:layout_width="370dp"
android:layout_height="50dp"
android:orientation="horizontal">
<TextView
android:layout_width="20dp"
android:layout_height="29dp"
android:text="ID:" />
<EditText
android:id="@+id/ID"
android:layout_width="50dp"
android:layout_height="40dp" />
<Button
android:id="@+id/deleteone"
android:layout_width="80dp"
android:layout_height="40dp"
android:text="ID删除" />
<Button
android:id="@+id/selectone"
android:layout_width="80dp"
android:layout_height="40dp"
android:text="ID查询" />
<Button
android:id="@+id/updateone"
android:layout_width="80dp"
android:layout_height="40dp"
android:text="ID更新" />
</LinearLayout>
</LinearLayout>
<ListView
android:id="@+id/lv1"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:longClickable="true"
/>
</LinearLayout>
list_item.xml
<?xml version="1.0" encoding="utf-8"?>
<TextView xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/text"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_gravity="center_vertical"
android:textColor="#0000FF"
android:textSize="16dp" />
AndroidManifest.xml:
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="wyx.exp_6">
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
实现效果