问题

我正在使用本地的derby服务器工作，但出现了一个错误，错误信息为java.sql.SQLSyntaxErrorException: 'ID'不是表或VTI'AUTHORBOOKDB.AUTHOR'中的列。

以下是我两个实体：

AuthorDAO.java

package entity;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import core.*;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class AuthorDAO implements DAO<Author>
{   
    public AuthorDAO() {
        
    }
    List<Author> authors;

    /**
     * 获取单个作者实体作为作者对象
     * @param id
     * @return 
     */
    @Override
    public Optional<Author> get(int id) {
        DB db = DB.getInstance();
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM Author WHERE ID = ?";
            PreparedStatement stmt = db.getPreparedStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            Author author = null;
            while (rs.next()) {
                author = new Author(rs.getInt("ID"), rs.getString("FIRSTNAME"), rs.getString("LASTNAME"));
            }
            return Optional.ofNullable(author);
        } catch (SQLException ex) {
            System.err.println(ex.toString());
            return null;
        }
    }
    
    /**
     * 获取所有作者实体作为列表
     * @return 
     */
    @Override
    public List<Author> getAll() {
        DB db = DB.getInstance();
        ResultSet rs = null;
        authors = new ArrayList<>();
        try {
            String sql = "SELECT * FROM Author";
            rs = db.executeQuery(sql);
            Author author = null;
            while (rs.next()) {
                author = new Author(rs.getInt("ID"), rs.getString("FIRSTNAME"), rs.getString("LASTNAME"));
                authors.add(author);
            }
            return authors;
        } catch (SQLException ex) {
            System.err.println(ex.toString());
            return null;
        }
    }
    
    /**
     * 将作者对象插入作者表中
     * @param author
     */
    @Override
    public void insert(Author author)
    {
        DB db = DB.getInstance();
        try {
            String sql = "INSERT INTO Author(ID, FIRSTNAME, LASTNAME) VALUES (?, ?, ?)";
            PreparedStatement stmt = db.getPreparedStatement(sql);
            stmt.setInt(1, author.getID());
            stmt.setString(2, author.getFirstName());
            stmt.setString(3, author.getLastName());
            int rowInserted = stmt.executeUpdate();
            if (rowInserted > 0) {
                System.out.println("成功插入新作者！");
            }
        } catch (SQLException ex) {
            System.err.println(ex.toString());
        }
    }
    
    /**
     * 如果存在，使用作者对象在数据库中更新作者实体
     * @param author
     */
    @Override
    public void update(Author author) {
        DB db = DB.getInstance();
        try {
            String sql = "UPDATE Author SET FIRSTNAME=?, LASTNAME=? WHERE ID=?";
            PreparedStatement stmt = db.getPreparedStatement(sql);
            stmt.setString(1, author.getFirstName());
            stmt.setString(2, author.getLastName());
            stmt.setInt(3, author.getID());
            int rowsUpdated = stmt.executeUpdate();
            if (rowsUpdated > 0) {
                System.out.println("成功更新现有作者！");
            }
        } catch (SQLException ex) {
            System.err.println(ex.toString());
        }
    }
    
    /**
     * 如果实体存在，从作者表中删除作者
     * @param author
     */
    @Override
    public void delete(Author author) {
        DB db = DB.getInstance();
        try {
            String sql = "DELETE FROM Author WHERE ID = ?";
            PreparedStatement stmt = db.getPreparedStatement(sql);
            stmt.setInt(1, author.getID());
            int rowsDeleted = stmt.executeUpdate();
            if (rowsDeleted > 0) {
                System.out.println("成功删除作者！");
            }
        } catch (SQLException ex) {
            System.err.println(ex.toString());
        }
    }
    
    /**
     * 获取列名的列表数组
     * @return 
     */
    @Override
    public List<String> getColumnNames() {
        DB db = DB.getInstance();
        ResultSet rs = null;
        List<String> headers = new ArrayList<>();
        try {
            String sql = "SELECT * FROM Author WHERE ID = -1";//我们只需要这个SQL查询来获取列头
            rs = db.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取结果集中的列数
            int numberCols = rsmd.getColumnCount();
            for (int i = 1; i <= numberCols; i++) {
                headers.add(rsmd.getColumnLabel(i));//将列头添加到列表中
            }
            return headers;
        } catch (SQLException ex) {
            System.err.println(ex.toString());
            return null;
        } 
    }
}

BookDAO.java

package entity;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import core.*;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class BookDAO implements DAO<Book>
{
    public BookDAO() {

    }
    List<Book> books;

    /**
     * 获取单个书籍实体作为书籍对象
     * @param id
     * @return
     */
    @Override
    public Optional<Book> get(int id) {
        DB db = DB.getInstance();
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM Book WHERE ID = ?";
            PreparedStatement stmt = db.getPreparedStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            Book book = null;
            while (rs.next()) {
                book = new Book(rs.getInt("ISBN"), rs.getString("TITLE"), rs.getInt("AUTHORID"));
            }
            return Optional.ofNullable(book);
        } catch (SQLException ex) {
            System.err.println(ex.toString());
            return null;
        }
    }

    /**
     * 获取所有书籍实体作为列表
     * @return
     */
    @Override
    public List<Book> getAll() {
        DB db = DB.getInstance();
        ResultSet rs = null;
        books = new ArrayList<>();
        try {
            String sql = "SELECT * FROM Book";
            rs = db.executeQuery(sql);
            Book book = null;
            while (rs.next()) {
                book = new Book(rs.getInt("ISBN"), rs.getString("TITLE"), rs.getInt("AUTHORID"));
                books.add(book);
            }
            return books;
        } catch (SQLException ex) {
            System.err.println(ex.toString());
            return null;
        }
    }

    /**
     * 将书籍对象插入书籍表中
     * @param book
     */
    @Override
    public void insert(Book book)
    {
        DB db = DB.getInstance();
        try {
            String sql = "INSERT INTO Book(ISBN, title, authorid) VALUES (?, ?, ?)";
            PreparedStatement stmt = db.getPreparedStatement(sql);
            stmt.setInt(1

<details>
<summary>英文:</summary>

I&#39;m working with a local derby server and I&#39;m having issues with an error saying java.sql.SQLSyntaxErrorException: &#39;ID&#39; is not a column in table or VTI &#39;AUTHORBOOKDB.AUTHOR&#39;.
I have my database set up and and ID is showing in the schema AUTHORBOOK.AUTHOR. 

Here are my two entities:

    AuthorDAO.java
    
    package entity;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Optional;
    import core.*;
    import java.sql.ResultSet;
    import java.sql.PreparedStatement;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    
    public class AuthorDAO implements DAO&lt;Author&gt;
    {   
        public AuthorDAO() {
            
        }
        List&lt;Author&gt; authors;
    
        /**
         * Get a single author entity as a author object
         * @param id
         * @return 
         */
        @Override
        public Optional&lt;Author&gt; get(int id) {
            DB db = DB.getInstance();
            ResultSet rs = null;
            try {
                String sql = &quot;SELECT * FROM Author WHERE ID = ?&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, id);
                rs = stmt.executeQuery();
                Author author = null;
                while (rs.next()) {
                    author = new Author(rs.getInt(&quot;ID&quot;), rs.getString(&quot;FIRSTNAME&quot;), rs.getString(&quot;LASTNAME&quot;));
                }
                return Optional.ofNullable(author);
            } catch (SQLException ex) {
                System.err.println(ex.toString());
                return null;
            }
        }
        
        /**
         * Get all author entities as a List
         * @return 
         */
        @Override
        public List&lt;Author&gt; getAll() {
            DB db = DB.getInstance();
            ResultSet rs = null;
            authors = new ArrayList&lt;&gt;();
            try {
                String sql = &quot;SELECT * FROM Author&quot;;
                rs = db.executeQuery(sql);
                Author author = null;
                while (rs.next()) {
                    author = new Author(rs.getInt(&quot;ID&quot;), rs.getString(&quot;FIRSTNAME&quot;), rs.getString(&quot;LASTNAME&quot;));
                    authors.add(author);
                }
                return authors;
            } catch (SQLException ex) {
                System.err.println(ex.toString());
                return null;
            }
        }
        
        /**
         * Insert a author object into author table
         * @param author
         */
        @Override
        public void insert(Author author)
        {
            DB db = DB.getInstance();
            try {
                String sql = &quot;INSERT INTO Author(ID, FIRSTNAME, LASTNAME) VALUES (?, ?, ?)&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, author.getID());
                stmt.setString(2, author.getFirstName());
                stmt.setString(3, author.getLastName());
                int rowInserted = stmt.executeUpdate();
                if (rowInserted &gt; 0) {
                    System.out.println(&quot;A new author was inserted successfully!&quot;);
                }
            } catch (SQLException ex) {
                System.err.println(ex.toString());
            }
        }
        
        /**
         * Update a author entity in database if it exists using a author object
         * @param author
         */
        @Override
        public void update(Author author) {
            DB db = DB.getInstance();
            try {
                String sql = &quot;UPDATE Author SET FIRSTNAME=?, LASTNAME=? WHERE ID=?&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setString(1, author.getFirstName());
                stmt.setString(2, author.getLastName());
                stmt.setInt(4, author.getID());
                int rowsUpdated = stmt.executeUpdate();
                if (rowsUpdated &gt; 0) {
                    System.out.println(&quot;An existing author was updated successfully!&quot;);
                }
            } catch (SQLException ex) {
                System.err.println(ex.toString());
            }
        }
        
        /**
         * Delete a author from author table if the entity exists
         * @param author
         */
        @Override
        public void delete(Author author) {
            DB db = DB.getInstance();
            try {
                String sql = &quot;DELETE FROM Author WHERE ID = ?&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, author.getID());
                int rowsDeleted = stmt.executeUpdate();
                if (rowsDeleted &gt; 0) {
                    System.out.println(&quot;A author was deleted successfully!&quot;);
                }
            } catch (SQLException ex) {
                System.err.println(ex.toString());
            }
        }
        
        /**
         * Get all column names in a list array
         * @return 
         */
        @Override
        public List&lt;String&gt; getColumnNames() {
            DB db = DB.getInstance();
            ResultSet rs = null;
            List&lt;String&gt; headers = new ArrayList&lt;&gt;();
            try {
                String sql = &quot;SELECT * FROM Author WHERE ID = -1&quot;;//We just need this sql query to get the column headers
                rs = db.executeQuery(sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                //Get number of columns in the result set
                int numberCols = rsmd.getColumnCount();
                for (int i = 1; i &lt;= numberCols; i++) {
                    headers.add(rsmd.getColumnLabel(i));//Add column headers to the list
                }
                return headers;
            } catch (SQLException ex) {
                System.err.println(ex.toString());
                return null;
            } 
        }
    }




    BookDAO.java 
   
        package entity;
        import java.util.ArrayList;
        import java.util.List;
        import java.util.Optional;
        import core.*;
        import java.sql.ResultSet;
        import java.sql.PreparedStatement;
        import java.sql.ResultSetMetaData;
        import java.sql.SQLException;

 
    public class BookDAO implements DAO&lt;Book&gt;
    {
        public BookDAO() {
    
        }
        List&lt;Book&gt; books;
    
        /**
         * Get a single book entity as a book object
         * @param id
         * @return
         */
        @Override
        public Optional&lt;Book&gt; get(int id) {
            DB db = DB.getInstance();
            ResultSet rs = null;
            try {
                String sql = &quot;SELECT * FROM Book WHERE ID = ?&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, id);
                rs = stmt.executeQuery();
                Book book = null;
                while (rs.next()) {
                    book = new Book(rs.getInt(&quot;ISBN&quot;), rs.getString(&quot;TITLE&quot;), rs.getInt(&quot;AUTHORID&quot;));
                }
                return Optional.ofNullable(book);
            } catch (SQLException ex) {
                System.err.println(ex.toString());
                return null;
            }
        }
    
        /**
         * Get all book entities as a List
         * @return
         */
        @Override
        public List&lt;Book&gt; getAll() {
            DB db = DB.getInstance();
            ResultSet rs = null;
            books = new ArrayList&lt;&gt;();
            try {
                String sql = &quot;SELECT * FROM Book&quot;;
                rs = db.executeQuery(sql);
                Book book = null;
                while (rs.next()) {
                    book = new Book(rs.getInt(&quot;ISBN&quot;), rs.getString(&quot;FIRSTNAME&quot;), rs.getInt(&quot;AUTHORID&quot;));
                    books.add(book);
                }
                return books;
            } catch (SQLException ex) {
                System.err.println(ex.toString());
                return null;
            }
        }
    
        /**
         * Insert a book object into book table
         * @param book
         */
        @Override
        public void insert(Book book)
        {
            DB db = DB.getInstance();
            try {
                String sql = &quot;INSERT INTO Book(ISBN, title, authorid) VALUES (?, ?, ?)&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, book.getIsbn());
                stmt.setString(2, book.getTitle());
                stmt.setInt(3, book.getauthorID());
                int rowInserted = stmt.executeUpdate();
                if (rowInserted &gt; 0) {
                    System.out.println(&quot;A new book was inserted successfully!&quot;);
                }
            } catch (SQLException ex) {
                System.err.println(ex.toString());
            }
        }
    
        /**
         * Update a book entity in database if it exists using a book object
         * @param book
         */
        @Override
        public void update(Book book) {
            DB db = DB.getInstance();
            try {
                String sql = &quot;UPDATE Book SET ISBN=?, TITLE=?, WHERE ID=?&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, book.getIsbn());
                stmt.setString(2, book.getTitle());
                stmt.setInt(4, book.getauthorID());
                int rowsUpdated = stmt.executeUpdate();
                if (rowsUpdated &gt; 0) {
                    System.out.println(&quot;An existing book was updated successfully!&quot;);
                }
            } catch (SQLException ex) {
                System.err.println(ex.toString());
            }
        }
    
        /**
         * Delete a book from book table if the entity exists
         * @param book
         */
        @Override
        public void delete(Book book) {
            DB db = DB.getInstance();
            try {
                String sql = &quot;DELETE FROM Book WHERE ID = ?&quot;;
                PreparedStatement stmt = db.getPreparedStatement(sql);
                stmt.setInt(1, book.getIsbn());
                int rowsDeleted = stmt.executeUpdate();
                if (rowsDeleted &gt; 0) {
                    System.out.println(&quot;A book was deleted successfully!&quot;);
                }
            } catch (SQLException ex) {
                System.err.println(ex.toString());
            }
        }
    
        /**
         * Get all column names in a list array
         * @return
         */
        @Override
        public List&lt;String&gt; getColumnNames() {
            DB db = DB.getInstance();
            ResultSet rs = null;
            List&lt;String&gt; headers = new ArrayList&lt;&gt;();
            try {
                String sql = &quot;SELECT * FROM Book WHERE ID = -1&quot;;//We just need this sql query to get the column headers
                rs = db.executeQuery(sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                //Get number of columns in the result set
                int numberCols = rsmd.getColumnCount();
                for (int i = 1; i &lt;= numberCols; i++) {
                    headers.add(rsmd.getColumnLabel(i));//Add column headers to the list
                }
                return headers;
            } catch (SQLException ex) {
                System.err.println(ex.toString());
                return null;
            }
        }
    }

Also here&#39;s the stacktrace:

    INFO: Flyway 3.2.1 by Boxfuse
    Apr 06, 2020 8:13:05 PM org.flywaydb.core.internal.dbsupport.DbSupportFactory createDbSupport
    INFO: Database: jdbc:derby:authorbookdb (Apache Derby 10.10)
    Apr 06, 2020 8:13:05 PM org.flywaydb.core.internal.metadatatable.MetaDataTableImpl removeFailedMigrations
    INFO: Repair of failed migration in metadata table &quot;AUTHORBOOKDB&quot;.&quot;schema_version&quot; not necessary. No failed migration detected.
    Apr 06, 2020 8:13:05 PM org.flywaydb.core.internal.command.DbRepair repair
    INFO: Metadata table &quot;AUTHORBOOKDB&quot;.&quot;schema_version&quot; successfully repaired (execution time 00:00.230s).
    Apr 06, 2020 8:13:05 PM org.flywaydb.core.internal.command.DbValidate validate
    INFO: Validated 2 migrations (execution time 00:00.007s)
    Apr 06, 2020 8:13:05 PM org.flywaydb.core.internal.command.DbMigrate migrate
    INFO: Current version of schema &quot;AUTHORBOOKDB&quot;: 1
    Apr 06, 2020 8:13:05 PM org.flywaydb.core.internal.command.DbMigrate logSummary
    INFO: Schema &quot;AUTHORBOOKDB&quot; is up to date. No migration necessary.
    java.sql.SQLSyntaxErrorException: &#39;ID&#39; is not a column in table or VTI &#39;AUTHORBOOKDB.AUTHOR&#39;.


Thanks for your help!




</details>