package squill.tests;
import static java.util.Arrays.asList;
import static junit.framework.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static squill.functions.Operations.add;
import static squill.functions.Operations.eq;
import static squill.functions.Operations.ge;
import static squill.functions.Operations.insertElement;
import static squill.functions.Operations.updateElement;
import static squill.tuple.Tuple2._;
import static squill.tests.TestUtil.DAY_FORMAT;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.util.Collections;
import java.util.Date;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import squill.Squill;
import squill.query.select.WritableTable;
import squill.query.select.Column;
import squill.db.Database;
import squill.db.StatementCallback;
import squill.db.UpdateStatementCallback;
import squill.tuple.Tuple2;
import squill.model.ComplaintData;
import squill.model.CustomerData;
import squill.model.ComplaintData.ComplaintTable;
import squill.model.CustomerData.CustomerTable;
/**
* @author Michael Hunger
* @since 26.08.2008
*/
public class UpdateTest {
private Database database;
private Squill squill;
private static final int COMPLAINT_ID = 123;
private static final int DELTA = 20;
@Test
public void insertComplaint() throws ParseException {
final ComplaintTable compl = new ComplaintTable("c");
final Date resolvedDate = DAY_FORMAT.parse("2008-08-26");
squill
.insert(compl)
.values(
insertElement(compl.id, 7),
insertElement(compl.customerId, 3),
insertElement(compl.refoundSum, new BigDecimal("7.2")),
insertElement(compl.resolvedDate, resolvedDate));
final Tuple2<BigDecimal, Date> result = database.execute(
"select refound_sum, resolved_date from complaint where id = 7 ", null,
new StatementCallback<Tuple2<BigDecimal, Date>>() {
public Tuple2<BigDecimal, Date> execute(final PreparedStatement stmt) throws Exception {
final ResultSet rs = stmt.executeQuery();
assertTrue("first row",rs.next());
final Tuple2<BigDecimal, Date> result = _(rs.getBigDecimal("REFOUND_SUM"), new Date(rs.getDate("RESOLVED_DATE").getTime()));
assertFalse("single row",rs.next());
return result;
}
});
assertEquals("Sum","7.2",result.v1.toString());
assertEquals("Date","2008-08-26", DAY_FORMAT.format(result.v2));
}
static class AutoKey {
Integer id;
String title;
public Integer getId() {
return id;
}
public void setId(final Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(final String title) {
this.title = title;
}
public static class AutoKeyTable extends WritableTable<AutoKey> {
@Override
public String getTableName() {
return "auto_key";
}
@Override
public Class<AutoKey> getTableType() {
return AutoKey.class;
}
public AutoKeyTable() {
}
public final Column<java.lang.Integer, AutoKey> id =
new Column<java.lang.Integer, AutoKey>("ID", java.lang.Integer.class, "id", this);
public final Column<java.lang.String, AutoKey> title =
new Column<java.lang.String, AutoKey>("TITLE", java.lang.String.class, "title", this);
}
}
@Test
public void insertWithGeneratedKeys() throws ParseException {
final AutoKey.AutoKeyTable autoKey = new AutoKey.AutoKeyTable();
database.execute("create table auto_key (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY, title varchar(50))",null, new UpdateStatementCallback());
final long id=squill
.insert(autoKey)
.returnId()
.values(
insertElement(autoKey.title, "Test"));
assertEquals("key generated",100,id);
final String title = database.executeUncheckedQuery("select title from auto_key where id = "+id,String.class);
assertEquals("AutoKey inserted","Test",title);
}
@Test public void insertCRU() {
final ComplaintTable compl = new ComplaintTable("complaint");
final CustomerTable cust = new CustomerTable("customer");
final CustomerData client =
squill
.from(cust)
.where(eq(cust.id, 1))
.select(cust);
final ComplaintData comp = new ComplaintData();
comp.setId(COMPLAINT_ID);
comp.setCustomerId(client.getId());
comp.setTitle("Phone not working");
squill
.insert(compl)
.values(insertElement(compl, comp));
assertTrue("row found",database.execute("select title, customer_id from complaint where id = ?", Collections.singletonList(COMPLAINT_ID),
new StatementCallback<Boolean>(){
public Boolean execute(final PreparedStatement stmt) throws Exception {
final ResultSet rs = stmt.executeQuery();
assertTrue("single row",rs.next());
assertEquals("title",comp.getTitle(), rs.getString("TITLE"));
assertEquals("customer id", (int)comp.getCustomerId(), rs.getInt("CUSTOMER_ID"));
assertFalse("single row",rs.next());
return true;
}
}));
}
@Test
public void update() {
final ComplaintTable COMPLAINT = new ComplaintTable("complaint");
final ComplaintData complaint = new ComplaintData();
complaint.setId(1);
complaint.setCustomerId(2);
complaint.setComments("Comments changed...");
squill
.update(COMPLAINT)
.where(eq(COMPLAINT.id, complaint.getId()))
.set(updateElement(COMPLAINT, complaint));
assertTrue("row found",database.execute("select comments, customer_id from complaint where id = ?", Collections.singletonList(1),
new StatementCallback<Boolean>(){
public Boolean execute(final PreparedStatement stmt) throws Exception {
final ResultSet rs = stmt.executeQuery();
assertTrue("single row",rs.next());
assertEquals("title",complaint.getComments(), rs.getString("COMMENTS"));
assertEquals("customer id", (int)complaint.getCustomerId(), rs.getInt("CUSTOMER_ID"));
assertFalse("single row",rs.next());
return true;
}
}));
}
@Test
public void updateFields() {
final ComplaintTable compl = new ComplaintTable("complaint");
final Date date = new Date();
squill
.update(compl)
.where(eq(compl.id, 1))
.set(
updateElement(compl.resolvedDate, date),
updateElement(compl.refoundSum,
add(compl.percentSolved, DELTA)));
assertTrue("row found", database.execute("select resolved_date, refound_sum, percent_solved from complaint where id = ?", Collections.singletonList(1),
new StatementCallback<Boolean>() {
public Boolean execute(final PreparedStatement stmt) throws Exception {
final ResultSet rs = stmt.executeQuery();
assertTrue("single row", rs.next());
assertEquals("date", DAY_FORMAT.format(date), DAY_FORMAT.format(rs.getDate("RESOLVED_DATE")));
assertEquals("refound sum = percent + 20", rs.getInt("REFOUND_SUM") - DELTA, rs.getInt("PERCENT_SOLVED"));
assertFalse("single row", rs.next());
return true;
}
}));
}
@Test public void delete() {
assertEquals("rows update", 1,(int)database.execute("insert into complaint (id,customer_id) values(?,?)", asList(732,1), new UpdateStatementCallback<Integer>()));
final ComplaintTable compl = new ComplaintTable("complaint");
assertEquals(" one row deleted",1,
squill
.delete(compl)
.where(ge(compl.id, 732)));
}
@Before
public void setUp() throws Exception {
database = TestUtil.getDefaultHsqlDatabase();
squill = new Squill(database);
}
@After
public void tearDown() {
TestUtil.shutDownHsql();
}
}