java.sql.SQLRecoverableException: No more data to read from socket when working with 2 mapped tables

Question

I am using Spring data JPA to retrieve data from database.

I have the following 2 tables: Department and Class. There is a one to many relationship with department and class.

Department columns: Dept_Id, Dept_Name, CreatedDate
Class columns: Class_Id, Dept_Id, Class_Type, Class_Name

I am trying to get the list of departments based on the class type and class name.

I have created the entities as follows:

@Table(name = "Department")
@Entity
@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Dept_Id", nullable = false)
    private Long DeptId;

    @Column(name = "Dept_Name", nullable = false, length = 256, unique = true)
    private String DeptName;

    @Column(name = "CreatedDate", length = 256)
    private String CreatedDate;

    @OneToMany(mappedBy = "Department", fetch = FetchType.EAGER)
    @ToString.Exclude
    private Set<Class> classess;
}

@Table(name = "Class")
@Entity
@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Class {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Class_Id", nullable = false)
    private Long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "Dept_Id", nullable = false)
    @ToString.Exclude
    @JsonIgnore
    private Department department;

    @Column(name = "Class_Type", length = 256)
    private String classType;

    @Column(name = "Class_Name", length = 256)
    private String className;
}

DepartmentRepository

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
     @Query(nativeQuery = true,
           value="SELECT dept.* " +
                   "FROM Department dept INNER JOIN Class cls ON dept.DeptId = cls.DeptId " +
                   "WHERE cls.classType = 'class1' " +
                   "AND cls.className IN :name " +
                   "ORDER BY START_TIME DESC")
  List<Department> findDepartmentByClassType(@Param("name") List<String> name);
}

ClassRepository

@Repository
public interface ClassRepository extends JpaRepository<Class, Long> {
}

Service:

@Service
public class DepartmentService {
     public List<Department> getDepartmentsForClassType(List<String> className) {
        departmentRepository.findDepartmentByClassType(className);
}

When I run the application I get the following error:

java.sql.SQLRecoverableException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:784) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:429) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:407) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:501) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1292) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1025) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar:na]
    j.LocalContainerEntityManagerFactoryBean : Failed to initialize JPA EntityManagerFactory: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]

I believe this error is due to a code issue in the way I have mapped the two entities. The reason I say that is because if I update the repository' select statement to get the records only from the department table and not join with class table, then the application runs successfully. Can someone help me in figuring out what I am doing wrong here.

Answer 1

I was able to figure this out. I had to add @OneToMany relationship in the Department entity as below

@OneToMany
@JoinColumn(name= "Dept_Id")
private List<Class> classList = new ArrayList<>();

Also, add @Transactional("transactionManager") to the service.