@Test public void testQueryBuilder() throws Exception { Calendar cal = new GregorianCalendar(2010, 0, 1, 12, 34, 56); try (Session session = getSession()) { QueryBuilder queryBuilder = new QueryBuilder().predicate(Predicates.not(Predicates.or(Predicates.eq("username", "user_1"), Predicates.eq("username", "Administrator")))) .and(Predicates.gt("intField", Long.valueOf(123))) .and(Predicates.noteq("booleanField", Long.valueOf(1))) .and(Predicates.between("intField", Long.valueOf(123), Long.valueOf(456))) .and(Predicates.like("firstName", "jo%n")) .and(Predicates.notlike("firstName", "bob")) .and(Predicates.ilike("lastName", "smith")) .and(Predicates.gte("dateField", cal)) .and(Predicates.lt("doubleField", Double.valueOf(3.14))) .and(Predicates.in("company", "c1", "c2")) .and(Predicates.notin("company", "c3", "c4")) .and(Predicates.isnull("username")); SQLQueryBuilder builder = new SQLQueryBuilder(getDirectory()); builder.visitMultiExpression(queryBuilder.predicate()); String expected; StorageConfiguration storageConfiguration = coreFeature.getStorageConfiguration(); if (storageConfiguration.isVCSH2()) { expected = "((NOT ((\"username\" = ?) OR (\"username\" = ?)))" + " AND (\"intField\" > ?)" + " AND (\"booleanField\" <> ?)" + " AND (\"intField\" BETWEEN ? AND ?)" + " AND (\"firstName\" LIKE ?)" + " AND (\"firstName\" NOT LIKE ?)" + " AND (LOWER(\"lastName\") LIKE LOWER(?))" + " AND (\"dateField\" >= ?)" + " AND (\"doubleField\" < ?)" + " AND (\"company\" IN (?, ?))" + " AND (\"company\" NOT IN (?, ?))" + " AND (\"username\" IS NULL ))"; } else if (storageConfiguration.isVCSPostgreSQL()) { expected = "((NOT ((\"username\" = ?) OR (\"username\" = ?)))" + " AND (\"intField\" > ?)" + " AND (\"booleanField\" <> ?)" + " AND (\"intField\" BETWEEN ? AND ?)" + " AND (\"firstName\" LIKE ?)" + " AND (\"firstName\" NOT LIKE ?)" + " AND (\"lastName\" ILIKE ?)" + " AND (\"dateField\" >= ?)" + " AND (\"doubleField\" < ?)" + " AND (\"company\" IN (?, ?))" + " AND (\"company\" NOT IN (?, ?))" + " AND (\"username\" IS NULL ))"; } else if (storageConfiguration.isVCSMySQL()) { expected = "((NOT ((`username` = ?) OR (`username` = ?)))" + " AND (`intField` > ?)" + " AND (`booleanField` <> ?)" + " AND (`intField` BETWEEN ? AND ?)" + " AND (`firstName` LIKE ?)" + " AND (`firstName` NOT LIKE ?)" + " AND (LOWER(`lastName`) LIKE LOWER(?))" + " AND (`dateField` >= ?)" + " AND (`doubleField` < ?)" + " AND (`company` IN (?, ?))" + " AND (`company` NOT IN (?, ?))" + " AND (`username` IS NULL ))"; } else if (storageConfiguration.isVCSOracle()) { expected = "((NOT ((\"username\" = ?) OR (\"username\" = ?)))" + " AND (\"intField\" > ?)" + " AND (\"booleanField\" <> ?)" + " AND (\"intField\" BETWEEN ? AND ?)" + " AND (\"firstName\" LIKE ? ESCAPE '\\')" + " AND (\"firstName\" NOT LIKE ? ESCAPE '\\')" + " AND (LOWER(\"lastName\") LIKE LOWER(?) ESCAPE '\\')" + " AND (\"dateField\" >= ?)" + " AND (\"doubleField\" < ?)" + " AND (\"company\" IN (?, ?))" + " AND (\"company\" NOT IN (?, ?))" + " AND (\"username\" IS NULL ))"; } else if (storageConfiguration.isVCSSQLServer()) { expected = "((NOT (([username] = ?) OR ([username] = ?)))" + " AND ([intField] > ?)" + " AND ([booleanField] <> ?)" + " AND ([intField] BETWEEN ? AND ?)" + " AND ([firstName] LIKE ? ESCAPE '\\')" + " AND ([firstName] NOT LIKE ? ESCAPE '\\')" + " AND (LOWER([lastName]) LIKE LOWER(?) ESCAPE '\\')" + " AND ([dateField] >= ?)" + " AND ([doubleField] < ?)" + " AND ([company] IN (?, ?))" + " AND ([company] NOT IN (?, ?))" + " AND ([username] IS NULL ))"; } else { expected = "Unknown VCS backend: " + DatabaseHelper.DATABASE.getClass().getSimpleName(); } assertEquals(expected, builder.clause.toString()); assertEqualsNormalized(Arrays.asList( "user_1", "Administrator", Long.valueOf(123), Boolean.TRUE, Long.valueOf(123), Long.valueOf(456), "jo%n", "bob", "smith", cal, Double.valueOf(3.14), "c1", "c2", "c3", "c4" ), builder.params.stream().map(cv -> cv.value).collect(toList())); } }
@Override public void visitMultiExpression(MultiExpression node) { if (node.predicates.isEmpty()) { return; } clause.append('('); for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) { it.next().accept(this); if (it.hasNext()) { node.operator.accept(this); } } clause.append(')'); }
SQLQueryBuilder extends DefaultQueryVisitor { @Override public void visitMultiExpression(MultiExpression node) { if (node.predicates.isEmpty()) { return; } clause.append('('); for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) { it.next().accept(this); if (it.hasNext()) { node.operator.accept(this); } } clause.append(')'); } }
SQLQueryBuilder extends DefaultQueryVisitor { @Override public void visitMultiExpression(MultiExpression node) { if (node.predicates.isEmpty()) { return; } clause.append('('); for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) { it.next().accept(this); if (it.hasNext()) { node.operator.accept(this); } } clause.append(')'); } SQLQueryBuilder(SQLDirectory directory); }
SQLQueryBuilder extends DefaultQueryVisitor { @Override public void visitMultiExpression(MultiExpression node) { if (node.predicates.isEmpty()) { return; } clause.append('('); for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) { it.next().accept(this); if (it.hasNext()) { node.operator.accept(this); } } clause.append(')'); } SQLQueryBuilder(SQLDirectory directory); @Override void visitMultiExpression(MultiExpression node); @Override void visitExpression(Expression node); @Override void visitOperator(Operator node); @Override void visitReference(Reference node); @Override void visitLiteralList(LiteralList node); @Override void visitDateLiteral(DateLiteral node); @Override void visitStringLiteral(StringLiteral node); @Override void visitDoubleLiteral(DoubleLiteral node); @Override void visitIntegerLiteral(IntegerLiteral node); @Override void visitBooleanLiteral(BooleanLiteral node); @Override void visitFunction(Function node); }
SQLQueryBuilder extends DefaultQueryVisitor { @Override public void visitMultiExpression(MultiExpression node) { if (node.predicates.isEmpty()) { return; } clause.append('('); for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) { it.next().accept(this); if (it.hasNext()) { node.operator.accept(this); } } clause.append(')'); } SQLQueryBuilder(SQLDirectory directory); @Override void visitMultiExpression(MultiExpression node); @Override void visitExpression(Expression node); @Override void visitOperator(Operator node); @Override void visitReference(Reference node); @Override void visitLiteralList(LiteralList node); @Override void visitDateLiteral(DateLiteral node); @Override void visitStringLiteral(StringLiteral node); @Override void visitDoubleLiteral(DoubleLiteral node); @Override void visitIntegerLiteral(IntegerLiteral node); @Override void visitBooleanLiteral(BooleanLiteral node); @Override void visitFunction(Function node); final StringBuilder clause; final List<ColumnAndValue> params; }