转自:
SpringBoot如何使用JDBC操作数据库呢?
下文笔者讲述SpringBoot中使用jdbc操作数据库的方法分享,如下所示:
实现思路:
1.引入相应的jar包
2.在application.yml配置相应的数据库连接信息及其它属性
3.编写相应的业务代码
POM依赖的加入
<!--数据库连接jdbc依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mysql链接依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--阿里druid数据库链接依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.5</version>
</dependency>
application-dev.yml
spring:
datasource:
url: jdbc:mysql://localhost:3306/test
username: root
password: 123
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
建表
CREATE TABLE `tp_user` (
`user_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`tel` bigint(19) NOT NULL COMMENT '手机号=用户名',
`password` varchar(35) NOT NULL COMMENT '密码',
`nickname` varchar(30) DEFAULT NULL COMMENT '昵称',
`secret` varchar(35) NOT NULL COMMENT '秘钥',
`portrait` varchar(120) DEFAULT NULL COMMENT '头像地址',
`i_card` varchar(18) NOT NULL DEFAULT '' COMMENT '身份证',
`area` int(10) NOT NULL DEFAULT '0' COMMENT '所在区域',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=238 DEFAULT CHARSET=utf8
UserController
@RequestMapping("/user")
@RestController
public class UserController {
@Autowired
private UserService userService;
/**
* 添加用户
* @param tel 注册手机号
* @param pwd 设置密码
*/
@PostMapping("/createUser")
public void createUser(@RequestParam("tel") String tel, @RequestParam("pwd") String pwd){
userService.createUser(tel,pwd);
}
/**
* 更新用户信息
* @param user_id 用户ID
* @param nickName 昵称
*/
@PutMapping("/updateUser/{id}")
public void updateUser(@PathVariable("id") String user_id, @RequestParam("nickName") String nickName){
userService.updateUser(user_id,nickName);
}
/**
* 获取用户信息
* @param id 用户Id
* @return
*/
@GetMapping("/getUser/{id}")
public UserInfo getUser(@PathVariable("id") Integer id){
return userService.getUser(id);
}
@DeleteMapping("/deleteUserByUserId/{id}")
public void deleteUserByUserId(@PathVariable("id") Integer id){
userService.deleteUserByUserId(id);
}
}
UserService
@Service
public class UserService {
@Autowired
UserInfoMapper userInfoMapper;
public void createUser(String tel,String pwd) {
userInfoMapper.createUser(tel,pwd);
}
public UserInfo getUser(Integer id) {
return userInfoMapper.getUser(id);
}
public void updateUser(String user_id, String nickName) {
userInfoMapper.updateUser(user_id,nickName);
}
public void deleteUserByUserId(Integer id) {
userInfoMapper.deleteUserByUserId(id);
}
}
UseInfoMapper
public interface UserInfoMapper {
void createUser(String tel,String pwd);
UserInfo getUser(Integer id);
void updateUser(String user_id, String nickName);
void deleteUserByUserId(Integer id);
}
UserInfoImpl
@Repository
public class UserInfoImpl implements UserInfoMapper {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void createUser(String tel,String pwd) {
jdbcTemplate.update("INSERT INTO test.tp_user(tel,password,nickname,secret) VALUES (?,md5(?),?,'')",tel,pwd,tel);
}
@Override
public UserInfo getUser(Integer id) {
List<UserInfo> userList = jdbcTemplate.query("select tel,nickname,password FROM test.tp_user WHERE user_id = ?",new Object[]{id},new BeanPropertyRowMapper(UserInfo.class));
if(userList != null && userList.size() > 0){
UserInfo user = userList.get(0);
return user;
}else {
return null;
}
}
@Override
public void updateUser(String user_id, String nickName) {
jdbcTemplate.update("UPDATE test.tp_user SET nickname = ? WHERE user_id = ?",nickName,user_id);
}
@Override
public void deleteUserByUserId(Integer id) {
jdbcTemplate.update("DELETE FROM test.tp_user WHERE user_id = ?",id);
}
}
serivice中使用JdbcTemplate
然后借助JdbcTemplate中方法对数据库进行操作