org.springframework.dao.InvalidDataAccessApiUsageException

If you like a tip written by other Samebug users, mark is as helpful! Marks help our algorithm provide you better solutions and also help other users.
tip

Try to remove your alias reference when defining your sort keys, so "provider.setSortKeys("p.id": Order.ASCENDING)" would become "provider.setSortKeys("id": Order.ASCENDING)"


rafael

You have a different solution? A short tip here would help you and many other users who saw this issue last week.

  • For the following SQL: {noformat} UPDATE ppdbbandung2015.school SET name=:name, code=:code, level=:level, address=:address, address_district=:address_district, address_subdistrict=:address_subdistrict, address_rw=:address_rw, address_rt=:address_rt, is_border=:is_border, foreigner_percentage=:foreigner_percentage, option_i=:option_i, option=ARRAY[:option]::json[] WHERE id=:id; {noformat} Notice that we have {{ARRAY[:option]}} which should be proper when executed in PostgreSQL, however Spring parses the {{]}} after {{:option}} as part of the parameter name, resulting in: {noformat} org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'option]': No value registered for key 'option]' at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:336) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:374) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318) at org.soluvas.scrape.core.sql.TableDmlGenerator.lambda$upsert$6(TableDmlGenerator.java:128) at org.soluvas.scrape.core.sql.TableDmlGenerator$$Lambda$3/1563634025.doInTransaction(Unknown Source) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) at org.soluvas.scrape.core.sql.TableDmlGenerator.upsert(TableDmlGenerator.java:108) at org.soluvas.scrape.core.UpsertTest.upsert(UpsertTest.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) {noformat} Workaround: just add a space after the parameter name, however such special characters (e.g. "]", "{" ) should never form part of a named parameter. Related to SPR-13178, SPR-13179.
    via by Hendy Irawan,
  • For the following SQL: {noformat} UPDATE ppdbbandung2015.school SET name=:name, code=:code, level=:level, address=:address, address_district=:address_district, address_subdistrict=:address_subdistrict, address_rw=:address_rw, address_rt=:address_rt, is_border=:is_border, foreigner_percentage=:foreigner_percentage, option_i=:option_i, option=ARRAY[:option]::json[] WHERE id=:id; {noformat} Notice that we have {{ARRAY[:option]}} which should be proper when executed in PostgreSQL, however Spring parses the {{]}} after {{:option}} as part of the parameter name, resulting in: {noformat} org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'option]': No value registered for key 'option]' at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:336) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:374) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318) at org.soluvas.scrape.core.sql.TableDmlGenerator.lambda$upsert$6(TableDmlGenerator.java:128) at org.soluvas.scrape.core.sql.TableDmlGenerator$$Lambda$3/1563634025.doInTransaction(Unknown Source) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) at org.soluvas.scrape.core.sql.TableDmlGenerator.upsert(TableDmlGenerator.java:108) at org.soluvas.scrape.core.UpsertTest.upsert(UpsertTest.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) {noformat} Workaround: just add a space after the parameter name, however such special characters (e.g. "]", "{" ) should never form part of a named parameter. Related to SPR-13178, SPR-13179.
    via by Hendy Irawan,
  • I have a simple javabean with: { private Long aId; public Long getAId() { return aId; } public void setAId(Long id) { aId = id; } } And in my sql string i have to refer to ':AId' instead of ':aId' as I usally do. public void insertItem(Item item) throws SQLException { final String sql = "INSERT INTO ITEM (I_ID, I_TITLE, I_A_ID, I_PUB_DATE, I_PUBLISHER, I_SUBJECT, I_DESC, I_SRP, I_COST, I_AVAIL, I_ISBN, I_PAGE, I_BACKING, I_DIMENSIONS, I_IMAGE)" + "VALUES (:id, :title, :AId, :pubDate, :publisher, :subject, :desc, :srp, :cost, :avail, :isbn, :page, :backing, :dimensions, :image)"; SqlParameterSource paramSource = new BeanPropertySqlParameterSource( item); this.namedParameterJdbcTemplate.update(sql, paramSource); } If i use aId this is what happens: 11:34:32,218 ERROR [DbPopulator] Exception while handling request: org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'aId': Invalid property 'aId' of bean class [pt.uc.dei.generic.component.pojo.Item]: Bean property 'aId' is not readable or has an invalid getter method: Does the return type of the getter match the parameter type of the setter? at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:289) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:216) at pt.uc.dei.loader.dao.InsertionDao.insertItem(InsertionDao.java:70) at pt.uc.dei.loader.logic.DbPopulator.populateItemTable(DbPopulator.java:261) at pt.uc.dei.loader.logic.DbPopulator.access$400(DbPopulator.java:30) at pt.uc.dei.loader.logic.DbPopulator$1.doInTransaction(DbPopulator.java:87) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128) at pt.uc.dei.loader.logic.DbPopulator.populate(DbPopulator.java:70) at pt.uc.dei.loader.logic.DbPopulator.init(DbPopulator.java:54) at pt.uc.dei.loader.Initializer.init(Initializer.java:39) at pt.uc.dei.loader.Initializer.main(Initializer.java:44) Using :AId solves the problem.
    via by Nuno Laranjeiro,
  • I have a simple javabean with: { private Long aId; public Long getAId() { return aId; } public void setAId(Long id) { aId = id; } } And in my sql string i have to refer to ':AId' instead of ':aId' as I usally do. public void insertItem(Item item) throws SQLException { final String sql = "INSERT INTO ITEM (I_ID, I_TITLE, I_A_ID, I_PUB_DATE, I_PUBLISHER, I_SUBJECT, I_DESC, I_SRP, I_COST, I_AVAIL, I_ISBN, I_PAGE, I_BACKING, I_DIMENSIONS, I_IMAGE)" + "VALUES (:id, :title, :AId, :pubDate, :publisher, :subject, :desc, :srp, :cost, :avail, :isbn, :page, :backing, :dimensions, :image)"; SqlParameterSource paramSource = new BeanPropertySqlParameterSource( item); this.namedParameterJdbcTemplate.update(sql, paramSource); } If i use aId this is what happens: 11:34:32,218 ERROR [DbPopulator] Exception while handling request: org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'aId': Invalid property 'aId' of bean class [pt.uc.dei.generic.component.pojo.Item]: Bean property 'aId' is not readable or has an invalid getter method: Does the return type of the getter match the parameter type of the setter? at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:289) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:216) at pt.uc.dei.loader.dao.InsertionDao.insertItem(InsertionDao.java:70) at pt.uc.dei.loader.logic.DbPopulator.populateItemTable(DbPopulator.java:261) at pt.uc.dei.loader.logic.DbPopulator.access$400(DbPopulator.java:30) at pt.uc.dei.loader.logic.DbPopulator$1.doInTransaction(DbPopulator.java:87) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128) at pt.uc.dei.loader.logic.DbPopulator.populate(DbPopulator.java:70) at pt.uc.dei.loader.logic.DbPopulator.init(DbPopulator.java:54) at pt.uc.dei.loader.Initializer.init(Initializer.java:39) at pt.uc.dei.loader.Initializer.main(Initializer.java:44) Using :AId solves the problem.
    via by Nuno Laranjeiro,
  • 最新评论 - 孤旅者 - 博客园
    via by Unknown author,
    • org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'option]': No value registered for key 'option]' at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:336) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:374) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318) at org.soluvas.scrape.core.sql.TableDmlGenerator.lambda$upsert$6(TableDmlGenerator.java:128)

    Users with the same issue

    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,