SQLite数据库实战项目
补充：由于需要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);//表示把所有字段的数据都取出来
1149
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
