准备工作
数据库
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`username` varchar(255) DEFAULT NULL COMMENT '用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`ctime` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
依赖
<?xml version="1.0" encoding="UTF-8"?>
<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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.tellsea</groupId>
<artifactId>springboot-jdbc-templete</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-jdbc-templete</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- spring boot -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- jdbc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 数据库驱动，我是MySQL5.7，所以使用5.1.x的驱动，如果你是MySQL8，则改成8.0.x的版本 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
配置文件
注意改成自己的相关属性值
server:
port: 8080
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot-jdbc-templete
username: root
password: 123456
SpringBoot 启动类
创建 SpringBoot 项目的话，这个应该是自动生成的，这里给通过 Maven 创建项目的同学粘贴一个启动类
@SpringBootApplication
public class SpringbootJdbcTempleteApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootJdbcTempleteApplication.class, args);
}
}
编写代码
- 实体类，采用了 Lombok 表达式
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Long id;
private String username;
private String password;
private Date ctime;
}
- dao 接口
public interface UserDao {
User getUserById(Integer id);
List<User> getUserList();
int add(User user);
int update(User user);
int delete(Integer id);
}
- dao 实现类，jdbcTemplate 是注入的对象
@Repository // 用于标注数据访问组件
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public User getUserById(Long id) {
List<User> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
if (list != null && list.size() > 0) {
return list.get(0);
} else {
return null;
}
}
@Override
public List<User> getUserList() {
List<User> list = jdbcTemplate.query("select * from tb_user", new Object[]{}, new BeanPropertyRowMapper(User.class));
if (list != null && list.size() > 0) {
return list;
} else {
return null;
}
}
@Override
public int add(User user) {
return jdbcTemplate.update("insert into tb_user(username, password, ctime) values(?, ?, ?)",
user.getUsername(), user.getPassword(), new Date());
}
@Override
public int update(User user) {
return jdbcTemplate.update("update tb_user SET username = ? , password = ? WHERE id=?",
user.getUsername(), user.getPassword(), user.getId());
}
@Override
public int delete(Long id) {
return jdbcTemplate.update("delete from tb_user where id = ? ", id);
}
}
- service 层接口
public interface UserService {
User getUserById(Integer id);
List<User> getUserList();
int add(User user);
int update(User user);
int delete(Integer id);
}
- service 层实现类，很简单，直接调用 dao 层就行了
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public User getUserById(Integer id) {
return userDao.getUserById(id);
}
@Override
public List<User> getUserList() {
return userDao.getUserList();
}
@Override
public int add(User user) {
return userDao.add(user);
}
@Override
public int update(User user) {
return userDao.update(user);
}
@Override
public int delete(Integer id) {
return userDao.delete(id);
}
}
- 下面这个是一个简单的公共数据返回类，通常项目中都是必须存在的，用于指定统一的响应数据格式
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ResponseResult {
private Integer code;
private String msg;
private Object data;
}
- 控制层
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
/**
* 根据id查询用户
*
* @param id
* @return
*/
@GetMapping("/get/{id}")
public ResponseResult getUserById(@PathVariable(value = "id") Long id) {
User user = userService.getUserById(id);
if (user == null) {
return new ResponseResult(200, "查询结果为空", null);
}
return new ResponseResult(200, "查询成功", user);
}
/**
* 查询用户列表
*
* @return
*/
@GetMapping("/list")
public ResponseResult getUserList() {
List<User> list = userService.getUserList();
if (list == null || list.isEmpty()) {
return new ResponseResult(200, "查询结果为空", null);
}
return new ResponseResult(200, "查询成功", list);
}
/**
* 新增用户
*
* @param user
* @return
*/
@PostMapping("/add")
public ResponseResult add(@RequestBody User user) {
int count = userService.add(user);
if (count == 0) {
return new ResponseResult(500, "新增失败", null);
}
return new ResponseResult(200, "新增成功", null);
}
/**
* 根据id删除用户
*
* @param id
* @return
*/
@GetMapping("/delete/{id}")
public ResponseResult delete(@PathVariable(value = "id") Long id) {
int count = userService.delete(id);
if (count == 0) {
return new ResponseResult(500, "删除失败", null);
}
return new ResponseResult(200, "删除成功", null);
}
/**
* 根据id修改用户信息
*
* @param user
* @return
*/
@PostMapping("/update")
public ResponseResult update(@RequestBody User user) {
int count = userService.update(user);
if (count == 0) {
return new ResponseResult(500, "更新失败", null);
}
return new ResponseResult(200, "更新成功", null);
}
}