创建数据库
设计数据库
现在已经设计好数据库GradeDB,定义了如下关系表
- 学生信息表(STUDENT)
| 属性名 | 存储代码 |
|---|---|
| 学生编号 | SID |
| 学生名称 | SName |
| 年龄 | Age |
| 性别 | Sex |
- 成绩信息表(GRADE)
| 属性名 | 存储代码 |
|---|---|
| 学生编号 | SID |
| 课程编号 | CID |
| 分数 | Score |
| 备注 | Note |
- 课程信息表(CROUSE)
| 属性名 | 存储代码 |
|---|---|
| 课程编号 | CID |
| 课程名称 | CName |
| 任课教师 | Teacher |
数据模型图如下:
- 概念数据模型
- 逻辑数据模型
- 物理数据模型
SQL语言实现
借助PowerDesigner生成对应的SQL语句,同时,因为要通过触发器来实现级联删除和级联更新,所以修改关系部分的语句,
/*==============================================================*/
/* Table: CROUSE */
/*==============================================================*/
create table CROUSE (
CID NUMERIC(8) not null,
CName VARCHAR(20) not null,
Teacher VARCHAR(20) not null,
constraint PK_CROUSE primary key (CID)
);
/*==============================================================*/
/* Index: CROUSE_PK */
/*==============================================================*/
create unique index CROUSE_PK on CROUSE (
CID
);
/*==============================================================*/
/* Table: GRADE */
/*==============================================================*/
create table GRADE (
SID NUMERIC(8) not null,
CID NUMERIC(8) not null,
Score INT2 not null,
Note TEXT null,
constraint PK_GRADE primary key (SID, CID)
);
/*==============================================================*/
/* Index: GRADE_PK */
/*==============================================================*/
create unique index GRADE_PK on GRADE (
SID,
CID
);
/*==============================================================*/
/* Index: have_FK */
/*==============================================================*/
create index have_FK on GRADE (
SID
);
/*==============================================================*/
/* Index: has_FK */
/*==============================================================*/
create index has_FK on GRADE (
CID
);
/*==============================================================*/
/* Table: STUDENT */
/*==============================================================*/
create table STUDENT (
SID NUMERIC(8) not null,
SName NUMERIC(8) not null,
Sex BOOL not null,
Age INT2 not null,
constraint PK_STUDENT primary key (SID)
);
/*==============================================================*/
/* Index: STUDENT_PK */
/*==============================================================*/
create unique index STUDENT_PK on STUDENT (
SID
);
插入数据
可以借助PowerDesigner生成测试数据,具体步骤为:菜单栏–>数据库–>生成测试数据–>设置相应参数–>点击生成
对生成的数据稍加修改,使满足要求
insert into STUDENT (SID, SName, Sex, Age) values (0, 9, true, 9);
insert into STUDENT (SID, SName, Sex, Age) values (1, 0, false, 3);
insert into STUDENT (SID, SName, Sex, Age) values (4, 8, true, 0);
insert into STUDENT (SID, SName, Sex, Age) values (7, 4, false, 4);
insert into STUDENT (SID, SName, Sex, Age) values (8, 6, true, 7);
insert into STUDENT (SID, SName, Sex, Age) values (9, 1, false, 6);
insert into STUDENT (SID, SName, Sex, Age) values (3, 2, true, 2);
insert into STUDENT (SID, SName, Sex, Age) values (2, 3, false, 5);
insert into STUDENT (SID, SName, Sex, Age) values (6, 7, true, 8);
insert into STUDENT (SID, SName, Sex, Age) values (5, 5, false, 1);
insert into CROUSE (CID, CName, Teacher) values (1, '数据库原理与应用', '张三');
insert into CROUSE (CID, CName, Teacher) values (0, '数据结构与算法', '李四');
insert into GRADE (SID, CID, Score, Note) values (0, 0, 80, '123456');
insert into GRADE (SID, CID, Score, Note) values (0, 1, 81, '1234567');
insert into GRADE (SID, CID, Score, Note) values (1, 0, 82, '123456');
insert into GRADE (SID, CID, Score, Note) values (1, 1, 83, '1234567');
insert into GRADE (SID, CID, Score, Note) values (2, 0, 84, '123456');
insert into GRADE (SID, CID, Score, Note) values (2, 1, 85, '1234567');
insert into GRADE (SID, CID, Score, Note) values (3, 0, 86, '123456');
insert into GRADE (SID, CID, Score, Note) values (3, 1, 87, '1234567');
insert into GRADE (SID, CID, Score, Note) values (4, 0, 88, '123456');
insert into GRADE (SID, CID, Score, Note) values (4, 1, 89, '1234567');
结果展示
- 插入执行成功
- 查看三个表的数据
Java Web实现
创建数据库
为了方便对比,不妨创建数据库GradeDB2
//链接数据库,ConnUtil.class
package gradedb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnUtil
{
public static Connection getConn()
{
Connection conn = null;
try
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/";
String username = "postgres";
String password = "yzn1370628636";
try
{
conn = DriverManager.getConnection(url, username, password);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
return conn;
}
}
//创建数据库,TestMain.class
package gradedb;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestMain
{
/**
* @param args
*/
public static void main(String[] args)
{
Connection conn=ConnUtil.getConn();
String sql="CREATE DATEBASE GradeDB2";
Statement stmt=null;
try
{
stmt=conn.createStatement();
stmt.executeUpdate(sql);
}
catch (SQLException e)
{
e.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}
}
}
运行,创建数据库成功
查看pgAdmin4可看到已经生成了对应的数据库
创建表并插入数据
将ConnUtil.class中的url修改以连接到GradeDB2
String url = "jdbc:postgresql://localhost:5432/gradedb2";
创建InitDate.class,使创建表并插入数据,因为与SQL部分有较大重复,此处只展示部分创建和数据插入
public class InitDate
{
/**
* @param args
*/
public static void main(String[] args)
{
Connection conn=ConnUtil.getConn();
Statement stmt=null;
try
{
stmt=conn.createStatement();
String sql = "create table CROUSE (" +
" CID NUMERIC(8) not null," +
" CName VARCHAR(20) not null," +
" Teacher VARCHAR(20) not null," +
" constraint PK_CROUSE primary key (CID)" +
")";
stmt.executeUpdate(sql);
System.out.println("table created successfully");
sql =
"insert into CROUSE (CID, CName, Teacher) values (1, '数据库原理与应用', '张三');" +
"insert into CROUSE (CID, CName, Teacher) values (0, '数据结构与算法', '李四');";
stmt.executeUpdate(sql);
System.out.println("date item inserted successfully");
sql="select * from crouse";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("CID");
String crousename = rs.getString("CName");
String teacher = rs.getString("Teacher");
System.out.print("CID: "+id);
System.out.print(",CName: "+crousename);
System.out.println(",Teacher: "+teacher);
}
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
根据返回结果来看,添加数据没有问题
构建触发器
编写触发器实现STUDENT表、CROUSE表与GRADE表之间的级联更新、级联删除
首先需要明确STUDENT和GRADE,CROUSE和GRADE之间存在着级联更新和删除.
STUDENT删除(更新SID)会导致GRADE删除(更新)CROUSE删除(更新CID)会导致GRADE删除(更新)GRADE更新和删除不应该影响STUDENT和GRADE
所以触发器分别建立在STUDENT和CROUSE表上
PL/pgSQL实现
语言编写
根据上述条件有如下SQL语句
--创建触发器函数student_grade()
CREATE OR REPLACE FUNCTION student_grade() RETURNS TRIGGER AS $student_grade$
BEGIN
IF (TG_OP = 'DELETE') THEN
DELETE FROM GRADE WHERE SID=OLD.SID;
RETURN NEW;
ELSEIF (TG_OP = 'UPDATE' OR OLD.SID IS DISTINCT FROM NEW.SID) THEN
--SID之外的信息更改不影响GRADE
UPDATE GRADE SET SID=NEW.SID WHERE SID=OLD.SID;
RETURN NEW;
END IF;
END;
$student_grade$ LANGUAGE plpgsql;
--创建student上的触发器
CREATE TRIGGER student_grades_tigger
AFTER UPDATE OR DELETE ON STUDENT
FOR EACH ROW
EXECUTE PROCEDURE student_grade();
--创建触发器函数crouse_grade()
CREATE OR REPLACE FUNCTION crouse_grade() RETURNS TRIGGER AS $crouse_grade$
BEGIN
IF (TG_OP = 'DELETE') THEN
DELETE FROM GRADE WHERE CID=OLD.CID;
RETURN NEW;
ELSEIF (TG_OP = 'UPDATE' OR OLD.CID IS DISTINCT FROM NEW.CID) THEN
UPDATE GRADE SET CID=NEW.CID WHERE CID=OLD.CID;
RETURN NEW;
END IF;
END;
$crouse_grade$ LANGUAGE plpgsql;
--创建crouse上的触发器
CREATE TRIGGER crouse_grades_tigger
AFTER UPDATE OR DELETE ON crouse
FOR EACH ROW
EXECUTE PROCEDURE crouse_grade();
创建结果
测试
接下来对student修改某个SID,查看grade的变换
修改语句如下:
update student set sid=10 where sid=0;
select * from grade;
与前面创建时相比,确实将SID=0的项修改为SID=10
Java Web实现
类似之前的创建表,同样,使用Statement类执行sql语句生成.以创建Crouse表上的触发器为例.
public class CreateTrigger {
public static void main(String[] args)
{
Connection conn=ConnUtil.getConn();
Statement stmt=null;
try
{
stmt=conn.createStatement();
String sql =
"CREATE OR REPLACE FUNCTION crouse_grade() RETURNS TRIGGER AS $crouse_grade$\r\n" +
" BEGIN\r\n" +
" IF (TG_OP = 'DELETE') THEN\r\n" +
" DELETE FROM GRADE WHERE CID=OLD.CID;\r\n" +
" RETURN NEW;\r\n" +
" ELSEIF (TG_OP = 'UPDATE' OR OLD.CID IS DISTINCT FROM NEW.CID) THEN\r\n" +
" UPDATE GRADE SET CID=NEW.CID WHERE CID=OLD.CID;\r\n" +
" RETURN NEW;\r\n" +
" END IF;\r\n" +
" END;\r\n" +
"$crouse_grade$ LANGUAGE plpgsql;";
stmt.executeUpdate(sql);
sql = "CREATE TRIGGER crouse_grades_tigger \r\n" +
"AFTER UPDATE OR DELETE ON crouse \r\n" +
"FOR EACH ROW \r\n" +
"EXECUTE PROCEDURE crouse_grade();";
stmt.executeUpdate(sql);
System.out.println("created successfully");
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
可以看到已经生成了对应的触发器
构建存储过程
编写存储过程程序实现统计各课程不及格学生人数，并在屏幕输出.这里不妨取分数低于85的为不及格
PL/pgSQL实现
CREATE OR REPLACE FUNCTION check_fail() returns bool as $$
declare
rec RECORD;
begin
for rec in select sname,cname,score from grade
right join student on grade.sid=student.sid
right join crouse on grade.cid=crouse.cid
where grade.score<85 loop
raise info '不及格信息:%,%,%',rec.sname,rec.cname,rec.score;
end loop;
return true;
end;
$$ language plpgsql;
调用函数,查询结果
select check_fail();
控制台中返回了如下信息:
Java Web实现
同上,也用Statement生成对应的存储过程
public class CreateTrigger {
public static void main(String[] args)
{
Connection conn=ConnUtil.getConn();
Statement stmt=null;
try
{
stmt=conn.createStatement();
String sql =
"CREATE OR REPLACE FUNCTION check_fail() returns bool as $$\r\n" +
"declare \r\n" +
" rec RECORD;\r\n" +
"begin\r\n" +
" for rec in select sname,cname,score from grade \r\n"+
"right join student on grade.sid=student.sid \r\n" +
" right join crouse on grade.cid=crouse.cid\r\n" +
" where grade.score<85 loop\r\n" +
" raise info '不及格信息:%,%,%',rec.sname,rec.cname,rec.score;\r\n" +
" end loop;\r\n" +
" return true;\r\n" +
"end;\r\n" +
"$$ language plpgsql;";
stmt.executeUpdate(sql);
System.out.println("created successfully");
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
查看pgAdmin4生成了对应的存储过程
查询课程学生成绩
用Mybatis+Servlet+jsp编程实现查询课程学生成绩列表页面输出功能
首先需要搭建开发的环境,下载Tomcat,在Eclipse下选择菜单栏Windows–>preferences,在Server下的Runtime添加Tomcat,如下图所示
需要下载JSP的标准标签库文件,将jar文件复制到WEB-INF–lib下,将一些tld文件复制到WEB-INF下;再将JDBC驱动程序复制到Tomcat–>lib下
Mybatis借助的教程
jsp+servelt借助的教程
工程的大致目录如下图所示,有些文件是用于测试的杂项,有些因为实现成绩查询功能暂时用不到,所以没有创建
- Mybatis的一些基本配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<!--给实体类起一个别名 user -->
<typeAlias alias="Grade" type="PO.Grade" />
<typeAlias alias="Student" type="PO.Student" />
<typeAlias alias="Crouse" type="PO.Crouse" />
</typeAliases>
<!--数据源配置 这块用 BD2数据库 -->
<environments default="development">
<environment id="development">
<transactionManager type="jdbc" />
<dataSource type="POOLED">
<property name="driver" value="org.postgresql.Driver" />
<property name="url"
value="jdbc:postgresql://localhost:5432/gradedb2?charSet=utf-8"/>
<property name="username" value="postgres" />
<property name="password" value="yzn1370628636" />
</dataSource>
</environment>
</environments>
<mappers>
<!--grade.xml装载进来 同等于把“dao”的实现装载进来 -->
<mapper resource="config/grade.xml" />
<mapper resource="config/crouse.xml"/>
<mapper resource="config/student.xml"/>
</mappers>
</configuration>
- 实体与关系的映射,展示Grade.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--这块等于dao接口的实现 namespace必须和接口的类路径一样 -->
<mapper namespace="GradeMapper">
<!--type是在configuration.xml里定义过的typeAlias -->
<resultMap id="GradeResult" type="Grade">
<id column="sid" property="sid" jdbcType="NUMERIC" />
<id column="cid" property="cid" jdbcType="NUMERIC" />
<result column="score" property="score" jdbcType="SMALLINT" />
<result column="note" property="note" jdbcType="VARCHAR" />
<collection property="crouse" ofType="Crouse">
<id property="cid" column="cid"/>
<result property="teacher" column="teacher"/>
<result property="cname" column="cname"/>
</collection>
<collection property="student" ofType="student">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
</collection>
</resultMap>
<insert id="addGrade" parameterType="Grade" useGeneratedKeys="true" keyProperty="id">
insert into
Grade(sid,cid,score,note)
values(#{sid},#{cid},#{score},#{note})
</insert>
<update id="updateGrade" parameterType="Grade">
update Grade set
score=#{score:SMALLINT} where
sid=#{sid:NUMERIC} and cid=#{cid:NUMERIC}
</update>
<select id="findGrade" parameterType="int" resultMap="GradeResult">
select grade.*,student.sname,crouse.cname from grade
right join student on grade.sid=student.sid
right join crouse on grade.cid=crouse.cid
where grade.sid = #{grade.sid:NUMERIC}
</select>
<delete id="deleteGrade" parameterType="int">
delete
from Grade where
sid=#{sid:NUMERIC}
</delete>
<select id="countGrade" resultType="int">
select count(*) from Grade
</select>
<select id="listGrade" resultMap="GradeResult">
select * from grade order by sid
</select>
</mapper>
- Mybatis工具类整合
package Util;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisUtil {
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sqlSessionFactory;
static{
try {
Reader reader = Resources.getResourceAsReader("mybatis.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
//禁止外界通过new方法创建
private MybatisUtil(){}
/**
* 获取SqlSession
*/
public static SqlSession getSqlSession(){
//从当前线程中获取SqlSession对象
SqlSession sqlSession = threadLocal.get();
//如果SqlSession对象为空
if(sqlSession == null){
//在SqlSessionFactory非空的情况下，获取SqlSession对象
sqlSession = sqlSessionFactory.openSession();
//将SqlSession对象与当前线程绑定在一起
threadLocal.set(sqlSession);
}
//返回SqlSession对象
return sqlSession;
}
/**
* 关闭SqlSession与当前线程分开
*/
public static void closeSqlSession(){
//从当前线程中获取SqlSession对象
SqlSession sqlSession = threadLocal.get();
//如果SqlSession对象非空
if(sqlSession != null){
//关闭SqlSession对象
sqlSession.close();
//分开当前线程与SqlSession对象的关系，目的是让GC尽早回收
threadLocal.remove();
}
}
}
- Pojo类,展示Grade.java文件内容
package PO;
public class Grade {
private Integer sid;
private Integer cid;
private Integer score;
private String note;
private Student student;
private Crouse crouse;
public Grade() {
}
public Grade(Integer sid, Integer cid, Integer score, String note) {
super();
this.sid = sid;
this.cid = cid;
this.score = score;
this.note = note;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
public Crouse getCrouse() {
return crouse;
}
public void setCrouse(Crouse crouse) {
this.crouse = crouse;
}
@Override
public String toString() {
return "\nGrade [sid=" + sid + ",sname="+", cid=" + cid + ", score="
+ score + ", note=" + note + "]";
}
}
- DAO层,持久化接口,展示GradeDAO.java和GradeDAOImpl.java文件内容
//接口文件GradeDAO.java
package DAO;
import java.util.List;
import PO.Grade;
public interface GradeDao {
public List<Grade> findbyId(Integer id);
}
//实现接口GradeDAOImpl.java
package DAO.Impl;
import DAO.GradeDao;
import PO.Grade;
import Util.MybatisUtil;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
public class GradeDaoImpl implements GradeDao{
@Override
public List<Grade> findbyId(Integer id){
SqlSession sqlSession=MybatisUtil.getSqlSession();
try {
return sqlSession.selectList("GradeMapper.findGrade",id);
}catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
throw e;
}finally{
MybatisUtil.closeSqlSession();
}
}
}
- ShowServlet.java
package servlet;
import PO.Grade;
import result.GradeResult;
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 DAO.Impl.GradeDaoImpl;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/Search")
public class ShowServlet extends HttpServlet{
private GradeDaoImpl gradedao = new GradeDaoImpl();
@Override
protected void service(HttpServletRequest req,HttpServletResponse resp)
throws ServletException,IOException{
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
Integer id=null;
String sid=req.getParameter("sid");
if(sid!=null){
id = Integer.valueOf(sid);
}
Grade temp=null;
List<GradeResult> res=new ArrayList<GradeResult>();
List<Grade> list = gradedao.findbyId(id);
//重新处理一下返回的结果表,方便jsp调用
for (int i=0;i<list.size();i++) {
temp=list.get(i);
res.add(new GradeResult(temp.getScore(),temp.getNote(),
temp.getStudent().getSname(),temp.getCrouse().getCname()));
}
//System.out.print(list);
req.setAttribute("list", res);
req.getRequestDispatcher("search.jsp").forward(req, resp);
}
}
- 最后编写相应的jsp文件
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>成绩查询</title>
</head>
<body>
<h1>学生成绩查询</h1>
<form method="post">
学号：<input type="text" name="sid" style="width:100px"/><br/>
<input type="submit" value="查询"/>
</form>
<table border="1">
<tr>
<th>名字</th>
<th>课程名称</th>
<th>分数</th>
<th>备注</th>
</tr>
<c:forEach items="${list}" var="grade">
<tr>
<td>${grade.sname}</td>
<td>${grade.cname}</td>
<td>${grade.score}</td>
<td>${grade.note}</td>
</tr>
</c:forEach>
</table>
</body>
</html>
最后进行测试有如下的结果
存在问题
- 在显示多表联合查询的结果时,不知如何获取Grade中Student的sname属性,只能用最笨的方法重新创建了一个类用于保存这些需要的结果
- 在查询提交后没有把之前输入的结果保存下来(滞留在输入框上)
扫一扫
839
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
