Java+Swing+sqlServer学生成绩管理系统（DAY 11）从数据库student表中获取主窗口的表格信息
问题引入
- 如何连接数据库中表的信息
- 如何将表的内容准确的对应到Java程序中的主窗口
- 如何实现查询与按钮响应
- 如何实现分页与按钮响应的功能（舍弃）
遇到的问题
-
StudentServiceImpl类中“limit”附近有语法错误。
sqlServer语句是不支持limit分页功能，所以我只能忍痛将系统中所有包含分页的按钮与事件舍弃
逻辑关系
实现效果
代码实现
Student表
create table student
(id int
constraint c1 not null
constraint UK unique,
sname varchar(10)
constraint c2 not null,
sno varchar(10),
homeTown varchar(10),
cnScore int ,
enScore int ,
mathScore int ,
totalScore int ,
constraint student_PK primary key(sno),
)
insert into student values(1,'张三','202001','长沙',97,98,99,294)
insert into student values(2,'李四','202002','北京',96,85,94,275)
insert into student values(3,'a','202003','北京',96,85,94,275)
insert into student values(4,'b','202004','北京',96,85,94,275)
insert into student values(5,'c','202005','北京',96,85,94,275)
insert into student values(6,'d','202006','北京',96,85,94,275)
insert into student values(7,'e','202007','北京',96,85,94,275)
insert into student values(8,'ff','202008','北京',96,85,94,275)
insert into student values(9,'f','202009','北京',96,85,94,275)
insert into student values(10,'fff','202010','北京',96,85,94,275)
insert into student values(11,'ffff','202011','北京',96,85,94,275)
insert into student values(12,'aa','202012','北京',96,85,94,275)
insert into student values(13,'ss','202013','北京',96,85,94,275)
insert into student values(14,'dd','202014','北京',96,85,94,275)
insert into student values(15,'ssss','202015','北京',96,85,94,275)
insert into student values(16,'ffffff','202016','北京',96,85,94,275)
insert into student values(17,'rr','202017','北京',96,85,94,275)
insert into student values(18,'r','202018','北京',96,85,94,275)
insert into student values(19,'y','202019','北京',96,85,94,275)
insert into student values(20,'yy','202020','北京',96,85,94,275)
insert into student values(21,'fy','202021','北京',96,85,94,275)
insert into student values(22,'fuf','202022','北京',96,85,94,275)
insert into student values(23,'fuuf','202023','北京',96,85,94,275)
insert into student values(24,'foof','202024','北京',96,85,94,275)
StudentRequest
package req;
public class StudentRequest {
private int pageNow;
private int pageSize;
//每一页的第一条记录
private int start;
//查询词
private String searchKey;
public int getStart() {
//比如第一页从第零条记录开始
return (pageNow-1)*pageSize;
}
public int getPageNow() {
return pageNow;
}
public void setPageNow(int pageNow) {
this.pageNow = pageNow;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public String getSearchKey() {
return searchKey;
}
public void setSearchKey(String searchKey) {
this.searchKey = searchKey;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
}
}
StudentService
package service;
import req.StudentRequest;
import res.TableDTO;
public interface StudentService {
//获取查询、搜索的结果，定义一个接口
//返回值应当返回一个data和记录总共的条数，也将其封装成一个对象TableDTO
//入参应有当前是第几页，查询词等，所以将入参包装成一个对象StudentRequest
TableDTO retrieveStudents(StudentRequest request);
//
}
TableDTO
package res;
import java.util.Vector;
public class TableDTO {
private Vector<Vector<Object>> data;
private int totalCount;
public Vector<Vector<Object>> getData() {
return data;
}
public void setData(Vector<Vector<Object>> data) {
this.data = data;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
}
}
StudentServiceImpl
package service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import req.StudentRequest;
import res.TableDTO;
import util.DBUtil;
/*
* StudentService实现类
*/
public class StudentServiceImpl implements StudentService{
@Override
public TableDTO retrieveStudents(StudentRequest request) {
StringBuffer sql=new StringBuffer();
sql.append("select * from student ");
//判断是否为空，如果查询词为空就不查
if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())) {
sql.append(" where sname like '%"+request.getSearchKey().trim()+"%'");
}
/*
* mysql里用order排序limit分页
* 但是sqlServer里不支持分页
* 因而下面两行代码舍弃，对应的分页功能也舍弃
*/
// sql.append("order by id desc limit ").append(request.getStart()).append(", ")
// .append(request.getPageSize());
//至此sql语句定义完成
//执行sql语句
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
TableDTO returnDTO=new TableDTO();
try {
conn=DBUtil.getConn();
ps =conn.prepareStatement(sql.toString());
rs=ps.executeQuery();
//查询记录,并设置到TableDTO中
returnDTO.setData(fillData(rs));
//至此sql语句完成data的查询
//将sql语句置空，开始查询totalCount
sql.setLength(0);
sql.append("select count(*) from student ");
if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())) {
sql.append(" where sname like '%"+request.getSearchKey().trim()+"%'");
}
ps =conn.prepareStatement(sql.toString());
rs=ps.executeQuery();
while(rs.next()) {
int count=rs.getInt(1);
//仅有一条记录，直接设置到TableDTO中
returnDTO.setTotalCount(count);
}
return returnDTO;
}catch(Exception e) {
e.printStackTrace();
}finally {
DBUtil.closeRs(rs);
DBUtil.closePs(ps);
DBUtil.closeConn(conn);
}
return null;
}
private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
Vector<Vector<Object>> data=new Vector<>();
while(rs.next()) {
//处理查出的每一条记录
Vector<Object> oneRecord=new Vector<>();
//获取每一列属性下的值,参数可以填属性名，也可以填对应的序号
int id=rs.getInt("id");
String sname=rs.getString("sname");
String sno=rs.getString("sno");
String homeTown=rs.getString("homeTown");
int cnScore=rs.getInt("cnScore");
int enScore=rs.getInt("enScore");
int mathSocre=rs.getInt("mathScore");
int totalScore=cnScore+enScore+mathSocre;
//将每一列的值加入到oneRecord中
oneRecord.addElement(id);
oneRecord.addElement(sname);
oneRecord.addElement(sno);
oneRecord.addElement(homeTown);
oneRecord.addElement(cnScore);
oneRecord.addElement(enScore);
oneRecord.addElement(mathSocre);
oneRecord.addElement(totalScore);
//将每一行的记录加入到二维data数组中
data.addElement(oneRecord);
}
return data;
}
}
MainView
package view;
import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.FlowLayout;
import java.net.URL;
import java.util.Vector;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextField;
import ext.MainViewTable;
import ext.MainViewTableModel;
import handler.MainHandler;
import req.StudentRequest;
import res.TableDTO;
import service.StudentService;
import service.StudentServiceImpl;
import util.DimensionUtil;
public class MainView extends JFrame{
JPanel northPanel=new JPanel(new FlowLayout(FlowLayout.LEFT));//JPanel默认流布局,组件居左
JButton addBtn=new JButton("增加");
JButton updataBtn=new JButton("修改");
JButton delBtn=new JButton("删除");
JTextField searchTxt=new JTextField(15);
JButton searchBtn=new JButton("查询");
JPanel southPanel=new JPanel(new FlowLayout(FlowLayout.RIGHT));//组件居右
JButton preBtn=new JButton("上一页");
JButton nextBtn =new JButton("下一页");
//创建一个MainViewTable对象
MainViewTable mianViewTable=new MainViewTable();
//声明一个MainHandler对象
MainHandler mainHandler;
//
private int pageNow=1;//默认显示第一页
private int pageSize=10;//默认每页显示十条记录
//构造方法
public MainView() {
super("主界面-学生成绩管理系统");
Container contentPane=getContentPane();
//创建一个MainHandler对象
mainHandler = new MainHandler(this);
//放置北边的组件
layoutNorth(contentPane);
//放置中间的组件
layoutCenter(contentPane);
//放置南边的组件
layoutSouth(contentPane);
//基础设置
init();
}
private void layoutCenter(Container contentPane) {
//调用数据库数据，创建一个StudentService和StudentRequest实例
//获取从数据库中查询到的data与totalCount
StudentService studentService=new StudentServiceImpl();
StudentRequest request=new StudentRequest();
request.setPageNow(pageNow);
request.setPageSize(pageSize);
request.setSearchKey(searchTxt.getText().trim());
TableDTO tableDTO = studentService.retrieveStudents(request);
Vector<Vector<Object>> data = tableDTO.getData();
showPreNext(tableDTO.getTotalCount());
//暂时还没调用数据库，所以把主面板上内容写死，创建一个二维数组存放data
// Vector<Vector<Object>> data = new Vector<>();
// Vector<Object> rowVector1 = new Vector<>();
// rowVector1.addElement("1");
// rowVector1.addElement("张三");
// rowVector1.addElement("1");
// rowVector1.addElement("湖南");
//
// Vector<Object> rowVector2 = new Vector<>();
// rowVector2.addElement("2");
// rowVector2.addElement("李四");
// rowVector2.addElement("2");
// rowVector2.addElement("北京");
//
// Vector<Object> rowVector3 = new Vector<>();
// rowVector3.addElement("3");
// rowVector3.addElement("王五");
// rowVector3.addElement("3");
// rowVector3.addElement("上海");
//
// data.addElement(rowVector1);
// data.addElement(rowVector2);
// data.addElement(rowVector3);
//自定义的MainViewTableModel
MainViewTableModel mainViewTableModel=MainViewTableModel.assembleModel(data);
//将Table与TableModel关联
mianViewTable.setModel(mainViewTableModel);
//设置渲染方式
mianViewTable.renderRule();
JScrollPane jScrollPane = new JScrollPane(mianViewTable);
contentPane.add(jScrollPane,BorderLayout.CENTER);
}
private void layoutSouth(Container contentPane) {
//增加按钮事件监听
preBtn.addActionListener(mainHandler);
nextBtn.addActionListener(mainHandler);
southPanel.add(preBtn);
southPanel.add(nextBtn);
contentPane.add(southPanel,BorderLayout.SOUTH);
}
/*
* 设置上一页与下一页按钮是否可见
*/
private void showPreNext(int totalCount) {
if(pageNow==1) {
preBtn.setVisible(false);
}else {
preBtn.setVisible(true);
}
//总共的页数
int pageCount=0;
if(totalCount%pageSize==0) {
pageCount=totalCount/pageSize;
}else {
pageCount=totalCount/pageSize+1;
}
if(pageCount==pageNow) {
nextBtn.setVisible(false);
}else {
nextBtn.setVisible(true);
}
}
private void layoutNorth(Container contentPane) {
//增加按钮事件监听
addBtn.addActionListener(mainHandler);
updataBtn.addActionListener(mainHandler);
delBtn.addActionListener(mainHandler);
searchBtn.addActionListener(mainHandler);
northPanel.add(addBtn);
northPanel.add(updataBtn);
northPanel.add(delBtn);
northPanel.add(searchTxt);
northPanel.add(searchBtn);
contentPane.add(northPanel,BorderLayout.NORTH);
}
private void init() {
//设置窗口图片
URL imgUrl=LoginView.class.getClassLoader().getResource("LoginView.jpg");
setIconImage(new ImageIcon(imgUrl).getImage());
//设置窗口基本参数
setBounds(DimensionUtil.getBounds());
//设置窗体完全充满整个屏幕
setExtendedState(JFrame.MAXIMIZED_BOTH);
setDefaultCloseOperation(EXIT_ON_CLOSE);
setLocationRelativeTo(null);
setResizable(true);
setVisible(true);
}
public static void main(String[] args) {
// TODO Auto-generated method stub
}
public void setPageNow(int pageNow) {
this.pageNow = pageNow;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageNow() {
return pageNow;
}
public void reloadTable() {
/*
*调用数据库数据，创建一个StudentService和StudentRequest实例
*获取从数据库中查询到的data与totalCount
*查询完后要更新Model的数据
*/
StudentService studentService=new StudentServiceImpl();
StudentRequest request=new StudentRequest();
request.setPageNow(pageNow);
request.setPageSize(pageSize);
request.setSearchKey(searchTxt.getText().trim());
TableDTO tableDTO = studentService.retrieveStudents(request);
Vector<Vector<Object>> data = tableDTO.getData();
// 更新调用updataModel实现更新Model
MainViewTableModel.uptadaModel(data);
// 设置渲染方式
mianViewTable.renderRule();
// 调用上一页与下一页按钮是否可见
showPreNext(tableDTO.getTotalCount());
}
}
MainHandler
package handler;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import view.MainView;
public class MainHandler implements ActionListener{
private MainView mainView;
public MainHandler(MainView mainView) {
this.mainView=mainView;
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
JButton jButton=(JButton) e.getSource();
String text = jButton.getText();
if("增加".equals(text)) {
}else if("修改".equals(text)) {
}else if("删除".equals(text)) {
}else if("查询".equals(text)) {
//每次查询都是从第一页开始
mainView.setPageNow(1);
//查询
mainView.reloadTable();
}else if("上一页".equals(text)) {
mainView.setPageNow(mainView.getPageNow()-1);
}else if("下一页".equals(text)) {
mainView.setPageNow(mainView.getPageNow()+1);
}
}
}