use of org.hibernate.dialect.PostgreSQL81Dialect in project hibernate-orm by hibernate.
the class ASTParserLoadingTest method testEJBQLFunctions.
@Test
public void testEJBQLFunctions() throws Exception {
Session session = openSession();
Transaction t = session.beginTransaction();
String hql = "from Animal a where a.description = concat('1', concat('2','3'), '4'||'5')||'0'";
session.createQuery(hql).list();
hql = "from Animal a where substring(a.description, 1, 3) = 'cat'";
session.createQuery(hql).list();
hql = "select substring(a.description, 1, 3) from Animal a";
session.createQuery(hql).list();
hql = "from Animal a where lower(a.description) = 'cat'";
session.createQuery(hql).list();
hql = "select lower(a.description) from Animal a";
session.createQuery(hql).list();
hql = "from Animal a where upper(a.description) = 'CAT'";
session.createQuery(hql).list();
hql = "select upper(a.description) from Animal a";
session.createQuery(hql).list();
hql = "from Animal a where length(a.description) = 5";
session.createQuery(hql).list();
hql = "select length(a.description) from Animal a";
session.createQuery(hql).list();
//note: postgres and db2 don't have a 3-arg form, it gets transformed to 2-args
hql = "from Animal a where locate('abc', a.description, 2) = 2";
session.createQuery(hql).list();
hql = "from Animal a where locate('abc', a.description) = 2";
session.createQuery(hql).list();
hql = "select locate('cat', a.description, 2) from Animal a";
session.createQuery(hql).list();
if (!(getDialect() instanceof DB2Dialect)) {
hql = "from Animal a where trim(trailing '_' from a.description) = 'cat'";
session.createQuery(hql).list();
hql = "select trim(trailing '_' from a.description) from Animal a";
session.createQuery(hql).list();
hql = "from Animal a where trim(leading '_' from a.description) = 'cat'";
session.createQuery(hql).list();
hql = "from Animal a where trim(both from a.description) = 'cat'";
session.createQuery(hql).list();
}
if (!(getDialect() instanceof HSQLDialect)) {
//HSQL doesn't like trim() without specification
hql = "from Animal a where trim(a.description) = 'cat'";
session.createQuery(hql).list();
}
hql = "from Animal a where abs(a.bodyWeight) = sqrt(a.bodyWeight)";
session.createQuery(hql).list();
hql = "from Animal a where mod(16, 4) = 4";
session.createQuery(hql).list();
/**
* PostgreSQL >= 8.3.7 typecasts are no longer automatically allowed
* <link>http://www.postgresql.org/docs/current/static/release-8-3.html</link>
*/
if (getDialect() instanceof PostgreSQLDialect || getDialect() instanceof PostgreSQL81Dialect || getDialect() instanceof HSQLDialect) {
hql = "from Animal a where bit_length(str(a.bodyWeight)) = 24";
} else {
hql = "from Animal a where bit_length(a.bodyWeight) = 24";
}
session.createQuery(hql).list();
if (getDialect() instanceof PostgreSQLDialect || getDialect() instanceof PostgreSQL81Dialect || getDialect() instanceof HSQLDialect) {
hql = "select bit_length(str(a.bodyWeight)) from Animal a";
} else {
hql = "select bit_length(a.bodyWeight) from Animal a";
}
session.createQuery(hql).list();
/*hql = "select object(a) from Animal a where CURRENT_DATE = :p1 or CURRENT_TIME = :p2 or CURRENT_TIMESTAMP = :p3";
session.createQuery(hql).list();*/
// todo the following is not supported
//hql = "select CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP from Animal a";
//parse(hql, true);
//System.out.println("sql: " + toSql(hql));
hql = "from Animal a where a.description like '%a%'";
session.createQuery(hql).list();
hql = "from Animal a where a.description not like '%a%'";
session.createQuery(hql).list();
hql = "from Animal a where a.description like 'x%ax%' escape 'x'";
session.createQuery(hql).list();
t.commit();
session.close();
}
Aggregations