安卓：SqlLite简单案例：实现学生列表的增删改查功能

最新推荐文章于 2025-05-13 15:50:04 发布

原创最新推荐文章于 2025-05-13 15:50:04 发布 · 3.7k 阅读

78 ·

CC 4.0 BY-SA版权

文章标签：

#移动开发 #安卓 #java

项目成果展示

四个按钮分别对应增删改查四个功能，当点击每一个学生的ListView组件时，会将信息映射到上面的输入框中，随后可以进行更改或者其他操作

项目结构：

如果想深究项目背后的逻辑请先细细阅读：安卓学习笔记——SQLite学习

1、数据库的创建——MyDatabaseHelper()：

该类继承于SQLiteOpenHelper()，SQLiteOpenHelper()：简言之就是用来创建数据库以及版本更新

2、数据库的CRUD(增删改查)——StudentBpo：

该类通过自定义的SQLiteOpenHelper类中该类的构造方法得到SQLiteOpenHelper dbHelper；然后通过dbHelper.getReadableDatabase()方法得到SQLiteDatabase类db，最后通过SQLiteDatabase类中的execSQL()方法完成增删改的部分，通过SQLiteDatabase类中的rawQuery()方法完成查的部分

3、实体类Student的创建

4、程序主代码：StudentManageActivity来通过实例完成CRUD（增删改查）的具体应用

代码部分：

xml：

activity_student_manager.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"
    >
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:text="学号："
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <EditText
            android:id="@+id/txtStudentId"
            android:text=""
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
    </LinearLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:text="姓名："
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <EditText
            android:id="@+id/txtStudentName"
            android:text=""
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
    </LinearLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:text="性别："
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <EditText
            android:id="@+id/txtSex"
            android:text=""
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
    </LinearLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:text="年龄："
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <EditText
            android:id="@+id/txtAge"
            android:text=""
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
    </LinearLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content">
        <Button
            android:id="@+id/btnAdd"
            android:text="添加"
            android:onClick="btnAddClick"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <Button
            android:id="@+id/btnDelete"
            android:text="删除"
            android:onClick="btnDeleteClick"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <Button
            android:id="@+id/btnUpdate"
            android:text="修改"
            android:onClick="btnUpdateClick"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>
        <Button
            android:id="@+id/btnQuery"
            android:text="查询"
            android:onClick="btnQueryClick"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="30dp"/>

    </LinearLayout>

    <ListView
        android:id="@+id/listViewStudent"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:dividerHeight="1dp"
        android:divider="@color/colorGreen"></ListView>
</LinearLayout>

student_item_header.xml
学生类表的头部布局文件

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="match_parent"
    android:layout_height="match_parent">
    <!--  设控件的宽度为0，然后设置layout_weight，让他们按比例瓜分整个这一行的宽度,从而保证每一行的各列宽度都一样 -->
    <TextView
        android:id="@+id/studentId"
        android:text="学号"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"
        android:textColor="#f0f"
        android:paddingLeft="10dp"
        android:layout_weight="2"/>
    <TextView
        android:id="@+id/studentName"
        android:text="姓名"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"
        android:textColor="#f0f"
        android:paddingLeft="10dp"
        android:layout_weight="1"/>
    <TextView
        android:id="@+id/sex"
        android:text="性别"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"
        android:textColor="#f0f"
        android:paddingLeft="10dp"
        android:layout_weight="1"/>
    <TextView
        android:id="@+id/age"
        android:text="年龄"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"
        android:textColor="#f0f"
        android:paddingLeft="10dp"
        android:layout_weight="1"/>
</LinearLayout>

java：
StudentManageActivity.java

public class StudentManageActivity extends AppCompatActivity {
    EditText txtStudentId,txtStudentName,txtSex,txtAge;
    Button btnAdd,btnDelete,btnUpdate,btnQuery;
    ListView listViewStudent;
    String currentStudentId="";
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_student_manage);
        txtStudentId=(EditText)findViewById(R.id.txtStudentId);
        txtStudentName=(EditText)findViewById(R.id.txtStudentName);
        txtSex=(EditText)findViewById(R.id.txtSex);
        txtAge=(EditText)findViewById(R.id.txtAge);
        btnUpdate=(Button)findViewById(R.id.btnUpdate);
        listViewStudent=(ListView)findViewById(R.id.listViewStudent);
        //把student_item_header.xml实例化为一个view，然后添加到listViewStudent作为表头
        View view= LayoutInflater.from(this).inflate(R.layout.student_item_header,null);
        listViewStudent.addHeaderView(view);
        listViewStudent.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                TextView studentId=(TextView)view.findViewById(R.id.studentId);
                TextView studentName=(TextView)view.findViewById(R.id.studentName);
                TextView sex=(TextView)view.findViewById(R.id.sex);
                TextView age=(TextView)view.findViewById(R.id.age);
                txtStudentId.setText(studentId.getText().toString());
                txtStudentName.setText(studentName.getText().toString());
                txtSex.setText(sex.getText().toString());
                txtAge.setText(age.getText().toString());
                currentStudentId=studentId.getText().toString();
            }
        });
    }
    public void btnAddClick(View view){
        Student student=new Student(txtStudentId.getText().toString(),txtStudentName.getText().toString(),txtSex.getText().toString(),Integer.parseInt(txtAge.getText().toString()));
        try {
            StudentBpo.insert(StudentManageActivity.this, student);
            btnQueryClick(view);
            AlertDialog.Builder builder = new AlertDialog.Builder(StudentManageActivity.this);
            //    设置Title的内容
            builder.setTitle("弹出警告框");
            //    设置Content来显示一个信息
            builder.setMessage("确定删除吗？");
            builder.show();
        }catch (Exception e){
            Toast.makeText(this,"添加学生信息出错"+e.getMessage(),Toast.LENGTH_LONG).show();
            System.out.println("添加学生信息出错: "+e.getMessage());
            Log.i("添加学生信息出错","添加学生信息出错  : "+e.getMessage());
        }
    }
    public void btnDeleteClick(View view){
        StudentBpo.delete(StudentManageActivity.this,txtStudentId.getText().toString());
        btnQueryClick(view);
    }
    public void btnUpdateClick(View view){
        Student student=new Student(txtStudentId.getText().toString(),txtStudentName.getText().toString(),txtSex.getText().toString(),Integer.parseInt(txtAge.getText().toString()));
        StudentBpo.update(StudentManageActivity.this,student,currentStudentId);
        btnQueryClick(view);
    }
    public void btnQueryClick(View view){
        //Student student=new Student(txtStudentId.getText().toString(),txtStudentName.getText().toString(),txtSex.getText().toString(),Integer.parseInt(txtAge.getText().toString()));
        List<Map<String,String>> studentList=StudentBpo.getAllStudent(this);
        SimpleAdapter adapter=new SimpleAdapter(this,studentList,R.layout.student_item,new String[]{"studentId","studentName","sex","age"},new int[]{R.id.studentId,R.id.studentName,R.id.sex,R.id.age});
        listViewStudent.setAdapter(adapter);
    }
}

自定义工具类：

1、数据库的创建SQLiteOpenHelper()----->MyDatabaseHelper()

public class MyDatabaseHelper extends SQLiteOpenHelper {
    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }
    public MyDatabaseHelper(Context context, String name, int version) {
        super(context, name, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sqlCreateTableStudent="create table student(studentid text primary key,studentname text,sex text,age integer)";
        String sqlCreateTableCourse="create table course(courseid text primary key,coursename text,credit integer)";
        String sqlCreateTableSc="create table sc(studentid text primary key,courseid text,score integer)";
        db.execSQL(sqlCreateTableStudent);
        db.execSQL(sqlCreateTableCourse);
        db.execSQL(sqlCreateTableSc);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sqlCreateTableTeacher="create table teacher(teacherid text primary key,teachername text,sex text,age integer)";
        if(oldVersion==1&&newVersion==2)//当是版本号是从1升级到2的时候执行下面的创建teacher表的操作
            db.execSQL(sqlCreateTableTeacher);
    }
}

2、数据库的CRUD,SQLiteDatabase()---->StudentBpo

public class StudentBpo {
    //往数据库student表中插入一个学生
    public static void insert(Context context, Student student){
        MyDatabaseHelper dbHelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbHelper.getReadableDatabase();
        String insertSql="insert into student(studentid,studentname,sex,age) values(?,?,?,?)";
        db.execSQL(insertSql,new Object[]{student.getStudentId(),student.getStudentName(),student.getSex(),student.getAge()});
        db.close();
        System.out.println("添加学生成功"+student.getStudentId());
    }
    //把原来学号为oldStudentId的学生更新为student对象对应的学生
    public static void update(Context context, Student student,String oldStudentId){
        MyDatabaseHelper dbHelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbHelper.getReadableDatabase();
        String insertSql="update student set studentid=?,studentname=?,sex=?,age=? where studentid=?";
        db.execSQL(insertSql,new Object[]{student.getStudentId(),student.getStudentName(),student.getSex(),student.getAge(),oldStudentId});
        db.close();
        System.out.println("修改学生成功"+student.getStudentId());
    }
    //根据学号删除学生，
    public static void delete(Context context,String studentId){
        MyDatabaseHelper dbDelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbDelper.getReadableDatabase();
        String deleteSql="delete from student where studentid=?";
        db.execSQL(deleteSql,new String[]{studentId});
        db.close();
        System.out.println("删除学生成功"+studentId);
    }


    //列出全部的学生,学生列表是List<Map<String,String>>
    public static List<Map<String,String>> getAllStudent(Context context){
        List<Map<String,String>> studentList=new ArrayList<Map<String,String>>();
        MyDatabaseHelper dbDelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbDelper.getReadableDatabase();
        String selectSql="select studentid,studentname,sex,age  from student";
        Cursor cursor=db.rawQuery(selectSql,null);
        while(cursor.moveToNext()){
            Map<String,String> map=new HashMap<String,String>();
            map.put("studentId",cursor.getString(0));
            map.put("studentName",cursor.getString(1));
            map.put("sex",cursor.getString(2));
            map.put("age",cursor.getString(3));
            //()Student(cursor.getString(1),cursor.getString(2),cursor.getString(3),cursor.getInt(4));
            studentList.add(map);
            System.out.println("studentid="+map.get("studentId"));

        }
        cursor.close();
        db.close();
        return studentList;
    }
    //列出全部的学生，学生列表是List<Student>
    public static List<Student> getStudent(Context context){
        List<Student> studentList=new ArrayList<Student>();
        MyDatabaseHelper dbDelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbDelper.getReadableDatabase();
        String selectSql="select studentid,studentname,sex,age  from student";
        Cursor cursor=db.rawQuery(selectSql,null);
        while(cursor.moveToNext()){
            Student student=new Student(cursor.getString(0),cursor.getString(1),cursor.getString(2),cursor.getInt(3));
            studentList.add(student);
        }
        db.close();
        return studentList;
    }
    //根据学号查找符合条件的学生，
    public static Student getStudentByStudentId(Context context,String studentId){
        Student student=null;
        MyDatabaseHelper dbDelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbDelper.getReadableDatabase();
        String selectSql="select studentid,studentname,sex,age  from student where studentid=?";
        Cursor cursor=db.rawQuery(selectSql,new String[]{studentId});
        if(cursor.moveToNext()){
            student=new Student(cursor.getString(0),cursor.getString(1),cursor.getString(2),cursor.getInt(3));
        }
        db.close();
        return student;
    }
    //按学号、姓名、性别查找符合条件的学生
    public static List<Map<String,String>> getAllStudent(Context context,String studentId,String studentName,String sex){
        List<Map<String,String>> studentList=new ArrayList<Map<String,String>>();
        MyDatabaseHelper dbDelper=new MyDatabaseHelper(context,"studentDB",null,1);
        SQLiteDatabase db=dbDelper.getReadableDatabase();
        String selectSql="select studentid,studentname,sex,age  from student where studentid like ? and studentname like ? and sex like ?";
        Cursor cursor=db.rawQuery(selectSql,new String[]{"%"+studentId+"%","%"+studentName+"%","%"+sex+"%"});
        while(cursor.moveToNext()){
            Map<String,String> map=new HashMap<String,String>();
            map.put("studentId",cursor.getString(0));
            map.put("studentName",cursor.getString(1));
            map.put("sex",cursor.getString(2));
            map.put("age",cursor.getString(3));
            //()Student(cursor.getString(1),cursor.getString(2),cursor.getString(3),cursor.getInt(4));
            studentList.add(map);
            System.out.println("studentid="+map.get("studentId"));
        }
        cursor.close();
        db.close();
        return studentList;
    }
}

Student实体类
Studeng.java

package com.example.firstapplication.entity;

/**
 * Created by Administrator on 2018-04-25.
 */

public class Student {
    private String studentId,studentName,Sex;
    private int age;

    public String getStudentId() {
        return studentId;
    }

    public String getStudentName() {
        return studentName;
    }

    public String getSex() {
        return Sex;
    }

    public int getAge() {
        return age;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public void setSex(String sex) {
        Sex = sex;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Student(String studentId, String studentName, String sex, int age) {
        this.studentId = studentId;
        this.studentName = studentName;
        Sex = sex;
        this.age = age;
    }
    public Student(){}

    @Override
    public String toString() {
        return "Student{" +
                "studentId='" + studentId + '\'' +
                ", studentName='" + studentName + '\'' +
                ", Sex='" + Sex + '\'' +
                ", age=" + age +
                '}';
    }
}

关键代码分析：

布局文件：

布局文件有两个，第一个是主布局文件，具体实现效果如项目运行图所示。第二个为学生表头布局文件，既学号、姓名、性别、年龄等

java文件：

第一个为布局文件的主要java代码，该代码自上向下分别实现了如下功能：

1、把student_item_header.xml实例化为一个view，然后添加到listViewStudent作为表头

View view= LayoutInflater.from(this).inflate(R.layout.student_item_header,null);
        listViewStudent.addHeaderView(view);

2、为ListView设置单击事件，来将对应的学生数据映射到主布局文件的对应的输入框中

如图演示部分

对应代码部分：

//为ListView设置鼠标单击监听事件，实现数据的映射

listViewStudent.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                TextView studentId=(TextView)view.findViewById(R.id.studentId);
                TextView studentName=(TextView)view.findViewById(R.id.studentName);
                TextView sex=(TextView)view.findViewById(R.id.sex);
                TextView age=(TextView)view.findViewById(R.id.age);
                txtStudentId.setText(studentId.getText().toString());
                txtStudentName.setText(studentName.getText().toString());
                txtSex.setText(sex.getText().toString());
                txtAge.setText(age.getText().toString());
                currentStudentId=studentId.getText().toString();
            }
        });
    }

3、单击按钮实现添加功能

public void btnAddClick(View view){
        Student student=new Student(txtStudentId.getText().toString(),txtStudentName.getText().toString(),txtSex.getText().toString(),Integer.parseInt(txtAge.getText().toString()));
        try {
            StudentBpo.insert(StudentManageActivity.this, student);
            btnQueryClick(view);
            AlertDialog.Builder builder = new AlertDialog.Builder(StudentManageActivity.this);
            //    设置Title的内容
            builder.setTitle("弹出警告框");
            //    设置Content来显示一个信息
            builder.setMessage("确定删除吗？");
            builder.show();
        }catch (Exception e){
            Toast.makeText(this,"添加学生信息出错"+e.getMessage(),Toast.LENGTH_LONG).show();
            System.out.println("添加学生信息出错: "+e.getMessage());
            Log.i("添加学生信息出错","添加学生信息出错  : "+e.getMessage());
        }
    }

4、单击按钮实现删除功能

  public void btnDeleteClick(View view){
        StudentBpo.delete(StudentManageActivity.this,txtStudentId.getText().toString());
        btnQueryClick(view);
    }

5、单击按钮实现更新功能

 public void btnUpdateClick(View view){
        Student student=new Student(txtStudentId.getText().toString(),txtStudentName.getText().toString(),txtSex.getText().toString(),Integer.parseInt(txtAge.getText().toString()));
        StudentBpo.update(StudentManageActivity.this,student,currentStudentId);
        btnQueryClick(view);
    }

6、单击按钮实现查询功能

  public void btnQueryClick(View view){
        //Student student=new Student(txtStudentId.getText().toString(),txtStudentName.getText().toString(),txtSex.getText().toString(),Integer.parseInt(txtAge.getText().toString()));
        List<Map<String,String>> studentList=StudentBpo.getAllStudent(this);
        SimpleAdapter adapter=new SimpleAdapter(this,studentList,R.layout.student_item,new String[]{"studentId","studentName","sex","age"},new int[]{R.id.studentId,R.id.studentName,R.id.sex,R.id.age});
        listViewStudent.setAdapter(adapter);
    }

增删改查功能的实现所展示的代码均是封装好代码直接调用的成功代码。被封装的代码均在工具类StudentBpo类与实体类Student均写出，建议先将代码部署成功后再进行学习