SpringBoot整合JDBC（二）

最新推荐文章于 2024-09-03 20:17:53 发布

RakuLomis

最新推荐文章于 2024-09-03 20:17:53 发布

阅读量170

点赞数

分类专栏： SpringBoot 文章标签： java spring boot

本文链接：https://blog.csdn.net/RakuLomis/article/details/106317318

版权

SpringBoot 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

基本的增删查改功能。

添加用户

新建POJO

POJO（Plain Ordinary Java Object）简单的Java对象，实际就是普通JavaBeans。

public class Users {
    private Integer userid;
    private String username;
    private String usersex;

    public Integer getUserid() {
        return userid;
    }

    public void setUserid(Integer userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUsersex() {
        return usersex;
    }

    public void setUsersex(String usersex) {
        this.usersex = usersex;
    }
}

创建页面（基于Thymeleaf）

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>Title</title>
</head>
<body>
    <form th:action="@{/user/addUser}" method="post">
        <input type="text" username="username"><br/>
        <input type="text" usersex="usersex"><br/>
        <input type="submit" value="OK"/>
    </form>
</body>
</html>

Thymeleaf于templates目录下，不能直接跳转。故需要一个页面跳转的Controller。

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

"favicon.ico"问题的解决：

<link rel="shortcut icon" href="../static/icon/Cicon1.ico" th:href="@{../static/icon/Cicon1.ico}"/>

业务实现

后端业务的实现主要依靠于Controller、Service和Dao的配合。

Controller

@Controller
@RequestMapping("/user")    //前缀，用于前端页面的定位
public class UserController {
    @Autowired
    private UsersService usersService;
    /**
     * 添加用户
     * @return
     */
    @RequestMapping("/addUser")
    public String addUser(Users users) {
        try {
            this.usersService.addUser(users);
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
        return "redirect:/success"; //重定向
    }
}

Service

接口层：

public interface UsersService {
    void addUser(Users users);
}

实现层（Impl）：

/**
 * 用户管理业务层
 */
@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private UsersDao usersDao;

    /**
     * 添加用户
     * @param users
     */
    @Override
    @Transactional  //考虑到事务问题，操作为DML时需该注解
    public void addUser(Users users) {
        this.usersDao.insertUsers(users);
    }
}

Dao

接口层：

public interface UsersDao {
    void insertUsers(Users users);
}

实现层：

/**
 * 持久层
 */
@Repository
public class UsersDaoImpl implements UsersDao {
    //数据源自带，注入后即可使用相关API
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加用户
     * @param users
     */
    @Override
    public void insertUsers(Users users) {
        String sql = "insert into users(username, usersex) values(?, ?)";
        this.jdbcTemplate.update(sql, users.getUsername(), users.getUsersex());
    }
}

查询用户

showUser.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<link rel="shortcut icon" href="../static/icon/Cicon1.ico" th:href="@{/static/icon/Cicon1.ico}"/>
<head>
    <title>ShowUsers</title>
</head>
<body>
    <table border="1" align="center">
        <tr>
            <th>UserID</th>
            <th>UserName</th>
            <th>UserSex</th>
            <th>Operation</th>
        </tr>
        <tr th:each="u : ${list}">
            <td th:text="${u.userid}"></td>
            <td th:text="${u.username}"></td>
            <td th:text="${u.usersex}"></td>
            <td>
                <a th:href="@{/user/preUpdateUser(id=${u.userid})}">Change</a>
                <a th:href="@{/user/deleteUser(id=${u.userid})}">Delete</a>
            </td>
        </tr>
    </table>
</body>
</html>

Controller

 	/**
     * 查询全部用户
     */
    @RequestMapping("/findUserAll")
    public String findUserAll(Model model) {
        List<Users> list = null;
        try {
            list = this.usersService.findUserAll();
            model.addAttribute("list", list);
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
        return "showUsers";
    }

Service

	/**
     * 查询全部用户
     * @return
     */
    @Override   //查询并不需要加事务
    public List<Users> findUserAll() {
        return this.usersDao.selectUserAll();
    }

Dao

	/**
     * 查询全部用户
     * @return
     */
    @Override
    public List<Users> selectUserAll() {
        String sql = "select * from users";
        //在query中创建List，直接调用RowMapper返回即可
        return this.jdbcTemplate.query(sql, new RowMapper<Users>() {
            /**
             * 结果集的映射
             * @param resultSet
             * @param i resultSet的迭代因子
             * @return
             * @throws SQLException
             */
            @Override
            public Users mapRow(ResultSet resultSet, int i) throws SQLException {
                Users users = new Users();
                users.setUserid(resultSet.getInt("userid"));
                users.setUsername(resultSet.getString("username"));
                users.setUsersex(resultSet.getString("usersex"));
                return users;
            }
        });
    }

更新用户

预更新查询

updateUser.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<link rel="shortcut icon" href="../static/icon/Cicon1.ico" th:href="@{/static/icon/Cicon1.ico}"/>
<head>
    <title>updateUser</title>
</head>
<body>
    <form th:action="@{/user/updateUser}" method="post">
        <input type="hidden" name="userid" th:value="${user.userid}">
        <input type="text" name="username" th:value="${user.username}"><br>
        <input type="text" name="usersex" th:value="${user.usersex}"><br>
        <input type="button" value="OK">
    </form>
</body>
</html>

Controller

	/**
     * 预更新用户查询
     */
    @RequestMapping("/preUpdateUser")
    public String preUpdateUser(Integer id, Model model) {
        try {
            Users user = this.usersService.findUserById(id);
            model.addAttribute("user", user);
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
        return "updateUser";
    }

Service

	/**
     * 预更新查询
     * @param id
     * @return
     */
    @Override
    public Users findUserById(Integer id) {
        return this.usersDao.selectUserById(id);
    }

Dao

@Override
    public Users selectUserById(Integer id) {
        Users user = new Users();
        String sql = "select * from users where userid = ?";
        Object[] arr = new Object[]{id};
        this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                user.setUserid(resultSet.getInt("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setUsersex(resultSet.getString("usersex"));
            }
        });
        return user;
    }

更新用户信息

Controller

 	/**
     * 更新用户
     */
    @RequestMapping("/updateUser")
    public String updateUser(Users users) {
        try {
            this.usersService.modifyUser(users);
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
        return "redirect:/success";
    }

Service

	/**
     * 更新用户
     * @param users
     */
    @Override
    @Transactional
    public void modifyUser(Users users) {
        this.usersDao.updateUser(users);
    }

Dao

    /**
     * 更新用户
     * @param users
     */
    @Override
    public void updateUser(Users users) {
        String sql = "update users set username = ?, usersex = ? where userid = ?";
        this.jdbcTemplate.update(sql, users.getUsername(), users.getUsersex(), users.getUserid());
    }

删除指定用户

Controller

    /**
     * 删除用户
     * @param id
     * @return
     */
    @RequestMapping("deleteUser")
    public String deleteUser(Integer id) {
        try {
            this.usersService.dropUser(id);
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
        return "redirect:/success";
    }

Service

    /**
     * 删除用户
     * @param id
     */
    @Override
    public void dropUser(Integer id) {
        this.usersDao.deletUserById(id);
    }

Dao

    /**
     * 删除用户
     * @param id
     */
    @Override
    public void deletUserById(Integer id) {
        String sql = "delete from users where userid = ?";
        this.jdbcTemplate.update(sql, id);
    }

RakuLomis

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot整合JDBC（二）

基本的增删查改功能。添加用户新建POJOPOJO（Plain Ordinary Java Object）简单的Java对象，实际就是普通JavaBeans。public class Users { private Integer userid; private String username; private String usersex; public Integer getUserid() { return userid; }
复制链接

扫一扫

专栏目录