I have 2 tables in database
authors (author_id, first_name, last_name);
books (book_id, book_title, book_author_id);
book_author_id is fk to author_id
and when im trying to fill database for tests
private static BookDao dao = new BookDaoImpl(new DBConnection("liquibase/liquibase.properties"));
private static Author author1 = new Author(1, "Joshua", "Bloch");
private static Author author2 = new Author(2, "Robert", "Martin");
private static Book book1 = new Book(1, "Effective Java", 1);
private static Book book2 = new Book(2, "Java Puzzlers: Traps, Pitfalls, and Corner Cases", 1);
private static Book book3 = new Book(3, "Clean code", 2);
private static Book book4 = new Book(4, "Clean Architecture", 2);
@BeforeClass
public static void fillDataBases() throws DAOException {
authorDao.save(author1);
authorDao.save(author2);
dao.save(book1);
dao.save(book2);
dao.save(book3);
dao.save(book4);
}
receiving this log
2019-05-29 23:48:26 - starting to check if author exists in database 2019-05-29 23:48:26 - creating statement for finding author by name 2019-05-29 23:48:26 - sql : 'SELECT * from authors WHERE first_name = ? AND last_name = ?', parameters : ['Joshua','Bloch'] 2019-05-29 23:48:26 - author doesn't exist in database. you can save new author 2019-05-29 23:48:26 - creating statement for saving new author 2019-05-29 23:48:26 - sql : 'insert into authors(first_name, last_name) values(?,?)', parameters : ['Joshua','Bloch'] 2019-05-29 23:48:26 - new author saved 2019-05-29 23:48:26 - starting to check if author exists in database 2019-05-29 23:48:26 - creating statement for finding author by name 2019-05-29 23:48:26 - sql : 'SELECT * from authors WHERE first_name = ? AND last_name = ?', parameters : ['Robert','Martin'] 2019-05-29 23:48:26 - author doesn't exist in database. you can save new author 2019-05-29 23:48:26 - creating statement for saving new author 2019-05-29 23:48:26 - sql : 'insert into authors(first_name, last_name) values(?,?)', parameters : ['Robert','Martin'] 2019-05-29 23:48:26 - new author saved 2019-05-29 23:48:26 - creating statement for finding book by title and author 2019-05-29 23:48:26 - sql : 'SELECT * from books WHERE book_title = ? AND book_author_id = ?', parameters : ['Effective Java',1] 2019-05-29 23:48:26 - book doesn't exist in database. we can save new book 2019-05-29 23:48:26 - creating statement for saving new book 2019-05-29 23:48:26 - book title Effective Java book author's id 1 2019-05-29 23:48:26 - sql : 'insert into books(book_title, book_author_id) values(?,?)', parameters : ['Effective Java',1] 2019-05-29 23:48:26 - new book saved 2019-05-29 23:48:26 - creating statement for finding book by title and author 2019-05-29 23:48:26 - sql : 'SELECT * from books WHERE book_title = ? AND book_author_id = ?', parameters : ['Java Puzzlers: Traps, Pitfalls, and Corner Cases',1] 2019-05-29 23:48:26 - book doesn't exist in database. we can save new book 2019-05-29 23:48:26 - creating statement for saving new book 2019-05-29 23:48:26 - book title Java Puzzlers: Traps, Pitfalls, and Corner Cases book author's id 1 2019-05-29 23:48:26 - sql : 'insert into books(book_title, book_author_id) values(?,?)', parameters : ['Java Puzzlers: Traps, Pitfalls, and Corner Cases',2] 2019-05-29 23:48:26 - new book saved 2019-05-29 23:48:26 - creating statement for finding book by title and author 2019-05-29 23:48:26 - sql : 'SELECT * from books WHERE book_title = ? AND book_author_id = ?', parameters : ['Clean code',2] 2019-05-29 23:48:26 - book doesn't exist in database. we can save new book 2019-05-29 23:48:26 - creating statement for saving new book 2019-05-29 23:48:26 - book title Clean code book author's id 2 2019-05-29 23:48:26 - sql : 'insert into books(book_title, book_author_id) values(?,?)', parameters : ['Clean code',3] 2019-05-29 23:48:26 - (conn=1118) Cannot add or update a child row: a foreign key constraint fails (`myapp`.`books`, CONSTRAINT `fk_author_books` FOREIGN KEY (`book_author_id`) REFERENCES `authors` (`author_id`) ON DELETE CASCADE ON UPDATE CASCADE)
as you can see preparedStatement increment book_author_id by 1 each time when im saving book into database, but it shouldn’t do this. can you please explain why this happening and how to fix this. if i will use just database console sql queries it will work, but if using prepared statement got this problem. Added book dao save method
public int save(Book book) throws DAOException {
int i = 0;
if (!isExists(book.getTitle(), book.getAuthorId())) {
String sql = "insert into books(book_title, book_author_id) values(?,?)";
try (Connection connection = dbConnection.getConnection()) {
logger.trace("creating statement for saving new book");
try(PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, book.getTitle());
statement.setLong(2, book.getId());
logger.trace("book title " + book.getTitle() + " book author's id " + book.getAuthorId());
logger.trace(statement);
i = statement.executeUpdate();
logger.trace("new book saved");
logger.trace(connection.getMetaData());
}
} catch (Exception e) {
logger.error(e.getMessage());
throw new DAOException("cannot save new book to database", e);
}
} else {
throw new DAOException("book " + book.getTitle() + " by author "
+ book.getAuthorId() + " already exists in database");
}
return i;
}
isExists same method with select * from books where book_title = ‘title’ and book_author_id = id return true if exists and false if doesnt
Advertisement
Answer
when you look at your code sample: you’re setting the second parameter of your prepared statement to the book id, instead of the book_author_id.
Your code:
statement.setLong(2, book.getId());
but it should be
statement.setLong(2, book.getBookAuthorId()); // assuming the getter is called that way
as a matter of fact- the ID of the books is incrementing by one for each