java 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出现语法错误的原因，MySQL希望使用倒勾（`name`）引用名称

之所以会生成双引号，是因为您使用错误的SQLDialect配置了dsl引用，即使用了SQLDialect.MYSQL以外的内容。例如：

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