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

Example 1 with SQLServerDialect

use of org.hibernate.dialect.SQLServerDialect 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 2 with SQLServerDialect

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

the class CriteriaQueryTest method testSubselect.

@Test
@SkipForDialect(value = SybaseASE15Dialect.class, strictMatching = true, jiraKey = "HHH-3032", comment = "I was told this is fixed in Sybase ASE 15.7")
public void testSubselect() {
    Session session = openSession();
    Transaction t = session.beginTransaction();
    Course course = new Course();
    course.setCourseCode("HIB");
    course.setDescription("Hibernate Training");
    session.persist(course);
    Student gavin = new Student();
    gavin.setName("Gavin King");
    gavin.setStudentNumber(232);
    session.persist(gavin);
    Enrolment enrolment2 = new Enrolment();
    enrolment2.setCourse(course);
    enrolment2.setCourseCode(course.getCourseCode());
    enrolment2.setSemester((short) 3);
    enrolment2.setYear((short) 1998);
    enrolment2.setStudent(gavin);
    enrolment2.setStudentNumber(gavin.getStudentNumber());
    gavin.getEnrolments().add(enrolment2);
    session.persist(enrolment2);
    DetachedCriteria dc = DetachedCriteria.forClass(Student.class).add(Property.forName("studentNumber").eq(new Long(232))).setProjection(Property.forName("name"));
    session.createCriteria(Student.class).add(Subqueries.propertyEqAll("name", dc)).list();
    session.createCriteria(Student.class).add(Subqueries.exists(dc)).list();
    session.createCriteria(Student.class).add(Property.forName("name").eqAll(dc)).list();
    session.createCriteria(Student.class).add(Subqueries.in("Gavin King", dc)).list();
    DetachedCriteria dc2 = DetachedCriteria.forClass(Student.class, "st").add(Property.forName("st.studentNumber").eqProperty("e.studentNumber")).setProjection(Property.forName("name"));
    session.createCriteria(Enrolment.class, "e").add(Subqueries.eq("Gavin King", dc2)).list();
    DetachedCriteria dc3 = DetachedCriteria.forClass(Student.class, "st").createCriteria("enrolments").createCriteria("course").add(Property.forName("description").eq("Hibernate Training")).setProjection(Property.forName("st.name"));
    session.createCriteria(Enrolment.class, "e").add(Subqueries.eq("Gavin King", dc3)).list();
    DetachedCriteria dc4 = DetachedCriteria.forClass(Student.class, "st").setProjection(Property.forName("name").as("stname"));
    dc4.getExecutableCriteria(session).list();
    // SQL Server and Oracle doesn't normally support ORDER BY in subqueries...
    if (!(getDialect() instanceof SQLServerDialect) && !(getDialect() instanceof Oracle8iDialect)) {
        dc4.getExecutableCriteria(session).addOrder(Order.asc("stname")).list();
    }
    session.createCriteria(Enrolment.class, "e").add(Subqueries.eq("Gavin King", dc4)).list();
    session.delete(enrolment2);
    session.delete(gavin);
    session.delete(course);
    t.commit();
    session.close();
}

Example 3 with SQLServerDialect

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

the class ASTParserLoadingTest method testStr.

@Test
public void testStr() {
    Session session = openSession();
    Transaction txn = session.beginTransaction();
    Animal an = new Animal();
    an.setBodyWeight(123.45f);
    session.persist(an);
    String str = (String) session.createQuery("select str(an.bodyWeight) from Animal an where str(an.bodyWeight) like '%1%'").uniqueResult();
    if ((getDialect() instanceof DB2Dialect || getDialect() instanceof TeradataDialect) && !(getDialect() instanceof DerbyDialect)) {
        assertTrue(str.startsWith("1.234"));
    } else //noinspection deprecation
    if (getDialect() instanceof SybaseDialect || getDialect() instanceof Sybase11Dialect || getDialect() instanceof SybaseASE15Dialect || getDialect() instanceof SybaseAnywhereDialect || getDialect() instanceof SQLServerDialect) {
        // str(val) on sybase assumes a default of 10 characters with no decimal point or decimal values
        // str(val) on sybase result is right-justified
        assertEquals(str.length(), 10);
        assertTrue(str.endsWith("123"));
        str = (String) session.createQuery("select str(an.bodyWeight, 8, 3) from Animal an where str(an.bodyWeight, 8, 3) like '%1%'").uniqueResult();
        assertEquals(str.length(), 8);
        assertTrue(str.endsWith("123.450"));
    } else {
        assertTrue(str.startsWith("123.4"));
    }
    //noinspection deprecation
    if (!(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof Sybase11Dialect) && !(getDialect() instanceof SybaseASE15Dialect) && !(getDialect() instanceof SybaseAnywhereDialect) && !(getDialect() instanceof SQLServerDialect || getDialect() instanceof TeradataDialect)) {
        // In TransactSQL (the variant spoken by Sybase and SQLServer), the str() function
        // is explicitly intended for numeric values only...
        String dateStr1 = (String) session.createQuery("select str(current_date) from Animal").uniqueResult();
        String dateStr2 = (String) session.createQuery("select str(year(current_date))||'-'||str(month(current_date))||'-'||str(day(current_date)) from Animal").uniqueResult();
        System.out.println(dateStr1 + '=' + dateStr2);
        if (!(getDialect() instanceof Oracle8iDialect)) {
            //Oracle renders the name of the month :(
            String[] dp1 = StringHelper.split("-", dateStr1);
            String[] dp2 = StringHelper.split("-", dateStr2);
            for (int i = 0; i < 3; i++) {
                if (dp1[i].startsWith("0")) {
                    dp1[i] = dp1[i].substring(1);
                }
                assertEquals(dp1[i], dp2[i]);
            }
        }
    }
    session.delete(an);
    txn.commit();
    session.close();
}

Example 4 with SQLServerDialect

use of org.hibernate.dialect.SQLServerDialect 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 5 with SQLServerDialect

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

the class RepeatableReadTest method testStaleVersionedInstanceFoundOnLock.

@Test
public void testStaleVersionedInstanceFoundOnLock() {
    if (!readCommittedIsolationMaintained("repeatable read tests")) {
        return;
    }
    String check = "EJB3 Specification";
    Session s1 = sessionFactory().openSession();
    Transaction t1 = s1.beginTransaction();
    Item item = new Item(check);
    s1.save(item);
    t1.commit();
    s1.close();
    Long itemId = item.getId();
    long initialVersion = item.getVersion();
    // Now, open a new Session and re-load the item...
    s1 = sessionFactory().openSession();
    t1 = s1.beginTransaction();
    item = (Item) s1.get(Item.class, itemId);
    // now that the item is associated with the persistence-context of that session,
    // open a new session and modify it "behind the back" of the first session
    Session s2 = sessionFactory().openSession();
    Transaction t2 = s2.beginTransaction();
    Item item2 = (Item) s2.get(Item.class, itemId);
    item2.setName("EJB3 Persistence Spec");
    t2.commit();
    s2.close();
    // at this point, s1 now contains stale data, so acquire a READ lock
    // and make sure we get the already associated state (i.e., the old
    // name and the old version)
    s1.lock(item, LockMode.READ);
    item2 = (Item) s1.get(Item.class, itemId);
    assertTrue(item == item2);
    assertEquals("encountered non-repeatable read", check, item2.getName());
    assertEquals("encountered non-repeatable read", initialVersion, item2.getVersion());
    // attempt to acquire an UPGRADE lock; this should fail
    try {
        s1.lock(item, LockMode.UPGRADE);
        fail("expected UPGRADE lock failure");
    } catch (StaleObjectStateException expected) {
    // this is the expected behavior
    } catch (SQLGrammarException t) {
        if (getDialect() instanceof SQLServerDialect) {
            // sql-server (using snapshot isolation) reports this as a grammar exception /:)
            //
            // not to mention that it seems to "lose track" of the transaction in this scenario...
            t1.rollback();
            t1 = s1.beginTransaction();
        } else {
            throw t;
        }
    }
    t1.commit();
    s1.close();
    // clean up
    s1 = sessionFactory().openSession();
    t1 = s1.beginTransaction();
    s1.createQuery("delete Item").executeUpdate();
    t1.commit();
    s1.close();
}