jdbc插入具有自动增量字段的数据库 [英] jdbc insert into database with auto increment field
本文介绍了jdbc插入具有自动增量字段的数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的代码中有这个:
String query
= "INSERT INTO usermail (FirstName, LastName, Town, Country, Email) "
+ "VALUES (?, ?, ?, ? ,?)";
我的表格如下:
UserID – AutoNumber (Primary Key)
FirstName – varchar(15)
astName – varchar (15)
Town – varchar(15)
Country – varchar(15)
Email – varchar(20)
问题是，当我第一次填写表格时，所有值都被添加到了数据库中，但是如果我第二次尝试这样做，它们将不会被添加.如果我删除了UserID表，一切将正常运行. 这就是我创建UserID的方式(图片摘自-此处)
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一起使用.我无法在其中输入任何内容，因为我的表单中没有这样的字段.
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.
这是我从stackTrace中得到的
This is what I get from 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$9.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.access$100(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)
推荐答案
我使用MariaDB实现了您的问题.它工作正常.这是我的表创建SQL:
I implemented your problem using MariaDB. It works fine. Here's my table creation 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)
);
我创建了一个模型对象Contact:
I created a model object 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);
}
我实现了接口:
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测试可以完美地工作.
JUnit tests work perfectly.
如果我多次插入相同的联系人，我会得到预期的结果:包含新ID和相同数据的多行.
If I INSERT the same contact multiple times I get the expected result: multiple rows with new ids, same data.
这篇关于jdbc插入具有自动增量字段的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
