java 我们如何使用prepareStatement()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1908652/
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
how can we use prepareStatement()?
提问by Johanna
I use prepareStatement() when the id is a key of my SQL table and it will be created by SQL and I want to use this statement :(what should I write instead of X in the first column of SQL table(reminder:SQL create it automatically)
当 id 是我的 SQL 表的键并且它将由 SQL 创建并且我想使用此语句时，我使用 prepareStatement() :(我应该在 SQL 表的第一列中写什么而不是 X（提醒：SQL create它自动）
File file = new File(pathFile);
FileInputStream input = new FileInputStream(file);
query = ("insert into birthtable VALUES(?,?,?,?,?,?,?,?)");
pstmt = (PreparedStatement) conn.prepareStatement(query);
pstmt.setInt(1,**X** )
pstmt.setString(2, name);
pstmt.setString(3, family);
pstmt.setString(4, fatherName);
pstmt.setString(5, mName);
pstmt.setString(6, dOfBirth);
pstmt.setString(7, pOfBirth);
// Method used to insert a stream of bytes
pstmt.setBinaryStream(8, input);
pstmt.executeUpdate();
I have done what you all say but I have this exception??
我已经按照你们说的做了，但我有这个例外？？
java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
at database.Manager.addBirth(Manager.java:76)
at AdminGUI.AddNewBornInformation.submit(AddNewBornInformation.java:358)
at AdminGUI.AddNewBornInformation.setButtonActionPerformed(AddNewBornInformation.java:282)
at AdminGUI.AddNewBornInformation.access0(AddNewBornInformation.java:28)
at AdminGUI.AddNewBornInformation.actionPerformed(AddNewBornInformation.java:139)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6038)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
回答by Joel
If you have specified the type of that column as int and auto-increment e.g.
如果您已将该列的类型指定为 int 并自动递增，例如
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
then you don't need to supply any value at all, so you can start the prepared statement params at 1 with name.
那么您根本不需要提供任何值，因此您可以使用名称从 1 开始准备好的语句参数。
query = ("insert into birthtable (nameCol, familyCol, fatherNameCol, mNameCol, dOfBirthCol, pOfBirthCol, inputCol) VALUES(?,?,?,?,?,?,?)");
pstmt = (PreparedStatement) conn.prepareStatement(query);
pstmt.setString(1, name);
pstmt.setString(2, family);
pstmt.setString(3, fatherName);
pstmt.setString(4, mName);
pstmt.setString(5, dOfBirth);
pstmt.setString(6, pOfBirth);
// Method used to insert a stream of bytes
pstmt.setBinaryStream(7, input);
Note that, as others have said, you must include the column names, whatever they might be.
请注意，正如其他人所说，您必须包括列名称，无论它们是什么。
回答by BalusC
You don't need to specify it yourself, so you can just leave it away. But you should however specify the columns yourself, e.g.
你不需要自己指定它，所以你可以把它放在一边。但是您应该自己指定列，例如
"INSERT INTO tablename (columnname1, columnname2, columnname3) VALUES (?, ?, ?);"
otherwise the DB wouldn't know where to insert the values because there is one column missing.
否则数据库将不知道在哪里插入值，因为缺少一列。
回答by Thomas Jung
You do not set the id parameter in the prepared statement assuming you're using auto increment and the column names are valid:
假设您使用自动增量并且列名有效，则不要在准备好的语句中设置 id 参数：
query = ("insert into birthtable (name, family, fatherName, mName, dOfBirth, pOfBirth, input) VALUES(?,?,?,?,?,?,?)");
pstmt = conn.prepareStatement(query);
pstmt.setString(1, name);
pstmt.setString(2, family);
pstmt.setString(3, fatherName);
pstmt.setString(4, mName);
pstmt.setString(5, dOfBirth);
pstmt.setString(6, pOfBirth);
pstmt.setBinaryStream(7, input);
You should state the column names explicitly otherwise you depend on the order in the table create statement.
您应该明确说明列名，否则取决于 table create 语句中的顺序。
回答by Alexander Pogrebnyak
My only 2 cents is that if you want a generated ID, you may call
我唯一的 2 美分是，如果你想要一个生成的 ID，你可以打电话
pstmt.getGeneratedKeys( );
If it's going to return anything valid depends on your JDBC driver implementation though.
如果它将返回任何有效内容取决于您的 JDBC 驱动程序实现。
