1.功能要求
实现学生student表和专业major表的关联，要求在之前的学生信息系统中加入来自另一张表的专业信息，修改对应的所有文件
2.student表与major表关联
2.1 二者关系
一个学生只能有一个专业，一个专业可以有多个学生，所以学生与专业是多对一的关系。因此在学生表中加入major_id关键字，表示学生专业编号
3 student.class文件
虽然major_id是外键，但是在实际代码书写过程中，我们并不把他当作外键，就仅仅视为普通的一个属性，在脑子中记住它为外键。
在添加属性时，因为我们在浏览器展示的实际上是major中的name属性，所以在设置Student类时，我们不添加major_id这个属性，而是将Major类直接加入Student，需要用哪个就取哪个。
具体代码如下：
package student.model;
import major.model.Major;
public class Student {
private int id = -1;
private String name;
private String sex;
private int age = -1;
private Major major;
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 Major getMajor() {
return major;
}
public void setMajor(Major major) {
this.major = major;
}
}
4. jsp文件
修改浏览器界面中的major专业部分即可
4.1 stu_list.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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<body>
<form action="${ request.contextPath }/StuCour/StuListServlet" method="post">
学号:<input type="text" name="id" <c:if test="${ requestScope.stu.id ne -1}"> value="${ requestScope.stu.id }"</c:if> /><br />
姓名:<input type="text" name="name" value="${ requestScope.stu.name }"><br />
性别:<select name=sex>
<option value="" selected></option>
<option value="男" <c:if test="${ requestScope.stu.sex eq '男'}">selected</c:if> >男</option>
<option value="女" <c:if test="${ requestScope.stu.sex eq '女'}">selected </c:if> >女</option>
</select>
年龄:<input type="text" name="age" <c:if test="${ requestScope.stu.age ne -1}"> value="${ requestScope.stu.age }"</c:if>"><br />
专业:<select name="majorId">
<option value=""> </option>
<c:forEach items="${ requestScope.majors }" var="major">
<option value="${ major.id }" <c:if test="${ major.id eq requestScope.stu.major.id}">selected</c:if> >${ major.name }</option>
</c:forEach>
</select><br />
<input type="submit" value="搜索"/><input type="reset" />
</form>
<hr />
<table border='1'>
<tr>
<th>学号</th><th>姓名</th><th>性别</th><th>年龄</th><th>专业</th><th>operation</th>
</tr>
<c:forEach items="${requestScope.stus}" var="stu">
<tr>
<td>${ stu.id }</td>
<td>${ stu.name }</td>
<td>${ stu.sex }</td>
<td>${ stu.age }</td>
<td>${ stu.major.name }</td>
<td>
<a href="${request.contextPath}/StuCour/StuUpdServlet?id=${stu.id}">update</a>
<a href="${request.contextPath}/StuCour/StuDelServlet?id=${stu.id}">delete</a>
</td>
</tr>
</c:forEach>
</table>
<a href="${request.contextPath}/StuCour/StuAddServlet">insert student</a>
</body>
</html>4.2 stu_add.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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<body>
<form action="${ request.contextPath }/StuCour/StuListServlet" method="post">
学号:<input type="text" name="id" <c:if test="${ requestScope.stu.id ne -1}"> value="${ requestScope.stu.id }"</c:if> /><br />
姓名:<input type="text" name="name" value="${ requestScope.stu.name }"><br />
性别:<select name=sex>
<option value="" selected></option>
<option value="男" <c:if test="${ requestScope.stu.sex eq '男'}">selected</c:if> >男</option>
<option value="女" <c:if test="${ requestScope.stu.sex eq '女'}">selected </c:if> >女</option>
</select>
年龄:<input type="text" name="age" <c:if test="${ requestScope.stu.age ne -1}"> value="${ requestScope.stu.age }"</c:if>"><br />
专业:<select name="majorId">
<option value=""> </option>
<c:forEach items="${ requestScope.majors }" var="major">
<option value="${ major.id }" <c:if test="${ major.id eq requestScope.stu.major.id}">selected</c:if> >${ major.name }</option>
</c:forEach>
</select><br />
<input type="submit" value="搜索"/><input type="reset" />
</form>
<hr />
<table border='1'>
<tr>
<th>学号</th><th>姓名</th><th>性别</th><th>年龄</th><th>专业</th><th>operation</th>
</tr>
<c:forEach items="${requestScope.stus}" var="stu">
<tr>
<td>${ stu.id }</td>
<td>${ stu.name }</td>
<td>${ stu.sex }</td>
<td>${ stu.age }</td>
<td>${ stu.major.name }</td>
<td>
<a href="${request.contextPath}/StuCour/StuUpdServlet?id=${stu.id}">update</a>
<a href="${request.contextPath}/StuCour/StuDelServlet?id=${stu.id}">delete</a>
</td>
</tr>
</c:forEach>
</table>
<a href="${request.contextPath}/StuCour/StuAddServlet">insert student</a>
</body>
</html>4.3 stu_upd.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>StuUpd</title>
</head>
<body>
<form action="${request.contextPath}/StuCour/StuUpdServlet" method="post">
<input type="hidden" name="id" value=${ requestScope.stu.id }><br />
姓名: <input type="text" name="name" value=${ requestScope.stu.name }><br />
性别:<br />
<input type="radio" name="sex" value="男" <c:if test="${ requestScope.stu.sex eq '男' }">checked</c:if> />男<br />
<input type="radio" name="sex" value="女" <c:if test="${ requestScope.stu.sex eq '女' }">checked</c:if> />女<br />
年龄: <input type="text" name="age" value=${ requestScope.stu.age }><br />
专业:<select name="majorId">
<c:forEach items="${ requestScope.majors }" var="major">
<option value="${ major.id }" <c:if test="${ major.id eq requestScope.stu.major.id }">selected</c:if> >${ major.name }</option>
</c:forEach>
</select><br />
<input type="submit">
<input type="reset">
</form>
</body>
</html>5 Servlet文件
因为删除模块和之前无差别，所以不需要修改删除模块
5.1StuListServlet
package student.controller;
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 major.model.Major;
import major.service.MajorService;
import major.service.imp.MajorServiceImp;
import student.service.imp.StuServiceImp;
import student.model.Student;
import student.service.*;
/**
* Servlet implementation class StuListServlet
*/
@WebServlet("/StuListServlet")
public class StuListServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
StuService stuService = new StuServiceImp();
MajorService majorService = new MajorServiceImp();
List <Student> stus = stuService.listAll();
List <Major> majors = majorService.listAll();
request.setAttribute("stus", stus);
request.setAttribute("majors", majors);
request.getRequestDispatcher("student/stu_list.jsp").forward(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String id = request.getParameter("id").trim();
String name = request.getParameter("name").trim();
String sex = request.getParameter("sex").trim();
String age = request.getParameter("age").trim();
String majorId = request.getParameter("majorId").trim();
Student stu = new Student();
Major major = new Major();
if(id != null && !id.equals(""))stu.setId( Integer.parseInt(id) );
if(name != null && !name.equals(""))stu.setName( name );
if(sex != null && !sex.equals(""))stu.setSex( sex );
if(age != null && !age.equals(""))stu.setAge( Integer.parseInt(age) );
if(majorId != null && !majorId.equals("")) {
major.setId(Integer.parseInt(majorId));
stu.setMajor(major);
}
StuService stuService = new StuServiceImp();
MajorService majorService = new MajorServiceImp();
List <Student> stus = stuService.list(stu);
List <Major> majors = majorService.listAll();
request.setAttribute("majors", majors);
request.setAttribute("stu", stu);
request.setAttribute("stus", stus);
request.getRequestDispatcher("student/stu_list.jsp").forward(request, response);
}
}
5.2 StuAddtServlet
package student.controller;
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 major.model.Major;
import major.service.MajorService;
import major.service.imp.MajorServiceImp;
import student.model.Student;
import student.service.StuService;
import student.service.imp.StuServiceImp;
/**
* Servlet implementation class StuAddServlet
*/
@WebServlet("/StuAddServlet")
public class StuAddServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
MajorService majorService = new MajorServiceImp();
List <Major> majors = majorService.listAll();
request.setAttribute("majors", majors);
request.getRequestDispatcher("student/stu_add.jsp").forward(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String id = request.getParameter("id");
String name = request.getParameter("name");
String sex = request.getParameter("sex");
String age = request.getParameter("age");
String majorId = request.getParameter("majorId");
Student stu = new Student();
stu.setId( Integer.parseInt(id) );
stu.setName(name);
stu.setSex(sex);
stu.setAge( Integer.parseInt(age) );
Major major = new Major();
major.setId( Integer.parseInt(majorId) );
stu.setMajor(major);
StuService stuService = new StuServiceImp();
stuService.save(stu);
response.sendRedirect("StuListServlet");
}
}
5.3 StuUpdServlet
package student.controller;
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 major.model.Major;
import major.service.MajorService;
import major.service.imp.MajorServiceImp;
import student.model.Student;
import student.service.StuService;
import student.service.imp.StuServiceImp;
/**
* Servlet implementation class StuUpdServlet
*/
@WebServlet("/StuUpdServlet")
public class StuUpdServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String id = request.getParameter("id");
StuService stuService = new StuServiceImp();
Student stu = stuService.listById( Integer.parseInt(id) );
MajorService majorService = new MajorServiceImp();
List <Major> majors = majorService.listAll();
request.setAttribute("majors", majors);
request.setAttribute("stu", stu);
request.getRequestDispatcher("student/stu_upd.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
String id = request.getParameter("id");
String name = request.getParameter("name");
String sex = request.getParameter("sex");
String age = request.getParameter("age");
String majorId = request.getParameter("majorId");
Student stu = new Student();
stu.setId( Integer.parseInt(id) );
stu.setName(name);
stu.setSex(sex);
stu.setAge( Integer.parseInt(age) );
Major major = new Major();
major.setId(Integer.parseInt(majorId));
stu.setMajor(major);
StuService stuService = new StuServiceImp();
stuService.update(stu);
response.sendRedirect("StuListServlet");
}
}
6 DaoImp文件
在修改Dao层文件时，注意有两种方法修改Dao与SQL的连接，一种是直接在SQL语句中进行修改，多表连接查询，一种是多次查询，在Java代码查询之中再插入查询。
第一种效率更高，但是复杂难懂，而且有可能出现bug，例如有值为空，一般对数据库掌握较好的，以及小规模团队使用
第二种简单易懂，更加规范，但是代码内容更长，一般大厂要求只能用这种
以listAll()来举例说明
6.1第一种方法
MajorDao majorDao = new MajorDaoImp();
@Override
public List<Student> listAll() {
// TODO Auto-generated method stub
//
Connection con = null;
ArrayList <Student> stus = new ArrayList <Student> ();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select s.id as id, s.name as name, s.sex as sex, s.age as age,m.id asmajor_id,m.name as major_name from student a LEFT JOIN major m on s.major_id = m.id");
while( rs.next() ) {
Student stu = new Student();
stu.setId( rs.getInt("id") );
stu.setName( rs.getString("name") );
stu.setSex( rs.getString("sex") );
stu.setAge( rs.getInt("age") );
Major major = new Major();
major.setId(rs.getInt("major_id"));
major.setName(rs.getString("major_name"));
stu.setMajor(major);
stus.add(stu);
}
}catch(Exception e) {
e.printStackTrace();
}
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
return stus;
}6.2第二种方法
MajorDao majorDao = new MajorDaoImp();
@Override
public List<Student> listAll() {
// TODO Auto-generated method stub
Connection con = null;
ArrayList <Student> stus = new ArrayList <Student> ();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from student");
while( rs.next() ) {
Student stu = new Student();
stu.setId( rs.getInt("id") );
stu.setName( rs.getString("name") );
stu.setSex( rs.getString("sex") );
stu.setAge( rs.getInt("age") );
Major major = majorDao.listById( rs.getInt("major_id") );
stu.setMajor(major);
stus.add(stu);
}
}catch(Exception e) {
e.printStackTrace();
}
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
return stus;
}6.3StuDaoImp
package student.dao.imp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import config.DataSource;
import major.dao.MajorDao;
import major.dao.imp.MajorDaoImp;
import major.model.Major;
import student.dao.StuDao;
import student.model.Student;
public class StuDaoImp implements StuDao{
MajorDao majorDao = new MajorDaoImp();
@Override
public List<Student> listAll() {
// TODO Auto-generated method stub
// ********************�����ݿ��ȡ����(Control)*****************************
Connection con = null;
ArrayList <Student> stus = new ArrayList <Student> ();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from student");
while( rs.next() ) {
Student stu = new Student();
stu.setId( rs.getInt("id") );
stu.setName( rs.getString("name") );
stu.setSex( rs.getString("sex") );
stu.setAge( rs.getInt("age") );
Major major = majorDao.listById( rs.getInt("major_id") );
stu.setMajor(major);
stus.add(stu);
}
}catch(Exception e) {
e.printStackTrace();
}
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
return stus;
}
@Override
public Student listById(int id) {
Connection con = null;
Student stu = new Student();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from student where id=" + id);
rs.next();
stu.setId( rs.getInt("id") );
stu.setName( rs.getString("name") );
stu.setSex( rs.getString("sex") );
stu.setAge( rs.getInt("age") );
Major major = majorDao.listById( rs.getInt("major_id") );
stu.setMajor(major);
}catch(Exception e) {
e.printStackTrace();
}
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
return stu;
}
@Override
public List<Student> list(Student stu) {
// TODO Auto-generated method stub
Connection con = null;
ArrayList <Student> stus = new ArrayList <Student> ();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
Statement st = con.createStatement();
String sql = "select * from student where 1=1";
int id = stu.getId();
String name = stu.getName();
String sex = stu.getSex();
int age = stu.getAge();
Major major = stu.getMajor();
if( id != -1 ) {
sql = sql + " and id=" + id;
}
if(name != null && !name.equals("")) {
sql = sql + " and name like '%" + name + "%'";
}
if(sex != null && !sex.equals("")) {
sql = sql + " and sex ='" + sex + "'";
}
if( age != -1 ) {
sql = sql + " and age=" + age;
}
if( null != major ) {
sql = sql + " and major_id=" + major.getId();
}
ResultSet rs = st.executeQuery(sql);
while( rs.next() ) {
Student stu1 = new Student();
stu1.setId( rs.getInt("id") );
stu1.setName( rs.getString("name") );
stu1.setSex( rs.getString("sex") );
stu1.setAge( rs.getInt("age") );
Major major1 = majorDao.listById( rs.getInt("major_id") );
stu1.setMajor(major1);
stus.add(stu1);
}
}catch(Exception e) {
e.printStackTrace();
}
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
return stus;
}
@Override
public boolean save(Student stu) {
// TODO Auto-generated method stub
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
String sql = "insert into student values (?,?,?,?,?)";
PreparedStatement pst = con.prepareStatement(sql);
pst.setInt(1, stu.getId());
pst.setString(2, stu.getName());
pst.setString(3, stu.getSex());
pst.setInt(4, stu.getAge());
pst.setInt(5, stu.getMajor().getId());
pst.execute();
return true;
}catch(Exception e) {
e.printStackTrace();
return false;
}
finally {
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
}
}
@Override
public boolean update(Student stu) {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
String sql = "update student set name=?,sex=?,age=?,major_id=? where id=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, stu.getName());
pst.setString(2, stu.getSex());
pst.setInt(3, stu.getAge());
pst.setInt(4, stu.getMajor().getId());
pst.setInt(5, stu.getId());
return pst.execute();
}catch(Exception e) {
e.printStackTrace();
return false;
}
finally {
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
}
}
@Override
public boolean del(int id) {
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url , DataSource.username , DataSource.password);
Statement st = con.createStatement();
st.execute("delete from student where id = " + id);
return true;
}catch(Exception e) {
e.printStackTrace();
return false;
}
finally {
try {
con.close();
}
catch(Exception e1) {
e1.printStackTrace();
}
}
}
}
扫一扫
383
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
