Failed to convert from type [java.lang.String] to type [java.lang.Long] for Spring JPA?

Question

I have a problem about implementing two entities named for Country and State in Spring JPA.

I wrote a code to pose Country and State entities shown below.

Country Entity

@Entity
@Table(name="COUNTRY",catalog ="SPRINGANGULAR")
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(of = {"id","code","countryName"}, exclude = "states")
public class Country {

    @Id
    @SequenceGenerator(name="COUNTRY_SEQ", sequenceName="COUNTRY_SEQ", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="COUNTRY_SEQ")
    @Column(name="ID", nullable = false)
    private long id;
    
    @Column(name="CODE")
    private String code;
    
    @Column(name="COUNTRY_NAME")
    private String countryName;
    
    @OneToMany(fetch=FetchType.LAZY,mappedBy="country",
            cascade= CascadeType.ALL)
    private Set<State> states = new HashSet<State>();

    public Country(String code, String countryName) {
        super();
        this.code = code;
        this.countryName = countryName;
    }
    
}

State Entity

@Entity
@Table(name="STATE",catalog ="SPRINGANGULAR")
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(of = {"id", "stateName"})
public class State {

    @Id
    @SequenceGenerator(name="STATE_SEQ", sequenceName="STATE_SEQ", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="STATE_SEQ")
    @Column(name="ID", nullable = false)
    private long id;
    
    @Column(name="STATE_NAME")
    private String stateName;
    
    @ManyToOne(fetch=FetchType.LAZY,cascade= CascadeType.ALL)
    @JoinColumn(name = "COUNTRY_ID", nullable = false)
    private Country country;

    public State(String stateName, Country country) {
        super();
        this.stateName = stateName;
        this.country = country;
    }
    
}

Then I create a State Reporistory to get states by country code.

@CrossOrigin("http://localhost:4200")
@RepositoryRestResource(collectionResourceRel = "states", path = "states")
public interface StateRepository extends JpaRepository<State, Long>{

    @Query("SELECT s FROM State s \n" + 
           "LEFT JOIN Country c ON c.id = c.id \n" + 
           "WHERE c.code = :code \n" + 
           "ORDER BY s.id ASC")
    List<State> findByCountryCode(@RequestParam("code") String code);
}

Then I write a connection url in Postman to check if it works.

http://localhost:8080/api/states/findByCountryCode?code=BR

It throws an error

Failed to convert from type [java.lang.String] to type [java.lang.Long] for value 'findByCountryCode'; nested exception is java.lang.NumberFormatException: For input string: "findByCountryCode"

How can I fix the issue?

you are trying to parse the value "findByCountryCode" to a numerical value. it is obvious that it isn't a numerical value, but text — Stultuske, Jul 09 '20 at 09:15
@Stultuske How can I fix `List findByCountryCode(@RequestParam("code") String code)` method? — S.N, Jul 09 '20 at 09:20
are you sure SELECT s FROM STATE s is valid? seems a bit off — Stultuske, Jul 09 '20 at 09:21
@Stultuske `SELECT s FROM STATE` is valid when I use thir url in Postman. `http://localhost:8080/api/states` — S.N, Jul 09 '20 at 09:27
SELECT s FROM STATE is not the same as SELECT s FROM STATE s — Stultuske, Jul 09 '20 at 09:30
This URL works: http://localhost:8080/states/search/findByCountryCode?code=BR — , Jul 09 '20 at 10:05

score 0 · Answer 1 · 2020-07-09T10:06:43.983

0

Your query is a mix of native SQL and JPQL, definitely not valid. Here is the valid one:

@Query("SELECT s FROM State s \n" + 
           "WHERE s.country.code = :code \n" + 
           "ORDER BY s.id ASC")

There is no need for explicit left join, it is done by s.country expression.

Also, there is no such thing as LEFT JOIN ON in JPQL. Here are some join examples

Also, the URL should be something like:

http://localhost:8080/states/search/findByCountryCode?code=BR

You are missing a search as path element.

edited Jul 09 '20 at 10:06

answered Jul 09 '20 at 09:29

1

I already tried this sql code and it also threw the same error. – S.N Jul 09 '20 at 09:38

Peter Walser · Answer 2 · 2020-07-09T09:47:42.277

Looks to me like a problem with the REST controller.
I assume you have a mapping for /api/states/{id} somewhere, like this:

@RestController
@RequestMapping(path = "api/states/")
public class StateController {

    @GetMapping("/{id}")
    public State getState(@PathVariable("id") long id) {
       ...
    }
}

When you call /api/states/findByCountryCode?code=BR, it then interprets findByCountryCode as path variable id of type long, which will result in exactly this exception:

NumberFormatException: For input string: "findByCountryCode"

Solution:

either restrict the id path parameter with a regex pattern (numeric), or
use a different path to query the states (such as /api/states?code=BR)

The latter pattern is a common standard for listing and finding resources:

no query parameters: list all
with query parameters: list matching -> find functionality

So something like this:

    @GetMapping("/")
    public List<State> find(@RequestParam(value = "code", required = false) String code) {
       if (code != null) {
           // find by code
       } 
       else {
           // list all
       }
    }

score 0 · Accepted Answer · answered Jul 09 '20 at 09:45

0

The solution is shown below.

I wrongly use this url http://localhost:8080/api/states/findByCountryCode?code=BR

Convert url

http://localhost:8080/api/states/findByCountryCode?code=BR`

to

http://localhost:8080/api/states?code=BR

answered Jul 09 '20 at 09:45

S.N

2,157
3
29
78

Failed to convert from type [java.lang.String] to type [java.lang.Long] for Spring JPA?

3 Answers3