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)
- Will modifying the primary key type using ALTER in MySQL reset the auto-increment value to 0?
- Auto-increment is not resetting in MySQL
- Php update database if checkbox is not checked
- Retrieve 10 records from MySQL?
- how to use str_to_date (MySQL) to parse two-digit year correctly
- sum column value(s) if previous values has same value with current value from the same table in mysql
- JDBC connection without database definition
- Checking connection to MySQL (Java)
- WARN: Establishing SSL connection without server's identity verification is not recommended
- how to solve django.db.utils.NotSupportedError in django
- How to make an Inner Join in django?
- Can I order by a conditional combination of fields in MySQL?
- Select from 2 tables in the last 30 days, return 0 if null/not exist
- Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)
- Access denied for user 'root@localhost' (using password:NO)
- Watching a table for change in MySQL?
- table is specified twice both as a target for INSERT and as separate source of data
- Python Mysql, "commands out of sync; you can't run this command now"
- Does Laravel's "soft_delete" need index on MySQL?
- How do I access a field from the main query inside a sub query in mySQL?
- Not getting unpaid or partially paid records some can help me?
- Warning: The post-install step did not complete successfully when trying to install mysql using brew in Mac OS High Sierra
- MySQL Entity Attribute Value table structure proposal
- CodeIgniter SELECT query on table with JOIN and WHERE IN a subquery
- is there a MYSQL tables schema for storing raw material, intermediate and final products?
- MySQL: Total GROUP BY WITH ROLLUP curiosity
- Problem with pip install mariadb - mariadb_config not found
- nodejs mysql memory leak on large volume of high frequency queries
- `MODIFY COLUMN` vs `CHANGE COLUMN`
- Displaying BLOB image from Mysql database into dynamic div in html