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 } } }