作业要求:在数据库中创建课程表,包括课程名称,课程价格,开课状态(已开课、未开课、已结束)。创建JSP页面,可以根据开课状态查询得到相应的课程列表进行显示。要求:基于MVC模式创建应用,JSP中不能出现任何脚本元素和表达式元素。
1.创建数据库
service处理业务逻辑
1.定义一个接口
2.定义一个实现层serviceImpl
servlet
web.xm不用改,用注解
查询页面
提交完后页面
项目结构:(需要用的jar包放在lib文件夹下,会自动加载使用)
完整源码:
已上传到csdn,自行下载
部分核心源码:
package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
private static final String URL="jdbc:mysql://localhost:3306/work";
private static final String USERNAME="root";
private static final String PASSWORD="root";
private static final String DRIVER="com.mysql.jdbc.Driver";
private static PreparedStatement ps = null;
private static ResultSet rs = null;
public static Connection getConn(){
Connection connection =null;
try {
Class.forName(DRIVER);
connection=DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public static ResultSet getResultSet(String sql,Object...objects){
try {
ps=getConn().prepareStatement(sql);
for(int i = 0,j=0;i<objects.length;i++){
ps.setObject(++j, objects[i]);
}
rs=ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
package com.utentity;
import com.dao.ClassDao;
public class Classes {
private int id;
private String classname;
private int price;
private int sid;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getClassname() {
return classname;
}
public void setClassname(String classname) {
this.classname = classname;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public Classes(){
super();
}
public Classes(int id, String classname, int price, int sid) {
super();
this.id = id;
this.classname = classname;
this.price = price;
this.sid = sid;
}
@Override
public String toString() {
return "Classes [id=" + id + ", classname=" + classname + ", price=" + price + ", sid=" + sid + "]";
}
}
package com.dao;
import java.util.List;
import com.utentity.Classes;
public interface ClassDao {
//public Classes getClass(int sid);
List<Classes> getClass(String sid);
}
package com.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.dao.ClassDao;
import com.utentity.Classes;
import com.util.DBUtil;
public class ClassDaoImpl implements ClassDao{
public List<Classes> getClass(String sid){
//String sql = "SELECT class.id,class.classname,class.price FROM class JOIN state ON state.sid=class.sid WHERE class.sid="+sid;
String sql = "SELECT class.id,class.classname,class.price FROM class JOIN state ON state.sid=class.sid WHERE class.sid=?";
ResultSet rs = DBUtil.getResultSet(sql,sid);
List<Classes> classes = new ArrayList<Classes>();
Classes user = null;
try{
//这里用if就指挥打印出一个结果,如果用while就会一条条打印出结果
//这里循环完
while(rs.next()){
user = new Classes();
user.setId(rs.getInt("id"));
user.setClassname(rs.getString("classname"));
user.setPrice(rs.getInt("price"));
//user.setSid(rs.getInt("sid"));
//System.out.println(user.toString());
classes.add(user);
System.out.println("课程编号: "+user.getId()+"\n课程名字: "+user.getClassname()+"\n课程价格: "+user.getPrice());
}
}catch(SQLException e){
e.printStackTrace();
}
return classes;
}
/* public static void main(String[] args) {
Classes user=new ClassDaoImpl().getClass(2);
if(user ==null){
System.out.println("user2为空");
}else{
System.out.println("课程编号: "+user.getId()+"\n课程名字: "+user.getClassname()+"\n课程价格: "+user.getPrice());
}
}*/
}
package com.service;
import java.util.List;
import com.utentity.Classes;
public interface ClassService {
//Classes test(int sid);
List<Classes> getClass(String sid);
}
package com.service.impl;
import java.util.List;
import com.dao.ClassDao;
import com.dao.impl.ClassDaoImpl;
import com.service.ClassService;
import com.utentity.Classes;
public class ClassServiceImpl implements ClassService{
private ClassDao dao = new ClassDaoImpl();
@Override
public List<Classes> getClass(String sid) {
return dao.getClass(sid);
}
}
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
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 com.dao.impl.ClassDaoImpl;
import com.service.ClassService;
import com.service.impl.ClassServiceImpl;
import com.utentity.Classes;
import com.util.DBUtil;
/**
* Servlet implementation class ClassServlet
*/
@WebServlet("/classServlet")
public class ClassServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private ClassService classService = new ClassServiceImpl();
/**
* @see HttpServlet#HttpServlet()
*/
public ClassServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//doPost(request, response);
String sid = request.getParameter("myselect");
List<Classes> classes = classService.getClass(sid);
request.setAttribute("classes", classes);
request.getRequestDispatcher("success.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
/*response.setContentType("text/html;charset=utf-8");
//PrintWriter writer = response.getWriter();
//得到jsp里面选项的值
String sid = request.getParameter("myselect");
//显示到控制台
System.out.println("你选择的课程状态序号是:"+sid);
//把字符串变成int类型
int sid2 = Integer.parseInt(sid);
//把选择了的值传入数据库,在ClassDaoImpl这个方法之间遍历循环输出语句
Classes user=new ClassDaoImpl().getClass(sid2);
//System.out.println();
//输出值
int a = user.getId();
String b= user.getClassname();
int c = user.getPrice();
//这里一个细节是setAttribute的值必须是字符串
request.setAttribute("key",a+"");
request.setAttribute("bb",b);
request.setAttribute("cc",c+"");
request.getRequestDispatcher("success.jsp").forward(request, response);*/
doGet(request, response);
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>显示课程状态</title>
</head>
<body>
<form action="classServlet" method="post">
请选择课程状态: <select name="myselect">
<option selected="selected" value="1">已开课</option>
<option value="2">未开课</option>
<option value="3">已结课</option>
</select><br/>
<input type="submit" value="查询">
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="s"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>成功页面</title>
</head>
<body>
<%-- <%
String a =(String)request.getAttribute("key");
String b =(String) request.getAttribute("bb");
String c =(String) request.getAttribute("cc");
%>
<p>课程编号:<%=a%></p>
<p>课程名称:<%=b%></p>
<p>课程价格:<%=c%></p>
<%
%> --%>
<table border="1">
<tr><th>课程编号:</th><th>课程名称:</th><th>课程价格:</th></tr>
<s:forEach items="${classes}" var="c">
<tr><td>${c.id }</td><td>${c.classname }</td><td>${c.price}</td></tr>
</s:forEach>
</table>
</body>
</html>
源码下载:(包含数据库.导入就可以用了)