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
– duffymo, Commented Mar 20, 2017 at 16:16

duffymo · Accepted Answer · 2017-03-21 14:59:32Z

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.

Fredy Fischer · Accepted Answer · 2017-03-21 14:20:41Z

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'

Nagesh Dalave · Accepted Answer · 2018-07-05 11:34:55Z

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

Riddle03 · Accepted Answer · 2017-03-20 16:25:41Z

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.
I tried this and its still not working. Thanks for trying though.

Collectives™ on Stack Overflow

jdbc insert into database with auto increment field

4 Answers 4

Comments

Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Linked

Related