如何在Room中实现MySQL的SUM()等函数及解决DAY函数报错问题

First off, that no such function: DAY error pops up because Room relies on SQLite under the hood, and functions like DAY(), MONTH(), YEAR() are MySQL-specific—SQLite doesn’t recognize those exact function names. The good news is SQLite has equivalent date-handling tools, and SUM() works exactly like it does in MySQL.

Here’s how to swap MySQL functions for SQLite-compatible alternatives: #

• MySQL DAY(date) → Use SQLite strftime('%d', date) (returns two-digit days like 03, 17). If you want an integer without leading zeros, wrap it in CAST(strftime('%d', date) AS INTEGER)
• MySQL MONTH(date) → SQLite strftime('%m', date) (two-digit months like 02, 11), cast to integer if needed
• MySQL YEAR(date) → SQLite strftime('%Y', date) (four-digit years like 2024)
• MySQL SUM(number) → No changes required—SQLite supports this aggregate function natively

Updated Code Implementation #

1. Fix the DAO Query #

Rewrite your query to use SQLite functions, and add clear aliases to aggregated fields so Room can map results to your data class correctly:

@Query("""
    SELECT SUM(number) AS total_value, 
           CAST(strftime('%d', date) AS INTEGER) AS day_index
    FROM Records 
    GROUP BY day_index 
    ORDER BY day_index ASC
""")
fun getResumeData(): List<Graph>

2. Adjust the Graph Data Class #

Update the @ColumnInfo annotations to match the aliases from your query (raw function expressions like DAY(date) won’t work for mapping—this is a common gotcha!):

data class Graph ( 
    @ColumnInfo(name = "day_index") var index: Int,
    @ColumnInfo(name = "total_value") var value: Int 
)

Bonus: Extending to Month/Year Grouping #

If you need to group by month or year later, just swap out the strftime parameter:

• Monthly grouping example:

@Query("""
    SELECT SUM(number) AS total_value, 
           CAST(strftime('%m', date) AS INTEGER) AS month_index
    FROM Records 
    GROUP BY month_index 
    ORDER BY month_index ASC
""")
fun getMonthlyResumeData(): List<Graph>

• Yearly grouping example:

@Query("""
    SELECT SUM(number) AS total_value, 
           CAST(strftime('%Y', date) AS INTEGER) AS year_index
    FROM Records 
    GROUP BY year_index 
    ORDER BY year_index ASC
""")
fun getYearlyResumeData(): List<Graph>

Quick Notes #

• Ensure your date column in the Room entity is either a Date type (Room handles conversion automatically) or a String in a SQLite-recognizable format like yyyy-MM-dd
• Using aliases for aggregated and date fields eliminates mapping confusion—Room can’t match raw function calls directly to your data class properties

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