实验六 SQLite
一、实验目的及任务
-
熟悉数据库SQLite的操作方法
-
SQLiteDatabase操作类，SQLiteOpenHelper辅助类的使用
二、实验环境
-
Jdk
-
Android Studio
-
Android SDK
三、实验步骤
做一个教师管理系统。要求：
（1）使用Sqlite数据库，创建TeacherDB，包含teacher表
（2）包括增加、删除、修改、查询按钮。
（3）包括教师号、姓名、性别、工资4个输入框。
（4）当单击增加按钮时，将输入的教师号、姓名、性别、工资插入到teacher表中。
（5）当单击删除按钮时，根据输入的教师号则从数据库中删除此条记录。
（6）当单击修改按钮时，根据输入的教师号、姓名、性别、工资修改到教师号对应的记录上，其中教师号不允许修改。
（7）当单击查询按钮时，根据教师号、姓名、性别模糊查询，并将结果显示到下面的文本区中。
实验结果
代码
项目目录
构建数据库的类: TeacherSQLiteOpenHelper.java
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class TeacherSQLiteOpenHelper extends SQLiteOpenHelper {
private Context context;
//用来定义数据库的名称，数据库查询的结果集，数据库的版本
public TeacherSQLiteOpenHelper(Context context){
super(context,"TeacherDB.db",null,5);
this.context = context;
}
//数据库第一次被创建时调用该方法
public void onCreate(SQLiteDatabase db){
//初始化数据库的表结构，执行一条建表的SQL语句
db.execSQL("create table teacher" +
"(id integer primary key autoincrement,teacherId varchar(20)," +
"name varchar(20),sex varchar(20),wage int )");
}
//当数据库的版本号增加时调用
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
db.execSQL("alter table teacher add account varchar(20)");
}
}
根布局layout
<LinearLayout
android:id="@+id/tva"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<TableLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:stretchColumns="*"
android:padding="3dip"
>
<TableRow>
<TextView
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:background="@drawable/retangle"
android:padding="10sp"
android:text="教师号"
android:textColor="#000"
android:textSize="20sp"/>
<TextView
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:background="@drawable/retangle"
android:padding="10sp"
android:text="姓名"
android:textColor="#000"
android:textSize="20sp"/>
<TextView
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:background="@drawable/retangle"
android:padding="10sp"
android:text="性别"
android:textColor="#000"
android:textSize="20sp"/>
<TextView
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:background="@drawable/retangle"
android:padding="10sp"
android:text="工资"
android:textColor="#000"
android:textSize="20sp"/>
</TableRow>
</TableLayout>
<ListView
android:id="@+id/mylistA"
android:layout_width="match_parent"
android:layout_height="wrap_content">
</ListView>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent">
<RelativeLayout
android:layout_width="match_parent"
android:layout_height="match_parent">
<FrameLayout
android:id="@+id/FrameLayout"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_above="@+id/LinearLayout1"/>
<TableLayout
android:id="@+id/LinearLayout1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:stretchColumns="*"
android:padding="3dip"
android:layout_alignParentBottom="true"
>
<TableRow>
<Button
android:id="@+id/btnA"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询"
android:textColor="#fff"
android:background="#2C2E30"
android:paddingTop="10sp"
android:paddingBottom="10sp"/>
<Button
android:id="@+id/btnB"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="添加"
android:textColor="#fff"
android:background="#2C2E30"
android:paddingTop="10sp"
android:paddingBottom="10sp"/>
<Button
android:id="@+id/btnC"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除"
android:textColor="#fff"
android:background="#2C2E30"
android:paddingTop="10sp"
android:paddingBottom="10sp"/>
<Button
android:id="@+id/btnD"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改"
android:textColor="#fff"
android:background="#2C2E30"
android:paddingTop="10sp"
android:paddingBottom="10sp"/>
</TableRow>
</TableLayout>
</RelativeLayout>
</LinearLayout>
listView布局文件 message.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"
xmlns:tools="http://schemas.android.com/tools"
android:layout_height="match_parent"
tools:context=".MainActivity">
<LinearLayout
android:id="@+id/ll_view"
android:gravity="center"
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TableLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:stretchColumns="*"
android:padding="3dip"
>
<TableRow>
<TextView
android:id="@+id/teacherId"
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:padding="10sp"
android:text="教师号"/>
<TextView
android:id="@+id/name"
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:padding="10sp"
android:text="姓名"/>
<TextView
android:id="@+id/sex"
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:padding="10sp"
android:text="性别"/>
<TextView
android:id="@+id/wage"
android:layout_width="1dip"
android:layout_height="match_parent"
android:textAlignment="center"
android:padding="10sp"
android:text="工资"/>
</TableRow>
</TableLayout>
</LinearLayout>
</LinearLayout>
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MainActivity extends AppCompatActivity {
TeacherSQLiteOpenHelper teacherSQLiteOpenHelper;
private SQLiteDatabase db;
Button btnA;
Button btnB;
Button btnC;
Button btnD;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
teacherSQLiteOpenHelper = new TeacherSQLiteOpenHelper(this);
db = teacherSQLiteOpenHelper.getWritableDatabase();
init();//初始化
btnA = findViewById(R.id.btnA);
btnB = findViewById(R.id.btnB);
btnB.setOnClickListener(new View.OnClickListener() {//路由到添加教师界面
@Override
public void onClick(View view) {
Intent i = new Intent();
i.setClass(MainActivity.this , AddTeacher.class);
startActivityForResult(i,1);
}
});
btnC = findViewById(R.id.btnC);
btnC.setOnClickListener(new View.OnClickListener() {//路由到删除教师界面
@Override
public void onClick(View view) {
Intent i = new Intent();
i.setClass(MainActivity.this , DeleteTeacher.class);
startActivityForResult(i,2);
}
});
btnD = findViewById(R.id.btnD);
btnD.setOnClickListener(new View.OnClickListener() {//路由到修改教师界面
@Override
public void onClick(View view) {
Intent i = new Intent();
i.setClass(MainActivity.this , UpdateTeacher.class);
startActivityForResult(i,3);
}
});
printAll();
}
//接收Intent跳转回来
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
init();//跳回后刷新
}
//初始化, 也做刷新
public void init(){
List<String[]> list = queryAll();//找到好几行 每行包含完整教师信息的数据
List<Map<String,Object>> listItems=new ArrayList<>();
for(int i=0;i<list.size();i++){
Map<String,Object> listItem=new HashMap<>();
listItem.put("teacherId",list.get(i)[0]);
listItem.put("name",list.get(i)[1]);
listItem.put("sex",list.get(i)[2]);
listItem.put("wage",list.get(i)[3]);
listItems.add(listItem);
}
//new一个SimpleAdapter对象,用于适配listView 这个对象的构造函数需要5个参数
//当前context的上下文
//一个包含了map元素的list列表
//描述了这个ListView的layout布局文件
//每一个ListView中需要赋值的元素的在map中的index
//每一个ListView中需要赋值的元素在layout中的位置的id
SimpleAdapter simpleAdapter=new SimpleAdapter(this, listItems, R.layout.message,
new String[]{"teacherId","name","sex","wage"}, new int[]{R.id.teacherId,R.id.name, R.id.sex,R.id.wage});
ListView myList = findViewById(R.id.mylistA);//通过id找到引用了这个listView的位置, 并依据此id构造listView对象
myList.setAdapter(simpleAdapter);//将适配器适配器listView
}
//查询所有教师信息
public List<String[]> queryAll(){
List<String[]> list = new ArrayList<>();
//查询到的所有的值都会存入cursor中
Cursor cursor = db.query("teacher",null, null, null, null, null, null);
if(cursor.moveToFirst()){//遍历cursor中的所有信息
do {
String teacherId = cursor.getString(cursor.getColumnIndex("teacherId"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String sex = cursor.getString(cursor.getColumnIndex("sex"));
String wage = cursor.getString(cursor.getColumnIndex("wage"));
String[] strings = new String[]{teacherId,name,sex,wage};
list.add(strings);//将遍历得到的信息放入list中
} while (cursor.moveToNext());
}
cursor.close();//关闭cursor
return list;
}
//插入数据
public void insertData(String teacherId, String name, String sex, int wage){
db.execSQL("insert into teacher(teacherId, name, sex, wage) values(?, ?, ?, ?)",
new Object[]{teacherId,name,sex,wage});
Toast toast=Toast.makeText(getApplicationContext(), "添加成功!", Toast.LENGTH_SHORT);
toast.show();
}
//用户打印所有数据库信息于控制台
public void printAll(){
Cursor cursor = db.query("teacher",null, null, null, null, null, null);
if(cursor.moveToFirst()){
do {
String teacherId = cursor.getString(cursor.getColumnIndex("teacherId"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String sex = cursor.getString(cursor.getColumnIndex("sex"));
String wage = cursor.getString(cursor.getColumnIndex("wage"));
System.out.println("teacherId: "+teacherId);
System.out.println("name: "+name);
System.out.println("sex: "+sex);
System.out.println("wage: "+wage);
} while (cursor.moveToNext());
}
cursor.close();
}
//用于添加一点初始化数据
public void initData(){
insertData("123456","老师1","女",15000);
insertData("123457","老师2","男",15000);
insertData("123458","老师3","男",15000);
insertData("123459","teacher1","male",15000);
}
}
添加教师界面 activity_add_teacher.xml
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
tools:context=".AddTeacher">
<LinearLayout
android:id="@+id/tvb"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师编号: "
android:padding="20sp"/>
<EditText
android:id="@+id/addTeacherId"
android:layout_width="257dp"
android:layout_height="wrap_content"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师姓名: "
android:padding="20sp"/>
<EditText
android:id="@+id/addName"
android:layout_width="257dp"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师性别: "
android:padding="20sp"/>
<EditText
android:id="@+id/addTeacherSex"
android:layout_width="257dp"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师工资: "
android:padding="20sp"/>
<EditText
android:id="@+id/addTeacherWage"
android:layout_width="257dp"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center">
<Button
android:layout_width="200sp"
android:layout_height="wrap_content"
android:text="提交添加"
android:onClick="doAdd"/>
</LinearLayout>
</LinearLayout>
</androidx.constraintlayout.widget.ConstraintLayout>
添加教师 Java业务代码 AddTeacher.java
public class AddTeacher extends AppCompatActivity {
TeacherSQLiteOpenHelper teacherSQLiteOpenHelper;
private SQLiteDatabase db;
EditText addTeacherId;
EditText addTeacherName;
EditText addTeacherSex;
EditText addTeacherWage;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_add_teacher);
teacherSQLiteOpenHelper = new TeacherSQLiteOpenHelper(this);
db = teacherSQLiteOpenHelper.getWritableDatabase();
addTeacherId = findViewById(R.id.addTeacherId);
addTeacherName = findViewById(R.id.addName);
addTeacherSex = findViewById(R.id.addTeacherSex);
addTeacherWage = findViewById(R.id.addTeacherWage);
}
public void doAdd(View view){
String id = addTeacherId.getText().toString();
String name = addTeacherName.getText().toString();
String sex = addTeacherSex.getText().toString();
System.out.println(id+name+sex);
int wage = Integer.parseInt(addTeacherWage.getText().toString());
insertData(id,name,sex,wage);
Intent i = new Intent();
setResult(1,i);
finish();
}
public void insertData(String teacherId, String name, String sex, int wage){
db.execSQL("insert into teacher(teacherId, name, sex, wage) values(?, ?, ?, ?)",
new Object[]{teacherId,name,sex,wage});
Toast toast=Toast.makeText(getApplicationContext(), "添加成功!", Toast.LENGTH_SHORT);
toast.show();
}
}
删除教师界面代码 activity_delete_teacher.xml
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
tools:context=".DeleteTeacher">
<LinearLayout
android:id="@+id/tvb"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:layout_marginTop="100sp">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师编号: "
android:padding="20sp"/>
<EditText
android:id="@+id/deleteTeacherId"
android:layout_width="257dp"
android:layout_height="wrap_content"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center">
<Button
android:layout_width="200sp"
android:layout_height="wrap_content"
android:text="提交删除"
android:onClick="doDelete"
android:layout_marginTop="20sp"/>
</LinearLayout>
</LinearLayout>
</androidx.constraintlayout.widget.ConstraintLayout>
删除教师Java业务代码 DeleteTeacher.java
package com.experiment.ep6;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
public class DeleteTeacher extends AppCompatActivity {
TeacherSQLiteOpenHelper teacherSQLiteOpenHelper;
private SQLiteDatabase db;
EditText deleteTeacherId;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_delete_teacher);
teacherSQLiteOpenHelper = new TeacherSQLiteOpenHelper(this);
db = teacherSQLiteOpenHelper.getWritableDatabase();
deleteTeacherId = findViewById(R.id.deleteTeacherId);
}
public void doDelete(View view){
String id = deleteTeacherId.getText().toString();
deleteData(id);
Intent i = new Intent();
setResult(2,i);
finish();
}
public void deleteData(String teacherId){
db.execSQL("delete from teacher where teacherId = ?", new Object[]{teacherId});
Toast toast=Toast.makeText(getApplicationContext(), "删除成功!", Toast.LENGTH_SHORT);
toast.show();
}
}
修改教师界面代码 activity_update_teacher.xml
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
tools:context=".UpdateTeacher">
<LinearLayout
android:id="@+id/tvb"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师编号: "
android:padding="20sp"/>
<EditText
android:id="@+id/updateTeacherId"
android:layout_width="257dp"
android:layout_height="wrap_content"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师姓名: "
android:padding="20sp"/>
<EditText
android:id="@+id/updateName"
android:layout_width="257dp"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师性别: "
android:padding="20sp"/>
<EditText
android:id="@+id/updateTeacherSex"
android:layout_width="257dp"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="教师工资: "
android:padding="20sp"/>
<EditText
android:id="@+id/updateTeacherWage"
android:layout_width="257dp"
android:layout_height="wrap_content" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center">
<Button
android:layout_width="200sp"
android:layout_height="wrap_content"
android:text="提交修改"
android:onClick="doUpdate"/>
</LinearLayout>
</LinearLayout>
</androidx.constraintlayout.widget.ConstraintLayout>
修改教师Java业务代码UpdateTeacher.java
public class UpdateTeacher extends AppCompatActivity {
TeacherSQLiteOpenHelper teacherSQLiteOpenHelper;
private SQLiteDatabase db;
EditText updateTeacherId;
EditText updateTeacherName;
EditText updateTeacherSex;
EditText updateTeacherWage;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_update_teacher);
teacherSQLiteOpenHelper = new TeacherSQLiteOpenHelper(this);
db = teacherSQLiteOpenHelper.getWritableDatabase();
updateTeacherId = findViewById(R.id.updateTeacherId);
updateTeacherName = findViewById(R.id.updateName);
updateTeacherSex = findViewById(R.id.updateTeacherSex);
updateTeacherWage = findViewById(R.id.updateTeacherWage);
}
public void doUpdate(View view){
String id = updateTeacherId.getText().toString();
String name = updateTeacherName.getText().toString();
String sex = updateTeacherSex.getText().toString();
int wage = Integer.parseInt(updateTeacherWage.getText().toString());
updateData(id,name,sex,wage);
Intent i = new Intent();
setResult(3,i);
finish();
}
public void updateData(String teacherId, String name, String sex, int wage){
db.execSQL("update teacher set name=?, sex=?, wage=? where teacherId=?",
new Object[]{name,sex,wage,teacherId});
Toast toast=Toast.makeText(getApplicationContext(), "更新成功!", Toast.LENGTH_SHORT);
toast.show();
}
}
355
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
