org.hibernate.dialect.PostgreSQLDialect 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
 */
@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;
	}
}
 

/**
 * Copied from {@link HQLTest#testExpressionWithParamInFunction}
 */
public void testExpressionWithParamInFunction() {
	Session s = openSession();
	s.beginTransaction();
	s.createQuery( "from Animal a where abs(a.bodyWeight-:param) < 2.0" ).setLong( "param", 1 ).list();
	s.createQuery( "from Animal a where abs(:param - a.bodyWeight) < 2.0" ).setLong( "param", 1 ).list();
	if ( ! ( getDialect() instanceof HSQLDialect ) ) {
		// HSQLDB does not like the abs(? - ?) syntax...
		s.createQuery( "from Animal where abs(:x - :y) < 2.0" ).setLong( "x", 1 ).setLong( "y", 1 ).list();
	}
	s.createQuery( "from Animal where lower(upper(:foo)) like 'f%'" ).setString( "foo", "foo" ).list();
	s.createQuery( "from Animal a where abs(abs(a.bodyWeight - 1.0 + :param) * abs(length('ffobar')-3)) = 3.0" ).setLong( "param", 1 ).list();
	s.createQuery( "from Animal where lower(upper('foo') || upper(:bar)) like 'f%'" ).setString( "bar", "xyz" ).list();
	if ( ! ( getDialect() instanceof PostgreSQLDialect || getDialect() instanceof MySQLDialect ) ) {
		s.createQuery( "from Animal where abs(cast(1 as float) - cast(:param as float)) = 1.0" ).setLong( "param", 1 ).list();
	}
	s.getTransaction().commit();
	s.close();
}
 

public void testExpressionWithParamInFunction() {
	assertTranslation("from Animal a where abs(a.bodyWeight-:param) < 2.0");
	assertTranslation("from Animal a where abs(:param - a.bodyWeight) < 2.0");
	assertTranslation("from Animal where abs(:x - :y) < 2.0");
	assertTranslation("from Animal where lower(upper(:foo)) like 'f%'");
	if ( ! ( getDialect() instanceof SybaseDialect ) ) {
		// SybaseDialect maps the length function -> len; classic translator does not consider that *when nested*
		assertTranslation("from Animal a where abs(abs(a.bodyWeight - 1.0 + :param) * abs(length('ffobar')-3)) = 3.0");
	}
	if ( !( getDialect() instanceof MySQLDialect || getDialect() instanceof SybaseDialect ) ) {
		assertTranslation("from Animal where lower(upper('foo') || upper(:bar)) like 'f%'");
	}
	if ( getDialect() instanceof PostgreSQLDialect ) {
		return;
	}
	assertTranslation("from Animal where abs(cast(1 as float) - cast(:param as float)) = 1.0");
}
 

@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
	final Dialect dialect = criteriaQuery.getFactory().getDialect();
	final String[] columns = criteriaQuery.findColumns( propertyName, criteria );
	if ( columns.length != 1 ) {
		throw new HibernateException( "ilike may only be used with single-column properties" );
	}
	if ( dialect instanceof PostgreSQLDialect || dialect instanceof PostgreSQL81Dialect) {
		return columns[0] + " ilike ?";
	}
	else {
		return dialect.getLowercaseFunction() + '(' + columns[0] + ") like ?";
	}
}
 

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
	Dialect dialect = criteriaQuery.getFactory().getDialect();
	String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
	if (columns.length!=1) throw new HibernateException("ilike may only be used with single-column properties");
	if ( dialect instanceof PostgreSQLDialect ) {
		return columns[0] + " ilike ?";
	}
	else {
		return dialect.getLowercaseFunction() + '(' + columns[0] + ") like ?";
	}

	//TODO: get SQL rendering out of this package!
}
 

public void testGroupByFunction() {
	if ( getDialect() instanceof Oracle9Dialect ) return;
	if ( getDialect() instanceof Oracle8iDialect ) return; // the new hiearchy...
	if ( getDialect() instanceof PostgreSQLDialect ) return;
	assertTranslation( "select count(*) from Human h group by year(h.birthdate)" );
	assertTranslation( "select count(*) from Human h group by trunc( sqrt(h.bodyWeight*4)/2 )" );
	assertTranslation( "select count(*) from Human h group by year(sysdate)" );
}
 

public void testLazy() {
	if ( getDialect() instanceof PostgreSQLDialect ) {
		return; //doesn't like boolean=1
	}

	Session s = openSession();
	Transaction t = s.beginTransaction();
	Auction a = new Auction();
	a.setDescription( "an auction for something" );
	a.setEnd( new Date() );
	Bid b = new Bid();
	b.setAmount( new BigDecimal( 123.34 ).setScale( 19, BigDecimal.ROUND_DOWN ) );
	b.setSuccessful( true );
	b.setDatetime( new Date() );
	b.setItem( a );
	a.getBids().add( b );
	a.setSuccessfulBid( b );
	s.persist( b );
	t.commit();
	s.close();

	Long aid = a.getId();
	Long bid = b.getId();

	s = openSession();
	t = s.beginTransaction();
	b = ( Bid ) s.load( Bid.class, bid );
	assertFalse( Hibernate.isInitialized( b ) );
	a = ( Auction ) s.get( Auction.class, aid );
	assertFalse( Hibernate.isInitialized( a.getBids() ) );
	assertTrue( Hibernate.isInitialized( a.getSuccessfulBid() ) );
	assertSame( a.getBids().iterator().next(), b );
	assertSame( b, a.getSuccessfulBid() );
	assertTrue( Hibernate.isInitialized( b ) );
	assertTrue( b.isSuccessful() );
	t.commit();
	s.close();

	s = openSession();
	t = s.beginTransaction();
	b = ( Bid ) s.load( Bid.class, bid );
	assertFalse( Hibernate.isInitialized( b ) );
	a = ( Auction ) s.createQuery( "from Auction a left join fetch a.bids" ).uniqueResult();
	assertTrue( Hibernate.isInitialized( b ) );
	assertTrue( Hibernate.isInitialized( a.getBids() ) );
	assertSame( b, a.getSuccessfulBid() );
	assertSame( a.getBids().iterator().next(), b );
	assertTrue( b.isSuccessful() );
	t.commit();
	s.close();

	s = openSession();
	t = s.beginTransaction();
	b = ( Bid ) s.load( Bid.class, bid );
	a = ( Auction ) s.load( Auction.class, aid );
	assertFalse( Hibernate.isInitialized( b ) );
	assertFalse( Hibernate.isInitialized( a ) );
	s.createQuery( "from Auction a left join fetch a.successfulBid" ).list();
	assertTrue( Hibernate.isInitialized( b ) );
	assertTrue( Hibernate.isInitialized( a ) );
	assertSame( b, a.getSuccessfulBid() );
	assertFalse( Hibernate.isInitialized( a.getBids() ) );
	assertSame( a.getBids().iterator().next(), b );
	assertTrue( b.isSuccessful() );
	t.commit();
	s.close();

	s = openSession();
	t = s.beginTransaction();
	b = ( Bid ) s.load( Bid.class, bid );
	a = ( Auction ) s.load( Auction.class, aid );
	assertFalse( Hibernate.isInitialized( b ) );
	assertFalse( Hibernate.isInitialized( a ) );
	assertSame( s.get( Bid.class, bid ), b );
	assertTrue( Hibernate.isInitialized( b ) );
	assertSame( s.get( Auction.class, aid ), a );
	assertTrue( Hibernate.isInitialized( a ) );
	assertSame( b, a.getSuccessfulBid() );
	assertFalse( Hibernate.isInitialized( a.getBids() ) );
	assertSame( a.getBids().iterator().next(), b );
	assertTrue( b.isSuccessful() );
	t.commit();
	s.close();
}
 

public void testMixedInheritance() {
	Session s = openSession( new DocumentInterceptor() );
	Transaction t = s.beginTransaction();
	Folder f = new Folder();
	f.setName( "/" );
	s.save( f );

	Document d = new Document();
	d.setName( "Hibernate in Action" );
	d.setContent( Hibernate.createBlob( "blah blah blah".getBytes() ) );
	d.setParent( f );
	Long did = (Long) s.save( d );

	SecureDocument d2 = new SecureDocument();
	d2.setName( "Secret" );
	d2.setContent( Hibernate.createBlob( "wxyz wxyz".getBytes() ) );
	d2.setPermissionBits( (byte) 664 );
	d2.setOwner( "gavin" );
	d2.setParent( f );
	Long d2id = (Long) s.save( d2 );

	t.commit();
	s.close();

	if ( getDialect() instanceof PostgreSQLDialect ) return;

	s = openSession( new DocumentInterceptor() );
	t = s.beginTransaction();
	Item id = (Item) s.load( Item.class, did );
	assertEquals( did, id.getId() );
	assertEquals( "Hibernate in Action", id.getName() );
	assertEquals( "/", id.getParent().getName() );

	Item id2 = (Item) s.load( Item.class, d2id );
	assertEquals( d2id, id2.getId() );
	assertEquals( "Secret", id2.getName() );
	assertEquals( "/", id2.getParent().getName() );

	id.setName( "HiA" );

	d2 = (SecureDocument) s.load( SecureDocument.class, d2id );
	d2.setOwner( "max" );

	s.flush();

	s.clear();

	d = (Document) s.load( Document.class, did );
	assertEquals( did, d.getId() );
	assertEquals( "HiA", d.getName() );
	assertNotNull( d.getContent() );
	assertEquals( "/", d.getParent().getName() );
	assertNotNull( d.getCreated() );
	assertNotNull( d.getModified() );

	d2 = (SecureDocument) s.load( SecureDocument.class, d2id );
	assertEquals( d2id, d2.getId() );
	assertEquals( "Secret", d2.getName() );
	assertNotNull( d2.getContent() );
	assertEquals( "max", d2.getOwner() );
	assertEquals( "/", d2.getParent().getName() );
	assertEquals( (byte) 664, d2.getPermissionBits() );
	assertNotNull( d2.getCreated() );
	assertNotNull( d2.getModified() );

	s.delete( d.getParent() );
	s.delete( d );
	s.delete( d2 );

	t.commit();
	s.close();
}
 

public void testInterfaceProxies() {
	
	if ( getDialect() instanceof PostgreSQLDialect ) {
		// TODO : why?
		return;
	}
	
	Session s = openSession( new DocumentInterceptor() );
	Transaction t = s.beginTransaction();
	Document d = new DocumentImpl();
	d.setName("Hibernate in Action");
	d.setContent( Hibernate.createBlob( "blah blah blah".getBytes() ) );
	Long did = (Long) s.save(d);
	SecureDocument d2 = new SecureDocumentImpl();
	d2.setName("Secret");
	d2.setContent( Hibernate.createBlob( "wxyz wxyz".getBytes() ) );
	d2.setPermissionBits( (byte) 664 );
	d2.setOwner("gavin");
	Long d2id = (Long) s.save(d2);
	t.commit();
	s.close();

	s = openSession( new DocumentInterceptor() );
	t = s.beginTransaction();
	d = (Document) s.load(ItemImpl.class, did);
	assertEquals( did, d.getId() );
	assertEquals( "Hibernate in Action", d.getName() );
	assertNotNull( d.getContent() );
	
	d2 = (SecureDocument) s.load(ItemImpl.class, d2id);
	assertEquals( d2id, d2.getId() );
	assertEquals( "Secret", d2.getName() );
	assertNotNull( d2.getContent() );
	
	s.clear();
	
	d = (Document) s.load(DocumentImpl.class, did);
	assertEquals( did, d.getId() );
	assertEquals( "Hibernate in Action", d.getName() );
	assertNotNull( d.getContent() );
	
	d2 = (SecureDocument) s.load(SecureDocumentImpl.class, d2id);
	assertEquals( d2id, d2.getId() );
	assertEquals( "Secret", d2.getName() );
	assertNotNull( d2.getContent() );
	assertEquals( "gavin", d2.getOwner() );
	
	//s.clear();
	
	d2 = (SecureDocument) s.load(SecureDocumentImpl.class, did);
	assertEquals( did, d2.getId() );
	assertEquals( "Hibernate in Action", d2.getName() );
	assertNotNull( d2.getContent() );
	
	try {
		d2.getOwner(); //CCE
		assertFalse(true);
	}
	catch (ClassCastException cce) {
		//correct
	}

	s.createQuery( "delete ItemImpl" ).executeUpdate();
	t.commit();
	s.close();
}
 

public void testFormulaJoin() {
	Session s = openSession();
	Transaction tx = s.beginTransaction();
	Master master = new Master();
	master.setName("master 1");
	Detail current = new Detail();
	current.setCurrentVersion(true);
	current.setVersion(2);
	current.setDetails("details of master 1 blah blah");
	current.setMaster(master);
	master.setDetail(current);
	Detail past = new Detail();
	past.setCurrentVersion(false);
	past.setVersion(1);
	past.setDetails("old details of master 1 yada yada");
	past.setMaster(master);
	s.persist(master);
	s.persist(past);
	s.persist(current);
	tx.commit();
	s.close();
	
	if ( getDialect() instanceof PostgreSQLDialect ) return;

	s = openSession();
	tx = s.beginTransaction();
	List l = s.createQuery("from Master m left join m.detail d").list();
	assertEquals( l.size(), 1 );
	tx.commit();
	s.close();
	
	s = openSession();
	tx = s.beginTransaction();
	l = s.createQuery("from Master m left join fetch m.detail").list();
	assertEquals( l.size(), 1 );
	Master m = (Master) l.get(0);
	assertEquals( "master 1", m.getDetail().getMaster().getName() );
	assertTrue( m==m.getDetail().getMaster() );
	tx.commit();
	s.close();
	
	s = openSession();
	tx = s.beginTransaction();
	l = s.createQuery("from Master m join fetch m.detail").list();
	assertEquals( l.size(), 1 );
	tx.commit();
	s.close();
	
	s = openSession();
	tx = s.beginTransaction();
	l = s.createQuery("from Detail d join fetch d.currentMaster.master").list();
	assertEquals( l.size(), 2 );
	tx.commit();
	s.close();

	s = openSession();
	tx = s.beginTransaction();
	l = s.createQuery("from Detail d join fetch d.currentMaster.master m join fetch m.detail").list();
	assertEquals( l.size(), 2 );
	
	s.createQuery("delete from Detail").executeUpdate();
	s.createQuery("delete from Master").executeUpdate();
	
	tx.commit();
	s.close();

}
 

public void testLoadSaveRepository() throws Exception {

		Connection theConnection = createConnection();

		Class theHibernateDialect = PostgreSQLDialect.class;

		String theModelResource = "/de/erdesignerng/test/io/repository/examplemodel.mxm";

		String theNewFile = RepositioryHelper.performRepositorySaveAndLoad(theModelResource, theHibernateDialect,
				theConnection);

		String theOriginalFile = IOUtils.toString(getClass().getResourceAsStream(theModelResource));

		assertTrue(compareStrings(theOriginalFile, theNewFile));
	}