How to create Temporary table programmatically in jmix

Support
#1

Hi,

How to create Temporary table programmatically in jmix or is there any way to compare data present in xls file with existing table.

for e.g. if i had account number present in my file which is upload i want to check that account number present in my file must be present in database also.

#2

If you know the structure of the xls file, use the DataManager :: Jmix Documentation to verify that the data from the file is already contained in the database.
I do not think that loading the entire raw xls file into a temporary table with subsequent data validation will give great advantages…

...
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//for example the xls file format requires loginname in a cell with coordinates of 0:0 for first sheet
XSSFSheet sheet = workbook.getSheetAt(0);
String loginName = sheet.getRow(0).getCell(0).toString();
User user;
try {
     User user = dataManager.load(User.class)
              .query("select e from myapp_User e  where e.username like :loginName")
              .parameter("loginName", loginName)
              .one();
   } catch (IllegalStateException e) {
            log.info("Liginname = {} is not found in DB.", loginName);
}
...
#3

Hi Andrey,

If i had a file which contains 40k data and if i try to compare each cell one by one with database it will slower the server performance.

#4

But if you load all the data data from the xls file into a temporary table, then you still have to verify the data at least for the fact that they already exist in the permanent tables.
Most likely, you will also have to check the correctness and format of the data in the cell before writing to the temporary table.

In addition, I am not 100% sure, but I think that the most resource-intensive operation here will be loading and reading xls file via POI.