Auto Increment ID field both in Java and mySQL DB?

Question

if a java object has a auto-increment ID field, should the Database table field should be auto-increment as well?

for example

public class Company {
    private static int countID = 0;
    private int companyID;
    private String name;
    private String email;
    private String password;
    private List<Coupon> couponsList;

    public Company(int companyID, String name, String email, String password, List<Coupon> couponsList) {
        this.companyID = companyID+=1;
        this.name = name;
        this.email = email;
        this.password = password;
        this.couponsList = couponsList;
    }

you can see that the companyID field is generated automatically.

i have created this table in my SQL server

CREATE TABLE `companies` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(200) DEFAULT NULL,
  `EMAIL` varchar(200) DEFAULT NULL,
  `PASSWORD` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)

how should I connect the Java field (commpanID) to the sql field (ID)?

Gordon Linoff · Accepted Answer · 2021-06-13 14:31:34Z

1

No. They are different things. Auto-incremented columns in MySQL are not guaranteed to be gapless. Gaps can occur for multiple reasons. The most common are:

Concurrent transactions.
Deletion.

It sounds like you have a unique identifier in Java which is either redundant or an item of data. If the latter, then add it as an additional column.

More likely, though, you might want to reconsider your design, so there is only one auto-incremented value for a given record. I would recommend using the one in the database, because that would apply regardless of how inserts are made into the database.

answered Jun 13, 2021 at 14:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Over a year ago

Thank you. So how should i approach the ID field in the C`TOR when creating an object and inserting it to the DB? how would i update the ID field of the object to the one in the DB?

Gordon Linoff Over a year ago

@Daniel . . . No, you just let the database calculate it. If you need it in the application, use LAST_INSERTED_ID().

basant biswakarma · Accepted Answer · 2021-06-13 14:33:54Z

0

It isn't compulsory to create and unique id field in the database . You can instead change the table like-->

CREATE TABLE companies (
  'COMPANYID' int NOT NULL,
  `NAME` varchar(200) DEFAULT NULL,
  `EMAIL` varchar(200) DEFAULT NULL,
  `PASSWORD` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)

since you are auto incrementing the same the same value twice , it will create some problems. your ID column will be like this-->

Id|
---
2 |
---
4 |
--
6 |
--
8 |

it will increment the values twice

answered Jun 13, 2021 at 14:33

basant biswakarma

1

Collectives™ on Stack Overflow

Auto Increment ID field both in Java and mySQL DB?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related