Android实验SQLite_Johngo学长

实验六 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){

        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


<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();
    }

    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);
        }

        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);
        myList.setAdapter(simpleAdapter);
    }

    public List<String[]> queryAll(){
        List<String[]> list = new ArrayList<>();

        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"));
                String[] strings = new String[]{teacherId,name,sex,wage};
                list.add(strings);
            } while (cursor.moveToNext());
        }
        cursor.close();
        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


<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


<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


<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();
    }

}

Original: https://blog.csdn.net/qq_54900427/article/details/122135953
Author: Web_Ranger
Title: Android实验SQLite

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/815985/

转载文章受原作者版权保护。转载请注明原作者出处！

2024 年 5 月
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Android实验SQLite