StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting Multiple IDs for Same Row While Testing CRUD in Spring with JUnit
text
Body
copied!<p>I am using JUnit4 with AbstractTransactionalJUnit4SpringContextTests, and would like to create simple CRUD unit tests - as shown below. However, the call to SQL update is causing the transaction manager to generate a new ID for my test instance. Since the new ID isn't passed back during the update I no longer have the primary key for the row I am testing - which foils the remainder of the test. </p> <p>Is there a way to prevent the transaction manager from generating a new ID for the book when update is called? (failing that is there a better way to test CRUD?)</p> <pre><code>@Test public void testCRUDBook() { Book b1 = new Book(title, author); BookFactory factory = database.getBookFactory(); int id = factory.createBook(b1); Book b2 = factory.readBook(id); assertEquals(b1.getTitle(), b2.getTitle()); assertEquals(b1.getAuthor(), b2.getAuthor()); b2.setTitle("title 2"); b2.setAuthor("author 2"); assertTrue(factory.updateBook(b2)); // The problem arises here as updating the book record causes a new Id // to be generated so querying by Id is no longer possible. Book b3 = factory.readBook(b2.getId()); assertEquals(b3.getTitle(), "title 2"); assertEquals(b3.getAuthor(), "author 2"); assertTrue(factory.deleteBook(b3)); } </code></pre> <p>Book looks like this:</p> <pre><code>public class Book { private int id; private String title; private String author; public Book() {} // NEW public Book(String title, String author) { this.title = title; this.author = author; } // READ public Book(int id, String title, String author) { this.id = id; this.title = title; this.author = author; } // GENERIC ACCESSORS (left out for brevity) } </code></pre> <p>For completeness - the factory:</p> <pre><code>public class BookFactory extends BaseDatabaseFactory { @Transactional public int createBook(final Book b) { final String insertSQL = "INSERT INTO book (author, title) VALUES (?, ?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); try { int update = jdbcTemplate.update( new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(insertSQL, PreparedStatement.RETURN_GENERATED_KEYS); ps.setString(1, b.getTitle()); ps.setString(2, b.getAuthor()); return ps; } }, keyHolder); if (update != 1) { throw new IllegalStateException("Adding book record to database resulted in " + update + " records."); } return keyHolder.getKey().intValue(); } catch (DataAccessException ex) { String msg = "Falied to create new book:" + b.toString() + "ex:" + ex.getMessage(); throw new RuntimeException(msg); } } private static class BookRowMapper implements RowMapper { @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Book b = new Book(); b.setId(rs.getInt("id")); b.setTitle(rs.getString("title")); b.setAuthor(rs.getString("author")); return b; } } @Transactional public Book readBook(int id) { Book b = null; try { String sql = "SELECT * FROM book WHERE id = " + id; b = (Book) jdbcTemplate.queryForObject(sql, new Object[]{}, new BookRowMapper()); } catch (DataAccessException ex) { throw new RuntimeException("Falied to locate book.", ex); } return b; } @Transactional public boolean updateBook(final Book b) { final String updateSQL = "UPDATE book SET author = ?, title = ? WHERE id = ?"; try { jdbcTemplate.update( new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(updateSQL); ps.setString(1, b.getAuthor()); ps.setString(2, b.getTitle()); ps.setInt(3, b.getId()); return ps; } }); } catch (DataAccessException ex) { log.error("Falied to update Book:" + b.toString(), ex); return false; } return true; } @Transactional public boolean deleteBook(final Book b) { try { jdbcTemplate.update("DELETE FROM book WHERE id = ?", b.getId()); } catch (DataAccessException ex) { log.error("Falied to delete Book:" + b.toString(), ex); return false; } return true; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload