Android Studio中SQLite随机取数与无重复自动组卷APP开发问询

Hey there! Let's tackle your two Android SQLite-related tasks—they're super common for quiz/paper generation apps, so I'll share practical, actionable code snippets and logic for both:

First, to pull 4 random entries from your database, use SQLite's ORDER BY RANDOM() LIMIT 4 clause—it’s reliable for Android’s SQLite implementation. Once you have the entries, we’ll loop through them to insert "or" after every pair.

Here’s a Kotlin implementation using native SQLite:

// Inside your SQLiteOpenHelper subclass
fun getRandomFourEntriesWithOr(): List<String> {
    val rawEntries = mutableListOf<String>()
    val db = readableDatabase
    
    // Fetch 4 random entries
    val cursor = db.rawQuery("SELECT your_content_column FROM your_table ORDER BY RANDOM() LIMIT 4", null)
    
    with(cursor) {
        while (moveToNext()) {
            rawEntries.add(getString(getColumnIndexOrThrow("your_content_column")))
        }
    }
    cursor.close()
    db.close()
    
    // Insert "or" after every two items
    val formattedList = mutableListOf<String>()
    rawEntries.forEachIndexed { index, item ->
        formattedList.add(item)
        // Add "or" only after the 2nd item (since we have 4 total, no need after the 4th)
        if ((index + 1) % 2 == 0 && index != rawEntries.lastIndex) {
            formattedList.add("or")
        }
    }
    
    return formattedList
}

This will output a list like [Item1, Item2, "or", Item3, Item4], matching your requirement.

The key here is ensuring no duplicate questions in the generated paper. We’ll use random sorting with a limit to pull unique entries, then format them to your desired structure.

First, define a data class for your question model, then build the generation function:

// Data class to represent a single question in your database
data class Question(
    val id: Int,
    val questionText: String,
    val optionA: String,
    val optionB: String,
    val optionC: String,
    val optionD: String,
    val difficulty: String
)

// Inside your SQLiteOpenHelper subclass
fun generateUniquePaper(paperSize: Int): String {
    val questions = mutableListOf<Question>()
    val db = readableDatabase
    
    // Fetch random, unique questions (LIMIT ensures we only get the needed number)
    val cursor = db.rawQuery(
        "SELECT * FROM questions ORDER BY RANDOM() LIMIT ?",
        arrayOf(paperSize.toString())
    )
    
    with(cursor) {
        while (moveToNext()) {
            val question = Question(
                id = getInt(getColumnIndexOrThrow("id")),
                questionText = getString(getColumnIndexOrThrow("question_text")),
                optionA = getString(getColumnIndexOrThrow("option_a")),
                optionB = getString(getColumnIndexOrThrow("option_b")),
                optionC = getString(getColumnIndexOrThrow("option_c")),
                optionD = getString(getColumnIndexOrThrow("option_d")),
                difficulty = getString(getColumnIndexOrThrow("difficulty"))
            )
            questions.add(question)
        }
    }
    cursor.close()
    db.close()
    
    // Format the paper to your specified structure (example below)
    return buildString {
        append("# Generated Quiz Paper\n")
        append("Total Questions: ${questions.size}\n\n")
        
        questions.forEachIndexed { index, q ->
            append("${index + 1}. ${q.questionText}\n")
            append("  A. ${q.optionA}\n")
            append("  B. ${q.optionB}\n")
            append("  C. ${q.optionC}\n")
            append("  D. ${q.optionD}\n\n")
        }
    }
}

Notes for customization: #

• If you need to enforce question type ratios (e.g., 5 easy, 5 hard), split the query into separate calls for each type, then combine and shuffle the results.
• Adjust the buildString block to match your exact formatting needs (like HTML, PDF-ready text, or UI-friendly objects).

内容的提问来源于stack exchange，提问作者rambo