JasperReport (三) 主子报表的实现

飞宇千虹

已于 2024-04-06 11:22:19 修改

阅读量843

点赞数

CC 4.0 BY-SA版权

文章标签： java 数据库开发语言

于 2023-10-08 22:39:02 首次发布

本文链接：https://blog.csdn.net/weixin_45924250/article/details/133707471

本文详细描述了如何创建数据库表结构，包括学生、教师、课程和成绩表，并演示了如何使用SQL查询和报表设计，如主报表筛选和子报表嵌套。涉及了JDBC配置等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、数据源创建

1.1 学生表

CREATE TABLE Student(
SID NUMBER,
NAME VARCHAR(20)
);

1.2 教师表

CREATE  TABLE Teacher(
TID NUMBER,
NAME VARCHAR(20)
);

1.3 课程信息表

CREATE  TABLE CourseList(
CID NUMBER,
TID NUMBER,
NAME VARCHAR(20)
);

1.4 学科成绩表

CREATE  TABLE CourseScore(
SID NUMBER,
CID NUMBER,
SCORE NUMBER
);

1.5 数据插入

-- Student 表
INSERT INTO STUDENT ("SID", "NAME") VALUES ('1', '张三');
INSERT INTO STUDENT ("SID", "NAME") VALUES ('2', '李四');
-- Teacher 表
INSERT INTO TEACHER ("TID", "NAME") VALUES ('1', '老王');
INSERT INTO TEACHER ("TID", "NAME") VALUES ('2', '老李');
INSERT INTO TEACHER ("TID", "NAME") VALUES ('3', '老张');
-- CourseList 表
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('1', '1', '语文');
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('2', '2', '英语');
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('3', '3', '数学');
-- CourseScore 表
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '1', '50');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '2', '60');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '3', '80');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '1', '90');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '2', '88');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '3', '86');

1.6 语句整合

CREATE TABLE Student(
SID NUMBER,
NAME VARCHAR(20)
);

CREATE  TABLE Teacher(
TID NUMBER,
NAME VARCHAR(20)
);

CREATE  TABLE CourseList(
CID NUMBER,
TID NUMBER,
NAME VARCHAR(20)
);

CREATE  TABLE CourseScore(
SID NUMBER,
CID NUMBER,
SCORE NUMBER
);

-- Student 表
INSERT INTO STUDENT ("SID", "NAME") VALUES ('1', '张三');
INSERT INTO STUDENT ("SID", "NAME") VALUES ('2', '李四');
-- Teacher 表
INSERT INTO TEACHER ("TID", "NAME") VALUES ('1', '老王');
INSERT INTO TEACHER ("TID", "NAME") VALUES ('2', '老李');
INSERT INTO TEACHER ("TID", "NAME") VALUES ('3', '老张');
-- CourseList 表
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('1', '1', '语文');
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('2', '2', '英语');
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('3', '3', '数学');
-- CourseScore 表
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '1', '50');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '2', '60');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '3', '80');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '1', '90');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '2', '88');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '3', '86');

commit;

二、创建主报表文件

2.1 获取字段列表

select SID as "学号"，Name as "姓名" from student

2.2 修改执行语句

2.2.1 创建变量

2.2.3 修改执行语句

select SID as "学号"，Name as "姓名"
from student
where name like '%' || $P{姓名} || '%'

2.3 页面布局

三、创建子报表

3.1 获取数据字段

SELECT cl.Name "课程名",t.Name "任课老师名", cs.SCORE "成绩" FROM CourseScore cs LEFT JOIN Student s ON s.SID = cs.SID LEFT JOIN CourseList cl ON cs.CID = cl.CID LEFT JOIN Teacher t ON t.TID = cl.TID

3.2 修改执行语句

3.2.1 创建变量

3.2.2 修改执行语句

SELECT cl.Name "课程名",t.Name "任课老师名", cs.SCORE "成绩"
FROM CourseScore cs LEFT JOIN Student s ON s.SID = cs.SID LEFT JOIN CourseList cl ON cs.CID = cl.CID
LEFT JOIN Teacher t ON t.TID = cl.TID
WHERE s.SID = $P{学号}