springboot系列教程（六）——整合jdbctemplate

短命女 2022-06-03 09:25 239阅读 0赞

**整体目录结构**  
![这里写图片描述][SouthEast]

## sql ##

-- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `money` double NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('1', '11', 'a');
    INSERT INTO `user` VALUES ('2', '22', 'b');

## pom文件 ##

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.0.29</version>
            </dependency>

## 配置文件 ##

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://localhost:3306/test
    spring.datasource.username=root
    spring.datasource.password=123456

## model类 ##

package com.example.demo.model;
    
    /** * @author :小虎 * @date :2017/12/22 */
    
    
    public class User { 
    
        private int id ;
        private String name ;
        private double money;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public double getMoney() {
            return money;
        }
    
        public void setMoney(double money) {
            this.money = money;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", money=" + money +
                    '}';
        }
    }

## dao ##

package com.example.demo.dao;
    
    import com.example.demo.model.User;
    
    import java.util.List;
    
    /** * @author :小虎 * @date :2017/12/25 */
    
    
    public interface UserDao { 
        int add(User user);
    
        int delete(int id);
    
        int update(User user);
    
        User findUserById(int id);
    
        List<User> findUserList();
    }

## dao实现类 ##

package com.example.demo.dao.impl;
    
    import com.example.demo.dao.UserDao;
    import com.example.demo.model.User;
    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.List;
    
    /** * @author :小虎 * @date :2017/12/25 */
    
    @Repository
    public class UserDaoImpl implements UserDao { 
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public int add(User user) {
            return jdbcTemplate.update("insert into user(name, money) values(?, ?)",
                    user.getName(),user.getMoney());
        }
    
        @Override
        public int delete(int id) {
            return jdbcTemplate.update("delete from user wehre id = ?",id);
        }
    
        @Override
        public int update(User user) {
            return jdbcTemplate.update("UPDATE user SET NAME=? ,money=? WHERE id=?",
                    user.getName(),user.getMoney(),user.getId());
        }
    
        @Override
        public User findUserById(int id) {
            List<User> list = jdbcTemplate.query("select * from USER where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
            if(list!=null && list.size()>0){
                User user = list.get(0);
                return user;
            }else{
                return null;
            }
        }
    
        @Override
        public List<User> findUserList() {
            List<User> list = jdbcTemplate.query("select * from USER ", new Object[]{}, new BeanPropertyRowMapper(User.class));
            if(list!=null && list.size()>0){
                return list;
            }else{
                return null;
            }
        }
    }

## service ##

package com.example.demo.service;
    
    import com.example.demo.model.User;
    
    import java.util.List;
    
    /** * @author :小虎 * @date :2017/12/25 */
    
    
    public interface UserService { 
    
        List<User> findAccountList();
    
    }

## service实现类 ##

package com.example.demo.service.impl;
    
    import com.example.demo.dao.UserDao;
    import com.example.demo.model.User;
    import com.example.demo.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /** * @author :小虎 * @date :2017/12/25 */
    
    @Service
    public class UserServiceImpl implements UserService { 
        @Autowired
        UserDao userDao;
        @Override
        public List<User> findAccountList() {
            return userDao.findUserList();
        }
    }

## controller ##

package com.example.demo.web;
    
    import com.example.demo.model.User;
    import com.example.demo.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    /** * @author :小虎 * @date :2017/12/25 */
    
    @RestController
    public class UserController {
    
        @Autowired
        UserService userService;
    
        @RequestMapping("/")
        public List<User> list(){
            return userService.findAccountList();
        }
    }

源代码

> [https://github.com/itmybaby/springboot/tree/master/study6][https_github.com_itmybaby_springboot_tree_master_study6]

[SouthEast]: /images/20220603/cf4267beaa2a46afa47125dcf5732cbb.png
[https_github.com_itmybaby_springboot_tree_master_study6]: https://github.com/itmybaby/springboot/tree/master/study6