0

I've below domain model objects, with below DDL and spring version, when i try to fetch the complete aggregate (just two entities) with my custom sql, spring-data returns below error.

when I remove the aggregate definition( i.e, remove the AppUsersAuth entity from my root Entity AppUsers, this method isEmailRegistered(String Email) works fine.

So I'm thinking that either i don't have my aggregates defined correctly or my DDL or hitting a blocker in spring-data-jdbc.

Any help, suggestion on this will be much appreciated.

spring boot = 2.2.5-RELEASE
spring-data-releasetrain.version =Moore-SR5
spring-boot-starter-data-jdbc = 2.1.0.RELEASE

      
@Table("APPUSERS")
@Data @AllArgsConstructor @NoArgsConstructor
public class AppUsers implements Serializable {

    private @Id
    Long userid;
    private String username;
    @Column(value = "first_name")
    private String firstName;
    @Column(value = "last_name")
    private String lastName;
    private String email;
    @Column(value = "phone_number")
    private String phoneNumber;
    private boolean active;
    private boolean disabled;
    private boolean verified;
    private boolean locked;
    private String zipcode;
    private String password;
    @Column(value = "registered_date")
    private LocalDateTime registeredDate;
    @Column(value = "registered_date")
    private LocalDateTime lastModified;

    private List<AppUsersAuth> appUsersAuthList = new ArrayList<>();

}

    @Table("appusers_auth")
    @Data
    class AppUsersAuth {
        @Column("auth_user_id")
        private long authUserId;
        @Column("userid")
        private  Long userid;
        private String email;
        @Column("role_id")
        private String roleId;
        @Column("username")
        private String username;
        @Column("updated_time")
        private Date updatedTime;

    }






      ```

The DDL for these are:
```CREATE TABLE APPUSERS(
    userid bigserial PRIMARY KEY,
    username text not null unique ,
    password text not null,
    first_name text not null unique ,
    last_name text not null,
    zipcode text not null,
    email text not null unique 

    }


    CREATE TABLE appusers_auth (
     auth_user_id bigserial not null ,
     userid bigserial ,
     username text,
     email text,
     role_id VARCHAR(50),
     updated_time timestamp default CURRENT_TIMESTAMP,
     primary key (auth_user_id),
     CONSTRAINT FK_APPUSERS_AUTH_APPUSER foreign key (userid,username,email) references APPUSERS(userid,username,email)
    );```

    isEmailRegistered(String email) is a method in service which call findByEmail(String email)


    @Repository
    public interface AppUsersRepository extends CrudRepository<AppUsers, Long> {
    @Query("select * from APPUSERS where upper(email) = upper(:email)   ")
    AppUsers findByEmail(@Param("email") String email);
     .......
    }

01:01:08.120 [https-jsse-nio-8585-exec-10] DEBUG o.s.jdbc.core.JdbcTemplate - Executing prepared SQL statement [SELECT appusers_auth.email AS email, appusers_auth.role_id AS role_id, appusers_auth.userid AS userid, appusers_auth.username AS username, appusers_auth.auth_user_id AS auth_user_id, appusers_auth.updated_time AS updated_time, appusers_auth.APPUSERS_key AS APPUSERS_key FROM appusers_auth WHERE appusers_auth.app_users = ? ORDER BY APPUSERS_key]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT appusers_auth.email AS email, appusers_auth.role_id AS role_id, appusers_auth.userid AS userid, appusers_auth.username AS username, appusers_auth.auth_user_id AS auth_user_id, appusers_auth.updated_time AS updated_time, appusers_auth.APPUSERS_key AS APPUSERS_key FROM appusers_auth WHERE appusers_auth.app_users = ? ORDER BY APPUSERS_key]; nested exception is org.postgresql.util.PSQLException: ERROR: column appusers_auth.appusers_key does not exist
  Position: 228
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:216)
    at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAllByPath(DefaultDataAccessStrategy.java:282)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
    at com.sun.proxy.$Proxy109.findAllByPath(Unknown Source)
    at org.springframework.data.jdbc.core.convert.BasicJdbcConverter$ReadingContext.resolveRelation(BasicJdbcConverter.java:360)
    at org.springframework.data.jdbc.core.convert.BasicJdbcConverter$ReadingContext.readOrLoadProperty(BasicJdbcConverter.java:338)
    at org.springframework.data.jdbc.core.convert.BasicJdbcConverter$ReadingContext.populateProperties(BasicJdbcConverter.java:327)
    at org.springframework.data.jdbc.core.convert.BasicJdbcConverter$ReadingContext.createInstanceInternal(BasicJdbcConverter.java:463)
    at org.springframework.data.jdbc.core.convert.BasicJdbcConverter$ReadingContext.mapRow(BasicJdbcConverter.java:312)
    at org.springframework.data.jdbc.core.convert.BasicJdbcConverter.mapRow(BasicJdbcConverter.java:252)
    at org.springframework.data.jdbc.core.convert.EntityRowMapper.mapRow(EntityRowMapper.java:67)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:679)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:236)
    at org.springframework.data.jdbc.repository.support.JdbcRepositoryQuery.lambda$createObjectRowMapperQueryExecutor$4(JdbcRepositoryQuery.java:178)
    at org.springframework.data.jdbc.repository.support.JdbcRepositoryQuery.lambda$createObjectQueryExecutor$0(JdbcRepositoryQuery.java:135)
    at org.springframework.data.jdbc.repository.support.JdbcRepositoryQuery.execute(JdbcRepositoryQuery.java:124)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy120.findByEmail(Unknown Source)
    at com.rajesh.transcribe.transribeapi.api.services.JwtUserDetailsService.isEmailRegistered(JwtUserDetailsService.java:356)
    at com.rajesh.transcribe.transribeapi.api.controller.JwtAuthenticationController.createAuthenticationToken(JwtAuthenticationController.java:98)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.rajesh.transcribe.transribeapi.api.filters.JwtRequestFilter.doFilterInternal(JwtRequestFilter.java:85)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:209)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:109)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:666)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:688)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1594)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: ERROR: column appusers_auth.appusers_key does not exist
Rajesh
  • 83
  • 11
  • The code you posted doesn't look like correspond to the error you mentioned. The error mentions the table `appusersauthrolesmaster` but you annotation declares `@Table("authorities_master")`... – Augusto Sep 06 '20 at 12:40
  • Thanks for having a look, Apologies, I've updated the question with complete aggregate. I can share the complete stack trace, if required – Rajesh Sep 06 '20 at 13:06
  • @Rajesh Somewhere `app_users_auth` is mentioned as a `column` while `appusers_auth` is your table name. Please check the source for incorrect spelling or wrong usages. – Viswanath Lekshmanan Sep 07 '20 at 02:19
  • @Viswanath Lekshmanan I removed the Entity AppUsersAuthRolesMaster as this does not need to be in this root aggregate Appusers, now I've only two entities in the aggregate AppUsers and AppUsersAuth, now the error is different, spring-data-jdbc is looking for another column that is not defined in my enities or used. I can share my git repo url, if this helps , Thanks. – Rajesh Sep 07 '20 at 05:13
  • @Rajesh Please do share – Viswanath Lekshmanan Sep 07 '20 at 14:26
  • Hi Viswanath Lekshmanan, This repo is sample of mine other repos, this has example of an aggregate with 3 entities. https://github.com/rajeshhazari/spring-boot-samples – Rajesh Sep 09 '20 at 01:23

2 Answers2

1

I was able resolve this usecase (aggregate with just two entities) by using the @MappedCollection with attributes IdColumn and KeyColumn defined. and changing the Java Type from String to Long for property strong text

complete code after changes:


@Table("APPUSERS")
@Data @AllArgsConstructor @NoArgsConstructor
public class AppUsers implements Serializable {

    private @Id
    Long userid;
    private String username;
    @Column(value = "first_name")
    private String firstName;
    @Column(value = "last_name")
    private String lastName;
    private String email;
    @Column(value = "phone_number")
    private String phoneNumber;
    private boolean active;
    private boolean disabled;
    private boolean verified;
    private boolean locked;
    private String zipcode;
    private String password;
    @Column(value = "registered_date")
    private LocalDateTime registeredDate;
    @Column(value = "registered_date")
    private LocalDateTime lastModified;

    @MappedCollection(idColumn="userid", keyColumn="userid")
    private Set<AppUsersAuth> appUsersAuthList ;

}

@Table("appusers_auth")
@Data
public  class AppUsersAuth {
    @Column("auth_user_id")
    private Long authUserId;
    @Column("userid")
    private  Long userid;
    private String email;
    @Column("role_id")
    private String roleId;
    @Column("username")
    private String username;
    @Column("updated_time")
    private Date updatedTime;

}

public interface AppUsersRepository extends CrudRepository<AppUsers, Long> {

@Query("select * from APPUSERS where upper(email) = upper(:email)   ")
    AppUsers findByEmail(@Param("email") String email);

}

@Service
public class JwtUserDetailsService implements UserDetailsService {
    

 /**
     * This method will validate if the given email is registered.
     * @param email
     * @return
     */
    public boolean isEmailRegistered(String email){
        boolean isRegistered = false;
        Optional<AppUsers> appUsers = Optional.ofNullable(userRepo.findByEmail(email));
        if(!appUsers.isEmpty() && !appUsers.get().isDisabled()){
            isRegistered = true;
        } else {
            isRegistered = false;
        }
        return isRegistered;
    }

}
Rajesh
  • 83
  • 11
0

I had some problem when I created 3 classes Client, Phone and Address. Client received phone by reference field "client_id". But under annotation @MappedCollection(idColumn = "client_id") I wrote List phones. When we want to get List we always had to add keyColumn= "order_by_your_column" in your annotation. Or change List on Set.

with List: @MappedCollection(idColumn = "client_id", keyColumn= "order_by_your_column") with Set: @MappedCollection(idColumn = "client_id")

ivvasch
  • 1
  • 1