I'm doing a project in Spring Boot and there have table named master_file and it has below columns.
| file_name | data_set_tag | file_type | deleted_status | year |
|---|
Values for deleted_status = DELETED, NOT_DELETED
Values for file_type = FINANCE, HR, IS
There are 2 points I need to achieve.
- according to the particular file type, deleted_status = NOT_DELETED and for a particular year, need to set the value for data_set_tag with increment like DATA-SET 1, DATA-SET 2 in file insert.
Expected result :
| file_name | data_set_tag | file_type | deleted_status | year |
|---|---|---|---|---|
| finance | DATA-SET 1 | FINANCE | NOT_DELETED | 2020 |
| finance quart | DATA-SET 2 | FINANCE | NOT_DELETED | 2020 |
| hr | DATA-SET 1 | HR | NOT_DELETED | 2020 |
| hr monthly | DATA-SET 2 | HR | NOT_DELETED | 2020 |
| finance quart | DATA-SET 3 | FINANCE | NOT_DELETED | 2020 |
- When file is deleted and then insert a particular file type, data_set_tag value need to increment according to the deleted_Status = NOT_DELETED
Expected result :
| file_name | data_set_tag | file_type | deleted_status | year |
|---|---|---|---|---|
| finance | DATA-SET 1 | FINANCE | NOT_DELETED | 2020 |
| finance quart | DATA-SET 2 | FINANCE | NOT_DELETED | 2020 |
| hr | DATA-SET 1 | HR | NOT_DELETED | 2020 |
| hr monthly | DATA-SET 2 | HR | DELETED | 2020 |
| finance quart | DATA-SET 3 | FINANCE | DELETED | 2020 |
| finance report 1 | DATA-SET 3 | FINANCE | NOT_DELETED | 2020 |
| hr monthly | DATA-SET 2 | HR | NOT_DELETED | 2020 |
I tried add a new column to table and using get a count of each file_type which are NOT_DELETED in particular year and set that in data_set_tag as DATA-SET count to the when insert file.
alter table master_file
add data_set_tag varchar(20) not null;
FileEntity fileEntity = new FileEntity();
Integer fileCount = fileRepository.countByYearAndDeletedStatusAndAppealType(year,'NOT_DELETED', appealType );
fileEntity.setExamYear( examYear );
fileEntity.setFileName(originalFileName);
fileEntity.setDataSetTag( "DATA-SET " + fileCount+1 );
fileEntity.setAppealType( appealType );
fileRepository.save(fileEntity);
Is there a way to do this in Database column definition level at alter table add column? Or is above way the best way to do this?
I found questions related to this in how-do-i-add-string-with-auto-increment-value-in-sql , auto-incrementing-a-number-that-is-part-of-a-string-value-in-a-sql, need-to-auto-increment-string-in-sql here but both do this in DB creation. I want this in Column creation since this table is already created.
Thanks in advance.
