CrudRepository查询实体时使用错误数据库表/模式问题求助

Hey there, let's break down why your CrudRepository methods are pulling data from the wrong schema, and how to fix it!

The Core Problem Recap #

From what you described:

• You have 3 database schemas, each with 5 tables of the same name
• When using CrudRepository#count or findAll, the generated SQL targets the wrong schema (e.g., one_to_one.primary_table instead of identity_schema.primary_table)
• This only stops happening if you comment out all other PrimaryTable entities except the one tied to your target schema
• Oddly, CrudRepository#findById works perfectly fine

Likely Root Causes #

1. Duplicate Entity Class Names Confusing JPA Metadata
   Spring Data JPA relies on entity class names to resolve JPQL queries and map repositories to entities. When you have multiple classes named PrimaryTable (even with different @Table(schema = ...) annotations), the JPA EntityManager can mix up their metadata during query generation.

findById works because it’s tied directly to the entity’s primary key mapping, which is more explicitly bound to the entity’s schema configuration. But count/findAll generate JPQL using the simple class name, leading JPA to pick the "first" or conflicting entity’s schema by mistake.

2. Repository Generic Type Ambiguity
   If all your repositories use CrudRepository<PrimaryTable, Long>, Spring can’t distinguish which PrimaryTable entity each repository should target. It ends up associating the repository with an unintended entity (and its schema) during bean creation.

Fixes to Try #

1. Rename Entity Classes (Simplest Solution)

Give each schema-specific entity a unique class name, like IdentityPrimaryTable, OneToOnePrimaryTable, etc. This eliminates any ambiguity for JPA:

@Entity
@Table(name = "primary_table", schema = "identity_schema")
public class IdentityPrimaryTable {
    // ... entity fields and mappings
}

Then update your repository to match:

public interface IdentityPrimaryTableRepository extends CrudRepository<IdentityPrimaryTable, Long> { }

2. Explicitly Bind Repositories to Entities with @Query

If you don’t want to rename classes, use custom @Query annotations to force the correct schema in JPQL or native SQL:

public interface IdentityPrimaryTableRepository extends CrudRepository<PrimaryTable, Long> {
    // JPQL (uses entity's schema from @Table)
    @Query("SELECT COUNT(p) FROM PrimaryTable p")
    long count();

    // Or native SQL for full control
    @Query(value = "SELECT COUNT(*) FROM identity_schema.primary_table", nativeQuery = true)
    long countNative();
}

3. Configure Separate EntityManager Factories per Schema

For more complex multi-schema setups, create distinct EntityManagerFactory beans for each schema, then annotate repositories to use the correct one with @Qualifier:

@Configuration
@EnableJpaRepositories(
    basePackages = "com.yourpackage.identityrepo",
    entityManagerFactoryRef = "identityEntityManagerFactory"
)
public class IdentitySchemaConfig {
    // Define DataSource, LocalContainerEntityManagerFactoryBean for identity_schema here
}

This ensures each repository set is tied exclusively to its target schema’s entity metadata.

4. Verify @Table Schema Annotations

Double-check that every PrimaryTable entity has the correct schema value in its @Table annotation. A missing or incorrect schema here will cause JPA to fall back to the default schema:

@Entity
@Table(name = "primary_table", schema = "identity_schema") // Ensure this matches your target schema
public class PrimaryTable { }

Additional Debugging Steps #

• Enable JPA SQL logging with spring.jpa.show-sql=true in your application properties to see exactly what queries are being generated.
• Check your component scan configuration to ensure entities from all schemas are being scanned correctly, but not causing metadata conflicts.

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