javascript - 我在尝试访问和设置数据库中的外键时遇到问题

Question

使用 android studio 运行我的应用程序时出现以下错误。

错误

==============================================

java.lang.IllegalStateException:无法从 CursorWindow 读取第 0 行，col -1。在从中访问数据之前，请确保 Cursor 已正确初始化。

我的数据库是 SQLite。我的应用程序允许用户注册和登录。运动应用。

他们可以从这里将“玩家”添加到他们的团队中。我在我的数据库中设置了两个表，一个用于用户，一个用于玩家，用户表中的“user_id”字段被用作链接两个数据库的外键。基本上只有添加了特定玩家的登录用户才能看到该玩家的信息，而不能看到其他用户创建的所有其他信息。

最初，该应用会将玩家保存到正确的牌 table 。但是外键没有被填满。然后我重新编写代码来纠正这个问题。然而，这是我遇到这个新问题的时候。

非常感谢任何帮助或建议。

1) DatabaseHelper.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) Players.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);

    }
}

Answer 1

我认为您的问题可能是您的外键正在正常运行，并且正在引发冲突，因为传递给 addPlayer 方法的 player 没有't appear to have the user set(除非在 Player 对象的构造中出现圆顶)。

即 user 可能为 null 或一些其他值，该值不是存储在 User 的 User_id 列中的值表。

也就是说，您似乎只在 Activity 之间传递电子邮件，然后只使用checkUser(只返回一个 boolean 值)和getColumnsUserName(仅返回一个字符串，该字符串似乎是根据用作搜索参数的电子邮件找到的电子邮件)。

但是，User_id 必须是一个整数值，这样它才能成为 User 表中的有效 User_id 列。

• 这是因为使用 INTEGER PRIMARY KEY(带或不带 AUTOINCREMENT)定义的 User_id 列是隐藏的 rowid 列的别名并且必须 是一个 INTEGER 值(SQLite Autoincrement P.S. 另外注意关于使用 AUTOINCREMENT 的摘要)。

确定问题

我建议在行 db.insert(TABLE_PLAYERS, null, values); 上放置一个断点在 DatabaseHelper 中并在 Debug 中运行，当到达断点时检查 player 对象中的 foreignId 的值。

您不妨阅读Debug your app

但是，如果原因与所怀疑的一样，我也相信堆栈跟踪也应该指示冲突。

我个人建议传递 User_id 值，而不是将 email 从一个 Activity 传递到另一个 Activity ，因为这将是唯一的，也是定位的最有效方法相应的行:-

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. ROWIDs and the INTEGER PRIMARY KEY

补充

有两个问题，每个问题都会导致索引 -1 问题。

-1 问题本身是因为 Cursor getColumnIndex 方法返回 -1 如果传递给该方法的列在 Cursor 中不存在(注意 Cursor 只有指定的列) .

第一个问题是行:-

user =  cursor.getString(cursor.getColumnIndex("EMAIL"));

用户表中没有这样的列，所以这总是会失败。将其更改为:- user = cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL));

将指定表中的列。

第二个问题是，尽管 COLUMN_USER_EMAIL 是表中的有效列，但该列未包含在 Cursor 中。

要在 Cursor 中包含该列，然后更改:-

        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);