java - 由 : org. h2.jdbc.JdbcSQLException 引起:数据转换错误转换

Question

我参与了一个 Spring Boot 项目，该项目读取 CSV 文件并将数据保存在 H2 数据库中。

下面提供了 CSV 文件 -

authors.csv

email;firstname;lastname 
null-walter@echocat.org;Paul;Walter 
null-mueller@echocat.org;Max;Mülle

书籍.csv

title;isbn;bookAuthors;description 

Ich helfe dir kochen. Das erfolgreiche Universalkochbuch mit großem Backteil;5554-5545-4518;null-walter@echocat.org,boby-walter@echocat.org;Auf der Suche nach einem Basiskochbuch steht man heutzutage vor einer Fülle von Alternativen. Es fällt schwer, daraus die für sich passende Mixtur aus Grundlagenwerk und Rezeptesammlung zu finden. Man sollte sich darüber im Klaren sein, welchen Schwerpunkt man setzen möchte oder von welchen Koch- und Backkenntnissen man bereits ausgehen kann.

杂志.csv

title;isbn;bookAuthors;publishedAt 

Beautiful cooking;5454-5587-3210;null-walter@echocat.org;21.05.2011

我编写 POJO 文件，

@Entity
@Table(name = "Author")
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private Long id;


    // email;firstname;lastname

    @Column
    private String email;

    @Column
    private String firstname;

    @Column
    private String lastname;

    @ManyToMany(mappedBy = "bookAuthors", fetch = FetchType.LAZY)
    List<Book> books;

    @ManyToMany(mappedBy = "bookAuthors", fetch = FetchType.LAZY)
    List<Magazine> magazines;

    public Author() {

    }

    public Author(String email, String firstname, String lastname) {
        this.email = email;
        this.firstname = firstname;
        this.lastname = lastname;
    }
}



@Entity
@Table(name = "Book")
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    // title;isbn;bookAuthors;description
    @Column
    private String title;

    @Column
    private String isbn;

    @Column(length = 5000)
    private String description;

    @ElementCollection
    @Column(name="bookAuthors")
    List<String> bookAuthors;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "book_authors",
            joinColumns = @JoinColumn(name = "book_id"),
            inverseJoinColumns = @JoinColumn(name = "author_id"))
    List<Author> authors;

    public Book() {

    }

    public Book(String title, String isbn, String description) {
        this.title = title;
        this.isbn = isbn;
        this.description = description;
    }
}



@Entity
@Table(name = "Magazine")
public class Magazine implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    // title;isbn;bookAuthors;publishedAt
    @Column
    private String title;

    @Column
    private String isbn;

    @ElementCollection
    @Column(name = "bookAuthors")
    List<String> bookAuthors;

    @Column
    private String publishedAt;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "magazine_authors",
            joinColumns = @JoinColumn(name = "magazine_id"),
            inverseJoinColumns = @JoinColumn(name = "author_id"))
    List<Author> authors;

    public Magazine() {

    }

    public Magazine(String title, String isbn, String publishedAt) {

        this.title = title;
        this.isbn = isbn;
        this.publishedAt = publishedAt;
    }
}

提供了存储库和服务文件的示例，

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {


    @Query(value = "SELECT * FROM book WHERE book.isbn=:isbn",nativeQuery = true)
    Optional<Book> findBookByIsbn(String isbn);
}

服务文件，

@Service
public class BookService {


    private BookRepository repository;

    @Autowired
    public void setBookRepository(BookRepository BookRepository) {
        this.repository = BookRepository;
    }

    @Transactional(rollbackFor = Exception.class)
    public Optional<Book> findById(Long id) {
        return repository.findById(id);
    }

    @Transactional(rollbackFor = Exception.class)
    public List<Book> findAll() {
        return (List<Book>) repository.findAll();
    }

    @Transactional(rollbackFor = Exception.class)
    public Book save(Book Product) {
        return repository.save(Product);
    }

    @Transactional
    public <S extends Book> List<Book> saveAll(List<Book> students) {

        List<Book> result = new ArrayList<>();

        if (students == null) {
            return result;
        }

        for (Book student : students) {
            result.add(repository.save(student));
        }

        return result;
    }

    @Transactional(rollbackFor = Exception.class)
    public void deleteAll() {
        repository.deleteAll();
    }

    @Transactional(rollbackFor = Exception.class)
    public Optional<Book> findBookByIsbn(String isbn){
        return repository.findBookByIsbn(isbn);
    }
}

当我尝试保存书籍数据时，我编写了代码，

String bookFilePath = "/Users/Chaklader/IdeaProjects/Publications/src/main/resources/data/books.csv";
        List<Book> books = new BookCsvFileReader(authorService).readBooksCsvData(bookFilePath);
        bookService.saveAll(books);

我收到错误，

Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "'null-walter@echocat.org' (AUTHOR: ID BIGINT NOT NULL)"; SQL statement:
insert into book_book_authors (books_id, book_authors) values (?, ?) [22018-196]

如何纠正它？

更新

BookCsvFileRead.java

public class BookCsvFileReader extends CsvFileReader {

    private AuthorService authorService;

    public BookCsvFileReader(AuthorService service) {
        this.authorService = service;
    }

    public List<Book> readBooksCsvData(String fileName) throws IOException {

        List<Book> books = new ArrayList<>();

        try {
            List<List<String>> lines = CsvFileReader.readCsvFile(fileName);

            lines.remove(0);

            for (List<String> line : lines) {

                String title = line.get(0);
                String isbn = line.get(1);

                String[] emails = line.get(2).split(",");

                List<String> bookAuthors = new ArrayList<>();

                for (String email : emails) {
                    bookAuthors.add(email);
                }

                String description = line.get(3);

                Book book = new Book(title, isbn, description, bookAuthors);
                book.setBookAuthors(bookAuthors);

                books.add(book);
            }
        }

        //
        catch (IOException e) {
            e.printStackTrace();
        }

        return books;
    }
}

Answer 1

根本问题是 CSV 文件使用电子邮件地址字符串作为作者 ID，但作者实体类被建模为使用数字序列 ID。我将代码修改如下，

@Entity
@Table(name = "Author")
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;


    @Id
    @Column(name = "email")
    private String email;

    // email;firstname;lastname

    @Column
    private String firstname;

    @Column
    private String lastname;

    @ManyToMany(mappedBy = "bookAuthors", fetch = FetchType.LAZY)
    List<Book> books;

    @ManyToMany(mappedBy = "bookAuthors", fetch = FetchType.LAZY)
    List<Magazine> magazines;

    public Author() {

    }

    public Author(String email, String firstname, String lastname) {
        this.email = email;
        this.firstname = firstname;
        this.lastname = lastname;
    }}

因此，代码现在可以正确读取和保存 CSV 文件中的数据。