使用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>