@Before
public void setup() {
db = IciqlSuite.openNewDb();
db.insertAll(UserId.getList());
db.insertAll(UserNote.getList());
}
@Test
public void testRuntimeSet() {
Db db = IciqlSuite.openNewDb();
// do not test non-H2 databases because dialects will get in the way
// e.g. column quoting, etc
Assume.assumeTrue(IciqlSuite.isH2(db));
StaticQueries.StaticModel1 m = new StaticQueries.StaticModel1();
String q = db.from(m).set(m.myTimestamp).toParameter().where(m.id).isParameter().toSQL();
db.close();
assertEquals("UPDATE StaticQueryTest1 SET myTimestamp = ? WHERE id = ?", q);
}
@Test
public void testRuntimeQuery() {
Db db = IciqlSuite.openNewDb();
db.insertAll(Product.getList());
Product p = new Product();
List<Product> products = db.from(p).where("unitsInStock=?", 120).orderBy(p.productId).select();
assertEquals(1, products.size());
products =
db.from(p)
.where("unitsInStock=? and productName like ? order by productId", 0, "Chef%")
.select();
assertEquals(1, products.size());
db.close();
}
@Test
public void testExecuteQuery() throws SQLException {
Db db = IciqlSuite.openNewDb();
db.insertAll(Product.getList());
// test plain statement
List<Product> products =
db.executeQuery(Product.class, "select * from product where unitsInStock=120");
assertEquals(1, products.size());
assertEquals("Condiments", products.get(0).category);
// test prepared statement
products = db.executeQuery(Product.class, "select * from product where unitsInStock=?", 120);
assertEquals(1, products.size());
assertEquals("Condiments", products.get(0).category);
db.close();
}
@Test
public void testJoin() throws Exception {
final UserId u = new UserId();
final UserNote n = new UserNote();
// this query returns 1 UserId if the user has a note
// it's purpose is to confirm fluency/type-safety on a very simple
// join case where the main table is filtered/reduced by hits in a
// related table
List<UserId> users =
db.from(u).innerJoin(n).on(u.id).is(n.userId).where(u.id).is(2).selectDistinct();
assertEquals(1, users.size());
assertEquals(2, users.get(0).id);
}
@Test
public void testPrimitiveJoin() throws Exception {
final UserId u = new UserId();
final UserNote n = new UserNote();
List<UserNote> notes =
db.from(u)
.innerJoin(n)
.on(u.id)
.is(n.userId)
.where(u.id)
.is(2)
.select(
new UserNote() {
{
userId = n.userId;
noteId = n.noteId;
text = n.text;
}
});
assertEquals(3, notes.size());
}
@Test
public void testBuildObjects() throws SQLException {
Db db = IciqlSuite.openNewDb();
db.insertAll(Product.getList());
// test plain statement
ResultSet rs = db.executeQuery("select * from product where unitsInStock=120");
List<Product> products = db.buildObjects(Product.class, rs);
JdbcUtils.closeSilently(rs, true);
assertEquals(1, products.size());
assertEquals("Condiments", products.get(0).category);
// test prepared statement
rs = db.executeQuery("select * from product where unitsInStock=?", 120);
products = db.buildObjects(Product.class, rs);
JdbcUtils.closeSilently(rs, true);
assertEquals(1, products.size());
assertEquals("Condiments", products.get(0).category);
db.close();
}
@Test
public void testParameters() {
Db db = IciqlSuite.openNewDb();
// do not test non-H2 databases because dialects will get in the way
// e.g. column quoting, etc
Assume.assumeTrue(IciqlSuite.isH2(db));
Product p = new Product();
String q1 =
db.from(p)
.where(p.unitsInStock)
.isParameter()
.and(p.productName)
.likeParameter()
.orderBy(p.productId)
.toSQL();
String q2 =
db.from(p)
.where(p.unitsInStock)
.lessThan(100)
.and(p.productName)
.like("test")
.or(p.productName)
.likeParameter()
.orderBy(p.productId)
.toSQL();
StaticQueries.StaticModel1 m1 = new StaticQueries.StaticModel1();
String q3 = db.from(m1).where(m1.myTree).is(Tree.MAPLE).and(m1.myTree).isParameter().toSQL();
StaticQueries.StaticModel2 m2 = new StaticQueries.StaticModel2();
String q4 = db.from(m2).where(m2.myTree).is(Tree.MAPLE).and(m2.myTree).isParameter().toSQL();
StaticQueries.StaticModel3 m3 = new StaticQueries.StaticModel3();
String q5 = db.from(m3).where(m3.myTree).is(Tree.MAPLE).and(m3.myTree).isParameter().toSQL();
long now = System.currentTimeMillis();
java.sql.Date aDate = new java.sql.Date(now);
java.sql.Time aTime = new java.sql.Time(now);
java.sql.Timestamp aTimestamp = new java.sql.Timestamp(now);
String q6 = db.from(m1).where(m1.myDate).is(aDate).and(m1.myDate).isParameter().toSQL();
String q7 = db.from(m1).where(m1.myTime).is(aTime).and(m1.myTime).isParameter().toSQL();
String q8 =
db.from(m1).where(m1.myTimestamp).is(aTimestamp).and(m1.myTimestamp).isParameter().toSQL();
db.close();
assertEquals(
"SELECT * FROM Product WHERE unitsInStock = ? AND productName LIKE ? ORDER BY productId",
q1);
assertEquals(
"SELECT * FROM Product WHERE unitsInStock < 100 AND productName LIKE 'test' OR productName LIKE ? ORDER BY productId",
q2);
assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTree = 'MAPLE' AND myTree = ?", q3);
assertEquals("SELECT * FROM StaticQueryTest2 WHERE myTree = 50 AND myTree = ?", q4);
assertEquals("SELECT * FROM StaticQueryTest3 WHERE myTree = 4 AND myTree = ?", q5);
java.util.Date refDate = new java.util.Date(now);
assertEquals(
"SELECT * FROM StaticQueryTest1 WHERE myDate = '"
+ new SimpleDateFormat("yyyy-MM-dd").format(refDate)
+ "' AND myDate = ?",
q6);
assertEquals(
"SELECT * FROM StaticQueryTest1 WHERE myTime = '"
+ new SimpleDateFormat("HH:mm:ss").format(refDate)
+ "' AND myTime = ?",
q7);
assertEquals(
"SELECT * FROM StaticQueryTest1 WHERE myTimestamp = '"
+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(refDate)
+ "' AND myTimestamp = ?",
q8);
}
@Test
public void testRuntimeSelectWildcards() {
Db db = IciqlSuite.openNewDb();
// do not test non-H2 databases because dialects will get in the way
// e.g. column quoting, etc
Assume.assumeTrue(IciqlSuite.isH2(db));
StaticQueries.StaticModel1 m1 = new StaticQueries.StaticModel1();
StaticQueries.StaticModel2 m2 = new StaticQueries.StaticModel2();
StaticQueries.StaticModel2 m3 = new StaticQueries.StaticModel2();
int t0 = Utils.AS_COUNTER.get() + 1;
int t1 = t0 + 1;
QueryWhere<?> where =
db.from(m1).innerJoin(m2).on(m1.id).is(m2.id).where(m2.myTree).is(Tree.MAPLE);
String q1 = where.toSQL(false);
String q2 = where.toSQL(true);
String q3 = where.toSQL(false, m1);
String q4 = where.toSQL(true, m1);
String q5 = where.toSQL(false, m2);
String q6 = where.toSQL(true, m2);
// test unused alias
String q7 = where.toSQL(true, m3);
db.close();
assertEquals(
MessageFormat.format(
"SELECT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q1);
assertEquals(
MessageFormat.format(
"SELECT DISTINCT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q2);
assertEquals(
MessageFormat.format(
"SELECT T{0,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q3);
assertEquals(
MessageFormat.format(
"SELECT DISTINCT T{0,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q4);
assertEquals(
MessageFormat.format(
"SELECT T{1,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q5);
assertEquals(
MessageFormat.format(
"SELECT DISTINCT T{1,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q6);
assertEquals(
MessageFormat.format(
"SELECT DISTINCT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50",
t0,
t1),
q7);
}
@After
public void tearDown() {
db.close();
}