PlanningDao
package com.example.demo3.dao; import static com.example.demo3.dao.StudentDao.getCurrentLoggedInUserId; import android.util.Log; import com.example.demo3.entity.Planning; import com.example.demo3.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.example.demo3.dao.StudentDao; 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 表示出错 } }
RecordDao
package com.example.demo3.dao; import static com.example.demo3.dao.StudentDao.getCurrentLoggedInUserId; import android.util.Log; import com.example.demo3.entity.Planning; import com.example.demo3.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.example.demo3.dao.StudentDao; 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 表示出错 } }
StudentDao
package com.example.demo3.dao; import android.util.Log; import com.example.demo3.entity.Student; import com.example.demo3.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.HashMap; public class StudentDao { private static final String TAG = "mysql-db_timing-StudentDao"; private static String currentLoggedInUserId; // 添加一个类变量来存储当前登录用户的ID public int login(String id,String password){ HashMap<String,Object> map =new HashMap<>(); //连接数据库 Connection connection = JDBCUtils.getConn(); int msg = 0; try{ //简单的sql查询 String sql = "select * from student where id = ?"; if (connection != null){// connection不为null表示与数据库建立了连接 PreparedStatement ps = connection.prepareStatement(sql); if (ps != null){ Log.e(TAG,"账号:" + id); //根据账号进行查询 ps.setString(1, id); // 执行sql查询语句并返回结果集 ResultSet rs = ps.executeQuery(); int count = rs.getMetaData().getColumnCount(); //将查到的内容储存在map里 while (rs.next()){ // 注意:下标是从1开始的 for (int i = 1;i <= count;i++){ String field = rs.getMetaData().getColumnName(i); map.put(field, rs.getString(field)); } } connection.close(); ps.close(); if (map.size()!=0){ StringBuilder s = new StringBuilder(); //寻找密码是否匹配 for (String key : map.keySet()){ if(key.equals("password")){ if(password.equals(map.get(key))){ msg = 1; //密码正确 currentLoggedInUserId = id; // 设置当前登录用户的ID } else msg = 2; //密码错误 break; } } }else { Log.e(TAG, "查询结果为空"); msg = 3; } }else { msg = 0; } }else { msg = 0; } }catch (Exception e){ e.printStackTrace(); Log.d(TAG, "异常login:" + e.getMessage()); msg = 0; } return msg; } /** * function: 注册 * */ public boolean register(Student student){ HashMap<String, Object> map = new HashMap<>(); // 根据数据库名称,建立连接 Connection connection = JDBCUtils.getConn(); try { String sql = "insert into student(id,name,phone,className,password,setGoal,setRecord) values (?,?,?,?,?,?,?)"; if (connection != null){// connection不为null表示与数据库建立了连接 PreparedStatement ps = connection.prepareStatement(sql); if (ps != null){ //将数据插入数据库 ps.setString(1,student.getId()); ps.setString(2,student.getName()); ps.setString(3,student.getPhone()); ps.setString(4,student.getClassName()); ps.setString(5, student.getPassword()); ps.setInt(6,student.getSetGoal()); ps.setInt(7,student.getSetRecord()); // 执行sql查询语句并返回结果集 int rs = ps.executeUpdate(); if(rs>0) return true; else return false; }else { return false; } }else { return false; } }catch (Exception e){ e.printStackTrace(); Log.e(TAG, "异常register:" + e.getMessage()); return false; } } public Student findStudent(String StudentId) { // 根据数据库名称,建立连接 Connection connection = JDBCUtils.getConn(); Student student = null; try { String sql = "select * from student where id = ?"; if (connection != null){// connection不为null表示与数据库建立了连接 PreparedStatement ps = connection.prepareStatement(sql); if (ps != null) { ps.setString(1, StudentId); ResultSet rs = ps.executeQuery(); while (rs.next()) { //注意:下标是从1开始 String id = rs.getString(1); String name = rs.getString(2); String phone = rs.getString(3); String className = rs.getString(4); String password = rs.getString(5); int setGoal = rs.getInt(6); int setRecord = rs.getInt(7); student = new Student(id, name, phone, className, password, setGoal, setRecord); } } } }catch (Exception e){ e.printStackTrace(); Log.d(TAG, "异常findUser:" + e.getMessage()); return null; } return student; } public static String getCurrentLoggedInUserId() { return currentLoggedInUserId; } /** * 增加 setGoal 字段的值 */ public static boolean incrementSetGoal() { Connection connection = JDBCUtils.getConn(); if (connection != null) { try { String sql = "UPDATE student SET setGoal = setGoal + 1 WHERE id = ?"; PreparedStatement ps = connection.prepareStatement(sql); String studentId = getCurrentLoggedInUserId(); // 获取当前登录用户的ID ps.setString(1, studentId); int rowsAffected = ps.executeUpdate(); ps.close(); connection.close(); return rowsAffected > 0; } catch (Exception e) { e.printStackTrace(); Log.e(TAG, "异常 incrementSetGoal:" + e.getMessage()); return false; } } return false; } /** * 增加 setRecord 字段的值 */ public static boolean incrementSetRecord() { Connection connection = JDBCUtils.getConn(); if (connection != null) { try { String sql = "UPDATE student SET setRecord = setRecord + 1 WHERE id = ?"; PreparedStatement ps = connection.prepareStatement(sql); String studentId = getCurrentLoggedInUserId(); // 获取当前登录用户的ID ps.setString(1, studentId); int rowsAffected = ps.executeUpdate(); ps.close(); connection.close(); return rowsAffected > 0; } catch (Exception e) { e.printStackTrace(); Log.e(TAG, "异常 incrementSetRecord:" + e.getMessage()); return false; } } return false; } public int getSetGoal(String studentId) { StudentDao studentDao = new StudentDao(); Student student = studentDao.findStudent(studentId); if (student != null) { return student.getSetGoal(); } else { // 如果未找到学生,则返回默认值或者抛出异常,这取决于你的需求 return 0; // 默认值为0 } } public int getSetRecord(String studentId){ StudentDao studentDao = new StudentDao(); Student student = studentDao.findStudent(studentId); if (student != null) { return student.getSetRecord(); } else { // 如果未找到学生,则返回默认值或者抛出异常,这取决于你的需求 return 0; // 默认值为0 } } }
标签:4.5,ps,studentId,return,connection,planning,sql From: https://www.cnblogs.com/yindantong/p/18168437