目录
创建Spring Boot项目,可以先了解其创建的两种方式(参考:Spring Boot概览和环境搭建),一般比较喜欢用start.spring.io网站上直接进行创建。项目GitHub地址为:https://github.com/kevin-lihongmin/spring-boot-project-kevin/tree/master/jdbc-demo
1、start.spring.io创建项目
在项目上直接选择Web、Actuator、jdbc、mysql添加到项目中,或者可以直接在项目依赖中添加:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</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-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency>
2、配置信息
配置信息中直接添加 mysql 的相关配置即可,配置信息如下:
# 应用名称 spring.application.name = jdbc-demo # 服务端口 server.port = 8888 # 服务运维端口和相关模块开放配置 management.server.port = 9999 management.endpoint.health.show-details = always management.endpoints.web.exposure.include = * management.cloudfoundry.enabled = true # 数据库配置信息 spring.datasource.url = jdbc:mysql://127.0.0.1:3306/kevin?\ useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT spring.datasource.username = root spring.datasource.password = 123456 spring.datasource.driver-class-name = com.mysql.jdbc.Driver # 数据库连接池类型(spring boot2 之后默认使用Hikari数据库连接池) #spring.datasource.type = com.zaxxer.hikari.HikariDataSource
3、服务代码
1)、启动类(启动类使用默认生成的就可以)
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class JdbcDemoApplication {
public static void main(String[] args) {
SpringApplication.run(JdbcDemoApplication.class, args);
}
}
2)、controller层
import com.kevin.jdbc.entity.User;
import com.kevin.jdbc.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
/**
* 数据库实体{@link User} 控制层
* @author kevin
* @date 2019/7/6 16:05
* @since 1.0.0
*/
@RestController
public class UserController {
@Autowired
private UserService userService;
/**
* 获取用户信息
* @param id 用户id
* @return 用户信息
*/
@GetMapping("/getUser/{id}")
public User getUser(@PathVariable("id") Long id) {
return userService.getUser(id);
}
}
3)、service层
为了方便就直接在service层调用结果,两个方法分别使用了底层的DataSource和Spring的JdbcTemplate进行数据结果的或者和返回。
import com.kevin.jdbc.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 用户业务层
* @author kevin
* @date 2019/7/6 16:11
* @since 1.0.0
*/
@Service
public class UserService {
@Autowired
private DataSource dataSource;
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 获取用户信息
* @param id 用户id
* @return 用户信息
*/
public User getUser(Long id) {
return jdbcTemplateImpl(id);
}
/**
* 使用{@link DataSource} 实现方式
* @param id 用户id
* @return 用户信息
*/
private User dataSourceImpl(Long id) {
User user = new User();
Connection connection = null;
try {
connection = dataSource.getConnection();
boolean canTransactions = connection.getMetaData().supportsTransactions();
System.out.println("Now database canTransactions is : " + canTransactions);
PreparedStatement preparedStatement = connection.prepareStatement("select * from user where id = ?");
preparedStatement.setLong(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return user;
}
/**
* 使用{@link JdbcTemplate} 实现方式
*
* @param id 用户id
* @return 用户信息
*/
private User jdbcTemplateImpl(Long id) {
User user = new User();
try {
boolean canTransactions = jdbcTemplate.getDataSource()
.getConnection().getMetaData().supportsTransactions();
System.out.println("Now database canTransactions is : " + canTransactions);
} catch (SQLException e) {
e.printStackTrace();
}
jdbcTemplate.query("select * from user where id = " + id, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
}
});
return user;
}
}
4、访问应用
浏览器中访问:http://localhost:8888/getUser/1