org.hibernate.dialect.DB2Dialect Java Examples

/**
 * Determine the Hibernate database dialect class for the given target database.
 * @param database the target database
 * @return the Hibernate database dialect class, or {@code null} if none found
 */
@Nullable
protected Class<?> determineDatabaseDialectClass(Database database) {
	switch (database) {
		case DB2: return DB2Dialect.class;
		case DERBY: return DerbyTenSevenDialect.class;
		case H2: return H2Dialect.class;
		case HANA: return HANAColumnStoreDialect.class;
		case HSQL: return HSQLDialect.class;
		case INFORMIX: return InformixDialect.class;
		case MYSQL: return MySQL5Dialect.class;
		case ORACLE: return Oracle12cDialect.class;
		case POSTGRESQL: return PostgreSQL95Dialect.class;
		case SQL_SERVER: return SQLServer2012Dialect.class;
		case SYBASE: return SybaseDialect.class;
		default: return null;
	}
}
 

/**
 * Determine the Hibernate database dialect class for the given target database.
 * @param database the target database
 * @return the Hibernate database dialect class, or {@code null} if none found
 */
@Nullable
protected Class<?> determineDatabaseDialectClass(Database database) {
	switch (database) {
		case DB2: return DB2Dialect.class;
		case DERBY: return DerbyTenSevenDialect.class;
		case H2: return H2Dialect.class;
		case HANA: return HANAColumnStoreDialect.class;
		case HSQL: return HSQLDialect.class;
		case INFORMIX: return InformixDialect.class;
		case MYSQL: return MySQL5Dialect.class;
		case ORACLE: return Oracle12cDialect.class;
		case POSTGRESQL: return PostgreSQL95Dialect.class;
		case SQL_SERVER: return SQLServer2012Dialect.class;
		case SYBASE: return SybaseDialect.class;
		default: return null;
	}
}
 

/**
 * Determine the Hibernate database dialect class for the given target database.
 * @param database the target database
 * @return the Hibernate database dialect class, or {@code null} if none found
 */
@SuppressWarnings("deprecation")
protected Class<?> determineDatabaseDialectClass(Database database) {
	switch (database) {
		case DB2: return DB2Dialect.class;
		case DERBY: return DerbyDialect.class;  // DerbyDialect deprecated in 4.x
		case H2: return H2Dialect.class;
		case HSQL: return HSQLDialect.class;
		case INFORMIX: return InformixDialect.class;
		case MYSQL: return MySQL5Dialect.class;
		case ORACLE: return Oracle9iDialect.class;
		case POSTGRESQL: return PostgreSQLDialect.class;  // PostgreSQLDialect deprecated in 4.x
		case SQL_SERVER: return SQLServer2008Dialect.class;
		case SYBASE: return org.hibernate.dialect.SybaseDialect.class;  // SybaseDialect deprecated in 3.6 but not 4.x
		default: return null;
	}
}
 

/**
 * Determine the Hibernate database dialect class for the given target database.
 * @param database the target database
 * @return the Hibernate database dialect class, or {@code null} if none found
 */
@SuppressWarnings("deprecation")
protected Class<?> determineDatabaseDialectClass(Database database) {
	switch (database) {
		case DB2: return DB2Dialect.class;
		case DERBY: return DerbyDialect.class;  // DerbyDialect deprecated in 4.x
		case H2: return H2Dialect.class;
		case HSQL: return HSQLDialect.class;
		case INFORMIX: return InformixDialect.class;
		case MYSQL: return MySQLDialect.class;
		case ORACLE: return Oracle9iDialect.class;
		case POSTGRESQL: return PostgreSQLDialect.class;  // PostgreSQLDialect deprecated in 4.x
		case SQL_SERVER: return SQLServerDialect.class;
		case SYBASE: return org.hibernate.dialect.SybaseDialect.class;  // SybaseDialect deprecated in 3.6 but not 4.x
		default: return null;
	}
}
 

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 '123%' or str(an.bodyWeight) like '1.23%'").uniqueResult();
	if ( getDialect() instanceof DB2Dialect ) {
		assertTrue( str.startsWith("1.234") );
	}
	else if ( getDialect() instanceof SQLServerDialect ) {
		// no assertion as SQLServer always returns nulls here; even trying directly against the
		// database, it seems to have problems with str() in the where clause...
	}
	else {
		assertTrue( str.startsWith("123.4") );
	}
	if ( ! ( getDialect() instanceof SybaseDialect ) ) {
		// 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 Oracle9Dialect || 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();
}
 

public void testCast() {
	if ( ( getDialect() instanceof MySQLDialect ) || ( getDialect() instanceof DB2Dialect ) ) {
		return;
	}
	Session session = openSession();
	Transaction txn = session.beginTransaction();
	session.createQuery("from Human h where h.nickName like 'G%'").list();
	session.createQuery("from Animal a where cast(a.bodyWeight as string) like '1.%'").list();
	session.createQuery("from Animal a where cast(a.bodyWeight as integer) = 1").list();
	txn.commit();
	session.close();
}
 

public void testExtract() {
	Session session = openSession();
	Transaction txn = session.beginTransaction();
	session.createQuery("select second(current_timestamp()), minute(current_timestamp()), hour(current_timestamp()) from Mammal m").list();
	session.createQuery("select day(m.birthdate), month(m.birthdate), year(m.birthdate) from Mammal m").list();
	if ( !(getDialect() instanceof DB2Dialect) ) { //no ANSI extract
		session.createQuery("select extract(second from current_timestamp()), extract(minute from current_timestamp()), extract(hour from current_timestamp()) from Mammal m").list();
		session.createQuery("select extract(day from m.birthdate), extract(month from m.birthdate), extract(year from m.birthdate) from Mammal m").list();
	}
	txn.commit();
	session.close();
}
 

public void testOrderBy() throws Exception {
	assertTranslation( "from Animal an order by an.bodyWeight" );
	assertTranslation( "from Animal an order by an.bodyWeight asc" );
	assertTranslation( "from Animal an order by an.bodyWeight desc" );
	assertTranslation( "from Animal an order by sqrt(an.bodyWeight*4)/2" );
	assertTranslation( "from Animal an order by an.mother.bodyWeight" );
	assertTranslation( "from Animal an order by an.bodyWeight, an.description" );
	assertTranslation( "from Animal an order by an.bodyWeight asc, an.description desc" );
	if ( getDialect() instanceof HSQLDialect || getDialect() instanceof DB2Dialect ) {
		assertTranslation( "from Human h order by sqrt(h.bodyWeight), year(h.birthdate)" );
	}
}
 

private void addDialects(StrategySelectorImpl strategySelector) {
	addDialect( strategySelector, Cache71Dialect.class );
	addDialect( strategySelector, CUBRIDDialect.class );
	addDialect( strategySelector, DB2Dialect.class );
	addDialect( strategySelector, DB2390Dialect.class );
	addDialect( strategySelector, DB2390V8Dialect.class );
	addDialect( strategySelector, DB2400Dialect.class );
	addDialect( strategySelector, DerbyTenFiveDialect.class );
	addDialect( strategySelector, DerbyTenSixDialect.class );
	addDialect( strategySelector, DerbyTenSevenDialect.class );
	addDialect( strategySelector, FirebirdDialect.class );
	addDialect( strategySelector, FrontBaseDialect.class );
	addDialect( strategySelector, H2Dialect.class );
	addDialect( strategySelector, HANAColumnStoreDialect.class );
	addDialect( strategySelector, HANARowStoreDialect.class );
	addDialect( strategySelector, HSQLDialect.class );
	addDialect( strategySelector, InformixDialect.class );
	addDialect( strategySelector, IngresDialect.class );
	addDialect( strategySelector, Ingres9Dialect.class );
	addDialect( strategySelector, Ingres10Dialect.class );
	addDialect( strategySelector, InterbaseDialect.class );
	addDialect( strategySelector, JDataStoreDialect.class );
	addDialect( strategySelector, MckoiDialect.class );
	addDialect( strategySelector, MimerSQLDialect.class );
	addDialect( strategySelector, MySQL5Dialect.class );
	addDialect( strategySelector, MySQL5InnoDBDialect.class );
	addDialect( strategySelector, MySQL57InnoDBDialect.class );
	addDialect( strategySelector, MySQL57Dialect.class );
	addDialect( strategySelector, Oracle8iDialect.class );
	addDialect( strategySelector, Oracle9iDialect.class );
	addDialect( strategySelector, Oracle10gDialect.class );
	addDialect( strategySelector, PointbaseDialect.class );
	addDialect( strategySelector, PostgresPlusDialect.class );
	addDialect( strategySelector, PostgreSQL81Dialect.class );
	addDialect( strategySelector, PostgreSQL82Dialect.class );
	addDialect( strategySelector, PostgreSQL9Dialect.class );
	addDialect( strategySelector, ProgressDialect.class );
	addDialect( strategySelector, SAPDBDialect.class );
	addDialect( strategySelector, SQLServerDialect.class );
	addDialect( strategySelector, SQLServer2005Dialect.class );
	addDialect( strategySelector, SQLServer2008Dialect.class );
	addDialect( strategySelector, Sybase11Dialect.class );
	addDialect( strategySelector, SybaseAnywhereDialect.class );
	addDialect( strategySelector, SybaseASE15Dialect.class );
	addDialect( strategySelector, SybaseASE157Dialect.class );
	addDialect( strategySelector, TeradataDialect.class );
	addDialect( strategySelector, TimesTenDialect.class );
}
 

private HibernateRevengArgs<List<? extends Class<?>>> getRevengArgs(String schema, boolean explicitSchemaRequired) {
    return new HibernateRevengArgs(schema, outputPath, new Db2DbPlatform(), DB2Dialect.class, Arrays.asList(HibClassA.class, HibClassB.class, HibClassSchemaC.class, HibClassSchemaD.class))
            .withPostCreateTableSql(" lock datarows")
            .withExplicitSchemaRequired(explicitSchemaRequired);
}
 

public void testQueryLockMode() throws Exception {

		Session s = openSession();
		Transaction tx = s.beginTransaction();
		Bar bar = new Bar();
		s.save(bar);
		s.flush();
		bar.setString("changed");
		Baz baz = new Baz();
		baz.setFoo(bar);
		s.save(baz);
		Query q = s.createQuery("from Foo foo, Bar bar");
		if ( !(getDialect() instanceof DB2Dialect) ) {
			q.setLockMode("bar", LockMode.UPGRADE);
		}
		Object[] result = (Object[]) q.uniqueResult();
		Object b = result[0];
		assertTrue( s.getCurrentLockMode(b)==LockMode.WRITE && s.getCurrentLockMode( result[1] )==LockMode.WRITE );
		tx.commit();
		s.disconnect();

		s.reconnect();
		tx = s.beginTransaction();
		assertTrue( s.getCurrentLockMode(b)==LockMode.NONE );
		s.find("from Foo foo");
		assertTrue( s.getCurrentLockMode(b)==LockMode.NONE );
		q = s.createQuery("from Foo foo");
		q.setLockMode("foo", LockMode.READ);
		q.list();
		assertTrue( s.getCurrentLockMode(b)==LockMode.READ);
		s.evict(baz);
		tx.commit();
		s.disconnect();
		
		s.reconnect();
		tx = s.beginTransaction();
		assertTrue( s.getCurrentLockMode(b)==LockMode.NONE );
		s.delete( s.load( Baz.class, baz.getCode() ) );
		assertTrue( s.getCurrentLockMode(b)==LockMode.NONE );
		tx.commit();
		s.close();

		s = openSession();
		tx = s.beginTransaction();
		q = s.createQuery("from Foo foo, Bar bar, Bar bar2");
		if ( !(getDialect() instanceof DB2Dialect) ) {
			q.setLockMode("bar", LockMode.UPGRADE);
		}
		q.setLockMode("bar2", LockMode.READ);
		result = (Object[]) q.list().get(0);
		if ( !(getDialect() instanceof DB2Dialect) ) {
			assertTrue( s.getCurrentLockMode( result[0] )==LockMode.UPGRADE && s.getCurrentLockMode( result[1] )==LockMode.UPGRADE );
		}
		s.delete( result[0] );
		tx.commit();
		s.close();
	}
 

public void testScrollableIterator() throws Exception {
	if ( getDialect() instanceof DB2Dialect || getDialect() instanceof OracleDialect || getDialect() instanceof SybaseDialect || getDialect() instanceof HSQLDialect ) {
		Session s = openSession();
		Transaction txn = s.beginTransaction();
		s.save( new Foo() );
		s.save( new Foo() );
		s.save( new Foo() );
		s.save( new Bar() );
		Query query = s.createQuery("select f, f.integer from Foo f");
		assertTrue( query.getReturnTypes().length==2 );
		ScrollableResults iter = query.scroll();
		assertTrue( iter.next() );
		assertTrue( iter.scroll(1) );
		FooProxy f2 = (FooProxy) iter.get()[0];
		assertTrue( f2!=null );
		assertTrue( iter.scroll(-1) );
		Object f1 = iter.get(0);
		iter.next();
		assertTrue( f1!=null && iter.get(0)==f2 );
		iter.getInteger(1);

		assertTrue( !iter.scroll(100) );
		assertTrue( iter.first() );
		assertTrue( iter.scroll(3) );
		Object f4 = iter.get(0);
		assertTrue( f4!=null );
		assertTrue( !iter.next() );
		assertTrue( iter.first() );
		assertTrue( iter.get(0)==f1 );
		assertTrue( iter.last() );
		assertTrue( iter.get(0)==f4 );
		assertTrue( iter.previous() );
		txn.commit();
		s.close();

		s = openSession();
		txn = s.beginTransaction();
		query = s.createQuery("select f, f.integer from Foo f");
		assertTrue( query.getReturnTypes().length==2 );
		iter = query.scroll();
		assertTrue( iter.next() );
		assertTrue( iter.scroll(1) );
		f2 = (FooProxy) iter.get()[0];
		assertTrue( f2!=null );
		assertTrue( f2.getString()!=null  && f2.getComponent().getImportantDates().length > 0 );
		assertTrue( iter.scroll(-1) );
		f1 = iter.get(0);
		iter.next();
		assertTrue( f1!=null && iter.get(0)==f2 );
		iter.getInteger(1);

		assertTrue( !iter.scroll(100) );
		assertTrue( iter.first() );
		assertTrue( iter.scroll(3) );
		f4 = iter.get(0);
		assertTrue( f4!=null );
		assertTrue( !iter.next() );
		assertTrue( iter.first() );
		assertTrue( iter.get(0)==f1 );
		assertTrue( iter.last() );
		assertTrue( iter.get(0)==f4 );
		assertTrue( iter.previous() );
		assertTrue( s.delete("from Foo")==4 );
		s.flush();
		assertTrue( s.find("from java.lang.Object").size()==0 );
		txn.commit();
		s.close();
	}
}
 

public boolean appliesTo(Dialect dialect) {
	return ( dialect instanceof DB2Dialect);
}
 

public void testEJBQLFunctions() throws Exception {
	Session session = openSession();

	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();

	hql = "from Animal a where bit_length(a.bodyWeight) = 24";
	session.createQuery(hql).list();

	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();

	session.close();
}
 

/**
 * Does the db/dialect support using a column's physical name in the order-by clause
 * even after it has been aliased in the select clause.  This is not actually
 * required by the SQL spec, although virtually ever DB in the world supports this
 * (the most glaring omission here being IBM-variant DBs ala DB2 and Derby).
 *
 * @param testDescription description of the scenario being tested.
 * @return true if is allowed
 */
protected boolean allowsPhysicalColumnNameInOrderby(String testDescription) {
	if ( DB2Dialect.class.isInstance( getDialect() ) ) {
		// https://issues.apache.org/jira/browse/DERBY-1624
		reportSkip( "Dialect does not support physical column name in order-by clause after it is aliased", testDescription );
		return false;
	}
	return true;
}
 

/**
 * Does the db/dialect support using a column's physical name in the order-by clause
 * even after it has been aliased in the select clause.  This is not actually
 * required by the SQL spec, although virtually ever DB in the world supports this
 * (the most glaring omission here being IBM-variant DBs ala DB2 and Derby).
 *
 * @param testDescription description of the scenario being tested.
 * @return true if is allowed
 */
protected boolean allowsPhysicalColumnNameInOrderby(String testDescription) {
	if ( DB2Dialect.class.isInstance( getDialect() ) ) {
		// https://issues.apache.org/jira/browse/DERBY-1624
		reportSkip( "Dialect does not support physical column name in order-by clause after it is aliased", testDescription );
		return false;
	}
	return true;
}