SpringBoot整合JdbcTemplate
-
CREATE TABLE `tb_user` (
-
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
-
`username` varchar(50) NOT NULL COMMENT '用户名',
-
`age` int(11) NOT NULL COMMENT '年龄',
-
`ctm` datetime NOT NULL COMMENT '创建时间',
-
PRIMARY KEY (`id`)
-
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('张三', '18', NOW()) ;
-
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('李四', '20', NOW()) ;
-
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('王五', '19', NOW()) ;
二、引入依赖
-
<!-- jdbcTemplate -->
-
<dependency>
-
<groupId>org.springframework.boot</groupId>
-
<artifactId>spring-boot-starter-jdbc</artifactId>
-
</dependency>
-
<!-- MySQL连接 -->
-
<dependency>
-
<groupId>mysql</groupId>
-
<artifactId>mysql-connector-java</artifactId>
-
<scope>runtime</scope>
-
</dependency>
另外web依赖也需要,因为我们采用MVC模式。
-
<!-- Add typical dependencies for a web application -->
-
<dependency>
-
<groupId>org.springframework.boot</groupId>
-
<artifactId>spring-boot-starter-web</artifactId>
-
</dependency>
三、数据库配置文件
一如既往,我们采用yaml文件配置,当然properties文件也是一样。
注意点,SpringBoot默认采用tomcat-jdbc连接池,如果需要C3P0,DBCP,Druid等作为连接池,需要加入相关依赖以及配置,这里不作说明,采用默认配置即可。
-
spring:
-
datasource:
-
driver-class-name: com.mysql.jdbc.Driver
-
url: jdbc:mysql://localhost:3306/db_user
-
username: root
-
password: root
四、代码
项目结构如下:
实体类User.class
-
package cn.saytime.bean;
-
import java.util.Date;
-
/**
-
* @ClassName cn.saytime.bean.User
-
* @Description
-
* @date 2017-07-04 22:47:28
-
*/
-
public class User {
-
private int id;
-
private String username;
-
private int age;
-
private Date ctm;
-
public User() {
-
}
-
public User(String username, int age) {
-
this.username = username;
-
this.age = age;
-
this.ctm = new Date();
-
}
-
// Getter、Setter
-
}
UserDao.class
-
package cn.saytime.dao;
-
import cn.saytime.bean.User;
-
import java.util.List;
-
/**
-
* @ClassName cn.saytime.dao.UserDao
-
* @Description
-
* @date 2017-07-04 22:48:45
-
*/
-
public interface UserDao {
-
User getUserById(Integer id);
-
public List<User> getUserList();
-
public int add(User user);
-
public int update(Integer id, User user);
-
public int delete(Integer id);
-
}
UserDaoImpl.class
-
package cn.saytime.dao.impl;
-
import cn.saytime.bean.User;
-
import cn.saytime.dao.UserDao;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.jdbc.core.BeanPropertyRowMapper;
-
import org.springframework.jdbc.core.JdbcTemplate;
-
import org.springframework.stereotype.Repository;
-
import java.util.Date;
-
import java.util.List;
-
/**
-
* @ClassName cn.saytime.dao.impl.UserDaoImpl
-
* @Description
-
* @date 2017-07-04 22:50:07
-
*/
-
@Repository
-
public class UserDaoImpl implements UserDao {
-
@Autowired
-
private JdbcTemplate jdbcTemplate;
-
@Override
-
public User getUserById(Integer 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, age, ctm) values(?, ?, ?)",
-
user.getUsername(),user.getAge(), new Date());
-
}
-
@Override
-
public int update(Integer id, User user) {
-
return jdbcTemplate.update("UPDATE tb_user SET username = ? , age = ? WHERE id=?",
-
user.getUsername(),user.getAge(), id);
-
}
-
@Override
-
public int delete(Integer id) {
-
return jdbcTemplate.update("DELETE from tb_user where id = ? ",id);
-
}
-
}
UserService.class
-
package cn.saytime.service;
-
import cn.saytime.bean.User;
-
import org.springframework.stereotype.Service;
-
import java.util.List;
-
/**
-
* @ClassName cn.saytime.service.UserService
-
* @Description
-
* @date 2017-07-04 22:49:05
-
*/
-
public interface UserService {
-
User getUserById(Integer id);
-
public List<User> getUserList();
-
public int add(User user);
-
public int update(Integer id, User user);
-
public int delete(Integer id);
-
}
UserServiceimpl.class
-
package cn.saytime.service.impl;
-
import cn.saytime.bean.User;
-
import cn.saytime.dao.UserDao;
-
import cn.saytime.service.UserService;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.stereotype.Service;
-
import java.util.List;
-
/**
-
* @ClassName cn.saytime.service.impl.UserServiceImpl
-
* @Description
-
* @date 2017-07-04 22:49:27
-
*/
-
@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(Integer id, User user) {
-
return userDao.update(id, user);
-
}
-
@Override
-
public int delete(Integer id) {
-
return userDao.delete(id);
-
}
-
}
JsonResult.class 通用json返回类
-
package cn.saytime.bean;
-
public class JsonResult {
-
private String status = null;
-
private Object result = null;
-
public JsonResult status(String status) {
-
this.status = status;
-
return this;
-
}
-
// Getter Setter
-
}
UserController.class(Restful风格)
-
package cn.saytime.web;
-
import cn.saytime.bean.JsonResult;
-
import cn.saytime.bean.User;
-
import cn.saytime.service.UserService;
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.http.HttpStatus;
-
import org.springframework.http.ResponseEntity;
-
import org.springframework.web.bind.annotation.PathVariable;
-
import org.springframework.web.bind.annotation.RequestBody;
-
import org.springframework.web.bind.annotation.RequestMapping;
-
import org.springframework.web.bind.annotation.RequestMethod;
-
import org.springframework.web.bind.annotation.RequestParam;
-
import org.springframework.web.bind.annotation.RestController;
-
import java.util.List;
-
/**
-
* @ClassName cn.saytime.web.UserController
-
* @Description
-
* @date 2017-07-04 22:46:14
-
*/
-
@RestController
-
public class UserController {
-
@Autowired
-
private UserService userService;
-
/**
-
* 根据ID查询用户
-
* @param id
-
* @return
-
*/
-
@RequestMapping(value = "user/{id}", method = RequestMethod.GET)
-
public ResponseEntity<JsonResult> getUserById (@PathVariable(value = "id") Integer id){
-
JsonResult r = new JsonResult();
-
try {
-
User user = userService.getUserById(id);
-
r.setResult(user);
-
r.setStatus("ok");
-
} catch (Exception e) {
-
r.setResult(e.getClass().getName() + ":" + e.getMessage());
-
r.setStatus("error");
-
e.printStackTrace();
-
}
-
return ResponseEntity.ok(r);
-
}
-
/**
-
* 查询用户列表
-
* @return
-
*/
-
@RequestMapping(value = "users", method = RequestMethod.GET)
-
public ResponseEntity<JsonResult> getUserList (){
-
JsonResult r = new JsonResult();
-
try {
-
List<User> users = userService.getUserList();
-
r.setResult(users);
-
r.setStatus("ok");
-
} catch (Exception e) {
-
r.setResult(e.getClass().getName() + ":" + e.getMessage());
-
r.setStatus("error");
-
e.printStackTrace();
-
}
-
return ResponseEntity.ok(r);
-
}
-
/**
-
* 添加用户
-
* @param user
-
* @return
-
*/
-
@RequestMapping(value = "user", method = RequestMethod.POST)
-
public ResponseEntity<JsonResult> add (@RequestBody User user){
-
JsonResult r = new JsonResult();
-
try {
-
int orderId = userService.add(user);
-
if (orderId < 0) {
-
r.setResult(orderId);
-
r.setStatus("fail");
-
} else {
-
r.setResult(orderId);
-
r.setStatus("ok");
-
}
-
} catch (Exception e) {
-
r.setResult(e.getClass().getName() + ":" + e.getMessage());
-
r.setStatus("error");
-
e.printStackTrace();
-
}
-
return ResponseEntity.ok(r);
-
}
-
/**
-
* 根据id删除用户
-
* @param id
-
* @return
-
*/
-
@RequestMapping(value = "user/{id}", method = RequestMethod.DELETE)
-
public ResponseEntity<JsonResult> delete (@PathVariable(value = "id") Integer id){
-
JsonResult r = new JsonResult();
-
try {
-
int ret = userService.delete(id);
-
if (ret < 0) {
-
r.setResult(ret);
-
r.setStatus("fail");
-
} else {
-
r.setResult(ret);
-
r.setStatus("ok");
-
}
-
} catch (Exception e) {
-
r.setResult(e.getClass().getName() + ":" + e.getMessage());
-
r.setStatus("error");
-
e.printStackTrace();
-
}
-
return ResponseEntity.ok(r);
-
}
-
/**
-
* 根据id修改用户信息
-
* @param user
-
* @return
-
*/
-
@RequestMapping(value = "user/{id}", method = RequestMethod.PUT)
-
public ResponseEntity<JsonResult> update (@PathVariable("id") Integer id, @RequestBody User user){
-
JsonResult r = new JsonResult();
-
try {
-
int ret = userService.update(id, user);
-
if (ret < 0) {
-
r.setResult(ret);
-
r.setStatus("fail");
-
} else {
-
r.setResult(ret);
-
r.setStatus("ok");
-
}
-
} catch (Exception e) {
-
r.setResult(e.getClass().getName() + ":" + e.getMessage());
-
r.setStatus("error");
-
e.printStackTrace();
-
}
-
return ResponseEntity.ok(r);
-
}
-
}
五、测试
GET http://localhost:8080/users 获取用户列表
GET http://localhost:8080/user/{id} 根据ID获取用户信息
POST http://localhost:8080/user 添加用户(注意提交格式以及内容)
PUT http://localhost:8080/user/{id} 根据ID修改用户信息
再次查询所有用户信息
DELETE http://localhost:8080/user/{id} 根据ID删除用户
最终用户数据
测试结果通过,ok