java 使用 JdbcTemplate 连接数据库

Connection to database with JdbcTemplate

提问by Georgi Stoyanov

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.

我正在尝试使用 jdbcTemplate 创建表 (h2)。当我在 UsersDAOImpl 类中执行不同的查询时一切正常，但是当我尝试首先在 Application 类中创建表时，JdbcTemplate 无法连接到数据库。我读到我需要添加依赖项组 spring-boot-starter-jdbc，它将自动生成数据源以连接我的 JdbcTemplate，但这似乎不起作用。我想我错过了一些东西，但找不到什么。

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:

用户DAOImpl类：

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：

<?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:

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>

采纳答案by Georgi Stoyanov

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

在 Spring Boot 中配置 DataSource 最简单的方法是在 src/main/resources 下创建一个 application.properties 文件，内容如下（可能需要使用正确的 url、用户名和密码更新它）：

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

进一步阅读可以在这个问题中找到：spring boot autoconfiguration with jdbc template autowiring dataSource issue

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

在这种情况下，答案和评论非常有帮助。

回答by Florian Schaetz

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

您似乎在 spring 配置中定义了一个数据源（这就是 DAO 工作的原因），但没有 JdbcTemplate（这可能是您的应用程序没有的原因），因此您可以使用自动装配的数据源自己创建它...

JdbcTemplate jdbcTemplate = new JdbcTemplate( dataSource );

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

...或者在你的 spring 配置中将它定义为一个 bean 并自动装配它。

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

回答by Lovababu

Add below snippet of code in your Application.javaclass, 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.

在您的Application.java类中添加以下代码片段，并在相应的 dml.sql 和 ddl.sql 文件中添加dml（数据操作查询）、ddl（数据定义查询）脚本，确保它们都在类路径中可用。

Remove jdbcTemplate declaration and run() method.

删除 jdbcTemplate 声明和 run() 方法。

@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 .javashould be like this.

你的UsersDAOImpl .java应该是这样的。

public class UsersDAOImpl implements UsersDAO {

 @Autowired
 private JdbcTemplate jdbcTemplate;

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