1 环境准备

数据库使用MySQL,我们先创建一个SpringBoot项目再导入MySQL驱动:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.137.140:3306/mysql
    driver-class-name: com.mysql.jdbc.Driver

2 测试代码

package cn.zhangyu;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

import static sun.misc.Version.println;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootJdbcApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    public void contextLoads() throws SQLException {
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
    connection.close();

    }

}

3 执行sql语句

DataSourceInitializer:ApplicationListener;可以帮助我们执行sql语句,但是需要将文件命名为:schema-.sql、data-.sql
默认规则:schema.sql,schema-all.sql;(DataSourceProperties这个类进行了默认的配置)

DataSourceInitializer作用:

1)、runSchemaScripts();运行建表语句;

2)、runDataScripts();运行插入数据的sql语句;
  • 实例;
  1. 再pom中添加

    #    如果你配置文件没有指定执行文件的名称而是使用默认的schema.sql或者schema-all.sql的话
    #    就在配置文件中加上spring.datasource.initialization-mode=always
       initialization-mode: always
    
  2. 我们再resources目录下放入schema.sql

    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for department
    -- ----------------------------
    DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `departmentName` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    INSERT INTO department(id,departmentName) VALUES(1,'业务部');
    INSERT INTO department(id,departmentName) VALUES(2,'前台部');
    
  3. 查看结果

    mysql> select * from department;
    +----+----------------+
    | id | departmentName |
    +----+----------------+
    |  1 | 业务部      |
    |  2 | 前台部      |
    +----+----------------+
    2 rows in set (0.00 sec)
    --------------------- 
  4. 编写controller 进行测试

    package cn.zhangyu;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    import java.util.Map;
    
    /**
    * Created by grace on 2018/11/28.
    */
    @RestController
    public class HelloController {
    
       //spring boot中已经给我们注册好JdbcTemplate
       @Autowired
       JdbcTemplate jdbcTemplate;
    
       @GetMapping("/hello")
       public Map<String,Object> getDepartment(){
           List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department");
           return list.get(0);
       }
    }
    

访问http://localhost:8080/hello:
{"id":1,"departmentName":"业务部"}

  • 自定义sql执行
#可以使用   
    schema:
      - classpath:department.sql

schema是一个list所以可以使用- 指定多个位置