public class PlanningDao { private static final String TAG = "mysql-db_timing-PlanningDao"; public PlanningDao() { // 在构造函数中初始化必要的操作,如果有的话 } // 方法:插入目标数据 public static boolean insertGoal(Planning planning) { Connection connection = JDBCUtils.getConn(); if (connection != null) { try { String sql = "INSERT INTO planning (studentId, weekNum, goal) VALUES (?, ?, ?)"; PreparedStatement ps = connection.prepareStatement(sql); String studentId = getCurrentLoggedInUserId(); ps.setString(1, studentId); ps.setInt(2, planning.getWeekNum()); ps.setString(3, planning.getGoal()); int rowsAffected = ps.executeUpdate(); ps.close(); connection.close(); return rowsAffected > 0; } catch (SQLException e) { Log.e(TAG, "目标录入失败" + e.getMessage()); e.printStackTrace(); } } return false; } //录入目标分析 public boolean insertAnalysis(Planning planning){ Connection connection = JDBCUtils.getConn(); if(connection != null){ try{ String sql = "UPDATE planning SET analysis = ?, complete = ? WHERE studentId = ? AND weekNum = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, planning.getAnalysis()); // 设置分析字段 ps.setInt(2, planning.getComplete()); // 设置完成度字段 ps.setString(3, getCurrentLoggedInUserId()); // 设置学生ID ps.setInt(4, planning.getWeekNum()); // 设置周数 int rowsAffected = ps.executeUpdate(); ps.close(); connection.close(); return rowsAffected > 0; }catch(SQLException e){ Log.e(TAG, "目标分析录入失败" + e.getMessage()); e.printStackTrace(); } } return false; } // 方法:根据学生ID和周数查找计划 public static Planning findPlanning(String studentId, int weekNum) { Connection connection = JDBCUtils.getConn(); if (connection != null) { try { String sql = "SELECT * FROM planning WHERE studentId = ? AND weekNum = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, studentId); ps.setInt(2, weekNum); ResultSet resultSet = ps.executeQuery(); if (resultSet.next()) { Planning planning = new Planning(); planning.setStudentId(resultSet.getString("studentId")); planning.setWeekNum(resultSet.getInt("weekNum")); planning.setGoal(resultSet.getString("goal")); planning.setAnalysis(resultSet.getString("analysis")); planning.setComplete(resultSet.getInt("complete")); resultSet.close(); ps.close(); connection.close(); return planning; } } catch (SQLException e) { Log.e(TAG, "Error finding planning: " + e.getMessage()); e.printStackTrace(); } } return null; } public static double calculateCompletionPercentage(String studentId) { Connection connection = JDBCUtils.getConn(); if (connection != null) { try { // 查询指定 studentId 的所有记录的 complete 字段总和 String sql = "SELECT SUM(complete) AS totalComplete FROM planning WHERE studentId = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, studentId); ResultSet resultSet = ps.executeQuery(); if (resultSet.next()) { int totalComplete = resultSet.getInt("totalComplete"); // 查询 student 表中指定 studentId 对应的 setGoal 值 StudentDao studentDao = new StudentDao(); int setGoal = studentDao.getSetGoal(studentId); // 如果 setGoal 为 0,避免除以0错误,返回0 if (setGoal == 0) { return 0; } // 计算完成度百分比 return ((double) totalComplete / setGoal); } } catch (SQLException e) { Log.e(TAG, "Error calculating completion percentage: " + e.getMessage()); e.printStackTrace(); } finally { try { connection.close(); } catch (SQLException e) { Log.e(TAG, "Error closing connection: " + e.getMessage()); e.printStackTrace(); } } } return -1; // 返回 -1 表示出错 } }
标签:ps,studentId,return,数据库,resultSet,插入,connection,planning,计划 From: https://www.cnblogs.com/aixin52129211/p/18107334