SpringBoot整合--JDBCTemplate
一. JDBCTemplate简介
- Spring对数据库的操作在jdbc上面做了深层次的封装,使用spring的注入功能,可以把DataSource注册到JdbcTemplate之中。
- JdbcTemplate主要提供以下五类方法:
execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;
batchUpdate方法用于执行批处理相关语句;
query方法及queryForXXX方法:用于执行查询相关语句;
call方法:用于执行存储过程、函数相关语句。
二. 数据库的建表
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login_name` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
三. 创建SpringBoot项目
1. 相关依赖的选择
2. pop.xml文件
<!--web开发依赖-->
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--JDBCTemplate核心配置-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
3. 项目结构
4. application.properties配置文件
spring.datasource.username=root
spring.datasource.password=1234
spring.datasource.url=jdbc:mysql://localhost:3306/springboottest?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
server.port=8080
5. 编写实体类—User
一般开发中,数据库字段设计推荐使用下划线(login_name),Java实体类属性使用驼峰命名(loginName)
package com.xizi.springbootjdbctemplate.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String loginName;
private String username;
private String password;
}
6. 编写持久层—UserRepository
package com.xizi.springbootjdbctemplate.repository;
import com.xizi.springbootjdbctemplate.pojo.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
/**
* @Repository注解:标注这是一个持久化操作对象
*/
@Repository
public class UserRepository {
//注入JdbcTemplate模板对象
@Resource
private JdbcTemplate jdbcTemplate;
/**
* 插入数据
* @return 插入影响的行数
*/
public Integer insertUser(){
String sql = "insert into tb_user(login_name, username, password) values(?, ?, ?), (?, ?, ?)";
Object args[] = new Object[]{"戏子", "admin", "123456", "戏子2", "123456", "123456"};
//参数一:插入数据的SQL语句,参数二:对应SQL语句中占位符?的参数
return jdbcTemplate.update(sql, args);
}
/***
* 根据userName查询数据
* @param username
* @return User对象
*/
public User selectByUsername(String username){
//定义SQL语句
String sql = "select * from tb_user where username = ?";
//定义一个RowMapper
RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
//执行查询方法
User user = jdbcTemplate.queryForObject(sql, new Object[]{username}, rowMapper);
return user;
}
/**
* 根据id查询数据
* @param id
* @return User对象
*/
public User findUserById(Integer id){
//定义SQL语句
String sql = "select * from tb_user where id = ?";
RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
//执行查询方法
return jdbcTemplate.queryForObject(sql, new Object[]{id}, rowMapper);
}
/**
* 查询所有数据
* @return 包含User对象的List集合
*/
public List<User> findAll(){
//定义SQL语句
String sql = "select * from tb_user";
//声明结果集的映射rowMapper,将结果集的数据映射成User对象数据
RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
return jdbcTemplate.query(sql, rowMapper);
}
/**
* 根据id删除数据
* @param id
*/
public void delete(final Integer id){
//定义SQL语句
String sql = "delete from tb_user where id=?";
//执行
jdbcTemplate.update(sql, new Object[]{id});
}
/**
* 修改数据
* @param user
*/
public void update(final User user){
//定义SQL语句
String sql = "update tb_user set username=?, login_name=?, where id=?";
//执行
jdbcTemplate.update(sql, new Object[]{user.getUsername(),user.getLoginName(), user.getId()});
}
}
7. 编写Service层—UserService
package com.xizi.springbootjdbctemplate.service;
import com.xizi.springbootjdbctemplate.pojo.User;
import com.xizi.springbootjdbctemplate.repository.UserRepository;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
//@Service 服务层导入Sring容器
@Service
public class UserService {
//注入UserRepository
@Resource
private UserRepository userRepository;
public Integer insertUser(){
return userRepository.insertUser();
}
public User selectByUsername(String username){
return userRepository.selectByUsername(username);
}
public List<User> findAll(){
return userRepository.findAll();
}
public User findUserById(Integer id){
return userRepository.findUserById(id);
}
public void update(User user){
userRepository.update(user);
}
public void delete(Integer id){
userRepository.delete(id);
}
}
8. 编写Controller层—UserController
package com.xizi.springbootjdbctemplate.controller;
import com.xizi.springbootjdbctemplate.pojo.User;
import com.xizi.springbootjdbctemplate.service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
@RestController
@RequestMapping("/user")
public class UserControler {
@Resource
private UserService userService;
@RequestMapping("/insertUser")
public String insertUser(){
return "一共插入数据有["+userService.insertUser()+"]条";
}
@RequestMapping("/selectByUsername")
public User selectByUsername(String username){
return userService.selectByUsername(username);
}
@RequestMapping("/selectById")
public User selectById(Integer id){
return userService.findUserById(id);
}
@RequestMapping("/findAll")
public List<User> findAll(){
return userService.findAll();
}
@RequestMapping("/update")
public void update(User user){
userService.update(user);
}
@RequestMapping("/delete")
public void delete(Integer id){
userService.delete(id);
}
}
四. 测试接口数据
1. 添加用户
http://localhost:8080/user/insertUser
2. 通过ID查询用户
http://localhost:8080/user/selectById?id=4
3. 查询所有的用户信息
http://localhost:8080/user/findAll
4. 通过username查询用户信息
http://localhost:8080/user/selectByUsername?username=戏子
5. 删除用户
http://localhost:8080/user/delete?id=3
6. 更新用户
http://localhost:8080/user/update?username=伟少&login_name=伟少&id=7
五. GitHub源码下载
https://github.com/Y960303802/SpringBoot-JDBCTemplate.git