【vue+jdbc实现数据库操作java web前后分离版】

穏场辉

已于 2024-07-23 00:00:28 修改

阅读量350

点赞数 14

文章标签：前端 vue.js 数据库 java

于 2024-07-22 23:53:15 首次发布

本文链接：https://blog.csdn.net/weixin_46198124/article/details/140621177

版权

创建数据库

drop table if exists users;
create table users
(
  id               bigint(20)      not null   auto_increment  comment '用户id',
  username         varchar(100)    default ''                 comment '用户名',
  phone            varchar(100)    default ''                 comment '电话',
  email            varchar(100)    default ''                 comment '邮箱',
  primary key (id)
) engine=innodb comment='用户表';

后端代码

根据模版创建javaweb项目(此步骤省略)
链接: 百度网盘
在主目录下创建lib目录添加jar包右击jar选择Add as Library 在选择相对应选择即可添加成功

创建实体类

public class User {

    private int id;
    private String username;
    private String phone;
    private String email;

    public User() {
    }

    public User(int id, String username, String phone, String email) {
        this.id = id;
        this.username = username;
        this.phone = phone;
        this.email = email;
    }

    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 getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", phone='" + phone + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

jdbc连接数据库

public class JdbcConnection {
    private static Connection conn = null;

    private static final String username = "你的账号";  //默认root
    private static final String password = "你的密码";

    public static Connection dbConnection() {

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException ce) {
            System.out.print(ce);
        }
        try {
            String url = "jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException ce) {
            System.out.print(ce);
        }
        return conn;
    }

    public static void closeConnection(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeConnection(Connection conn, PreparedStatement pstmt) {
        try {
            if (pstmt != null)
                pstmt.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

创建Servlet

@WebServlet("/users/*")
public class UserServlet extends HttpServlet {

    private UserService userService = new UserService();


    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 获取用户列表
        List<User> users = userService.getAllUsers();
        String json = new Gson().toJson(users);
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().write(json);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 添加用户
        User user = new GsonBuilder()
                .registerTypeAdapter(int.class, new IntTypeAdapter())
                .registerTypeAdapter(Integer.class, new IntTypeAdapter()).create()
                .fromJson(req.getReader(), User.class);
        userService.addUser(user);
        System.out.println("添加成功" + user);
    }

    @Override
    protected void doPut(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 更新用户
        String pathInfo = req.getPathInfo();
        if (pathInfo != null) {
            String[] pathParts = pathInfo.split("/");
            if (pathParts.length > 1) {
                int id = Integer.parseInt(pathParts[1]);
                User user = new Gson().fromJson(req.getReader(), User.class);
                userService.updateUser(id, user);
                System.out.println("编辑成功" + id);
            }
        }
    }

    @Override
    protected void doDelete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 删除用户
        String pathInfo = req.getPathInfo();
        if (pathInfo != null) {
            String[] pathParts = pathInfo.split("/");
            if (pathParts.length > 1) {
                int id = Integer.parseInt(pathParts[1]);
                userService.deleteUser(id);
                System.out.println("删除成功" + id);
            }
        }

    }
}

链接: 百度网盘
在主目录下创建lib目录添加jar包右击jar选择Add as Library 在选择相对应选择即可添加成功

工具类

public class IntTypeAdapter extends TypeAdapter<Number> {

    @Override
    public void write(JsonWriter out, Number value)
            throws IOException {
        out.value(value);
    }

    @Override
    public Number read(JsonReader in) throws IOException {
        if (in.peek() == JsonToken.NULL) {
            in.nextNull();
            return null;
        }
        try {
            String result = in.nextString();
            if ("".equals(result)) {
                return null;
            }
            return Integer.parseInt(result);
        } catch (NumberFormatException e) {
            throw new JsonSyntaxException(e);
        }
    }
}

数据库操作

public class UserService {

    // 查询用户
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 连接数据库
            conn = JdbcConnection.dbConnection();
            // 查询产品信息
            String querySql = "SELECT id, username, phone, email FROM users";
            pstmt = conn.prepareStatement(querySql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                User user = new User(id, username, phone, email);
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接
            JdbcConnection.closeConnection(conn, pstmt, rs);
        }
        return users;
    }


    // 添加用户
    public void addUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 连接数据库
            conn = JdbcConnection.dbConnection();
            // 添加用户
            String addSql = "INSERT INTO users(username, phone, email) VALUES(?, ?, ?)";
            pstmt = conn.prepareStatement(addSql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPhone());
            pstmt.setString(3, user.getEmail());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接
            JdbcConnection.closeConnection(conn, pstmt);
        }

    }

    // 修改用户信息
    public void updateUser(int id, User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 连接数据库
            conn = JdbcConnection.dbConnection();
            // 更新产品信息
            String updateSql = "UPDATE users SET phone = ?, email = ?, username = ? WHERE id = ?";
            pstmt = conn.prepareStatement(updateSql);
            pstmt.setString(1, user.getPhone());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getUsername());
            pstmt.setInt(4, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接
            JdbcConnection.closeConnection(conn, pstmt);
        }

    }

    // 删除用户
    public void deleteUser(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 连接数据库
            conn = JdbcConnection.dbConnection();
            // 删除用户
            String deleteSql = "DELETE FROM users WHERE id = ?";
            pstmt = conn.prepareStatement(deleteSql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接
            JdbcConnection.closeConnection(conn, pstmt);
        }
    }
}

跨域

@WebFilter(filterName = "Filter_CrossOrigin",urlPatterns = "/*")
public class Filter_CrossOrigin implements Filter {

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws ServletException, IOException {
        HttpServletRequest request1 = (HttpServletRequest) request;
        HttpServletResponse response1 = (HttpServletResponse) response;
        request1.setCharacterEncoding("utf-8");
        response1.setHeader("Access-Control-Allow-Origin", request1.getHeader("origin"));
        response1.setHeader("Access-Control-Allow-Methods", "POST, GET, PUT, OPTIONS, DELETE");
        response1.setHeader("Access-Control-Max-Age", "3600");
        response1.setHeader("Access-Control-Allow-Headers", "x-requested-with, Content-Type");
        response1.setHeader("Access-Control-Allow-Credentials", "true");
        chain.doFilter(request1, response1);
    }
}

前端代码

测试时注意后端端口和路径我的是http://localhost:8080/j_vue_war_exploded/users

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>User Management System</title>
    <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
</head>
<body>

<div id="app">
    <!-- 用户列表 -->
    <table>
        <tr>
            <th>ID</th>
            <th>Username</th>
            <th>Phone</th>
            <th>Email</th>
            <th>Actions</th>
        </tr>
        <tr v-for="user in users">
            <td>{{ user.id }}</td>
            <td>{{ user.username }}</td>
            <td>{{ user.phone }}</td>
            <td>{{ user.email }}</td>
            <td>
                <button @click="editUser(user)">编辑</button>
                <button @click="deleteUser(user.id)">删除</button>
            </td>
        </tr>
    </table>

    <!-- 添加/编辑用户表单 -->
    <form @submit.prevent="submitForm">
        <input type="hidden" v-model="form.id">
        <input type="text" v-model="form.username" placeholder="Username">
        <input type="text" v-model="form.phone" placeholder="Phone">
        <input type="email" v-model="form.email" placeholder="Email">
        <button type="submit">添加/修改</button>
    </form>
</div>

<script>
    new Vue({
        el: '#app',
        data: {
            users: [],
            form: {
                id: '',
                username: '',
                phone: '',
                email: ''
            }
        },
        methods: {
            fetchUsers() {
                axios.get('http://localhost:8080/j_vue_war_exploded/users').then(response => {
                    this.users = response.data;
                });
            },
            editUser(user) {
                this.form = {...user};
            },
            deleteUser(id) {
                axios.delete(`http://localhost:8080/j_vue_war_exploded/users/${id}`).then(() => {
                    this.fetchUsers();
                });
            },
            submitForm() {
                const method = this.form.id ? 'put' : 'post';
                const url = this.form.id ? `http://localhost:8080/j_vue_war_exploded/users/${this.form.id}` : 'http://localhost:8080/j_vue_war_exploded/users';
                axios[method](url, this.form).then(() => {
                    this.fetchUsers();
                    this.form = { id: '', username: '', phone: '', email: '' };
                });
            }
        },
        mounted() {
            this.fetchUsers();
        }
    });
</script>

</body>
</html>