终于,经过一番艰苦的错误之旅,可算是把这个DAO的简单开发模式弄好了。直接上代码。
注意,记得要去数据库创建一个student的表。
链接数据库的类
import java.sql.*;
public class DbConnect {
private static String userName = "sa"; // 默认用户名
private static String userPwd = "123456"; // 密码
private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//加载JDBC驱动
private static String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=zero";
public static Connection getConnect() throws SQLException,ClassNotFoundException{
Connection dbConn = null;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
return dbConn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void closeDB(Connection con,PreparedStatement pstmt,ResultSet rs) {
try {
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
Student类(bean类)
public class Student {
private int id;
private String name;
private String sex;
private int age;
private float weight;
private float hight;
public Student() {
// TODO Auto-generated constructor stub
}
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public float getWeight() {
return weight;
}
public void setWeight(float weight) {
this.weight = weight;
}
public float getHight() {
return hight;
}
public void setHight(float hight) {
this.hight = hight;
}
}
Student类的接口
import java.util.*;
public interface IStudentDAO {
public abstract Student create(Student stu) throws Exception;//添加记录的方法
public abstract void remove(Student stu) throws Exception;//删除记录的方法
public abstract Student find(Student stu) throws Exception;//查询记录的方法
public abstract List<Student>findAll() throws Exception;//列出全部记录的方法
public abstract void update(Student stu) throws Exception;//修改记录的方法
}
实现Student类
import java.sql.*;
import java.util.*;
public class StudentDAO implements IStudentDAO{
protected static final String FIELDS_INSERT="id,name,sex,age,weight,hight";
protected static String INSERT_SQL="insert into stu_info("+FIELDS_INSERT+")"+"values(?,?,?,?,?,?)";
protected static String SELECT_SQL="select "+FIELDS_INSERT+" from stu_info where id=?";
protected static String UPDATE_SQL="update stu_info set "+"id=?,name=?,sex=?,age=?,weight=?,hight=? where id=?";
protected static String DELETE_SQL="delete from stu_info where id=?";
public Student create(Student stu) throws Exception{//添加记录的方法
Connection con=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try {
con=DbConnect.getConnect();
pstm=con.prepareStatement(INSERT_SQL);
pstm.setInt(1,stu.getId());
pstm.setString(2,stu.getName());
pstm.setString(3,stu.getSex());
pstm.setInt(4,stu.getAge());
pstm.setFloat(5,stu.getWeight());
pstm.setFloat(6,stu.getHight());
pstm.executeUpdate();
}catch (Exception e) {
}finally {
DbConnect.closeDB(con, pstm, rs);
}
return stu;
}
public void remove(Student stu) throws Exception{//删除记录的方法
Connection con=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try {
con=DbConnect.getConnect();
pstm=con.prepareStatement(DELETE_SQL);
pstm.setInt(1,stu.getId());
pstm.executeUpdate();
}catch (Exception e) {
}finally {
DbConnect.closeDB(con, pstm, rs);
}
}
public Student find(Student stu) throws Exception{//查询记录的方法
Connection con=null;
PreparedStatement pstm=null;
ResultSet rs=null;
Student stu2=null;
try {
con=DbConnect.getConnect();
pstm=con.prepareStatement(SELECT_SQL);
pstm.setInt(1,stu.getId());
rs=pstm.executeQuery();
if(rs.next()) {
stu2=new Student();
stu2.setId(rs.getInt(1));
stu2.setName(rs.getString(2));
stu2.setSex(rs.getString(3));
stu2.setAge(rs.getInt(4));
stu2.setWeight(rs.getFloat(5));
stu2.setHight(rs.getFloat(6));
}
}catch (Exception e) {
}finally {
DbConnect.closeDB(con, pstm, rs);
}
return stu2;
}
public List<Student>findAll() throws Exception{//列出全部记录的方法
Connection con=null;
PreparedStatement pstm=null;
ResultSet rs=null;
List<Student>student=new ArrayList<Student>();
con=DbConnect.getConnect();
pstm=con.prepareStatement("select * from stu_info");
rs=pstm.executeQuery();
while(rs.next()) {
Student stu2=new Student();
stu2=new Student();
stu2.setId(rs.getInt(1));
stu2.setName(rs.getString(2));
stu2.setSex(rs.getString(3));
stu2.setAge(rs.getInt(4));
stu2.setWeight(rs.getFloat(5));
stu2.setHight(rs.getFloat(6));
student.add(stu2);
}
DbConnect.closeDB(con, pstm, rs);
return student;
}
public void update(Student stu) throws Exception{//修改记录的方法
Connection con=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try {
con=DbConnect.getConnect();
pstm=con.prepareStatement(UPDATE_SQL);
pstm.setInt(1,stu.getId());
pstm.setString(2,stu.getName());
pstm.setString(3,stu.getSex());
pstm.setInt(4,stu.getAge());
pstm.setFloat(5,stu.getWeight());
pstm.setFloat(6,stu.getHight());
pstm.setInt(7, stu.getId());
int rowCount=pstm.executeUpdate();
if(rowCount==0) {
throw new Exception("Update Error:Student Id:"+stu.getId());
}
}catch (Exception e) {
}finally {
DbConnect.closeDB(con, pstm, rs);
}
}
}
下面是对应方法的servlet
import java.io.IOException;
import java.io.PrintWriter;
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 beans.StudentDAO;
import beans.Student;
/**
* Servlet implementation class StuDAO
*/
@WebServlet("/seven/StuDAO")
public class StuDAO extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StuDAO() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
request.setCharacterEncoding("UTF-8");
/*
int id=Integer.parseInt(request.getParameter("id"));
String name=request.getParameter("name");
String sex=request.getParameter("sex");
int age=Integer.parseInt(request.getParameter("age"));
float weight=Float.parseFloat(request.getParameter("weight"));
float hight=Float.parseFloat(request.getParameter("hight"));
String sql1="Insert into stu_info(id,name,sex,age,weight,hight)";
String sql2="values("+id+",'"+name+"','"+sex+"',"+age+","+weight+","+hight+")";
//这里个name和sex是字符串,要先用'单引号再用"双引号
String sql=sql1+sql2;
*/
response.setContentType("text/html;charset=GBK");
response.setContentType("text/html");
PrintWriter out=response.getWriter();
Student stu=new Student();
stu.setId(Integer.parseInt(request.getParameter("id")));
stu.setName(request.getParameter("name"));
stu.setSex(request.getParameter("sex"));
stu.setAge(Integer.parseInt(request.getParameter("age")));
stu.setWeight(Float.parseFloat(request.getParameter("weight")));
stu.setHight(Float.parseFloat(request.getParameter("hight")));
StudentDAO sd=new StudentDAO();
try {
sd.create(stu);
out.println("创建成功!");
}
catch(Exception e) {
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
import java.io.IOException;
import java.io.PrintWriter;
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 beans.StudentDAO;
import beans.Student;
/**
* Servlet implementation class StuUpdata
*/
@WebServlet("/seven/StuUpdata")
public class StuUpdata extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StuUpdata() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=GBK");
response.setContentType("text/html");
PrintWriter out=response.getWriter();
Student stu = new Student();
stu.setId(Integer.parseInt(request.getParameter("id")));
stu.setName(request.getParameter("name"));
stu.setSex(request.getParameter("sex"));
stu.setAge(Integer.parseInt(request.getParameter("age")));
stu.setWeight(Float.parseFloat(request.getParameter("weight")));
stu.setHight(Float.parseFloat(request.getParameter("hight")));
StudentDAO update = new StudentDAO();
try
{
update.update(stu);
out.print("更新成功");
}
catch (Exception e)
{
e.printStackTrace();
out.print("更新失败");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
import java.io.IOException;
import java.io.PrintWriter;
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 beans.StudentDAO;
import beans.Student;
/**
* Servlet implementation class StuRemove
*/
@WebServlet("/seven/StuRemove")
public class StuRemove extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StuRemove() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
//request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=GBK");
response.setContentType("text/html");
PrintWriter out=response.getWriter();
Student stu = new Student();
stu.setId(Integer.parseInt(request.getParameter("id")));
StudentDAO remove = new StudentDAO();
try
{
remove.remove(stu);
out.println("删除成功");
}
catch (Exception e)
{
out.println("删除失败");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
import java.io.IOException;
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 beans.StudentDAO;//加载我的【beans】包中的bean类
import beans.Student;
import java.util.*;
/**
* Servlet implementation class StuFindAll
*/
@WebServlet("/seven/StuFindAll")
public class StuFindAll extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StuFindAll() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
request.setCharacterEncoding("UTF-8");
StudentDAO findAll = new StudentDAO();
List<Student> student = new ArrayList();
try
{
student = findAll.findAll();
request.setAttribute("list", student);
}
catch (Exception e)
{
e.printStackTrace();
}
request.getRequestDispatcher("find_stu_all.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
import java.io.IOException;
import java.io.PrintWriter;
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 beans.StudentDAO;
import beans.Student;
import java.util.*;
/**
* Servlet implementation class StuFind
*/
@WebServlet("/seven/StuFind")
public class StuFind extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StuFind() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=GBK");
response.setContentType("text/html");
PrintWriter out=response.getWriter();
Student stu = new Student();
stu.setId(Integer.parseInt(request.getParameter("id")));
StudentDAO stu2 = new StudentDAO();
try
{
stu = stu2.find(stu);
request.setAttribute("student",stu);
request.getRequestDispatcher("find_stu_show.jsp").forward(request, response);
out.println("查找成功");
}
catch (Exception e)
{
out.println("查找失败");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
下面是各个JSP的代码
<%@ page language="java" contentType="text/html; charset=UTF-8" import="beans.Student" import="java.sql.*"
import="java.util.*"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查询结果</title>
</head>
<body>
<table align="center" width="450" border="1" height="180"
bordercolor="white" bgcolor="black" cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="5">
<h2>查询学生信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1">
<td><b>学号</b></td>
<td><b>姓名</b></td>
<td><b>性别</b></td>
<td><b>年龄</b></td>
<td><b>体重</b></td>
<td><b>身高</b></td>
</tr>
<%Student student=(Student)request.getAttribute("student");%>
<tr align="center" bgcolor="white">
<td><%=student.getId()%></td>
<td><%=student.getName()%></td>
<td><%=student.getSex()%></td>
<td><%=student.getAge()%></td>
<td><%=student.getWeight()%></td>
<td><%=student.getHight()%></td>
</tr>
</table>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改记录的条件提交页面</title>
</head>
<body>
请选择修改记录所满足的条件<hr width="100%" size="3"><!-- width设置线的长度 -->
<form action="StuUpdata" method="post"><br><!-- 必需的 action 属性规定当提交表单时,向何处发送表单数据。 -->
<table border="0" width="238" height="252">
<tr><td>学号</td><td><input type="text" name="id"></td></tr>
<tr><td>姓名</td><td><input type="text" name="name"></td></tr>
<tr><td>性别</td><td><input type="text" name="sex"></td></tr>
<tr><td>年龄</td><td><input type="text" name="age"></td></tr>
<tr><td>体重</td><td><input type="text" name="weight"></td></tr>
<tr><td>身高</td><td><input type="text" name="hight"></td></tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="更 新">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加信息提交页面</title>
</head>
<body>
<form action="StuDAO" method="post">
<table border="0" width="238" height=252"">
<tr><td>学号</td><td><input type="text" name="id"></td></tr>
<tr><td>姓名</td><td><input type="text" name="name"></td></tr>
<tr><td>性别</td><td><input type="text" name="sex"></td></tr>
<tr><td>年龄</td><td><input type="text" name="age"></td></tr>
<tr><td>体重</td><td><input type="text" name="weight"></td></tr>
<tr><td>身高</td><td><input type="text" name="hight"></td></tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="提 交"> <!-- 表示空格,因为表格中如果是手打的空格会合并为一个 -->
<input type="reset" value="取 消">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查询条件提交页面</title>
</head>
<body>
请输入查询的学号<hr width="100%" size="3">
<form action="StuFind" method="post">
学号:<input type="text" name="id"><br><br>
<!--
性别:男<input type="radio" value="男" name="sex" checked="checked">
女<input type="radio" value="女" name="sex"><br><br>
体重范围:<p>
最小<input type="text" value="0" name="w1"><br><br>
最大<input type="text" value="150" name="w2"><p>
身高范围:<p>
最小<input type="text" value="0" name="h1"><br><br>
最大<input type="text" value="190" name="h2"><p> -->
<input type="submit" value="提交">
<input type="reset" value="取消">
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*" import="beans.Student"
import="java.util.*"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>显示所有学生的页面</title>
</head>
<body>
<center>
<table align="center" width="450" border="1" height="180" bordercolor="white" bgcolor="black" cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="5">
<h2>所有学生信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1" >
<td><b>学号</b></td>
<td><b>姓名</b></td>
<td><b>性别</b></td>
<td><b>年龄</b></td>
<td><b>体重</b></td>
<td><b>身高</b></td>
</tr>
<%
// 获取学生信息集合
List<Student> list = (List<Student>)request.getAttribute("list");
// 判断集合是否有效
if(list == null || list.size() < 1){
out.print("没有数据!");
}else{
// 遍历学生集合中的数据
for(Student student : list){
%>
<tr align="center" bgcolor="white">
<td><%=student.getId()%></td>
<td><%=student.getName()%></td>
<td><%=student.getSex()%></td>
<td><%=student.getAge()%></td>
<td><%=student.getWeight()%></td>
<td><%=student.getHight()%></td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除条件提交页面</title>
</head>
<body>
请选择删除的学号<hr width="100%" size="3">
<form action="StuRemove" method="post">
学号:<input type="text" name="id"><br><br>
<!--
姓名:<input type="text" name="name"><br><br>
性别:男<input type="radio" value="男" name="sex">
女<input type="radio" value="女" name="sex"><br><br>
体重范围:<p>
最小:<input type="text" name="w1"><br><br>
最大:<input type="text" name="w2"><p> -->
<input type="submit" value="提交">
<!-- 这是4个空格,最后网页会变成:提交 取消 -->
<input type="reset" value="取消">
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>菜单页面</title>
</head>
<body>
<br><br><br><br><br><br>
<p><a href="StuFindAll" target="right">列出全部学生</a></p><!-- <a> 标签的 target 属性规定在何处打开链接文档。 -->
<p><a href="find_stu_tijiao.jsp" target="right">按条件查询学生</a></p>
<p><a href="insert_stu_tijiao.jsp" target="right">新添加学生</a></p>
<p><a href="delete_stu_tijiao.jsp" target="right">按条件删除学生</a></p>
<p><a href="update_stu_tijiao.jsp" target="right">按条件修改学生</a></p>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>页面标题</title>
</head>
<body>
<center><h1>学生身体体质信息管理系统</h1></center>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>信息显示页面</title>
</head>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生身体体质信息管理系统</title>
</head>
<frameset rows="80,*">
<!-- frameset 元素可定义一个框架集。它被用来组织多个窗口(框架)。
每个框架存有独立的文档。在其最简单的应用中,frameset 元素仅仅会规定
在框架集中存在多少列或多少行。您必须使用 cols 或 rows 属性。 -->
<frame src="dao_stu_title.jsp" scrolling="no">
<frameset cols="140,*">
<frame src="dao_stu_left.jsp" scrolling="no">
<frame src="dao_stu_right.jsp" name="right" scrolling="no">
</frameset>
</frameset>
<!-- frameset框架标签不能放在body里面 -->
</html>
截图