8.7作业(work87文件) 简单的JDBC+servlet+jsp 查询数据

最新推荐文章于 2022-08-28 19:58:32 发布

一身正气z

最新推荐文章于 2022-08-28 19:58:32 发布

阅读量282

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/wudingan/article/details/98853050

版权

java 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

作业要求:在数据库中创建课程表，包括课程名称，课程价格，开课状态（已开课、未开课、已结束）。创建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>

源码下载:(包含数据库.导入就可以用了)

https://download.csdn.net/download/wudingan/11502205