Java 使用HANA数据库的坑_java hana

解决方式：

private static final String SPLIT = "\"";
private static List<String> jointInsertListSql(List<String> columns, List<Map<String, String>> resultOtherMaps) {
        String insertDataStart = "insert into ";
        List<String> executeDMLList = Lists.newArrayList();
        Lists.partition(resultOtherMaps, 1000).forEach(resultOtherMapsList -> {
            StringBuilder insertListSql = new StringBuilder();
            insertListSql.append(insertDataStart);
            insertListSql.append(SPLIT).append("RESULT_SET").append(SPLIT).append(".").append(SPLIT).append("CUTOVER_MMR_CHK_LIST").append(SPLIT).append("( ");
            columns.forEach(column -> {
                insertListSql.append(SPLIT).append(column).append(SPLIT);
                if (columns.size() - 1 == columns.indexOf(column)) {
                    insertListSql.append(")\n ");
                } else {
                    insertListSql.append(",\n ");
                }
            });
            resultOtherMapsList.forEach(map -> {
                insertListSql.append(" select top 1 ");
                columns.forEach(column -> {
                    if (Objects.nonNull(map.get(column))) {
                        insertListSql.append(VALUE_SPLIT).append(map.get(column)).append(VALUE_SPLIT);
                    } else {
                        insertListSql.append("null");
                    }
                    if (columns.size() - 1 != columns.indexOf(column)) {
                        insertListSql.append(",");
                    }
                });
                if (resultOtherMapsList.size() - 1 == resultOtherMapsList.indexOf(map)) {
                    insertListSql.append(" from dummy; \n ");
                } else {
                    insertListSql.append(" from dummy union\n");
                }
            });
            log.info("insert data sql -> {} ", insertListSql);
            executeDMLList.add(insertListSql.toString());
        });
        return executeDMLList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

public static void main(String[] args) {
        String jdbcUrl = "jdbc:sap://xx.xxx.xx.xxx:30015";
        String username = "1234";
        String password = "1234";

        try {
            // Load Excel file
            InputStream inputStream =FtpFileServiceImpl.class.getClassLoader().getResourceAsStream("123.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // Prepare database connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO VALIDATE.TEST (ID,NAME) VALUES (?, ?)");

            // Process and insert data
            for (Row row : sheet) {
                if(row.getRowNum() == 0){
                    continue;
                }
                double value1 = row.getCell(0).getNumericCellValue();
                String value2 = row.getCell(1).getStringCellValue();

                preparedStatement.setDouble(1, value1);
                preparedStatement.setString(2, value2);
                preparedStatement.addBatch();
            }

            // Execute batch insert
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            // Clean up resources
            preparedStatement.close();
            connection.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

  select * from user limit #{pageSize} OFFSET #{pageNum};
  select count(1) from user;
1
2

假如现在有98条数据，每页10条数据 前9页正常分页都没有问题，只有第十页 ，最后的8条数据偏移错误

解决方法 先查询总数 
先判断是否是最后一页，如果是最后一页就先求最后一页
1
2

  	int pageNum = 10
   	int pageSize = 10;
   	int total = 98;
    if (Objects.nonNull(req.getPageNum()) && Objects.nonNull(req.getPageSize())) {
        if ((int) Math.ceil(total / (double) req.getPageSize()) == req.getPageNum()) {
           req.setPageNum(total - (req.getPageNum() - 1) * req.getPageSize());
        }else {
         req.setPageNum((req.getPageNum() - 1) * req.getPageSize());
        }
    }
1
2
3
4
5
6
7
8
9
10