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

Example 16 with MySQLDialect

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

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

the class SQLLoaderTest method testFindBySQLAssociatedObjects.

@Test
public void testFindBySQLAssociatedObjects() throws HibernateException, SQLException {
    Session s = openSession();
    s.beginTransaction();
    for (Object entity : s.createQuery("from Assignable").list()) {
        s.delete(entity);
    }
    for (Object entity : s.createQuery("from Category").list()) {
        s.delete(entity);
    }
    Category c = new Category();
    c.setName("NAME");
    Assignable assn = new Assignable();
    assn.setId("i.d.");
    List l = new ArrayList();
    l.add(c);
    assn.setCategories(l);
    c.setAssignable(assn);
    s.save(assn);
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    List list = s.createSQLQuery("select {category.*} from category {category}").addEntity("category", Category.class).list();
    list.get(0);
    s.getTransaction().commit();
    s.close();
    if (getDialect() instanceof MySQLDialect) {
        return;
    }
    s = openSession();
    s.beginTransaction();
    Query query = s.getNamedQuery("namedsql");
    assertNotNull(query);
    list = query.list();
    assertNotNull(list);
    Object[] values = (Object[]) list.get(0);
    assertNotNull(values[0]);
    assertNotNull(values[1]);
    assertTrue("wrong type: " + values[0].getClass(), values[0] instanceof Category);
    assertTrue("wrong type: " + values[1].getClass(), values[1] instanceof Assignable);
    s.getTransaction().commit();
    s.close();
}

Example 18 with MySQLDialect

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

the class SQLLoaderTest method testFindBySQLSimpleByDiffSessions.

@Test
public void testFindBySQLSimpleByDiffSessions() throws Exception {
    Session session = openSession();
    session.beginTransaction();
    Category s = new Category();
    s.setName(String.valueOf(nextLong++));
    session.save(s);
    session.getTransaction().commit();
    session.close();
    if (getDialect() instanceof MySQLDialect) {
        return;
    }
    session = openSession();
    session.beginTransaction();
    Query query = session.createSQLQuery("select s.category_key_col as {category.id}, s.name as {category.name}, s.\"assign-able-id\" as {category.assignable} from {category} s").addEntity("category", Category.class);
    List list = query.list();
    assertNotNull(list);
    assertTrue(list.size() > 0);
    assertTrue(list.get(0) instanceof Category);
    // How do we handle objects that does not have id property (such as Simple ?)
    // How do we handle objects with composite id's ? (such as Single)
    session.getTransaction().commit();
    session.close();
}

Example 19 with MySQLDialect

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

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

the class ParentChildTest method testCollectionQuery.

@Test
public void testCollectionQuery() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Simple s1 = new Simple(Long.valueOf(1));
    s1.setName("s");
    s1.setCount(0);
    Simple s2 = new Simple(Long.valueOf(2));
    s2.setCount(2);
    Simple s3 = new Simple(Long.valueOf(3));
    s3.setCount(3);
    s.save(s1);
    s.save(s2);
    s.save(s3);
    Container c = new Container();
    Contained cd = new Contained();
    List bag = new ArrayList();
    bag.add(cd);
    c.setBag(bag);
    List l = new ArrayList();
    l.add(s1);
    l.add(s3);
    l.add(s2);
    c.setOneToMany(l);
    l = new ArrayList();
    l.add(s1);
    l.add(null);
    l.add(s2);
    c.setManyToMany(l);
    s.save(c);
    Container cx = new Container();
    s.save(cx);
    Simple sx = new Simple(Long.valueOf(5));
    sx.setCount(5);
    sx.setName("s");
    s.save(sx);
    assertTrue(s.createQuery("select c from ContainerX c, Simple s where c.oneToMany[2] = s").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c, Simple s where c.manyToMany[2] = s").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c, Simple s where s = c.oneToMany[2]").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c, Simple s where s = c.manyToMany[2]").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c where c.oneToMany[0].name = 's'").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c where c.manyToMany[0].name = 's'").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c where 's' = c.oneToMany[2 - 2].name").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c where 's' = c.manyToMany[(3+1)/4-1].name").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c where c.oneToMany[ c.manyToMany[0].count ].name = 's'").list().size() == 1);
    assertTrue(s.createQuery("select c from ContainerX c where c.manyToMany[ c.oneToMany[0].count ].name = 's'").list().size() == 1);
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof org.hibernate.dialect.TimesTenDialect)) {
        assertTrue(s.createQuery("select c from ContainerX c where c.manyToMany[ maxindex(c.manyToMany) ].count = 2").list().size() == 1);
    }
    assertTrue(s.contains(cd));
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof HSQLDialect)) {
        s.createFilter(c.getBag(), "where 0 in elements(this.bag)").list();
        s.createFilter(c.getBag(), "where 0 in elements(this.lazyBag)").list();
    }
    s.createQuery("select count(comp.name) from ContainerX c join c.components comp").list();
    s.delete(cd);
    s.delete(c);
    s.delete(s1);
    s.delete(s2);
    s.delete(s3);
    s.delete(cx);
    s.delete(sx);
    t.commit();
    s.close();
}