背景:win10、java11、IDEA、mysqlmysql-8.0.16-winx64、springboot2.1.7RELEASE
通过JDBC方式
1. 创建工程spring-boot-template,pom.xml依赖如下
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zsx</groupId>
<artifactId>spring-boot-template</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>11</java.version>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.7.RELEASE</version>
<relativePath />
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- junit5运行所需jar包 -->
<dependency>
<groupId>org.junit.platform</groupId>
<artifactId>junit-platform-launcher</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<scope>test</scope>
</dependency>
<!-- 引入mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<mainClass>com.zsx.SpringBootTemplateApplication</mainClass>
</configuration>
</plugin>
</plugins>
</project>
2. application.yml文件
server:
port: 8080
spring:
application:
name: event
datasource:
username: root
password: 1234
# 如果自定义 hikari DataSource,url要改为jdbc-url
# url: jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
jdbc-url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
hikari:
maximum-pool-size: 20
max-lifetime: 1800000
idle-timeout: 30000
data-source-properties:
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
cachePrepStmts: true
useServerPrepStmts: true
first-datasource:
username: root
password: 1234
jdbc-url: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
hikari:
maximum-pool-size: 20
max-lifetime: 1800000
idle-timeout: 30000
data-source-properties:
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
cachePrepStmts: true
useServerPrepStmts: true
3. 数据库配置
package com.zsx.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DataBaseConfig {
@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "firstDataSource")
@ConfigurationProperties(prefix = "spring.first-datasource")
public DataSource firstDataSource() {
return DataSourceBuilder.create().build();
}
}
4. JDBCTemplate配置
package com.zsx.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class JdbcTemplateConfig {
@Bean
@Primary
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource primaryDataSource) {
return new JdbcTemplate(primaryDataSource);
}
@Bean(name = "firstJdbcTemplate")
public JdbcTemplate firstJdbcTemplate(@Qualifier("firstDataSource") DataSource firstDataSource) {
return new JdbcTemplate(firstDataSource);
}
}
5. 用户实体类
package com.zsx.entity;
import java.io.Serializable;
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
6. 数据接口配置
package com.zsx.dao;
import com.zsx.entity.User;
import com.zsx.model.UserRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
@Qualifier("firstJdbcTemplate")
private JdbcTemplate firstJdbcTemplate;
public User findById(Integer id){
String sql = "SELECT id, name FROM user WHERE id = ?";
User user = (User)jdbcTemplate.queryForObject(sql, new Object[] { id }, new UserRowMapper());
return user;
}
public User findById1(Integer id){
String sql = "SELECT id, name FROM user WHERE id = ?";
User user = (User)firstJdbcTemplate.queryForObject(sql, new Object[] { id }, new UserRowMapper());
return user;
}
}
7. 引导类
package com.zsx;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
public class SpringBootTemplateApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootTemplateApplication.class, args);
}
}
8. UserDao测试类
package com.zsx.test.dao;
import com.zsx.dao.UserDao;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
@ExtendWith(SpringExtension.class)
@SpringBootTest
public class UserDaoTest {
@Autowired
private UserDao userDao;
@Test
void testFindById() {
System.out.println(userDao.findById(1));
System.out.println(userDao.findById1(1));
}
}
9. 运行测试方法,查看测试结果
到此配置结束