表结构设计类似于群投票调查问卷考试卷用户端后台端表结构统计某个人最后的投票问卷投票操作单选和多选

// 导入必要的包

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

// 数据库连接相关配置
String url = "jdbc:mysql://localhost:3306/database_name";
String user = "username";
String password = "password";

// 查询某个用户最近一次答卷的单选和多选操作
int userId = 1; // 假设用户ID为1

// 连接数据库
try (Connection connection = DriverManager.getConnection(url, user, password)) {
    // 查询最近一次答卷的记录
    String userAnswerQuery = "SELECT * FROM UserAnswer WHERE user_id = ? ORDER BY create_time DESC LIMIT 1";
    try (PreparedStatement statement = connection.prepareStatement(userAnswerQuery)) {
        statement.setInt(1, userId);
        try (ResultSet resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                int surveyId = resultSet.getInt("survey_id");

                // 查询问卷的信息
                String surveyQuery = "SELECT * FROM Survey WHERE survey_id = ?";
                try (PreparedStatement surveyStatement = connection.prepareStatement(surveyQuery)) {
                    surveyStatement.setInt(1, surveyId);
                    try (ResultSet surveyResultSet = surveyStatement.executeQuery()) {
                        if (surveyResultSet.next()) {
                            // 查询问卷的问题
                            String questionQuery = "SELECT * FROM Question WHERE survey_id = ?";
                            try (PreparedStatement questionStatement = connection.prepareStatement(questionQuery)) {
                                questionStatement.setInt(1, surveyId);
                                try (ResultSet questionResultSet = questionStatement.executeQuery()) {
                                    while (questionResultSet.next()) {
                                        int questionId = questionResultSet.getInt("question_id");
                                        int questionType = questionResultSet.getInt("type");

                                        // 查询单选问题的答案
                                        if (questionType == 0) {
                                            String answerQuery = "SELECT * FROM UserAnswer WHERE user_id = ? AND question_id = ?";
                                            try (PreparedStatement answerStatement = connection.prepareStatement(answerQuery)) {
                                                answerStatement.setInt(1, userId);
                                                answerStatement.setInt(2, questionId);
                                                try (ResultSet answerResultSet = answerStatement.executeQuery()) {
                                                    if (answerResultSet.next()) {
                                                        int answerOptionId = answerResultSet.getInt("answer");
                                                        // 查询选项的内容
                                                        String optionQuery = "SELECT * FROM Option WHERE option_id = ?";
                                                        try (PreparedStatement optionStatement = connection.prepareStatement(optionQuery)) {
                                                            optionStatement.setInt(1, answerOptionId);
                                                            try (ResultSet optionResultSet = optionStatement.executeQuery()) {
                                                                if (optionResultSet.next()) {
                                                                    String optionContent = optionResultSet.getString("content");
                                                                    // 统计单选操作
                                                                    // 处理统计结果，例如放入Map等
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                        // 查询多选问题的答案
                                        else if (questionType == 1) {
                                            String answerQuery = "SELECT * FROM UserAnswer WHERE user_id = ? AND question_id = ?";
                                            try (PreparedStatement answerStatement = connection.prepareStatement(answerQuery)) {
                                                answerStatement.setInt(1, userId);
                                                answerStatement.setInt(2, questionId);
                                                try (ResultSet answerResultSet = answerStatement.executeQuery()) {
                                                    if (answerResultSet.next()) {
                                                        String answerOptions = answerResultSet.getString("answer");
                                                        String[] options = answerOptions.split(",");
                                                        for (String option : options) {
                                                            int answerOptionId = Integer.parseInt(option);
                                                            // 查询选项的内容
                                                            String optionQuery = "SELECT * FROM Option WHERE option_id = ?";
                                                            try (PreparedStatement optionStatement = connection.prepareStatement(optionQuery)) {
                                                                optionStatement.setInt(1, answerOptionId);
                                                                try (ResultSet optionResultSet = optionStatement.executeQuery()) {
                                                                    if (optionResultSet.next()) {
                                                                        String optionContent = optionResultSet.getString("content");
                                                                        // 统计多选操作
                                                                        // 处理统计结果，例如放入Map等
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}