Ad

Connection To Database With JdbcTemplate

- 1 answer

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>
Ad

Answer

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.

Ad
source: stackoverflow.com
Ad