CODEWITHSUNDEEP
javahtmlmysql
Programmer
Programmer

Reputation: 1294

How to auto increment employeeID based on values already entered in database?

I have a html page and a servlet which is responsible for adding new employee information to a database. Currently, as you can see, the 4 digit employee ID is entered manually by this admin. Is there any way I can auto-generate this, based on values which are already in the database? E.g. if there is an employee with id 0001, then the 4 digit employee ID will automatically be filled out as 0002 or something along those lines? Thanks

Part of code from NewEmployee.html 

<form action ="NewEmployeeAccount" method = "post"> 

    <table border ="1"> 
        <tr>
            <td>4 digit Employee ID:</td>
            <td><input type = "text" name = "employee_id"  pattern="[0-9]{4}" title="4 digit number: e.g. 1234" maxlength="4" required></td>
        </tr>

        <tr>
            <td>First Name</td>
            <td><input type = "text" name = "firstName"  required></td>
        </tr>

        <tr>
            <td>Last Name</td>
            <td><input type = "text" name = "lastName"  required></td>
        </tr>
    </table>
    <br/><br/>
    <input type = "submit" value = "Create Employee Account"/>
</form>

Part of code from NewEmployeeAccount.java

try {

    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", "");
    PreparedStatement ps = con.prepareStatement("INSERT INTO payroll_system.employee_info(employeeID, FirstName, LastName) values(?,?,?)");
    ps.setString(1, employee_id);
    ps.setString(2, firstName);
    ps.setString(3, lastName);

Upvotes: 0

Views: 3502

Answers (3)

ujulu
ujulu

Reputation: 3309

  • Create the table as follows. The auto_increment=1000 will tell MySQL to start inrementing the employeeID column starting from 1000 (four digit number).

    CREATE TABLE create table employee_info (
    employeeID int not null auto_increment,
    FirstName char(30) not null,
    LastName char(30) not null) auto_increment = 1000;

  • After creating the table insert an initializer row into the table so that the counting is initialized:

    insert into employee_info (FirstName, LastName) values ('initializer', 'initilizer);

  • From now on, before the page is displayed you query max value of the employeeID, add 1 to it and display it on the page in an readonly field:

    select max(employeeID) from employee_info

  • Insert the new entry to the table when the page is submitted. This time the value you incremented manually will be generated and saved:

    insert into employee_info (FirstName, LastName) values ('newFirstName', 'newLastName');

This is just a work around and not an ideal solution. Especially, the first entry to the table should be documented well so that other users or you yourself a couple of months later know why you are using it.

This solution is based on the assumption that you can use 1000 as your starting ID. If you want to use numbers less than 1000 I don't know any automatic way of generating the key except you generate it in the code.

Upvotes: 1

Warship
Warship

Reputation: 36

Try this:

ALTER TABLE payroll_system MODIFY COLUMN employee_id INT PRIMARY KEY AUTO_INCREMENT

Then you don't have to enter employee_id value by yourself.

Upvotes: 0

miquel09
miquel09

Reputation: 174

In SQL you can set a column to AUTO_INCREMENT. If you have an integer value of 1 and a new record is added the value for this record in the specified column will be 2.

A table with an auto increment column can be created like this:

CREATE table `user`(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32),
    pass VARCHAR(256)
);

Upvotes: 0

Related Questions