英文:
Error: The column name id is not valid from Spring Data Jpa
问题
It seems like you're encountering an error related to the column name "id" not being valid when executing a query with Spring Data JPA. This error might be caused by an inconsistency between your entity class and the actual database table structure. Here are some things you can check:
-
Case Sensitivity: Make sure that the column names in your entity class match the column names in your database table exactly, including case sensitivity. Column names in your entity class are defined using Java naming conventions, which are typically camelCase, while column names in the database are often written in uppercase or lowercase.
-
Table Name: Double-check that the
@Tableannotation'snameattribute in your entity class matches the actual name of the database table. Also, verify that the schema of the table matches the schema you are connecting to. -
Column Annotations: Ensure that each field in your entity class has a
@Columnannotation with the correct column name that corresponds to your database table. The column names in your entity class should match the column names in the database. -
Repository Queries: Your repository interface should have methods that match the queries defined in your
@Queryannotations. In your repository code, you seem to have two methods with similar queries, but one returns aList<Portal_User>and the other returns aList<PortalUserInterface>. Make sure the return types and method names correspond correctly. -
Database Schema: Check the actual schema of your database. It's possible that there is another schema in use, and the JPA connection might not be configured to use the correct schema.
-
Data Source Configuration: Verify that your data source configuration in your Spring application properties or configuration class is pointing to the correct database.
-
Entity Class Location: Ensure that your entity class is being scanned and picked up by Spring. The package containing your entity class should be included in the scanning configuration.
-
Database Connection: If you've recently made changes to your entity class or database schema, make sure you've restarted your application to apply those changes.
Remember to carefully compare the column names in your entity class with the actual column names in the database. Even small discrepancies in case or naming can lead to issues like the one you're encountering.
英文:
I am trying to find some records from a table using Spring Data Jpa but I keep getting error that says The column id is not valid. I have searched online for solutions like <https://stackoverflow.com/questions/31921482/the-column-name-is-not-valid-error> , <https://stackoverflow.com/questions/11269892/the-column-name-is-not-valid> , <https://stackoverflow.com/questions/50859830/spring-data-jpa-the-column-name-id-is-not-valid> and the solutions have not helped my specific case. So I decided to ask for help here.
The error occurs when I hit the controller from postman localhost:8080//api/portal/portal-user
The get request enters the controller and stops at portal_usersRepository.findApprovedUsers(); it doesn’t enter the service class but throws that error at the point of queryin the dB.
PS: The dB table am working with is a legacy table with a lot of null columns.
Below is the entity class:
@Data
@Entity
@Table(name = "PORTAL_USER")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Portal_User implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private Long id;
@Column(name = "SURNAME")
private String surname;
@Column(name = "FIRSTNAME")
private String firstname;
@Column(name = "OTHER_NAME")
private String otherName;
@Column(name = "EMAIL")
private String email;
@Column(name = "PHONE_NUMBER")
private String phoneNumber;
@Column(name = "USER_ID")
private String userId;
@Column(name = "APPROVED")
private Boolean approved;
@ManyToOne
@JoinColumn(name = "DEPARTMENT_FK", referencedColumnName = "id")
private Department departmentFk;
@ManyToOne
@JoinColumn(name = "PORTAL_USER_TYPE_FK", referencedColumnName = "id")
private Portal_User_Type portalUserTypeFk;
@ManyToOne
@JoinColumn(name = "MINISTRY_FK", referencedColumnName = "id")
private Ministry ministryFk;
@ManyToOne
@JoinColumn(name = "AGENCY_FK", referencedColumnName = "id")
private Agency agencyFk;
@Column(name = "GENDER")
private String gender;
@Column(name = "DATE_OF_BIRTH")
private Date dateOfBirth;
@Column(name = "STREET_NUMBER")
private String streetNumber;
@Column(name = "POSTCODE")
private String postcode;
@ManyToOne
@JoinColumn(name = "COUNTRY_FK", referencedColumnName = "id")
private Country countryFk;
@Column(name = "NATIONALITY")
private String nationality;
@Column(name = "ADDRESS")
private String address;
@Column(name = "CITY")
private String city;
@Column(name = "STATE")
private String state;
@Column(name = "LGA")
private String lga;
@Column(name = "ACCREDITATION_NUMBER")
private String accreditationNumber;
@ManyToOne
@JoinColumn(name = "PICK_UP_LOCATIONS_FK", referencedColumnName = "id")
private Pick_Up_Locations pickUpLocations_fk;
@Column(name = "OCCUPATION")
private String occupation;
@Column(name = "IS_ACCREDITED_USER")
private Boolean isAccreditedUser;
@OneToOne(targetEntity = CAC_Branch_Location.class)
@JoinColumn(name = "CAC_BRANCH_LOCATION_FK", referencedColumnName = "id")
private CAC_Branch_Location branchLocation;
@Column(name = "STAFF_ID")
private String staffId;
@Column(name = "IS_CUSTOMER")
private Boolean isCustomer;
@Column(name = "NIN")
private String nin;
@Column(name = "identityType")
private String identityType;
@Column(name = "PWD")
private String PWD;
@Column(name = "IS_A_SPECIAL_ACCESS_USER")
private Boolean isASspecialAccessUser;
@Column(name = "UPDATING_OFFICE")
private String updatingOffice;
@Column(name = "PASSWORD")
private String password;
@Column(name = "DATE_CREATED")
private Date dateCreated;
@Column(name = "EMAIL_ADDRESS_VERIFIED")
private Boolean emailAddressVerified;
@Column(name = "PHONE_NUMBER_VERIFIED")
private Boolean phoneNumberVerified;
@Column(name = "LOCKED_OUT")
private Boolean lockedOut;
@Column(name = "LOCKOUT_DATE")
private Date lockoutDate;
@Column(name = "STATUS")
private String status;
@Column(name = "SECRET_QUESTION")
private String secretQuestion;
@Column(name = "SECRET_QUESTION_ANSWER")
private String secretQuestionAnswer;
@Column(name = "CONTACT_ADDRESS")
private String contactAddress;
@OneToOne(targetEntity = Address.class)
@JoinColumn(name = "ADDRESS_FK", referencedColumnName = "id")
private Address addressFk;
@ManyToOne
@JoinColumn(name = "NATIONALITY_FK", referencedColumnName = "id")
private Nationality nationalityFk;
@Column(name = "USERNAME")
private String username;
@JsonIgnore
@ManyToMany
@JoinTable(name = "portal_user_role",
joinColumns = @JoinColumn(name = "PORTAL_USER_FK"),
inverseJoinColumns = @JoinColumn(name = "ROLE_FK"))
private List<Role> roles;
@Transient
private long noOfTasks;
@Transient
private AccreditationUserRequests accreditationUserRequests;
public Portal_User() {
}
public Portal_User(Long id, String username, String email) {
this.id = id;
this.email = email;
this.username = username;
}
public Portal_User(Long id, String surname, String firstname, String otherName) {
this.id = id;
this.surname = surname;
this.firstname = firstname;
this.otherName = otherName;
}
public Portal_User(List<Role> roles) {
this.roles = roles;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null) {
return false;
}
Portal_User user = (Portal_User) o;
return Objects.equals(id, user.getId());
}
@Override
public int hashCode() {
return Objects.hash(id);
}
@Transient
public String getFullNames(){
String fullNames = this.getFirstname();
if (this.getOtherName()!=null && this.getOtherName().length() > 0){
fullNames += " "+this.getOtherName();
}
fullNames += " "+this.getSurname();
return fullNames;
}
@Transient
public String getFullAddress2() {
String anAddress = "";
if (!Strings.isNullOrEmpty(this.address)) {
anAddress = anAddress + this.address;
}
if (!Strings.isNullOrEmpty(this.city)) {
if (Strings.isNullOrEmpty(anAddress)) {
anAddress = this.city;
} else {
anAddress = anAddress + ", " + this.city;
}
}
if (!Strings.isNullOrEmpty(this.state)) {
if (Strings.isNullOrEmpty(anAddress)) {
anAddress = this.state;
} else {
anAddress = anAddress + ", " + this.state;
}
}
return anAddress;
}
}
This is my service class:
@Data
@Service
public class Portal_UserService {
@Autowired
private Portal_UsersRepository portal_usersRepository;
public List<PortalUserDto> findPortalUsers(List<Portal_User> portal_users) {
System.out.println("Hello SERVICE...>>>");
List<PortalUserDto> portalUserDtoList = new ArrayList<>();
if (portal_users != null && !portal_users.isEmpty()) {
portal_users.stream().map(portalUserReport -> {
PortalUserDto portalDto = new PortalUserDto();
portalDto.setFirstname(portalUserReport.getFirstname());
portalDto.setSurname(portalUserReport.getSurname());
portalDto.setEmail(portalUserReport.getEmail());
portalDto.setApproved(portalUserReport.getApproved());
return portalDto;
}).forEachOrdered(portalDto -> {
portalUserDtoList.add(portalDto);
});
}
return portalUserDtoList;
}
}
Here is a snippet from my controller:
@GetMapping("/portal-user")
public ResponseEntity<Object> findPortalByApproved() {
List<PortalUserInterface> portalUsers = portal_usersRepository.findApprovedUsers();
List<PortalUserDto> userDtoList = new ArrayList<>();
System.out.println("Hello CONTROLLER...>>>");
if (portalUsers != null) {
portalUsers.forEach((portalUser) -> {
PortalUserDto userDto = new PortalUserDto();
userDto.setFirstname(portalUser.getFirstname());
userDto.setSurname(portalUser.getSurname());
userDto.setEmail(portalUser.getEmail());
userDto.setApproved(portalUser.getApproved());
userDtoList.add(userDto);
});
}
return ResponseEntity.ok(new JsonResponse("See Data Object for Details", userDtoList));
}
UPDATE
Here is my repository:
public interface Portal_UsersRepository extends JpaRepository<Portal_User, Long> {
@Query(value="SELECT FIRSTNAME, SURNAME, EMAIL FROM PORTAL_USER "
+ "WHERE APPROVED = 1 "
+ "ORDER BY ID DESC ", nativeQuery = true)
List<Portal_User> findApprovedPortalUsers();
@Query(value="SELECT FIRSTNAME, SURNAME, EMAIL FROM PORTAL_USER "
+ "WHERE APPROVED = 1 "
+ "ORDER BY ID DESC ", nativeQuery = true)
List<PortalUserInterface> findApprovedUsers();
}
When I use the entity in my controller like this:
List<Portal_User> portalUsers = portal_usersRepository.findApprovedPortalUsers();
List<PortalUserDto> userDtoList = new ArrayList<>();
I get the following error trace:
...SYSTEM LAUNCHED!!!
2020-09-29 21:28:01.452 INFO 32616 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-09-29 21:28:01.456 INFO 32616 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2020-09-29 21:28:01.468 INFO 32616 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 12 ms
Hibernate: SELECT FIRSTNAME, SURNAME, EMAIL FROM PORTAL_USER WHERE APPROVED = 1 ORDER BY ID DESC
2020-09-29 21:28:37.894 WARN 32616 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: S1093
2020-09-29 21:28:37.894 ERROR 32616 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : The column name id is not valid.
2020-09-29 21:28:37.914 ERROR 32616 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: The column name id is not valid.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-7.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:686) ~[mssql-jdbc-7.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2328) ~[mssql-jdbc-7.4.1.jre8.jar:na]
at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java) ~[HikariCP-3.4.5.jar:na]
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:63) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:808) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:732) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1044) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.processResultSet(Loader.java:995) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:964) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:350) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2887) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2869) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2701) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.Loader.list(Loader.java:2696) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2142) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1163) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:173) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.hibernate.query.Query.getResultList(Query.java:165) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126) ~[spring-data-jpa-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88) ~[spring-data-jpa-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154) ~[spring-data-jpa-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142) ~[spring-data-jpa-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195) ~[spring-data-commons-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152) ~[spring-data-commons-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130) ~[spring-data-commons-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80) ~[spring-data-commons-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149) ~[spring-data-jpa-2.3.4.RELEASE.jar:2.3.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at com.sun.proxy.$Proxy119.findApprovedPortalUsers(Unknown Source) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at com.sun.proxy.$Proxy67.findApprovedPortalUsers(Unknown Source) ~[na:na]
at com.oasis.isds.executivedashboard.controller.PortalUsersController.findPortalByApproved(PortalUsersController.java:34) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:626) ~[tomcat-embed-core-9.0.38.jar:4.0.FR]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.38.jar:4.0.FR]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.38.jar:9.0.38]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
2020-09-29 21:56:23.953 WARN 32616 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=14m3s351ms76µs200ns).
And When I use PortalUserInterface, List<PortalUserInterface> portalUsers = portal_usersRepository.findApprovedUsers();
I get this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Read timed out
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2924) ~[mssql-jdbc-7.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2029) ~[mssql-jdbc-7.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6418) ~[mssql-jdbc-7.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7579) ~[mssql-jdbc-7.4.1.jre8.jar:na]
Here is a snippet from my pom.xml
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
Please help me because this error is happening in different controllers I am using for this project. I have spent hours trying to fix it to no avail. I would appreciate your help.
答案1
得分: 0
我成功地找出了这个问题的原因并进行了修复，因此我决定分享出来，以造福他人。行之有效的方法是使用一个接口来获取记录。但最初这种方法并不起作用，因为数据库表格中有超过400万条记录。这就是我为什么会收到读取超时错误的原因。只有在将项目运行在调试模式下后，我才发现了这一点，因为最初我并没有被通知表格的大小。所以这里是我查询数据库的方式...
我将搜索减少到了前5000条记录...而且它奏效了！
@Query(value="SELECT TOP 5000 FIRSTNAME, SURNAME, EMAIL FROM PORTAL_USER "
+ "WHERE APPROVED = 1 "
+ "ORDER BY ID DESC ", nativeQuery = true)
List<PortalUserInterface> findApprovedUsers();
以下是我用于从仓库中获取记录的接口
public interface PortalUserInterface {
public Long getId();
public String getFirstname();
public String getSurname();
public String getEmail();
}
这就是我解决问题的方法。
感谢所有在某种程度上做出贡献的人。
英文:
I was able to identify the cause of this problem and fixed it, so I decided to share for the benefit of others. The approach that worked was using an interface to fetch the records. But this approach did not work initially because the database table has over 4 million records. That was why I was getting the read timed out error. I only discovered this after running my project in debug mode as I was not notified of the size of the table initially. So here is how I queried the database....
I reduced the search to TOP 5000 ... and it worked!
@Query(value="SELECT TOP 5000 FIRSTNAME, SURNAME, EMAIL FROM PORTAL_USER "
+ "WHERE APPROVED = 1 "
+ "ORDER BY ID DESC ", nativeQuery = true)
List<PortalUserInterface> findApprovedUsers();
Here is the interface I used for fetching the records from the repository
public interface PortalUserInterface {
public Long getId();
public String getFirstname();
public String getSurname();
public String getEmail();
}
This was how I solved the problem.
Thanks to all who contributed one way or the other.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论