Examples with PostgreSQLDialect org.hibernate.dialect.PostgreSQLDialect used on opensource projects

Example 6 with PostgreSQLDialect

use of org.hibernate.dialect.PostgreSQLDialect 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();
}