Spring Boot+H2数据库项目问题求助：约束异常与@EmbeddedId注解使用错误排查

Hey there! Let's break down your problems one by one to get your Spring Boot project back on track:

1. Fix the Compilation Error with @EmbeddedId #

The error about "Embedded property type should not be Long" makes sense—@EmbeddedId is designed for composite primary keys (made of multiple columns), not single primitive types like Long. Since your organization_application_lookup is a join table linking organizations and applications, its primary key should be the combination of organization_id and application_id.

Here's how to fix this:

Step 1: Create an Embeddable Primary Key Class #

First, make a class that represents your composite key and mark it with @Embeddable:

package com.ciaranmckenna.companycoordinatorapp.model;

import javax.persistence.Embeddable;
import java.io.Serializable;
import java.util.Objects;

@Embeddable
public class OrgAppLookupId implements Serializable {
    @Column(name = "organization_id")
    private Long organisationId;
    @Column(name = "application_id")
    private Long applicationId;

    // Default constructor required by JPA
    public OrgAppLookupId() {}

    public OrgAppLookupId(Long organisationId, Long applicationId) {
        this.organisationId = organisationId;
        this.applicationId = applicationId;
    }

    // Getters and setters
    public Long getOrganisationId() { return organisationId; }
    public void setOrganisationId(Long organisationId) { this.organisationId = organisationId; }
    public Long getApplicationId() { return applicationId; }
    public void setApplicationId(Long applicationId) { this.applicationId = applicationId; }

    // Equals and hashCode are mandatory for composite keys
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        OrgAppLookupId that = (OrgAppLookupId) o;
        return Objects.equals(organisationId, that.organisationId) &&
               Objects.equals(applicationId, that.applicationId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(organisationId, applicationId);
    }
}

Step 2: Update Your OrgAppMapper Entity #

Modify your entity to use the embeddable key with @EmbeddedId:

package com.ciaranmckenna.companycoordinatorapp.model;

import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table(name= "organization_application_lookup")
public class OrgAppMapper {
    @EmbeddedId
    private OrgAppLookupId id;

    // Default constructor required by JPA
    public OrgAppMapper() {}

    public OrgAppMapper(Long organisationId, Long applicationId) {
        this.id = new OrgAppLookupId(organisationId, applicationId);
    }

    // Getter/setter for the embedded ID
    public OrgAppLookupId getId() { return id; }
    public void setId(OrgAppLookupId id) { this.id = id; }

    // Convenience getters/setters for direct access to individual key fields
    public Long getOrganisationId() { return id.getOrganisationId(); }
    public void setOrganisationId(Long organisationId) { id.setOrganisationId(organisationId); }
    public Long getApplicationId() { return id.getApplicationId(); }
    public void setApplicationId(Long applicationId) { id.setApplicationId(applicationId); }
}

2. Resolve the H2 NULL Constraint Violation #

The error NULL not allowed for column "VALUE" tells you that your organization_application_lookup table has a VALUE column marked as non-null, but your INSERT statement isn’t providing a value for it.

Possible Fixes: #

• Check your table structure: Use the H2 console to inspect the table. If the VALUE column is unnecessary, delete it (for practice projects, you can set spring.jpa.hibernate.ddl-auto=create-drop in application.properties to regenerate tables from your updated entities).
• Update your INSERT statement: If the VALUE column is required, add a value to your query:

  INSERT INTO organization_application_lookup (organization_id, application_id, value) VALUES ('1', '1', 'sample-value')
  

3. Implement the Application Name Filter Feature #

To return applications whose names start with a given letter (e.g., 'c'), use Spring Data JPA’s query method magic or custom JPQL.

Example with Spring Data JPA Query Method #

Assuming you have an Application entity with a name field, add this method to your repository:

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface ApplicationRepository extends JpaRepository<Application, Long> {
    // Finds names starting with the given prefix (case-insensitive)
    List<Application> findByNameStartingWithIgnoreCase(String prefix);
}

Then call it from your controller:

@GetMapping("/applications/filter")
public ResponseEntity<List<Application>> filterByFirstLetter(@RequestParam String prefix) {
    List<Application> matchingApps = applicationRepository.findByNameStartingWithIgnoreCase(prefix);
    return ResponseEntity.ok(matchingApps);
}

Custom JPQL Alternative (if needed) #

If you prefer explicit control over the query:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Query("SELECT a FROM Application a WHERE LOWER(a.name) LIKE LOWER(CONCAT(:prefix, '%'))")
List<Application> filterApplicationsByPrefix(@Param("prefix") String prefix);

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