Proper way to use JDBI and Spring together

[Matty Southall]

Hi,

I am looking for a guide to use JDBI object api with Spring and a way to have transactions working properly. I have this code,

@RegisterMapper(UserMapper.class)
public abstract class UserDao implements CrudRepository<User> {

    @Override
    @SqlQuery("select * from users where id = :id")
    public abstract User retrieve(@Bind("id") Long id);

    @Override
    @SqlQuery("select count(*) from users")
    public abstract long count();

    @Override
    @SqlQuery("select exists (select 1 from users where id = :id)")
    @RegisterMapper(ExistsMapper.class)
    public abstract boolean exists(@Bind("id") Long id);

    @Override
    @SqlUpdate("update users set username = :u.username, email = :u.email where id = :u.id limit 1")
    @Transaction
    @GetGeneratedKeys
    public abstract long update(@BindBean("u") User instance);

    @Override
    @Transaction
    @GetGeneratedKeys
    public long insert(User instance) {
        return 0;
    }

    @Override
    @SqlUpdate("delete from users where id = :id")
    public abstract void delete(@Bind("id") Long id);

    @SqlQuery("select * from users where email = :email")
    public abstract User retrieveByEmail(@Bind("email") String email);
}

But I am looking on how I can use Autowired features with JDBI.

Is there a full guide including example repositories etc I can use to look how to setup jdbi properly?

[Brian McCallister]

I don't know of any full guides or examples of recent Spring and JDBI, sorry.

-Brian

> --
> You received this message because you are subscribed to the Google Groups
> "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Peter Garbar]

I know this is an old question, but I would like to know if this is still the same even for JDBI3?

I would love to use jdbi with spring on my new project, but I found out that the spring part in the docs is not done yet. Is here anyone who can give me some guide/example/... on how to set up jdbi to work with Autowired features?

Thanks

Dňa pondelok, 29. júna 2015 1:46:30 UTC+2 Brian McCallister napísal(-a):

[Matthew Hall]

Hi Peter,

I was just revising the Spring package docs the other day. It's not merged yet but here's a direct link:

https://github.com/jdbi/jdbi/blob/6dce8a9a7d1be500428458314ee258dbec37185e/spring4/src/main/java/org/jdbi/v3/spring4/package.html

Hope this helps,

-Matt

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Peter Garbar]

Hi,
thanks for the quick reply. I used your link together with the test and seems it is finally working. I don't use xml to configure spring, so I had to adapt it into a class. I think there is a small mistake in the package.html - there is still the DummyService from v2. But it doesn't change anything in the way it should be used.

Dňa streda, 12. apríla 2017 7:54:41 UTC+2 Matthew Hall napísal(-a):

[Matthew Hall]

Would you mind submitted a small PR for that error? Also it would be great if you could provide an example for Spring using a class instead of XML--I'd be happy to incorporate it into the docs.

-Matt

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Peter Garbar]

Hi,
here is my code:

@Configuration
@EnableTransactionManagement
@PropertySource(value = { "classpath:application.properties" })
public class JdbiConfiguration {
    @Autowired
    private Environment environment;
 
    @Bean
    public JdbiFactoryBean jdbiFactory() {
        return new JdbiFactoryBean().setDataSource(dataSource());
    }
    
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(environment.getRequiredProperty("jdbi.driverClassName"));
        dataSource.setUrl(environment.getRequiredProperty("jdbi.url"));
        dataSource.setUsername(environment.getRequiredProperty("jdbi.username"));
        dataSource.setPassword(environment.getRequiredProperty("jdbi.password"));
        return dataSource;
    }
 
    @Bean
    @Autowired
    public DataSourceTransactionManager transactionManager()
    {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dataSource());
        return txManager;
    }
}

But I have to say that I'm new to Spring, so feel free to teach me if there is something wrong with it.
Thanks

Peter

Dňa štvrtok, 13. apríla 2017 7:12:18 UTC+2 Matthew Hall napísal(-a):

[Peter Garbar]

Hi,
I have another question. I want to use SQL Object API and I'm not sure, if I use it the correct way. It works, but maybe it's not meant to be used this way.
Here is my code:

Configuration:
    @Bean
    public JdbiFactoryBean jdbiFactory() {
        Vector<JdbiPlugin> plugins = new Vector<JdbiPlugin>();
        plugins.add(new SqlObjectPlugin());
        return new JdbiFactoryBean().setDataSource(dataSource()).setPlugins(plugins);
    }

Dao:
@Repository("searchStructDao")
public interface ISearchStructDao
{
    @SqlQuery("SELECT * FROM \"SearchStruct\"")
    @RegisterBeanMapper(SearchStruct.class)
    List<SearchStruct> findAll();
}

Service:
@Service("searchStructService")
@Transactional("jdbiTransactionManager")
public class SearchStructService
{
    @Autowired
    private Jdbi db;
   
    public List<SearchStruct> findAll() {
        Handle handle = JdbiUtil.getHandle(db);
        ISearchStructDao dao = handle.attach(ISearchStructDao.class);
        return dao.findAll();
    }

}

The main question is about the findAll method in SearchStructService. If I use the way from docs(jdbi.withExtension()) I got an error, that a Handle is closed, but a transaction is still open. I don't know if that is a bug or if there is some other way it should be used(perhaps mine? :) ). Thanks for all answers.

Peter

Dňa štvrtok, 13. apríla 2017 7:24:24 UTC+2 Peter Garbar napísal(-a):

[Steven Schlansker]

Your use here looks like a decent use case for injecting an onDemand style ISearchStructDao.

[Peter Garbar]

I tried to use onDemand too, but I got the same error as with withExtension.

Dňa štvrtok, 20. apríla 2017 18:47:27 UTC+2 Steven Schlansker napísal(-a):

[Matthew Hall]

What does JdbiUtil.getHandle look like? Is it starting a transaction? That error sounds like you've called begin() without a matching rollback() or commit().

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Brian McCallister]

IIRC, and no longer applicable in v3, I suspect, in the ancient version of spring when I first implemented that spring integration, Spring had conventions for handling open connections on a given thread, and this hooked into those conventions. I was young and foolish in those days, so don't trust the code if it is what I wrote :-)

-Brian

[Peter Garbar]

When I change my service like this

    public List<SearchStruct> findAll() {      
        ISearchStructDao dao = jdbi.onDemand(ISearchStructDao.class);
        return dao.findAll();
    }

I get this error:

apr 22, 2017 12:18:09 AM org.springframework.transaction.interceptor.TransactionInterceptor completeTransactionAfterThrowing
SEVERE: Application exception overridden by rollback exception
org.jdbi.v3.core.transaction.TransactionException: Improper transaction handling detected: A Handle with an open transaction was closed. Transactions must be explicitly committed or rolled back before closing the Handle. JDBI has rolled back this transaction automatically.
    at org.jdbi.v3.core.Handle.close(Handle.java:128)
    at org.jdbi.v3.core.LazyHandleSupplier.close(LazyHandleSupplier.java:86)
    at org.jdbi.v3.core.Jdbi.withExtension(Jdbi.java:430)
    at org.jdbi.v3.core.OnDemandExtensions.lambda$create$1(OnDemandExtensions.java:57)
    at com.sun.proxy.$Proxy76.findAll(Unknown Source)
    at sk.prosoft.eshop.service.SearchStructService.findAll(SearchStructService.java:30)

I don't know if the is problem in my implementation or in JDBI code. Thanks for any tips.

Peter

Dňa piatok, 21. apríla 2017 22:38:28 UTC+2 Brian McCallister napísal(-a):

[Matthew Hall]

Unlike v2, on-demand SQL objects in v3 no longer "hold the door open" for outstanding operations. This is to close the loop on resource cleanup so that it's very hard to e.g. leak a database connection.

If you need to do multiple operations at once on an on-demand SQL object, you would call e.g. `myDao.inTransaction(dao -> { dao.doStuff(); dao.doOtherStuff(); }`. 

However we noticed that the `Transactional` was a problem with on-demand SQL objects: if you call `dao.begin()`, it would start a transaction, and immediately commit it before the `begin()` method returns. Successive calls would execute in isolation instead of in a transaction.

We added this exception in v3 to detect this scenario, and warn users.

I don't use Spring, so am largely ignorant about what `jdbi3-spring4` is doing. Can anybody explain what JdbiUtil.getHandle() is supposed to do? What is TransactionSynchronizationManager about? It appears that method is just opening a handle, and possibly opening a transaction on it--in which case that would definitely explain your error.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.

[Brian McCallister]

Spring has (had?) some threadlical handling that it's hibernate, jdbc template, etc used. The old spring stuff basically just hooked into that to get the connection.