面试常见问题——数据库（一）

于 2020-04-08 13:51:25 发布
阅读量558 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库 sqlserver 机试 JTabbedPane Swing
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Remoa_Dengqinyi/article/details/104964878
版权

面试常见问题——数据库（一）

目录：

  1. 应用实例
    1. XML转txt
    2. 建表
    3. 图形化界面上传文件，数据入库
    4. 条件查询，结果排序

一、应用实例

1、题目描述：a）读入XML文本，按成绩降序排序输出成txt，如果成绩相等，则按照姓名升序。(40分)
b）建三个表：课程表（课程Id，课程名称），学生表（学生Id，学生姓名，性别），成绩表（课程Id，学生Id，成绩Id，分数）（20分）
c）编写应用程序，要求有可视化界面，能进行数据库连接，和实现如下操作：
①实现将xml文件导入数据库。注意题目说不是直接导入，而是要写代码实现导入。（20分）
②实现数据表查看，同时可根据课程名查看学生的成绩，成绩要求从大到小排序（20分）

（1）前言：

  • 假设上机环境中没有maven等构建工具，使用JDK版本为1.8。数据库考纲为关系型数据库，因此这里用SQL Server2012进行测试。
  • 思路：
    • JTabbedPane设置三个选项卡：
      • 第一个选项卡，实现数据库导入功能：用户上传XML文件，系统统一存放该xml文件到指定目录下：C:/Users/邓小艺/Desktop/2020考研复试/，在程序后台中创建表（详见ExamService类中createAllTable方法），文件上传成功后，后台将XML文件中数据解析成Java Bean，然后存放到数据库中
      • 第二个选项卡，实现数据库表查看功能：通过JPanel中存放JComboBox进行选择不同数据表查看其内容，内容展示由JTable + JScrollPane + AbstractTableModel实现（详见ViewAllDataPanel类）
      • 第三个选项卡，实现按照课程名查看学生成绩的功能：通过JPanel中存放JComboBox，选项卡中课程名添加监听器实时更新，内容展示由JTable + JScrollPane + AbstractTableModel实现（详见GetGradePanel类）

（2）遇到的问题及解决方案：

  • 问题1：打包后出现错误：java.lang.SecurityException: Invalid signature file digest for Manifest main attributes
  • 解决方案：
    • 删除打包后jar包中的*.SF,*.DSA,*.RSA文件
    • 在cmd中cd进入到jar包存放的目录后，执行java -jar secondRoundExam.jar
    • 或者可以直接使用exe4j工具，将jar包转换为exe可执行程序
  • 问题2：JDK版本冲突：A JNI error has occurred, please check your installation and try again
  • 解决方案：
    • 多JDK版本可共存。打开环境变量，修改系统变量（比如我本机中java -version查看为1.10，javac -version查看为1.8，将其统一使用JDK1.8）：
      • 创建环境变量：
        • JAVA8_HOME=C:\Program Files\Java\jdk1.8.0
        • JAVA10_HOME=C:\Program Files\Java\jdk-10.0.1
        • JAVA_HOME=%JAVA8_HOME%
      • 然后在path环境变量中添加%JAVA_HOME%\bin;%MAVEN_HOME%\bin;%JAVA_HOME%\jre\bin;其中该变量要放在C:\Program Files (x86)\Common Files\Oracle\Java\javapath;前面（如果存在）。
  • 问题3：使用idea打包流程出错
  • 解决方案：
    • File -> Project Structure -> Artifacts -> 选择Add JAR -> 选择From modules with dependencies -> 指定Main Class ->  选择extract to the target jar -> Directory for META-INF/MANIFEST.MF中修改路径，去掉\main\java，修改后为：D:\workspace\secondRoundExam\src -> 点击OK即可。
  • 问题4：idea工程打开时默认jdk1.5
  • 解决方案：
    • 对工程统一使用JDK1.8。
      • File -> File Settings -> Build,Execution,Deployment -> Java Compiler -> Project bytecode version修改为1.8，同时Target bytecode version修改为1.8
      • File -> Project Structure -> Project -> Project SDK选择1.8，下面Project language level选择为8
      • File -> Project Structure -> Modules -> Sources -> Language level选择为8
  • 问题5：配置SQL Server2012使用用户名及密码通过jdbc + TCP/IP连接
  • 解决方案：
    • SQL Server Management Studio配置：
      • 连接、选择数据库引擎，通过Windows身份验证登录后，右键选择服务器属性 -> 安全性 -> 服务器身份验证选择SQL Server和Windows身份验证模式
      • 左侧菜单栏 选择安全性 -> 登录名 -> 选择sa右键属性，设置密码123456
    • 本地防火墙配置：
      • 打开控制面板 -> 系统和安全 -> Windows防火墙 -> 关闭防火墙
    • jdbc配置：
      • 提前准备好sqljdbc4.jar
      • 配置参数：
        • private static final String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        • private static final String user = "sa";
        • private static final String password = "123456";
        • private static final String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=SCUT";

（3）程序界面实现以及包目录截图：

i）可视化界面：

ii）按成绩降序输出txt：

iii）Java工程目录结构：


（4）测试样例XML文件内容：

<grades>
    <grade grade_id="grade_001">
        <id>2019001</id>
        <name>张三</name>
        <course>计算机组成原理</course>
        <score>85</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_002">
        <id>2019002</id>
        <name>李四</name>
        <course>操作系统</course>
        <score>90</score>
        <sex>女</sex>
    </grade>
    <grade grade_id="grade_003">
        <id>2019003</id>
        <name>王五</name>
        <course>数据结构</course>
        <score>95</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_004">
        <id>2019004</id>
        <name>赵六</name>
        <course>计算机网络</course>
        <score>70</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_005">
        <id>2019005</id>
        <name>郑成</name>
        <course>数据库</course>
        <score>90</score>
        <sex>女</sex>
    </grade>
    <grade grade_id="grade_006">
        <id>2019006</id>
        <name>刘奇</name>
        <course>编译原理</course>
        <score>90</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_888">
        <id>2019007</id>
        <name>易烊千玺</name>
        <course>泥塑</course>
        <score>100</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_666">
        <id>2019008</id>
        <name>千玺零</name>
        <course>跳舞</course>
        <score>100</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_999">
        <id>2019009</id>
        <name>千玺一</name>
        <course>数据库</course>
        <score>99</score>
        <sex>女</sex>
    </grade>
    <grade grade_id="grade_667">
        <id>2019010</id>
        <name>千玺二</name>
        <course>跳舞</course>
        <score>95</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_668">
        <id>2019011</id>
        <name>千玺三</name>
        <course>跳舞</course>
        <score>96</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_669">
        <id>2019012</id>
        <name>千玺3</name>
        <course>跳舞</course>
        <score>92.5</score>
        <sex>男</sex>
    </grade>

    <grade grade_id="grade_610">
        <id>2019013</id>
        <name>千玺四</name>
        <course>跳舞</course>
        <score>90.5</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_611">
        <id>2019014</id>
        <name>千玺五</name>
        <course>跳舞</course>
        <score>99</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_612">
        <id>2019015</id>
        <name>千玺六</name>
        <course>跳舞</course>
        <score>97.5</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_613">
        <id>2019016</id>
        <name>千玺七</name>
        <course>跳舞</course>
        <score>97.5</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_614">
        <id>2019008</id>
        <name>刘艳芬</name>
        <course>跳舞</course>
        <score>100</score>
        <sex>女</sex>
    </grade>
    <grade grade_id="grade_615">
        <id>2019018</id>
        <name>千玺八</name>
        <course>跳舞</course>
        <score>99</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_616">
        <id>2019019</id>
        <name>千玺九</name>
        <course>跳舞</course>
        <score>99</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_617">
        <id>2019020</id>
        <name>千玺十</name>
        <course>跳舞</course>
        <score>97.5</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_618">
        <id>2019021</id>
        <name>千玺十一</name>
        <course>跳舞</course>
        <score>97.5</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_619">
        <id>2019022</id>
        <name>千玺十二</name>
        <course>跳舞</course>
        <score>100</score>
        <sex>男</sex>
    </grade>
    <grade grade_id="grade_777">
        <id>2019008</id>
        <name>刘艳芬</name>
        <course>数据库</course>
        <score>95</score>
        <sex>女</sex>
    </grade>
</grades>

（5）源程序代码：

i）Util工具类：包括DB工具、XML解析工具、文件处理工具等

package com.remoa.test.util;

import com.remoa.test.domain.Course;
import com.remoa.test.domain.Grade;
import com.remoa.test.domain.Stu;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
    private static final String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String user = "sa";
    private static final String password = "123456";
    private static final String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=SCUT";
    public static final String TABLE_NOT_EXIST = "table-not-exist";

    public static Connection conn = null;

    public DBUtil(){
        try{
            Class.forName(driverName);

        }catch(Exception e){
            System.out.println("Connection Failed");
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            conn = DriverManager.getConnection(url, user, password);
        }catch(Exception e){
            System.out.println("获取连接失败");
        }
        return conn;
    }

    public static void close(Statement stmt, ResultSet rst, Connection conn){
        try {
            if(stmt != null){
                stmt.close();
            }
            if(rst != null){
                rst.close();
            }
            if(conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println("关闭连接失败");
            e.printStackTrace();
        }
    }

    public static void initInsertAllTable(List<Grade> gradeList) {
        try {
            conn = getConnection();
            String deleteSql = "if exists(select * from sysobjects where name = 'grade' and type = 'U') " +
                    "delete from grade; " +
                    "if exists(select * from sysobjects where name = 'course' and type = 'U') " +
                    "delete from course; " +
                    "if exists(select * from sysobjects where name = 'stu' and type = 'U') " +
                    "delete from stu;";
            Statement stmt = conn.createStatement();
            stmt.execute(deleteSql);

            String courseSql = "if not exists(select * from course where course_name = ?) " +
                    "insert into course(course_name) values(?)";
            String stuSql = "if not exists(select * from stu where stu_id = ?) " +
                    "insert into stu values(?, ?, ?)";
            String gradeSql = "insert into grade values(?, ?, ?, ?)";

            PreparedStatement psCourse = conn.prepareStatement(courseSql);
            PreparedStatement psGrade = conn.prepareStatement(gradeSql);
            PreparedStatement psStu = conn.prepareStatement(stuSql);
            for(int i = 0; i < gradeList.size(); i++){
                psCourse.setString(1, gradeList.get(i).getCourse());
                psCourse.setString(2, gradeList.get(i).getCourse());
                psCourse.executeUpdate();
                ResultSet rst = stmt.executeQuery("select course_id from course where course_name = '" + 
                        gradeList.get(i).getCourse() + "'");
                rst.next();

                psStu.setString(1, gradeList.get(i).getStuId());//查看该学生信息是否已经插入了
                psStu.setString(2, gradeList.get(i).getStuId());
                psStu.setString(3, gradeList.get(i).getName());
                psStu.setString(4, gradeList.get(i).getSex());
                psStu.executeUpdate();

                psGrade.setInt(1, rst.getInt(1));
                psGrade.setString(2, gradeList.get(i).getStuId());
                psGrade.setString(3, gradeList.get(i).getGradeId());
                psGrade.setDouble(4, gradeList.get(i).getScore());
                psGrade.executeUpdate();
                close(null, rst, null);
            }
            close(psCourse, null, null);
            close(psGrade, null, null);
            close(psStu, null, conn);
            System.out.println("数据已成功导入数据库");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void createTable(String sql, String tableName){
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            close(stmt, null, conn);
            System.out.println("创建表" + tableName + "成功");
        } catch (Exception e) {
            System.out.println("创建表" + tableName + "失败");
            e.printStackTrace();
        }
    }

    public static List<Grade> searchDataByCourse(String sql){
        List<Grade> resultList = new ArrayList<>();
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery(sql);
            while(rst.next()){
                if(rst.getString(1).equals(TABLE_NOT_EXIST)) {
                    break;
                }
                Grade grade = new Grade();
                grade.setCourseId(rst.getInt(1));
                grade.setStuId(rst.getString(2));
                grade.setGradeId(rst.getString(3));
                grade.setScore(rst.getDouble(4));
                grade.setCourse(rst.getString(5));
                grade.setName(rst.getString(6));
                resultList.add(grade);
            }
            close(stmt, rst, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultList;
    }

    public static List<Stu> getAllStu(String sql){
        List<Stu> resultList = new ArrayList<>();
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery(sql);
            while(rst.next()){
                if(rst.getString(1).equals(TABLE_NOT_EXIST)) {
                    break;
                }
                Stu stu = new Stu();
                stu.setStuId(rst.getString(1));
                stu.setName(rst.getString(2));
                stu.setSex(rst.getString(3));
                resultList.add(stu);
            }
            close(stmt, rst, conn);
        } catch (SQLException e) {
            System.out.println("获取学生表数据失败");
            e.printStackTrace();
        }
        return resultList;
    }

    public static List<Course> getAllCourse(String sql){
        List<Course> resultList = new ArrayList<>();
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery(sql);
            while (rst.next()) {
                if(rst.getString(1).equals(TABLE_NOT_EXIST)) {
                    break;
                }
                Course course = new Course();
                course.setCourseId(rst.getInt(1));
                course.setCourse(rst.getString(2));
                resultList.add(course);
            }
            close(stmt, rst, conn);
            System.out.println("获取课程表数据:" + resultList.size() + "条");
        } catch (SQLException e) {
            System.out.println("获取课程表数据失败");
            e.printStackTrace();
        }
        return resultList;
    }

    public static List<Grade> getAllGrade(String sql){
        List<Grade> resultList = new ArrayList<>();
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery(sql);
            while(rst.next()){
                if(rst.getString(1).equals(TABLE_NOT_EXIST)) {
                    break;
                }
                Grade grade = new Grade();
                grade.setCourseId(rst.getInt(1));
                grade.setStuId(rst.getString(2));
                grade.setGradeId(rst.getString(3));
                grade.setScore(rst.getDouble(4));
                resultList.add(grade);
            }
            close(stmt, rst, conn);
        } catch (SQLException e) {
            System.out.println("获取成绩数据失败");
            e.printStackTrace();
        }
        return resultList;
    }
}

package com.remoa.test.util;

import com.remoa.test.domain.Grade;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

public class XMLUtil {
    public static List<Grade> XMLRead(String path){
        List<Grade> resultList = new ArrayList<>();
        //初始化一个XML解析工厂
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        try {
            //创建解析器实例
            DocumentBuilder builder = factory.newDocumentBuilder();
            File file = null;
            if(path == null) {
                URL url = XMLUtil.class.getClassLoader().getResource("grades.xml");
                if(url != null) {
                    file = new File(url.getFile());
                }
            }else{
                file = new File(path);
            }
            Document document = builder.parse(file);
            //根据根元素名获取根元素集合
            NodeList gradeList = document.getElementsByTagName("grade");
            for (int i = 0; i < gradeList.getLength(); i++) {
                Grade grade = new Grade();
                Element gradeElm = (Element) gradeList.item(i);
                //获得属性
                String gradeId = gradeElm.getAttribute("grade_id");
                grade.setGradeId(gradeId);
                //获得子元素
                NodeList nodeList = gradeElm.getChildNodes();
                for (int j = 0; j < nodeList.getLength(); j++) {
                    Node node = nodeList.item(j);
                    if(node.getNodeType() == Node.ELEMENT_NODE){
                        Element child = (Element)node;
                        switch(child.getNodeName()){
                            case "id":
                                grade.setStuId(child.getTextContent());
                                break;
                            case "name":
                                grade.setName(child.getTextContent());
                                break;
                            case "course":
                                grade.setCourse(child.getTextContent());
                                break;
                            case "score":
                                grade.setScore(Double.valueOf(child.getTextContent()));
                                break;
                            case "sex":
                                grade.setSex(child.getTextContent());
                                break;
                        }
                    }
                }
                resultList.add(grade);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }
}
package com.remoa.test.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;

public class FileUtil {
    public static void writeTxt(File file, String word){
        try {
            BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, true)));
            out.write(word + "\r\n");
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ii）图形界面代码：包括三个选项卡及主窗体

文件上传以及数据库导入：

package com.remoa.test.view;

import com.remoa.test.domain.Grade;
import com.remoa.test.service.ExamService;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.*;
import java.util.List;
import java.util.UUID;

//导入XML文件到数据库
public class UploadJPanel extends JPanel{
    ExamService service = new ExamService();
    public UploadJPanel(String directPath, String outputPath){
        JLabel label = new JLabel("1.上传xml文件以导入数据库：");
        label.setHorizontalAlignment(SwingConstants.LEFT);
        add(label);
        JToolBar toolBar = new JToolBar();
        JButton button = new JButton("上传文件");
        button.setHorizontalAlignment(SwingConstants.CENTER);
        toolBar.add(button);
        add(toolBar);
        button.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseClicked(MouseEvent e) {
                importFile(new JButton(), directPath, outputPath);
            }
        });
    }

    public void importFile(JButton button, String directPath, String outputPath){
        JFileChooser chooser = new JFileChooser();
        chooser.setMultiSelectionEnabled(false);
        chooser.setFileFilter(new FileFilter() {
            @Override
            public boolean accept(File f) {
                if(f.getName().endsWith(".txt") || f.getName().endsWith(".xml") || f.isDirectory()){
                    return true;
                }
                return false;
            }

            @Override
            public String getDescription() {
                return "请选择txt或xml文件";
            }
        });
        chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
        int returnVal = chooser.showOpenDialog(button);
        try {
            if(returnVal == JFileChooser.APPROVE_OPTION) {
                File file = chooser.getSelectedFile();
                if (file.getName().endsWith(".txt") || file.getName().endsWith(".xml") ) {
                    InputStream inputStream;
                    OutputStream out;
                    String absolutePath = directPath + file.getName();
                    File tmp = new File(absolutePath);
                    if (tmp.exists()) {
                        absolutePath = directPath + UUID.randomUUID().toString().substring(0,9) + file.getName();
                    }
                    inputStream = new FileInputStream(file);
                    byte[] buffer = new byte[1024];
                    out = new FileOutputStream(absolutePath);
                    int len = 0;
                    while ((len = inputStream.read(buffer)) != -1) {
                        out.write(buffer, 0, len);
                    }
                    out.close();
                    inputStream.close();
                    List<Grade> gradeList = service.getXMLGrade(absolutePath);
                    service.insertAllData(gradeList);
                    //按照成绩降序排序输出成txt
                    service.writeTxt(gradeList, outputPath);
                    JOptionPane.showMessageDialog(null, "数据导入数据库成功", "提示", JOptionPane.INFORMATION_MESSAGE);
                }else{
                    JOptionPane.showMessageDialog(null, "请按格式上传", "提示", JOptionPane.WARNING_MESSAGE);
                }
            }
        } catch (Exception e) {
            System.out.println("文件上传异常");
            JOptionPane.showMessageDialog(null, "上传失败", "提示", JOptionPane.ERROR_MESSAGE);
            e.printStackTrace();
        }
    }
}

查看所有库表：

package com.remoa.test.view;

import com.remoa.test.domain.Course;
import com.remoa.test.domain.Grade;
import com.remoa.test.domain.Stu;
import com.remoa.test.service.ExamService;

import javax.swing.*;
import javax.swing.table.AbstractTableModel;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.util.List;

public class ViewAllDataPanel extends JPanel {
    public ViewAllDataPanel(){
        ExamService service = new ExamService();
        JLabel label2 = new JLabel("2.查看所有数据库表：");
        add(label2);
        JComboBox<String> comboBox = new JComboBox<>();
        String[] items = new String[]{"--请选择--", "课程表", "学生表", "成绩表"};
        DefaultComboBoxModel<String> model = new DefaultComboBoxModel<>(items);
        comboBox.setModel(model);
        add(comboBox);
        comboBox.addItemListener(new ItemListener() {
            JScrollPane scrollPane = new JScrollPane();
            JTable table = null;
            @Override
            public void itemStateChanged(ItemEvent e) {
                System.out.println("click :" +e.getItem().toString());
                AbstractTableModel model = null;
                if(e.getItem().toString().equals("课程表")){
                    model = new CourseDataModel();
                }else if(e.getItem().toString().equals("学生表")){
                    model = new StuDataModel();
                }else if(e.getItem().toString().equals("成绩表")){
                    model = new GradeDataModel();
                }
                if(model != null) {
                    model.fireTableDataChanged();
                    table = new JTable(model);
                    table.setPreferredSize(new Dimension(500, 800));
                }else{
                    table = null;
                }
                scrollPane.getViewport().add(table);
                add(scrollPane);
                revalidate();
            }
        });

    }
}

class CourseDataModel extends AbstractTableModel {
    List<Course> courseList;
    public CourseDataModel(){
        ExamService service = new ExamService();
        courseList = service.getAllCourse();
    }

    @Override
    public int getRowCount() {
        return courseList.size();
    }

    @Override
    public int getColumnCount() {
        return 2;
    }

    //数据库查询字段依次返回：course_id、course_name
    //将每一列赋予对应的list中的值
    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        Course course = courseList.get(rowIndex);
        switch(columnIndex){
            case 0:
                return course.getCourseId();
            case 1:
                return course.getCourse();
        }
        return null;
    }

    @Override
    public String getColumnName(int column){
        switch (column){
            case 0:
                return "课程Id";
            case 1:
                return "课程名称";
        }
        return null;
    }
}

class StuDataModel extends AbstractTableModel {
    List<Stu> stuList;
    public StuDataModel(){
        ExamService service = new ExamService();
        stuList = service.getAllStu();
    }

    @Override
    public int getRowCount() {
        return stuList.size();
    }

    @Override
    public int getColumnCount() {
        return 3;
    }

    //数据库查询字段依次返回：stu_id、name、sex
    //将每一列赋予对应的list中的值
    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        Stu stu = stuList.get(rowIndex);
        switch(columnIndex){
            case 0:
                return stu.getStuId();
            case 1:
                return stu.getName();
            case 2:
                return stu.getSex();
        }
        return null;
    }

    @Override
    public String getColumnName(int column){
        switch (column){
            case 0:
                return "学生Id";
            case 1:
                return "学生姓名";
            case 2:
                return "性别";
        }
        return null;
    }
}


class GradeDataModel extends AbstractTableModel {
    List<Grade> gradeList;
    public GradeDataModel(){
        ExamService service = new ExamService();
        gradeList = service.getAllGrade();
    }

    @Override
    public int getRowCount() {
        return gradeList.size();
    }

    @Override
    public int getColumnCount() {
        return 4;
    }

    //数据库查询字段依次返回：course_id、stu_id、grade_id、score
    //将每一列赋予对应的list中的值
    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        Grade grade = gradeList.get(rowIndex);
        switch(columnIndex){
            case 0:
                return grade.getCourseId();
            case 1:
                return grade.getStuId();
            case 2:
                return grade.getGradeId();
            case 3:
                return grade.getScore();
        }
        return null;
    }

    @Override
    public String getColumnName(int column){
        switch (column){
            case 0:
                return "课程Id";
            case 1:
                return "学生Id";
            case 2:
                return "成绩Id";
            case 3:
                return "分数";
        }
        return null;
    }
}

按照课程名查看成绩：

package com.remoa.test.view;

import com.remoa.test.domain.Course;
import com.remoa.test.domain.Grade;
import com.remoa.test.service.ExamService;

import javax.swing.*;
import javax.swing.table.AbstractTableModel;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.util.List;

import static java.util.Collections.sort;

public class GetGradePanel extends JPanel {
    public GetGradePanel(){
        ExamService service = new ExamService();
        JLabel label = new JLabel("3.课程名：");
        add(label);
        DefaultComboBoxModel<String> model = new DefaultComboBoxModel<>();
        model.addElement("------请选择课程------");
        JComboBox comboBox = new JComboBox(model);
        add(comboBox);
        comboBox.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseEntered(MouseEvent e) {
                mouseClicked(e);
            }

            @Override
            public void mouseClicked(MouseEvent e) {
                int index = comboBox.getSelectedIndex();
                model.removeAllElements();
                model.addElement("------请选择课程------");
                List<Course> list = service.getAllCourse();
                for (int i = 0; i < list.size(); i++) {
                    model.addElement(list.get(i).getCourse());
                }
                comboBox.setModel(model);
                if(index < list.size()) {
                    comboBox.setSelectedIndex(index);
                }
            }
        });
        comboBox.addItemListener(new ItemListener() {
            JScrollPane scrollPane = new JScrollPane();
            JTable table = null;
            @Override
            public void itemStateChanged(ItemEvent e) {
                if(table != null){
                    remove(table);
                }
                if(e.getStateChange() == ItemEvent.SELECTED){
                    AllDataModel model = new AllDataModel(e.getItem().toString());
                    model.fireTableDataChanged();//刷新表格以显示数据
                    table = new JTable(model);
                    table.setPreferredSize(new Dimension(600, 800));
                    scrollPane.getViewport().add(table);
                    add(scrollPane);
                    revalidate();
                }
            }
        });
    }
}

class AllDataModel extends AbstractTableModel{
    List<Grade> gradeList;
    public AllDataModel(String name){
        ExamService service = new ExamService();
        gradeList = service.searchGrade(name);
        sort(gradeList);
    }

    @Override
    public int getRowCount() {
        return gradeList.size();
    }

    @Override
    public int getColumnCount() {
        return 6;
    }

    //数据库查询字段依次返回：course_id、stu_id、grade_id、score、course_name、stu_name
    //将每一列赋予对应的list中的值
    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        Grade grade = gradeList.get(rowIndex);
        switch(columnIndex){
            case 0:
                return grade.getGradeId();
            case 1:
                return grade.getStuId();
            case 2:
                return grade.getName();
            case 3:
                return grade.getCourseId();
            case 4:
                return grade.getCourse();
            case 5:
                return grade.getScore();
        }
        return null;
    }

    @Override
    public String getColumnName(int column){
        switch (column){
            case 0:
                return "成绩Id";
            case 1:
                return "学生Id";
            case 2:
                return "学生姓名";
            case 3:
                return "课程Id";
            case 4:
                return "课程名称";
            case 5:
                return "分数";
        }
        return null;
    }
}

选项卡类：

package com.remoa.test.view;

import javax.swing.*;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;
import java.awt.*;

public class MyTabbedPane extends JTabbedPane {
    public MyTabbedPane(String inputPath, String outputPath){
        addTab("数据库导入", new UploadJPanel(inputPath, outputPath));
        addTab("数据库表查看", new ViewAllDataPanel());
        addTab("根据课程名查成绩", new GetGradePanel());
        setPreferredSize(new Dimension(600, 350));
        addChangeListener(new ChangeListener() {
            @Override
            public void stateChanged(ChangeEvent e) {
                System.out.println("change to：" + getTitleAt(getSelectedIndex()));
            }
        });
    }
}

主窗体类：

package com.remoa.test.view;

import javax.swing.*;
import java.awt.*;

public class MyView extends JFrame {
    public MyView(String inputPath, String outputPath){
        setLayout(new FlowLayout());
        setBounds(100, 100, 700, 400);
        setVisible(true);
        setTitle("复试 Remoa：3114005847");
        setDefaultCloseOperation(EXIT_ON_CLOSE);
        add(new MyTabbedPane(inputPath, outputPath));
        validate();
    }
}

iii）实体类：学生实体、课程实体、成绩实体

package com.remoa.test.domain;

public class Stu {
    private String stuId;
    private String name;
    private String sex;

    public String getStuId() {
        return stuId;
    }

    public void setStuId(String stuId) {
        this.stuId = stuId;
    }

    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;
    }
}

package com.remoa.test.domain;

public class Course {
    private Integer courseId;
    private String course;

    public Integer getCourseId() {
        return courseId;
    }

    public void setCourseId(Integer courseId) {
        this.courseId = courseId;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }
}

package com.remoa.test.domain;

public class Grade implements Comparable<Grade>{
    private String gradeId;
    private String stuId;
    private String name;
    private String course;
    private double score;
    private Integer courseId;

    public Integer getCourseId() {
        return courseId;
    }

    public void setCourseId(Integer courseId) {
        this.courseId = courseId;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    private String sex;

    public String getGradeId() {
        return gradeId;
    }

    public void setGradeId(String gradeId) {
        this.gradeId = gradeId;
    }

    public String getStuId() {
        return stuId;
    }

    public void setStuId(String stuId) {
        this.stuId = stuId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }

    public double getScore() {
        return score;
    }

    public void setScore(double score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return this.getGradeId() + "," + getStuId() + "," + getName() + "," + getCourse() + "," + getScore();
    }

    @Override
    public int compareTo(Grade grade) {
        if(grade.getScore() == this.getScore()){
            return this.getName().compareTo(grade.getName()); //得分相同，则按照姓名升序
        }
        return (int)(grade.getScore() - this.getScore());//按照成绩得分降序
    }
}

iv）数据访问dao层：StuDao、CourseDao、GradeDao

package com.remoa.test.dao;

import com.remoa.test.domain.Stu;
import com.remoa.test.util.DBUtil;

import java.util.List;

public class StuDao {
    public List<Stu> getAllStu(){
        String sql = "if exists(select * from sysobjects where name = 'stu' and type = 'U') " +
                "select * from stu else select '" + DBUtil.TABLE_NOT_EXIST + "'";
        return DBUtil.getAllStu(sql);
    }
}

package com.remoa.test.dao;

import com.remoa.test.domain.Course;
import com.remoa.test.util.DBUtil;

import java.util.List;

public class CourseDao {
    public List<Course> getAllCourse(){
        String sql = "if exists(select * from sysobjects where name = 'course' and type = 'U') " +
                "select * from course else select '" + DBUtil.TABLE_NOT_EXIST + "'";
        return DBUtil.getAllCourse(sql);
    }
}

package com.remoa.test.dao;

import com.remoa.test.domain.Grade;
import com.remoa.test.util.DBUtil;

import java.util.List;

public class GradeDao {
    public void createCourse() {
        String sql = "if not exists(select * from sysobjects where name = 'course' and type = 'U') " +
                "create table course(" +
                "course_id int primary key identity(1, 1)," + //从1开始，每次增长1
                "course_name varchar(50) not null" +
                ");";
        DBUtil.createTable(sql, "course");
    }

    public void createStu(){
        String sql = "if not exists(select * from sysobjects where name = 'stu' and type = 'U') " +
                "create table stu(" +
                "stu_id varchar(50) primary key," +
                "name varchar(50) not null," +
                "sex varchar(50) not null" +
                ");";
        DBUtil.createTable(sql, "stu");
    }

    public void createGrade(){
        String sql = "if not exists(select * from sysobjects where name = 'grade' and type = 'U') " +
                "create table grade(" +
                "course_id int, " +
                "stu_id varchar(50), " +
                "grade_id varchar(50) primary key," +
                "score decimal(4,1) not null," +
                "foreign key(course_id) references course(course_id)," +
                "foreign key(stu_id) references stu(stu_id)" +
                ");";
        DBUtil.createTable(sql, "grade");
    }

    public List<Grade> getGradeByCourse(String course){
        String sql = "if exists(select * from sysobjects where name = 'grade' and type = 'U') " +
                "select grade.*, course.course_name, stu.name from grade, course, stu " +
                "where course_name = '" + course + "' " +
                "and grade.course_id = course.course_id " +
                "and stu.stu_id = grade.stu_id " +
                "else select '" + DBUtil.TABLE_NOT_EXIST + "'";
        return DBUtil.searchDataByCourse(sql);
    }

    public List<Grade> getAllGrade(){
        String sql = "if exists(select * from sysobjects where name = 'grade' and type = 'U') " +
                "select * from grade else select '" + DBUtil.TABLE_NOT_EXIST + "'";
        return DBUtil.getAllGrade(sql);
    }
}

v）逻辑服务service层：

package com.remoa.test.service;

import com.remoa.test.dao.CourseDao;
import com.remoa.test.dao.GradeDao;
import com.remoa.test.dao.StuDao;
import com.remoa.test.domain.Course;
import com.remoa.test.domain.Grade;
import com.remoa.test.domain.Stu;
import com.remoa.test.util.DBUtil;
import com.remoa.test.util.FileUtil;
import com.remoa.test.util.XMLUtil;

import java.io.File;
import java.util.List;

import static java.util.Collections.sort;

public class ExamService {
    GradeDao gradeDao = new GradeDao();
    CourseDao courseDao = new CourseDao();
    StuDao stuDao = new StuDao();

    public void insertAllData(List<Grade> list){
        DBUtil.initInsertAllTable(list);
    }

    public void createAllTable(){
        gradeDao.createStu();
        gradeDao.createCourse();
        gradeDao.createGrade();
    }

    public List<Grade> searchGrade(String courseName){
        return gradeDao.getGradeByCourse(courseName);
    }

    public List<Course> getAllCourse(){
        return courseDao.getAllCourse();
    }

    public List<Grade> getAllGrade(){
        return gradeDao.getAllGrade();
    }

    public List<Stu> getAllStu(){
        return stuDao.getAllStu();
    }

    public List<Grade> getXMLGrade(String path){
        return XMLUtil.XMLRead(path);
    }

    public void writeTxt(List<Grade> list, String outputPath){
        sort(list);
        try {
            File file = new File(outputPath);
            if(file.exists()){
                file.delete();
            }
            file.createNewFile();
            for (int i = 0; i < list.size(); i++) {
                FileUtil.writeTxt(file, list.get(i).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

vi）业务控制controller层：

package com.remoa.test.controller;

import com.remoa.test.view.MyView;
import com.remoa.test.service.ExamService;

public class Main {
    private static final String outputPath = "C:/Users/邓小艺/Desktop/output.txt";
    private static final String uploadRootPath = "C:/Users/邓小艺/Desktop/2020考研复试/";

    public static void main(String[] args) {
        ExamService service = new ExamService();
        /*1、建三个表：课程表（课程Id，课程名称），
            学生表（学生Id，学生姓名，性别），
            成绩表（课程Id，学生Id，成绩Id，分数）
        */
        service.createAllTable();

        //方式1：直接在程序中后台导入所有数据
        //service.insertAllData(service.getXMLGrade(uploadRootPath + "grades.xml"));

        //方式2：让用户在界面上操作导入xml文件数据到数据库，同时按照成绩降序排序输出成txt
        new MyView(uploadRootPath, outputPath);
    }
}

 

评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值