javascript - 尝试在数据库中访问和设置外键时遇到问题
问题描述
使用 android studio 运行我的应用程序时出现以下错误。
错误
===============================================
java.lang.IllegalStateException：无法从 CursorWindow 读取第 0 行 col -1。确保在从光标访问数据之前正确初始化光标。
我的数据库是 SQLite。我的应用程序允许用户注册和登录。运动应用。
从这里他们可以将“玩家”添加到他们的团队中。我在我的数据库中设置了两张表，一张用于用户，一张用于玩家，用户表中的“user_id”字段用作链接两个数据库的外键。基本上只有添加了某个玩家的登录用户才能看到该玩家信息，而不能看到其他用户创建的所有其他信息。
最初，该应用程序将玩家保存到正确的牌桌。但是外键没有被填充。然后我重新编写了代码来纠正这个问题。然而，这是我遇到这个新问题的时候。
非常感谢任何帮助或建议。
1) 数据库助手.java
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "MyDB1.db";
private static final String TABLE_USER = "User";
private static final String COLUMN_USER_NAME = "User_name";
private static final String COLUMN_USER_ID = "User_id";
private static final String COLUMN_USER_EMAIL = "User_email";
private static final String COLUMN_USER_PASSWORD = "User_password";
private static final String TABLE_PLAYERS = "Player";
private static final String COLUMN_PLAYER_NAME = "Player_name";
private static final String COLUMN_PLAYER_AGE = "Player_age";
private static final String COLUMN_PLAYER_WEIGHT = "Player_weight";
private static final String COLUMN_PLAYER_HEIGHT = "Player_height";
private static final String COLUMN_PLAYER_ID = "Player_id";
private static final String FOREIGN_PLAYER_ID = COLUMN_USER_ID;
// private static final Image COLUMN_PLAYER_IMAGE ;
// Table 1 : Login/Register
private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "(" + COLUMN_USER_NAME + " TEXT,"
+ COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";
// Table 2 : Adding players
private String CREATE_PLAYER_TABLE = "CREATE TABLE " + TABLE_PLAYERS + "(" + COLUMN_PLAYER_NAME + " TEXT,"
+ COLUMN_PLAYER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COLUMN_PLAYER_AGE + " INTEGER," + COLUMN_PLAYER_WEIGHT + " INTEGER," + COLUMN_PLAYER_HEIGHT + " INTEGER, " + FOREIGN_PLAYER_ID + " INTEGER," + "FOREIGN KEY(" + FOREIGN_PLAYER_ID + ") REFERENCES " + TABLE_USER + "(User_id) " + ")";
// Drop tables
private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER ;
private String DROP_PLAYER_TABLE = "DROP TABLE IF EXISTS " + TABLE_PLAYERS ;
public DatabaseHelper(Context context){
//String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
// Enable foreign key constraints
db.execSQL("PRAGMA foreign_keys=ON;");
}
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_USER_TABLE);
db.execSQL(CREATE_PLAYER_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_USER_TABLE);
db.execSQL(DROP_PLAYER_TABLE);
onCreate(db);
}
// Adding a user to Users table
public void addUser(User user){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_USER_NAME, user.getName());
values.put(COLUMN_USER_EMAIL, user.getEmail());
values.put(COLUMN_USER_PASSWORD, user.getPassword());
values.put(FOREIGN_PLAYER_ID, user.getForeignID());
db.insert(TABLE_USER, null, values);
db.close();
}
// Adding a player to players table
public void addPlayer(Player player) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
// Table 2 : Add players info
values.put(COLUMN_PLAYER_NAME, player.getPlayerName());
values.put(COLUMN_PLAYER_AGE, player.getPlayerAge());
values.put(COLUMN_PLAYER_HEIGHT, player.getPlayerHeight());
values.put(COLUMN_PLAYER_WEIGHT, player.getPlayerWeight());
values.put(FOREIGN_PLAYER_ID, player.getForeignKey());
db.insert(TABLE_PLAYERS, null, values);
db.close();
}
// Checking the users email
public boolean checkUser(String email){
String[] columns = {
COLUMN_USER_ID
};
SQLiteDatabase db = this.getWritableDatabase();
String selection = COLUMN_USER_EMAIL + " = ?";
String[] selectionArgs = { email };
Cursor cursor = db.query(TABLE_USER,
columns,
selection,
selectionArgs,
null,
null,
null);
int cursorCount = cursor.getCount();
cursor.close();
db.close();
if (cursorCount > 0){
return true;
}
return false;
}
//
public String getColumnUserName(String email){
String user = "";
String[] columns = {
COLUMN_USER_ID
};
SQLiteDatabase db = this.getWritableDatabase();
String selection = COLUMN_USER_EMAIL + " = ?";
String[] selectionArgs = { email };
Cursor cursor = db.query(TABLE_USER,
columns,
selection,
selectionArgs,
null,
null,
null);
int cursorCount = cursor.getCount();
if (cursor.moveToFirst()) // data?{
user = cursor.getString(cursor.getColumnIndex("EMAIL"));
cursor.close(); // that's important too, otherwise you're gonna leak cursors
db.close();
if (cursorCount > 0){
return user;
}
return user;
}
// Checking the users email and password
public boolean checkUser(String email, String password){
String[] columns = {
COLUMN_USER_ID
};
SQLiteDatabase db = this.getWritableDatabase();
String selection = COLUMN_USER_EMAIL + " = ?" + " AND " + COLUMN_USER_PASSWORD + " =?";
String[] selectionArgs = { email, password };
Cursor cursor = db.query(TABLE_USER,
columns,
selection,
selectionArgs,
null,
null,
null);
int cursorCount = cursor.getCount();
cursor.close();
db.close();
if (cursorCount > 0){
return true;
}
return false;
}
}
2) 玩家.java
public class Players extends AppCompatActivity {
private Button insert;
private static final int PICK_IMAGE=100;
private String nameFromIntent = "";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_players);
//Open add players section
insert = (Button) findViewById(R.id.addPlayer);
insert.setOnClickListener(new View.OnClickListener()
{
@Override
public void onClick (View v)
{
openaAddPlayersActivity();
}
});
nameFromIntent = getIntent().getStringExtra("EMAIL");
}
private void openaAddPlayersActivity() {
Intent intent = new Intent(this, addPlayers.class );
String nameFromIntent = getIntent().getStringExtra("EMAIL");
intent.putExtra(("EMAIL") ,nameFroenter code heremIntent);
startActivity(intent);
}
}
3)addPlayers.java
public class addPlayers extends AppCompatActivity implements View.OnClickListener{
private Button insert;
private static final int PICK_IMAGE=100;
private final AppCompatActivity activity = addPlayers.this;
private EditText editTextPlayerName;
private EditText editTextPlayerAge;
private EditText editTextPlayerWeight;
private EditText editTextPlayerHeight;
private TextInputEditText textInputEditTextEmail;
private Inputvalidation inputvalidation;
private DatabaseHelper databaseHelper;
private Player player;
private Button appCompatButtonRegister;
private User user;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_add_players);
// insert = (Button) findViewById(R.id.profilePicture);
// insert.setOnClickListener(new View.OnClickListener()
getSupportActionBar().hide();
initViews();
initListeners();
initObjects();
}
private void initViews() {
editTextPlayerName = (EditText) findViewById(R.id.playerName);
editTextPlayerAge = (EditText) findViewById(R.id.playerAge);
editTextPlayerHeight = (EditText) findViewById(R.id.playerHeight);
editTextPlayerWeight = (EditText) findViewById(R.id.playerWeight);
textInputEditTextEmail = (TextInputEditText) findViewById(R.id.enterEmail);
appCompatButtonRegister = (Button) findViewById(R.id.savePlayer);
}
private void initListeners() {
appCompatButtonRegister.setOnClickListener(this);
}
private void initObjects() {
inputvalidation = new Inputvalidation(activity);
databaseHelper = new DatabaseHelper(activity);
player = new Player ();
}
// Table 2 : Add players info
@Override
public void onClick(View v) {
// Intent intent = new Intent(Intent.ACTION_PICK, Uri.parse("content://media/internal/images/media"));
//startActivityForResult(intent, PICK_IMAGE);
switch (v.getId()){
case R.id.savePlayer:
postDataToSQLite();
break;
}
}
private void postDataToSQLite() {
if(!databaseHelper.checkUser(editTextPlayerName.getText().toString().trim()))
//textInputEditTextPassword.getText().toString().trim()))
{
Bundle email= getIntent().getExtras();
String a = databaseHelper.getColumnUserName(email.getString("EMAIL"));
player.setPlayerName(editTextPlayerName.getText().toString().trim());
player.setPlayerAge(Integer.parseInt(editTextPlayerAge.getText().toString().trim()));
player.setPlayerHeight(Integer.parseInt(editTextPlayerHeight.getText().toString().trim()));
player.setPlayerWeight(Integer.parseInt(editTextPlayerWeight.getText().toString().trim()));
player.setForeignKey(Integer.parseInt(a));
//Integer.parseInt(databaseHelper.getColumnUserName(ContactsContract.CommonDataKinds.Email.getString("EMAIL"))));
databaseHelper.addPlayer(player);
Snackbar.make(findViewById(R.id.addPlayer), R.string.success_player_message,Snackbar.LENGTH_LONG).show();
// emptyEditText();
Intent accountIntent = new Intent(activity, Players.class);
accountIntent.putExtra("EMAIL", textInputEditTextEmail.getText().toString().trim());
//emptyInputEditText();
startActivity(accountIntent);
}
//else {
// Snack Bar to show error message that record already exists
// Snackbar.make(findViewById(R.id.Register), getString(R.string.error_email_exists), Snackbar.LENGTH_LONG).show();
// }
}
/*protected void onActivityResult(int requestCode, int resultCode, Intent data){
super.onActivityResult(requestCode, resultCode, data);
if(resultCode==RESULT_OK && requestCode==PICK_IMAGE){
Uri uri = data.getData();
String x = getPath(uri);
Toast.makeText(getApplicationContext(), x, Toast.LENGTH_LONG).show();
}
}
private String getPath(Uri uri) {
if(uri==null)return null;
String [] projection = {MediaStore.Images.Media.DATA};
Cursor cursor = managedQuery(uri, projection, null, null, null);
if(cursor!=null){
int column_index = cursor.getColumnIndexOrThrow(MediaStore.Images.Media.DATA);
cursor.moveToFirst();
return cursor.getString(column_index);
}
return uri.getPath();
}
*/
}
4)登录.java
public class LoginActivity extends AppCompatActivity implements View.OnClickListener {
private final AppCompatActivity activity = LoginActivity.this;
private NestedScrollView nestedScrollView;
private TextInputLayout textInputLayoutEmail;
private TextInputLayout textInputLayoutPassword;
private TextInputEditText textInputEditTextEmail;
private TextInputEditText textInputEditTextPassword;
private AppCompatButton appCompatButtonLogin;
private AppCompatTextView textViewLinkRegister;
private Inputvalidation inputvalidation;
private DatabaseHelper databaseHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_login);
getSupportActionBar().hide();
initViews();
initListeners();
initObjects();
}
private void initViews() {
textInputLayoutEmail = findViewById(R.id.textInputLayoutEmail);
textInputLayoutPassword = findViewById(R.id.textInputLayoutPassword);
textInputEditTextEmail = findViewById(R.id.enterEmail);
textInputEditTextPassword = findViewById(R.id.enterPassword);
appCompatButtonLogin = findViewById(R.id.Login);
textViewLinkRegister = findViewById(R.id.textViewLinkRegister);
}
private void initListeners() {
appCompatButtonLogin.setOnClickListener(this);
textViewLinkRegister.setOnClickListener(this);
}
private void initObjects() {
databaseHelper = new DatabaseHelper(activity);
inputvalidation = new Inputvalidation(activity);
}
@Override
public void onClick(View v){
switch (v.getId()){
case R.id.Login:
verifyFromSQLite();
break;
case R.id.textViewLinkRegister:
Intent intentRegister = new Intent(getApplicationContext(), Register.class);
startActivity(intentRegister);
break;
}
}
private void verifyFromSQLite() {
if (!inputvalidation.isInputEditTextFilled(textInputEditTextEmail, textInputLayoutEmail, getString(R.string.error_message_email))){
return;
}
if (!inputvalidation.isInputEditTextEmail(textInputEditTextEmail, textInputLayoutEmail, getString(R.string.error_message_email))){
return;
}
if (!inputvalidation.isInputEditTextFilled(textInputEditTextPassword, textInputLayoutPassword, getString(R.string.error_message_password))){
return;
}
if(databaseHelper.checkUser(textInputEditTextEmail.getText().toString().trim(), textInputEditTextPassword.getText().toString().trim()))
{
Intent accountIntent = new Intent(activity, LoggedIn.class);
accountIntent.putExtra("EMAIL", textInputEditTextEmail.getText().toString().trim());
emptyInputEditText();
startActivity(accountIntent);
}else {
Snackbar.make(findViewById(R.id.Login), R.string.error_valid_email_password,Snackbar.LENGTH_LONG).show();
//nestedScrollView, getString(R.string.error_valid_email_password), Snackbar.LENGTH_LONG).show();
}
}
private void emptyInputEditText() {
textInputEditTextEmail.setText(null);
textInputEditTextPassword.setText(null);
}
}
解决方案
我相信您的问题可能是您的外键正在按应有的方式运行，并且由于传递给addPlayer方法的播放器似乎没有设置用户（除非在 Player 的构造中使用圆顶）目的）。
也就是说，用户很可能是 null 或其他一些不是存储在User表的User_id列中的值。
也就是说，您似乎只是将电子邮件从一个活动传递到另一个活动，然后只使用checkUser（只返回一个布尔值）和getColumnsUserName（只返回一个字符串，该字符串似乎是根据用作搜索的电子邮件找到的电子邮件争论）。
然而，User_id 必须是整数值，才能成为User表中的有效User_id列。
- 那是因为使用 INTEGER PRIMARY KEY（带或不带 AUTOINCREMENT）定义的User_id列是隐藏的rowid列的别名，并且必须是一个整数值（SQLite Autoincrement PS 另外注意关于使用 AUTOINCREMENT 的摘要）。
确定问题
我建议db.insert(TABLE_PLAYERS, null, values);在 DatabaseHelper 中的行上放置一个断点并在 Debug 中运行，当达到断点时，然后检查player对象中的foreignId的值。
您可能希望阅读调试您的应用程序
但是，如果怀疑原因，我也相信堆栈跟踪也应该表明冲突。
就个人而言，而不是将电子邮件从活动传递到活动，我建议传递User_id值，因为这将是唯一的，并且也是按照以下方式定位相应行的最有效方法：-
rowid 表的数据存储为 B-Tree 结构，其中每个表行包含一个条目，使用 rowid 值作为键。这意味着按 rowid 检索或排序记录很快。搜索具有特定 rowid 的记录或具有指定范围内的 rowid 的所有记录的速度大约是通过指定任何其他 PRIMARY KEY 或索引值进行的类似搜索的两倍。 ROWID 和整数主键
附加的
有两个问题将导致索引 -1 问题。
-1 问题本身是因为如果传递给该方法的列在 Cursor 中不存在，则 Cursor getColumnIndex方法返回 -1（请注意，Cursor 只有已指定的列）。
第一个问题是这条线：-
user = cursor.getString(cursor.getColumnIndex("EMAIL"));
用户表中没有这样的列，所以这总是会失败。将其更改为：- user = cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL));
将指定表中的列。
第二个问题是虽然 COLUMN_USER_EMAIL 是表中的有效列，但该列不包含在游标中。
要在光标中包含该列，然后更改：-
String[] columns = {
COLUMN_USER_ID
};
至 ：-
String[] columns = {
COLUMN_USER_ID,
COLUMN_USER_EMAIL
};
或者 ：-
String[] columns = {"*" }; //<<<<<<<<<< * means ALL columns
或者改为更改：-
Cursor cursor = db.query(TABLE_USER,
columns,
selection,
selectionArgs,
null,
null,
null);
int cursorCount = cursor.getCount();
至
Cursor cursor = db.query(TABLE_USER,
null, //<<<<<<<<<< null equates to * and thus ALL columns
selection,
selectionArgs,
null,
null,
null);
推荐阅读
- c++ - 检查元素是否属于 std::stack
- python - 如何在 Flask 中使用文件数据重新路由/重新创建 POST 请求
- angular - 添加项目后刷新项目列表
- javascript - 如何使用 npm 在命令行中传递参数并在反应 javascript 代码中读取它
- sql - 如果 group by 有 0 条记录，如何返回 0 行？
- css - SCSS 命名约定
- css - 我应该在 html 模板中编写什么 CSS 来生成特定高度和宽度的 pdf
- onedrive - MS OneDrive - 命令行将文件夹共享给电子邮件收件人？
- amazon-web-services - Airflow 2.1.0 返回“拒绝删除与角色关联的权限视图...”
- java - 获取 Spark 包时如何禁用 SSL 身份验证？