SpringBoot整合JDBC实现CRUD【超详细】

最新推荐文章于 2024-08-24 19:14:27 发布

AmarisEx

最新推荐文章于 2024-08-24 19:14:27 发布

阅读量946

点赞数 1

分类专栏：后台 # SpringBoot 文章标签： spring

本文链接：https://blog.csdn.net/weixin_43488958/article/details/105420935

版权

SpringBoot 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

后台

6 篇文章 0 订阅

订阅专栏

文章目录

一、项目结构
二、配置依赖
三、数据源配置
四、业务代码
五、其他
六、项目打包
七、测试

一、项目结构

项目Gitee仓库

概览
详细

二、配置依赖

除了基础的Spring Web还有？

		<!-- Thymeleaf 简单的前端界面-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!-- jdbc启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- 数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--Druid数据源依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>

三、数据源配置

spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=xxxxxx
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

四、业务代码

三层关系

主要涉及三层：
- controller层
  负责前端与后台的交互，转递请求，返回页面等
- service层
  转达controller层的请求给dao层，将dao层执行结果递交给controller层
- dao层
  业务的具体执行，实际的与数据库进行增删改查等

controller层

UsersController.java

package com.example.demo.controller;

import com.example.demo.pojo.Users;
import com.example.demo.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Controller
@RequestMapping("/user")
public class UsersController {

    @Autowired
    private UsersService usersService;


    /**
     * 添加用户
     * @return
     */
    @PostMapping("/addUser")
    public String showInfo(Users users){

        try{
            this.usersService.addUser(users);

        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }

        return "redirect:/ok";  //重定向，防止表单重复提交
    }

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

    /**
     * 预更新用户的查询
     */
    @GetMapping("/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";
    }

    @PostMapping("/updateUser")
    public String updateUser(Users user){
        try{
            this.usersService.modifyUser(user);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/ok";
    }

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

}

service层

接口

package com.example.demo.service;

import com.example.demo.pojo.Users;

import java.util.List;

public interface UsersService {
    void addUser(Users users);
    List<Users> findUserAll();
    Users findUserById(Integer id);
    void modifyUser(Users user);
    void dropUser(Integer id);
}

子类

package com.example.demo.service.impl;

import com.example.demo.dao.UsersDao;
import com.example.demo.pojo.Users;
import com.example.demo.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * 用户管理业务层
 */
@Service
public class UsersServiceImpl implements UsersService {

    @Autowired
    private UsersDao usersDao;

    /**
     * 添加用户
     * @param users
     */

    @Override
    @Transactional//事务注解
    public void addUser(Users users) {
        this.usersDao.insertUsers(users);
    }

    /**
     * 查询全部用户
     */
    @Override
    public List<Users> findUserAll() {
        return this.usersDao.serlectUserAll();
    }

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

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

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


}

dao层

接口

package com.example.demo.dao;

import com.example.demo.pojo.Users;

import java.util.List;

public interface UsersDao {
    void insertUsers(Users users);
    List<Users> serlectUserAll();
    Users selectUserById(Integer id);
    void updateUser(Users user);
    void deleteUser(Integer id);
}

子类

package com.example.demo.dao.impl;

import com.example.demo.dao.UsersDao;
import com.example.demo.pojo.Users;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 持久层
 */
@Repository
public class UsersDaoImpl implements UsersDao {

    @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());
    }

    /**
     * 查询全部用户
     * @return
     */
    @Override
    public List<Users> serlectUserAll() {
        String sql="select * from users";
        return this.jdbcTemplate.query(sql, new RowMapper<Users>() {
            @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;
            }
        });
        //return null;
    }

    /**
     * 预更新用户查询
     * @param id
     * @return
     */
    @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;
    }

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

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

五、其他

pojo

package com.example.demo.pojo;

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;
    }
}

页面跳转类

为了跳转到前端的界面

package com.example.demo.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

/**
 * 页面跳转Controller
 */
@Controller
public class PageController {

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

html

addUser.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="../resources/favicon.ico"th:href="@{/static/favicon.ico}"/>
<head>
    <title>用户管理系统</title>
</head>
<body>

    <form th:action="@{/user/addUser}" method="post">
        <input type="text" name="username"><br/>
        <input type="text" name="usersex"><br/>
        <input type="submit" value="OK"/>
    </form>
</body>
</html>

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="../resources/favicon.ico"th:href="@{/static/favicon.ico}"/>
<head>
    <title>用户管理系统</title>
</head>
<body>
    <table border="1" align="center">
        <tr>
            <th>用户ID</th>
            <th>用户姓名</th>
            <th>用户性别</th>
            <th>操作</th>
        </tr>
        <tr th:each="u : ${userlist}">
            <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})}">修改</a>
                <a th:href="@{/user/deleteUser(id=${u.userid})}">删除</a>
            </td>
        </tr>
    </table>
</body>
</html>

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="../resources/favicon.ico"th:href="@{/static/favicon.ico}"/>
<head>
    <title>用户管理系统</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="submit" value="OK"/>
</form>
</body>
</html>

ok.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="../resources/favicon.ico"th:href="@{/static/favicon.ico}"/>
<head>
    <title>用户管理系统</title>
</head>
<body>
    成功！！！
</body>
</html>

error.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="../resources/favicon.ico"th:href="@{/static/favicon.ico}"/>
<head>
    <title>用户管理系统</title>
</head>
<body>
    出错了，。。。。
</body>
</html>