嗨,我无法执行以下功能而没有遇到以下异常。我不确定为什么会这样。我认为这可能与报价有关。如果有问题,我正在使用derby数据库。
java.sql.SQLSyntaxErrorException
这是我尝试执行的以下代码:
public void addAlbum(Album album) throws IOException, SQLException {
Properties props = new Properties();
FileInputStream in = new FileInputStream("database.properties");
props.load(in);
in.close();
props.getProperty("jdbc.drivers");
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = null;
if(album instanceof CDAlbum) {
CDAlbum cdAlbum = (CDAlbum)album;
sql = "INSERT INTO MyAlbums VALUES ('CD', '" + cdAlbum.getTitle() + "', '" + cdAlbum.getGenre() + "','" + cdAlbum.getArtist() + "', '" + cdAlbum.getTracks() + "');";
}
if(album instanceof DVDAlbum) {
DVDAlbum dvdAlbum = (DVDAlbum)album;
sql = "INSERT INTO MyAlbums VALUES ('DVD', '" + dvdAlbum.getTitle() + "', '" + dvdAlbum.getGenre() + "','" + dvdAlbum.getDirector() + "', '" + dvdAlbum.getPlotOutline() + "');";
}
statement.executeUpdate(sql);
System.out.println("Album Added!");
if(statement != null) {
statement.close();
}
if(connection != null) {
connection.close();
}
}
这是例外:
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "t" at line 2, column 5.
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.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.executeLargeUpdate(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
at au.edu.uow.CollectionDB.MyCollectionDB.addAlbum(MyCollectionDB.java:194)
at au.edu.uow.Collection.CollectionFactory.loadCollection(CollectionFactory.java:136)
at MyCollection.main(MyCollection.java:18)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: ERROR 42X01: Syntax error: Encountered "t" at line 2, column 5.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatementOrSearchCondition(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)
... 11 more
您的代码中有两个问题:
SQL语句;
最后不需要分号。它将使代码失败。
该代码易于进行SQL注入并且难以维护。请PreparedStatement
改用:
这应该是工作代码:
String sql = "INSERT INTO MyAlbums VALUES (?, ?, ?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
if(album instanceof CDAlbum) {
pstmt.setString(1, "CD");
CDAlbum cdAlbum = (CDAlbum)album;
pstmt.setString(4, cdAlbum.getArtist());
pstmt.setString(5, cdAlbum.getTracks());
}
if(album instanceof DVDAlbum) {
pstmt.setString(1, "DVD");
DVDAlbum dvdAlbum = (DVDAlbum)album;
pstmt.setString(4, dvdAlbum.getDirector());
pstmt.setString(5, dvdAlbum.getPlotOutline());
}
pstmt.setString(2, album.getTitle());
pstmt.setString(3, album.getGenre());
pstmt.executeUpdate();
在大平原字符串连接而这种做法对于你的情况之间的区别就是PreparedStatement
参数会逃跑任何'
和"
和其他字符你。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句