目录
一、MVC是什么
MVC设计模式
M:模型(数据),模型接受视图请求的数据,并返回最终的处理结果
V:视图(网页,显示模型中的数据),呈现数据给用户
C:控制器,用来把不同的数据显示在不同的视图上;控制器接受用户的输入并调用模型和视图去完成用户的需求,它本身不输出任何东西和做任何处理。它只接收请求并决定调用哪个模型构件去处理请求,然后再确定用哪个视图来显示返回数据。
MVC模式工作过程
1.用户发送请求,请求由控制器处理
2.控制器根据用户请求选择相应的模型和视图,并将请求传递给模型
3.模型根据请求处理数据,并将处理结果返回给控制器
4.控制器根据模型返回的数据更新视图,并将更新后的视图返回给用户
二、结合servlet和jsp实现查询功能
第一种方式:
教程:MVC系列教材 (二)- 结合Servlet和JSP 实现查询功能 (how2j.cn)
由于我的eclipse是与SQL server相连接的,在复制教程中的代码并修改相关数据库连接语句后,运行出来不能显示数据表的数据,在这过程中找了很多原因,但还是显示不出来;
最后询问ChatGPT知道了相关知识点:SQL server不支持LIMIT子句,将String sql =
"select * from hero order by id desc limit ?,? ";修改为String sql = "select * from huiyuan ORDER BY id DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";即可正确显示出来数据。
其中OFFSET子句表示从第几行开始获取数据,FETCH子句表示获取多少行数据;且在
SQL Server中,OFFSET和FETCH子句中的参数都是以行数为单位,而不是以页数为单位,要根据自己的需求来计算和设置这些参数。
Hero.java
package bean;
public class Hero {
public int id;
public String name;
public float age;
public String xingbie;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getAge() {
return age;
}
public void setAge(float age) {
this.age = age;
}
public String getXingbie() {
return xingbie;
}
public void setXingbie(String xingbie) {
this.xingbie = xingbie;
}
}
HeroDao.java
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.Hero;
public class HeroDao {
public HeroDao() {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=javaweb", "sa",
"123456");
}
public int getTotal() {
int total = 0;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from huiyuan";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Hero hero) {
String sql = "insert into huiyuan values(?,?,?)";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, hero.name);
ps.setFloat(2, hero.age);
ps.setString(3, hero.xingbie);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
hero.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(Hero hero) {
String sql = "update huiyuan set name= ?, age = ? , xingbie = ? where id = ?";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, hero.name);
ps.setFloat(2, hero.age);
ps.setString(3, hero.xingbie);
// ps.setInt(4, hero.id);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "delete from huiyuan where id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public Hero get(int id) {
Hero hero = null;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select * from huiyuan where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
hero = new Hero();
String name = rs.getString(2);
float age = rs.getInt("age");
String xingbie = rs.getString(4);
hero.name = name;
hero.age = age;
hero.xingbie = xingbie;
hero.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
return hero;
}
public List<Hero> list() {
return list(0, Short.MAX_VALUE);
}
public List<Hero> list(int start, int count) {
List<Hero> heros = new ArrayList<Hero>();
String sql = "select * from huiyuan ORDER BY id DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Hero hero = new Hero();
int id = rs.getInt(1);
String name = rs.getString(2);
float age = rs.getInt(3);
String xingbie = rs.getString(4);
hero.id = id;
hero.name = name;
hero.age = age;
hero.xingbie = xingbie;
heros.add(hero);
}
} catch (SQLException e) {
e.printStackTrace();
}
return heros;
}
}
HeroListServlet.java(新建servlet)
package servlet;
import java.io.IOException;
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 bean.Hero;
import dao.HeroDao;
/**
* Servlet implementation class HeroListServlet
*/
@WebServlet("/HeroListServlet")
public class HeroListServlet extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
List<Hero> heros = new HeroDao().list();
request.setAttribute("heros", heros);
request.getRequestDispatcher("listHero.jsp").forward(request, response);
}
}
listHero.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.util.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<table align='center' border='1' cellspacing='0'>
<tr>
<td>id</td>
<td>姓名</td>
<td>年龄</td>
<td>性别</td>
<td>edit</td>
<td>delete</td>
</tr>
<c:forEach items="${heros}" var="hero" varStatus="st">
<tr>
<td>${hero.id}</td>
<td>${hero.name}</td>
<td>${hero.age}</td>
<td>${hero.xingbie}</td>
<td><a href="HeroEditServlet?id=${hero.id}">edit</a></td>
<td><a href="HeroDeleteServlet?id=${hero.id}">delete</a></td>
</tr>
</c:forEach>
</table>
最终显示结果:
第二种方式:
教程:(1条消息) jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上_servlet读取数据库打印_Mr.Aholic的博客-CSDN博客
新建类(Hero.java)
package j2ee;
public class Hero {
public int id;
public String name;
public int age;
public String xingbie;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getXingbie() {
return xingbie;
}
public void setXingbie(String xingbie) {
this.xingbie = xingbie;
}
}
新建servlet(HeroListServlet.java)
package j2ee;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
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;
/**
* Servlet实现类FindServlet
*/
@WebServlet("/HeroListServlet") //配置Servlet
public class HeroListServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public HeroListServlet() {
super();
}
/**
* 执行POST请求的方法
*/
protected void doPostt(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
/**
* 执行GET请求的方法
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 加载数据库驱动,注册到驱动管理器
String url = "jdbc:sqlserver://localhost:1433;databaseName=javaweb";// 数据库连接字符串
String username = "sa"; // 数据库用户名
String password = "123456"; // 数据库密码
// 创建Connection连接
Connection conn = DriverManager.getConnection(url,username,password);
Statement stmt = conn.createStatement(); // 获取Statement
String sql = "select * from huiyuan"; // 添加会员信息的SQL语句
ResultSet rs = stmt.executeQuery(sql); // 执行查询
List<Hero> list = new ArrayList<>(); // 实例化List对象
while(rs.next()){ // 光标向后移动,并判断是否有效
Hero hero = new Hero(); // 实例化Hero对象
hero.setId(rs.getInt("id")); // 对id属性赋值
hero.setName(rs.getString("name")); // 对name属性赋值
hero.setAge(rs.getInt("age")); // 对age属性赋值
hero.setXingbie(rs.getString("xingbie")); // 对xingbie属性赋值
list.add(hero); // 将会员对象添加到集合中
}
request.setAttribute("list", list); // 将会员集合放置到request中
rs.close(); // 关闭ResultSet
stmt.close(); // 关闭Statement
conn.close(); // 关闭Connection
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
// 请求转发到listHero.jsp
request.getRequestDispatcher("listHero.jsp").forward(request, response);
}
}
listHero.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<%@ page import="j2ee.Hero"%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>显示会员信息</title>
<style type="text/css">
td,th {
padding: 5px;
}
</style>
</head>
<body>
<div width="98%" align="center">
<h2>所有会员信息</h2>
</div>
<table width="98%" border="0" align="center" cellpadding="0"
cellspacing="1" bgcolor="#666666">
<tr>
<th bgcolor="#FFFFFF">编号</th>
<th bgcolor="#FFFFFF">姓名</th>
<th bgcolor="#FFFFFF">年龄</th>
<th bgcolor="#FFFFFF">性别</th>
</tr>
<%
// 获取会员信息集合
List<Hero> list = (List<Hero>) request.getAttribute("list");
// 判断集合是否有效
if (list == null || list.size() < 1) {
out.print("<tr><td bgcolor='#FFFFFF' colspan='5'>没有任何会员信息!</td></tr>");
} else {
// 遍历会员集合中的数据
for (Hero hero : list) {
%>
<tr align="center">
<td bgcolor="#FFFFFF" ><%=hero.getId()%></td>
<td bgcolor="#FFFFFF"><%=hero.getName()%></td>
<td bgcolor="#FFFFFF"><%=hero.getAge()%></td>
<td bgcolor="#FFFFFF"><%=hero.getXingbie()%></td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
在这中间要注意:
Hello.java与HeroListServlet.java要在同一个包中,或者在HeroListServlet.java中引入hero类(即写入import j2ee.Hero;)(第一次便是没有引入也没放在同一包中爆了错。)
这样便可成功利用Servlet实现对数据表数据的查询,并将数据在jsp页面显示出来。