初学者Java （十四） Idea Servlet 与Mysql进行简单的增删改查

一:前提准备工作

1：需要安装  Tomcat 运行环境 加载jar包

2：单元测试环境可以安装，JUnit4  Jar包

3：加载 JDK

4：加载数据库jar包

5：安装Servlet-api  的jar包

6：安装 jsp-api  的jar包

项目整体框架，开发只需要添加项目的两块：后端：src  前端：web

 

二：底层数据库连接：

1：代码：首先需要建应该properties后缀名的文件。

# driver=com.mysql.jdbc.Driver
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://10.3.90.84:3306/crs_miniservice?serverTimezone=UTC&characterEncoding=utf-8
username=crs_mysql_test
password=crs123456

2:新建一个dbhelp的java类，里面去上面的配置信息。

DBHelper 类

package cn.servlet.dbhelp;

import cn.servlet.utils.ConfigManager;
import java.sql.*;

public class DBHelper {
    //region attributes
    Connection conn = null;
    PreparedStatement preparedStatement;
    ResultSet resultSet = null;
    //endregion

    //region method
    //数据库连接
    public boolean getConnection(){
        try {
            Class.forName(ConfigManager.getInstance().getPropertyValue("driver"));
            conn = DriverManager.getConnection(ConfigManager.getInstance().getPropertyValue("url"),
                    ConfigManager.getInstance().getPropertyValue("username"),
                    ConfigManager.getInstance().getPropertyValue("password"));
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //查询，返回结果集
    public ResultSet getResultSet(String sql,Object[] parms)  {
        if (getConnection()) {
            try {
                preparedStatement = conn.prepareStatement(sql);
                for (int i = 0; i < parms.length; i++) {
                    preparedStatement.setObject(i+1,parms[i]);
                }
                resultSet = preparedStatement.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  resultSet;
    }

    //删除，更新，添加，返回受影响的行数
    public int getExecuteUpdateSQL(String sql,Object[] parms) throws SQLException, ClassNotFoundException {
        int rows=0;
        if (getConnection()) {
            try {
                preparedStatement = conn.prepareStatement(sql);
                for (int i = 0; i < parms.length; i++) {
                    preparedStatement.setObject(i+1,parms[i]);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rows = preparedStatement.executeUpdate();
        }
        return  rows;
    }

    //endregion
}

配置文件类：

ConfigManager类的代码如下：根据下面方法获取properties文件的配置信息

 

package cn.servlet.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

//数据库配置工具类
public class ConfigManager {
    //region attributes
    private static ConfigManager configManager;
    private Properties properties;
    private InputStream resourceAsStream;
    //endregion

    //region methods
    public ConfigManager() {
        properties = new Properties();
        String filePath="./resources/database.properties";
        resourceAsStream = ConfigManager.class.getClassLoader().getResourceAsStream(filePath);
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 创建ConfigManager实例
    public static ConfigManager getInstance(){
        if (configManager ==null) {
            configManager = new ConfigManager();
        }
        return configManager;
    }
    //通过key获取属性值
    public String getPropertyValue(String key){
        return properties.getProperty(key);
    }
    //endregion
}

三：实体类：UserInfo

package cn.servlet.entity;

public class UserInfo {
    //region attributes
    public int id;
    private String userName;
    private int sex;
    private int age;
    private String job;
    private String description;
    //endregion

    //region method
    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 int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
    //endregion
}

四：服务层：Service层，需要有接口实现，

接口代码：UserInfoService

 

package cn.servlet.service;

import cn.servlet.entity.UserInfo;

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

public interface UserInfoService {
    //获取所有用户
    public List<UserInfo> getAllUsers() throws SQLException;
    //通过用户id获取用户信息
    public UserInfo getUserByUserId(int userId) throws SQLException;
    //单个添加用户
    public int addUser(UserInfo userInfo) throws SQLException, ClassNotFoundException;
    //修改用户
    public int updateUserByUserId(UserInfo userInfo) throws SQLException, ClassNotFoundException;
    //删除用户
    public int delUserByUserId(int userId);
    //判断用户是否存在
    public boolean isExistUserByUserId(int userId);
}

接口实现代码：UserInfoServiceImp

package cn.servlet.service.imp;

import cn.servlet.dao.UserInfoDAO;
import cn.servlet.dao.imp.UserInfoDAOImp;
import cn.servlet.entity.UserInfo;
import cn.servlet.service.UserInfoService;
import java.sql.SQLException;
import java.util.List;

//用户信息访问Service
public class UserInfoServiceImp implements UserInfoService {
    UserInfoDAO userInfoDAO = null;
    public UserInfoServiceImp() {
        userInfoDAO = new UserInfoDAOImp();
    }

    //获取所有用户信息
    @Override
    public List<UserInfo> getAllUsers() throws SQLException {
        return userInfoDAO.getAllUsers();
    }
    //通过用户id获取用户信息(非批量)
    @Override
    public UserInfo getUserByUserId(int userId) throws SQLException {
        return userInfoDAO.getUserByUserId(userId);
    }
    //添加用户(非批量)
    @Override
    public int addUser(UserInfo userInfo) throws SQLException, ClassNotFoundException {
        return userInfoDAO.addUser(userInfo);
    }
    //更新用户(非批量)
    @Override
    public int updateUserByUserId(UserInfo userInfo) throws SQLException, ClassNotFoundException {
        return userInfoDAO.updateUserByUserId(userInfo);
    }
    //通过用户id删除用户(非批量)
    @Override
    public int delUserByUserId(int userId) {
        return userInfoDAO.delUserByUserId(userId);
    }

    @Override
    public boolean isExistUserByUserId(int userId) {
        return false;
    }
}

五：控制器 代码： AddUserInfo  控制器代码需要继承HttpServlet 类

并且该方法时单例的，只能进入调用doGet方法，

package cn.servlet.controller;

import cn.servlet.entity.UserInfo;
import cn.servlet.service.UserInfoService;
import cn.servlet.service.imp.UserInfoServiceImp;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;

public class AddUserInfo extends HttpServlet {
    //region attributes
    private static final long serialVersionUID = 5804433309240831094L;
    private UserInfo userInfo = null;
    private UserInfoService userInfoService = null;
    //endregion
    //region method
    public AddUserInfo() {
        super();
        userInfo = new UserInfo();
        userInfoService = new UserInfoServiceImp();
    }

    public void destroy() {
        super.destroy();
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int iSex=1;
        String username = request.getParameter("username");
        String sex = request.getParameter("sex");
        if (sex == "男"){
            iSex=0;
        }
        int age = Integer.parseInt(request.getParameter("age"));
        String job = request.getParameter("job");
        String description = request.getParameter("description");

         userInfo.setUserName(username);
         userInfo.setSex(iSex);
         userInfo.setAge(age);
         userInfo.setJob(job);
         userInfo.setDescription(description);

        try {
            userInfoService.addUser(userInfo);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

//        if(studentService.findStudent(stu)){//如果学生已经存在，就不添加
//            response.sendRedirect("/Student/pages/stuList.jsp");
//        }else{//如果学生不存在，就添加
//            String opr = request.getParameter("opr");//根据传的opr参数决定是添加学生还是修改学生信息
//            int n = 0;
//            if(opr.equals("addStu")){
//                n = studentService.addStudent(stu);
//            }else if(opr.equals("modifyStu")){
//                int id = Integer.parseInt(request.getParameter("stuId"));
//                stu.setId(id);
//                n = studentService.modifyStudent(stu);
//            }
//
//            if(n>0){
//                response.sendRedirect("/Student/pages/stuList.jsp");
//            }else{
//                response.sendRedirect("/Student/pages/addStu.jsp");
//            }
//        }
    }

    public void init() throws ServletException {

    }
    //endregion
}

 

控制器 删除的类代码：DeleteUserInfo

package cn.servlet.controller;

import cn.servlet.service.UserInfoService;
import cn.servlet.service.imp.UserInfoServiceImp;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class DeleteUserInfo  extends HttpServlet {

    private UserInfoService userInfoService = null;


    public DeleteUserInfo() {
        super();

        userInfoService = new UserInfoServiceImp();
    }
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //int UserId= 1084999;//  Integer.parseInt(request.getParameter("userId"));

        System.out.println(request.getParameter("userId"));

        int UserId=  Integer.parseInt(request.getParameter("userId"));
        System.out.println("测试页面传值");
        userInfoService.delUserByUserId(UserId);
    }
}

 

六：前端页面代码：

一：查询页面信息jsp，这里查询没有使用servlet的配置调用，而是直接引用包名，然后调用。

<%@ page import="cn.servlet.entity.UserInfo" %>
<%@ page import="cn.servlet.service.UserInfoService" %>
<%@ page import="cn.servlet.service.imp.UserInfoServiceImp" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: wangjm
  Date: 2019/7/1
  Time: 12:33
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>

<html>
<head>
    <title>用户列表页</title>
</head>
<body>
    <table class="table">
    <tr>
        <td>id</td>
        <td>用户名</td>
        <td>性别</td>
        <td>年龄</td>
        <td>工作</td>
        <td>描述</td>
        <td>操作</td>
    </tr>
    <%--第一种写法，使用JSP原生标签 --%>
    <%
        UserInfoService userInfoService=new UserInfoServiceImp();
        List<UserInfo> list = userInfoService.getAllUsers();
        for (UserInfo userInfo : list) {
    %>

    <tr>
        <td><%=userInfo.getId() %></td>
        <td><%=userInfo.getUserName() %></td>
        <td><%=userInfo.getSex() %></td>
        <td><%=userInfo.getAge() %></td>
        <td><%=userInfo.getJob() %></td>
        <td><%=userInfo.getDescription() %></td>
        </td>
        <td><a href="">查看&nbsp;&nbsp;</a>
            <a href=">">修改&nbsp;&nbsp;</a>
           <%-- <a href="%>"
               onclick="javascript:del(${userInfo.getId})">删除&nbsp;&nbsp;</a>--%>

            <a href="${pageContext.request.contextPath }/controller/deleteUsers?userId=<%=userInfo.getId()%>" >删除&nbsp;&nbsp;</a>
        </td>
    </tr>
    <%} %>
</table>

    <script type="text/javascript">
        function del(id){
            var flag = confirm("确定要删除吗？");
            if(flag){
                window.location= "/controller/deleteUsers?opr=addStu"
                // window.location="pages/userList.jsp?type=delete&id="+id
            }
        }
    </script>

</body>
</html>

 

新增Jsp页面：

<%--
  Created by IntelliJ IDEA.
  User: wangjm
  Date: 2019/7/1
  Time: 19:20
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>添加学生</title>
    <%--<link href="${pageContext.request.contextPath }/css/common.css" rel="stylesheet" type="text/css"/>--%>
    <script type="text/javascript">
        function check() {
            for (var i = 0; i < document.form1.elements.length - 1; i++) {
                if (document.form1.elements[i].value == "") {
                    alert("当前表单不能有空项");
                    document.form1.elements[i].focus();
                    return false;
                }
            }
            return true;

        }

    </script>
</head>
<body>
<h2>添加学生信息</h2>
<form action="${pageContext.request.contextPath }/controller/addUser?opr=addStu" name="form1" method="post"
      onsubmit="return check()">
    <table id="stuDetailList">
        <tr>
            <td>用户名：</td>
            <td><input type="text" name="username"></td>
        </tr>
        <tr>
            <td>性别：</td>
            <td><input type="radio" name="sex" value="男" checked="checked">男<input type="radio" name="sex" value="女">女
            </td>
        </tr>
        <tr>
            <td>年龄：</td>
            <td><input type="text" name="age"></td>
        </tr>
        <tr>
            <td>工作：</td>
            <td><input type="text" name="job"></td>
            </td>
        </tr>
        <tr>
            <td>自我介绍：</td>
            <td><textarea name="description" cols="40" rows="6"></textarea></td>
        </tr>
        <tr>
            <td colspan="2" style="text-align: center"><input type="submit" value="提交信息"></td>
        </tr>
    </table>
</form>
<body>
</html>

 

七：配置servlet调用的文件：

代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
    <servlet>
        <servlet-name>addUsers</servlet-name>
        <servlet-class>cn.servlet.controller.AddUserInfo</servlet-class>
    </servlet>
    
    <servlet-mapping>
        <servlet-name>addUsers</servlet-name>
        <url-pattern>/controller/addUsers</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>deleteUsers</servlet-name>
        <servlet-class>cn.servlet.controller.DeleteUserInfo</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>deleteUsers</servlet-name>
        <url-pattern>/controller/deleteUsers</url-pattern>
    </servlet-mapping>

</web-app>

 

完毕