/** * Reads an table from the database and returns the parsed object. * * <p>Note that this method does not parse everything: default values, enum types, primary and * foreign keys are not parsed. * * @param conn the JDBC {@link Connection} * @param template the query template * @param tableName the table name * @return the parsed table * @throws SQLException if any sql error occur */ public static Table createTable(Connection conn, QueryTemplate template, String tableName) throws SQLException { Statement st = conn.createStatement(); try { st.execute( new StringBuilder(template.getSelect()) .append("*") .append(template.getFrom()) .append(template.quoteIdentifier(tableName)) .toString()); final ResultSet rs = st.getResultSet(); ResultSetMetaData metaData = rs.getMetaData(); final Map<String, Column> columns = CollectionFactory.newMap(); for (int i = 1; i <= metaData.getColumnCount(); i++) { columns.put( metaData.getColumnName(i), new Column( metaData.getColumnName(i), getColumnType(metaData.getColumnType(i)), false, metaData.getColumnDisplaySize(i), false, null)); } return new Table(metaData.getTableName(1), columns, null, null); } finally { st.close(); } }
@Test public void updateTest() throws Exception { final Person person = new Person(1, "John Doe", new Date(new java.util.Date().getTime()), false); int expectedRowAffected = 1; int result = queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { return connection.update(person); } }, true); Person anotherPerson = queryTemplate.run( new Query<Person>() { @Override public Person perform(DatabaseConnection connection) throws Exception { return connection.read(Person.class, 1); } }, false); Assert.assertNotNull(anotherPerson); Assert.assertEquals(expectedRowAffected, result); }
@Test public void transactionMethodsTest() throws Exception { int expectedRowAffected = 3; int result = queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { int affectedRows = 0; affectedRows += connection.executeUpdate( "INSERT INTO Person (id, name, birthday, employed) VALUES (?, ?, ?, ?)", 5, "Test", new Date(new java.util.Date().getTime()), true); affectedRows += connection.executeUpdate( "UPDATE Person SET name = ? WHERE name = ?", "TestTest", "Test"); affectedRows += connection.executeUpdate("DELETE FROM Person WHERE name = ?", "TestTest"); return affectedRows; } }, true); Assert.assertEquals(expectedRowAffected, result); }
@Before public void populate() throws Exception { queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { int affectedRows = 0; affectedRows += connection.executeUpdate( "INSERT INTO Person (id, name, birthday, employed) VALUES (?, ?, ?, ?)", 1, "John", johnBirthday, true); affectedRows += connection.executeUpdate( "INSERT INTO Person (id, name, birthday, employed) VALUES (?, ?, ?, ?)", 2, "Doe", doeBirthday, false); return affectedRows; } }, true); DatabaseConnection.clearCache(); }
@After public void clear() throws Exception { queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { return connection.executeUpdate("DELETE FROM Person"); } }, true); }
@Test(expected = IllegalArgumentException.class) public void savingAnEntityWithoutPrimaryKeyShouldThrowAnException() throws Exception { queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { return connection.persist(new PersonWithoutPrimaryKey()); } }, true); }
@Test public void readTest() throws Exception { Person person = queryTemplate.run( new Query<Person>() { @Override public Person perform(DatabaseConnection connection) throws Exception { return connection.read(Person.class, 1); } }, false); Assert.assertEquals("John", person.getName()); Assert.assertEquals(johnBirthday, person.getBirthday()); }
@Test public void deleteTest() throws Exception { final Person person = new Person(1, "John", new Date(new java.util.Date().getTime()), false); int expectedRowAffected = 1; int result = queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { return connection.delete(person); } }, true); Assert.assertEquals(expectedRowAffected, result); }
@Test public void executeQueryForCollectionTest() throws Exception { int expectedSize = 2; List<Person> persons = queryTemplate.run( new Query<List<Person>>() { @Override public List<Person> perform(DatabaseConnection connection) throws Exception { return connection.executeQueryForCollection("SELECT * FROM Person", Person.class); } }, false); Assert.assertEquals(expectedSize, persons.size()); Assert.assertEquals("John", persons.get(0).getName()); Assert.assertEquals("Doe", persons.get(1).getName()); }
@Test public void executeUpdateTest() throws Exception { final int doeId = 2; int expectedRowAffected = 1; int result = queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { return connection.executeUpdate( "UPDATE Person SET name = ? WHERE id = ?", "FunnyName", doeId); } }, true); Assert.assertEquals(expectedRowAffected, result); }
@Test public void executeQueryTest() throws Exception { String expectedName = "John"; final int joeId = 1; Person person = queryTemplate.run( new Query<Person>() { @Override public Person perform(DatabaseConnection connection) throws Exception { return connection.executeQuery( "SELECT * FROM Person WHERE id = ?", Person.class, joeId); } }, false); Assert.assertNotNull(person); Assert.assertEquals(expectedName, person.getName()); }
@BeforeClass public static void initDatabaseScheme() throws Exception { HikariDataSource dataSource = new HikariDataSource(); dataSource.setJdbcUrl("jdbc:h2:mem:test"); dataSource.setDriverClassName("org.h2.Driver"); dataSource.setUsername("user"); dataSource.setPassword("password"); queryTemplate = new QueryTemplate(dataSource); queryTemplate.run( new UpdateQuery() { @Override public Integer perform(DatabaseConnection connection) throws Exception { return connection.executeUpdate( "CREATE TABLE Person(id INT PRIMARY KEY, name VARCHAR, birthday DATE, employed BOOLEAN);"); } }, true); }