mysqlspring-bootspring-data-jpaforeign-keysone-to-many
Spring boot JPA foreign key variable can not read data from mysql db
I'm creating two tables for users and companies, multiple users can belong to one company, so it is a many-to-one relationship. But for some reason, the company_id foreign key variable can not extract data from the Company table(please see the screenshot below). I have been googling for some time but fail to find any solution. Would really appreciate your help.
UserEntity.java
@Entity(name = "Users")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@SuperBuilder
public class UserEntity extends DateAudit {
private static final long serialVersionUID = -6622427995561357149L;
@Id
@GeneratedValue()
@Type(type = "uuid-char")
@Column(columnDefinition = "VARCHAR(36)", updatable = false, nullable = false)
private UUID id;
@Column(unique = true, nullable = false)
private String username;
@Column(nullable = false)
private String password;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "company_id")
private CompanyEntity company; //can not extract data
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<ItemEntity> safetyPlans; //but this works
}
CompanyEntity.java
@Entity(name = "Companies")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@SuperBuilder
public class CompanyEntity extends DateAudit {
private static final long serialVersionUID = -6622427995561357151L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@Column(nullable = false)
private String companyId;
@OneToMany(mappedBy = "company", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<UserEntity> users;
}
UserRepository.java
public interface UserRepository extends CrudRepository<UserEntity, String>, IUserRepository {
Optional<UserEntity> findOneById(UUID id);
Optional<UserEntity> findByUsername(String username);
List<UserEntity> findAll();
}
Service class
@Service
public class UserDetailsServiceImpl implements UserDetailsService {
private UserRepository userRepository;
public UserDetailsServiceImpl(UserRepository userRepository) {
this.userRepository = userRepository;
}
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
UserEntity entity = userRepository.findByUsername(username).orElseThrow(() -> new UsernameNotFoundException(username));
return new User(entity.getUsername(), entity.getPassword(), emptyList());
}
}
companies table
users table
Error log when want to read the value(e.g. entity.getCompany().getName()):
2021-09-10 17:29:02.747 ERROR 88245 --- [nio-5000-exec-1] c.a.m.auth.JWTAuthenticationFilter : An internal error occurred while trying to authenticate the user.
org.springframework.security.authentication.InternalAuthenticationServiceException: could not initialize proxy [com.ahurufoundation.management.data.mysql.entity.user.CompanyEntity#1] - no Session
at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:108) ~[spring-security-core-5.4.5.jar:5.4.5]
DaoAuthenticationProvider.java:108
at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:133) ~[spring-security-core-5.4.5.jar:5.4.5]
AbstractUserDetailsAuthenticationProvider.java:133
at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:182) ~[spring-security-core-5.4.5.jar:5.4.5]
ProviderManager.java:182
at com.ahurufoundation.management.auth.JWTAuthenticationFilter.attemptAuthentication(JWTAuthenticationFilter.java:41) ~[main/:na]
JWTAuthenticationFilter.java:41
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:222) [spring-security-web-5.4.5.jar:5.4.5]
AbstractAuthenticationProcessingFilter.java:222
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:212) [spring-security-web-5.4.5.jar:5.4.5]
AbstractAuthenticationProcessingFilter.java:212
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103) [spring-security-web-5.4.5.jar:5.4.5]
LogoutFilter.java:103
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89) [spring-security-web-5.4.5.jar:5.4.5]
LogoutFilter.java:89
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91) [spring-web-5.3.4.jar:5.3.4]
CorsFilter.java:91
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90) [spring-security-web-5.4.5.jar:5.4.5]
HeaderWriterFilter.java:90
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75) [spring-security-web-5.4.5.jar:5.4.5]
HeaderWriterFilter.java:75
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110) [spring-security-web-5.4.5.jar:5.4.5]
SecurityContextPersistenceFilter.java:110
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80) [spring-security-web-5.4.5.jar:5.4.5]
SecurityContextPersistenceFilter.java:80
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55) [spring-security-web-5.4.5.jar:5.4.5]
WebAsyncManagerIntegrationFilter.java:55
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55) [spring-security-web-5.4.5.jar:5.4.5]
WebAsyncManagerIntegrationFilter.java:55
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:336
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:211) [spring-security-web-5.4.5.jar:5.4.5]
FilterChainProxy.java:211
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:183) [spring-security-web-5.4.5.jar:5.4.5]
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) [spring-web-5.3.4.jar:5.3.4]
DelegatingFilterProxy.java:358
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271) [spring-web-5.3.4.jar:5.3.4]
DelegatingFilterProxy.java:271
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:189
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:162
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) [spring-web-5.3.4.jar:5.3.4]
RequestContextFilter.java:100
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:189
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:162
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) [spring-web-5.3.4.jar:5.3.4]
FormContentFilter.java:93
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:189
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:162
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) [spring-web-5.3.4.jar:5.3.4]
CharacterEncodingFilter.java:201
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.4.jar:5.3.4]
OncePerRequestFilter.java:119
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:189
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.43.jar:9.0.43]
ApplicationFilterChain.java:162
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat-embed-core-9.0.43.jar:9.0.43]
StandardWrapperValve.java:202
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.43.jar:9.0.43]
StandardContextValve.java:97
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) [tomcat-embed-core-9.0.43.jar:9.0.43]
AuthenticatorBase.java:542
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) [tomcat-embed-core-9.0.43.jar:9.0.43]
StandardHostValve.java:143
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.43.jar:9.0.43]
ErrorReportValve.java:92
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.43.jar:9.0.43]
StandardEngineValve.java:78
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346) [tomcat-embed-core-9.0.43.jar:9.0.43]
CoyoteAdapter.java:346
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) [tomcat-embed-core-9.0.43.jar:9.0.43]
Http11Processor.java:374
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.43.jar:9.0.43]
AbstractProcessorLight.java:65
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887) [tomcat-embed-core-9.0.43.jar:9.0.43]
AbstractProtocol.java:887
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684) [tomcat-embed-core-9.0.43.jar:9.0.43]
NioEndpoint.java:1684
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.43.jar:9.0.43]
SocketProcessorBase.java:49
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_221]
ThreadPoolExecutor.java:1149
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_221]
ThreadPoolExecutor.java:624
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.43.jar:9.0.43]
TaskThread.java:61
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_221]
Thread.java:748
Caused by: org.hibernate.LazyInitializationException: could not initialize proxy [com.ahurufoundation.management.data.mysql.entity.user.CompanyEntity#1] - no Session
at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:170) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
AbstractLazyInitializer.java:170
at org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:310) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
AbstractLazyInitializer.java:310
at org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor.intercept(ByteBuddyInterceptor.java:45) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
ByteBuddyInterceptor.java:45
at org.hibernate.proxy.ProxyConfiguration$InterceptorDispatcher.intercept(ProxyConfiguration.java:95) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
ProxyConfiguration.java:95
at com.ahurufoundation.management.data.mysql.entity.user.CompanyEntity$HibernateProxy$FN6IM1Ba.getName(Unknown Source) ~[main/:na]
at com.ahurufoundation.management.auth.UserDetailsServiceImpl.loadUserByUsername(UserDetailsServiceImpl.java:33) ~[main/:na]
UserDetailsServiceImpl.java:33
at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:93) ~[spring-security-core-5.4.5.jar:5.4.5]
DaoAuthenticationProvider.java:93
Solution
In the many to one relationship you need to specify the referenced column name:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "company_id", referencedColumnName = "id")
private CompanyEntity company; //can not extract data
just like you would do in a join sql query
Also make sure whenever you try to access the company field from the UserEntity object you have an open session (you are in a transactional context) because it is loaded lazily (only loaded when accessed)
Be aware that unless you call: userEntity.getCompany() the company object will not be initialized, that's what lazy loading means. It only loads the object from the database if it's accessed.
And the most important! You need to call the userEntity.getCompany() in a transactional context so that you have an open session (annotate the method that finds the user and calls the getter with @Transactional)
Example (bad):
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
UserEntity user = userRepository.findByUsername(username);
// the call to user.getCompany() will throw the same error because as soon as the
// findByUsername returns the session is closed
CompanyEntity company = user.getCompany();
}
this example will throw the same error because as soon as the findByUsername returns the session is closed.
Example (good):
@Override
@Transactional
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
UserEntity user = userRepository.findByUsername(username);
CompanyEntity company = user.getCompany();
}
Now here the session will only get closed after leaving the loadUserByUsername method so inside you'll have an active session
RULE: Every time you have a lazy association if you want to be able to load it you need to have an open session (a session is always opened when a transaction starts)
- Combine rows if the dates exist in another row
- Distinguish between two maximum values in a column with Power Query
- Power Query Conditional JOIN - JOIN with WHERE clause
- Calculate combinations for a range of text values in excel
- How to update a column's values in Power BI M Language
- What's the difference between DAX and Power Query (or M)?
- Sorting issue when consolidating monthly data into a year to date file. Using query, sorting by day, time, and letter
- Power Query `List.Generate` runs too slow
- Custom Colum based on specific dates in powerquery
- How do I properly use table.group in a PowerQuery query to dynamically summarize different rows and columns?
- Excel Power Query syntax issue transform multiple columns of records
- How to capitalize only first letter of a sentence
- Power Query Import For Power BI with group by on SelectRows
- Get data from config table cell when loading data source with power query
- Translate Excel Formula into Power Query
- Counting matching pairs of letters in lists of bigrams in Power Query M
- Bigrams of a string in Power Query M
- Power Query code to refer to another query (and how buffering works)
- M code for the equivalent of of Left (string, len(string)-11)
- How to add a missing column to the Transform Sample File
- How to add or insert ' (single quotes) for every string in a list in which strings are separated by commas in Power Query
- Power BI M code Split column from Lowercase to Uppercase won't include diacritics (accents)
- M Code(Power Query) to Remove Empty columns that runs fast
- How to find the value with date closest to another date
- Error when build AOSP 14 - internal error: undefined variable CTS_TEST_SUITES_DEFAULT
- Call a table using a dynamic identifier
- Problem getting the value from a cell using M in excel
- How to rename columns based on the data within them in Power Query M?
- Power Query return a field name based on parameter
- Power Query: after combining 2 tables got something strange