CODEWITHSUNDEEP
spring-bootjpa
Bernd
Bernd

Reputation: 657

How to solve SQLException - Data conversion error in Spring Boot

I have two tables that are connected via class name (1:n).

Domain: Product (1)

Domain: HistoryPrice (n)

Product

@Entity
@Table
public class Product extends AbstractBaseDomain<Long> {

    @NotBlank
    @Size(min = 2, max = 50)
    @Column(name ="name", unique = true)
    private String name;

HistoryPrice

@Entity
@Table(name = "historyPrice")
public class HistoryPrice extends AbstractBaseDomain<Long> {

    @NotNull
    @ManyToOne
    @JoinColumn(name ="product")
    private Product product;

This is my repository

@Repository
public interface HistoryPriceRepository extends JpaRepository<HistoryPrice, Long> {

    @Query(value = "SELECT h.product " +
            "FROM history_price h " +
            "INNER JOIN product p ON h.product = p.name " +
            "WHERE p.name = :name", nativeQuery = true)
    List<?> findProductByName(@Param("name") String name);
}

This is my Controller

@PostMapping(value = "/historyPrice")
public String searchForProducts(Model model, @RequestParam String namePart) {
    List<?> productList = historyPriceService.findProductName(namePart);
    model.addAttribute(HISTORYPRICE_VIEW, productList);
    return HISTORYPRICE_VIEW;
}

This is my SQL output of my table creation:

2019-04-11 18:39:20 DEBUG org.hibernate.SQL - create table history_price (id bigint not null, version integer, price decimal(19,2) not null, valid_since timestamp not null, product bigint not null, primary key (id))

2019-04-11 18:39:20 DEBUG org.hibernate.SQL - create table product (id bigint not null, version integer, current_price decimal(19,2) not null, manufacturer varchar(50), name varchar(50), primary key (id))

This is my shortened error that I always get:

Caused by: org.hibernate.exception.DataException: could not extract ResultSet

Caused by: org.h2.jdbc.JdbcSQLException: Datenumwandlungsfehler beim Umwandeln von "HAMMER"
Data conversion error converting "HAMMER"; SQL statement:
SELECT h.product FROM history_price h INNER JOIN product p ON h.product = p.name WHERE p.name = ? [22018-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:168)

Caused by: java.lang.NumberFormatException: For input string: "HAMMER"
    at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)

I do not know whether my problem is in my repository or somewhere else.

Maybe someone can give me a the right solution or a good hint.

Thank you very much.

Upvotes: 0

Views: 2263

Answers (3)

BeWu
BeWu

Reputation: 2121

The problem indicated by your stacktrace is your join. You try to join h.product which is the id of the product object internally to h.product.name which is a string. Spring tries to parse the string as number afterwards thus resulting in the NumberFormatException.

I assume you want to get the HistoryPrice objects. Thus you have three options in your repository:

  1. Use native query as you do now but fix tablenames and join, I assume this could work: 
"SELECT h.* " +
"FROM historyPrice h " +
"INNER JOIN product p ON h.product = p.id " +
"WHERE p.name = :name"
  1. Use a JPQL query:
"SELECT h " +
"FROM historyPrice h " +
"INNER JOIN product p " +
"WHERE p.name = :name"
  1. Use the method name to let spring data generate your queries:
List<HistoryPrice> findAllByProductName(String name);

Upvotes: 1

Mohendra Amatya
Mohendra Amatya

Reputation: 441

It seems in the native query you are trying to equate a product object with a string name. 

@Query(value = "SELECT h.product " +
            "FROM history_price h " +
            "INNER JOIN product p ON h.product.name = p.name " +
            "WHERE p.name = :name", nativeQuery = true)
    List<?> findProductByName(@Param("name") String name);

If the Product Entity contains a name variable, then the above query might execute.

Upvotes: 0

user11342811
user11342811

Reputation:

do you have any stack ? Can you copy the error ?

In your log stack you should see some caused by label which will give you the place where the exception is throwed

Upvotes: 0

Related Questions