Spring Data JPA关联查询遇SQLRecoverableException错误求助排查

问题 #

使用Spring Data JPA从Oracle数据库获取数据，现有Department和Class两张表，为一对多关联关系：

• Department表字段：Dept_Id, Dept_Name, CreatedDate
• Class表字段：Class_Id, Dept_Id, Class_Type, Class_Name

尝试根据Class的类型和名称获取对应的Department列表，已编写实体类、Repository及Service代码，但运行时触发数据库连接错误，仅单独查询Department表时应用可正常运行。

现有代码 #

实体类

@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);
}

错误信息 #

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]

问题排查与修复 #

1. 实体关联映射错误 #

Department实体的@OneToMany注解中，mappedBy属性值应为Class实体中的字段名department（小写d），而非Department（大写D）；同时字段名classess存在拼写错误，应改为classes。

修正后的Department实体关联代码：

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

2. Native Query语法错误 #

Oracle数据库表名、字段名默认大写，SQL中需统一使用大写；且START_TIME字段不存在，需替换为现有字段（如CREATEDDATE）：

修正后的Repository查询：

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
     @Query(nativeQuery = true,
           value="SELECT dept.* " +
                   "FROM DEPARTMENT dept INNER JOIN CLASS cls ON dept.DEPT_ID = cls.DEPT_ID " +
                   "WHERE cls.CLASS_TYPE = 'class1' " +
                   "AND cls.CLASS_NAME IN :names " +
                   "ORDER BY dept.CREATEDDATE DESC")
  List<Department> findDepartmentByClassType(@Param("names") List<String> names);
}

3. Service方法缺少返回值 #

getDepartmentsForClassType方法未返回查询结果，需添加return语句，同时确保Repository已注入：

@Service
public class DepartmentService {
    @Autowired
    private DepartmentRepository departmentRepository;

    public List<Department> getDepartmentsForClassType(List<String> className) {
        return departmentRepository.findDepartmentByClassType(className);
    }
}

4. 额外注意事项 #

• 若使用Oracle 12c以下版本，需将GenerationType.IDENTITY改为SEQUENCE策略，因为旧版本Oracle不支持自增主键的IDENTITY方式。
• 验证ojdbc驱动版本与Oracle服务器版本是否匹配，避免因版本不兼容导致的连接异常。

内容的提问来源于stack exchange，提问作者perplexedDev