/**
* 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);
}
