jdbc insert into database with auto increment fiel

问题:

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)

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.

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)

回答1:

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

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

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.

If I INSERT the same contact multiple times I get the expected result: multiple rows with new ids, same data.

回答2:

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'

回答3:

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.

You can then retrieve this value and insert into the "usermail" table. Something like this:

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!

回答4:

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.

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