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

懒人版: https://download.csdn.net/download/weixin_46198124/89570121

创建数据库

droptableifexists users;createtable users
(
  id               bigint(20)notnullauto_incrementcomment'用户id',
  username         varchar(100)default''comment'用户名',
  phone            varchar(100)default''comment'电话',
  email            varchar(100)default''comment'邮箱',primarykey(id))engine=innodbcomment='用户表';

后端代码

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

创建实体类

publicclassUser{privateint id;privateString username;privateString phone;privateString email;publicUser(){}publicUser(int id,String username,String phone,String email){this.id = id;this.username = username;this.phone = phone;this.email = email;}publicintgetId(){return id;}publicvoidsetId(int id){this.id = id;}publicStringgetUsername(){return username;}publicvoidsetUsername(String username){this.username = username;}publicStringgetPhone(){return phone;}publicvoidsetPhone(String phone){this.phone = phone;}publicStringgetEmail(){return email;}publicvoidsetEmail(String email){this.email = email;}@OverridepublicStringtoString(){return"User{"+"id="+ id +", username='"+ username +'\''+", phone='"+ phone +'\''+", email='"+ email +'\''+'}';}}

jdbc连接数据库

publicclassJdbcConnection{privatestaticConnection conn =null;privatestaticfinalString username ="你的账号";//默认rootprivatestaticfinalString password ="你的密码";publicstaticConnectiondbConnection(){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;}publicstaticvoidcloseConnection(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();}}publicstaticvoidcloseConnection(Connection conn,PreparedStatement pstmt){try{if(pstmt !=null)
                pstmt.close();if(conn !=null)
                conn.close();}catch(SQLException e){
            e.printStackTrace();}}}

创建Servlet

@WebServlet("/users/*")publicclassUserServletextendsHttpServlet{privateUserService userService =newUserService();@OverrideprotectedvoiddoGet(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{// 获取用户列表List<User> users = userService.getAllUsers();String json =newGson().toJson(users);
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().write(json);}@OverrideprotectedvoiddoPost(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{// 添加用户User user =newGsonBuilder().registerTypeAdapter(int.class,newIntTypeAdapter()).registerTypeAdapter(Integer.class,newIntTypeAdapter()).create().fromJson(req.getReader(),User.class);
        userService.addUser(user);System.out.println("添加成功"+ user);}@OverrideprotectedvoiddoPut(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{// 更新用户String pathInfo = req.getPathInfo();if(pathInfo !=null){String[] pathParts = pathInfo.split("/");if(pathParts.length >1){int id =Integer.parseInt(pathParts[1]);User user =newGson().fromJson(req.getReader(),User.class);
                userService.updateUser(id, user);System.out.println("编辑成功"+ id);}}}@OverrideprotectedvoiddoDelete(HttpServletRequest req,HttpServletResponse resp)throwsServletException,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 在选择相对应选择即可添加成功

工具类

publicclassIntTypeAdapterextendsTypeAdapter<Number>{@Overridepublicvoidwrite(JsonWriter out,Number value)throwsIOException{
        out.value(value);}@OverridepublicNumberread(JsonReader in)throwsIOException{if(in.peek()==JsonToken.NULL){
            in.nextNull();returnnull;}try{String result = in.nextString();if("".equals(result)){returnnull;}returnInteger.parseInt(result);}catch(NumberFormatException e){thrownewJsonSyntaxException(e);}}}

数据库操作

publicclassUserService{// 查询用户publicList<User>getAllUsers(){List<User> users =newArrayList<>();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 =newUser(id, username, phone, email);
                users.add(user);}}catch(SQLException e){
            e.printStackTrace();}finally{// 关闭数据库连接JdbcConnection.closeConnection(conn, pstmt, rs);}return users;}// 添加用户publicvoidaddUser(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);}}// 修改用户信息publicvoidupdateUser(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);}}// 删除用户publicvoiddeleteUser(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 ="/*")publicclassFilter_CrossOriginimplementsFilter{@OverridepublicvoiddoFilter(ServletRequest request,ServletResponse response,FilterChain chain)throwsServletException,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

<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>User Management System</title><scriptsrc="https://cdn.jsdelivr.net/npm/[email protected]/dist/vue.min.js"></script><scriptsrc="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script></head><body><divid="app"><!-- 用户列表 --><table><tr><th>ID</th><th>Username</th><th>Phone</th><th>Email</th><th>Actions</th></tr><trv-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><!-- 添加/编辑用户表单 --><[email protected]="submitForm"><inputtype="hidden"v-model="form.id"><inputtype="text"v-model="form.username"placeholder="Username"><inputtype="text"v-model="form.phone"placeholder="Phone"><inputtype="email"v-model="form.email"placeholder="Email"><buttontype="submit">添加/修改</button></form></div><script>newVue({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>

实现效果图