POSTGRESQL error working with Spring MVC/Hibernate 'Large Objects may not be used in auto-commit mode'

Question

Of course this one seems easy enough per Here but I added the following to my application context...

<property name="dataSource">
    <ref bean="dataSource"/>
</property>

<property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.autocommit">false</prop>
        </props>
    </property>
    <property name="annotatedClasses">
        <list>
            <value>com.gleason.gt.server.model.database.PlayListEntry</value>
            <value>com.gleason.gt.server.model.database.MusicFile</value>
        </list>
    </property>
</bean>

I still get the same error, any ideas?

UPDATE Adding Service/DAO

@Repository("musicFileDao")
public class MusicFileDAOImpl extends GroovyTimeHibernateDAOSupport{
@Transactional()
public MusicFile getMusicFile(Integer i){
    @SuppressWarnings("unchecked")
    List<MusicFile> returnValue = (List<MusicFile>)getHibernateTemplate().find("from MusicFile where id=?",i);
    if(returnValue.size()>0){
        return returnValue.get(0);
    }
    return null;
}
}
@Service("musicFileService")
public class MusicFileService {
@Autowired
private MusicFileDAOImpl dao;

@Transactional
public MusicFile getMusicFile(Integer i){
    return dao.getMusicFile(i);
}
}

@Lob
@Column(name="file")
private byte[] file;

Same comment from me as in the other quesion: I'm pretty sure you don't want LargeObjects but bytea instead. — user330315
– user330315, Commented Oct 10, 2011 at 19:58
Consult the manual on large objects. They are quite different from anything else. — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 10, 2011 at 20:02
Thanks for the comments I thought I could use LargeObject for a byte array? I tried to look at the link but a little over my head. In other news I tried to isolate it. I took out the OneToOne mapping and went to a straight up access. I still get the error, I have tried making it transactional, etc. So Should I grind and bear it through the link? — Jackie
– Jackie, Commented Oct 11, 2011 at 1:35

Chris Travers · Accepted Answer · 2013-03-22 09:26:39Z

2

The basic thing is that a large object is its own thing quite different from bytea. If you are looking for a byte array, use bytea instead.

A large object is an independent database entity which is saved using a different interface, and then referenced using an object id. This requires two SQL statements and hence it is not safe in autocommit mode. Large objects are preferred for very large fields for two reasons:

You can store twice as much in a large object than you can a bytea, and
Large objects come with a streaming interface, allowing seek by offset and the like. They operate much more like files than like database attributes.

Large objects are really useful in the right places, but they are somewhat unusual. Chances are you want bytea.

answered Mar 22, 2013 at 9:26

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hinotori · Accepted Answer · 2020-06-01 03:46:47Z

0

Using Spring boot 2+ There are two ways:

Create a configuration class that configures the datasource, and change Hikari behavior

@Configuration
public class DataSourceConfiguration {

    // (...) some @Value(s) (...)

    @Bean
    public DataSource getDataSource() {

        HikariConfig config = new HikariConfig();
        config.setDriverClassName(driverClassName);   
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);

        // these are optional
        config.setMinimumIdle(minumunIdle);
        config.setMaximumPoolSize(maximunPoolSize);
        config.setPoolName(poolName);
        config.setConnectionTimeout(connectionTimeout);
        config.setIdleTimeout(idleTimeout);
        config.setMaxLifetime(maxLifetime);

        // sets auto-commit to false
        config.setAutoCommit(false);

        return new HikariDataSource(config);
    }
}

or in application.properties, this key/value can be set:

spring.datasource.hikari.auto-commit=false

answered Jun 1, 2020 at 3:46

Hinotori

5826 silver badges16 bronze badges

1 Comment

kapellan Over a year ago

When use multiple datasources bean config applies

Collectives™ on Stack Overflow

POSTGRESQL error working with Spring MVC/Hibernate 'Large Objects may not be used in auto-commit mode'

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related