Spring Boot JDBC 连接数据库

﹏ヽ暗。殇╰゛Y 2021-09-21 02:38 393阅读 0赞

接下来将对在Spring Boot构建的Web应用中，基于MYSQL数据库的几种数据库连接方式进行介绍，本节说下jdbc方式

**创建表**

![20200129163827883.png][]

**配置相关文件**

配置maven依赖

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

在application.properties文件配置mysql的驱动类，数据库地址，数据库账号、密码信息。

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.url=jdbc:mysql://127.0.0.1/test?characterEncoding=utf8&serverTimezone=UTC
    spring.datasource.username=root
    spring.datasource.password=9958

通过引入这些依赖和配置一些基本信息，springboot就可以访问数据库类。

### 具体编码 ###

**实体类**

public class User {
    
        int id;
    
        String name;
    
        String sex;
    
        ...省略了getter. setter
    
    }

**dao层**

public interface IUserDao {
        void addUser(String name, String sex);
        User getUser(Integer id);
        void updateUser(int id, String name);
        void deleteUserById(Integer id);
    }

@Component
    public class UserDaoImpl implements IUserDao {
        @Autowired
        private JdbcTemplate jdbcTemplate;
        @Override
        public void addUser(String name, String sex) {
            jdbcTemplate.update("INSERT INTO `user`(`name`,sex) VALUES (?,?)", name, sex);
        }
        @Override
        public User getUser(Integer id) {
            List<User> userList = jdbcTemplate.query("select * FROM first_db.user WHERE id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
            if (userList != null && userList.size() > 0) {
                User user = userList.get(0);
                return user;
            }
            return null;
        }
        @Override
        public void updateUser(int id, String name) {
            jdbcTemplate.update("UPDATE user SET `name` = ? WHERE id = ?", name, id);
        }
        @Override
        public void deleteUserById(Integer id) {
            jdbcTemplate.update("DELETE FROM `user` WHERE id = ?", id);
        }
    }

**service层**

public interface IUserService {
        void addUser(String name, String sex);
        User getUser(Integer id);
        void updateUser(int id, String name);
        void deleteUserById(Integer id);
    }

@Service
    public class UserService {
        @Autowired
        private JdbcTemplate jdbcTemplate;
        public void addUser(String name,String sex) {
            jdbcTemplate.update("INSERT INTO `user`(`name`,sex) VALUES (?,?)",name,sex);
        }
        public User getUser(Integer id) {
            List<User> userList = jdbcTemplate.query("select * FROM first_db.user WHERE id = ?",new Object[]{id},new BeanPropertyRowMapper(User.class));
            if(userList != null && userList.size() > 0){
                User user = userList.get(0);
                return user;
            }else {
                return null;
            }
        }
        public void updateUser(int id, String name) {
            jdbcTemplate.update("UPDATE user SET `name` = ? WHERE id = ?",name,id);
        }
        public void deleteUserById(Integer id) {
            jdbcTemplate.update("DELETE FROM `user` WHERE id = ?",id);
        }
    }

**controller类**

@RestController
    @RequestMapping("/user")
    public class UserController {
        @Autowired
        private UserService userService;
    
        @GetMapping(value = "/{id}")
        public User getUser(@PathVariable("id") Integer id) {
            return userService.getUser(id);
        }
        @PostMapping(value = "/add")
        public void addUser(@RequestParam("name") String name, @RequestParam("sex") String sex) {
            userService.addUser(name, sex);
        }
        @PutMapping(value = "/update/{id}")
        public void updateUser(@PathVariable("id") Integer id, @RequestParam("name") String name) {
            userService.updateUser(id, name);
        }
        @DeleteMapping(value = "/delete/{id}")
        public void deleteUser(@PathVariable("id") Integer id) {
            userService.deleteUserById(id);
        }
    }

所有功能都已经通过postman来测试，小伙伴也可以尝试下，没有任何问题。

参考：

[http://blog.csdn.net/catoop/article/details/50507516][http_blog.csdn.net_catoop_article_details_50507516]

[http://blog.csdn.net/forezp/article/details/70477821][http_blog.csdn.net_forezp_article_details_70477821]

[http://blog.csdn.net/qq\_31001665/article/details/70196825][http_blog.csdn.net_qq_31001665_article_details_70196825]

[http://blog.csdn.net/u010412719/article/details/70147824][http_blog.csdn.net_u010412719_article_details_70147824]

[https://www.cnblogs.com/moonlightL/p/8066018.html][https_www.cnblogs.com_moonlightL_p_8066018.html]

[20200129163827883.png]: /images/20210920/217b04287d9d47b6b1d54d130f5d199e.png
[http_blog.csdn.net_catoop_article_details_50507516]: http://blog.csdn.net/catoop/article/details/50507516
[http_blog.csdn.net_forezp_article_details_70477821]: http://blog.csdn.net/forezp/article/details/70477821
[http_blog.csdn.net_qq_31001665_article_details_70196825]: http://blog.csdn.net/qq_31001665/article/details/70196825
[http_blog.csdn.net_u010412719_article_details_70147824]: http://blog.csdn.net/u010412719/article/details/70147824
[https_www.cnblogs.com_moonlightL_p_8066018.html]: https://www.cnblogs.com/moonlightL/p/8066018.html