Java jdbc 使用自动增量字段插入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42908548/
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
jdbc insert into database with auto increment field
提问by hDDen
I have this in my code:
我的代码中有这个：
String query
= "INSERT INTO usermail (FirstName, LastName, Town, Country, Email) "
+ "VALUES (?, ?, ?, ? ,?)";
My table is as follows :
我的表如下：
UserID – AutoNumber (Primary Key)
FirstName – varchar(15)
astName – varchar (15)
Town – varchar(15)
Country – varchar(15)
Email – varchar(20)
The thing is that when I fill the form for the first time all the values are added into the database, but if I try to do it the second time they won't be added. If I remove the UserID table everything works perfectly. This is how I created UserID (image taken from - here)
问题是，当我第一次填写表单时，所有值都被添加到数据库中，但如果我第二次尝试这样做，它们将不会被添加。如果我删除 UserID 表，一切正常。这就是我创建 UserID 的方式（图片取自 -此处）
I am unsure on how to make it work with the UserID. I cannot input anything in there since I dont have such a field in my form.
我不确定如何使其与 UserID 一起使用。我无法在其中输入任何内容，因为我的表单中没有这样的字段。
This is what I get from stackTrace
这是我从 stackTrace 得到的
Info: visiting unvisited references
Info: visiting unvisited references
Info: Loading application [MailRegistration] at [/MailRegistration]
Info: MailRegistration was successfully deployed in 293 milliseconds.
Severe: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at Data.MailDB.insert(MailDB.java:32)
at details.DisplayUserDetailsServlet.doPost(DisplayUserDetailsServlet.java:44)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283)
at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
at org.glassfish.grizzly.filterchain.ExecutorResolver.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access0(WorkerThreadIOStrategy.java:56)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571)
at java.lang.Thread.run(Thread.java:745)
采纳答案by duffymo
I implemented your problem using MariaDB. It works fine. Here's my table creation SQL:
我使用 MariaDB 实现了您的问题。它工作正常。这是我的表创建 SQL：
create table contact (
id MEDIUMINT not null AUTO_INCREMENT,
firstName VARCHAR(64),
lastName varchar(128),
city varchar(64),
state char(2),
email VARCHAR(64),
PRIMARY KEY (id)
);
I created a model object Contact:
我创建了一个模型对象Contact：
package database.mariadb.model;
import org.apache.commons.lang3.builder.ToStringBuilder;
/**
* Created by Michael
* Creation date 3/21/2017.
* @link
*/
public class Contact {
private Long id;
private String firstName;
private String lastName;
private String city;
private String state;
private String email;
public Contact(String firstName, String lastName, String city, String state, String email) {
this(null, firstName, lastName, city, state, email);
}
public Contact(Long id, String firstName, String lastName, String city, String state, String email) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.city = city;
this.state = state;
this.email = email;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public String getLastName() {
return lastName;
}
public String getCity() {
return city;
}
public String getState() {
return state;
}
public String getEmail() {
return email;
}
@Override
public String toString() {
return new ToStringBuilder(this)
.append("id", id)
.append("firstName", firstName)
.append("lastName", lastName)
.append("city", city)
.append("state", state)
.append("email", email)
.toString();
}
}
I created a repository interface:
我创建了一个存储库接口：
package database.mariadb.persistence;
import database.mariadb.model.Contact;
import java.util.List;
/**
* Created by Michael
* Creation date 3/21/2017.
* @link
*/
public interface ContactRepository {
List<Contact> findAll();
void insert(Contact contact);
}
I implemented the interface:
我实现了接口：
package database.mariadb.persistence;
import database.mariadb.model.Contact;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* Created by Michael
* Creation date 3/21/2017.
* @link
*/
public class ContactRepositoryImpl implements ContactRepository {
private static final String FIND_ALL_SQL = "SELECT id, firstName, lastName, city, state, email from contact ";
private static final String INSERT_SQL = "INSERT INTO contact(firstName, lastName, city, state, email) values(?, ?, ?, ?, ?) ";
private Connection connection;
public ContactRepositoryImpl(DataSource dataSource) throws SQLException {
this.connection = dataSource.getConnection();
}
public ContactRepositoryImpl(Connection connection) {
this.connection = connection;
}
@Override
public List<Contact> findAll() {
List<Contact> result = new ArrayList<>();
try (Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(FIND_ALL_SQL)) {
while (rs.next()) {
Long id = rs.getLong("id");
String firstName = rs.getString("firstName");
String lastName = rs.getString("lastName");
String city = rs.getString("city");
String state = rs.getString("state");
String email = rs.getString("email");
result.add(new Contact(id, firstName, lastName, city, state, email));
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public void insert(Contact contact) {
if (contact != null) {
try (PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, contact.getFirstName());
ps.setString(2, contact.getLastName());
ps.setString(3, contact.getCity());
ps.setString(4, contact.getState());
ps.setString(5, contact.getEmail());
int numRowsAffected = ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
contact.setId(rs.getLong(1));
}
} catch (SQLException s) {
s.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
JUnit tests work perfectly.
JUnit 测试工作完美。
If I INSERT the same contact multiple times I get the expected result: multiple rows with new ids, same data.
如果我多次插入同一个联系人，我会得到预期的结果：具有新 ID 的多行，相同的数据。
回答by Riddle03
I think your code starts incrementing from the same number each time its run (not sure where you're generating the Userid). You could have a separate table with a userid, and increment it each time the program is run. This way the latest userid is stored in the "userid" table.
我认为您的代码每次运行时都会从相同的数字开始递增（不确定您在哪里生成用户 ID）。您可以有一个带有用户 ID 的单独表，并在每次程序运行时增加它。这样，最新的用户 ID 就存储在“用户 ID”表中。
You can then retrieve this value and insert into the "usermail" table. Something like this:
然后您可以检索该值并插入到“usermail”表中。像这样的东西：
int userid= "Select userid from USERID_TB";
//Execute query and get value of userid
String query
= "INSERT INTO usermail (Userid,FirstName, LastName, Town, Country, Email) "
+ "VALUES (userid,?, ?, ?, ? ,?)";
String update="update USERID_TB set userid="+(userid+1)+"where userid="+userid";
I use this design to maintain a list of userids so I always have the latest count and don't have to maintain it in my front end code. Hope this works for you!
我使用这种设计来维护用户 ID 列表，因此我始终拥有最新的计数，而不必在我的前端代码中维护它。希望这对你有用！
回答by Fredy Fischer
When you create the table this way:
当您以这种方式创建表时：
create table usermail (
UserID BIGINT NOT NULL AUTO_INCREMENT,
FirstName varchar(15),
LastName varchar(15),
Town varchar(15),
Country varchar(15),
Email varchar(2),
PRIMARY KEY (UserID)
);
You should be able to add records in the way you've tried. Probably it also works, when you click the check-Field left to 'INCREMENT BY 1'
您应该能够以您尝试过的方式添加记录。可能它也有效，当您单击左侧的检查字段到“INCREMENT BY 1”时
回答by Nagesh Dalave
Simple solution to solve this problem of auto-increment primary key is as follows: Here ID is integer but we can set string with null value for first '?'. and please don't mention column name, it will insert values as per column sequence in table.
解决这个自增主键问题的简单解决方案如下： 这里 ID 是整数，但我们可以将字符串设置为第一个 '?' 为空值。并且请不要提及列名，它会按照表中的列顺序插入值。
String insertQueryStatement = "INSERT INTO MATRIX_A VALUES (?,?,?,?,?)";
preparedStatement = connection.prepareStatement(insertQueryStatement);
preparedStatement.setString(1, null);
preparedStatement.setString(2, entity.getType());
preparedStatement.setString(3, entity.getGroup());
preparedStatement.setString(4, entity.getLoss());
preparedStatement.setString(5, entity.getSeverity());
preparedStatement.executeUpdate();
