FAQ（89）： org.jooq.exception.DataAccessException: SQL [insert into "eh_short_url"."eh_url_mapping" ("

2019年9月21日

1、自己用JOOQ搭建了ORM，启动springboot工程之后，插入数据报错了，先看日志：

Request processing failed; nested exception is

2019-09-21 18:07:20.801 ERROR 14956 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.jooq.exception.DataAccessException: SQL [insert into "eh_short_url"."eh_url_mapping" ("id", "short_url", "long_url", "url_id", "create_time", "update_time", "status") values (?, ?, ?, ?, ?, ?, ?)]; 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 '"eh_short_url"."eh_url_mapping" ("id", "short_url", "long_url", "url_id", "creat' at line 1] with root cause

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 '"eh_short_url"."eh_url_mapping" ("id", "short_url", "long_url", "url_id", "creat' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_151]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_151]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_151]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_151]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) ~[mysql-connector-java-5.1.39.jar:5.1.39]
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) ~[commons-dbcp-1.4.jar:1.4]
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) ~[commons-dbcp-1.4.jar:1.4]
	at org.jooq.tools.jdbc.DefaultPreparedStatement.executeUpdate(DefaultPreparedStatement.java:88) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:466) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:184) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:155) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:151) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:139) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:134) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.DAOImpl.insert(DAOImpl.java:165) ~[jooq-3.8.0.jar:na]
	at org.jooq.impl.DAOImpl.insert(DAOImpl.java:146) ~[jooq-3.8.0.jar:na]

原因：sql语法出错了

insert into "eh_short_url"."eh_url_mapping" ("id", "short_url", "long_url", "url_id", "create_time", "update_time", "status") values (?, ?, ?, ?, ?, ?, ?)

 

后面查找代码找到了原因：

	@Override
	public DefaultConfiguration getDbConfiguration() {
		BasicDataSource basicDataSource = new BasicDataSource();
		basicDataSource.setUrl(defaultDbUrl);
		DefaultConfiguration configuration = new DefaultConfiguration();
		configuration.set(basicDataSource);
        //需要在获取Configuration时，设置方言类型=MYSQL
		configuration.setSQLDialect(SQLDialect.MYSQL);
		return configuration;
	}