3.27

	第十六天
所花时间	2h
代码量	100行
博客量	1篇
学到的知识	和团队成员完成了android studio 页面的制作和mysql 的配置

 

 

 

 

 

 

 

 

 

public class JDBCUtils {
    private static final String TAG = "mysql-party-JDBCUtils";

    private static String driver = "com.mysql.jdbc.Driver";// MySql驱
     private static String dbName = "party";// 数据库名称

    private static String user = "root";// 用户名

    private static String password = "123456";// 密码

    public static Connection getConn(){

        Connection connection = null;
        try{
            Class.forName(driver);// 动态加载类
            String ip = "10.99.113.121";// 写成本机地址，不能写成localhost，同时手机和电脑连接的网络必须是同一个

            // 尝试建立到给定数据库URL的连接
            connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbName,
                    user, password);

        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }
}

public class UserDao {

    private static final String TAG = "mysql-party-UserDao";

    /**
     * function: 登录
     */
    public static boolean login(String userAccount, String userPassword) {
        Connection connection = null;
        boolean success = false;

        try {
            connection = JDBCUtils.getConn();

            if (connection != null) {
                String query = "SELECT * FROM user WHERE userAccount = ? AND userPassword = ?";

                try (PreparedStatement pstmt = connection.prepareStatement(query)) {
                    pstmt.setString(1, userAccount);
                    pstmt.setString(2, userPassword);

                    try (ResultSet rs = pstmt.executeQuery()) {
                        if (rs.next()) {
                            Log.d("MysqlHelp_denglu", "用户名和密码匹配成功！");
                            success = true;
                        } else {
                            Log.d("MysqlHelp_denglu", "用户名和密码不匹配。");
                            success = false;
                        }
                    }
                }
            } else {
                Log.d("MysqlHelp_denglu", "无法获取数据库连接。");
            }
        } catch (Exception ex) {
            Log.e("MysqlHelp_denglu", "验证登录时发生异常", ex);
            success = false;
        }

        return success;
    }


    /**
     * function: 注册
     */
    public static boolean register(User user) {

        // 根据数据库名称，建立连接
        Connection connection = JDBCUtils.getConn();
        try {
            String sql = "insert into user(userAccount,userName,userPhone,userClass,userPassword) values (?,?,?,?,?)";
            if (connection != null) {// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null) {
                    //将数据插入数据库
                    ps.setString(1, user.getUserAccount());
                    ps.setString(2, user.getUserName());
                    ps.setString(3, user.getUserPhone());
                    ps.setString(4, user.getUserClass());
                    ps.setString(5, Integer.toString(123456));

                    // 执行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;
        }

    }


    /**
     * function: 打卡
     */


    // 在方法中获取当前系统日期并插入数据库
    public static Boolean daka(String time_s, String time_e, String text, String account) {
        Connection connection = JDBCUtils.getConn();
        try {
            // 检查数据库是否已经存在相同日期的记录
            String checkSql = "SELECT COUNT(*) FROM infor WHERE date = ?";
            PreparedStatement checkPs = connection.prepareStatement(checkSql);
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String currentDate = dateFormat.format(new Date());
            checkPs.setString(1, currentDate);
            ResultSet resultSet = checkPs.executeQuery();

            if (resultSet.next() && resultSet.getInt(1) > 0) {


            }

            // 插入新打卡记录
            String insertSql = "INSERT INTO infor(time_s, time_e, text, account, date) VALUES (?, ?, ?, ?, ?)";
            PreparedStatement ps = connection.prepareStatement(insertSql);
            ps.setString(1, time_s);
            ps.setString(2, time_e);
            ps.setString(3, text);
            ps.setString(4, account);
            ps.setString(5, currentDate);

            int rs = ps.executeUpdate();

            return rs > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            // 关闭资源
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * function: 打卡记录
     */

    public static String records(String account) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuilder resultBuilder = new StringBuilder();

        try {
            connection = JDBCUtils.getConn();
            String sql = "SELECT * FROM infor WHERE Account = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, account);

            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                String date = resultSet.getString("date");
                String resultAccount = resultSet.getString("Account");
                String times = resultSet.getString("time_s");
                String timee = resultSet.getString("time_e");
                String text = resultSet.getString("text");

                // 将查询结果拼接为字符串
                resultBuilder.append("日期：").append(date).append("\n").append("学号: ").append(resultAccount).append("\n")
                        .append("开始时间: ").append(times).append("\n")
                        .append("结束时间: ").append(timee).append("\n")
                        .append("学习内容: ").append(text).append("\n\n");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return resultBuilder.toString();
    }



    /**
     * function: 设置学习目标
     */

    public static boolean tar(User user, String account) {


        // 根据数据库名称，建立连接
        Connection connection = JDBCUtils.getConn();
        try {
            String sql = "insert into goal(date1, account, target) values (?, ?, ?)";
            if (connection != null) { // connection 不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);


                if (ps != null) {

                    // 获取当前系统日期并格式化为字符串
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String currentDate = dateFormat.format(new Date());

                    // 插入当前系统日期作为第五个数据
                    ps.setString(1, currentDate);


                    ps.setString(2, account);
                    ps.setString(3, user.getTarget());

                    // 执行sql查询语句并返回结果集
                    int rs = ps.executeUpdate();
                    if (rs > 0)
                        return true;
                    else
                        return false;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }


    /**
     * function: 完成度分析
     */
    public static boolean ana(User user, String account) {

        Connection connection = JDBCUtils.getConn();
        // 获取当前系统日期
        Calendar calendar = Calendar.getInstance();

        // 往前推四天
        calendar.add(Calendar.DAY_OF_MONTH, -4);

        // 格式化日期为 "yyyy-MM-dd" 格式的字符串
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String formattedDate = dateFormat.format(calendar.getTime());

        try {

            // 构建更新语句，更新符合账户和前四天日期的 analysis 数据
            String updateSql = "UPDATE goal SET analysis = ?, buzu = ? WHERE date1 = ? AND account = ?";
            PreparedStatement updatePs = connection.prepareStatement(updateSql);

            updatePs.setString(1, user.getAnalysis());
            updatePs.setString(2, user.getBuzu());
            updatePs.setString(3, formattedDate);
            updatePs.setString(4, account);

            int result = updatePs.executeUpdate(); // 执行更新操作


            return result > 0; // 返回更新是否成功的结果
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 更新过程中出现异常，返回更新失败
        }
    }


/**
 * function: 统计打卡次数
 */