How to make a db connection using spring jdbc?

Question

I am trying to do a simple connection using spring jdbc that returns a type Connection , at my project I am using spring jdbc with spring data, automatically configured.

At my code, I need to return this connection (with my local info).

Is it possible to do it? and in the case it is, is it possible to get the information that is being used at the moment? (I mean, dbname, password and so on..)

Thanks

EDIT------

The original dataSource bean looks like this

 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="url" value="jdbc:mysql://${jdbc.host}:${jdbc.port}"/>
 </bean>

But I cannot make it with annotations, any idea?

Ive tried to do this

DriverManagerDataSource source = new org.springframework.jdbc.datasource.DriverManagerDataSource();

THE ERROR

I Keep getting that this

Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: 'dataSource' or 'jdbcTemplate' is required

This class extends of JdbcDaoSupport that seems like it needs of it...

Here is the JdbcDaoSupport.class

package org.springframework.jdbc.core.support;

import java.sql.Connection;
import javax.sql.DataSource;
import org.springframework.dao.support.DaoSupport;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.SQLExceptionTranslator;

public abstract class JdbcDaoSupport extends DaoSupport {
    private JdbcTemplate jdbcTemplate;

    public JdbcDaoSupport() {
    }

    public final void setDataSource(DataSource dataSource) {
        if(this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
            this.jdbcTemplate = this.createJdbcTemplate(dataSource);
            this.initTemplateConfig();
        }

    }

    protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    public final DataSource getDataSource() {
        return this.jdbcTemplate != null?this.jdbcTemplate.getDataSource():null;
    }

    public final void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.initTemplateConfig();
    }

    public final JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }

    protected void initTemplateConfig() {
    }

    protected void checkDaoConfig() {
        if(this.jdbcTemplate == null) {
            throw new IllegalArgumentException("\'dataSource\' or \'jdbcTemplate\' is required");
        }
    }

    protected final SQLExceptionTranslator getExceptionTranslator() {
        return this.getJdbcTemplate().getExceptionTranslator();
    }

    protected final Connection getConnection() throws CannotGetJdbcConnectionException {
        return DataSourceUtils.getConnection(this.getDataSource());
    }

    protected final void releaseConnection(Connection con) {
        DataSourceUtils.releaseConnection(con, this.getDataSource());
    }
}

Ive declared this class as @bean and do this

public DriverManagerDataSource provideSource() {
    DriverManagerDataSource dataSource = new org.springframework.jdbc.datasource.DriverManagerDataSource();
    //this.dataSource = dataSource;
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUsername("user");
    dataSource.setPassword("pass");
    dataSource.setUrl("jdbc:mysql://localhost:3306/db");
    return dataSource;
}


@Bean
MyClientDao myClientDao(){
    MyClientDao myClientDao = new MyClientDao();
    myClientDao().setDatabaseName("db");
    myClientDao().setDataSource(provideSource());
    return myClientDao();
}

Any idea how to do it?

Answer 1

The exception indicates that the JdbcTemplate is null. (code extract from the JdbcDaoSupport.class)

protected void checkDaoConfig() {
    if(this.jdbcTemplate == null) {
        throw new IllegalArgumentException("\'dataSource\' or \'jdbcTemplate\' is required");
    }
}

You need to wire the DAO (which is extending the JdbcDaoSupport) with the dataSource (bean)

If you create the following dao class:

public class CustomDaoImpl extends JdbcDaoSupport implements CustomDao {
...<implementation>
}

Your configuration would be:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="url" value="jdbc:mysql://${jdbc.host}:${jdbc.port}"/>
 </bean>

<bean id="myClientDao" class="package.MyClientDao">
    <property name="dataSource" ref="dataSource" />
</bean>

This way the JdbcTemplate will be initialized correctly.

With annotations, this will look like the following:

@Configuration
public class ConfigBean {
    @Bean
    public DriverManagerDataSource dataSource() {
        DriverManagerDataSource dataSource = new org.springframework.jdbc.datasource.DriverManagerDataSource();
        //this.dataSource = dataSource;
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUsername("user");
        dataSource.setPassword("pass");
        dataSource.setUrl("jdbc:mysql://localhost:3306/db");
        return dataSource;
    }

    @Bean
    public MyClientDao myClientDao(){
        MyClientDao myClientDao = new MyClientDao();
        myClientDao.setDataSource(dataSource());
        return myClientDao;
    }
}

Do not forget to add the @Bean annotation to the dataSource.

Sidenotes:

• spring uses 'dataSource' as the 'default datasource' name
• When working with dao/repository, the dataSource is mandatory, so spring advises you to add the datasource as a parameter to the constructor of your dao

Answer 2

To Mae JDBC connection ( Using Spring Boot ) You need to first:

1. Define DB properties in .properties file.
2. Then Use JDBCTemplate Class for DB connection.

A Step by step implementation is given here.