Android-SQLite数据库实战

SQLite数据库实战项目

补充:由于需要recyclerview控件,具体如何使用可以参考

Android-第七节RecyclerView详解

1.建表

MySQLiteOpenHelper实体类中
在这里插入图片描述

package com.hnucm.a_test133;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql="create table student(id integer primary key autoincrement,stuid varchar(50), stuname varchar(20),stuclass varchar(20))";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

在activity中
在这里插入图片描述

package com.hnucm.a_test133;

import androidx.appcompat.app.AppCompatActivity;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        MySQLiteOpenHelper mySQLiteOpenHelper=new MySQLiteOpenHelper(this,"students.db",null,1);
        SQLiteDatabase sqLiteDatabase=mySQLiteOpenHelper.getWritableDatabase();
    }
}

在这里插入图片描述

2.编写ui

在这里插入图片描述

<?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=".MainActivity">


    <EditText
        android:id="@+id/editTextTextPersonName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="10dp"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="10dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="请输入学号"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/editTextTextPersonName2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="请输入姓名"
        app:layout_constraintStart_toStartOf="@+id/editTextTextPersonName"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonName" />

    <EditText
        android:id="@+id/editTextTextPersonName3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="请输入班级"
        app:layout_constraintStart_toStartOf="@+id/editTextTextPersonName2"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonName2" />

    <EditText
        android:id="@+id/editTextTextPersonName4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:hint="查询学生信息"
        app:layout_constraintStart_toStartOf="@+id/editTextTextPersonName3"
        app:layout_constraintTop_toBottomOf="@+id/editTextTextPersonName3" />

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="增加学生"
        app:layout_constraintBottom_toBottomOf="@+id/editTextTextPersonName3"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toEndOf="@+id/editTextTextPersonName3" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询学生"
        app:layout_constraintBottom_toBottomOf="@+id/editTextTextPersonName4"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toEndOf="@+id/editTextTextPersonName4" />
    <androidx.recyclerview.widget.RecyclerView
        android:id="@+id/recylerview"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        app:layout_constraintTop_toBottomOf="@id/button2"
        app:layout_constraintBottom_toBottomOf="parent"/>
</androidx.constraintlayout.widget.ConstraintLayout>

运行可得到students.db文件
在这里插入图片描述

3.设置增加数据按钮

在这里插入图片描述

package com.hnucm.a_test133;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    EditText editText;
    EditText editText2;
    EditText editText3;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        MySQLiteOpenHelper mySQLiteOpenHelper=new MySQLiteOpenHelper(this,"students.db",null,1);
        SQLiteDatabase sqLiteDatabase=mySQLiteOpenHelper.getWritableDatabase();

        editText=findViewById(R.id.editTextTextPersonName);
        editText2=findViewById(R.id.editTextTextPersonName2);
        editText3=findViewById(R.id.editTextTextPersonName3);
        Button button=findViewById(R.id.button);
        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues contentValues=new ContentValues();
                contentValues.put("stuid",editText.getText().toString());
                contentValues.put("stuname",editText2.getText().toString());
                contentValues.put("stuclass",editText3.getText().toString());

                sqLiteDatabase.insert("student",null,contentValues);
                Toast.makeText(MainActivity.this,"增加学生成功",Toast.LENGTH_LONG).show();
            }
        });
    }
}

在这里插入图片描述

4.添加Recyclerview视图

首先,之前在布局文件中添加了Recyclerview控件,还需要建立一个item布局文件
在这里插入图片描述

<?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="150dp">


    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="42dp"
        android:layout_marginLeft="42dp"
        android:text="TextView"
        app:layout_constraintBottom_toTopOf="@+id/textView2"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="TextView"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintStart_toStartOf="@+id/textView2"
        app:layout_constraintTop_toBottomOf="@+id/textView2" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="TextView"
        app:layout_constraintBottom_toTopOf="@+id/textView3"
        app:layout_constraintStart_toStartOf="@+id/textView"
        app:layout_constraintTop_toBottomOf="@+id/textView" />



</androidx.constraintlayout.widget.ConstraintLayout>

第二步:创建实体类
在这里插入图片描述

第三步:编写myholder,myadapter并赋值
在这里插入图片描述

package com.hnucm.a_test133;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    EditText editText;
    EditText editText2;
    EditText editText3;
    RecyclerView recyclerView;
    List<Student> list=new ArrayList<>();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        MySQLiteOpenHelper mySQLiteOpenHelper=new MySQLiteOpenHelper(this,"students.db",null,1);
        SQLiteDatabase sqLiteDatabase=mySQLiteOpenHelper.getWritableDatabase();

        editText=findViewById(R.id.editTextTextPersonName);
        editText2=findViewById(R.id.editTextTextPersonName2);
        editText3=findViewById(R.id.editTextTextPersonName3);

        recyclerView=findViewById(R.id.recylerview);//实例化

        Cursor cursor=sqLiteDatabase.query("student",null,null,null,null,null,null);//表示把所有字段的数据都取出来
        while (cursor.moveToNext()){//cursor游标默认在第一行,可以通过移动游标来获取数据
            Student student=new Student();

            //有时候要同时修改多处相同的字段名 可以按住alt+shift+r
            //通过游标来取数据
            student.id=cursor.getInt(0);
            student.stuid=cursor.getString(1);
            student.stuname=cursor.getString(2);
            student.stuclass=cursor.getString(3);

            list.add(student);
        }
        cursor.close();//关闭cursor,防止内存泄漏
        recyclerView.setAdapter(new MyAdapter());
        //表示RecyclerView采用一种线性方式来排列,一行显示一个
        recyclerView.setLayoutManager(new LinearLayoutManager(this));

        Button button=findViewById(R.id.button);
        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues contentValues=new ContentValues();
                contentValues.put("stuid",editText.getText().toString());
                contentValues.put("stuname",editText2.getText().toString());
                contentValues.put("stuclass",editText3.getText().toString());

                sqLiteDatabase.insert("Student",null,contentValues);
                Toast.makeText(MainActivity.this,"增加学生成功",Toast.LENGTH_LONG).show();
            }
        });
    }
    public class MyAdapter extends RecyclerView.Adapter<MyHolder>{

        @NonNull
        @Override
        public MyHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
            View view= LayoutInflater.from(MainActivity.this).inflate(R.layout.item,parent,false);//加载view
            MyHolder myHolder=new MyHolder(view);//返回view
            return myHolder;
        }

        @Override
        public void onBindViewHolder(@NonNull MyHolder holder, int position) {

            Student student=list.get(position);

            holder.textView.setText("学号: "+student.stuid);
            holder.textView1.setText("姓名: "+student.stuname);
            holder.textView2.setText("班级: "+student.stuclass);
        }

        @Override
        public int getItemCount() {
            return list.size();
        }

    }
    public class MyHolder extends RecyclerView.ViewHolder{
        TextView textView;//成员变量
        TextView textView1;
        TextView textView2;
        public MyHolder(@NonNull View itemView) {
            super(itemView);
            textView=itemView.findViewById(R.id.textView);
            textView1=itemView.findViewById(R.id.textView2);
            textView2=itemView.findViewById(R.id.textView3);
        }
    }
}

运行:
在这里插入图片描述

但是每次添加数据只是添加到了数据库,我们的列表不会刷新数据,因此我们要在每次点击添加数据的时候在列表里也添加数据

在这里插入图片描述

package com.hnucm.a_test133;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    EditText editText;
    EditText editText2;
    EditText editText3;
    RecyclerView recyclerView;
    MyAdapter myAdapter;
    List<Student> list=new ArrayList<>();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        MySQLiteOpenHelper mySQLiteOpenHelper=new MySQLiteOpenHelper(this,"students.db",null,1);
        SQLiteDatabase sqLiteDatabase=mySQLiteOpenHelper.getWritableDatabase();

        editText=findViewById(R.id.editTextTextPersonName);
        editText2=findViewById(R.id.editTextTextPersonName2);
        editText3=findViewById(R.id.editTextTextPersonName3);

        recyclerView=findViewById(R.id.recylerview);//实例化

        Cursor cursor=sqLiteDatabase.query("student",null,null,null,null,null,null);//表示把所有字段的数据都取出来
        while (cursor.moveToNext()){//cursor游标默认在第一行,可以通过移动游标来获取数据
            Student student=new Student();

            //有时候要同时修改多处相同的字段名 可以按住alt+shift+r
            //通过游标来取数据
            student.id=cursor.getInt(0);
            student.stuid=cursor.getString(1);
            student.stuname=cursor.getString(2);
            student.stuclass=cursor.getString(3);

            list.add(student);
        }
        cursor.close();//关闭cursor,防止内存泄漏
        myAdapter = new MyAdapter();
        recyclerView.setAdapter(myAdapter);
        //表示RecyclerView采用一种线性方式来排列,一行显示一个
        recyclerView.setLayoutManager(new LinearLayoutManager(this));

        Button button=findViewById(R.id.button);
        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues contentValues=new ContentValues();
                contentValues.put("stuid",editText.getText().toString());
                contentValues.put("stuname",editText2.getText().toString());
                contentValues.put("stuclass",editText3.getText().toString());

                long id = sqLiteDatabase.insert("student", null, contentValues);

                Student student=new Student();
                student.stuid=editText.getText().toString();
                student.stuname=editText2.getText().toString();
                student.stuclass=editText3.getText().toString();
                student.id= (int) id;
                list.add(student);
                myAdapter.notifyDataSetChanged();

                Toast.makeText(MainActivity.this,"增加学生成功",Toast.LENGTH_LONG).show();


            }
        });
    }
    public class MyAdapter extends RecyclerView.Adapter<MyHolder>{

        @NonNull
        @Override
        public MyHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
            View view= LayoutInflater.from(MainActivity.this).inflate(R.layout.item,parent,false);//加载view
            MyHolder myHolder=new MyHolder(view);//返回view
            return myHolder;
        }

        @Override
        public void onBindViewHolder(@NonNull MyHolder holder, int position) {

            Student student=list.get(position);

            holder.textView.setText("学号: "+student.stuid);
            holder.textView1.setText("姓名: "+student.stuname);
            holder.textView2.setText("班级: "+student.stuclass);
        }

        @Override
        public int getItemCount() {
            return list.size();
        }

    }

    public class MyHolder extends RecyclerView.ViewHolder{
        TextView textView;//成员变量
        TextView textView1;
        TextView textView2;
        public MyHolder(@NonNull View itemView) {
            super(itemView);
            textView=itemView.findViewById(R.id.textView);
            textView1=itemView.findViewById(R.id.textView2);
            textView2=itemView.findViewById(R.id.textView3);
        }
    }
}

运行,可以看到我们一开始下拉刷新是没有加载数据的,点击按钮以后新增了一条新数据
在这里插入图片描述

5.设置查询数据按钮

在这里插入图片描述

package com.hnucm.a_test133;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    EditText editText;
    EditText editText2;
    EditText editText3;
    EditText editText4;
    RecyclerView recyclerView;
    MyAdapter myAdapter;
    List<Student> list=new ArrayList<>();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        MySQLiteOpenHelper mySQLiteOpenHelper=new MySQLiteOpenHelper(this,"students.db",null,1);
        SQLiteDatabase sqLiteDatabase=mySQLiteOpenHelper.getWritableDatabase();

        editText=findViewById(R.id.editTextTextPersonName);
        editText2=findViewById(R.id.editTextTextPersonName2);
        editText3=findViewById(R.id.editTextTextPersonName3);
        editText4=findViewById(R.id.editTextTextPersonName4);
        recyclerView=findViewById(R.id.recylerview);//实例化

        Cursor cursor=sqLiteDatabase.query("student",null,null,null,null,null,null);//表示把所有字段的数据都取出来
        while (cursor.moveToNext()){//cursor游标默认在第一行,可以通过移动游标来获取数据
            Student student=new Student();

            //有时候要同时修改多处相同的字段名 可以按住alt+shift+r
            //通过游标来取数据
            student.id=cursor.getInt(0);
            student.stuid=cursor.getString(1);
            student.stuname=cursor.getString(2);
            student.stuclass=cursor.getString(3);

            list.add(student);
        }
        cursor.close();//关闭cursor,防止内存泄漏
        myAdapter = new MyAdapter();
        recyclerView.setAdapter(myAdapter);
        //表示RecyclerView采用一种线性方式来排列,一行显示一个
        recyclerView.setLayoutManager(new LinearLayoutManager(this));

        Button button1=findViewById(R.id.button2);
        button1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                list.clear();//清空数据
                String content="%"+editText4.getText().toString()+"%";
                //like 表示模糊匹配   where stuname like %name%
                Cursor cursor= sqLiteDatabase.query("student",null,"stuclass like ?",new  String[]{content},null,null,null);//表示把所有字段的数据都取出来
                while (cursor.moveToNext()) {//cursor游标默认在第一行,可以通过移动游标来获取数据
                    Student student=new Student();
                    //通过游标来取数据
                    student.id=cursor.getInt(0);
                    student.stuid=cursor.getString(1);
                    student.stuname=cursor.getString(2);
                    student.stuclass=cursor.getString(3);
                    list.add(student);

                }
                cursor.close();//关闭cursor,防止内存泄漏
                myAdapter.notifyDataSetChanged();//界面更新

            }
        });

        Button button=findViewById(R.id.button);
        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                ContentValues contentValues=new ContentValues();
                contentValues.put("stuid",editText.getText().toString());
                contentValues.put("stuname",editText2.getText().toString());
                contentValues.put("stuclass",editText3.getText().toString());

                long id = sqLiteDatabase.insert("student", null, contentValues);

                Student student=new Student();
                student.stuid=editText.getText().toString();
                student.stuname=editText2.getText().toString();
                student.stuclass=editText3.getText().toString();
                student.id= (int) id;
                list.add(student);
                myAdapter.notifyDataSetChanged();

                Toast.makeText(MainActivity.this,"增加学生成功",Toast.LENGTH_LONG).show();


            }
        });
    }
    public class MyAdapter extends RecyclerView.Adapter<MyHolder>{

        @NonNull
        @Override
        public MyHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
            View view= LayoutInflater.from(MainActivity.this).inflate(R.layout.item,parent,false);//加载view
            MyHolder myHolder=new MyHolder(view);//返回view
            return myHolder;
        }

        @Override
        public void onBindViewHolder(@NonNull MyHolder holder, int position) {

            Student student=list.get(position);

            holder.textView.setText("学号: "+student.stuid);
            holder.textView1.setText("姓名: "+student.stuname);
            holder.textView2.setText("班级: "+student.stuclass);
        }

        @Override
        public int getItemCount() {
            return list.size();
        }

    }

    public class MyHolder extends RecyclerView.ViewHolder{
        TextView textView;//成员变量
        TextView textView1;
        TextView textView2;
        public MyHolder(@NonNull View itemView) {
            super(itemView);
            textView=itemView.findViewById(R.id.textView);
            textView1=itemView.findViewById(R.id.textView2);
            textView2=itemView.findViewById(R.id.textView3);
        }
    }
}

运行:
在这里插入图片描述
模糊匹配多个字段:
在这里插入图片描述

Cursor cursor= sqLiteDatabase.query("student",null,"stuclass like ? or stuid like ?",new  String[]{content,content},null,null,null);//表示把所有字段的数据都取出来

在这里插入图片描述

  • 2
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值