jsp 增删改查

使用Idea创建项目

1.新建web application项目 Idea 选择 Java Enterprise -> web application

2.新版本没有web-inf文件夹解决方法进入project structure ->facets->点击小加号，添加web.xml

3.在web/WEB-INF目录下新建两个文件夹，classes用于存放servlet的字节码文件（.class），lib用于存放项目引用的包。

4.进入Project Structure，进入Modules（IDEA的工程）选项卡，将Paths的两个输出路径均改成第2步新建的classes。

5.然后点击Dependencies，选择右边＋号，新建JARS路径，选择第2步创建的lib文件夹 - >Jar Directory

6.进入Artifacts选项卡，将输出目录定为Tomcat安装位置的webapps下新建的该工程文件夹

7.Run->Edit Configurations配置Tomcat，设置tomcat端口等信息。

8.设置默认启动路径http://localhost:9999/web/ 或手动输入

导入servlet.jar包

file->project structure ->libraries->加号->tomcat下的lib 即可。

数据结构

CREATE TABLE `david2018_db`.`User` (

`UserId` INT NOT NULL AUTO_INCREMENT,

`UserName` NVARCHAR(30) NULL,

`PassWord` NVARCHAR(30) NULL,

`Age` INT NULL,

`Sex` NVARCHAR(30) NULL,

PRIMARY KEY (`UserId`));

测试数据

insert into david2018_db.User (UserName,PassWord,Age,Sex) values ('武大郎','123456',35,'男');

insert into david2018_db.User (UserName,PassWord,Age,Sex) values ('潘金莲','123456',18,'女');

insert into david2018_db.User (UserName,PassWord,Age,Sex) values ('西门庆','123456',24,'男');

insert into david2018_db.User (UserName,PassWord,Age,Sex) values ('武松','123456',22,'男');

insert into david2018_db.User (UserName,PassWord,Age,Sex) values ('鲁智深','123456',30,'男');

insert into david2018_db.User (UserName,PassWord,Age,Sex) values ('林冲','123456',32,'男');

创建项目结构

src/com.david.dao 数据访问层

src/com.david.domain 模型层

src/com.david.service 服务层

src/com.david.utils 工具层

src/com.david.web servlet层

创建数据库配置文件src新建 database.properties

driverName=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/david2018_db

user=root

password=1234

导入dbutils的jar包 http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

连接池的jar包 dbcp、pool、logging

http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

http://commons.apache.org/proper/commons-pool/download_pool.cgi

http://commons.apache.org/proper/commons-logging/download_logging.cgi

如果报错，java.lang.NoClassDefFoundError: org/apache/commons/dbutils/ResultSetHandler

Artifacts->点击右下角fix即可

编写domain 实体类

public class User {

    private int UserId;

    private String UserName;

    private String PassWord;

    private int Age;

    private String Sex;

    public int getUserId() {

        return UserId;

    }

    public void setUserId(int UserId) {

        this.UserId = UserId;

    }

    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;

    }

    public int getAge() {

        return Age;

    }

    public void setAge(int Age) {

        this.Age = Age;

    }

    public String getSex() {

        return Sex;

    }

    public void setSex(String Sex) {

        this.Sex = Sex;

    }

}

编写工具类获得数据库连接 utils包下创建DBUtil类

public class DBUtil {

    public static BasicDataSource dataSource = new BasicDataSource();

    static {

        try {

            Properties pp = new Properties();

            InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("database.properties");

            pp.load(is);

            String driver = pp.getProperty("driverName");

            String url = pp.getProperty("url");

            String username = pp.getProperty("user");

            String password = pp.getProperty("password");

            dataSource.setDriverClassName(driver);

            dataSource.setUrl(url);

            dataSource.setUsername(username);

            dataSource.setPassword(password);

            dataSource.setInitialSize(10);//初始化连接数

            dataSource.setMaxIdle(5); //最大空闲

            dataSource.setMinIdle(1); //最小空闲

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static BasicDataSource GetDataSource(){

        return dataSource;

    }

}

编写获得User列表接口 dao创建UserDao类

//获取全部列表

public List<User> GetAllUser() throws SQLException {

    String sql = "select * from User";

    QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

    List<User> users = qr.query(sql,new BeanListHandler<User>(User.class));

    return users;

}

编写User服务调用dao中的 GetAllUser方法

public List<User> GetAllService() throws SQLException {

    UserDao dao = new UserDao();

    return dao.GetAllUser();

}

编写Servlet 将列表数据转发到jsp页面

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    UserService service = new UserService();

    List<User> userList = null;

    try {

        userList = service.GetAllService();

    } catch (SQLException e) {

        e.printStackTrace();

    }

    request.setAttribute("userList", userList);

    request.getRequestDispatcher("userList.jsp").forward(request,response);

}

配置web.xml文件

<servlet>

    <servlet-name>UserList</servlet-name>

    <servlet-class>com.david.web.UserList</servlet-class>

</servlet>

<servlet-mapping>

    <servlet-name>UserList</servlet-name>

    <url-pattern>/userList</url-pattern>

</servlet-mapping>

编写userList.jsp 渲染页面

<%@ page import="com.david.domain.User" %>

<%@ page import="java.util.List" %>

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<html>

<head>

    <title>用户列表</title>

</head>

<body>

<a href="AddUser.jsp">添加用户</a>

<table>

    <tr>

        <th>用户ID</th>

        <th>用户名</th>

        <th>用户密码</th>

        <th>用户年龄</th>

        <th>用户性别</th>

        <th>操作</th>

    </tr>

    <%

        if (request.getAttribute("userList") != null) {

    %>

    <%for (User u : (List<User>) request.getAttribute("userList")) {%>

    <tr>

        <td><%=u.getUserId()%>

        </td>

        <td><%=u.getUserName()%>

        </td>

        <td><%=u.getPassWord()%>

        </td>

        <td><%=u.getAge()%>

        </td>

        <td><%=u.getSex()%>

        </td>

        <td>

            <a href="GetUserById?userId=<%=u.getUserId()%>">修改</a>

            <a href="DeleteUser?userId=<%=u.getUserId()%>">删除</a>

        </td>

    </tr>

    <%}%>

    <%}%>

</table>

</body>

</html>

页面中可以使用el jstl语法我就不写了

列表完成，下面编写添加用户方法

UserDao输入如下方法

//添加用户

public boolean AddUser(User user) throws SQLException {

    String sql = "insert into User (UserName,PassWord,Age,Sex) values (?,?,?,?)";

    QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

    Object[] params = {user.getUserName(),user.getPassWord(),user.getAge(),user.getSex()};

    int result = qr.update(sql,params);

    return result > 0;

}

UserService

public boolean AddUser(User user) throws SQLException {

    UserDao dao = new UserDao();

    return dao.AddUser(user);

}

servlet

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    request.setCharacterEncoding("UTF-8");

    String userName = request.getParameter("userName");

    String passWord = request.getParameter("passWord");

    String age = request.getParameter("age");

    String sex = request.getParameter("sex");

    User u = new User();

    u.setUserName(userName);

    u.setPassWord(passWord);

    u.setAge(Integer.parseInt(age));

    u.setSex(sex);

    UserService users = new UserService();

    try {

        boolean result  = users.AddUser(u);

        if(result){

            response.sendRedirect("userList");

        }else{

        }

    } catch (SQLException e) {

        e.printStackTrace();

    }

}

AddUser.jsp页面如下

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<html>

<head>

    <title>添加用户</title>

</head>

<body>

<form method="post" action="AddUser">

    <p>用户名:<input name="userName"></p>

    <p>密码：<input name="passWord"></p>

    <p>性别：

        <input type="radio" checked value="男" name="sex">男

        <input type="radio" value="女" name="sex">女

    </p>

    <p>年龄：<select name="age">

        <%for (int i = 18; i < 50; i++) {%>

        <option value="<%=i%>"><%=i%></option>

        <%}%>

    </select></p>

    <p>

        <button type="submit">添加</button>

    </p>

    <a href="userList">用户列表</a>

</form>

</body>

</html>

xml文件

<servlet>

    <servlet-name>AddUser</servlet-name>

    <servlet-class>com.david.web.AddUser</servlet-class>

</servlet>

<servlet-mapping>

    <servlet-name>AddUser</servlet-name>

    <url-pattern>/AddUser</url-pattern>

</servlet-mapping>

要设置mysql的字符集编码是utf8的否则会乱码

编写删除方法

dao中

    //删除用户

    public boolean DeleteUser(int userId) throws SQLException {

        String sql = "delete from User where UserId = ?";

        QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

        return qr.update(sql,userId) > 0;

    }

service

    public boolean DeleteUser(int userId) throws SQLException {

        UserDao dao = new UserDao();

        return dao.DeleteUser(userId);

    }

servlet

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String userId = request.getParameter("userId");

        UserService service = new UserService();

        int id = Integer.parseInt(userId);

        try {

            boolean result = service.DeleteUser(id);

            if(result){

                response.sendRedirect("userList");

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

web.xml

    <servlet>

        <servlet-name>DeleteUser</servlet-name>

        <servlet-class>com.david.web.DeleteUser</servlet-class>

    </servlet>

    <servlet-mapping>

        <servlet-name>DeleteUser</servlet-name>

        <url-pattern>/DeleteUser</url-pattern>

    </servlet-mapping>

userList.jsp加入路径即可

<a href="DeleteUser?userId=<%=u.getUserId()%>">删除</a>

修改

dao

    //查找一条User通过UserId

    public User GetUserByUserId(int userId) throws SQLException {

        String sql = "select * from User where UserId = ?";

        QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

        User user = qr.query(sql, userId, new BeanHandler<User>(User.class));

        return user;

    }

    //修改user

    public boolean UpdateUser(User user) throws SQLException {

        String sql = "update User set UserName = ?,PassWord = ?,Age = ?, Sex = ? where UserId = ?";

        Object [] params = {user.getUserName(),user.getPassWord(),user.getAge(),user.getSex(),user.getUserId()};

        QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

        int result = qr.update(sql,params);

        return result > 0;

    }

service

    public User GetUserByUserId(int userId) throws SQLException {

        UserDao dao = new UserDao();

        return dao.GetUserByUserId(userId);

    }

    //修改user

    public boolean UpdateUser(User user) throws SQLException {

        UserDao dao = new UserDao();

        return dao.UpdateUser(user);

    }

servlet

GetUserById

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String userId = request.getParameter("userId");

        UserService service = new UserService();

        try {

            User u =service.GetUserByUserId(Integer.parseInt(userId));

            request.setAttribute("user",u);

            request.getRequestDispatcher("Update.jsp").forward(request,response);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

updateUser

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("UTF-8");

        String userId = request.getParameter("userId");

        String userName = request.getParameter("userName");

        String passWord = request.getParameter("passWord");

        String age = request.getParameter("age");

        String sex = request.getParameter("sex");

        User u = new User();

        u.setUserName(userName);

        u.setPassWord(passWord);

        u.setAge(Integer.parseInt(age));

        u.setSex(sex);

        u.setUserId(Integer.parseInt(userId));

        UserService users = new UserService();

        try {

            boolean result = users.UpdateUser(u);

            if (result) {

                response.sendRedirect("userList");

            } else {

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

userList.jsp 加入修改入口

<a href="GetUserById?userId=<%=u.getUserId()%>">修改</a>

updateuser.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<html>

<head>

    <title>修改用户</title>

</head>

<body>

<%

User u = (User)request.getAttribute("user");

%>

<form method="post" action="UpdateUser">

    <p>用户Id：<input name="userId" value="<%=u.getUserId()%>" readonly></p>

    <p>用户名:<input name="userName" value="<%=u.getUserName()%>"></p>

    <p>密码：<input name="passWord" value="<%=u.getPassWord()%>"></p>

    <p>性别：

        <input type="radio" <%if(u.getSex().equals("男")){%> checked <%}%> value="男" name="sex">男

        <input type="radio" <%if(u.getSex().equals("女")){%> checked <%}%> value="女" name="sex">女

    </p>

    <p>年龄：<select name="age">

        <%for (int i = 18; i < 50; i++) {%>

        <option value="<%=i%>"><%=i%></option>

        <%}%>

    </select>

    <script>

        for(var i = 0;i<document.getElementsByTagName("option").length;i++){

            var option = document.getElementsByTagName("option")[i];

            if(option.value == "<%=u.getAge()%>"){

                option.selected = true;

            }

        }

    </script>

    </p>

    <p>

        <button type="submit">确认修改</button>

    </p>

    <a href="userList">用户列表</a>

</form>

</body>

</html>

xml.web

    <servlet>

        <servlet-name>UpdateUser</servlet-name>

        <servlet-class>com.david.web.UpdateUser</servlet-class>

    </servlet>

    <servlet-mapping>

        <servlet-name>UpdateUser</servlet-name>

        <url-pattern>/UpdateUser</url-pattern>

    </servlet-mapping>

    <servlet>

        <servlet-name>GetUserById</servlet-name>

        <servlet-class>com.david.web.GetUserById</servlet-class>

    </servlet>

    <servlet-mapping>

        <servlet-name>GetUserById</servlet-name>

        <url-pattern>/GetUserById</url-pattern>

    </servlet-mapping>

相关文章