SpringBoot(四):SpringBoot整合JdbcTemplate
来源:互联网 发布:梅花女子大学 知乎 编辑:程序博客网 时间:2024/05/27 01:05
个人其实偏向于使用类似于JdbcTemplate这种的框架,返回数据也习惯于接受Map/List形式,而不是转化成对象,一是前后台分离转成json方便,另外是返回数据格式,数据字段可以通过SQL控制,而不是返回整个对象字段数据,或者通过VO方式。当然更多人习惯于采用Bean形式,所以这里也同样使用Bean.
一、数据准备
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 */@Repositorypublic class UserDaoImpl implements UserDao {@Autowiredprivate JdbcTemplate jdbcTemplate;@Overridepublic 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;}}@Overridepublic 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;}}@Overridepublic int add(User user) {return jdbcTemplate.update("insert into tb_user(username, age, ctm) values(?, ?, ?)",user.getUsername(),user.getAge(), new Date());}@Overridepublic int update(Integer id, User user) {return jdbcTemplate.update("UPDATE tb_user SET username = ? , age = ? WHERE id=?",user.getUsername(),user.getAge(), id);}@Overridepublic 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 */@Servicepublic class UserServiceImpl implements UserService {@Autowiredprivate UserDao userDao;@Overridepublic User getUserById(Integer id) {return userDao.getUserById(id);}@Overridepublic List<User> getUserList() {return userDao.getUserList();}@Overridepublic int add(User user) {return userDao.add(user);}@Overridepublic int update(Integer id, User user) {return userDao.update(id, user);}@Overridepublic 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 */@RestControllerpublic class UserController {@Autowiredprivate 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
- SpringBoot(四):SpringBoot整合JdbcTemplate
- springBoot整合JdbcTemplate
- Springboot整合jdbcTemplate
- springboot学习笔记(四) JdbcTemplate
- springBoot(四)整合之MyBatis整合
- 【四】Dubbo整合Springboot
- springboot系列教程(六)——整合jdbctemplate
- springboot- JdbcTemplate
- springboot整合多数据源(四)
- springboot(四、springboot+mybatis)
- SpringBoot(五):SpringBoot整合MyBatis
- SpringBoot(七):SpringBoot整合Swagger2
- springboot搭建web(整合mybatis)(四)
- SpringBoot学习笔记(四):整合thymeleaf遇到的问题
- Spring Boot学习之旅:(四)springboot 整合 fastjson
- Springboot学习笔记(五)JdbcTemplate
- SpringBoot使用JdbcTemplate案例(学习笔记)
- springboot加入jdbcTemplate
- 2301: [HAOI2011]Problem b 莫比乌斯反演+前缀+容斥原理
- vue环境的安装
- Python空间数据处理1: GDAL读写遥感图像
- 今日成长
- 一张图读懂三个值得投资的消费金融平台
- SpringBoot(四):SpringBoot整合JdbcTemplate
- PAT 1014. 福尔摩斯的约会
- SpringBoot(五):SpringBoot整合MyBatis
- Angularjs2
- 关于实现线程死锁的一个例子
- 用shell脚本语言实现一个斐波那契数列的递归和非递归版本
- 求<=N的平方数
- 日期推算
- 349 Intersection of Two Arrays