jsp 增删改查

2018/05/11 15:47
阅读数 36
使用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
 
连接池的jar包 dbcp、pool、logging
 
如果报错,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>

 

 
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部