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

Example 6 with MySQLDialect

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

the class FooBarTest method testQueryCollectionOfValues.

@Test
public void testQueryCollectionOfValues() throws Exception {
    Session s = openSession();
    s.beginTransaction();
    Baz baz = new Baz();
    baz.setDefaults();
    s.save(baz);
    Glarch g = new Glarch();
    Serializable gid = s.save(g);
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof HSQLDialect) && /*&& !(dialect instanceof MckoiDialect)*/
    !(getDialect() instanceof SAPDBDialect) && !(getDialect() instanceof PointbaseDialect) && !(getDialect() instanceof TimesTenDialect)) {
        s.createFilter(baz.getFooArray(), "where size(this.bytes) > 0").list();
        s.createFilter(baz.getFooArray(), "where 0 in elements(this.bytes)").list();
    }
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    s.createQuery("from Baz baz join baz.fooSet foo join foo.foo.foo foo2 where foo2.string = 'foo'").list();
    s.createQuery("from Baz baz join baz.fooArray foo join foo.foo.foo foo2 where foo2.string = 'foo'").list();
    s.createQuery("from Baz baz join baz.stringDateMap date where index(date) = 'foo'").list();
    s.createQuery("from Baz baz join baz.topGlarchez g where index(g) = 'A'").list();
    s.createQuery("select index(g) from Baz baz join baz.topGlarchez g").list();
    assertTrue(s.createQuery("from Baz baz left join baz.stringSet").list().size() == 3);
    baz = (Baz) s.createQuery("from Baz baz join baz.stringSet str where str='foo'").list().get(0);
    assertTrue(!Hibernate.isInitialized(baz.getStringSet()));
    baz = (Baz) s.createQuery("from Baz baz left join fetch baz.stringSet").list().get(0);
    assertTrue(Hibernate.isInitialized(baz.getStringSet()));
    assertTrue(s.createQuery("from Baz baz join baz.stringSet string where string='foo'").list().size() == 1);
    assertTrue(s.createQuery("from Baz baz inner join baz.components comp where comp.name='foo'").list().size() == 1);
    //List bss = s.find("select baz, ss from Baz baz inner join baz.stringSet ss");
    s.createQuery("from Glarch g inner join g.fooComponents comp where comp.fee is not null").list();
    s.createQuery("from Glarch g inner join g.fooComponents comp join comp.fee fee where fee.count > 0").list();
    s.createQuery("from Glarch g inner join g.fooComponents comp where comp.fee.count is not null").list();
    s.delete(baz);
    s.delete(s.get(Glarch.class, gid));
    s.getTransaction().commit();
    s.close();
}

Example 7 with MySQLDialect

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

the class FooBarTest method testAutoFlush.

@Test
public void testAutoFlush() throws Exception {
    Session s = openSession();
    Transaction txn = s.beginTransaction();
    FooProxy foo = new Foo();
    s.save(foo);
    assertTrue("autoflush create", s.createQuery("from Foo foo").list().size() == 1);
    foo.setChar('X');
    assertTrue("autoflush update", s.createQuery("from Foo foo where foo.char='X'").list().size() == 1);
    txn.commit();
    s.close();
    s = openSession();
    txn = s.beginTransaction();
    foo = (FooProxy) s.load(Foo.class, foo.getKey());
    //assertTrue( s.find("from Foo foo where not foo.char='X'").size()==1, "autoflush update" );
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof HSQLDialect) && !(getDialect() instanceof PointbaseDialect)) {
        foo.setBytes("osama".getBytes());
        assertTrue("autoflush collection update", s.createQuery("from Foo foo where 111 in elements(foo.bytes)").list().size() == 1);
        foo.getBytes()[0] = 69;
        assertTrue("autoflush collection update", s.createQuery("from Foo foo where 69 in elements(foo.bytes)").list().size() == 1);
    }
    s.delete(foo);
    assertTrue("autoflush delete", s.createQuery("from Foo foo").list().size() == 0);
    txn.commit();
    s.close();
}

Example 8 with MySQLDialect

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

the class SQLFunctionsTest method testDialectSQLFunctions.

@Test
public void testDialectSQLFunctions() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Iterator iter = s.createQuery("select max(s.count) from Simple s").iterate();
    if (getDialect() instanceof MySQLDialect)
        assertTrue(iter.hasNext() && iter.next() == null);
    Simple simple = new Simple(Long.valueOf(10));
    simple.setName("Simple Dialect Function Test");
    simple.setAddress("Simple Address");
    simple.setPay(Float.valueOf(45.8f));
    simple.setCount(2);
    s.save(simple);
    // Test to make sure allocating an specified object operates correctly.
    assertTrue(s.createQuery("select new org.hibernate.test.legacy.S(s.count, s.address) from Simple s").list().size() == 1);
    // Quick check the base dialect functions operate correctly
    assertTrue(s.createQuery("select max(s.count) from Simple s").list().size() == 1);
    assertTrue(s.createQuery("select count(*) from Simple s").list().size() == 1);
    if (getDialect() instanceof Oracle9iDialect) {
        // Check Oracle Dialect mix of dialect functions - no args (no parenthesis and single arg functions
        List rset = s.createQuery("select s.name, sysdate, trunc(s.pay), round(s.pay) from Simple s").list();
        assertNotNull("Name string should have been returned", (((Object[]) rset.get(0))[0]));
        assertNotNull("Todays Date should have been returned", (((Object[]) rset.get(0))[1]));
        assertEquals("trunc(45.8) result was incorrect ", Float.valueOf(45), ((Object[]) rset.get(0))[2]);
        assertEquals("round(45.8) result was incorrect ", Float.valueOf(46), ((Object[]) rset.get(0))[3]);
        simple.setPay(new Float(-45.8));
        s.update(simple);
        // Test type conversions while using nested functions (Float to Int).
        rset = s.createQuery("select abs(round(s.pay)) from Simple s").list();
        assertEquals("abs(round(-45.8)) result was incorrect ", Float.valueOf(46), rset.get(0));
        // Test a larger depth 3 function example - Not a useful combo other than for testing
        assertTrue(s.createQuery("select trunc(round(sysdate)) from Simple s").list().size() == 1);
        // Test the oracle standard NVL funtion as a test of multi-param functions...
        simple.setPay(null);
        s.update(simple);
        Integer value = (Integer) s.createQuery("select MOD( NVL(s.pay, 5000), 2 ) from Simple as s where s.id = 10").list().get(0);
        assertTrue(0 == value.intValue());
    }
    if ((getDialect() instanceof HSQLDialect)) {
        // Test the hsql standard MOD funtion as a test of multi-param functions...
        Integer value = (Integer) s.createQuery("select MOD(s.count, 2) from Simple as s where s.id = 10").list().get(0);
        assertTrue(0 == value.intValue());
    }
    s.delete(simple);
    t.commit();
    s.close();
}

Example 9 with MySQLDialect

use of org.hibernate.dialect.MySQLDialect 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 10 with MySQLDialect

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

the class ParentChildTest method testLoadAfterNonExists.

@Test
public void testLoadAfterNonExists() throws HibernateException, SQLException {
    Session session = openSession();
    if ((getDialect() instanceof MySQLDialect) || (getDialect() instanceof IngresDialect)) {
        session.doWork(new AbstractWork() {

            @Override
            public void execute(Connection connection) throws SQLException {
                connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            }
        });
    }
    session.getTransaction().begin();
    // First, prime the fixture session to think the entity does not exist
    try {
        session.load(Simple.class, new Long(-1));
        fail();
    } catch (ObjectNotFoundException onfe) {
        if (getDialect() instanceof TeradataDialect) {
            session.getTransaction().rollback();
            session.getTransaction().begin();
        }
    // this is correct
    }
    // Next, lets create that entity "under the covers"
    Session anotherSession = sessionFactory().openSession();
    anotherSession.beginTransaction();
    Simple myNewSimple = new Simple(Long.valueOf(-1));
    myNewSimple.setName("My under the radar Simple entity");
    myNewSimple.setAddress("SessionCacheTest.testLoadAfterNonExists");
    myNewSimple.setCount(1);
    myNewSimple.setDate(new Date());
    myNewSimple.setPay(Float.valueOf(100000000));
    anotherSession.save(myNewSimple);
    anotherSession.getTransaction().commit();
    anotherSession.close();
    // Now, lets make sure the original session can see the created row...
    session.clear();
    try {
        Simple dummy = (Simple) session.get(Simple.class, Long.valueOf(-1));
        assertNotNull("Unable to locate entity Simple with id = -1", dummy);
        session.delete(dummy);
    } catch (ObjectNotFoundException onfe) {
        fail("Unable to locate entity Simple with id = -1");
    }
    session.getTransaction().commit();
    session.close();
}