How do I create a query in Java for auto increment table?-CodePudding

Hello so i was trying to create an insert query in java to my sql server but the thing is it keep asking for value for field user id even tho i've already define AUTO_INCREMENT, i've tried to set the value to NULL too but it said "coloumn user id cannot be null"

uh from what i know in sql you dont have to define value for auto increment type right ?

query:

String query = "INSERT INTO 
user(userId,username,password,gender,country,role) 
VALUES(NULL,'" uu "', '" pp "', '" gg "','" cc "', '" rr "')";

errors :

java.sql.SQLIntegrityConstraintViolationException: Column 'userId' cannot be null
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1333)
    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2106)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1243)
    at main.Connect.updateData(Connect.java:43)
    at main.Regis.actionPerformed(Regis.java:189)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

table definition :

|Field   |Type         |Null|key|Default|Extra|
|--------|-------------|----|---|-------|-----|
|userId  |int          |NO  |___|NULL   |     |
|username|varchar(255) |NO  |___|NULL   |     |
|password|varchar(255) |NO  |___|NULL   |     |
|gender  |varchar(255) |NO  |___|NULL   |     |
|country |varchar(255) |NO  |___|NULL   |     |
|role    |varchar(255) |NO  |___|NULL   |     |

Note : from the .sql file that i get there is alter table code for table user

ALTER TABLE `user`
MODIFY `userId` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

CodePudding user response：

You can't make a column AUTO_INCREMENT unless it's has a key (that is, index) on it. Best if it's a PRIMARY KEY or UNIQUE KEY.

But I see in your example of describe table, the column is not a key:

|Field   |Type         |Null|key|Default|Extra|
|--------|-------------|----|---|-------|-----|
|userId  |int          |NO  |___|NULL   |     |

                             ^^^ ideally this should say "PRI"

So your ALTER TABLE to make the column AUTO_INCREMENT probably failed. You can confirm this:

SHOW CREATE TABLE `user`\G

Do you see the AUTO_INCREMENT option next to the userId column? I don't think you will.

You can try again to make the column a primary key and make it AUTO_INCREMENT:

ALTER TABLE `user`
 ADD PRIMARY KEY (`userId`),
 MODIFY `userId` INT AUTO_INCREMENT;

Don't bother with INT(255). The length argument for an integer is a common source of confusion. It has almost no purpose or effect, and it's deprecated in MySQL 8.0.

Don't bother making the column NOT NULL. That will happen automatically as you add the PRIMARY KEY constraint.

Don't bother setting the AUTO_INCREMENT=8. The next AI value will automatically be set to the highest value in that column, plus one. It can never be less than the greatest value in that column.

CodePudding user response：

Try this code, must work if you already defined userId is an auto_increament.

String query = "INSERT INTO user(username,password,gender,country,role) VALUES('" uu "', '" pp "', '" gg "','" cc "', '" rr "')";