如何更新 Sqlite 数据库方法

尝试更新表中恰好是外键的列。

我知道当我使用这些详细信息登录时它会进入数据库，但在更新时它似乎不起作用。

这是我的更新方法，其中包含将ID转换为电子邮件的方法：

private int getIdFromName(String email) {
SQLiteDatabase db = this.getWritableDatabase();
ArrayList<Integer> refs = new ArrayList<>();
Cursor cursor;
cursor = db.rawQuery("select " + REFEREE_EMAIL_COL + " from " +
REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);
while (cursor.moveToNext()){
refs.add(cursor.getInt(0));
}
return refs.get(0);
}
public boolean updateRef(String email)
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
int ref_id = getIdFromName(email);
values.put(REFEREE_ID_COL, ref_id);
db.update(MATCH_TABLE, values, "REFEREEID = ?", new String[]{email});
return true;
}

private Button btnSave,btnDelete;
private EditText homeTeamEdit, awayTeamEdit, typeOfMatchEdit, redCardsEdit, bookingsEdit, groundEdit, refEdit, dateEdit, timeEdit,
awayScoreEdit, homeScoreEdit;
DBHandler mDatabaseHelper;
private String homeTeam, awayTeam, homeScore, awayScore;
private String typeOfMatch;
private String ref;
private String redCards, bookings;
private String date, time;
private String ground;
private int selectedID;
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_update_match);
btnSave = (Button) findViewById(R.id.UpdateMatchButton);
homeTeamEdit = (EditText) findViewById(R.id.HomeTeamUpdate);
homeScoreEdit = (EditText) findViewById(R.id.updateHomeScore);
awayTeamEdit = (EditText) findViewById(R.id.AwayTeamUpdate);
awayScoreEdit = (EditText) findViewById(R.id.updateAwayScore);
typeOfMatchEdit = (EditText) findViewById(R.id.updateTypeOfMatch);
refEdit = (EditText) findViewById(R.id.updateRef);
groundEdit = (EditText) findViewById(R.id.updateGround);
refEdit = (EditText) findViewById(R.id.updateRef);
final String referee = refEdit.getText().toString();
mDatabaseHelper = new DBHandler(this);

//get the intent extra from the ListDataActivity
Intent receivedIntent = getIntent();
//now get the itemID we passed as an extra
selectedID = receivedIntent.getIntExtra("MatchId", -1); //NOTE: -1 is just the default value
//now get the name we passed as an extra
homeTeam = receivedIntent.getStringExtra("homeTeam");
homeScore = receivedIntent.getStringExtra("homeScore");
awayTeam = receivedIntent.getStringExtra("awayTeam");
awayScore = receivedIntent.getStringExtra("awayScore");
ground = receivedIntent.getStringExtra("ground");
typeOfMatch = receivedIntent.getStringExtra("typeOfMatch");

//set the text to show the current selected name
homeTeamEdit.setText(homeTeam);
awayTeamEdit.setText(awayTeam);
typeOfMatchEdit.setText(typeOfMatch);
groundEdit.setText(ground);
homeScoreEdit.setText(homeScore);
awayScoreEdit.setText(awayScore);
btnSave.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String item = refEdit.getText().toString();
if(mDatabaseHelper.checkIfRefExists(referee)) {
if (!item.equals("")) {
//                    mDatabaseHelper.updateName(referee, selectedID);
mDatabaseHelper.updateRef(referee);
toastMessage("Ref Updated");
} else {
toastMessage("You must enter a name");
}
}
else
{
toastMessage("No ref with that email");
}
}
});
}
/**
* customizable toast
* @param message
*/
private void toastMessage(String message){
Toast.makeText(this,message, Toast.LENGTH_SHORT).show();
}
}

当我尝试使用 ref 更新匹配时，它总是通过该电子邮件点击没有 refs 的 toast 消息。

更新方法是否有问题，或者我完全关闭了？

更新这是我使用列名创建的表：

private static final String TAG = "DBHandler";
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "RefereeDB";
public static final String REF_TABLE = "referee_table";
public static final String MATCH_TABLE = "match_table";
public static final String ADMIN_TABLE = "admin_table";
//REF Tables
public static final String REFEREE_ID_COL = "REFEREEID";
public static final String REFEREE_NAME_COL = "NAME";
public static final String REFEREE_AGE_COL = "AGE";
public static final String REFEREE_PHONENUM_COL = "PHONENUMBER";
public static final String REFEREE_STATUS_COL = "REFEREESTATUS";
public static final String REFEREE_CLUB_COL = "REFCLUB";
public static final String REFEREE_EMAIL_COL = "REFEMAIL";
public static final String REFEREE_PASSWORD_COL = "REFEREEPASSWORD";
//Match Tables
public static final String MATCH_ID_COL = "MATCHID";
public static final String MATCH_HOME_TEAM_COL = "HOMETEAM";
public static final String MATCH_AWAY_TEAM_COL = "AWAYTEAM";
public static final String MATCH_TIME_COL = "MATCHTIME";
public static final String MATCH_DATE_COL = "MATCHDATE";
public static final String MATCH_HOMESCORE_COL = "MATCHHOMESCORE";
public static final String MATCH_AWAYSCORE_COL = "MATCHAWAYSCORE";
public static final String MATCH_TYPEOFMATCH_COL = "TYPEOFMATCH";
public static final String MATCH_GROUND_NAME_COL = "GROUNDNAME";
public static final String MATCH_GROUND_LAT_COL = "GROUNDLAT";
public static final String MATCH_GROUND_LONGITUDE_COL = "GROUNDLONGITUDE";
//Admin Tables
public static final String ADMIN_ID_COL = "ADMINID";
public static final String ADMIN_EMAIL = "ADMINEMAIL";
public static final String ADMIN_PASSWORD = "ADMINPASSWORD";

//Create Tables
public static final String CREATE_TABLE_REF = "CREATE TABLE "
+ REF_TABLE + "(" + REFEREE_ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + REFEREE_NAME_COL + " TEXT," +
REFEREE_AGE_COL + " INTEGER," + REFEREE_PHONENUM_COL + " TEXT," + REFEREE_STATUS_COL +
" TEXT," + REFEREE_CLUB_COL + " TEXT," + REFEREE_EMAIL_COL + " TEXT," +
REFEREE_PASSWORD_COL + " TEXT" + ")";

public static final String CREATE_TABLE_MATCH = "CREATE TABLE "
+ MATCH_TABLE + "(" + MATCH_ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + MATCH_HOME_TEAM_COL +
" TEXT," + MATCH_AWAY_TEAM_COL + " TEXT," + MATCH_TIME_COL + " TEXT," + MATCH_DATE_COL +
" DATETIME," + MATCH_HOMESCORE_COL + " TEXT," + MATCH_AWAYSCORE_COL + " TEXT," + MATCH_TYPEOFMATCH_COL +
" TEXT," + MATCH_GROUND_NAME_COL + " TEXT," + MATCH_GROUND_LONGITUDE_COL + " TEXT," + MATCH_GROUND_LAT_COL + " TEXT, " + REFEREE_ID_COL + " INTEGER," + " FOREIGN KEY (REFEREEID) REFERENCES " + REF_TABLE + " (REFEREEID)" + ")";
public static final String CREATE_TABLE_ADMIN = "CREATE TABLE "
+ ADMIN_TABLE + "(" + ADMIN_ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + ADMIN_EMAIL +
" TEXT," + ADMIN_PASSWORD + " TEXT" + ")";

我感觉你只是打错了字。您正在选择REFEREE_EMAIL_COL：

cursor = db.rawQuery("select " + REFEREE_EMAIL_COL + " from " +
REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

相反，您必须提供一些变量(如 REFEREE_ID_COL)具有列的名称 ID 并使用它。

cursor = db.rawQuery("select " + REFEREE_ID_COL + " from " +
REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

getIdFromName方法似乎存在问题，因为它似乎正在从传递给该方法的字符串中提取整数。

也就是说，假设电子邮件是fred@email.com并且是存储在REFEREE_EMAIL_COL中的有效电子邮件，则：-

该查询说创建一个具有单列的游标，即REFEREE_EMAIL_COLfred@email.com的REFEREE_EMAIL_COL。

因此，生成的 Cursor 将包含一行，其中一列的值为fred@email.com。

因此，行refs.add(cursor.getInt(0));将尝试根据光标列偏移量 0(唯一列)的内容获取一个整数，该列包含值fred@email.com。由于getInt方法友好/善良，这将返回 0(请参阅示例 - 此处String myTEXT = "The Lazy Quick Brown Fox Jumped Over the Fence or something like that.";)。

因此，除非电子邮件传递给该方法，实际上是 id，并且是转换为字符串的整数以及所需的 id，否则getIdFromName方法将返回 0。否则，您将传递id的id，这将是浪费时间。

注意！fred@email.com 只是假设性的使用。

调试代码以检查上述情况是否如此。

以下代码可用于确定上述情况是否为：-

private int getIdFromName(String email) {
SQLiteDatabase db = this.getWritableDatabase();
ArrayList<Integer> refs = new ArrayList<>();
Cursor cursor;
cursor = db.rawQuery("select " + REFEREE_EMAIL_COL + " from " +
REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);
Log.d("CURSOR COUNT","The number of rows in the Cursor is " + Integer.toString(ccursor.getCount()));
while (cursor.moveToNext()){
Log.d("ID EXTRACTED","The extracted ID is " + Integer.toString(cursor.getInt(0))); // <<<<<<<< added 
refs.add(cursor.getInt(0));
}
return refs.get(0);
}

日志将包含， - a) 包含符合选择标准的行数的行。

b) 如果一行

包含字符串，根据变量email，在REFEREE_EMAIL_COL列中，一行(如果多行，则为行)带有提取的 ID。如果为0，则上述可能是原因。

一个潜在的解决方案：-

假设该表不是使用WITHOUT ROWID创建的，那么以下内容可能有效：-

cursor = db.rawQuery("select " + rowid + " from " +
REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

关于这里罗维德的一点

在这种情况下，rowid将是游标中唯一的列，它将被转换为整数(实际上应该使用getLong因为它是一个长，然后返回并随后使用)。

注意！您的问题中没有可用的信息(例如实际列名称)限制了可以给出的答案。

关于您的评论

这行不通，我的朋友。这必须与我的有关更新方法！

更新方法取决于从getIdFromName方法返回的值。0，假设您使用的是标准 id 列，例如?? INTEGER PRIMARY KEY还是?? INTEGER PRIMARY KEY AUTOINCREMENT，在哪里？？是列名(ROWID的别名)，不会是一行。Id 从 1 开始(除非您强制它)。如果您确实有一列是 rowid 的别名，那么您可以在上面的修复中使用该列名而不是rowid。

表结构后编辑

前面的答案适用，它只能部分解决问题，因此getIdFromName可能是：-

private long getIdFromName(String email) {
long rv = -1; //default return value -1 indicates no referee
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery("select " + REFEREE_ID_COL + " from " +
REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);
if (cursor.moveToFirst()) {
rv = cursor.getLong(csr.getColumnIndex(REFEREE_ID_COL));
}
cursor.close();
return rv;
}
**`getIdFromName`** will now return a **long** rather than an **int**, there is no need for the Integer Array as you only return a long (was int). If the email passed does not result in a referee being found then the value returned will be -1. The Cursor is also closed to reduce the potential for problems if there are too many open Cursors. You will note that the hard coded offset of 0, has been replaced with csr.getColumnIndex(column_name), this gets the offset based upon the column name it is more felxible and less prone to errors.

updateRef方法也存在问题。这看起来好像是用裁判的 id 更新当前比赛。

但是，它正在做的是尝试更新裁判 ID 的比赛的裁判 ID，该比赛的裁判 ID 与裁判 ID 相同。最终结果将是没有任何内容得到更新。

例如，假设您的匹配 ID 为 10，它当前具有带有电子邮件的 Ref 的 fred@email.com 和 ID 为 200。
getIdFromName 正确返回 200。
您的代码说：-
更新比赛表以将裁判 ID 更改为 200，其中裁判ID是200。应该说的是更新匹配表将裁判 ID 更改为 200，其中匹配 ID 为 10。
你的实际上不会对所有有裁判的比赛做任何事情 200.

需要的是当前比赛，以便裁判可以更新该特定比赛，因此需要将两个参数传递给updateRef方法，例如：-

public boolean updateRef(String email, long matchid) {
long ref_id = -1;
int updates_done = 0;
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
// Get the referee's id
ref_id = getIdFromName(email);
// if the referee exists then try to update the match
if (ref_id > 0) {
values.put(REFEREE_ID_COL,ref_id);
updates_done = db.update(
MATCH_TABLE, 
values, 
MATCH_ID_COL + "=?",
new String[]{Long.toString(matchid)}
);
return updates_done > 0;
}
return false;
}

然后需要稍作更改才能传递匹配 ID，例如：-

mDatabaseHelper.updateRef(referee);

应改为：-

mDatabaseHelper.updateRef(referee, (long) selectedID);

理想情况下，您应该始终使用 long for id，因为 id 最多可以达到 9223372036854775807。

注意！同样，代码尚未检查，因此可能存在一些错误。

调试代码以检查上述情况是否如此。

一个潜在的解决方案：-

关于您的评论

表结构后编辑

相关内容

最新更新

热门标签：