?
最近有个新需求，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
?