php链接oracle乱码,尝试把一个php的项目转成java,

发布时间:2023年12月17日

?
最近有个新需求,OA上的考勤信息确认,开始的时候搞了个php的版本,莫名其妙的数据库会乱码(oracle)(等有时间再写一篇php链接oracle数据库)折腾了将近一个周,乱码莫名其妙的出现,代码啥也不动,多请求两遍又正常了。准备搞个java来替换php,在gpt的协助下,搞成了。上代码

连接数据库:

private static final String DB_URL = "jdbc:oracle:thin:@**.**.**.**:1521:***";
    private static final String DB_USER = "****";
    private static final String DB_PASSWORD = "****";

发起网络请求,,获取数据:

//使用OkHttp发起网络请求
OkHttpClient client = new OkHttpClient().newBuilder()
                    .connectTimeout(300, TimeUnit.SECONDS)
                    .readTimeout(300, TimeUnit.SECONDS)
                    .build();
            MediaType mediaType = MediaType.parse("application/x-www-form-urlencoded");
            RequestBody body = RequestBody.create(mediaType, "***=**&****=**&*****=**&*****="+******);
            Request request = new Request.Builder()
                    .url("http://******/***/**/*************")
                    .method("POST", body)
                    .addHeader("Content-Type", "application/x-www-form-urlencoded")
                    .addHeader("Cookie", "****************************")
                    .build();
            Response response = client.newCall(request).execute();

处理数据考勤数据:

  // json化数据
                    ObjectMapper objectMapper = new ObjectMapper();
                    Map<String, Object> data = objectMapper.readValue(responseBody, Map.class);
            
                    JsonArray resultArray = new JsonArray();
                    //定义一个json  取data中data的值
                    for (Map<String, Object> employee : (List<Map<String, Object>>) data.get("data")) {
        // 计算日期
                    Calendar calendar = Calendar.getInstance();
                    calendar.add(Calendar.MONTH, -1);
                    calendar.set(Calendar.DAY_OF_MONTH, 1);
                    Date firstDayOfPreviousMonth = calendar.getTime();
                    calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
                    Date lastDayOfPreviousMonth = calendar.getTime();
                        JsonObject employeeData = new JsonObject();
                        // 给employeeData绑定值
                        employeeData.addProperty("*****", (String) employee.get("****"));
                        employeeData.addProperty("****", (String) employee.get("*****"));
                        employeeData.addProperty("*****", (String) employee.get("****"));
                        employeeData.addProperty("****", (String) employee.get("*****"));
                        ****************
                        JsonArray attendanceDetailsArray = new JsonArray();
                        calendar.setTime(firstDayOfPreviousMonth);
                        //处理考勤信息json 获取需要的数据 1-31  格式{"Date":16,"Text":"√"}
                        while (firstDayOfPreviousMonth.compareTo(lastDayOfPreviousMonth) <= 0) {
                            // 考勤信息原始的date数据格式在这里
                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                            String fullDateKey = dateFormat.format(firstDayOfPreviousMonth);
                            int dayKey = calendar.get(Calendar.DAY_OF_MONTH);
                            JsonObject attendanceDetails = new JsonObject();
                            //是否包含指定的键值
                            if (employee.containsKey(fullDateKey)) {
                                String attendanceText = (String) ((Map<String, Object>) employee.get(fullDateKey)).get("text");
                                attendanceDetails.addProperty("Date", dayKey);
                                attendanceDetails.addProperty("Text", attendanceText);
                            } else {
                                attendanceDetails.addProperty("Date", dayKey);
                                attendanceDetails.addProperty("Text", "");
                            }

                            // 赋值给attendanceDetailsArray
                            attendanceDetailsArray.add(attendanceDetails);
                            // 清除天数
                            calendar.add(Calendar.DAY_OF_MONTH, 1);
                            firstDayOfPreviousMonth = calendar.getTime();
                        }//处理完成的考勤数据 [{Date=1.0, Text=√}, {Date=2.0, Text=√}, {Date=3.0, Text=年假 年假}, {Date=4.0, Text=休息}, {Date=5.0, Text=√}, {Date=6.0, Text=事假 事假}, {Date=7.0, Text=√}, {Date=8.0, Text=√}, {Date=9.0, Text=√}, {Date=10.0, Text=事假 事假}, {Date=11.0, Text=√}, {Date=12.0, Text=√}, {Date=13.0, Text=√}, {Date=14.0, Text=√}, {Date=15.0, Text=√}, {Date=16.0, Text=√}, {Date=17.0, Text=年假 年假}, {Date=18.0, Text=休息}, {Date=19.0, Text=√}, {Date=20.0, Text=√}, {Date=21.0, Text=事假 事假}, {Date=22.0, Text=√}, {Date=23.0, Text=√}, {Date=24.0, Text=√}, {Date=25.0, Text=√}, {Date=26.0, Text=休息}, {Date=27.0, Text=年假 年假}, {Date=28.0, Text=√}, {Date=29.0, Text=√}, {Date=30.0, Text=√}]
                        employeeData.add("attendanceDetails", attendanceDetailsArray);
                        resultArray.add(employeeData);
                    }

                    //打印jsonString看一下数据是否完整
                    String jsonString = new Gson().toJson(resultArray);
                    System.out.println("new"+jsonString);

创建数据库链接,更新数据:

   try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {


                        // 根据id查询一下主表的id
                        String selectSql = "SELECT id FROM (SELECT id FROM " + formName + " WHERE ********= ? ORDER BY id DESC) WHERE ROWNUM = 1";
                        try (PreparedStatement selectStatement = connection.prepareStatement(selectSql)) {
                            selectStatement.setString(1, requestId);
                            try (ResultSet resultSet = selectStatement.executeQuery()) {
                                if (resultSet.next()) {
                                    //获取的json  考勤信息
                                    System.out.println("123789"+resultSet);
                                    //查询的主表的id更新附表时要用哪个
                                    int mainId = resultSet.getInt("id");
                                    System.out.println("mainId");
                                    System.out.println(mainId);
                                    //拼接sql
                                    String updateSql = "UPDATE " + formName + "_dt1 " +
                                            "SET ***= ?, bymqts = ?, *** = ?, kqs = ?, ***= ?, ***= ?, ****= ?, ****= ?, " +
                                            "****= ?, ****= ?, ****= ?, ****= ?, ****= ?, ****= ?, ****= ?, *****= ?, ****= ?,                         ****= ?, *****= ?"; 
                                    for (int i = 1; i <= 31; i++) {
                             String columnName = getColumnName(i);
                             updateSql += ", " + columnName + " = CASE WHEN ? = " + i + " THEN ? ELSE " + columnName + " END";
                                    }
                                    updateSql += " WHERE ****= ? AND ****= ?";
                                 

                                    try (PreparedStatement updateStatement = connection.prepareStatement(updateSql)) {
                                        System.out.println("  System.out.println(resultArray);"+resultArray);
                                        List<Map<String, Object>> resultJson = new Gson().fromJson(
                                                resultArray, new TypeToken<List<Map<String, Object>>>() {}.getType()
                                        );

                                        for (Map<String, Object> employee : resultJson) {
                                        // Set parameters for the prepared statement
                                         updateStatement.setString(1, (String) employee.get("****"));
                                         updateStatement.setString(2, (String) employee.get("****"));
                                         updateStatement.setString(3, (String) employee.get("****"));
                                         updateStatement.setString(4, (String) employee.get("****"));
                                         updateStatement.setString(5, (String) employee.get("****"));
                                         updateStatement.setString(6, (String) employee.get("****"));
                                            

                                            List<Map<String, Object>> attendanceDetails = (List<Map<String, Object>>) employee.get("attendanceDetails");
                                          //日期类型 必须要用linkedHashMap 不然1会到15后面导致15号之前的更新不成功
                                            Map<Double, String> attendanceMap = new LinkedHashMap<>();
                                            for (Map<String, Object> record : attendanceDetails) {
                                                Double date = (Double) record.get("Date");
                                                String text = (String) record.get("Text");
                                                attendanceMap.put(date, text);

                                            }
                                            System.out.println("    System.out.println(attendanceMap1);");
                                            System.out.println(attendanceMap);
                                            // 补全缺失的日期  都到31号
                                            for (double i = 1; i <= 31; i++) {
                                                if (!attendanceMap.containsKey(i)) {
                                                    attendanceMap.put(i, "");
                                                }
                                            }
                                            System.out.println("    System.out.println(attendanceMap2);");
                                            System.out.println(attendanceMap);
                                            int parameterIndex = 20;
                                            // 打印结果
                                            for (Map.Entry<Double, String> entry : attendanceMap.entrySet()) {
                                               Object dateValue = entry.getKey();
                                                Object textValue = entry.getValue();
                                                                                                String dateString;
                                                if (dateValue instanceof Double) {
                                                    dateString = String.valueOf(((Double) dateValue).intValue());
                                                } else {
                                                    dateString = (String) dateValue;
                                                }

                                                updateStatement.setString(parameterIndex++, dateString);
                                                updateStatement.setString(parameterIndex++, (String) textValue);

                                                System.out.println("Date: " + entry.getKey() + ", Text: " + entry.getValue());
                                            }

                                            updateStatement.setString(parameterIndex++, String.valueOf(*****));
                                            updateStatement.setString(parameterIndex++, (String) employee.get("*****"));
                                            System.out.println("Executing SQL statement: " + updateStatement.toString());
                                            String generatedSql = buildGeneratedSql(updateSql, resultJson.get(0));          }

                                        return "{\"status\": \"0\", \"message\": \"SUCCESS\", \"data\": \"\"}";

                                    }
                                }
                            }
                        }

                    } catch (SQLException e) {
                        e.printStackTrace(); // Handle the exception based on your application's requirements
                    }

完工,部署的时候又遇到了jdk和数据库驱动不匹配的问题,升级了jdk,按照下面的链接部署服务,测试正常。

https://blog.csdn.net/SmallCat0912/article/details/127426885

?

文章来源:https://blog.csdn.net/u010481239/article/details/134926084
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。