java - 架构 'TEST' 不存在

Question

我是使用数据库连接创建 Java 程序的新手。我试图让程序创建一个表，读取一个表，以便我可以运行查询并显示某些数据。据我所知，我的程序已成功连接到数据库，但收到错误:

Syntax error: Encountered ")" at line 8, column 1.
Schema 'TEST' does not exist
Schema 'TEST' does not exist
Schema 'TEST' does not exist
Schema 'TEST' does not exist

其他错误，我习惯接收 # 行，这样我至少知道从哪里开始查找。对于行和列 #，我不确定，我查看了其他帖子并尝试进行更新，例如将 APP 设置为默认架构。有助于插入正确的方向，让我们从哪里开始寻找。一旦我弄清楚如何克服这个问题并打印查询，我知道我就可以开始了。感谢您提供的任何帮助。

import static java.lang.System.out;
import java.sql.*;
import java.sql.SQLException;

public class AnimalDB1 {

private static final String url = "jdbc:derby://localhost:1527/AnimalDB;create=true;user=test;password=test";
private static final String tableName = "Animal";
private static Connection conn = null;
private static int nextId = 1;
private boolean tablesCreated = false;

private static void createConnection(){
try{
    System.out.println("Connecting to Database...");
    conn = DriverManager.getConnection(url);
    System.out.println("Database Connection Successful\n");
}
catch (SQLException e){}

}

// Increments the ID number for each animal
private void incId(){
    AnimalDB1.nextId++;
}

private void animalTable() throws SQLException{
    Statement statement = null;

    try{
    StringBuilder sb = new StringBuilder("");
        sb.append("CREATE table Animal (\n");
            sb.append("ID INTEGER NOT NULL,\n");
            sb.append("AnimalName varchar(15),\n");
            sb.append("Char1 varchar(15),\n");
            sb.append("Char2 varchar(15),\n");
            sb.append("Char3 varchar(15),\n");
            sb.append("Char4 varchar(15),\n");

        sb.append(")\n");

        // Get a statement from the connection so we can execute the query.
        statement = conn.createStatement();
        statement.executeUpdate(sb.toString());
        tablesCreated = true;
    } catch (Exception e){
        System.out.println(e.getMessage());
    } finally {
        if(statement != null){
            try {
                statement.close();
            } 
            catch(Exception e){
                System.err.println(e.getMessage());
                System.exit(0); // Something is terribly wrong so just quit the program.
            }
        }
    }
}

private void createAnimal (String animalName, String char1, String char2, String char3, String char4){
    PreparedStatement pState = null;
    try{
        String sql = "Insert into Animal values (?,?,?,?,?,?)";
        pState = conn.prepareStatement(sql);
        pState.setInt(1, nextId);
        pState.setString(2, animalName);
        pState.setString(3, char1);
        pState.setString(4, char2);
        pState.setString(5, char3);
        pState.setString(6, char3);

        pState.executeUpdate();
        pState.close();
        incId();
    }
    catch (SQLException e){
        System.err.println(e.getMessage());
    }
}

private static void closeConnection() {
    try {
        // Close the connection
        if(conn != null){
            conn.close();
        }

    } catch(Exception e){
        System.out.println(e.getMessage());
    }
}

public static void queryShowAnimals() throws SQLException{
    String query = "SELECT * FROM Animal";
    try{
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()){
            out.print(rs.getInt(nextId) + " ");
            out.print(rs.getString("animalName") + "  ");
            out.print(rs.getString("char1") + ", ");
            out.print(rs.getString("char2") + ", ");
            out.print(rs.getString("char3") + ", ");
            out.print(rs.getString("char4") + ", ");                               
        }
    }catch (SQLException se){}

}
public static void main(String[] args) throws ClassNotFoundException, SQLException {

    AnimalDB1 db = new AnimalDB1();

    AnimalDB1.createConnection();

    System.out.println("Welcome to the Animal Database");
    System.out.println("The list below shows all of the animals currently "
                       + "stored in the database\n");

    db.animalTable();

    db.createAnimal("Huskie", "White", "Long hair", "Four legs", "Paws");
    db.createAnimal("Salmon", "Silver", "Scales", "Fins", "Swims");
    db.createAnimal("Crow", "Black", "Feathers", "Claws", "Flies");
    db.createAnimal("Black Snake", "Black", "Scales", "No Appendages", "Slithers");              

    AnimalDB1.queryShowAnimals();

    closeConnection();
}

}

Answer 1

有两个打字错误:

...
StringBuilder sb = new StringBuilder("");
sb.append("CREATE table Animal (\n");
    sb.append("ID INTEGER NOT NULL,\n");
    sb.append("AnimalName varchar(15),\n");
    sb.append("Char1 varchar(15),\n");
    sb.append("Char2 varchar(15),\n");
    sb.append("Char3 varchar(15),\n");
    sb.append("Char4 varchar(15)\n"); // <-- unnecessary comma

sb.append(")\n");
...

和:

...
while (rs.next()){
    out.print(rs.getInt("ID") + " "); // <-- invalid column identifier
    out.print(rs.getString("animalName") + "  ");
    out.print(rs.getString("char1") + ", ");
    out.print(rs.getString("char2") + ", ");
    out.print(rs.getString("char3") + ", ");
    out.print(rs.getString("char4") + ", ");                               
}
...

此外，我相信您想使用嵌入式数据库。因此，您需要加载相应的驱动程序(自 Java 6 起可选步骤):

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

网址:

jdbc:derby:AnimalDB;create=true;user=test;password=test