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

Example 11 with HSQLDialect

use of org.hibernate.dialect.HSQLDialect in project hibernate-orm by hibernate.

the class SQLFunctionsTest method testSQLFunctions.

@Test
public void testSQLFunctions() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Simple simple = new Simple(Long.valueOf(10));
    simple.setName("Simple 1");
    s.save(simple);
    if (getDialect() instanceof DB2Dialect && !(getDialect() instanceof DerbyDialect)) {
        s.createQuery("from Simple s where repeat('foo', 3) = 'foofoofoo'").list();
        s.createQuery("from Simple s where repeat(s.name, 3) = 'foofoofoo'").list();
        s.createQuery("from Simple s where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'").list();
    }
    assertTrue(s.createQuery("from Simple s where upper( s.name ) ='SIMPLE 1'").list().size() == 1);
    if (!(getDialect() instanceof HSQLDialect)) {
        assertTrue(s.createQuery("from Simple s where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )").list().size() == 1);
    }
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof SQLServerDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof InterbaseDialect) && !(getDialect() instanceof TimesTenDialect)) {
        //My SQL has a funny concatenation operator
        assertTrue(s.createQuery("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").list().size() == 1);
    }
    if ((getDialect() instanceof SybaseDialect)) {
        assertTrue(s.createQuery("from Simple s where lower( s.name + ' foo' ) ='simple 1 foo'").list().size() == 1);
    }
    if ((getDialect() instanceof MckoiDialect) || (getDialect() instanceof TimesTenDialect)) {
        assertTrue(s.createQuery("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").list().size() == 1);
    }
    Simple other = new Simple(Long.valueOf(20));
    other.setName("Simple 2");
    other.setCount(12);
    simple.setOther(other);
    s.save(other);
    //s.find("from Simple s where s.name ## 'cat|rat|bag'");
    assertTrue(s.createQuery("from Simple s where upper( s.other.name ) ='SIMPLE 2'").list().size() == 1);
    assertTrue(s.createQuery("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").list().size() == 0);
    assertTrue(s.createQuery("select distinct s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2").list().size() == 1);
    assertTrue(s.createQuery("select s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count").list().size() == 1);
    Simple min = new Simple(Long.valueOf(30));
    min.setCount(-1);
    s.save(min);
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof HSQLDialect)) {
        //My SQL has no subqueries
        assertTrue(s.createQuery("from Simple s where s.count > ( select min(sim.count) from Simple sim )").list().size() == 2);
        t.commit();
        t = s.beginTransaction();
        assertTrue(s.createQuery("from Simple s where s = some( select sim from Simple sim where sim.count>=0 ) and s.count >= 0").list().size() == 2);
        assertTrue(s.createQuery("from Simple s where s = some( select sim from Simple sim where sim.other.count=s.other.count ) and s.other.count > 0").list().size() == 1);
    }
    Iterator iter = s.createQuery("select sum(s.count) from Simple s group by s.count having sum(s.count) > 10").iterate();
    assertTrue(iter.hasNext());
    assertEquals(Long.valueOf(12), iter.next());
    assertTrue(!iter.hasNext());
    if (!(getDialect() instanceof MySQLDialect)) {
        iter = s.createQuery("select s.count from Simple s group by s.count having s.count = 12").iterate();
        assertTrue(iter.hasNext());
    }
    s.createQuery("select s.id, s.count, count(t), max(t.date) from Simple s, Simple t where s.count = t.count group by s.id, s.count order by s.count").iterate();
    Query q = s.createQuery("from Simple s");
    q.setMaxResults(10);
    assertTrue(q.list().size() == 3);
    q = s.createQuery("from Simple s");
    q.setMaxResults(1);
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s");
    assertTrue(q.list().size() == 3);
    q = s.createQuery("from Simple s where s.name = ?");
    q.setString(0, "Simple 1");
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s where s.name = ? and upper(s.name) = ?");
    q.setString(1, "SIMPLE 1");
    q.setString(0, "Simple 1");
    q.setFirstResult(0);
    assertTrue(q.iterate().hasNext());
    q = s.createQuery("from Simple s where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
    q.setParameter("bar", "SIMPLE 1");
    q.setString("foo", "Simple 1");
    q.setInteger("count", 69);
    q.setFirstResult(0);
    assertTrue(q.iterate().hasNext());
    q = s.createQuery("select s.id from Simple s");
    q.setFirstResult(1);
    q.setMaxResults(2);
    iter = q.iterate();
    int i = 0;
    while (iter.hasNext()) {
        assertTrue(iter.next() instanceof Long);
        i++;
    }
    assertTrue(i == 2);
    q = s.createQuery("select all s, s.other from Simple s where s = :s");
    q.setParameter("s", simple);
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s where s.name in (:name_list) and s.count > :count");
    HashSet set = new HashSet();
    set.add("Simple 1");
    set.add("foo");
    q.setParameterList("name_list", set);
    q.setParameter("count", Integer.valueOf(-1));
    assertTrue(q.list().size() == 1);
    ScrollableResults sr = s.createQuery("from Simple s").scroll();
    sr.next();
    sr.get(0);
    sr.close();
    s.delete(other);
    s.delete(simple);
    s.delete(min);
    t.commit();
    s.close();
}

Example 12 with HSQLDialect

use of org.hibernate.dialect.HSQLDialect in project hibernate-orm by hibernate.

the class MasterDetailTest method testExample.

@Test
public void testExample() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Master m = new Master();
    m.setName("name");
    m.setX(5);
    m.setOtherMaster(m);
    s.save(m);
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    Master m1 = (Master) s.createCriteria(Master.class).add(Example.create(m).enableLike().ignoreCase().excludeProperty("bigDecimal")).uniqueResult();
    assertTrue(m1.getOtherMaster() == m1);
    m1 = (Master) s.createCriteria(Master.class).add(Restrictions.eq("name", "foobar")).uniqueResult();
    assertTrue(m1 == null);
    m1 = (Master) s.createCriteria(Master.class).add(Example.create(m).excludeProperty("bigDecimal")).createCriteria("otherMaster").add(Example.create(m).excludeZeroes().excludeProperty("bigDecimal")).uniqueResult();
    assertTrue(m1.getOtherMaster() == m1);
    Master m2 = (Master) s.createCriteria(Master.class).add(Example.create(m).excludeNone().excludeProperty("bigDecimal")).uniqueResult();
    assertTrue(m2 == m1);
    m.setName(null);
    m2 = (Master) s.createCriteria(Master.class).add(Example.create(m).excludeNone().excludeProperty("bigDecimal")).uniqueResult();
    assertTrue(null == m2);
    if (getDialect() instanceof HSQLDialect || getDialect() instanceof MySQLDialect) {
        m1.setOtherMaster(null);
        s.flush();
    }
    s.delete(m1);
    t.commit();
    s.close();
}

Example 13 with HSQLDialect

use of org.hibernate.dialect.HSQLDialect in project hibernate-orm by hibernate.

the class MasterDetailTest method testMasterDetail.

@Test
public void testMasterDetail() throws Exception {
    if (getDialect() instanceof HSQLDialect)
        return;
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Master master = new Master();
    assertTrue("save returned native id", s.save(master) != null);
    Serializable mid = s.getIdentifier(master);
    Detail d1 = new Detail();
    d1.setMaster(master);
    Serializable did = s.save(d1);
    Detail d2 = new Detail();
    d2.setI(12);
    d2.setMaster(master);
    assertTrue("generated id returned", s.save(d2) != null);
    master.addDetail(d1);
    master.addDetail(d2);
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SAPDBDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof org.hibernate.dialect.TimesTenDialect)) {
        assertTrue("query", s.createQuery("from Detail d, Master m where m = d.master and size(m.outgoing) = 0 and size(m.incoming) = 0").list().size() == 2);
    }
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    master = new Master();
    s.load(master, mid);
    assertTrue(master.getDetails().size() == 2);
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    master = (Master) s.load(Master.class, mid);
    Iterator iter = master.getDetails().iterator();
    int i = 0;
    while (iter.hasNext()) {
        Detail d = (Detail) iter.next();
        assertTrue("master-detail", d.getMaster() == master);
        i++;
    }
    assertTrue("master-detail", i == 2);
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    assertTrue(s.createQuery("select elements(master.details) from Master master").list().size() == 2);
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    List list = s.createQuery("from Master m left join fetch m.details").list();
    Master m = (Master) list.get(0);
    assertTrue(Hibernate.isInitialized(m.getDetails()));
    assertTrue(m.getDetails().size() == 2);
    list = s.createQuery("from Detail d inner join fetch d.master").list();
    Detail dt = (Detail) list.get(0);
    Serializable dtid = s.getIdentifier(dt);
    assertTrue(dt.getMaster() == m);
    //assertTrue(m.getAllDetails().size()==2);
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    list = s.createQuery("select m from Master m1, Master m left join fetch m.details where m.name=m1.name").list();
    assertTrue(Hibernate.isInitialized(((Master) list.get(0)).getDetails()));
    dt = (Detail) s.load(Detail.class, dtid);
    assertTrue(((Master) list.get(0)).getDetails().contains(dt));
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    list = s.createQuery("select m, m1.name from Master m1, Master m left join fetch m.details where m.name=m1.name").list();
    assertTrue(Hibernate.isInitialized(((Master) ((Object[]) list.get(0))[0]).getDetails()));
    dt = (Detail) s.load(Detail.class, dtid);
    assertTrue(((Master) ((Object[]) list.get(0))[0]).getDetails().contains(dt));
    //list = s.find("select m from Master m, Master m2 left join fetch m.details");
    // depracted syntax
    //		list = s.find("select m.id from Master m inner join fetch m.details");
    t.commit();
    s.close();
    s = openSession();
    t = s.beginTransaction();
    Detail dd = (Detail) s.load(Detail.class, did);
    master = dd.getMaster();
    assertTrue("detail-master", master.getDetails().contains(dd));
    assertTrue(s.createFilter(master.getDetails(), "order by this.i desc").list().size() == 2);
    assertTrue(s.createFilter(master.getDetails(), "select this where this.id > -1").list().size() == 2);
    Query q = s.createFilter(master.getDetails(), "where this.id > :id");
    q.setInteger("id", -1);
    assertTrue(q.list().size() == 2);
    q = s.createFilter(master.getDetails(), "where this.id > :id1 and this.id < :id2");
    q.setInteger("id1", -1);
    q.setInteger("id2", 99999999);
    assertTrue(q.list().size() == 2);
    q.setInteger("id2", -1);
    assertTrue(q.list().size() == 0);
    q = s.createFilter(master.getDetails(), "where this.id in (:ids)");
    list = new ArrayList();
    list.add(did);
    list.add(new Long(-1));
    q.setParameterList("ids", list);
    assertTrue(q.list().size() == 1);
    assertTrue(q.iterate().hasNext());
    assertTrue(s.createFilter(master.getDetails(), "where this.id > -1").list().size() == 2);
    assertTrue(s.createFilter(master.getDetails(), "select this.master where this.id > -1").list().size() == 2);
    assertTrue(s.createFilter(master.getDetails(), "select m from Master m where this.id > -1 and this.master=m").list().size() == 2);
    assertTrue(s.createFilter(master.getIncoming(), "where this.id > -1 and this.name is not null").list().size() == 0);
    assertTrue(s.createFilter(master.getDetails(), "select max(this.i)").iterate().next() instanceof Integer);
    assertTrue(s.createFilter(master.getDetails(), "select max(this.i) group by this.id").iterate().next() instanceof Integer);
    assertTrue(s.createFilter(master.getDetails(), "select count(*)").iterate().next() instanceof Long);
    assertTrue(s.createFilter(master.getDetails(), "select this.master").list().size() == 2);
    assertTrue(s.createFilter(master.getMoreDetails(), "").list().size() == 0);
    assertTrue(s.createFilter(master.getIncoming(), "").list().size() == 0);
    Query f = s.createFilter(master.getDetails(), "select max(this.i) where this.i < :top and this.i>=:bottom");
    f.setInteger("top", 100);
    f.setInteger("bottom", 0);
    assertEquals(f.iterate().next(), new Integer(12));
    f.setInteger("top", 2);
    assertEquals(f.iterate().next(), new Integer(0));
    f = s.createFilter(master.getDetails(), "select max(this.i) where this.i not in (:list)");
    Collection coll = new ArrayList();
    coll.add(new Integer(-666));
    coll.add(new Integer(22));
    coll.add(new Integer(0));
    f.setParameterList("list", coll);
    assertEquals(f.iterate().next(), new Integer(12));
    f = s.createFilter(master.getDetails(), "select max(this.i) where this.i not in (:list) and this.master.name = :listy2");
    f.setParameterList("list", coll);
    f.setParameter("listy2", master.getName());
    assertEquals(f.iterate().next(), new Integer(12));
    iter = master.getDetails().iterator();
    i = 0;
    while (iter.hasNext()) {
        Detail d = (Detail) iter.next();
        assertTrue("master-detail", d.getMaster() == master);
        s.delete(d);
        i++;
    }
    assertTrue("master-detail", i == 2);
    s.delete(master);
    t.commit();
    s.close();
}

Example 14 with HSQLDialect

use of org.hibernate.dialect.HSQLDialect in project hibernate-orm by hibernate.

the class StandardDialectResolver method resolveDialect.

@Override
public Dialect resolveDialect(DialectResolutionInfo info) {
    final String databaseName = info.getDatabaseName();
    if ("CUBRID".equalsIgnoreCase(databaseName)) {
        return new CUBRIDDialect();
    }
    if ("HSQL Database Engine".equals(databaseName)) {
        return new HSQLDialect();
    }
    if ("H2".equals(databaseName)) {
        return new H2Dialect();
    }
    if ("MySQL".equals(databaseName)) {
        final int majorVersion = info.getDatabaseMajorVersion();
        if (majorVersion >= 5) {
            return new MySQL5Dialect();
        }
        return new MySQLDialect();
    }
    if ("PostgreSQL".equals(databaseName)) {
        final int majorVersion = info.getDatabaseMajorVersion();
        final int minorVersion = info.getDatabaseMinorVersion();
        if (majorVersion == 9) {
            if (minorVersion >= 4) {
                return new PostgreSQL94Dialect();
            } else if (minorVersion >= 2) {
                return new PostgreSQL92Dialect();
            }
            return new PostgreSQL9Dialect();
        }
        if (majorVersion == 8 && minorVersion >= 2) {
            return new PostgreSQL82Dialect();
        }
        return new PostgreSQL81Dialect();
    }
    if ("EnterpriseDB".equals(databaseName)) {
        return new PostgresPlusDialect();
    }
    if ("Apache Derby".equals(databaseName)) {
        final int majorVersion = info.getDatabaseMajorVersion();
        final int minorVersion = info.getDatabaseMinorVersion();
        if (majorVersion > 10 || (majorVersion == 10 && minorVersion >= 7)) {
            return new DerbyTenSevenDialect();
        } else if (majorVersion == 10 && minorVersion == 6) {
            return new DerbyTenSixDialect();
        } else if (majorVersion == 10 && minorVersion == 5) {
            return new DerbyTenFiveDialect();
        } else {
            return new DerbyDialect();
        }
    }
    if ("ingres".equalsIgnoreCase(databaseName)) {
        final int majorVersion = info.getDatabaseMajorVersion();
        final int minorVersion = info.getDatabaseMinorVersion();
        switch(majorVersion) {
            case 9:
                if (minorVersion > 2) {
                    return new Ingres9Dialect();
                }
                return new IngresDialect();
            case 10:
                return new Ingres10Dialect();
            default:
                LOG.unknownIngresVersion(majorVersion);
        }
        return new IngresDialect();
    }
    if (databaseName.startsWith("Microsoft SQL Server")) {
        final int majorVersion = info.getDatabaseMajorVersion();
        switch(majorVersion) {
            case 8:
                {
                    return new SQLServerDialect();
                }
            case 9:
                {
                    return new SQLServer2005Dialect();
                }
            case 10:
                {
                    return new SQLServer2008Dialect();
                }
            case 11:
            case 12:
            case 13:
                {
                    return new SQLServer2012Dialect();
                }
            default:
                {
                    if (majorVersion < 8) {
                        LOG.unknownSqlServerVersion(majorVersion, SQLServerDialect.class);
                        return new SQLServerDialect();
                    } else {
                        // assume `majorVersion > 13`
                        LOG.unknownSqlServerVersion(majorVersion, SQLServer2012Dialect.class);
                        return new SQLServer2012Dialect();
                    }
                }
        }
    }
    if ("Sybase SQL Server".equals(databaseName) || "Adaptive Server Enterprise".equals(databaseName)) {
        return new SybaseASE15Dialect();
    }
    if (databaseName.startsWith("Adaptive Server Anywhere")) {
        return new SybaseAnywhereDialect();
    }
    if ("Informix Dynamic Server".equals(databaseName)) {
        return new InformixDialect();
    }
    if ("DB2 UDB for AS/400".equals(databaseName)) {
        return new DB2400Dialect();
    }
    if (databaseName.startsWith("DB2/")) {
        return new DB2Dialect();
    }
    if ("Oracle".equals(databaseName)) {
        final int majorVersion = info.getDatabaseMajorVersion();
        switch(majorVersion) {
            case 12:
                return new Oracle12cDialect();
            case 11:
            // fall through
            case 10:
                return new Oracle10gDialect();
            case 9:
                return new Oracle9iDialect();
            case 8:
                return new Oracle8iDialect();
            default:
                LOG.unknownOracleVersion(majorVersion);
        }
        return new Oracle8iDialect();
    }
    if ("HDB".equals(databaseName)) {
        // SAP recommends defaulting to column store.
        return new HANAColumnStoreDialect();
    }
    if (databaseName.startsWith("Firebird")) {
        return new FirebirdDialect();
    }
    return null;
}

Example 15 with HSQLDialect

use of org.hibernate.dialect.HSQLDialect in project hibernate-orm by hibernate.

the class UnionSubclassTest method testQuerySubclassAttribute.

@Test
@SkipForDialect(value = TeradataDialect.class, jiraKey = "HHH-8190", comment = "SQL uses Teradata reserved word: title")
public void testQuerySubclassAttribute() {
    if (getDialect() instanceof HSQLDialect) {
        // TODO : why??
        return;
    }
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Person p = new Person();
    p.setName("Emmanuel");
    p.setSex('M');
    s.persist(p);
    Employee q = new Employee();
    q.setName("Steve");
    q.setSex('M');
    q.setTitle("Mr");
    q.setSalary(new BigDecimal(1000));
    s.persist(q);
    List result = s.createQuery("from Person where salary > 100").list();
    assertEquals(result.size(), 1);
    assertSame(result.get(0), q);
    result = s.createQuery("from Person where salary > 100 or name like 'E%'").list();
    assertEquals(result.size(), 2);
    result = s.createCriteria(Person.class).add(Property.forName("salary").gt(new BigDecimal(100))).list();
    assertEquals(result.size(), 1);
    assertSame(result.get(0), q);
    result = s.createQuery("select salary from Person where salary > 100").list();
    assertEquals(result.size(), 1);
    assertEquals(((BigDecimal) result.get(0)).intValue(), 1000);
    s.delete(p);
    s.delete(q);
    t.commit();
    s.close();
}