Spring Boot中用JDBCTemplate实现数据的增删查改

今天药忘吃喽~ 2021-07-24 11:56 336阅读 0赞

**一 代码位置**

[https://gitee.com/cakin24/code/tree/master/08/Jdbc][https_gitee.com_cakin24_code_tree_master_08_Jdbc]

**二 代码**

package com.example.demo.controller;
    
    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.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    @RestController
    @RequestMapping("user")
    public class UserController {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        //创建数据表
        @GetMapping("createUserTable")
        public String createUserTable() throws Exception {
            String sql = "CREATE TABLE `user` (\n" +
                    "  `id` int(10) NOT NULL AUTO_INCREMENT,\n" +
                    "  `username` varchar(100) DEFAULT NULL,\n" +
                    "  `password` varchar(100) DEFAULT NULL,\n" +
                    "  PRIMARY KEY (`id`)\n" +
                    ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;\n" +
                    "\n";
    
            jdbcTemplate.execute(sql);
            return "创建表成功";
        }
    
        //saveUserTest
        //添加一个测试数据
        @GetMapping("saveUserTest")
        public String saveUserTest() throws Exception {
            String sql = "INSERT INTO user (USERNAME,PASSWORD) VALUES ('longzhiran','123456')";
            int rows = jdbcTemplate.update(sql);
            return "执行成功，影响" + rows + "行";
        }
    
        //batchSaveUser
        //批量添加测试数据
        @GetMapping("batchSave")
        public String batchSaveUser() throws Exception {
            String sql =
                    "INSERT INTO user (USERNAME,PASSWORD) VALUES (?,?)";
            List<Object[]> arrayList = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                String[] arr = new String[2];
                arr[0] = "longzhiran" + i;
                arr[1] = "123456" + i;
                arrayList.add(arr);
            }
            jdbcTemplate.batchUpdate(sql, arrayList);
            return "执行成功";
        }
    
        //addUser?userName=longzhiran&passWord=123456
        //
        @GetMapping("add")
        public String addUser( String userName, String passWord ) throws Exception {
            String sql = "INSERT INTO user (USERNAME,PASSWORD) VALUES (?,?)";
            int rows = jdbcTemplate.update(sql, userName, passWord);
            return "执行成功，影响" + rows + "行";
        }
    
        //updateUserPassword?id=1&passWord=12345678
        @GetMapping("updateUserPassword")
        public String updateUserPassword( int id, String passWord ) throws Exception {
            String sql = "UPDATE user SET PASSWORD = ? WHERE ID = ?";
            int rows = jdbcTemplate.update(sql, passWord, id);
            return "执行成功，影响" + rows + "行";
        }
    
        //deleteUserById?id=1
        @GetMapping("deleteUserById")
        public String deleteUserById( int id ) throws Exception {
            String sql = "DELETE FROM  user  WHERE ID = ?";
            int rows = jdbcTemplate.update(sql, id);
            return "执行成功，影响" + rows + "行";
        }
    
        //getUserByName?userName=longzhiran
        @GetMapping("getUserByName")
        public List getUserByName( String userName ) throws Exception {
            String sql = "SELECT * FROM user WHERE USERNAME = ?";
            List<User> list = jdbcTemplate.query(sql, new User(), new Object[]{userName});
            return list;
        }
    
        //getMapById?id=1
        @GetMapping("getMapById")
        public Map getMapById( Integer id ) throws Exception {
            String sql = "SELECT * FROM user WHERE ID = ?";
            Map map = jdbcTemplate.queryForMap(sql, id);
            return map;
        }
    
        //getUserById?id=1
        @GetMapping("getUserById")
        public User getUserById( Integer id ) throws Exception {
            String sql = "SELECT * FROM user WHERE ID = ?";
            User user = jdbcTemplate.queryForObject(sql, new User(), new Object[]{id});
            return user;
        }
    
        //getAll
        @GetMapping("list")
        public List<User> list() throws Exception {
            String sql = "SELECT * FROM user";
            List<User> userList = jdbcTemplate.query(sql,
                    new BeanPropertyRowMapper(User.class));
            return userList;
        }
    }

**三 测试**

1 浏览器输入： [http://localhost:8080/user/createUserTable][http_localhost_8080_user_createUserTable]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

2 浏览器输入： [http://localhost:8080/user/saveUserTest][http_localhost_8080_user_saveUserTest]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1][]

3 浏览器输入： [http://localhost:8080/user/batchSave][http_localhost_8080_user_batchSave]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 2][]

经过这3步操作，此时数据表如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 3][]

4 浏览器输入： [http://localhost:8080/user/add?userName=cakin&passWord=123456][http_localhost_8080_user_add_userName_cakin_passWord_123456]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 4][]

5 浏览器输入： [http://localhost:8080/user/updateUserPassword?id=1&passWord=12345678][http_localhost_8080_user_updateUserPassword_id_1_passWord_12345678]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 5][]

6 浏览器输入： [http://localhost:8080/user/deleteUserById?id=1][http_localhost_8080_user_deleteUserById_id_1]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 6][]

7 浏览器输入： [http://localhost:8080/user/getUserByName?userName=cakin][http_localhost_8080_user_getUserByName_userName_cakin]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 7][]

8 浏览器输入： [http://localhost:8080/user/getMapById?id=2][http_localhost_8080_user_getMapById_id_2]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 8][]

9 浏览器输入： [http://localhost:8080/user/getUserById?id=6][http_localhost_8080_user_getUserById_id_6]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 9][]

10 浏览器输入： [http://localhost:8080/user/list][http_localhost_8080_user_list]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 10][]

[https_gitee.com_cakin24_code_tree_master_08_Jdbc]: https://gitee.com/cakin24/code/tree/master/08/Jdbc
[http_localhost_8080_user_createUserTable]: http://localhost:8080/user/createUserTable
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/8ea6495f89744c0ca919ed30e780301b.png
[http_localhost_8080_user_saveUserTest]: http://localhost:8080/user/saveUserTest
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1]: /images/20210724/00e582df1779448994790cbce1c52538.png
[http_localhost_8080_user_batchSave]: http://localhost:8080/user/batchSave
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 2]: /images/20210724/8f98d4f8209a4189a27547335dc8769c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 3]: /images/20210724/a3958a2e568c41468a439bfbe8536c3b.png
[http_localhost_8080_user_add_userName_cakin_passWord_123456]: http://localhost:8080/user/add?userName=cakin&passWord=123456
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 4]: /images/20210724/3d3bfbd8f6d946cca1c028ba9fb10035.png
[http_localhost_8080_user_updateUserPassword_id_1_passWord_12345678]: http://localhost:8080/user/updateUserPassword?id=1&passWord=12345678
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 5]: /images/20210724/036da523f1b049f2938d8abd14ad0908.png
[http_localhost_8080_user_deleteUserById_id_1]: http://localhost:8080/user/deleteUserById?id=1
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 6]: /images/20210724/0e821c69fbee407fb28628af803f7eba.png
[http_localhost_8080_user_getUserByName_userName_cakin]: http://localhost:8080/user/getUserByName?userName=cakin
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 7]: /images/20210724/d94acdb9a0bb4f97a9cc6d00de29d0df.png
[http_localhost_8080_user_getMapById_id_2]: http://localhost:8080/user/getMapById?id=2
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 8]: /images/20210724/638b7b1ac8ec46e4bf963fd3b2719bc3.png
[http_localhost_8080_user_getUserById_id_6]: http://localhost:8080/user/getUserById?id=6
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 9]: /images/20210724/a464dfa2f1c14b12ac3cdb5bae928df7.png
[http_localhost_8080_user_list]: http://localhost:8080/user/list
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 10]: /images/20210724/8d94ca0e679547a1a16eab20cdf11de6.png