SQL语法错误尝试将行插入表时出现异常 [英] SQL Syntax Error Exception when trying to insert row into table
问题描述
您好我在执行以下功能时遇到了问题而没有遇到以下异常。我不确定为什么会这样。我认为它可能与引号有关。我正在使用德比数据库。
Hi I am having trouble executing the following function without running into the following exception. I'm not sure why this is happening. I think it might have something to do with the quotes. I am using derby database if it matters.
java.sql.SQLSyntaxErrorException
这是我试图执行的以下代码:
This is the following code I am trying to execute:
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 statements don't need semicolon
;
at the end. It will make the code fail.
代码很容易出现SQL注入,很难维护。改为使用 PreparedStatement
:
The code is prone to SQL Injection and is hard to maintain. Use a PreparedStatement
instead:
这应该是有效的代码:
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
参数将转义任何'
和等你的角色。
The big difference between plain string concatenation and this approach for your case is that PreparedStatement
parameters will escape any '
and "
and other characters for you.
这篇关于SQL语法错误尝试将行插入表时出现异常的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!