Wrong ID type Integer requested when creating query like findByEntityId(Long id);

[kopax]

I have to manage to get my @onetomany with a findBySiteServiceId information and I have found another bug.

This request expect my model constructor to have an ID of type Integer instead of Long as in LongId.java

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: No constructor found in com.domain.security.SiteService matching [java.lang.Integer, java.lang.String, java.lang.String]] with root cause
org.apache.ibatis.executor.ExecutorException: No constructor found in com.domain.security.SiteService matching [java.lang.Integer, java.lang.String, java.lang.String]
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createByConstructorSignature(DefaultResultSetHandler.java:656)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:605)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:578)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:859)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:831)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:315)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:290)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:187)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	at com.sun.proxy.$Proxy95.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
	at org.springframework.data.mybatis.repository.support.SqlSessionRepositorySupport.selectOne(SqlSessionRepositorySupport.java:85)

I have added on top of my constructor this one so it could start:

	public SiteService(Integer id, String i18nMessageId, String name) {
		super(name, i18nMessageId);
		this.setId(new Long(id));
	}

	public SiteService(String name, String i18nMessageId) {
		super(name, i18nMessageId);
	}

This new constructor looks a bit fancy.

I have then tried to call my private List<SiteService> findBySiteServiceId method, and I have another error regarding constructor:

Caused by: org.apache.ibatis.executor.ExecutorException: No constructor found in com.domain.security.SiteService matching [java.lang.Integer, java.lang.String, java.lang.String, java.lang.Integer, java.lang.String, java.lang.String]
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createByConstructorSignature(DefaultResultSetHandler.java:656)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:605)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:578)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:859)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyNestedResultMappings(DefaultResultSetHandler.java:913)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:868)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:831)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:315)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:290)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:187)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)

I don't know what are theses extra parameter so I have created the constructor.

This is my schema :

These are the values pass to the constructor :

It appear that it mix up with my entities, is it my using wrongly the mybatis mapping ?

I have check the example but got nothing.

[easybest]

@onetomany is not supported now, it will be supported next version.
now you must add @transient to you one-to-many column, and set it by yourself.

[kopax]

That make sens, I am still looking for a way to set it by myself.

So far, I can't add custom xml mappers. (#88)

I can't write generated custom query. ( #87)

Do you have an alternative way ?

[easybest]

see https://github.com/hatunet/spring-data-mybatis-testing/blob/extendxml/src/main/java/org/springframework/data/mybatis/samples/security/SiteFunctionRepository.java#L19

[kopax]

I am still trying to make this work, for some reason the list is null.

I have created a test case :

$ git clone git@github.com:kopax/spring-data-mybatis-testing.git && cd spring-data-mybatis-testing
$ git checkout extendxml
$ ./gradlew build --info && java -jar build/libs/spring-data-mybatis-testing-0.1.0.war

I have created two routes in Application.java :

    @RequestMapping(method = RequestMethod.GET, value = "/test/2", produces = MediaType.APPLICATION_JSON_VALUE)
    @ResponseBody
    public ResponseEntity<?> findBySiteServiceIdDirect() {
        return ResponseEntity.ok(siteServiceRepository.findOne(1L));
    }

    @RequestMapping(method = RequestMethod.GET, value = "/test/3", produces = MediaType.APPLICATION_JSON_VALUE)
    @ResponseBody
    public ResponseEntity<?> findBySiteServiceId() {
        return ResponseEntity.ok(siteFunctionRepository.findBySiteServiceId(1L));
    }

And the result :

$ curl http://localhost:8080/test/2
{"id":1,"name":"User service","siteFunctionList":[{"id":1,"name":"User management","siteService":null,"siteContentList":null,"new":false}],"new":false}
$ curl http://localhost:8080/test/3
[]

I have tried to write test but I wasn't able to load the repository in junit.

[easybest]

siteFunctionRepository.findBySiteServiceId(1L)

what's the SQL generate in console ?

[kopax]

siteServiceRepository.findOne(1L)

==>  Preparing: select "siteService".id as "id","siteService".I18N_ID as "messageId","siteService".NAME as "name","siteService.siteFunctionList".id as "siteFunctionList.id","siteService.siteFunctionList".I18N_ID as "siteFunctionList.messageId","siteService.siteFunctionList".NAME as "siteFunctionList.name","siteService.siteFunctionList".SITE_SERVICE_ID as "siteFunctionList.siteService.id" from SITE_SERVICE "siteService" left outer join SITE_FUNCTION "siteService.siteFunctionList" on "siteService".ID="siteService.siteFunctionList".SITE_SERVICE_ID where "siteService".id=? 
==> Parameters: 1(Long)
<==      Total: 1

siteFunctionRepository.findBySiteServiceId(1L)

==>  Preparing: select "siteFunction".id as "id","siteFunction".I18N_ID as "messageId","siteFunction".NAME as "name","siteFunction.siteService".id as "siteService.id","siteFunction.siteService".I18N_ID as "siteService.messageId","siteFunction.siteService".NAME as "siteService.name","siteFunction.siteContentList".id as "siteContentList.id","siteFunction.siteContentList".I18N_ID as "siteContentList.messageId","siteFunction.siteContentList".NAME as "siteContentList.name","siteFunction.siteContentList".SITE_FUNCTION_ID as "siteContentList.siteFunction.id" from SITE_FUNCTION "siteFunction" left outer join SITE_SERVICE "siteFunction.siteService" on "siteFunction".SITE_SERVICE_ID="siteFunction.siteService".id left outer join SITE_CONTENT "siteFunction.siteContentList" on "siteFunction".ID="siteFunction.siteContentList".SITE_FUNCTION_ID where ( "siteFunction.siteService".id=? ) 
==> Parameters: null
<==      Total: 0
Cleared thread-bound request context: org.apache.catalina.connector.RequestFacade@7b207b9e

[easybest]

is the SQL correct?

[kopax]

I think so, the escaping of the tables does not.

I have run the test on my database, I use a different LongId with uppercase for table ID, while in my sample github repo I am using your LongId.

Still, it appear there are some unescaped table names in each requests.

siteServiceRepository.findOne(1L)

The query produced by spring-data-mybatis :

 select 
     "siteService".ID as "id",
     "siteService".I18N_ID as "messageId",
     "siteService".NAME as "name","siteService.siteFunctionList".id as "siteFunctionList.id",
     "siteService.siteFunctionList".I18N_ID as "siteFunctionList.messageId",
     "siteService.siteFunctionList".NAME as "siteFunctionList.name",
     "siteService.siteFunctionList".SITE_SERVICE_ID as "siteFunctionList.siteService.id" 
 from 
    SITE_SERVICE "siteService" 
 left outer join 
    SITE_FUNCTION "siteService.siteFunctionList" on "siteService".ID="siteService.siteFunctionList".SITE_SERVICE_ID 
 where 
    "siteService".ID=1

It throw the following error :

relation "site_service" does not exist
name: error
length: 112
severity: ERROR
code: 42P01
position: 425
file: parse_relation.c
line: 1159
routine: parserOpenTable

It look like they are some unescaped column name.

I have corrected the query and it should look like :

 select 
     "siteService"."ID" as "id",
     "siteService"."I18N_ID" as "messageId",
     "siteService"."NAME" as "name",
     "siteService.siteFunctionList"."ID" as "siteFunctionList.id",
     "siteService.siteFunctionList"."I18N_ID" as "siteFunctionList.messageId",
     "siteService.siteFunctionList"."NAME" as "siteFunctionList.name",
     "siteService.siteFunctionList"."SITE_SERVICE_ID" as "siteFunctionList.siteService.id" 
 from 
    "SITE_SERVICE" "siteService" 
 left outer join 
    "SITE_FUNCTION" "siteService.siteFunctionList" on "siteService"."ID"="siteService.siteFunctionList"."SITE_SERVICE_ID"
 where 
    "siteService"."ID"=1

siteFunctionRepository.findBySiteServiceId(1L)

This query also has the same problem:

select 
    "siteFunction".ID as "id",
    "siteFunction".I18N_ID as "messageId",
    "siteFunction".NAME as "name",
    "siteFunction.siteService".id as "siteService.id",
    "siteFunction.siteService".I18N_ID as "siteService.messageId",
    "siteFunction.siteService".NAME as "siteService.name",
    "siteFunction.siteContentList".id as "siteContentList.id",
    "siteFunction.siteContentList".I18N_ID as "siteContentList.messageId",
    "siteFunction.siteContentList".NAME as "siteContentList.name",
    "siteFunction.siteContentList".SITE_FUNCTION_ID as "siteContentList.siteFunction.id" 
from 
    SITE_FUNCTION "siteFunction" 
left outer join 
    SITE_SERVICE "siteFunction.siteService" on "siteFunction".SITE_SERVICE_ID="siteFunction.siteService".id 
left outer join 
    SITE_CONTENT "siteFunction.siteContentList" on "siteFunction".ID="siteFunction.siteContentList".SITE_FUNCTION_ID 
where 
    ( "siteFunction.siteService".id=1 ) 

It throw the following :

relation "site_function" does not exist
name: error
length: 113
severity: ERROR
code: 42P01
position: 604
file: parse_relation.c
line: 1159
routine: parserOpenTable

Corrected query:

select 
    "siteFunction"."ID" as "id",
    "siteFunction"."I18N_ID" as "messageId",
    "siteFunction"."NAME" as "name",
    "siteFunction.siteService"."ID" as "siteService.id",
    "siteFunction.siteService"."I18N_ID" as "siteService.messageId",
    "siteFunction.siteService"."NAME" as "siteService.name",
    "siteFunction.siteContentList"."ID" as "siteContentList.id",
    "siteFunction.siteContentList"."I18N_ID" as "siteContentList.messageId",
    "siteFunction.siteContentList"."NAME" as "siteContentList.name",
    "siteFunction.siteContentList"."SITE_FUNCTION_ID" as "siteContentList.siteFunction.id" 
from 
    "SITE_FUNCTION" "siteFunction" 
left outer join 
    "SITE_SERVICE" "siteFunction.siteService" on "siteFunction"."SITE_SERVICE_ID"="siteFunction.siteService"."ID"
left outer join 
    "SITE_CONTENT" "siteFunction.siteContentList" on "siteFunction"."ID"="siteFunction.siteContentList"."SITE_FUNCTION_ID"
where 
    ( "siteFunction.siteService"."ID"=1 ) 

[easybest]

because of missing quotation marks?