jdbc insert into database with auto increment field

Question

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)

column

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)

Shouldn't the name be UserId? Do you intend the non-id fields to be unique? — duffymo, Mar 20 '17 at 16:16
Do you have another unique constraint on the combination of first name, last name, town, country, and email? If yes, then you should not be able to insert them more than once. — duffymo, Mar 20 '17 at 16:21
Could you put a picture of what you would like the table to look like? I think that would help clarify — Ishnark, Mar 20 '17 at 16:23
What messages do you get back when you INSERT a second time? I hope you have a catch block that prints any stack trace. — duffymo, Mar 20 '17 at 16:30
The exception suggests that auto increment isn't happening. What version of MySQL are you running? What version of the JDBC driver? — duffymo, Mar 20 '17 at 16:45
@duffymo Driver version: mysql-connector-java-5.1.23 ( Revision: ${bzr.revision-id} ) — hDDen, Mar 20 '17 at 16:49
5.1.23 is pretty old. But the problem itself seems to be that you haven't created an auto increment field. You just have a field that has a default value of 1. — Mark Rotteveel, Mar 21 '17 at 09:41

score 8 · Accepted Answer · answered Mar 21 '17 at 14:59

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.

score 1 · Answer 2 · answered Mar 21 '17 at 14:20

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'

score -1 · Answer 3 · answered Jul 05 '18 at 11:34

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

score -2 · Answer 4 · answered Mar 20 '17 at 16:25

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!

No one should be constructing query strings that way. Use PreparedStatement. Your answer is not correct. — duffymo, Mar 20 '17 at 16:31
I tried this and its still not working. Thanks for trying though. — hDDen, Mar 20 '17 at 16:45

jdbc insert into database with auto increment field

4 Answers4

Linked