环境:MYSQL8、JDK1.8、Spring Boot2.2
一、配置数据源
1)添加依赖
<!--MYSQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--JDBC数据源-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
2)application.properties配置文件中配置数据库
#服务器端口
server.port=8090
spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_part4
spring.datasource.username=root
spring.datasource.password=root
#MYSQL驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#最大等待连接中的数据量,设置0,没有限制
spring.datasource.tomcat.max-idle=10
#最大连接活动数
spring.datasource.tomcat.max-active=50
#最大等待毫秒数,单位ms,超过时间会出现错误信息
spring.datasource.tomcat.max-wait=10000
#数据库连接池初始化连接数
spring.datasource.tomcat.initial-size=5
二、创建表、实体类
1)创建用户表user
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`gender` int(3) NULL DEFAULT NULL,
`note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
2)创建user实体类
public class User {
private Long id;
private String userName;
private GenderEnum gender;
private String note;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public GenderEnum getGender() {
return gender;
}
public void setGender(GenderEnum gender) {
this.gender = gender;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
}
public enum GenderEnum {
MALE(1, "男"),
FEMALE(2, "女");
private int id;
private String name;
GenderEnum(int id, String name) {
this.id = id;
this.name = name;
}
public static GenderEnum getEnumById(int id) {
for(GenderEnum gender: GenderEnum.values()) {
if(gender.getId() == id) {
return gender;
}
}
return null;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
三、操作数据库
1)Service
接口
package com.mvp.www.service;
import com.mvp.www.model.User;
import java.util.List;
public interface JdbcTmplUserService {
public User getUser(Long id);
public List<User> findUsers(String userName, String note);
public int insertUser(User user);
public int updateUser(User user);
public int deleteUser(Long id);
}
实现类
package com.mvp.www.service.serviceimpl;
import com.mvp.www.model.GenderEnum;
import com.mvp.www.model.User;
import com.mvp.www.service.JdbcTmplUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
import java.util.List;
@Service
public class JdbcTmplUserServiceImpl implements JdbcTmplUserService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 获取影射关系
*
* @return
*/
private RowMapper<User> getUserMapper(){
RowMapper<User> userRowMapper = (ResultSet rs, int rownum) -> {
User user = new User();
user.setId(rs.getLong("id"));
user.setUserName(rs.getString("user_name"));
user.setGender(GenderEnum.getEnumById(rs.getInt("gender")));
user.setNote(rs.getString("note"));
return user;
};
return userRowMapper;
}
@Override
public User getUser(Long id) {
String sql = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER WHERE ID = ?";
Object[] params = new Object[]{id};
User user = jdbcTemplate.queryForObject(sql, params, getUserMapper());
return user;
}
@Override
public List<User> findUsers(String userName, String note) {
String sql = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER "
+ "WHERE USER_NAME LIKE CONCAT('%', ?, '%') AND "
+ "NOTE LIKE CONCAT('%', ?, '%')";
Object[] params = new Object[] {userName, note};
List<User> userList = jdbcTemplate.query(sql, params, getUserMapper());
return userList;
}
@Override
public int insertUser(User user) {
String sql = "INSERT INTO USER(USER_NAME, GENDER, NOTE) VALUES(?,?,?)";
Object[] params = new Object[] {user.getUserName(), user.getGender().getId(), user.getNote()};
int count = jdbcTemplate.update(sql, params);
return count;
}
@Override
public int updateUser(User user) {
String sql = "UPDATE USER SET USER_NAME = ?, GENDER = ?, NOTE = ? WHERE ID = ?";
Object[] params = new Object[] {user.getUserName(), user.getGender().getId(), user.getNote(), user.getId()};
return jdbcTemplate.update(sql, params);
}
@Override
public int deleteUser(Long id) {
String sql = "DELETE FROM USER WHERE ID = ?";
Object[] params = new Object[] {id};
return jdbcTemplate.update(sql, params);
}
}
2)controller
package com.mvp.www.controller;
import com.mvp.www.model.User;
import com.mvp.www.service.JdbcTmplUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
public class UserController {
@Autowired
private JdbcTmplUserService jdbcTmplUserServiceImpl;
@RequestMapping(value = "/getUser/{id}", method = RequestMethod.GET)
public User getUser(@PathVariable("id") Long id) {
return jdbcTmplUserServiceImpl.getUser(id);
}
@RequestMapping(value = "/findUsers", method = RequestMethod.GET)
public List<User> findUsers(String userName, String note) {
return jdbcTmplUserServiceImpl.findUsers(userName, note);
}
@RequestMapping(value = "/insertUser", method = RequestMethod.POST)
public int insertUser(@RequestBody User user) {
return jdbcTmplUserServiceImpl.insertUser(user);
}
@RequestMapping(value = "/updateUser", method = RequestMethod.POST)
public int updateUser(@RequestBody User user) {
return jdbcTmplUserServiceImpl.updateUser(user);
}
@RequestMapping(value = "/deleteUser", method = RequestMethod.GET)
public int deleteUser(Long id) {
return jdbcTmplUserServiceImpl.deleteUser(id);
}
}
四、其他
1)time zone报错
com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_191]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_191]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_191]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_191]
修改application.properties中url连接,改为spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_part4?serverTimezone=UTC
2)修改数据库连接池类型
默认com.zaxxer.hikari.HikariDataSource
- 修改为dbcp2
添加依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
</dependency>
application.properties中添加spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
3)StatementCallback或者ConnectionCallback
public User getUser2(Long id) {
User result = jdbcTemplate.execute((Statement stmt) -> {
String sql1 = "SELECT COUNT(*) TOTAL FROM USER WHERE ID = " + id;
ResultSet result1 = stmt.executeQuery(sql1);
while(result1.next()) {
int total = result1.getInt("TOTAL");
System.out.println(total);
}
String sql2 = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER WHERE ID = " + id;
ResultSet result2 = stmt.executeQuery(sql2);
User user = null;
while (result2.next()) {
int rowNum = result2.getRow();
user = getUserMapper().mapRow(result2, rowNum);
}
return user;
});
return result;
}
public User getUser3(Long id){
return jdbcTemplate.execute((Connection conn) -> {
String sql1 = "SELECT COUNT(*) TOTAL FROM USER WHERE ID = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql1);
preparedStatement.setLong(1, id);
ResultSet rs1 = preparedStatement.executeQuery();
while(rs1.next()){
System.out.println(rs1.getInt("TOTAL"));
}
String sql2 = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER WHERE ID = ?";
PreparedStatement preparedStatement2 = conn.prepareStatement(sql2);
preparedStatement2.setLong(1, id);
ResultSet result2 = preparedStatement2.executeQuery();
User user = null;
while (result2.next()) {
user = getUserMapper().mapRow(result2, result2.getRow());
}
return user;
});
}
五、小记
慢慢探索、深入,争取写出点有意义的东西。