Создайте rest API для получения огромных данных из 50 таблиц с сервера sql при весенней загрузке. ⇐ JAVA

[Anonymous]

There is 50 tables in ms sql server which named as Port1 to Port50 almost each tables having near about 55k data entries in it

Port table columns are as follows: 'SRNO', 'CDate', 'CompoNo', 'ParameterName', 'PortValue', 'OP_ID', 'Event', 'CorrectiveActionID', 'Shift', 'Serialno', 'Customername', 'Heatno', 'Remark', 'Machine', 'Batch', 'MarkingData', 'Shim1', 'Shim2', 'JobNo', 'VendorCode'

as a result we want data in format below: 'Job No' (SerialNo), 'B/E Dia Top X', 'B/E Dia Top Y', 'B/E Dia Bot X', 'B/E Dia Bot Y', 'B/E Dia Top ovality', 'B/E Dia Bot ovality', 'B/E Thickness', 'B/E Notch Presence', 'B/E Top Face Perpendicularity', 'S/E Flatness', 'S/E Dia Top X', 'S/E Dia Top Y', 'S/E Dia Bot X', 'S/E Dia Bot Y', 'S/E Dia Top ovality', 'S/E Dia Bot ovality', 'S/E Thickness', 'Oil Hole Presence', 'C.D.', 'Bend', 'Twist', 'B/E Weight', 'S/E Weight', 'Total Weight', 'Fixture_Temp', 'Comp_Temp', 'Status' (Ok/Not Ok)

serialNo is unique key (for us to fetch the data) through all the table (somewehre in the tables serialNo is not mentioned properly) and others are the parameters

requirenment is to fetch the data from the all the tables with respect to the serialNo and show in the above format as result and there is one table named as PortSetting, in PortSetting table limits are given for all the parameters

we have to compare the all the parameter portValue from the PortSetting table data and if the portValue is satisfied the condition UTL and LTL (from PortSetting) then set Status value as Ok otherwise Not Ok

I have tried below approach but because of huge data request will fail and when i applied pagination then it will not fetch correct data because pagination limits the fetching the data from sql server
`@Data @Getter @Setter public class DynamicPortDataDTO { private String serialNo; private List parameters; } @Service public class DynamicPortDataService { @Autowired private JdbcTemplate jdbcTemplate; private static final Logger logger = LoggerFactory.getLogger(DynamicPortDataService.class); public List getAll() { List resultList = new ArrayList(); Map resultMap = new HashMap(); List tableNames = jdbcTemplate.queryForList( "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema='dbo'", String.class ); for (String tableName : tableNames) { if (tableName.startsWith("Port") && !tableName.equals("PortSetting")) { System.out.println("table Name 0000000000000000000000000 " + tableName); // Modify the query to include pagination String query = String.format("SELECT * FROM %s WHERE CDate > '2024-01-01' ORDER BY CDate", tableName); logger.debug("Executing query: {}", query); System.out.println("below query logger &&&&&&&&&&&&&&"); List queryResults = jdbcTemplate.queryForList(query); for (Map queryResult : queryResults) { String serialNo = (String) queryResult.get("SerialNo"); logger.debug("Processing serialNo: {}", serialNo); DynamicPortDataDTO result = resultMap.get(serialNo); if (result == null) { logger.debug("Creating new result for serialNo: {}", serialNo); result = new DynamicPortDataDTO(); result.setSerialNo(serialNo); result.setParameters(new ArrayList()); resultMap.put(serialNo, result); } List tableDataList = result.getParameters(); String query1 = "SELECT * FROM " + tableName + " WHERE SerialNo = :serialNo"; NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); Map parameters = new HashMap(); parameters.put("serialNo", serialNo); namedParameterJdbcTemplate.query(query1, parameters, resultSet -> { ResultSetMetaData metaData = resultSet.getMetaData(); if (resultSet.next()) { do { Map tableData = new HashMap(); for (int i = 1; i

Источник: https://stackoverflow.com/questions/781 ... pring-boot