实验六 SQLite

一、实验目的及任务

1. 熟悉数据库SQLite的操作方法

2. SQLiteDatabase操作类，SQLiteOpenHelper辅助类的使用

二、实验环境

1. Jdk

2. Android Studio

3. 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

<LinearLayoutandroid:id="@+id/tva"android:layout_width="match_parent"android:layout_height="match_parent"android:orientation="vertical"><TableLayoutandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:stretchColumns="*"android:padding="3dip"><TableRow><TextViewandroid: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"/><TextViewandroid: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"/><TextViewandroid: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"/><TextViewandroid: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><ListViewandroid:id="@+id/mylistA"android:layout_width="match_parent"android:layout_height="wrap_content">ListView>LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="match_parent"><RelativeLayoutandroid:layout_width="match_parent"android:layout_height="match_parent"><FrameLayoutandroid:id="@+id/FrameLayout"android:layout_width="match_parent"android:layout_height="match_parent"android:layout_above="@+id/LinearLayout1"/><TableLayoutandroid:id="@+id/LinearLayout1"android:layout_width="fill_parent"android:layout_height="wrap_content"android:stretchColumns="*"android:padding="3dip"android:layout_alignParentBottom="true"><TableRow><Buttonandroid: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"/><Buttonandroid: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"/><Buttonandroid: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"/><Buttonandroid: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

<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"><LinearLayoutandroid:id="@+id/ll_view"android:gravity="center"android:orientation="horizontal"android:layout_width="match_parent"android:layout_height="wrap_content"><TableLayoutandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:stretchColumns="*"android:padding="3dip"><TableRow><TextViewandroid:id="@+id/teacherId"android:layout_width="1dip"android:layout_height="match_parent"android:textAlignment="center"android:padding="10sp"android:text="教师号"/><TextViewandroid:id="@+id/name"android:layout_width="1dip"android:layout_height="match_parent"android:textAlignment="center"android:padding="10sp"android:text="姓名"/><TextViewandroid:id="@+id/sex"android:layout_width="1dip"android:layout_height="match_parent"android:textAlignment="center"android:padding="10sp"android:text="性别"/><TextViewandroid: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;@Overrideprotected 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() {//路由到添加教师界面@Overridepublic 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() {//路由到删除教师界面@Overridepublic 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() {//路由到修改教师界面@Overridepublic 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中的位置的idSimpleAdapter 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();//关闭cursorreturn 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

<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"><LinearLayoutandroid:id="@+id/tvb"android:layout_width="match_parent"android:layout_height="match_parent"android:orientation="vertical"><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师编号: "android:padding="20sp"/><EditTextandroid:id="@+id/addTeacherId"android:layout_width="257dp"android:layout_height="wrap_content"/>LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师姓名: "android:padding="20sp"/><EditTextandroid:id="@+id/addName"android:layout_width="257dp"android:layout_height="wrap_content" />LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师性别: "android:padding="20sp"/><EditTextandroid:id="@+id/addTeacherSex"android:layout_width="257dp"android:layout_height="wrap_content" />LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师工资: "android:padding="20sp"/><EditTextandroid:id="@+id/addTeacherWage"android:layout_width="257dp"android:layout_height="wrap_content" />LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:gravity="center"><Buttonandroid: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;@Overrideprotected 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

<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"><LinearLayoutandroid:id="@+id/tvb"android:layout_width="match_parent"android:layout_height="match_parent"android:orientation="vertical"android:layout_marginTop="100sp"><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师编号: "android:padding="20sp"/><EditTextandroid:id="@+id/deleteTeacherId"android:layout_width="257dp"android:layout_height="wrap_content"/>LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:gravity="center"><Buttonandroid: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;@Overrideprotected 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

<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"><LinearLayoutandroid:id="@+id/tvb"android:layout_width="match_parent"android:layout_height="match_parent"android:orientation="vertical"><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师编号: "android:padding="20sp"/><EditTextandroid:id="@+id/updateTeacherId"android:layout_width="257dp"android:layout_height="wrap_content"/>LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师姓名: "android:padding="20sp"/><EditTextandroid:id="@+id/updateName"android:layout_width="257dp"android:layout_height="wrap_content" />LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师性别: "android:padding="20sp"/><EditTextandroid:id="@+id/updateTeacherSex"android:layout_width="257dp"android:layout_height="wrap_content" />LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:orientation="horizontal"android:gravity="center"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:text="教师工资: "android:padding="20sp"/><EditTextandroid:id="@+id/updateTeacherWage"android:layout_width="257dp"android:layout_height="wrap_content" />LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"android:gravity="center"><Buttonandroid: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;@Overrideprotected 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();}}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！