首页/變量

JdbcTemplate，SpringBoot整合JdbcTemplate

 2023-09-26 阅读 26 评论 0

摘要：SpringBoot整合JdbcTemplate JdbcTemplate、一、数据准备 CREATE TABLE `tb_user` ( Springboot框架，`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `username` varchar(50) NOT NULL COMMENT '用户名', `age` int(

SpringBoot整合JdbcTemplate

 

JdbcTemplate、一、数据准备

 

  1.  
    CREATE TABLE `tb_user` (
     
  2.  
    Springboot框架，`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     
  3.  
    `username` varchar(50) NOT NULL COMMENT '用户名',
     
  4.  
    `age` int(11) NOT NULL COMMENT '年龄',
     
  5.  
    `ctm` datetime NOT NULL COMMENT '创建时间',
     
  6.  
    PRIMARY KEY (`id`)
     
  7.  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
     


 

  1.  
    INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('张三', '18', NOW()) ;
     
  2.  
    INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('李四', '20', NOW()) ;
     
  3.  
    INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('王五', '19', NOW()) ;
     


二、引入依赖
 

 

  1.  
    <!-- jdbcTemplate -->
     
  2.  
    <dependency>
     
  3.  
    <groupId>org.springframework.boot</groupId>
     
  4.  
    <artifactId>spring-boot-starter-jdbc</artifactId>
     
  5.  
    </dependency>
     
  6.  
  7.  
    <!-- MySQL连接 -->
     
  8.  
    <dependency>
     
  9.  
    <groupId>mysql</groupId>
     
  10.  
    <artifactId>mysql-connector-java</artifactId>
     
  11.  
    <scope>runtime</scope>
     
  12.  
    </dependency>
     


另外web依赖也需要，因为我们采用MVC模式。
 

 

  1.  
    <!-- Add typical dependencies for a web application -->
     
  2.  
    <dependency>
     
  3.  
    <groupId>org.springframework.boot</groupId>
     
  4.  
    <artifactId>spring-boot-starter-web</artifactId>
     
  5.  
    </dependency>
     


三、数据库配置文件
 

一如既往，我们采用yaml文件配置，当然properties文件也是一样。
 

 
注意点，SpringBoot默认采用tomcat-jdbc连接池，如果需要C3P0，DBCP，Druid等作为连接池，需要加入相关依赖以及配置，这里不作说明，采用默认配置即可。
 

 

 

  1.  
    spring:
     
  2.  
    datasource:
     
  3.  
    driver-class-name: com.mysql.jdbc.Driver
     
  4.  
    url: jdbc:mysql://localhost:3306/db_user
     
  5.  
    username: root
     
  6.  
    password: root
     


四、代码
 

项目结构如下：
 

这里写图片描述
 

实体类User.class
 

 

  1.  
    package cn.saytime.bean;
     
  2.  
  3.  
    import java.util.Date;
     
  4.  
  5.  
    /**
     
  6.  
    * @ClassName cn.saytime.bean.User
     
  7.  
    * @Description
     
  8.  
    * @date 2017-07-04 22:47:28
     
  9.  
    */
     
  10.  
    public class User {
     
  11.  
  12.  
    private int id;
     
  13.  
    private String username;
     
  14.  
    private int age;
     
  15.  
    private Date ctm;
     
  16.  
  17.  
    public User() {
     
  18.  
    }
     
  19.  
  20.  
    public User(String username, int age) {
     
  21.  
    this.username = username;
     
  22.  
    this.age = age;
     
  23.  
    this.ctm = new Date();
     
  24.  
    }
     
  25.  
  26.  
    // Getter、Setter
     
  27.  
    }
     


UserDao.class
 

 

  1.  
    package cn.saytime.dao;
     
  2.  
  3.  
    import cn.saytime.bean.User;
     
  4.  
  5.  
    import java.util.List;
     
  6.  
  7.  
    /**
     
  8.  
    * @ClassName cn.saytime.dao.UserDao
     
  9.  
    * @Description
     
  10.  
    * @date 2017-07-04 22:48:45
     
  11.  
    */
     
  12.  
    public interface UserDao {
     
  13.  
  14.  
    User getUserById(Integer id);
     
  15.  
  16.  
    public List<User> getUserList();
     
  17.  
  18.  
    public int add(User user);
     
  19.  
  20.  
    public int update(Integer id, User user);
     
  21.  
  22.  
    public int delete(Integer id);
     
  23.  
    }
     


UserDaoImpl.class
 

 

  1.  
    package cn.saytime.dao.impl;
     
  2.  
  3.  
    import cn.saytime.bean.User;
     
  4.  
    import cn.saytime.dao.UserDao;
     
  5.  
    import org.springframework.beans.factory.annotation.Autowired;
     
  6.  
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
     
  7.  
    import org.springframework.jdbc.core.JdbcTemplate;
     
  8.  
    import org.springframework.stereotype.Repository;
     
  9.  
  10.  
    import java.util.Date;
     
  11.  
    import java.util.List;
     
  12.  
  13.  
    /**
     
  14.  
    * @ClassName cn.saytime.dao.impl.UserDaoImpl
     
  15.  
    * @Description
     
  16.  
    * @date 2017-07-04 22:50:07
     
  17.  
    */
     
  18.  
    @Repository
     
  19.  
    public class UserDaoImpl implements UserDao {
     
  20.  
  21.  
    @Autowired
     
  22.  
    private JdbcTemplate jdbcTemplate;
     
  23.  
  24.  
    @Override
     
  25.  
    public User getUserById(Integer id) {
     
  26.  
    List<User> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
     
  27.  
    if(list!=null && list.size()>0){
     
  28.  
    return list.get(0);
     
  29.  
    }else{
     
  30.  
    return null;
     
  31.  
    }
     
  32.  
    }
     
  33.  
  34.  
    @Override
     
  35.  
    public List<User> getUserList() {
     
  36.  
    List<User> list = jdbcTemplate.query("select * from tb_user", new Object[]{}, new BeanPropertyRowMapper(User.class));
     
  37.  
    if(list!=null && list.size()>0){
     
  38.  
    return list;
     
  39.  
    }else{
     
  40.  
    return null;
     
  41.  
    }
     
  42.  
    }
     
  43.  
  44.  
    @Override
     
  45.  
    public int add(User user) {
     
  46.  
    return jdbcTemplate.update("insert into tb_user(username, age, ctm) values(?, ?, ?)",
     
  47.  
    user.getUsername(),user.getAge(), new Date());
     
  48.  
  49.  
    }
     
  50.  
  51.  
    @Override
     
  52.  
    public int update(Integer id, User user) {
     
  53.  
    return jdbcTemplate.update("UPDATE tb_user SET username = ? , age = ? WHERE id=?",
     
  54.  
    user.getUsername(),user.getAge(), id);
     
  55.  
    }
     
  56.  
  57.  
    @Override
     
  58.  
    public int delete(Integer id) {
     
  59.  
    return jdbcTemplate.update("DELETE from tb_user where id = ? ",id);
     
  60.  
    }
     
  61.  
  62.  
    }
     


UserService.class
 

 

  1.  
    package cn.saytime.service;
     
  2.  
  3.  
    import cn.saytime.bean.User;
     
  4.  
    import org.springframework.stereotype.Service;
     
  5.  
  6.  
    import java.util.List;
     
  7.  
  8.  
    /**
     
  9.  
    * @ClassName cn.saytime.service.UserService
     
  10.  
    * @Description
     
  11.  
    * @date 2017-07-04 22:49:05
     
  12.  
    */
     
  13.  
    public interface UserService {
     
  14.  
  15.  
    User getUserById(Integer id);
     
  16.  
  17.  
    public List<User> getUserList();
     
  18.  
  19.  
    public int add(User user);
     
  20.  
  21.  
    public int update(Integer id, User user);
     
  22.  
  23.  
    public int delete(Integer id);
     
  24.  
    }
     


UserServiceimpl.class
 

 

  1.  
    package cn.saytime.service.impl;
     
  2.  
  3.  
    import cn.saytime.bean.User;
     
  4.  
    import cn.saytime.dao.UserDao;
     
  5.  
    import cn.saytime.service.UserService;
     
  6.  
    import org.springframework.beans.factory.annotation.Autowired;
     
  7.  
    import org.springframework.stereotype.Service;
     
  8.  
  9.  
    import java.util.List;
     
  10.  
  11.  
    /**
     
  12.  
    * @ClassName cn.saytime.service.impl.UserServiceImpl
     
  13.  
    * @Description
     
  14.  
    * @date 2017-07-04 22:49:27
     
  15.  
    */
     
  16.  
    @Service
     
  17.  
    public class UserServiceImpl implements UserService {
     
  18.  
  19.  
    @Autowired
     
  20.  
    private UserDao userDao;
     
  21.  
  22.  
    @Override
     
  23.  
    public User getUserById(Integer id) {
     
  24.  
    return userDao.getUserById(id);
     
  25.  
    }
     
  26.  
  27.  
    @Override
     
  28.  
    public List<User> getUserList() {
     
  29.  
    return userDao.getUserList();
     
  30.  
    }
     
  31.  
  32.  
    @Override
     
  33.  
    public int add(User user) {
     
  34.  
    return userDao.add(user);
     
  35.  
    }
     
  36.  
  37.  
    @Override
     
  38.  
    public int update(Integer id, User user) {
     
  39.  
    return userDao.update(id, user);
     
  40.  
    }
     
  41.  
  42.  
    @Override
     
  43.  
    public int delete(Integer id) {
     
  44.  
    return userDao.delete(id);
     
  45.  
    }
     
  46.  
    }
     


JsonResult.class 通用json返回类
 

 

  1.  
    package cn.saytime.bean;
     
  2.  
  3.  
    public class JsonResult {
     
  4.  
  5.  
    private String status = null;
     
  6.  
  7.  
    private Object result = null;
     
  8.  
  9.  
    public JsonResult status(String status) {
     
  10.  
    this.status = status;
     
  11.  
    return this;
     
  12.  
    }
     
  13.  
  14.  
    // Getter Setter
     
  15.  
    }
     


UserController.class（Restful风格）
 

 

  1.  
    package cn.saytime.web;
     
  2.  
  3.  
    import cn.saytime.bean.JsonResult;
     
  4.  
    import cn.saytime.bean.User;
     
  5.  
    import cn.saytime.service.UserService;
     
  6.  
    import org.springframework.beans.factory.annotation.Autowired;
     
  7.  
    import org.springframework.http.HttpStatus;
     
  8.  
    import org.springframework.http.ResponseEntity;
     
  9.  
    import org.springframework.web.bind.annotation.PathVariable;
     
  10.  
    import org.springframework.web.bind.annotation.RequestBody;
     
  11.  
    import org.springframework.web.bind.annotation.RequestMapping;
     
  12.  
    import org.springframework.web.bind.annotation.RequestMethod;
     
  13.  
    import org.springframework.web.bind.annotation.RequestParam;
     
  14.  
    import org.springframework.web.bind.annotation.RestController;
     
  15.  
  16.  
    import java.util.List;
     
  17.  
  18.  
    /**
     
  19.  
    * @ClassName cn.saytime.web.UserController
     
  20.  
    * @Description
     
  21.  
    * @date 2017-07-04 22:46:14
     
  22.  
    */
     
  23.  
    @RestController
     
  24.  
    public class UserController {
     
  25.  
  26.  
    @Autowired
     
  27.  
    private UserService userService;
     
  28.  
  29.  
    /**
     
  30.  
    * 根据ID查询用户
     
  31.  
    * @param id
     
  32.  
    * @return
     
  33.  
    */
     
  34.  
    @RequestMapping(value = "user/{id}", method = RequestMethod.GET)
     
  35.  
    public ResponseEntity<JsonResult> getUserById (@PathVariable(value = "id") Integer id){
     
  36.  
    JsonResult r = new JsonResult();
     
  37.  
    try {
     
  38.  
    User user = userService.getUserById(id);
     
  39.  
    r.setResult(user);
     
  40.  
    r.setStatus("ok");
     
  41.  
    } catch (Exception e) {
     
  42.  
    r.setResult(e.getClass().getName() + ":" + e.getMessage());
     
  43.  
    r.setStatus("error");
     
  44.  
    e.printStackTrace();
     
  45.  
    }
     
  46.  
    return ResponseEntity.ok(r);
     
  47.  
    }
     
  48.  
  49.  
    /**
     
  50.  
    * 查询用户列表
     
  51.  
    * @return
     
  52.  
    */
     
  53.  
    @RequestMapping(value = "users", method = RequestMethod.GET)
     
  54.  
    public ResponseEntity<JsonResult> getUserList (){
     
  55.  
    JsonResult r = new JsonResult();
     
  56.  
    try {
     
  57.  
    List<User> users = userService.getUserList();
     
  58.  
    r.setResult(users);
     
  59.  
    r.setStatus("ok");
     
  60.  
    } catch (Exception e) {
     
  61.  
    r.setResult(e.getClass().getName() + ":" + e.getMessage());
     
  62.  
    r.setStatus("error");
     
  63.  
    e.printStackTrace();
     
  64.  
    }
     
  65.  
    return ResponseEntity.ok(r);
     
  66.  
    }
     
  67.  
  68.  
    /**
     
  69.  
    * 添加用户
     
  70.  
    * @param user
     
  71.  
    * @return
     
  72.  
    */
     
  73.  
    @RequestMapping(value = "user", method = RequestMethod.POST)
     
  74.  
    public ResponseEntity<JsonResult> add (@RequestBody User user){
     
  75.  
    JsonResult r = new JsonResult();
     
  76.  
    try {
     
  77.  
    int orderId = userService.add(user);
     
  78.  
    if (orderId < 0) {
     
  79.  
    r.setResult(orderId);
     
  80.  
    r.setStatus("fail");
     
  81.  
    } else {
     
  82.  
    r.setResult(orderId);
     
  83.  
    r.setStatus("ok");
     
  84.  
    }
     
  85.  
    } catch (Exception e) {
     
  86.  
    r.setResult(e.getClass().getName() + ":" + e.getMessage());
     
  87.  
    r.setStatus("error");
     
  88.  
  89.  
    e.printStackTrace();
     
  90.  
    }
     
  91.  
    return ResponseEntity.ok(r);
     
  92.  
    }
     
  93.  
  94.  
    /**
     
  95.  
    * 根据id删除用户
     
  96.  
    * @param id
     
  97.  
    * @return
     
  98.  
    */
     
  99.  
    @RequestMapping(value = "user/{id}", method = RequestMethod.DELETE)
     
  100.  
    public ResponseEntity<JsonResult> delete (@PathVariable(value = "id") Integer id){
     
  101.  
    JsonResult r = new JsonResult();
     
  102.  
    try {
     
  103.  
    int ret = userService.delete(id);
     
  104.  
    if (ret < 0) {
     
  105.  
    r.setResult(ret);
     
  106.  
    r.setStatus("fail");
     
  107.  
    } else {
     
  108.  
    r.setResult(ret);
     
  109.  
    r.setStatus("ok");
     
  110.  
    }
     
  111.  
    } catch (Exception e) {
     
  112.  
    r.setResult(e.getClass().getName() + ":" + e.getMessage());
     
  113.  
    r.setStatus("error");
     
  114.  
  115.  
    e.printStackTrace();
     
  116.  
    }
     
  117.  
    return ResponseEntity.ok(r);
     
  118.  
    }
     
  119.  
  120.  
    /**
     
  121.  
    * 根据id修改用户信息
     
  122.  
    * @param user
     
  123.  
    * @return
     
  124.  
    */
     
  125.  
    @RequestMapping(value = "user/{id}", method = RequestMethod.PUT)
     
  126.  
    public ResponseEntity<JsonResult> update (@PathVariable("id") Integer id, @RequestBody User user){
     
  127.  
    JsonResult r = new JsonResult();
     
  128.  
    try {
     
  129.  
    int ret = userService.update(id, user);
     
  130.  
    if (ret < 0) {
     
  131.  
    r.setResult(ret);
     
  132.  
    r.setStatus("fail");
     
  133.  
    } else {
     
  134.  
    r.setResult(ret);
     
  135.  
    r.setStatus("ok");
     
  136.  
    }
     
  137.  
    } catch (Exception e) {
     
  138.  
    r.setResult(e.getClass().getName() + ":" + e.getMessage());
     
  139.  
    r.setStatus("error");
     
  140.  
  141.  
    e.printStackTrace();
     
  142.  
    }
     
  143.  
    return ResponseEntity.ok(r);
     
  144.  
    }
     
  145.  
  146.  
    }
     


五、测试
 

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
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
原文链接：https://hbdhgg.com/2/97828.html
上一篇：物联网典型应用有哪些，主流物联网平台的架构设计思考
下一篇：netty框架，springboot+security框架整合
标签：jdbctemplateSpringboot框架jdbctemplate jpa 混用Springboot项目spring整合了哪些框架Spring Boot陈恒jdbcproperties配置Java jdbc
jdbctemplate
发表评论:
最新文章
阅读排行
猜你喜欢
热门标签
本站为非赢利网站，部分文章来源或改编自互联网及其他公众平台，主要目的在于分享信息，版权归原作者所有，内容仅供读者参考，如有侵权请联系我们删除！
Copyright © 2022 匯編語言學習筆記 Inc. 保留所有权利。 
底部版权信息