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的方式（图片来自-这里）
我不确定如何使其与UserID一起使用。我无法在其中输入任何内容，因为我的表单中没有这样的字段。
这是我从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：
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：
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();
}
}
我创建了一个存储库接口：
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测试可以完美地工作。
如果我多次插入相同的联系人，则会得到预期的结果：具有新ID和相同数据的多行。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
编辑于
相关文章
TOP 榜单
- 1
UITableView的项目向下滚动后更改颜色，然后快速备份
- 2
Linux的官方Adobe Flash存储库是否已过时？
- 3
用日期数据透视表和日期顺序查询
- 4
应用发明者仅从列表中选择一个随机项一次
- 5
Mac OS X更新后的GRUB 2问题
- 6
验证REST API参数
- 7
Java Eclipse中的错误13，如何解决？
- 8
带有错误“ where”条件的查询如何返回结果？
- 9
ggplot：对齐多个分面图-所有大小不同的分面
- 10
尝试反复更改屏幕上按钮的位置 - kotlin android studio
- 11
如何从视图一次更新多行（ASP.NET - Core）
- 12
计算数据帧中每行的NA
- 13
蓝屏死机没有修复解决方案
- 14
在 Python 2.7 中。如何从文件中读取特定文本并分配给变量
- 15
离子动态工具栏背景色
- 16
VB.net将2条特定行导出到DataGridView
- 17
通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException
- 18
在Windows 7中无法删除文件（2）
- 19
python中的boto3文件上传
- 20
当我尝试下载 StanfordNLP en 模型时，出现错误
- 21
Node.js中未捕获的异常错误，发生调用
我来说两句