SimpleJdbcUpdate fails on compile query for table with single autoincrement field [SPR-4113]

[spring-projects-issues]

Eduard Dudar opened SPR-4113 and commented

Hi Spring team. Here is code that produces exception listed below.

<code>
private SimpleJdbcInsert delegate;
/**

• Creates new instance of the object and initializes it with given data
• source.
• @param ds

  {@link DataSource} instance to run object on.

*/
public InsertPrincipal(DataSource ds) {
this.delegate = new SimpleJdbcInsert(ds).withTableName("principal").usingColumns("id").usingGeneratedKeyColumns("id");
this.delegate.compile();
Number n = this.delegate.executeAndReturnKey(Collections.singletonMap("id", null));
}
</code>

Here is SQL statement for database table:
CREATE TABLE principal (
id bigint(20) NOT NULL auto_increment,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Exception thrown in compile method:
2007-11-16 14:41:14,377 ERROR: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'x': Autowiring of methods failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Unable to locate columns for table principal
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:233)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:986)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:519)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:169)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:168)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:404)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:735)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:369)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:245)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:188)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3764)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4216)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:626)
at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:553)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:488)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1138)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:736)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:448)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:700)
at org.apache.catalina.startup.Catalina.start(Catalina.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433)
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Unable to locate columns for table principal
at org.springframework.jdbc.core.metadata.TableMetaDataContext.createInsertString(TableMetaDataContext.java:291)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compileInternal(AbstractJdbcInsert.java:257)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compile(AbstractJdbcInsert.java:240)
...
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredElement.inject(AutowiredAnnotationBeanPostProcessor.java:372)
at org.springframework.beans.factory.annotation.InjectionMetadata.injectMethods(InjectionMetadata.java:69)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:230)
... 36 more

Thanks in advance for resolving this issue.

---

Affects: 2.5 RC1

[spring-projects-issues]

Eduard Dudar commented

Here is code that requires column names be differ from generated keys:

org.springframework.jdbc.core.metadata.TableMetaDataContext.java:
public String createInsertString(String[] generatedKeyNames) {
...
insertStatement.append(" (");
int columnCount = 0;
for (String columnName : this.getTableColumns()) {
if (!keys.contains(columnName.toUpperCase())) {
columnCount++;
if (columnCount > 1) {
insertStatement.append(", ");
}
insertStatement.append(columnName);
}
}
insertStatement.append(") VALUES(");
if (columnCount < 1) {
throw new InvalidDataAccessApiUsageException("Unable to locate columns for table " + this.getTableName());
}
...
}

[spring-projects-issues]

Eduard Dudar commented

But here is code that required generatedKeys for KeyHolder:

org.springframework.jdbc.core.simple.AbstractJdbcInsert.java
private KeyHolder executeInsertAndReturnKeyHolderInternal(final List<Object> values) {
...
//Get the key
String keyQuery = tableMetaDataContext.getSimulationQueryForGetGeneratedKey(
tableMetaDataContext.getTableName(),
getGeneratedKeyNames()[0]);
...
}

Seems like collision happened.

[spring-projects-issues]

Juergen Hoeller commented

Thomas, do you have a chance to look at this in time for 2.5 final?

Juergen

[spring-projects-issues]

Thomas Risberg commented

I changed the code so you can now insert into a table with a key that is autogenerated and that has no other columns.

You code must be modified to run - remove the withColumnName("id") and the id parameter value. Just pass in an empty Map.

This should work:

<code>
private SimpleJdbcInsert delegate;
/**

• Creates new instance of the object and initializes it with given data
• source.
• @param ds
• {@link DataSource} instance to run object on.
  */
  public InsertPrincipal(DataSource ds) {
  this.delegate = new SimpleJdbcInsert(ds).withTableName("principal").usingGeneratedKeyColumns("id");
  this.delegate.compile();
  Number n = this.delegate.executeAndReturnKey(new HashMap());
  }

</code>