jdbc 使用自动增量字段插入数据库

Question

我的代码中有这个：

 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)

原文由 hDDen 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

我使用 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 的多行，相同的数据。

原文由 duffymo 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

当您以这种方式创建表时：

 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)
);

您应该能够以您尝试过的方式添加记录。可能它也有效，当您单击左侧的检查字段“INCREMENT BY 1”时

原文由 Fredy Fischer 发布，翻译遵循 CC BY-SA 3.0 许可协议