JOOQ Mysql集成问题

Question

我已经为MySQL设置了JOOQ。已经完成了对各种表的编译，现在我正在尝试运行以下查询：

dsl.selectFrom(WALLPAPER).where(WALLPAPER.PARENT_ID.eq(id)).orderBy(WALLPAPER.VIEW_ORDER).fetchInto(WallpaperItem.class);

但我一直在犯这个错误

"org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select "library"."wallpaper"."id", "library"."wallpaper"."category_name", "library"."wallpaper"."icon", "library"."wallpaper"."view_order", "library"."wallpaper"."parent_id" from "library"."wallpaper" where "library"."wallpaper"."parent_id" = cast(? as int) order by "library"."wallpaper"."view_order" asc]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."wallpaper"."id", "library"."wallpaper"."category_name", "library"."wallpaper' at line 1

问题是什么？

Answer 1

生成的SQL对架构/表/列名使用双引号("name")。这就是导致MySQL语法错误的原因，它希望使用backticks (`name`)引用名称。

生成双引号的原因是，您将dsl引用配置为错误的SQLDialect，即使用SQLDialect.MYSQL以外的其他内容。例如：

DSLContext dsl = DSL.using(connection, SQLDialect.MYSQL);