轻轻松松学习SpringBoot2：第二十篇： JDBC操作

冷不防 2022-05-21 11:09 370阅读 0赞

前面我们讲解如何使用jpa操作数据库   https://blog.csdn.net/stronglyh/article/details/80904531

今天我们来讲解如何使用原生jdbc的方法操作数据库

定义一个controller，一个repository

其中repository文件中

@Autowired
    private JdbcTemplate jdbcTemplate;

controller文件中

@Autowired
    private JDBCRepository jdbcRepository;

一：访问

controller和repository代码分别如下

@GetMapping(value="/jdbc_tests/{id}")
        public Test testList(@PathVariable("id") Integer id){
            return jdbcRepository.getTest(id);
        }

public void createTest(Integer id,Integer age,String name) {
            jdbcTemplate.update("INSERT INTO test(id,age,name) VALUES (?,?,?)",id,age,name);
        }

然后启动服务，用postman进行访问

![70][]

二：增加

controller和repository代码分别如下

@PostMapping(value="/jdbc_tests")
        public void testAdd(@RequestParam("id")Integer id,@RequestParam("age")Integer age,@RequestParam("name")String name){
            jdbcRepository.createTest(id,age,name);
        }

public void createTest(Integer id,Integer age,String name) {
            jdbcTemplate.update("INSERT INTO test(id,age,name) VALUES (?,?,?)",id,age,name);
        }

我们重启服务，用postman看下效果

![70 1][]

![70 2][]

ok，新增成功

三：修改

controller和repository代码分别如下

@PutMapping(value="/jdbc_tests/{id}")
        public void testUpdate(@PathVariable("id") Integer id,@RequestParam("age") Integer age,@RequestParam("name")String name){
            jdbcRepository.updateTest(id,age,name);
        }

public void updateTest(Integer id,Integer age,String name) {
            jdbcTemplate.update("update test set age=?,name=? where id=?",age,name,id);
        }

我们重启服务，用postman看下效果（注意修改body类型）

![70 3][]

我们再来看下数据库结果

![70 4][]

非常的ok，id为7的数据发生了改变

四：删除

controller和repository代码分别如下

@DeleteMapping(value="/jdbc_tests/{id}")
        public void testDelete(@PathVariable("id") Integer id){
            jdbcRepository.deleteTest(id);
        }

public void deleteTest(Integer id){
            jdbcTemplate.update("delete from test where id=?",id);
        }

我们用postman看下结果

![70 5][]

![70 6][]

ok了

全部代码如下

package com.example.test;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    
    import java.util.List;
    
    @RestController
    public class JDBCController {
    
        @Autowired
        private JDBCRepository jdbcRepository;
    
        @GetMapping(value="/jdbc_tests/{id}")
        public Test testList(@PathVariable("id") Integer id){
            return jdbcRepository.getTest(id);
        }
    
        @PostMapping(value="/jdbc_tests")
        public void testAdd(@RequestParam("id")Integer id,@RequestParam("age")Integer age,@RequestParam("name")String name){
            jdbcRepository.createTest(id,age,name);
        }
    
        @PutMapping(value="/jdbc_tests/{id}")
        public void testUpdate(@PathVariable("id") Integer id,@RequestParam("age") Integer age,@RequestParam("name")String name){
            jdbcRepository.updateTest(id,age,name);
        }
    
        @DeleteMapping(value="/jdbc_tests/{id}")
        public void testDelete(@PathVariable("id") Integer id){
            jdbcRepository.deleteTest(id);
        }
    
    }

package com.example.test;
    
    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;
    
    @Repository
    public class JDBCRepository{
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        public void createTest(Integer id,Integer age,String name) {
            jdbcTemplate.update("INSERT INTO test(id,age,name) VALUES (?,?,?)",id,age,name);
        }
    
        public void updateTest(Integer id,Integer age,String name) {
            jdbcTemplate.update("update test set age=?,name=? where id=?",age,name,id);
        }
    
        public void deleteTest(Integer id){
            jdbcTemplate.update("delete from test where id=?",id);
        }
    
        public Test getTest(Integer id){
            List<Test> list = jdbcTemplate.query("select * from test where id="+id,new BeanPropertyRowMapper(Test.class));
            if(list!=null && list.size()>0){
                Test test = list.get(0);
                return test;
            }
            return null;
        }
    
    }

欢迎鉴赏

[70]: /images/20220521/6929bab8e17041e6b0cca6ec9f6dce65.png
[70 1]: /images/20220521/01bbdae3fb73417b848ae8a8918bc182.png
[70 2]: /images/20220521/95784419d34d4fe99921bcbc0e85122c.png
[70 3]: /images/20220521/5c7dbf1c957f4071878db0acad8079e0.png
[70 4]: /images/20220521/00220f47dba345089376aa31b62f390a.png
[70 5]: /images/20220521/acd3d3e5fda84b8bb945f506e38c629d.png
[70 6]: /images/20220521/13344d2b6e534ea0a253054e34eeba87.png