Spring Boot集成JDBC

蔚落 2022-05-23 09:11 205阅读 0赞

## 准备 ##

Spring boot整合jdbc访问mysql数据库。

*  IDEA
 *  mysql

## 集成过程 ##

1.初始化mysql测试表

DROP TABLE IF EXISTS `tb_spring`;
    CREATE TABLE `tb_spring` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `name` varchar(50) DEFAULT NULL COMMENT '名称', `mobile` varchar(11) DEFAULT NULL COMMENT '手机号', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
    
    INSERT INTO `tb_spring` VALUES ('1', '张三三', '15212345678');
    INSERT INTO `tb_spring` VALUES ('2', '李四', '15233334444');
    INSERT INTO `tb_spring` VALUES ('3', '王五', '15788889999');
    INSERT INTO `tb_spring` VALUES ('4', '赵六', '15900000000');
    INSERT INTO `tb_spring` VALUES ('5', '田琪琪', '13800001111');
    INSERT INTO `tb_spring` VALUES ('6', '添加试试', '15288881111');

2.创建spring boot工程，添加相关依赖：

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

3.添加配置信息，这里使用yml文件

spring:
      datasource:
        name: datasource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/spring
        username: root
        password: 123456

4.具体实现

用户实体对象

@Data
    public class User { 
        private Integer id;
        private String name;
        private String mobile;
    }

DAO

public interface IUserDao { 
    
        User queryUserById(int id);
    
        List<User> queryUserList();
    
        int updateUserById(User user);
    
        int insertUser(User user);
    
        int deleteUserById(int id);
    }

DAO实现类

@Repository
    public class UserDaoImpl implements IUserDao { 
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public User queryUserById(int id) {
            String sql = "select * from tb_spring where id = ?";
            List<User> list = jdbcTemplate.query(sql, new Object[]{id}, new BeanPropertyRowMapper(User.class));
            if (list != null && list.size() > 0) {
                return list.get(0);
            } else {
                return null;
            }
        }
    
        @Override
        public List<User> queryUserList() {
            String sql = "select * from tb_spring";
            List<User> list = jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper(User.class));
            if (list != null && list.size() > 0) {
                return list;
            } else {
                return null;
            }
        }
    
        @Override
        public int updateUserById(User user) {
            String sql = "update tb_spring set name = ?, mobile = ? where id = ?";
            return jdbcTemplate.update(sql, user.getName(), user.getMobile(), user.getId());
        }
    
        @Override
        public int insertUser(User user) {
            String sql = "insert into tb_spring(name,mobile) values(?, ?)";
            return jdbcTemplate.update(sql, user.getName(), user.getMobile());
        }
    
        @Override
        public int deleteUserById(int id) {
            String sql = "delete from tb_spring where id = ?";
            return jdbcTemplate.update(sql, id);
        }
    
    }

service

public interface IUserService { 
    
        User queryUserById(int id);
    
        List<User> queryUserList();
    
        int updateUserById(User user);
    
        int insertUser(User user);
    
        int deleteUserById(int id);
    }

service实现类

@Service
    public class UserServiceImpl implements IUserService { 
    
        @Autowired
        private IUserDao userDao;
    
        @Override
        public User queryUserById(int id) {
            return userDao.queryUserById(id);
        }
    
        @Override
        public List<User> queryUserList() {
            return userDao.queryUserList();
        }
    
        @Override
        public int updateUserById(User user) {
            return userDao.updateUserById(user);
        }
    
        @Override
        public int insertUser(User user) {
            return userDao.insertUser(user);
        }
    
        @Override
        public int deleteUserById(int id) {
            return userDao.deleteUserById(id);
        }
    
    }

web

@RestController
    public class UserController { 
    
        @Autowired
        private IUserService userService;
    
        /** * 根据id查询用户信息 * * @param id * @return */
        @RequestMapping(value = "/queryUserById/{id}", method = RequestMethod.GET)
        public User getUserById(@PathVariable("id") int id) {
            return userService.queryUserById(id);
        }
    
        /** * 查询用户信息列表 * * @return */
        @RequestMapping(value = "/queryUserList", method = RequestMethod.GET)
        public List<User> queryUserList() {
            return userService.queryUserList();
        }
    
        /** * 根据id更新用户信息 * * @param id * @param name * @param mobile * @return */
        @RequestMapping(value = "/updateUserById/{id}", method = RequestMethod.GET)
        public String updateUserById(@PathVariable("id") int id,
                                     @RequestParam(value = "name") String name,
                                     @RequestParam(value = "mobile") String mobile) {
            User user = new User();
            user.setId(id);
            user.setName(name);
            user.setMobile(mobile);
            int res = userService.updateUserById(user);
            if (res == 0) {
                return "update failed";
            }
    
            return "update success->" + user.toString();
        }
    
        /** * 添加用户信息 * * @param name * @param mobile * @return */
        @RequestMapping(value = "/insertUserPost", method = RequestMethod.POST)
        public String insertUser(@RequestParam(value = "name") String name,
                                 @RequestParam(value = "mobile") String mobile) {
            User user = new User();
            user.setName(name);
            user.setMobile(mobile);
            int res = userService.insertUser(user);
            if (res == 0) {
                return "insert failed";
            }
    
            return "insert success->" + user.toString();
        }
    
        /** * 根据id删除用户 * * @param id * @return */
        @RequestMapping(value = "/deleteUserById/{id}", method = RequestMethod.GET)
        public String deleteUserById(@PathVariable("id") int id) {
            int res = userService.deleteUserById(id);
            if (res == 0) {
                return "delete failed";
            }
    
            return "delete success";
        }
    
    }

## 参考资料 ##

[https://blog.csdn.net/forezp/article/details/70477821][https_blog.csdn.net_forezp_article_details_70477821]

[https_blog.csdn.net_forezp_article_details_70477821]: https://blog.csdn.net/forezp/article/details/70477821