Search in sources :

Example 11 with PostgreSQL81Dialect

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();
}
Also used : HSQLDialect(org.hibernate.dialect.HSQLDialect) PostgreSQLDialect(org.hibernate.dialect.PostgreSQLDialect) Transaction(org.hibernate.Transaction) DB2Dialect(org.hibernate.dialect.DB2Dialect) Session(org.hibernate.Session) PostgreSQL81Dialect(org.hibernate.dialect.PostgreSQL81Dialect) Test(org.junit.Test)

Aggregations

PostgreSQL81Dialect (org.hibernate.dialect.PostgreSQL81Dialect)11 Test (org.junit.Test)9 PostgreSQLDialect (org.hibernate.dialect.PostgreSQLDialect)6 Session (org.hibernate.Session)5 Transaction (org.hibernate.Transaction)4 Metadata (org.hibernate.boot.Metadata)4 MetadataSources (org.hibernate.boot.MetadataSources)4 StandardServiceRegistry (org.hibernate.boot.registry.StandardServiceRegistry)4 StandardServiceRegistryBuilder (org.hibernate.boot.registry.StandardServiceRegistryBuilder)4 DB2Dialect (org.hibernate.dialect.DB2Dialect)4 HSQLDialect (org.hibernate.dialect.HSQLDialect)4 MySQLDialect (org.hibernate.dialect.MySQLDialect)4 List (java.util.List)3 PersistentClass (org.hibernate.mapping.PersistentClass)3 TestForIssue (org.hibernate.testing.TestForIssue)3 AbstractHANADialect (org.hibernate.dialect.AbstractHANADialect)2 Oracle8iDialect (org.hibernate.dialect.Oracle8iDialect)2 Property (org.hibernate.mapping.Property)2 ArrayList (java.util.ArrayList)1 Iterator (java.util.Iterator)1