webdemo.sql
-- MySQL dump 10.13 Distrib 8.0.11, for macos10.13 (x86_64) -- -- Host: 127.0.0.1 Database: webdemo -- ------------------------------------------------------ -- Server version 8.0.11 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; SET NAMES utf8mb4 ; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `classes` -- DROP TABLE IF EXISTS `classes`; /*!40101 SET @saved_cs_client = @@character_set_client */; SET character_set_client = utf8mb4 ; CREATE TABLE `classes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `classes` -- LOCK TABLES `classes` WRITE; /*!40000 ALTER TABLE `classes` DISABLE KEYS */; INSERT INTO `classes` VALUES (1,'一班'),(2,'二班'),(3,'三班'); /*!40000 ALTER TABLE `classes` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `student` -- DROP TABLE IF EXISTS `student`; /*!40101 SET @saved_cs_client = @@character_set_client */; SET character_set_client = utf8mb4 ; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `cid` (`cid`), CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `classes` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `student` -- LOCK TABLES `student` WRITE; /*!40000 ALTER TABLE `student` DISABLE KEYS */; INSERT INTO `student` VALUES (1,'张三',20,1),(2,'李四',21,1),(3,'王五',22,2),(4,'小明',18,2),(5,'小红',17,3); /*!40000 ALTER TABLE `student` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2019-07-30 22:03:45
55.md
###Java Web 开发核心组件 Servlet：负责业务逻辑处理，接收客户端请求，作出响应。 JSP：负责用户交互界面，将业务数据展示给用户，并且提供用户操作界面。 EL：简化 JSP 复杂的编码。 JSTL：提供了一组标准标签库，进行开发，使得 JSP 代码更加简洁，JSTL + EL。 Ajax：异步加载，局部刷新。 Filter：对请求和响应进行过滤。 客户端 —> Controller —> Service —> Repository —> DB
index.jsp
<%-- Created by IntelliJ IDEA. User: southwind Date: 2019-07-30 Time: 20:03 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <title>$Title$</title> </head> <body> <table> <tr> <th>学生编号</th> <th>学生姓名</th> <th>学生年龄</th> <th>所在班级</th> <th>操作</th> </tr> <c:forEach items="${requestScope.list}" var="student"> <tr> <td>${student.id}</td> <td>${student.name}</td> <td>${student.age}</td> <td>${student.classes.name}</td> <td> <a href="/student.do?method=deleteById&id=${student.id}">删除</a> <a href="/student.do?method=findById&id=${student.id}">修改</a> </td> </tr> </c:forEach> </table> </body> </html>
update.jsp
<%-- Created by IntelliJ IDEA. User: southwind Date: 2019-07-30 Time: 21:50 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <title>Title</title> </head> <body> <form action="/student.do?method=update" method="post"> 学生编号:<input type="text" readonly value="${student.id}" name="id"/><br/> 学生姓名:<input type="text" value="${student.name}" name="name"/><br/> 学生年龄:<input type="text" value="${student.age}" name="age"/><br/> 所在班级:<select> <c:forEach items="${list}" var="classes"> <option <c:if test="${student.cid == classes.id}"> selected </c:if> value="${classes.id}">${classes.name}</option> </c:forEach> </select> </form> </body> </html>
web.xml
<?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"> </web-app>
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <named-config name="testc3p0"> <!-- 指定连接数据源的基本属性 --> <property name="user">root</property> <property name="password">root</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/webdemo?useUnicode=true&characterEncoding=UTF-8</property> <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 --> <property name="acquireIncrement">5</property> <!-- 初始化数据库连接池时连接的数量 --> <property name="initialPoolSize">5</property> <!-- 数据库连接池中的最小的数据库连接数 --> <property name="minPoolSize">5</property> <!-- 数据库连接池中的最大的数据库连接数 --> <property name="maxPoolSize">10</property> </named-config> </c3p0-config>
StudentServlet.java
package com.southwind.controller; import com.southwind.entity.Classes; import com.southwind.entity.Student; import com.southwind.service.StudentService; import com.southwind.service.impl.StudentServiceImpl; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; @WebServlet("/student.do") public class StudentServlet extends HttpServlet { private StudentService studentService = new StudentServiceImpl(); @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String method = req.getParameter("method"); if(method==null){ method="findAll"; } switch (method){ case "findAll": List<Student> list = studentService.findAll(); req.setAttribute("list",list); req.getRequestDispatcher("index.jsp").forward(req,resp); break; case "deleteById": String idStr = req.getParameter("id"); Integer id = Integer.parseInt(idStr); studentService.deleteById(id); resp.sendRedirect("/student.do?method=findAll"); break; case "findById": idStr = req.getParameter("id"); id = Integer.parseInt(idStr); Student student = studentService.findById(id); List<Classes> classList = studentService.findAllClasses(); req.setAttribute("student",student); req.setAttribute("list",classList); req.getRequestDispatcher("update.jsp").forward(req,resp); break; } } }
Classes.java
package com.southwind.entity; import java.util.List; public class Classes { private Integer id; private String name; private List<Student> students; public Classes(Integer id, String name) { this.id = id; this.name = name; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
Student.java
package com.southwind.entity; public class Student { private Integer id; private String name; private Integer age; private Integer cid; private Classes classes; public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public Student(Integer id, String name, Integer age, Integer cid) { this.id = id; this.name = name; this.age = age; this.cid = cid; } public Classes getClasses() { return classes; } public void setClasses(Classes classes) { this.classes = classes; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } }
ClassesRepository.java
package com.southwind.repository; import com.southwind.entity.Classes; import java.util.List; public interface ClassesRepository { public Classes findById(Integer id); public List<Classes> findAll(); }
StudentRepository.java
package com.southwind.repository; import com.southwind.entity.Student; import java.util.List; public interface StudentRepository { public List<Student> findAll(); public void deleteById(Integer id); public Student findById(Integer id); }
ClassesRepositoryImpl.java
package com.southwind.repository.impl; import com.southwind.entity.Classes; import com.southwind.repository.ClassesRepository; import com.southwind.util.JDBCTools; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class ClassesRepositoryImpl implements ClassesRepository { @Override public Classes findById(Integer id) { Connection connection = JDBCTools.getConnection(); String sql = "select * from classes where id = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; Classes classes = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,id); resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ String name = resultSet.getString(2); classes = new Classes(id,name); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCTools.release(connection,preparedStatement,resultSet); } return classes; } @Override public List<Classes> findAll() { Connection connection = JDBCTools.getConnection(); String sql = "select * from classes"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; List<Classes> list = new ArrayList<>(); try { preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ int id = resultSet.getInt(1); String name = resultSet.getString(2); Classes classes = new Classes(id,name); list.add(classes); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCTools.release(connection,preparedStatement,resultSet); } return list; } }
StudentRepositoryImpl.java
package com.southwind.repository.impl; import com.southwind.entity.Student; import com.southwind.repository.StudentRepository; import com.southwind.util.JDBCTools; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class StudentRepositoryImpl implements StudentRepository { @Override public List<Student> findAll() { Connection connection = JDBCTools.getConnection(); String sql = "select * from student"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; List<Student> list = new ArrayList<>(); try { preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ int id = resultSet.getInt(1); String name = resultSet.getString(2); int age = resultSet.getInt(3); int cid = resultSet.getInt(4); list.add(new Student(id,name,age,cid)); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCTools.release(connection,preparedStatement,resultSet); } return list; } @Override public void deleteById(Integer id) { Connection connection = JDBCTools.getConnection(); String sql = "delete from student where id = ?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,id); preparedStatement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { JDBCTools.release(connection,preparedStatement,null); } } @Override public Student findById(Integer id) { Connection connection = JDBCTools.getConnection(); String sql = "select * from student where id = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; Student student = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,id); resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ String name = resultSet.getString(2); int age = resultSet.getInt(3); int cid = resultSet.getInt(4); student = new Student(id,name,age,cid); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCTools.release(connection,preparedStatement,resultSet); } return student; } }
StudentService.java
package com.southwind.service; import com.southwind.entity.Classes; import com.southwind.entity.Student; import java.util.List; public interface StudentService { public List<Student> findAll(); public void deleteById(Integer id); public Student findById(Integer id); public List<Classes> findAllClasses(); }
StudentServiceImp.java
package com.southwind.service.impl; import com.southwind.entity.Classes; import com.southwind.entity.Student; import com.southwind.repository.ClassesRepository; import com.southwind.repository.StudentRepository; import com.southwind.repository.impl.ClassesRepositoryImpl; import com.southwind.repository.impl.StudentRepositoryImpl; import com.southwind.service.StudentService; import java.util.List; public class StudentServiceImpl implements StudentService { private StudentRepository studentRepository = new StudentRepositoryImpl(); private ClassesRepository classesRepository = new ClassesRepositoryImpl(); @Override public List<Student> findAll() { List<Student> list = studentRepository.findAll(); for(Student student:list){ Classes classes = classesRepository.findById(student.getCid()); student.setClasses(classes); } return list; } @Override public void deleteById(Integer id) { studentRepository.deleteById(id); } @Override public Student findById(Integer id) { return studentRepository.findById(id); } @Override public List<Classes> findAllClasses() { return classesRepository.findAll(); } }
JDBCTools.java
package com.southwind.util; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCTools { private static DataSource dataSource; static { dataSource = new ComboPooledDataSource("testc3p0"); } public static Connection getConnection(){ Connection connection = null; try { connection = dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return connection; } public static void release(Connection connection, Statement statement, ResultSet resultSet){ try { if(connection!=null){ connection.close(); } if(statement!=null){ statement.close(); } if(resultSet!=null){ resultSet.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
