无法使用 java 将 byte[] 插入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1324641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't insert byte[] into MySQL using java
提问by Asaph
Following is the code I have used:
以下是我使用过的代码：
byte[] bkey = key.getEncoded();
String query = "INSERT INTO keytable (name, key) VALUES (?,?)";
PreparedStatement pstmt = (PreparedStatement) connection.prepareStatement(query);
pstmt.setString(1, "test");
pstmt.setBytes(2, bkey);
pstmt.execute();
And following is an error I got:
以下是我得到的错误：
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key) VALUES ('test',_binary'????s??u\'?}p?u')' at line 1
I have MySQL 5.0.41 and mysql-connector-java-5.1.7-bin.jaras JDBC library.
Is anyone can help me out here?
Thanks in advance!
我有 MySQL 5.0.41 和mysql-connector-java-5.1.7-bin.jarJDBC 库。有人可以帮我吗？提前致谢！
回答by Asaph
The problem is that your column called "key" is a reserve word in SQL. Surround it with backticks and things should work. Better yet, consider renaming the column to something that is not a SQL reserve word. I've proven this out using the code below:
问题是您的名为“key”的列是 SQL 中的保留字。用反引号包围它，事情应该可以工作。更好的是，考虑将列重命名为不是 SQL 保留字的内容。我已经使用下面的代码证明了这一点：
MySQL table:
MySQL表：
create table keytable (name varchar(255) not null, `key` blob not null);
Java code:
爪哇代码：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class MySQLBlobInsert {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
throw new RuntimeException(e);
}
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
byte[] bkey = "This is some binary stuff".getBytes();
String query = "INSERT INTO keytable (name, `key`) VALUES (?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "test");
pstmt.setBytes(2, bkey);
pstmt.execute();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try { conn.close(); } catch (SQLException e) {}
}
}
System.out.println("done :)");
}
}
回答by Jonathan
Try using "setBinaryStream()" instead of "setBytes()", and pass it a ByteArrayInputStream constructed on your byte array. This, of course, assumes that the data type assigned to the column can store bytes... Make sure it is a BLOB, BINARY, or VARBINARY.
尝试使用“setBinaryStream()”而不是“setBytes()”，并将其传递给在您的字节数组上构造的 ByteArrayInputStream。当然，这假定分配给列的数据类型可以存储字节...确保它是 BLOB、BINARY 或 VARBINARY。
Also, use backticks to enclose your objects. "key" is a SQL keyword, and other than that it is just a good habit:
此外，使用反引号将您的对象括起来。“key”是一个SQL关键字，除此之外它只是一个好习惯：
String query = "INSERT INTO `keytable` (`name`, `key`) VALUES (?,?)";
回答by Rodrigo Asensio
You should add a binary stream. Do you have access to the inputstream ?? like this..
您应该添加一个二进制流。您可以访问输入流吗？像这样..
FileInputStream input = new FileInputStream("myfile.gif");
String query = "INSERT INTO `keytable` (`name`, `key`) VALUES (?,?)";
PreparedStatement pstmt = (PreparedStatement) connection.prepareStatement(query);
pstmt.setString(1, "test");
pstmt.setBinaryStream(2, input, input.available());
