android - 从表中删除单个记录,从第二个表中删除所有相应的数据
问题描述
我有两个表 SignUpInfo 和 DetailInfo,我想要实现的是当我从 SignUpInfo 中删除一条记录时,所有相应的 DetailInfo 也应该被删除。它可能是 DetailInfo 中的 1 条记录,也可能是单个 SignUpInfo 记录下的许多条记录。我正在粘贴我的数据库处理程序类(RAVI TAMADA 在https://www.androidhive.info/2013/09/android-sqlite-database-with-multiple-tables/上的代码)和我已经实现删除的 infoRecyclerView 类代码代码。
我得到的错误是,当我实现删除功能时,项目确实被删除,recyclerview 被更新,但在重新打开活动时再次出现,并且相应的数据也没有被删除。
public class InfoRecyclerView extends RecyclerView.Adapter<InfoRecyclerView.ViewHolder> {
private Context context;
private List<Info> infos;
private LayoutInflater layoutInflater;
private AlertDialog.Builder alertDialogBuilder;
private AlertDialog dialog;
public InfoRecyclerView(Context context, List<Info> infos) {
this.context = context;
this.infos = infos;
}
@NonNull
@Override
public ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.signup_list_row, parent, false);
return new ViewHolder(view, context);
}
@Override
public void onBindViewHolder(@NonNull ViewHolder holder, int position) {
Info info = infos.get(position);
holder.name.setText(info.getName());
holder.mobile.setText(info.getMobile());
holder.email.setText(info.getEmail());
}
@Override
public int getItemCount() {
return infos.size();
}
public class ViewHolder extends RecyclerView.ViewHolder implements View.OnClickListener {
public TextView name;
public TextView mobile;
public TextView email;
public Button editButton;
public Button deleteButton;
public int id;
public ViewHolder(View view, Context ctx) {
super(view);
context = ctx;
name = view.findViewById(R.id.list_name);
mobile = view.findViewById(R.id.list_mobile);
email = view.findViewById(R.id.list_email);
editButton = view.findViewById(R.id.list_edit_button);
deleteButton = view.findViewById(R.id.list_delete_button);
editButton.setOnClickListener(this);
deleteButton.setOnClickListener(this);
}
@Override
public void onClick(View view) {
switch (view.getId()) {
case R.id.list_edit_button:
int position = getAdapterPosition();
Info info = infos.get(position);
editItem(info);
Toast.makeText(context, "Edit Button Clicked", Toast.LENGTH_SHORT).show();
break;
case R.id.list_delete_button:
position = getAdapterPosition();
info = infos.get(position);
Toast.makeText(context, "Delete Button Clicked", Toast.LENGTH_SHORT).show();
deleteItem(info.getId());
break;
}
}
public void deleteItem(final int id) {
final Info infor = new Info();
AlertDialog.Builder builder = new AlertDialog.Builder(context);
builder.setMessage("Are you sure you want to delete the student !").setCancelable(false)
.setPositiveButton("YES", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
// delete the student
DataBaseHandler db = new DataBaseHandler(context);
db.deleteAllInfoDetail(infor,true);
// db.deleteSignUpInfo(id);
infos.remove(getAdapterPosition());
notifyItemRemoved(getAdapterPosition());
dialogInterface.dismiss();
}
})
.setNegativeButton("NO", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
dialogInterface.dismiss();
}
});
builder.show();
}
private void editItem(final Info info) {
alertDialogBuilder = new AlertDialog.Builder(context);
layoutInflater = LayoutInflater.from(context);
View view = layoutInflater.inflate(R.layout.edit_popup, null);
final EditText name = view.findViewById(R.id.enter_student_name);
final EditText mobile = view.findViewById(R.id.enter_mobile);
final EditText email = view.findViewById(R.id.enter_email);
final Button saveButton = view.findViewById(R.id.popup_save_button);
// above content will be build and inflated
alertDialogBuilder.setView(view);
dialog = alertDialogBuilder.create();
dialog.show();
saveButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
DataBaseHandler dataBaseHandler = new DataBaseHandler(context);
info.setName(name.getText().toString());
info.setMobile(mobile.getText().toString());
info.setEmail(email.getText().toString());
dataBaseHandler.updateSignUpInfo(info);
notifyItemChanged(getAdapterPosition(),info);
Toast.makeText(context, "Editing Successfull", Toast.LENGTH_SHORT).show();
dialog.dismiss();
}
});
}
}
}
现在我的数据库处理程序类
public class DataBaseHandler extends SQLiteOpenHelper {
// Database to Handler
private static final String DATABASE_NAME = "StudentInfo";
private static final int VERSION = 1;
private static final String KEY_ID = "id";
// Table Names
private static final String TABLE_SIGNUP = "SignUpForm";
private static final String TABLE_DETAIL = "DetailForm";
private static final String TABLE_SIGNUP_DETAIL = "SignUp_Detail";
// SignUp Form Table
private static final String NAME = "name";
private static final String MOBILE = "mobile";
private static final String EMAIL = "email";
private static final String IMAGE = "image";
// SignUp Detail Table
private static final String AGE = "age";
private static final String CLASS = "class";
private static final String SUBJECT = "suject";
// SignUp and Detail Table
private static final String KEY_SIGNUP_ID = "signUpId";
private static final String KEY_DETAIL_ID = "detailId";
// Creating Sign Up Table
private static final String CREATE_TABLE_SIGNUP = "CREATE TABLE "
+ TABLE_SIGNUP + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ NAME + " TEXT,"
+ MOBILE + " TEXT,"
+ EMAIL + " TEXT,"
+ IMAGE + " BLOB"
+ ")";
// Creating Detail Table
private static final String CREATE_TABLE_DETAIL = "CREATE TABLE "
+ TABLE_DETAIL + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ NAME + " TEXT,"
+ AGE + " TEXT,"
+ CLASS + " TEXT,"
+ SUBJECT + " TEXT"
+ ")";
// Creating Combined Table SignUp and Detail
private static final String CREATE_TABLE_SIGNUP_DETAIL = "CREATE TABLE "
+ TABLE_SIGNUP_DETAIL + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ KEY_SIGNUP_ID + " INTEGER,"
+ KEY_DETAIL_ID + " INTEGER"
+ ")";
public DataBaseHandler(Context context) {
super(context, DATABASE_NAME, null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(CREATE_TABLE_SIGNUP);
sqLiteDatabase.execSQL(CREATE_TABLE_DETAIL);
sqLiteDatabase.execSQL(CREATE_TABLE_SIGNUP_DETAIL);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_SIGNUP);
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_DETAIL);
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_SIGNUP_DETAIL);
onCreate(sqLiteDatabase);
}
/*
CRUD OPERATIONS for SIGNUP INFO
*/
public long insertData(Info info, long [] detailIDs) {
// getting writable datebase
SQLiteDatabase db = this.getWritableDatabase();
// new method content values to insert data into Info Class
ContentValues contentValues = new ContentValues();
contentValues.put(NAME, info.getName());
contentValues.put(MOBILE, info.getMobile());
contentValues.put(EMAIL, info.getEmail());
contentValues.put(IMAGE, info.getImage());
// insert new row
long signUp_id = db.insert(TABLE_SIGNUP,null,contentValues);
for (long detail_id : detailIDs){
createSignUpDetail(signUp_id, detail_id);
}
return signUp_id;
}
public Info getInfo (long signUp_id){
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT * FROM " + TABLE_SIGNUP + " WHERE "
+ KEY_ID + " = " + signUp_id;
Cursor c = db.rawQuery(selectQuery, null);
if ( c != null )
c.moveToFirst();
Info info = new Info();
info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
info.setName(c.getString(c.getColumnIndex(NAME)));
info.setEmail(c.getString(c.getColumnIndex(EMAIL)));
info.setMobile(c.getString(c.getColumnIndex(MOBILE)));
info.setImage(c.getBlob(c.getColumnIndex(IMAGE)));
return info;
}
public List<Info> getAllInfo (){
List<Info> infoList = new ArrayList<>();
String selectQuery = "SELECT * FROM " + TABLE_SIGNUP;
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list.
if ( c.moveToFirst() ){
do {
Info info = new Info();
info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
info.setName(c.getString(c.getColumnIndex(NAME)));
info.setEmail(c.getString(c.getColumnIndex(EMAIL)));
info.setMobile(c.getString(c.getColumnIndex(MOBILE)));
info.setImage(c.getBlob(c.getColumnIndex(IMAGE)));
// adding info to list
infoList.add(info);
}while (c.moveToNext());
}
return infoList;
}
/*
* Updating a SignUp Info
*/
public int updateSignUpInfo (Info info){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(NAME, info.getName());
values.put(MOBILE, info.getMobile());
values.put(EMAIL, info.getEmail());
values.put(IMAGE, info.getImage());
// updating row
return db.update(TABLE_SIGNUP, values, KEY_ID + " = ?", new String[] {String.valueOf(info.getId())});
}
/*
* Deleting SignUp Info
*/
public void deleteSignUpInfo (long signUp_id){
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_SIGNUP, KEY_ID + " = ?", new String[] {String.valueOf(signUp_id)});
}
/*
CRUD OPERATIONS for DETAIL INFO
*/
public void createDetails (Detail detail){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(NAME, detail.getName());
values.put(AGE, detail.getAge());
values.put(CLASS,detail.getmClass());
values.put(SUBJECT,detail.getSubject());
db.insert(TABLE_DETAIL, null, values);
}
public List<Detail> allDetails (){
List<Detail> detailList = new ArrayList<>();
String selectQuery = "SELECT * FROM " + TABLE_DETAIL;
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list.
if ( c.moveToFirst() ){
do {
Detail detail = new Detail();
detail.setId(c.getInt(c.getColumnIndex(KEY_ID)));
detail.setName(c.getString(c.getColumnIndex(NAME)));
detail.setAge(c.getString(c.getColumnIndex(AGE)));
detail.setmClass(c.getString(c.getColumnIndex(CLASS)));
detail.setSubject(c.getString(c.getColumnIndex(SUBJECT)));
detailList.add(detail);
}while (c.moveToNext());
}return detailList;
}
public int updateDetails ( Detail detail){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(NAME, detail.getName());
values.put(AGE, detail.getAge());
values.put(CLASS,detail.getmClass());
values.put(SUBJECT,detail.getSubject());
return db.update(TABLE_DETAIL,values,KEY_ID + " = ?", new String[]{String.valueOf(detail.getId())});
}
/*
* Deleting SignUp Info
*/
public void deleteDetailInfo (long detail_id){
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_DETAIL, KEY_ID + " = ?", new String[] {String.valueOf(detail_id)});
}
/*
Delete Details if student info is deleted
boolean should_delete_all_details = passing it true will delete all detail records.
*/
public void deleteAllInfoDetail (Info info, boolean should_delete_all){
SQLiteDatabase db = this.getWritableDatabase();
// checking if the all should be deleted
if ( should_delete_all ){
List<Detail> alldetails = getallDetailsbyInfo(info.getName());
// delete all info
for (Detail detail1 : alldetails){
deleteDetailInfo(detail1.getId());
}
}
db.delete(TABLE_SIGNUP,KEY_ID +" = ?", new String[]{String.valueOf(info.getId())});
}
public void deleteInfoandDetail (long signUpDetailID){
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_SIGNUP_DETAIL, KEY_ID + " = ?", new String[] {String.valueOf(signUpDetailID)});
}
/*
String selectQuery = "SELECT * FROM " + TABLE_TODO + " td, "
+ TABLE_TAG + " tg, " + TABLE_TODO_TAG + " tt WHERE tg."
+ KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
+ " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
+ "tt." + KEY_TODO_ID;
*/
private List<Detail> getallDetailsbyInfo(String name) {
List<Detail> allDetails = new ArrayList<>();
String selectQuery = "SELECT * FROM " + TABLE_DETAIL + " td, "
+ TABLE_SIGNUP + " ts," + TABLE_SIGNUP_DETAIL + " tsd WHERE ts."
+ NAME + " ='" + name + "'" + " AND ts." + KEY_ID
+ " = " + "tsd." + KEY_SIGNUP_ID + " AND td." + KEY_ID + " = "
+ "tsd." + KEY_DETAIL_ID;
SQLiteDatabase db = this.getWritableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if ( c.moveToFirst() ){
do {
Detail detail = new Detail();
detail.setId(c.getInt(c.getColumnIndex(KEY_ID)));
detail.setName(c.getString(c.getColumnIndex(NAME)));
detail.setAge(c.getString(c.getColumnIndex(AGE)));
detail.setmClass(c.getString(c.getColumnIndex(CLASS)));
detail.setSubject(c.getString(c.getColumnIndex(SUBJECT)));
allDetails.add(detail);
}while (c.moveToNext());
}return allDetails;
}
public long createSignUpDetail(long signUp_id, long detail_id) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_SIGNUP_ID, signUp_id);
values.put(KEY_DETAIL_ID, detail_id);
long id = db.insert(TABLE_SIGNUP_DETAIL, null, values);
return id;
}
// closing database
public void closeDB() {
SQLiteDatabase db = this.getReadableDatabase();
if (db != null && db.isOpen())
db.close();
}
}
解决方案
我相信您的问题集中在使用final Info infor = new Info();
即一个新的空/构造与默认值 Info 对象,然后您通过infor传递给deleteAllInfoDetail方法db.deleteAllInfoDetail(infor,true);
该项目似乎已被删除,因为您随后使用infos.remove(getAdapterPosition());
. 因此,它显然已被删除,但如果 RecyclerView 从数据库重建,它将重新出现。
您可能想使用
db.deleteAllInfoDetail(infos.get(getAdapterPosition()),true);
此外,您可以通过以下方式简化您执行删除的尝试:-
public void deleteAllInfoDetail (Info info, boolean should_delete_all){
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
//Delete the SIGNUP row
db.delete(TABLE_SIGNUP,KEY_ID +" = ?", new String[]{String.valueOf(info.getId())});
if (should_delete_all) {
// Delete the rows from the SIGNUP_DETAIL
db.delete(TABLE_SIGNUP_DETAIL,KEY_SIGNUP_ID + "=?",new String[]{String.valueOf(info.getId())});
// Delete the orphaned DETAIL ROWS
db.delete(TABLE_DETAIL, KEY_ID + " NOT IN (SELECT " + KEY_DETAIL_ID + " FROM " + TABLE_SIGNUP_DETAIL + ")", null);
}
db.setTransactionSuccessful();
db.endTransaction();
}
- 这将根据 Info 对象删除 SIGNUP 行,
- 然后删除在 SIGNUP_DETAILS 中引用已删除 SIGNUP 的行 - - 最后删除孤立的 DETAIL 行。
- 所有这些都在一个事务中完成
推荐阅读
- c# - 为什么当我在 Visual Studio 2017 中将存储过程添加到 edmx 时,我的复杂类型没有更新?
- laravel - Laravel echo / pusher在接收数据时不发送ping
- gitlab - 本地 GitLab Runner 实例的配置与我的本地计算机不同
- sql - Oracle dbms_Parallel_execute
- reactjs - 如何更改 React DnD 预览图像的大小?
- javascript - 在 React js 中使用 d3 将鼠标悬停时的工具提示添加到饼图的问题
- python - Python将包含科学值和浮点值的熊猫数据框与浮点数相乘
- python - 我们如何才能只获取每个 ID 的最大日期并删除其余记录?
- eclipse - Eclipes mars中的ArchetypeGenerationFailure
- javascript - 有没有办法使用下面的代码使按钮的颜色随机?