SpringBoot 基于Jdbc实现增删改查

一、Controller编写

1、页面跳转方法

@Controller
public class PageController {
    //页面跳转方法
    @RequestMapping("/{page}")
    public String showPage(@PathVariable String page){
        return page;
    }
}

2、增删改查功能

@Controller
@RequestMapping("/user")
public class UsersController {
    @Autowired
    private UserService userService;
	
    //添加用户
    @PostMapping("/addUser")
    public String addUser(Users users){
        try{
            this.userService.addUser(users);
        }catch(Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/ok";//避免表单二次提交
    }
	
    //查找所有用户信息
    @GetMapping("/findUserAll")
    public String findUserAll(Model model){
        List<Users> list = null;
        try{
            list = this.userService.findUsersAll();
            model.addAttribute("list",list);
        }catch(Exception e){
            e.printStackTrace();
            return "error";
        }
        return "showUsers";
    }
	
    //根据id查找用户信息
    @GetMapping("/preUpdateUser")
    public String preUpdateUser(Integer id,Model model){
        try{
            Users user = this.userService.findUserById(id);
            model.addAttribute("user",user);
        }catch(Exception e){
            e.printStackTrace();
            return "error";
        }
        return "updateUser";
    }
	
    //根据id更新用户信息
    @PostMapping("/updateUser")
    public String  updateUser(Users users){
        try{
            this.userService.modifyUser(users);
        }catch(Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/ok";
    }
	
    //根据id删除用户
    @GetMapping("/deleteUser")
    public String deleteUser(Integer id){
        try{
            this.userService.dropUser(id);
        }catch(Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/ok";
    }
}

二、业务层编写

UserServiceImpl

@Service
public class UserServiceImpl  implements UserService {
    @Autowired
    private UsersDao usersDao;
    
    @Override
    @Transactional
    public void addUser(Users users) {
        this.usersDao.insertUsers(users);
    }

    @Override
    public List<Users> findUsersAll() {
        return this.usersDao.selectUsersAll();
    }

    @Override
    public Users findUserById(Integer id) {
        return this.usersDao.selectUserById(id);
    }

    @Override
    @Transactional
    public void modifyUser(Users users) {
        this.usersDao.updateUsers(users);
    }

    @Override
    @Transactional
    public void dropUser(Integer id) {
        this.usersDao.deleteUserById(id);
    }
}

三、持久层编写

UsersDaoImpl

@Repository
public class UsersDaoImpl implements UsersDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    //添加用户
    @Override
    public void insertUsers(Users users) {
        String sql = "insert into user(name,gender) values(?,?)";
        this.jdbcTemplate.update(sql,users.getName(),users.getGender());
    }

    //查找所有用户信息
    @Override
    public List<Users> selectUsersAll() {
        String sql = "select * from user";
        return this.jdbcTemplate.query(sql, new RowMapper<Users>() {
            @Override
            public Users mapRow(ResultSet resultSet, int i) throws SQLException {
                Users users = new Users();
                users.setId(resultSet.getInt("id"));
                users.setName(resultSet.getString("name"));
                users.setGender(resultSet.getString("gender"));
                return users;
            }
        });
    }

    //根据id查找用户信息
    @Override
    public Users selectUserById(Integer id) {
        Users user = new Users();
        String sql = "select * from user where id = ?";
        Object[] arry = new Object[]{id};
        this.jdbcTemplate.query(sql, arry, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setGender(resultSet.getString("gender"));
            }
        });
        return user;
    }

    //根据id更新用户信息
    @Override
    public void updateUsers(Users users) {
        String sql = "update user set name = ?,gender = ? where id = ?";
        this.jdbcTemplate.update(sql,users.getName(),users.getGender(),users.getId());
    }

    //根据id删除用户
    @Override
    public void deleteUserById(Integer id) {
        String sql = "delete from user where id = ?";
        this.jdbcTemplate.update(sql,id);
    }
}

四、简单前端页面部分代码

所有页面开头需加入 Thymeleaf 支持

<html lang="en" xmlns:th="http://www.thymeleaf.org">

还需编写成功提示页和失败页面，随需求添加即可。

1、用户信息展示页

<table border="1" align="center">
        <tr>
            <th>用户ID</th>
            <th>用户姓名</th>
            <th>用户性别</th>
            <th>操作</th>
        </tr>
        <tr th:each="u : ${list}">
            <td th:text="${u.id}"></td>
            <td th:text="${u.name}"></td>
            <td th:text="${u.gender}"></td>
            <td>
                <a th:href="@{/user/preUpdateUser(id=${u.id})}">修改</a>
                <a th:href="@{/user/deleteUser(id=${u.id})}">删除</a>
            </td>
        </tr>
    </table>

2、添加用户信息页

<form th:action="@{/user/addUser}" method="post">
        <input type="text" name="name"><br/>
        <input type="text" name="gender"><br/>
        <input type="submit" value="添加">
    </form>

3、更改用户信息页

<form th:action="@{/user/updateUser}" method="post">
    <input type="hidden" name="id" th:value="${user.id}">
    <input type="text" name="name" th:value="${user.name}"><br/>
    <input type="text" name="gender" th:value="${user.gender}"><br/>
    <input type="submit" value="添加">
</form>