我正在编写一个Java程序，为大学作业创建、写入、编辑和删除derby数据库的实例。我已经更正了所有其他错误，但正在抛出SQL异常SQL State: 42X01
Error Code: 20000
Message: Syntax error: Encountered "(" at line 1, column 99.
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "(" at
line 1, column 99.
我是SQL的新手，看不出错误在哪里。这是我的SQL文件：
package animaljdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
public class DataBase {
private static final DataBase DATABASE_INSTANCE = new DataBase(); // Singleton
private DataBase() {} // a private constructor so no callers can instantiate the singleton object directly
public static synchronized DataBase getInstance() { // a public static method for callers to get a reference to the singleton instance
return DATABASE_INSTANCE;
}
Utility utils = Utility.getInstance(); // Create a new Utilities
private final String dbName = "AnimalsDB";
private final String tableName = "Animals"; // Name of the table
private final String framework = "embedded";
private final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
private final String protocol = "jdbc:derby:";
private final String Table = "create table Animals(type varchar(32), color varchar(32), gender varchar(32)," // Query to create Table
+ " isVertebrate Boolean(32), canSwim Boolean(32))";
Connection conn = null;
private final ArrayList statements = new ArrayList(); // List of Statements, PreparedStatements, flushed after every method run
PreparedStatement psInsert = null;
PreparedStatement psUpdate = null;
Statement s = null;
ResultSet rs = null;
Properties props = new Properties(); // connection properties
public void loadDBdriver() { // Loads the appropriate JDBC driver for this environment/framework. For
try { // example, if we are in an embedded environment, we load Derby's
Class.forName(driver).newInstance(); // embedded Driver, <code>org.apache.derby.jdbc.EmbeddedDriver</code>.
System.out.println("Loaded the appropriate driver");
} catch (ClassNotFoundException cnfe) {
System.err.println("nUnable to load the JDBC driver " + driver);
System.err.println("Please check your CLASSPATH.");
cnfe.printStackTrace(System.err);
} catch (InstantiationException ie) {
System.err.println(
"nUnable to instantiate the JDBC driver " + driver);
ie.printStackTrace(System.err);
} catch (IllegalAccessException iae) {
System.err.println(
"nNot allowed to access the JDBC driver " + driver);
iae.printStackTrace(System.err);
}
}
protected void createDB() throws UnsupportedOperationException {
System.out.println("Database in " + framework + " mode");
loadDBdriver(); // Load the desired JDBC driver
try { // load JDBC
props.put("user", "user1"); // providing a user name and password is optional in the embedded
props.put("password", "user1");
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props); // Setup the connection to the database
System.out.println("Database" + dbName + "created and connected ");
conn.setAutoCommit(false); // We want to control transactions manually. Autocommit is on by default in JDBC.
s = conn.createStatement(); // Creating a statement object that we can use for running various SQL statements commands against the database.
statements.add(s);
s.execute(Table); // We create a table...
System.out.println("Created table " + tableName);
statements.clear();
} catch (SQLException sqle) {
printSQLException(sqle);
}
}
//@param e the SQLException from which to print details.
public static void printSQLException(SQLException e) { // Prints details of an SQLException chain to <code>System.err</code>.
while (e != null) // Details included are SQL State, Error code, Exception message.
{ // Unwraps the entire exception chain to unveil the real cause of the Exception.
System.err.println("n----- SQLException -----");
System.err.println(" SQL State: " + e.getSQLState());
System.err.println(" Error Code: " + e.getErrorCode());
System.err.println(" Message: " + e.getMessage());
e.printStackTrace(System.err);
e = e.getNextException();
}
}
/**
* @param type
* @param color
* @param gender
* @param isVertebrate
* @param canSwim
* @return
*/
public boolean insertObject(String type, String color, String gender, String isVertebrate, String canSwim) { // This inserts a new animal into the database
boolean condition = false; // Return statement
psInsert = null;
try { // Exception handlers
psInsert = conn.prepareStatement("INSERT INTO " + tableName + " (type, color, gender, isVertebrate, canSwim) VALUES (?, ?, ?, ?, ?)");
statements.add(psInsert); // Prepare for insert()
psInsert.setString(1, type); // Insert the input
psInsert.setString(2, color);
psInsert.setString(3, gender);
psInsert.setString(4, isVertebrate);
psInsert.setString(5, canSwim);
psInsert.executeUpdate(); // Well....execute
statements.clear(); // Clear statements
condition = true;
} catch (SQLException sqle) {
printSQLException(sqle);
}
return condition;
}
ArrayList<Utility> getAnimals() {
ArrayList<Utility> animalium = new ArrayList<>(); // To hold the animals we want to return
rs = null;
try {
rs = s.executeQuery("SELECT * FROM " + tableName + " ORDER BY type");
while (rs.next()) { // temp animal to push to the arraylist
Utility temporaryAnimalium = new Utility(rs.getInt(1), // ID
rs.getString(2), // Type
rs.getString(3), // Color
rs.getString(4), // Gender
rs.getString(5), // is Vertebrate
rs.getString(6)); // canSwim
animalium.add(temporaryAnimalium); // Add to the arraylist
}
try { // Release resources
if (rs != null) {
rs.close();
rs = null;
}
} catch (SQLException sqle) {
printSQLException(sqle);
}
statements.clear(); // Clear statements
} catch (SQLException sqle) {
printSQLException(sqle);
}
return animalium; // Return our animal list
}
/**
* @return
*/
public boolean closeDB() { // Closes out the connection to the database and clears statements
boolean condition = false; // Return statements
statements.clear(); // Clear statements
try { // Exception handlers
s.execute("DROP TABLE " + tableName); // Drop the table
conn.commit(); // We commit the transaction. Any changes will be persisted to the database now.
if (framework.equals("embedded")) {
try {
DriverManager.getConnection("jdbc:derby:;shutdown=true"); // the shutdown=true attribute shuts down Derby
} catch (SQLException se) {
if (((se.getErrorCode() == 50000) && ("XJ015".equals(se.getSQLState())))) {
System.out.println("Database shut down normally"); // we got the expected exception
} else {
System.err.println("Database did not shut down normally"); // if the error code or SQLState is different, we have an unexpected exception (shutdown failed)
printSQLException(se);
}
}
}condition = true;
} catch (SQLException sqle) {
printSQLException(sqle);
} finally {
int i = 0; // release all open resources to avoid unnecessary memory usage
while (!statements.isEmpty()) { // Statements and PreparedStatements
Statement st = (Statement)statements.remove(i); // PreparedStatement extend Statement
try {
if (st != null) {
st.close();
st = null;
}
} catch (SQLException sqle) {
printSQLException(sqle);
}
}
try { //Connection
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException sqle) {
printSQLException(sqle);
}
}
return condition;
}
public boolean updateDB(int id, String type, String color, String gender, String isVertebrate, String canSwim) {
boolean condition = false; // Return statement
psUpdate = null;
try { // Exception handlers
psUpdate = conn.prepareStatement("UPDATE " + tableName + " SET type=?, color=?, gender=?, isVertebrate=?, canSwim=? WHERE id=?");
statements.add(psUpdate); // Prepare the statement for insertion of values
psUpdate.setString(1, type); // Update and set integers and strings, inserting the values
psUpdate.setString(2, color);
psUpdate.setString(3, gender);
psUpdate.setString(4, isVertebrate);
psUpdate.setString(5, canSwim);
psUpdate.setInt(6, id);
psUpdate.executeUpdate(); // Well....execute
statements.clear(); // Clear statements
condition = true;
} catch (SQLException sqle) {
printSQLException(sqle); }
return condition;
}
/**
* @param message
*/
public void reportFailure(String message) { // Reports a data verification failure to System.err with the given message.
System.err.println("nData could not be verified: ");
System.err.println('t' + message);
}
public boolean delete(int id) {
boolean state = false; // Return statement
psUpdate = null;
try { // Exception handlers
psUpdate = conn.prepareStatement("DELETE FROM " + tableName + " WHERE id=?"); // Statement preparation
statements.add(psUpdate);
psUpdate.setInt(1, id); // ID we wish to delete
psUpdate.executeUpdate(); // Well....execute
statements.clear(); // Clear statements
state = true;
} catch (SQLException sqle) {
printSQLException(sqle);
}
return state;
}
// method to close the dB connection
protected void finishDB(){ // method to close the dB connection
if (framework.equals("embedded"))
{
try
{
DriverManager.getConnection("jdbc:derby:;shutdown=true"); // the shutdown=true attribute shuts down Derby
} // To shut down a specific database only, but keep the
catch (SQLException se) // engine running (for example for connecting to other
{ // databases), specify a database in the connection URL:
if (( (se.getErrorCode() == 50000) //DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
&& ("XJ015".equals(se.getSQLState()) ))) {
System.out.println("Derby shut down normally"); // we got the expected exception
System.out.println(); // Note that for single database shutdown, the expected
} else { // SQL state is "08006", and the error code is 45000.
System.err.println("Derby did not shut down normally"); // if the error code or SQLState is different, we have
System.out.println(); // an unexpected exception (shutdown failed)
printSQLException(se);
}
}
}
}
}
这是打印的堆栈跟踪：
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at animaljdbc.DataBase.createDB(DataBase.java:74)
at animaljdbc.AnimalJDBC.main(AnimalJDBC.java:29)
Caused by: java.sql.SQLException: Syntax error: Encountered "(" at line 1, column 99.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 10 more
Caused by: ERROR 42X01: Syntax error: Encountered "(" at line 1, column 99.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 4 more
如何读取堆栈跟踪？我的错误在哪里？我该如何修复它？
有问题的SQL似乎是：
create table Animals(type varchar(32), color varchar(32),
gender varchar(32), isVertebrate Boolean(32), canSwim Boolean(32)
我认为第99列在Boolean之后的(32)附近。这向我建议您应该在Boolean的两个实例之后删除(32)。(这假设Boolean是数据库中支持的类型。(
您修改后的SQL是：
create table Animals(id int(10), type varchar(32), color varchar(32),
gender varchar(32), isVertebrate String, canSwim String)
我怀疑这一次您的错误(第30列(与新id列的int类型后面的(10)有关。如果你卸下(10)，你应该没事。