springboot 整合 JdbcTemplate

前言

Spring 对数据库的操作在jdbc上面做了封装JdbcTemplate。

demo 以 springboot 整合 jdbcTemplate 的使用

pom.xml

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.2.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>com.opencsv</groupId>
			<artifactId>opencsv</artifactId>
			<version>3.3</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

启动类

@SpringBootApplication
@Slf4j
public class SimpleJdbcDemoApplication implements CommandLineRunner {

    @Autowired
    private CoffeeService coffeeService;

    public static void main(String[] args) {
        SpringApplication.run(SimpleJdbcDemoApplication.class, args);
    }

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

    @Override
    public void run(String... args) throws Exception {
        // 查询
        coffeeService.query();
        String[] data = new String[] { "moka", "10.5" };
        // 插入
        coffeeService.insert(data);
    }

}

数据源配置

使用 druid 数据源

@ComponentScan
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceConfig {
    private String url;
    private String username;
    private String password;
    
    @Bean
    public DataSource getDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);// 用户名
        dataSource.setPassword(password);// 密码
        return dataSource;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

coffeeService 服务层

在 service 里直接调用了 jdbcTemplate

@Service
public class CoffeeService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insert(String[] args) {
        String sql = "insert into t_coffee (name, price) values (?, ?)";
        jdbcTemplate.update(sql, new Object[] { args[0], args[1] });
    }

    public void query() {
        String sql = "select * from t_coffee";
        System.out.println(jdbcTemplate.queryForList(sql));
    }
}

运行结果：

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.2.RELEASE)

2019-08-07 21:50:44.750  INFO 15814 --- [           main] com.beng.SimpleJdbcDemoApplication       : Starting SimpleJdbcDemoApplication on 127.0.0.1 with PID 15814 (/Users/apple/project/spring-bucket/spring-jdbc-demo/target/classes started by apple in /Users/apple/project/spring-bucket/spring-jdbc-demo)
2019-08-07 21:50:44.753  INFO 15814 --- [           main] com.beng.SimpleJdbcDemoApplication       : No active profile set, falling back to default profiles: default
2019-08-07 21:50:45.564  INFO 15814 --- [           main] com.beng.SimpleJdbcDemoApplication       : Started SimpleJdbcDemoApplication in 1.101 seconds (JVM running for 1.418)
2019-08-07 21:50:45.599  INFO 15814 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
[{id=1, name=espresso, price=20.0, create_time=2019-05-19 22:18:14.0, update_time=2019-05-19 22:18:14.0}, {id=2, name=latte, price=25.0, create_time=2019-05-19 22:18:14.0, update_time=2019-05-19 22:18:14.0}]
2019-08-07 21:50:45.918  INFO 15814 --- [       Thread-2] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed