java - SQLException错误说不存在现有列

Question

运行searchQuery时，出现SQLException错误，提示无法找到我的列之一。这非常令人困惑，因为我在代码的前面引用了该列，程序此时可以找到该列。我的语法有问题吗？谢谢!

public void createDB(){    
    String createCharTableSQL = "CREATE TABLE CosplayCharacter (" +
                "CharacterID int NOT NULL primary key GENERATED ALWAYS " +
                "AS IDENTITY (START WITH 1, INCREMENT BY 1), " +
                "name varchar(60) not null," +
                "gender varchar(10)," +
                "genreID int, " +
                "universeID int, " +
                "mediaID int, " +
                "description varchar(60))";

      statement.executeUpdate(createCharTableSQL);
}

public void insertCharacter(){    
  String fetchAllDataSQL = "SELECT * from CosplayCharacter";


    //Code can find the universeID column here
        resultSet = statement.executeQuery(fetchAllDataSQL);
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int universeCharID = resultSet.getInt("universeID");

            System.out.println("Character Name : " + name +
                    " UniverseID : " + universeCharID);
        }
}

//But returns error here
public void searchCharacter(String characterName){
    String fetchAllDataSQL = "SELECT * from CosplayCharacter where name = '" + characterName +"'";

        resultSet = statement.executeQuery(fetchAllDataSQL);
        while (resultSet.next()) {
            String gender = resultSet.getString("gender");
            int genreID = resultSet.getInt("genreID");
//Method to search a column in a joined table; no error
            String genreName = getGenreName(genreID);
            int universeID = resultSet.getInt("universeID");
   }
}

堆栈跟踪:

 java.sql.SQLException: Column 'universeID' not found.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown  
Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)

Answer 1

ResultSet已全局使用。 getGenreName方法中使用的ResultSet覆盖主searchCharacter方法中的resultSet。解决方案:在每个方法中分别设置ResultSet变量。