idea使用springMVC连接数据库进行增删改查

MVC创建看这里☺

项目目录

先连接数据库（jdbc），然后写实体类（entity），之后定义方法，写在service里面，然后在serviceImpl里面覆写方法，对接数据库进行操作最后在controller进行访问，jsp进行视图的查看。

service写的方法—》impl覆写，然后对接数据库—》controller调用----》jsp显示

数据库

pom.xml依赖

<!--写在<dependencies>-->
<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>4.3.13.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>4.3.13.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.13.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>4.3.13.RELEASE</version>
    </dependency>

首先连接数据库
jdbc层
DatebaseConnection

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;

public class DatebaseConnection {
    private static final String DRIVE="com.mysql.jdbc.Driver";//驱动
    private static final String DBURL="jdbc:mysql://localhost:3306/jwt";//数据库地址
    private static final String DBUSER="root";//账号
    private static final String DBPASS="root";//密码
    private static Connection conn;
    public static Connection getConnection() throws Exception{
        Class.forName(DRIVE);//加载驱动
        conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS);//连接数据库
        return conn;//返回
    }
    public void close() throws Exception{
        if(this.conn != null) {
            this.conn.close();
        }
    }
}

第二步写
entity层

package Entity;

public class User {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

第三写Service层里面的方法

package Service;

import Entity.User;

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

public interface UserService {
    public List<User> findAll();//查询全部
    public boolean add(User user) throws SQLException;//添加
    public boolean deleteById(int id)throws  SQLException;//删除
    public User findById(int id)throws SQLException;//按照id查询
    public boolean updateUser(User user)throws SQLException;//修改
}

第四步腹泻方法写入Impl层

package Service.Impl;

import Entity.User;
import Service.UserService;
import jdbc.DatebaseConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserServiceImpl implements UserService {
    //驱动
    private Connection conn = null;
    public UserServiceImpl() throws Exception{

        this.conn = DatebaseConnection.getConnection();
    }
    private PreparedStatement preparedStatement = null;
    @Override
    public List<User> findAll(){
        List<User> list = new ArrayList<User>();
        String sql = "select id,username,password from user";
        try {
            this.preparedStatement = this.conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                list.add(user);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }
    @Override
    public boolean add(User user) throws SQLException{
        boolean flag =false;
        String sql = "INSERT INTO user(username,PASSWORD) VALUES(?,?)";
        this.preparedStatement = this.conn.prepareStatement(sql);
//        this.preparedStatement.setInt(1,user.getId());
        this.preparedStatement.setString(1,user.getUsername());
        this.preparedStatement.setString(2,user.getPassword());
        if (this.preparedStatement.executeUpdate()>0){
            flag = true;
            return flag;
        }
        return flag;
    }
    @Override
    public boolean deleteById(int id)throws  SQLException{
        boolean flag =false;
        String sql = "DELETE FROM USER WHERE id = ?";
        this.preparedStatement = this.conn.prepareStatement(sql);
        this.preparedStatement.setInt(1,id);
        this.preparedStatement.executeUpdate();
        if (this.preparedStatement.executeUpdate()>0){
            flag = true;
            return flag;
        }
        return flag;
    }
    @Override
    public User findById(int id)throws SQLException {
        User user = null;
        String sql = "SELECT id,username,PASSWORD FROM USER WHERE id = ?";
        this.preparedStatement = this.conn.prepareStatement(sql);
        this.preparedStatement.setInt(1,id);
        ResultSet resultSet = this.preparedStatement.executeQuery();
        if (resultSet.next()){
            user = new User();
            user.setId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
        }
        return user;
    }
    @Override
    public boolean updateUser(User user)throws SQLException{
        boolean flag = false;
        String sql = "update user set username = ? ,password = ? where id = ?";
        this.preparedStatement = this.conn.prepareStatement(sql);
        this.preparedStatement.setInt(3,user.getId());
        this.preparedStatement.setString(1,user.getUsername());
        this.preparedStatement.setString(2,user.getPassword());
        if (this.preparedStatement.executeUpdate()>0){
            flag = true;
            return flag;
        }
        return flag;
    }
}

第五在controller层调用

package Controller;

import Entity.User;
import Service.Impl.UserServiceImpl;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

@Controller
@RequestMapping("/user")
public class UserController {
    @RequestMapping("/findAll")
    public ModelAndView  findAll(HttpServletRequest request) throws Exception {
        UserServiceImpl userService = new UserServiceImpl();
        List<User> list = userService.findAll();
        ModelAndView mv = new ModelAndView("User");
        mv.addObject("list",list);
        return mv;
    }

    @RequestMapping("/toadd")
    public String toadd() {
        return "user_add";
    }
    @RequestMapping("/add")
    public String add(HttpServletRequest request) throws Exception {
        User user = new User();
//        user.setId(Integer.parseInt(request.getParameter("id")));
        user.setUsername(request.getParameter("username"));
        user.setPassword(request.getParameter("password"));
        UserServiceImpl userService = new UserServiceImpl();
        userService.add(user);
        return "redirect:/user/findAll";
    }
    @RequestMapping("/deleteById")
    public String deleteById(HttpServletRequest request) throws Exception {
        int id = Integer.parseInt(request.getParameter("id"));
        UserServiceImpl userService = new UserServiceImpl();
        userService.deleteById(id);
        return "redirect:/user/findAll";
    }
    @RequestMapping("/findById")
    public ModelAndView findById(HttpServletRequest request) throws Exception {
        int id = Integer.parseInt(request.getParameter("id"));
        UserServiceImpl userService = new UserServiceImpl();
        User user = userService.findById(id);
        ModelAndView mv = new ModelAndView("user_update");
        mv.addObject("user",user);
        return mv;
    }
    @RequestMapping("/updateUser")
    public String updateUser(HttpServletRequest request) throws Exception {
        User user = new User();
        user.setId(Integer.parseInt(request.getParameter("id")));
        user.setUsername(request.getParameter("username"));
        user.setPassword(request.getParameter("password"));
        UserServiceImpl userService = new UserServiceImpl();
        userService.updateUser(user);
        return "redirect:/user/findAll";
    }
}

第六在jsp文件显示
user.jsp

<%@ page import="Entity.User" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: lenovo
  Date: 2020/12/23
  Time: 14:10
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <script type="text/javascript">
        function add() {//添加方法
            window.location.href="/maven-springmvc/user/toadd"
        }
        function del(id) {//添加方法
            window.location.href="/maven-springmvc/user/deleteById?id="+id;
        }
        function upd(id) {//修改方法
            window.location.href="/maven-springmvc/user/findById?id="+id;
        }
    </script>
</head>
    <%request.setCharacterEncoding("utf-8"); %>
<body>
<%
    List<User> list = (List<User>) request.getAttribute("list");
%>
<div id="main">
    <h1><p align="center">后台信息表</p></h1>
    <table border="1" width="100%">
        <tr>
            <th colspan="4">
                <button type="button" onclick="add()" id="add">增加</button>
            </th>
        </tr>
        <tr>
            <th>id</th>
            <th>用户名</th>
            <th>密码</th>
            <th>操作</th>
        </tr>
        <%
            for (int i = 0; i < list.size();i++){
        %>
        <tr>
            <th><%=list.get(i).getId()%></th>
            <th><%=list.get(i).getUsername()%></th>
            <th><%=list.get(i).getPassword()%></th>
            <th width="5px">
                <button type="button" onclick="upd(<%=list.get(i).getId()%>)" id="upd">修改</button>
                <button type="button" onclick="del(<%=list.get(i).getId()%>)" id="del">删除</button>
            </th>
        </tr>
        <%
        }
        %>
    </table>
</div>
</body>
</html>

user_add.jsp

<%--
  Created by IntelliJ IDEA.
  User: lenovo
  Date: 2020/12/23
  Time: 20:15
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>add</title>
</head>
<body>
<center>
    <form action="/maven-springmvc/user/add" method="post">
<%--        编号：<input type="text" name="id"/><br/>--%>
        用户名：<input type="text" name="username"/><br/>
        密码：<input type="text" name="password"/><br/>
        <br/>
        <input type="submit" value="添加"/>
        <input type="reset" value="重置"/>
    </form>
</center>
</body>
</html>

user_update.jsp

<%@ page import="Entity.User" %><%--
  Created by IntelliJ IDEA.
  User: lenovo
  Date: 2020/12/24
  Time: 8:35
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
    <%request.setCharacterEncoding("utf-8"); %>
<body>
    <%
        User user = (User) request.getAttribute("user");
    %>
<center>
    <p>修改</p>
    <form action="/maven-springmvc/user/updateUser" method="post">
        <input hidden type="text" name="id" value="<%=user.getId()%>"/><br/>
        用户名：<input type="text" name="username" value="<%=user.getUsername()%>"/><br/>
        密码：<input type="text" name="password" value="<%=user.getPassword()%>"/><br/>
        <br/>
        <input type="submit" value="修改"/>
        <input type="reset" value="重置"/>
    </form>
</center>
</body>
</html>