Connection to database with JdbcTemplate

Question

I'm trying to create table (h2) with jdbcTemplate. Everything is ok when I execute different queries in UsersDAOImpl class, but when I try to create table first in Application class, the JdbcTemplate can't connect to the database. I read that I need to add dependency group spring-boot-starter-jdbc that will automatically generate dataSource to witch my JdbcTemplate should connect, but it seems that doesn't work. I think that I missed something but can't find what.

Application class:

@SpringBootApplication
public class Application implements CommandLineRunner  {

public static void main(String[] args) throws Exception{
    SpringApplication.run(Application.class, args);
}
 //doesn't create db alone;
@Autowired
JdbcTemplate jdbcTemplate;


@Override
public void run(String... arg0) throws Exception {
    jdbcTemplate.execute("DROP TABLE test IF EXISTS");
    jdbcTemplate.execute("CREATE TABLE test( id int(11), name VARCHAR(255),      role VARCHAR(255))");
}
}

UsersDAOImpl class:

public class UsersDAOImpl implements UsersDAO {

private DataSource dataSource;

public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
}
//and some additional methods to work with the database
}

Controller class:

@RestController
class Controller {  

    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
    UsersDAO userDAO = ctx.getBean("userDAO", UsersDAO.class);
 //making the mapping
}

spring.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

<bean id="userDAO" class="hello.UsersDAOImpl">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource" 
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:~/test" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

pom.xml:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>   
</dependencies>

What do you expect to happen, and what happens instead? Be precise. Paste the output you get. — JB Nizet, Dec 28 '15 at 10:11
I dont have any output. I expect the code in application.class to create new database, but it only pass it and doesnt create nothing. — Georgi Stoyanov, Dec 28 '15 at 10:12
So, what do you expect to happen, precisely. Since you don't have any output and don't seem to expect any, what do you expect to happen? How do you realize that what was supposed to happen didn't happen? — JB Nizet, Dec 28 '15 at 10:17
When I log in to test database, there are some previous records, so drop table test if exist doesn't work. If I delete the database manually and then try to run this program, it cannot connect to test table, because there is no test table, so create table test... doesn't work too. — Georgi Stoyanov, Dec 28 '15 at 11:21
You've defined the datasource in a spring.xml file, but I don't see anywhere that you're configuring your app to use that file. See http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#using-boot-importing-xml-configuration. You should avoid XML. Just use the spring properties/yaml file, as explaiend here: http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#boot-features-sql — JB Nizet, Dec 28 '15 at 11:37
I followed your comment and with a bit reading the problem were solved :) In future I will try to avoid xml, the problem were solved when I use application.properties and annotations. THANKS! :) — Georgi Stoyanov, Dec 29 '15 at 09:33

score 1 · Answer 1 · answered Dec 28 '15 at 10:12

You seem have defined a datasource in your spring configuration (which is why the DAO works), but no JdbcTemplate (which is probably why your Application doesn't), so you can either create it yourself with your autowired datasource...

JdbcTemplate jdbcTemplate = new JdbcTemplate( dataSource );

... or define it as a bean in your spring configuration and autowire it.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

I've try to add that bean, but nothing changes, still create table doesn't work. Maybe I should add something more? — Georgi Stoyanov, Dec 28 '15 at 11:38

Lovababu Padala · Answer 2 · 2015-12-28T11:06:58.520

Add below snippet of code in your Application.java class, and add dml(data manipulation queries), ddl(data defining queries) scripts in respective dml.sql and ddl.sql files, make sure they both are available in class path.

Remove jdbcTemplate declaration and run() method.

@Bean
public JdbcTemplate jdbcTemplate() {
   return new JdbcTemplate(dataSource());
}

/**
 * Spring provided H2 Embedded Database. Read the dbscript and initiates the Database with the name H2-Test-DB.
 *
 * @return
 */
@Bean(name = "dataSource")
public DataSource dataSource(){
    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    builder.setName("H2-Test-DB");
    EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.H2)
            .addScript("classpath:db-script/ddl.sql")
            .addScript("classpath:db-script/dml.sql").build();
    log.info("Initiating the database from dbscript.");
    return db;

}

Your UsersDAOImpl .java should be like this.

public class UsersDAOImpl implements UsersDAO {

 @Autowired
 private JdbcTemplate jdbcTemplate;

 //and some additional methods to work with the database
}

score 1 · Accepted Answer · edited May 23 '17 at 11:52

Couldn't find the exact problem with the code, but there is the solution that worked:

The simplest way to configure a DataSource in Spring Boot is to create an application.properties file under src/main/resources with the following content (may need to update it with correct url, username and password):
spring.datasource.url=jdbc:mysql://localhost/:3306/databasename
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Further reading could be found in this question: spring boot autoconfiguration with jdbc template autowiring dataSource issue

The answer and comments are VERY helpful in cases like this.

Connection to database with JdbcTemplate

3 Answers3

Linked