1 android runtime_error ai_generated true

android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: SELECT * FROM users WHERE id = ? AND name = ?

ID: android/room-query-parameter-mismatch

Also available as: JSON · Markdown · 中文
95%Fix Rate
90%Confidence
1Evidence
2023-02-20First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Room 2.4.0 active — — —
Room 2.5.0 active — — —
Room 2.6.0 active — — —

Root Cause

The number of parameters in a Room @Query does not match the number of method arguments, causing a SQL syntax error.

generic

中文

Room @Query 中的参数数量与方法参数数量不匹配，导致 SQL 语法错误。

Official Documentation

https://developer.android.com/training/data-storage/room/accessing-data#query

Workarounds

  1. 95% success Ensure the number of '?' placeholders in the SQL query matches the number of method parameters. Example: If method is 'fun getUser(id: Int, name: String): User?', the query should be 'SELECT * FROM users WHERE id = ? AND name = ?'. 
    Ensure the number of '?' placeholders in the SQL query matches the number of method parameters. Example: If method is 'fun getUser(id: Int, name: String): User?', the query should be 'SELECT * FROM users WHERE id = ? AND name = ?'.
  2. 90% success Use named bind parameters with ':paramName' syntax for clarity and to avoid count mismatches. Example: 'SELECT * FROM users WHERE id = :id AND name = :name'. 
    Use named bind parameters with ':paramName' syntax for clarity and to avoid count mismatches. Example: 'SELECT * FROM users WHERE id = :id AND name = :name'.
  3. 85% success Run Room's annotation processor (kapt or ksp) to generate validation errors at compile time that catch parameter mismatches. 
    Run Room's annotation processor (kapt or ksp) to generate validation errors at compile time that catch parameter mismatches.

中文步骤

  1. 确保 SQL 查询中的 '?' 占位符数量与方法参数数量匹配。例如：如果方法是 'fun getUser(id: Int, name: String): User?'，查询应为 'SELECT * FROM users WHERE id = ? AND name = ?'。
  2. 使用命名绑定参数语法 ':paramName' 以提高清晰度并避免计数不匹配。例如：'SELECT * FROM users WHERE id = :id AND name = :name'。
  3. 运行 Room 的注解处理器（kapt 或 ksp）以在编译时生成验证错误，捕获参数不匹配问题。

Dead Ends

Common approaches that don't work:

  1. 80% fail

    The SQL query still has fewer placeholders than arguments, causing a mismatch error.

  2. 90% fail

    This introduces SQL injection risk and does not fix the parameter count issue.