This is my Entity class, which when I run project automatically mapping oracle database.

import lombok.Data;
import org.springframework.data.annotation.CreatedDate;

import javax.persistence.*;
import java.time.LocalDateTime;

@Data
@Entity
@Table(name = "test")
public class UserDetail {

    @SequenceGenerator(
            name = "sequence_name",
            allocationSize = 1
    )
    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "sequence_name"
    )
    @Column(name = "info_id", length = 11)
    private int id;

    @Column(name = "phone_number", length = 20)
    private String phoneNumber;

    @Lob
    @Column(name = "text_message")
    private String textMessage;

    @CreatedDate
    @Column(columnDefinition = "TIMESTAMP", nullable = false)
    private LocalDateTime createdDate;

}

and I made IDENTITY using the higher version of ORACLE, it still didn't work.

This is my code and here I am sending a file extension in swagger as follows:

C:\Users\anar.memmedov\Desktop\file2.xlsx


 public Response acceptExcellFileAndInsertToDatabase(String file) {
        try (FileInputStream excelFile = new FileInputStream(file)) {
            String phoneNumber = "";
            String textMessage = "";
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();
            while (iterator.hasNext()) {
                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();
                while (cellIterator.hasNext()) {
                    Cell currentCell = cellIterator.next();
                    if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
                        phoneNumber = NumberToTextConverter.toText(currentCell.getNumericCellValue());
                    } else if (currentCell.getCellTypeEnum() == CellType.STRING) {
                        textMessage = String.valueOf(currentCell.getStringCellValue());
                    }

                }
            }
            this.userDetailRepository.save(phoneNumber, textMessage);
            excelFile.close();
            Path sourcePath = Paths.get(file);
            Path targetPath = Paths.get("C:\\Users\\anar.memmedov\\Desktop\\ko\\"   sourcePath.getFileName());
            Files.move(sourcePath, targetPath);

            return new SuccessResponse(MessageCase.FILE_SUCCESSFULLY_WRITTEN_TO_DATABASE.getMessage(), 200);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ErrorResponse(MessageCase.FAILED_HAPPEND_WHEN_FILE_WRITTEN_TO_DATABASE, 404);
    }

This is my repository method and I take an excel file and send the values in it to the columns in the table you see, but I am getting the following error:

@Modifying
@Query(value = "INSERT INTO test (PHONE_NUMBER,TEXT_MESSAGE) VALUES (:phoneNumber,:textMessage)", nativeQuery = true)
@Transactional
void save(@Param("phoneNumber") String phoneNumber, @Param("textMessage") String textMessage);

oracle.jdbc.OracleDatabaseException: ORA-01400: cannot insert NULL into ("TEST3"."TEST"."ID")

I do not know what happened, but it worked when I using mysql database, I mean, I was taking the excel file, writing the values in it to the database, and then successfully extracting the excel file to the folder on my computer. But now I can't solve this error because I am using oracle.

CodePudding user response：

Error says:

cannot insert NULL into ("TEST3"."TEST"."ID")

You're inserting

INSERT INTO test (PHONE_NUMBER,TEXT_MESSAGE) --> no ID column here

If it worked in MySQL but it doesn't in Oracle, I presume that MySQL's ID column was auto-generated, while in Oracle it isn't the case.

So, what to do? Make sure ID gets its value, somehow.

• if you have it in Excel file, use it
• if not
  • if you're on Oracle database version lower than 12c, create a database trigger which will use a sequence to insert value into the ID column
  • if you're on 12c and above, you can create an identity column whose value is autogenerated
• if you don't care about ID (i.e. it can be NULL), remove that constraint. Might be NOT NULL, but it's probably a primary key. I don't think that this actually is an option here

CodePudding user response：

I solved the problem. I added **true** to nullable in createdDate variable in my entity class and it worked.