69期-Java SE-055_综合练习 - HiJackykun

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&amp;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();
        }
    }
}

posted on 2019-09-20 08:07 HiJackykun 阅读(418) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部