考试2，改查

courseDao

package dao;

import pojo.course;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class courseDao {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public course getCourseByName(String cname){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/jsptest","root", "root");
            preparedStatement=connection.prepareStatement("select * from course where cname=?");
            preparedStatement.setString(1,cname);
            ResultSet resultSet = preparedStatement.executeQuery();
            course course = new course();
            while (resultSet.next()){
                int cno = resultSet.getInt(1);
                float ccredit = resultSet.getFloat(3);
                String ctname = resultSet.getString(4);
                String ctype = resultSet.getString(5);
                course.setCno(cno);course.setCname(cname);course.setCcredit(ccredit);
                course.setCtname(ctname);course.setCtype(ctype);
            }
            return course;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public course getCourseByCno(int cno){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/jsptest","root", "root");
            preparedStatement=connection.prepareStatement("select * from course where cno=?");
            preparedStatement.setInt(1,cno);
            ResultSet resultSet = preparedStatement.executeQuery();
            course course = new course();
            while (resultSet.next()){
                String cname = resultSet.getString(2);
                float ccredit = resultSet.getFloat(3);
                String ctname = resultSet.getString(4);
                String ctype = resultSet.getString(5);
                course.setCno(cno);course.setCname(cname);course.setCcredit(ccredit);
                course.setCtname(ctname);course.setCtype(ctype);
            }
            return course;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<course> getAllCourse(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/jsptest","root", "root");
            preparedStatement=connection.prepareStatement("select * from course ");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<course> courses=new ArrayList<course>();
            while (resultSet.next()){
                course course = new course();
                int cno = resultSet.getInt(1);
                String cname = resultSet.getString(2);
                float ccredit = resultSet.getFloat(3);
                String ctname = resultSet.getString(4);
                String ctype = resultSet.getString(5);
                course.setCno(cno);course.setCname(cname);course.setCcredit(ccredit);
                course.setCtname(ctname);course.setCtype(ctype);
                courses.add(course);
            }
            return courses;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public int deleteCourse(int cno){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/jsptest","root", "root");
            preparedStatement=connection.prepareStatement("delete from course where cno=?");
            preparedStatement.setInt(1,cno);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
    public int insertCourse(course course){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/jsptest","root", "root");
            preparedStatement=connection.prepareStatement("insert into course values(null ,?,?,?,?)");
            preparedStatement.setString(1,course.getCname());
            preparedStatement.setFloat(2,course.getCcredit());
            preparedStatement.setString(3,course.getCtname());
            preparedStatement.setString(4,course.getCtype());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}

Course

package pojo;

public class course {
    private int cno;
    private String cname;
    private float ccredit;
    private String ctname;
    private String ctype;

    public course(){}

    public course(int cno, String cname, float ccredit, String ctname, String ctype) {
        this.cno = cno;
        this.cname = cname;
        this.ccredit = ccredit;
        this.ctname = ctname;
        this.ctype = ctype;
    }

    public int getCno() {
        return cno;
    }

    public void setCno(int cno) {
        this.cno = cno;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public float getCcredit() {
        return ccredit;
    }

    public void setCcredit(float ccredit) {
        this.ccredit = ccredit;
    }

    public String getCtname() {
        return ctname;
    }

    public void setCtname(String ctname) {
        this.ctname = ctname;
    }

    public String getCtype() {
        return ctype;
    }

    public void setCtype(String ctype) {
        this.ctype = ctype;
    }

    @Override
    public String toString() {
        return "course{" +
                "cno=" + cno +
                ", cname='" + cname + '\'' +
                ", ccredit=" + ccredit +
                ", ctname='" + ctname + '\'' +
                ", ctype='" + ctype + '\'' +
                '}';
    }
}

courseServletImpl

package service.impl;

import dao.courseDao;
import pojo.course;
import service.courseService;

import java.util.List;

public class courseServiceImpl implements courseService {
    courseDao courseDao=new courseDao();
    @Override
    public boolean deleteCourse(int cno) {
        if(courseDao.getCourseByCno(cno)!=null){
            return courseDao.deleteCourse(cno)>0;
        }
        return false;
    }

    @Override
    public boolean insertCourse(course course) {
        return courseDao.insertCourse(course)>0;
    }

    @Override
    public List<course> getAllCourse() {
        return courseDao.getAllCourse();
    }

    @Override
    public course getCourseByName(String cname) {
        return courseDao.getCourseByName(cname);
    }
}

courseService

package service;

import pojo.course;

import java.util.List;

public interface courseService {
    boolean deleteCourse(int cno);

    boolean insertCourse(course course);

    List<course> getAllCourse();

    course getCourseByName(String cname);
}

allCourseServlet

package servlet;

import pojo.course;
import service.courseService;
import service.impl.courseServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public class allCourseServlet extends HttpServlet {
    courseService courseService=new courseServiceImpl();
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setCharacterEncoding("UTF-8");

        List<course> allCourse = courseService.getAllCourse();

        req.setAttribute("allCourse",allCourse);

        req.getRequestDispatcher("allCourse.jsp").forward(req,resp);
    }
}

Test

import org.junit.Test;
import pojo.course;
import service.courseService;
import service.impl.courseServiceImpl;

import java.util.List;

public class test {
    @Test
    public void serviceTest(){
        courseService service=new courseServiceImpl();
        List<course> allCourse = service.getAllCourse();
        for (course course : allCourse) {
            System.out.println(course);
        }

        course course01=new course(0,"123",3,"123","123");

        System.out.println(service.insertCourse(course01));
        List<course> allCourse01 = service.getAllCourse();
        for (course course : allCourse01) {
            System.out.println(course);
        }

        System.out.println(service.deleteCourse(3));
        for (course course : service.getAllCourse()) {
            System.out.println(course);
        }

        System.out.println(service.getCourseByName("操作系统"));
    }
}

Pom

<dependencies>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
  </dependency>
  <dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>servlet-api</artifactId>
    <version>2.5</version>
  </dependency>
  <dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>javax.servlet.jsp-api</artifactId>
    <version>2.3.3</version>
  </dependency>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
  </dependency>
</dependencies>